public ActionResult ExportDataToExcel(DataTable dtExcelData, string sheetName, string fileNameWithoutExtension, bool totalRequired = true, bool passwordRequired = true)
{
using (XLWorkbook wb = new XLWorkbook())
{
sheetName = sheetName.Replace(" ", "_");
fileNameWithoutExtension = fileNameWithoutExtension.Replace(" ", "_");
dtExcelData.TableName = sheetName;
IXLWorksheet workSheet = wb.Worksheets.Add(dtExcelData);
IXLRow row = workSheet.Row(dtExcelData.Rows.Count + 1);
IXLRange range = workSheet.RangeUsed();
IXLTable xlTable = range.AsTable();
if (totalRequired)
{
string colLetterForSNo = GetExcelColumnLetter(xlTable, "#");
if (!string.IsNullOrEmpty(colLetterForSNo))
{
row.Cell(colLetterForSNo).Value = string.Empty;
}
string colLetterForDaysPresent = GetExcelColumnLetter(xlTable, "Days Present");
if (!string.IsNullOrEmpty(colLetterForDaysPresent))
{
row.Cell(colLetterForDaysPresent).Value = string.Empty;
}
}
wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
wb.Style.Font.Bold = true;
if (passwordRequired)
{
wb.Worksheet(sheetName).Protect("123456");
}
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename= " + fileNameWithoutExtension + ".xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
return(null);
}
}
