- ClosedXML
- Excel
- DocumentFormat.OpenXml
Create an method for Export data on Controller :
I have stored StudentDetails into TempData["ExportData"] you need to pass your DataTable
[HttpPost]
public ActionResult ExportStudentdata()
{
try
{
DataTable dt = (DataTable)TempData["ExportData"];
SQLhelper.ExportData(dt, "StudentDetails");
}
catch (Exception ex)
{
TempData["Error"] = ex.Message;
}
return View("Index");
}
SQLhelper a class , where written code to export data :
ExportData function required DataTable and Excel file Name which you want.
public static MemoryStream ExportData(DataTable dt, string ExcelFileName)
{
MemoryStream MyMemoryStream = new MemoryStream();
if (dt.Rows.Count > 0)
{
using (XLWorkbook wb = new XLWorkbook())
{
dt.TableName = ExcelFileName;
wb.Worksheets.Add(dt);
wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
wb.Style.Font.Bold = true;
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.Charset = "";
HttpContext.Current.Response.ContentType
= "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
HttpContext.Current.Response.AddHeader
("content-disposition",
"attachment;filename=" + ExcelFileName + "_" + DateTime.Now + ".xlsx");
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(HttpContext.Current.Response.OutputStream);
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
}
return MyMemoryStream;
}
Now need to call controller function to export data :
<body>
@using (@Html.BeginForm("ExportStudentdata", "Home", FormMethod.Post, new { target = "_blank" })) {
<button type="submit" id="btnExport" class="btn btn-success">Export</button>
<!--Make export button type Submit-->
}
</body>
Now your function is ready to export data :
Thanks for giving your valuable time to read this blog!!