C#生成Excel时内存溢出如何解决？

Question

在使用C#生成大型Excel文件时，常因一次性将全部数据加载到内存导致内存溢出。典型场景是利用EPPlus或ClosedXML等库操作大数据集时，对象未及时释放或缓存机制不当，引发OutOfMemoryException。如何在保证性能的同时，实现高效、低内存占用的Excel生成？

Answer 1

1. 问题背景与常见误区

在使用C#生成大型Excel文件（如百万行数据）时，开发者常依赖EPPlus、ClosedXML等基于Open XML SDK的第三方库。这些库虽然封装良好、API简洁，但其默认行为是将整个工作簿加载到内存中进行操作。当数据量超过几十万行时，极易触发OutOfMemoryException。

• 典型误区：一次性读取数据库全部结果集并构建DataTable
• 错误做法：循环中频繁创建Cell对象而未及时释放引用
• 缓存陷阱：Excel库内部为提升性能启用单元格缓存，导致内存持续增长

例如以下代码片段会导致严重内存泄漏：

using (var package = new ExcelPackage())
{
    var worksheet = package.Workbook.Worksheets.Add("Data");
    for (int i = 0; i < 1_000_000; i++)
    {
        worksheet.Cells[i + 1, 1].Value = GetData(i); // 每个Cell被缓存
    }
    package.SaveAs(new FileInfo("large.xlsx"));
}

2. 内存溢出的根本原因分析

因素	说明	影响程度
全量数据加载	从数据库一次性取出所有记录至List或DataTable	高
对象生命周期管理	ExcelPackage、Worksheet等未正确Dispose	高
单元格缓存机制	EPPlus/ClosedXML对已访问Cell进行内存缓存	极高
字符串驻留	大量重复字符串未复用，增加GC压力	中
样式复制	每行设置独立样式导致Style对象爆炸式增长	高

尤其值得注意的是，EPPlus v4/v5中的ExcelRangeBase类会维护一个内部字典来追踪所有已访问的单元格，即使你只写一次值，该引用仍保留在内存中直至工作表销毁。

3. 解决方案层级演进

1. 初级优化：分页查询 + using语句确保资源释放
2. 中级优化：禁用缓存、批量写入Row/Range
3. 高级优化：采用流式写入（Streaming），避免中间对象驻留
4. 终极方案：切换至底层Open XML SDK直接写入，实现零内存驻留

以EPPlus为例，可通过配置关闭部分缓存特性：

ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var package = new ExcelPackage())
{
    // 关闭自动计算公式缓存
    package.Workbook.CalcMode = ExcelCalcMode.Manual;

    var ws = package.Workbook.Worksheets.Add("Streamed");
    
    // 启用大容量优化模式（v5+）
    ws.Cells.AutoFilter = false;

    int row = 1;
    foreach (var record in LargeDataSetEnumerator())
    {
        ws.SetValue(row, 1, record.Field1);
        ws.SetValue(row, 2, record.Field2);
        // ... 其他字段
        if (row % 10000 == 0) Console.WriteLine($"Processed {row} rows");
        row++;
    }

    package.SaveAs(new FileInfo("optimized.xlsx"));
}

4. 高效低内存架构设计

graph TD
      A[开始] --> B[打开数据源游标]
      B --> C{是否有下一批数据?}
      C -- 是 --> D[读取N条记录]
      D --> E[批量写入Excel Worksheet]
      E --> F[清理当前批次对象引用]
      F --> C
      C -- 否 --> G[保存并关闭文件]
      G --> H[结束]

关键设计原则包括：

• 使用IEnumerable<T>延迟加载数据
• 避免构建完整DataTable，改用逐行枚举器
• 重用样式对象，避免为每行创建新Style
• 定期调用GC.Collect()仅在关键节点（谨慎使用）
• 启用64位进程以突破32位内存限制

5. 替代技术栈推荐

对于超大规模导出（>100万行），建议转向更底层的技术：

// 使用SimpleOOXML或DocumentFormat.OpenXml直接写入
using (SpreadsheetDocument doc = SpreadsheetDocument.Create("huge.xlsx", SpreadsheetDocumentType.Workbook))
{
    WorkbookPart workbookPart = doc.AddWorkbookPart();
    workbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

    WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
    worksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet();

    SheetData sheetData = new SheetData();
    for (long i = 1; i <= 2_000_000; i++)
    {
        Row row = new Row() { RowIndex = (UInt32)i };
        Cell cell = CreateTextCell("A", (uint)i, $"Value{i}");
        row.Append(cell);
        sheetData.Append(row);

        if (i % 50000 == 0) Console.WriteLine($"Wrote {i} rows");
    }

    worksheetPart.Worksheet.Append(sheetData);
    workbookPart.Workbook.Append(new Sheets(new Sheet() { Id = doc.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Data" }));
    workbookPart.Workbook.Save();
}

此方式可将内存占用控制在100MB以内处理200万行数据，远优于高层封装库。