How to export data from C# from List<object> or IEnumerable to Excel (MS OpenXml)?

Question

I'm trying to export a List<dynamic> or IEnumerable to an Excel file using the library Open XML of Microsoft.

Library: https://github.com/OfficeDev/Open-XML-SDK

Example of similar request: https://www.codeproject.com/Articles/692121/Csharp-Export-data-to-Excel-using-OpenXML-librarie

The example above works pretty fine but have a lot of code between (As far works neat, but I searching for an optimization)

But the documentation of this library does not extend (or hasn't many examples), and the examples that I saw around about how to export data to Excel it's using a DataTable or Dataset; so, they make the conversation from the List to DataTable to export the DataTable. And that seems pretty complex way to something that other libraries resolve easier.

So, if someone has a sort of example of how to make the export quickly and generic, it will be appreciated.

Answer 1

FYI, here is my implementation, using the code of @mikesknowledgebase (http://www.mikesknowledgebase.com -- The Webpage doesn't work... but to give the credits at least :D)

Post with all information here

---

So, I used it for .Net Core 2.2; and the intention is to use the method to export a List<dynamic> to Excel.

The final result (In the simplest example):

[HttpGet("[action]")]
public FileResult exportExample()
{
    List<dynamic> data = new List<dynamic>();

    data.Add(new { a = 1, b = "HELLO WORLD", c = DateTime.Now });
    data.Add(new { a = 2, b = "TEST", c = 34 });

    // Implementation of Dictionary to limit the columns and the type of the List
    // Works with some standard, and flat files (Not with Dynamic Objects that have multiples levels - Indentation of a dynamic into a property)
    Dictionary<string, Type> columns = new Dictionary<string, Type>();
    columns.Add("a", typeof(int));
    columns.Add("b", typeof(string));
    columns.Add("c", typeof(object)); // Accepts any (Numbers or DateTime)

    string excelContentType;
    var excelStream = CreateExcelFile.CreateExcelStream(data, columns, out excelContentType);

    return File(excelStream, excelContentType, "report.xlsx");
}

I created some other methods inside the class of Mike...

Method to get the List with the Dictionary<string, type> for the columns... and another param to return the ContentType:

public static byte[] CreateExcelStream<T>(List<T> list, Dictionary<string, Type> columns, out string contentType )
{
    DataSet ds = new DataSet();

    ds.Tables.Add(ListToDataTable(list, columns));
    contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; // "application/vnd.ms-excel";

    return CreateExcelStream(ds).fwToByteArray();
}

Another method to convert the List to a DataTable:

public static DataTable ListToDataTable<dynamic>(List<dynamic> list, Dictionary<string, Type> columns)
{
    DataTable dt = new DataTable();

    foreach (var column in columns)
        dt.Columns.Add(new DataColumn(column.Key, GetNullableType(column.Value)));

    foreach (var t in list)
    {
        DataRow row = dt.NewRow();

        ((object)t)
        .GetType()
        .GetProperties()
        .ToList()
        .ForEach(p =>
        {
            if (!IsNullableType(p.PropertyType))
                row[p.Name] = p.GetValue(t, null);
            else
                row[p.Name] = (p.GetValue(t, null) ?? DBNull.Value);
        });
        dt.Rows.Add(row);
    }
    return dt;
}

I included an extension to convert a stream to a byteArray:

public static byte[] fwToByteArray(this Stream stream)
{
    stream.Position = 0;
    byte[] buffer = new byte[stream.Length];

    for (int totalBytesCopied = 0; totalBytesCopied < stream.Length;)
        totalBytesCopied += stream.Read(buffer, totalBytesCopied, Convert.ToInt32(stream.Length) - totalBytesCopied);

    return buffer;
}

The rest of the code still the same... here is the result: