Ishak Khan

Ishak Khan

  • NA
  • 25
  • 5.8k

Excel file export - out of memory exception

Apr 13 2017 10:02 AM
protected void imgBtnExcel_Click(object sender, ImageClickEventArgs e)
{
string strExcelPath = string.Empty;
string strCharTypeID = "";
foreach (ListItem item in lstChartType.Items)
{
if (item.Selected == true)
{
strCharTypeID += item.Value.ToString() + ",";
}
}
strCharTypeID = strCharTypeID.TrimEnd(',');
DataTable dtReports = new DataTable();
dtReports = objReportBL.GetHospitalInvoiceProductionReport(txtBeginDate.Text.Trim(), txtEndDate.Text.Trim());
if (Session["dtReport"] != null)
{
DataTable dtReport = Session["dtReport"] as DataTable;
strExcelPath = GenerateExcelWorkSheet(dtReports, "Production Report");
if (dgvProduction.Rows.Count > 0)
{
dgvProduction.UseAccessibleHeader = true;
dgvProduction.HeaderRow.TableSection = TableRowSection.TableHeader;
imgBtnExcel.Visible = true;
}
else
{
imgBtnExcel.Visible = false;
}
try
{
FileInfo file = new FileInfo(strExcelPath);
if (file.Exists)
{
Response.Clear();
Response.ClearHeaders();
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=" + "ProductionReport" + DateTime.Now.ToString().Replace("/", "").Replace(" ", "").Replace(":", "").ToString() + ".xlsx");
Response.AddHeader("Content-Type", "application/Excel");
Response.ContentType = "application/vnd.xls";
Response.AddHeader("Content-Length", file.Length.ToString());
Response.WriteFile(file.FullName);
Response.End();
}
else
{
Response.Write("This file does not exist.");
}
}
catch (Exception ex)
{
}
}

}

private string GenerateExcelWorkSheet(DataTable dtExcelData, string sheetName, string fileName)
{
string _strAppendFileName = string.Empty;

int rowIndex = 1;
int colIndex = 0;
exclSheets = exclBook.Worksheets.Add(sheetName);
exclBook.ShowGridLines = false;

colIndex++;

fileName = fileName + ".xlsx";

exclSheets.Cell(rowIndex, colIndex).Style.Font.Bold = true;

exclSheets.Range(1, 1, 1, dtExcelData.Columns.Count).Style.Fill.BackgroundColor = Excel.XLColor.Yellow;
exclSheets.Range(1, 1, 1, dtExcelData.Columns.Count + 1).Style.Border.LeftBorder = Excel.XLBorderStyleValues.Thin;
exclSheets.Range(1, 1, 1, dtExcelData.Columns.Count).Style.Alignment.Horizontal = Excel.XLAlignmentHorizontalValues.Center;
exclSheets.Range(1, 1, 1, dtExcelData.Columns.Count).Style.Font.SetFontColor(Excel.XLColor.Black);
exclSheets.Range(1, 1, 1, dtExcelData.Columns.Count).Style.Font.Bold = true;

string strText = "";

foreach (DataColumn dCol in dtExcelData.Columns)
{
strText = dCol.Caption.ToString();
exclSheets.Cell(rowIndex, colIndex).Value = strText;
colIndex++;
}
colIndex = 1;
rowIndex++;
colIndex = 0;
string strCellvalue = "";
for (int i = 0; i < dtExcelData.Rows.Count; i++)
{

for (int j = 0; j < dtExcelData.Columns.Count; j++)
{

colIndex++;
strCellvalue = dtExcelData.Rows[i][j].ToString().ToUpper();
exclSheets.Cell(rowIndex, colIndex).Value = "'" + strCellvalue;
}
colIndex = 0;
rowIndex++;
}

char c = Convert.ToChar((64 + dtExcelData.Columns.Count - 2));
string strHeaderRange = GetExcelColumnName(dtExcelData.Columns.Count);

exclSheets.Range("A1", "A1").Style.Border.BottomBorder = Excel.XLBorderStyleValues.Thin;

if (File.Exists(Server.MapPath(@"~/Output/") + fileName.Trim()))
{
File.Delete(Server.MapPath(@"~/Output/") + fileName.Trim());
}
exclBook.SaveAs(Server.MapPath(@"~/Output/") + fileName.Trim());
exclBook = null;
exclSheets = null;
string path = Server.MapPath(@"~/Output/") + fileName.Trim();
return path;

}

Answers (1)