Click here to Skip to main content
15,891,033 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Getting Out of memory exception while exporting large amount of Data (more than 90,000 records , Columns 80) to excel 


What I have tried:

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;

}
Posted
Updated 13-Apr-17 4:26am
Comments
AnvilRanger 13-Apr-17 10:23am    
Which line does the error occur on? There are several places in your code where this can happen?

1 solution

There are several place in your code where you can get an Out of Memory exception. But I can tell you are using the Office Object interface for Excel and you should not be using this in a web environment. You should look at the OpenXML SDK or EPPlus.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900