1

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?

mdmilic
  • 13
  • 1
  • 4
  • 1
    You seem to have obscured away some code, but I assume you might use `.Join()` to create a comma separated string from a list [Creating a comma separated list from IList or IEnumerable](https://stackoverflow.com/questions/799446/creating-a-comma-separated-list-from-iliststring-or-ienumerablestring) – Equalsk Dec 09 '19 at 17:29
  • @Equalsk, thanks for your answer. I omitted the data generating code because it's actually using reflection to get values from the model classes using data attributes, which isn't really relevant. I was thinking of joining strings like you suggested, but was hoping that EPPlus has a built in mechanism to deal with columns which are collections. – mdmilic Dec 09 '19 at 21:51
  • @devlincarnate thanks, as mentioned above my code is generic and using reflection, so would prefer not to special case something if I don't have to. I was hoping that EPPlus has a way of dealing with columns which are collections. It clearly does something (printing just first element of the collection), but just not what I want it to do. – mdmilic Dec 09 '19 at 22:06
  • You can just always use `Join()`...it accepts a string as input as well as a collection. – devlin carnate Dec 10 '19 at 17:04
  • 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 We should re open to give a proper answer. Basicially, your best bet would be to generate the list yourself and store the as a string in the data table rather than a collection. – Ernie S Dec 13 '19 at 22:59
  • @ErnieS, thanks for pointing to the EPPlus code. The way you described it is exactly what I'm getting which in my opinion is a wrong way of handling collections. That's why I thought that I might be missing some config or other way of telling EPPlus how to handle my lists. What you suggest as a solution is what I basically did, but still don't like it and expect EPPlust to be able to handle columns that are lists properly. – mdmilic Dec 16 '19 at 13:13
  • Also @ErnieS, sorry I'm relatively new to this, so not sure if I closed the question (if I did it was not intentional) and if I need to reopen it somehow, or best would be to just open an enhancement ticket for EPPlus if this is their expected behavior? – mdmilic Dec 16 '19 at 13:15
  • @mdmilic Glad to see you got it work. No, you didnt close it, others did. They thought it was a dup of another question which it is not. So it looks like it has been reopened which is good. I will post an answer just so anyone else who has the same question sees it. I aggree with you - its a strange choice on the part of Epplus but the code references a bug so I assume there is a specific reason for it. – Ernie S Dec 16 '19 at 16:26

1 Answers1

1

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.

Ernie S
  • 13,902
  • 4
  • 52
  • 79