Export DataTable to Excel with EPPlus

Question

I want to export a data table to an Excel file with EPPlus. That data table has a property with int type, so I want the same format in the Excel file.

Does anyone know way to export a DataTable like this to Excel?

Solution 1

using (ExcelPackage pck = new ExcelPackage(newFile))
{
  ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Accounts");
  ws.Cells["A1"].LoadFromDataTable(dataTable, true);
  pck.Save();
}

That should do the trick for you. If your fields are defined as int EPPlus will properly cast the columns into a number or float.

Solution 2

and if you want to download in browser response

Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode("Logs.xlsx", System.Text.Encoding.UTF8));

using (ExcelPackage pck = new ExcelPackage())
{
    ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Logs");
    ws.Cells["A1"].LoadFromDataTable(dt, true);                 
    var ms = new System.IO.MemoryStream();
    pck.SaveAs(ms);
    ms.WriteTo(Response.OutputStream);                          
}

Solution 3

For downloading excelsheet in browser use HttpContext.Current.Response instead of Response otherwise you will get Response is not available in this context. error.Here is my code

public void ExporttoExcel(DataTable table, string filename)
{
    HttpContext.Current.Response.Clear();
    HttpContext.Current.Response.ClearContent();
    HttpContext.Current.Response.ClearHeaders();
    HttpContext.Current.Response.Buffer = true;
    HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
    HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache);
    HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=GridData.xlsx");


    using (ExcelPackage pack = new ExcelPackage())
    {
        ExcelWorksheet ws = pack.Workbook.Worksheets.Add(filename);
        ws.Cells["A1"].LoadFromDataTable(table, true);
        var ms = new System.IO.MemoryStream();
        pack.SaveAs(ms);
        ms.WriteTo(HttpContext.Current.Response.OutputStream); 
    }

    HttpContext.Current.Response.Flush();
    HttpContext.Current.Response.End();

}

Solution 4

Here is a snippet to export DataSet to Excel:

    private static void DataSetToExcel(DataSet dataSet, string filePath)
    {
        using (ExcelPackage pck = new ExcelPackage())
        {
            foreach (DataTable dataTable in dataSet.Tables)
            {
                ExcelWorksheet workSheet = pck.Workbook.Worksheets.Add(dataTable.TableName);
                workSheet.Cells["A1"].LoadFromDataTable(dataTable, true);
            }

            pck.SaveAs(new FileInfo(filePath));
        }
    }

And using statements:

using OfficeOpenXml;
using System.Data;
using System.IO;

Solution 5

Foreword

^{With v5, EPPlus switched to a paid-for licensing model for commercial use. To use v5 in a non-commercial setting you need to put this static line of code somewhere that will run:}

ExcelPackage.LicenseContext = LicenseContext.NonCommercial

^{If you're using it commercially, your company can obtain a license or use v4.5.3.3 (it does work in netcore/net5) which was the last version that can be used fee-free commercially}

The following code works on 4.5.3.3

C#

DataTable to Excel, using column names as excel headers.

It also loops over the table afterwards and sets any DateTime columns so that they show in Excel as a date, not a number like 45123

        DataTable dt = ...;
        string sheetName = ...;
        string dateFormat = "yyyy-MM-dd HH:mm:ss";

        using var p = new ExcelPackage();
        var ws = p.Workbook.Worksheets.Add(sheetName);
        ws.Cells["A1"].LoadFromDataTable(dt, PrintHeaders: true);
        for (int c = 0; c < dt.Columns.Count; c++)
        {
            if (dt.Columns[c].DataType == typeof(DateTime))
            {
                ws.Column(c + 1).Style.Numberformat.Format = dateFormat;
            }
        }

If you're using this in e.g. an API controller you can use the following to return it as a downloading file:

    string fileName = ...;  //without extension

    return File(p.GetAsByteArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName + ".xlsx");

Be aware of the scope of the using! It's C#8 syntax and lives until the end of the block it's declared in

VB.NET

Same as above, but in VB:

    Dim dt As DataTable = ...
    Dim sheetName As String = ...
    Dim dateFormat As String = "yyyy-MM-dd HH:mm:ss"

    Using p As New ExcelPackage()
        Dim ws = p.Workbook.Worksheets.Add(sheetName)
        ws.Cells("A1").LoadFromDataTable(dt, PrintHeaders:=True)

        For c As Integer = 0 To dt.Columns.Count - 1

            If dt.Columns(c).DataType Is GetType(Date) Then
                ws.Column(c + 1).Style.Numberformat.Format = dateFormat
            End If
        Next
    End Using

And for the download, it must be placed inside the using block

    Dim fileName As String = ...  'without extension
    Return File(p.GetAsByteArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName & ".xlsx")

View more solutions

Comments

[Abhinav]

How to format headers of datatable while exporting ??

[thevan]

Yes. this is working fine. But when the datatable contains rows in lakhs. This one is not working.

[bastianwegge]

Maybe you should open a question for that specific case then?

[Ray Koren]

For anyone getting 'System.Web.HttpContextBase' does not contain a definition for 'Current': To get a reference to HttpContext.Current you need replace HttpContext.Current with System.Web.HttpContext.Current stackoverflow.com/questions/19431820/…

[Anders Lindén]

Thanks for the snippet! No need to urlencode "Logs.xlsx" :)

[Helder Gurgel]

The headers will be included @AbhinavRastogi

[B.Hawkins]

using OfficeOpenXml; (required for reference to ExcelPackage/ExcelWorksheet)

[Ashokan Sivapragasam]

Clean approach. Thanks!

[bastianwegge]

This is 9 years old and still helps people. SO amazes me again and again. @AshokanSivapragasam thank you for reminding me of this!