如何不一次性加载全量数据生成大型SpreadsheetDocument Excel文件

可以实现，你当前的代码是基于OpenXML SDK的DOM操作模式，会将整个工作表的结构全部加载到内存中，数据量大时会占用很高内存。改用OpenXmlWriter 流式写入的方式即可实现分批写入，全程只需要保留当前批次的行数据，完全不需要把所有行加载到内存。

实现思路 #

OpenXmlWriter可以直接向Excel文件的流写入元素，不需要在内存中构建完整的DOM树，配合延迟加载的数据源，就能做到逐批写入，内存占用不会随数据量增长大幅升高。

改造后的代码示例 #

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Collections.Generic;
using System.Linq;

public void StreamExport<TRow>(string fileName, IEnumerable<TRow> data, ColumnDef<TRow>[] columnDefinitions)
{
    const int batchSize = 1000;
    using (var document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
    {
        // 初始化文档基础结构
        var workbookPart = document.AddWorkbookPart();
        workbookPart.Workbook = new Workbook();
        AddStyles(workbookPart);

        var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
        // 用OpenXmlWriter直接写入工作表流，不需要构建内存DOM
        using (var writer = OpenXmlWriter.Create(worksheetPart))
        {
            writer.WriteStartElement(new Worksheet());
            
            // 写入列定义，自动列宽可提前算好各列宽度再填，不需要遍历所有行数据
            writer.WriteStartElement(new Columns());
            for (uint i = 0; i < columnDefinitions.Length; i++)
            {
                // 这里可替换为你提前计算好的列宽
                writer.WriteElement(new Column 
                { 
                    Min = i + 1, 
                    Max = i + 1, 
                    Width = 20, 
                    CustomWidth = true 
                });
            }
            writer.WriteEndElement(); // 闭合Columns标签

            writer.WriteStartElement(new SheetData());
            
            // 分批处理数据
            var currentBatch = new List<TRow>(batchSize);
            foreach (var rowItem in data)
            {
                currentBatch.Add(rowItem);
                if (currentBatch.Count >= batchSize)
                {
                    WriteBatchRows(currentBatch, columnDefinitions, writer);
                    currentBatch.Clear();
                }
            }
            // 处理剩余不足1000行的数据
            if (currentBatch.Any())
            {
                WriteBatchRows(currentBatch, columnDefinitions, writer);
            }

            writer.WriteEndElement(); // 闭合SheetData标签
            writer.WriteEndElement(); // 闭合Worksheet标签
        }

        // 补全工作表引用
        var sheets = workbookPart.Workbook.AppendChild(new Sheets());
        sheets.Append(new Sheet
        {
            Id = workbookPart.GetIdOfPart(worksheetPart),
            SheetId = 1,
            Name = "Sheet1"
        });
        workbookPart.Workbook.Save();
    }
}

/// <summary>
/// 写入单批次行数据
/// </summary>
private void WriteBatchRows<TRow>(List<TRow> batch, ColumnDef<TRow>[] columnDefs, OpenXmlWriter writer)
{
    foreach (var rowData in batch)
    {
        var row = new Row();
        foreach (var colDef in columnDefs)
        {
            // 根据你自己的ColumnDef逻辑取值、设置单元格类型和样式
            var cellValue = colDef.GetValue(rowData)?.ToString() ?? string.Empty;
            row.AppendChild(new Cell
            {
                CellValue = new CellValue(cellValue),
                DataType = CellValues.String
            });
        }
        writer.WriteElement(row);
    }
}

注意事项 #

• 数据源如果是从数据库等地方读取，建议实现为延迟迭代的IEnumerable，比如每批次查询1000行返回，不要一次性把所有数据查出来放到内存集合里，才能最大化降低内存占用。
• 原来的自动列宽逻辑需要调整：如果需要自动列宽，可以单独遍历一次所有数据，仅记录每列的最大宽度（不需要存储整行内容，内存占用极低），再把计算得到的宽度写到Columns配置中即可。
• 该方案处理十万甚至百万行数据时，内存占用基本稳定在几十MB级别。

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