Write a dictionary to Excel using ClosedXML library

Question

I have a dictionary, the key is a string, the value is a list of string. I want to write it into Excel using the ClosedXML library.

Dictionary<string, List<string>> data = new Dictionary<string, List<string>>();
List<string> list1 = new List<string>();
List<string> list2 = new List<string>();
List<string> list3 = new List<string>();
List<string> list4 = new List<string>();

list1.Add("a1","b1","c1");
list2.Add("a2","b2","c2");
list3.Add("a3","b3","c3");
list4.Add("a4","b4","c4");

data.Add("head1", list1);
data.Add("head2", list2);
data.Add("head3", list3);
data.Add("head4", list4);

So you see the keys are "head1","head2","head3","head4". The expected result in the excel cells should like

head1   head2 head3 head4
a1      a2    a3    a4
b1      b2    b3    b4
c1      c2    c3    c4

If I follow this link closedXml, ws.Cell(1,1).Value = "Hello World!"; is not working at all. It seems the Cell's arguments only accept bool value rather than int. I mean that the example in the link maybe wrong.

Is there a better way to finish the task in C#?

Can you show us the block of code you utilized using `ClosedXML` to add the data to the sheet? The context clues may help. — Sunny Patel, Jan 13 '20 at 23:11
Also, you can't add multiple items to a list with a single call to `Add()` like that, but you can do: `List list1 = new List {"a1", "b1", "c1"};` — Rufus L, Jan 13 '20 at 23:16
In fact, you can initialize the dictionary in one line instead of 13: `var data = new Dictionary> { { "head1", new List { "a1", "b1", "c1" } }, { "head2", new List { "a2", "b2", "c2" } }, { "head3", new List { "a3", "b3", "c3" } }, { "head4", new List { "a4", "b4", "c4" } }, };` — Rufus L, Jan 13 '20 at 23:19

score 3 · Accepted Answer · answered Jan 13 '20 at 23:29

I followed the link and used this code to write columns and rows to spreadsheet,

You can assign string values to the cell.
Use two different loop counters, row and col to go about the spreadsheet to fill in the dictionary.
Row and Col counters starts with 1
Row counter needs to reset after each column is filled in.

using ClosedXML.Excel; // ClosedXML used for this solution...

    Dictionary<string, List<string>> data = new Dictionary<string, List<string>>
    {
        { "head1", new List<string>() { "a1", "b1", "c1" } },
        { "head2", new List<string>() { "a2", "b2", "c2" } },
        { "head3", new List<string>() { "a3", "b3", "c3" } },
        { "head4", new List<string>() { "a4", "b4", "c4" } },
    };

    IXLWorkbook wb = new XLWorkbook();
    IXLWorksheet ws = wb.Worksheets.Add("Sample Sheet");

    int col = 1;
    foreach (var key in data.Keys)
    {
        int row = 1; // Starts with 1
        ws.Cell(row++, col).Value = key;

        foreach (var val in data[key])
            ws.Cell(row++, col).Value = val;

        col++;
    }

    wb.SaveAs(@"C:\temp\excel.xlsx");

Output In Excel

    head1   head2 head3 head4
    a1      a2    a3    a4
    b1      b2    b3    b4
    c1      c2    c3    c4

Write a dictionary to Excel using ClosedXML library

1 Answers1