Search code examples
c#exceldapperepplus

List of dynamic and EPPlus LoadFromCollection

I have list of dynamic objects which I am getting by calling an api. Now, I need to load it EPPlus using,

List<dynamic> listOfDynamicObjects = Get();
worksheet.Cells.LoadFromCollection(listOfDynamicObjects,...

Now I am getting column out of range exception. Is there is a way to load dynamic objects? List is coming from Dapper.

Update: I used DataTable instead of List and it worked.

Solution

  • Rows loaded from a database aren't dynamic or arbitrary, they have a very strict schema that's returned along with the data. You can use Dapper's ExecuteDataReader and EPPlus's LoadFromDataReader instead of going through dynamic and losing the type information:

    using(var con=new SqlConnection(someConnectionString))
using(var reader=con.ExecuteReader(query))
{
    sheet.Cells["A1"].LoadFromDataReader(reader);
}

    This way you avoid caching the data twice - once in the DataTable or List and once in the Excel package