Export DataTable to Excel with EPPlus

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

}

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.


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