Export Datatable to Excel with Open Xml Sdk in C#

Export DataTable to Excel with Open Xml SDK in c#

I wrote this quick example. It works for me. I only tested it with one dataset with one table inside, but I guess that may be enough for you.

Take into consideration that I treated all cells as String (not even SharedStrings). If you want to use SharedStrings you might need to tweak my sample a bit.

Edit: To make this work it is necessary to add WindowsBase and DocumentFormat.OpenXml references to project.

Enjoy,

private void ExportDataSet(DataSet ds, string destination)
        {
            using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
            {
                var workbookPart = workbook.AddWorkbookPart();

                workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

                workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();

                foreach (System.Data.DataTable table in ds.Tables) {

                    var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
                    var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
                    sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);

                    DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
                    string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);

                    uint sheetId = 1;
                    if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
                    {
                        sheetId =
                            sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                    }

                    DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName };
                    sheets.Append(sheet);

                    DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();

                    List<String> columns = new List<string>();
                    foreach (System.Data.DataColumn column in table.Columns) {
                        columns.Add(column.ColumnName);

                        DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                        cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                        cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
                        headerRow.AppendChild(cell);
                    }

                    sheetData.AppendChild(headerRow);

                    foreach (System.Data.DataRow dsrow in table.Rows)
                    {
                        DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                        foreach (String col in columns)
                        {
                            DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                            cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                            cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
                            newRow.AppendChild(cell);
                        }

                        sheetData.AppendChild(newRow);
                    }

                }
            }
        }

Open XML Export datatable to excel directly on disk

SpreadsheetDocument.Create accepts a stream, string, or package as its first argument so we can just use a MemoryStream to create the workbook in memory and return a byte array.

It should be something like this:

public byte[] ExportToCSVFileOpenXML(DataTable dt)
    {
        byte[] returnBytes = null;
        using (MemoryStream mem = new MemoryStream())
        {
            var workbook = SpreadsheetDocument.Create(mem, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);

            // your code

            workbook.WorkbookPart.Workbook.Save();
            workbook.Close();

            returnBytes = mem.ToArray();
        }

        return returnBytes;
    }

Once you have a byte array passing it as a file should be quite easy.

If you are using MVC it should be something like this in your controler:

return File(ExportToCSVFileOpenXML(aTable), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "export.xlsx");

How to export data from C# from Listobject or IEnumerable to Excel (MS OpenXml)?

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:

---

Related Topics