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