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