Excel文件导出 - outofmemoryexception [英] Excel file export - outofmemoryexception

问题描述

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;

}

推荐答案

代码中有几个地方可以让你出去内存异常但我可以告诉您正在使用Excel的Office对象界面，您不应该在Web环境中使用它。您应该查看OpenXML SDK或EPPlus。

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.

这篇关于Excel文件导出 - outofmemoryexception的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！