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?

Persecution

Answer 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.

Keheley

How to format headers of datatable while exporting ?? – Kingpin

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

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

The headers will be included @AbhinavRastogi – Pipage

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

Clean approach. Thanks! – Bryon

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

Answer 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);                          
}

Campbellite

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

Answer 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();

}

Musket

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 #19432320 – Onomatopoeia

Answer 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;

Placia

Answer 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")

Goggles