CODEWITHSUNDEEP
c#excelepplus
mdmilic
mdmilic

Reputation: 13

Exporting list of strings to an excel cell using EPPlus

I'm currently trying to export some DataTable data to excel. One of the columns in my table is List<string>. I first create a DataTableReader and then try to load the data into a worksheet using LoadFromDataReader like so:

public static ToReader(List<object> data)
{
    var toReturn = new DataTable();
    // Complex code about creating the data
    return new DataTableReader(toReturn);
}
//...
public static Export(List<object> data)
{
    using (var pck = new ExcelPackage())
    {
        // add the content into the Excel file
        ExcelWorksheet ws = pck.Workbook.Worksheets["Data"];
        ws.Cells[startCell].LoadFromDataReader(ToReader(data), true);
        //....
    }
}

All the data gets exported to excel just fine except for the column which contains List<string>. For this column I would expect the cell value to be comma separated values of that list, instead I just get the first element of the list.

E.g. if the list was {"Fee", "Fi", "Fo", "Fum"}, I would expect the cell value to be "Fee, Fi, Fo, Fum", instead I get "Fee".

From debugging I see that data is correctly written to the DataTableReader, so the problem must be with loading it into excel. What am I doing wrong here, and how can I have this List column correctly exported to excel?

Upvotes: 1

Views: 2098

Answers (1)

Ernie S
Ernie S

Reputation: 14250

This is not a CSV question so much. It is specific to how Epplus handles table rows that contain non-string object types. If it detects is an IEnumerable it will actually cast it and grab the FIRST entry and call ToString() implicitly on the item. You can see that here:

https://github.com/JanKallman/EPPlus/blob/v4.5.3/EPPlus/ExcelWorksheet.cs#L3714

Its a strange call but I assume the referenced bug has something to do with it.

Your best bet would be to generate the list yourself and store the as a string in the data table rather than a collection. This would require you to change the underlying data type unfortunately but it would allow you to do something like:

string.Join(", ", myList)

into your object and push into the LoadFromDataReader.

Upvotes: 1

Related Questions