Export Table to Excel – Server side MVC

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

Leave a Reply