Excellent blogpost.. Using ClosedXml Nuget pkg..
http://www.c-sharpcorner.com/UploadFile/rahul4_saxena/export-data-table-to-excel-in-Asp-Net-mvc-4/
Excerpt:
public ActionResult ExportReportDataPerReport()
DataTable dt = new DataTable(“ReportDataExport”);
// tableName = ReportDataExport (also becomes sheet name in Xlsx)
// Add Columns to the dt. Then add data using dt.NewRow and
dt.AddRow …
// Once dt is populated, run below code..
using (var wb = new XLWorkbook())
{
wb.Worksheets.Add(dt);
wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
wb.Style.Font.Bold = true;
Response.Clear();
Response.Buffer = true;
Response.Charset = “”;
Response.ContentType = “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”;
string filenameSuffix = String.Format(“[{0}] [{1} to {2}]”,
reportContainer.FuelTypeName,
reportContainer.FromDate.Value.ToString(“dd-MMM-yyyy”),
reportContainer.ToDate.Value.ToString(“dd-MMM-yyyy”));
string downloadHeader = String.Format(“attachment;filename= MyReport{0}.xlsx”,
filenameSuffix);
Response.AddHeader(“content-disposition”, downloadHeader);
using (var myMemoryStream = new MemoryStream())
{
wb.SaveAs(myMemoryStream);
myMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
return new EmptyResult();
}
}
} // end of MVC action