ASP.Net C#导出12万行Excel时出现OutOfMemoryException问题

你遇到的内存溢出问题，核心原因是原代码的实现方式不适合处理大数据量：

• 用GridView绑定12万行数据会生成大量控件实例，占用大量内存
• RenderControl将整个GridView渲染成HTML字符串并存在内存中，12万行的HTML体积远超内存承载阈值，最终在Response.Write时触发OutOfMemoryException

下面给出两种可行的解决方案：

方案1：直接流式写入HTML格式Excel（无需额外依赖） #

绕过GridView，直接循环DataTable的行，逐行写入Response，写完就Flush，避免内存堆积整个数据集的字符串。

public void ExportExcel(DataSet ds)
{
    var dt = ds.Tables[0];
    // 生成唯一文件名，避免重复
    string xlsFileName = "Report_" + DateTime.Now.ToString("yyyyMMddHHmmss");
    
    var response = HttpContext.Current.Response;
    response.Clear();
    response.Buffer = false;
    response.ContentType = "application/vnd.ms-excel";
    response.AppendHeader("Content-Disposition", $"attachment;filename={xlsFileName}.xls");
    
    // 写入Excel头部HTML结构
    response.Write(@"
<html xmlns:o=""urn:schemas-microsoft-com:office:office"" xmlns:x=""urn:schemas-microsoft-com:office:excel"" xmlns=""http://www.w3.org/TR/REC-html40"">
<head>
<meta http-equiv=Content-Type content=""text/html; charset=windows-1252"">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content=""Microsoft Excel 9"">
<!--[if gte mso 9]>
<xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>" + xlsFileName + @"</x:Name>
<x:WorksheetOptions>
<x:Selected/>
<x:ProtectContents>False</x:ProtectContents>
<x:ProtectObjects>False</x:ProtectObjects>
<x:ProtectScenarios>False</x:ProtectScenarios>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
<x:ProtectStructure>False</x:ProtectStructure>
<x:ProtectWindows>False</x:ProtectWindows>
</x:ExcelWorkbook>
</xml>
<![endif]-->
</head>
<body>
<table>");
    
    // 写入表头
    response.Write("<tr>");
    foreach (DataColumn col in dt.Columns)
    {
        // 转义HTML特殊字符，避免破坏格式
        response.Write($"<th>{HttpUtility.HtmlEncode(col.ColumnName)}</th>");
    }
    response.Write("</tr>");
    
    // 逐行写入数据，每1000行Flush一次释放内存
    int rowCount = 0;
    foreach (DataRow row in dt.Rows)
    {
        response.Write("<tr>");
        foreach (var item in row.ItemArray)
        {
            string value = item == DBNull.Value ? "" : HttpUtility.HtmlEncode(item.ToString());
            response.Write($"<td>{value}</td>");
        }
        response.Write("</tr>");
        
        rowCount++;
        if (rowCount % 1000 == 0)
        {
            response.Flush();
        }
    }
    
    // 写入尾部结构
    response.Write("</table></body></html>");
    
    response.Flush();
    HttpContext.Current.ApplicationInstance.CompleteRequest();
}

方案2：使用EPPlus流式导出xlsx（推荐，更专业） #

EPPlus是开源的.NET库，支持生成标准xlsx格式，并且支持流式写入，不需要把所有数据加载到内存，适合大数据量场景。

步骤： #

1. 安装EPPlus NuGet包：Install-Package EPPlus
2. 使用以下代码实现流式导出：

using OfficeOpenXml;

public void ExportExcel(DataSet ds)
{
    var dt = ds.Tables[0];
    string xlsFileName = "Report_" + DateTime.Now.ToString("yyyyMMddHHmmss");
    
    var response = HttpContext.Current.Response;
    response.Clear();
    response.Buffer = false;
    response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    response.AppendHeader("Content-Disposition", $"attachment;filename={xlsFileName}.xlsx");
    
    // 启用流式写入，避免内存堆积
    using (var package = new ExcelPackage())
    {
        var worksheet = package.Workbook.Worksheets.Add("Report");
        
        // 写入表头
        for (int colIndex = 1; colIndex <= dt.Columns.Count; colIndex++)
        {
            worksheet.Cells[1, colIndex].Value = dt.Columns[colIndex - 1].ColumnName;
        }
        
        int rowIndex = 2;
        foreach (DataRow row in dt.Rows)
        {
            for (int colIndex = 1; colIndex <= dt.Columns.Count; colIndex++)
            {
                worksheet.Cells[rowIndex, colIndex].Value = row[colIndex - 1] == DBNull.Value ? null : row[colIndex - 1];
            }
            rowIndex++;
            
            // 每1000行Flush一次到Response
            if (rowIndex % 1000 == 0)
            {
                package.SaveAs(response.OutputStream);
                response.Flush();
            }
        }
        
        // 保存剩余数据
        package.SaveAs(response.OutputStream);
    }
    
    response.Flush();
    HttpContext.Current.ApplicationInstance.CompleteRequest();
}

内容的提问来源于stack exchange，提问作者Nagendra Kumar