.NET Core生成Excel内存飙升且无法释放的优化方案求助

问题根源 #

你的代码使用OpenXML的DOM（文档对象模型）模式操作Excel，每次往SheetData中追加行时，整个工作表的所有行都会被保留在内存中，随着记录数增加，内存占用会持续飙升。即使使用using和GC.Collect()，也因为DOM持有大量对象引用，无法有效释放内存。

具体优化方案 #

1. 切换到SAX流模式写入（最有效） #

SAX模式不需要把整个文档加载到内存，而是以流的方式逐行写入，内存占用会保持在很低的水平。针对你的场景，直接写入工作表的XML内容，替代DOM对象操作：

public UAOperationResult<string> AppendRecordsToExcel<T>(string filePath, List<T> records, List<string> columns = null)
{
    try
    {
        if (string.IsNullOrEmpty(filePath))
        {
            return new UAOperationResult<string>
            {
                Content = "文件路径为空",
                Success = false,
                Exceptions = new Exception("文件路径为空")
            };
        }

        // 快速获取当前工作表的最后行号，避免加载所有行到内存
        uint startRow = GetLastRowNumber(filePath) + 1;

        // 用SAX模式追加数据
        using (var fs = new FileStream(filePath, FileMode.Open, FileAccess.Write))
        using (var spreadsheetDocument = SpreadsheetDocument.Open(fs, true))
        {
            WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
            WorksheetPart worksheetPart = workbookPart.WorksheetParts.FirstOrDefault();
            
            // 获取工作表的XML写入流
            using (var writer = XmlWriter.Create(worksheetPart.GetStream(FileMode.Append, FileAccess.Write), new XmlWriterSettings { Indent = false, OmitXmlDeclaration = true }))
            {
                PropertyInfo[] properties = typeof(T).GetProperties();
                // 提前处理列映射，避免循环中重复查找
                var targetProperties = columns?.Select(col => properties.FirstOrDefault(p => p.Name == col)).Where(p => p != null).ToList() 
                                      ?? properties.ToList();

                for (uint rowNum = startRow; rowNum < startRow + records.Count; rowNum++)
                {
                    var record = records[(int)(rowNum - startRow)];
                    writer.WriteStartElement("row", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
                    writer.WriteAttributeString("r", rowNum.ToString());

                    for (int colIndex = 0; colIndex < targetProperties.Count; colIndex++)
                    {
                        var prop = targetProperties[colIndex];
                        string cellValue = prop.GetValue(record)?.ToString() ?? string.Empty;
                        string cellReference = GetCellReference(colIndex, rowNum); // 生成正确的单元格引用（如A1、B1）

                        writer.WriteStartElement("c", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
                        writer.WriteAttributeString("r", cellReference);
                        writer.WriteAttributeString("t", "str"); // 标记为字符串类型

                        writer.WriteStartElement("v", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
                        writer.WriteString(cellValue);
                        writer.WriteEndElement(); // 闭合<v>标签

                        writer.WriteEndElement(); // 闭合<c>标签
                    }

                    writer.WriteEndElement(); // 闭合<row>标签
                }
            }

            // 保存工作表和工作簿
            worksheetPart.Worksheet.Save();
            workbookPart.Workbook.Save();
        }

        return new UAOperationResult<string>
        {
            Content = $"文件已成功追加数据：{filePath}",
            Success = true,
            Exceptions = null
        };
    }
    catch (Exception ex)
    {
        return new UAOperationResult<string>
        {
            Content = ex.Message,
            Success = false,
            Exceptions = ex
        };
    }
}

// 辅助方法：快速获取最后行号，不加载所有行
private uint GetLastRowNumber(string filePath)
{
    using (var spreadsheetDocument = SpreadsheetDocument.Open(filePath, false))
    {
        WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
        WorksheetPart worksheetPart = workbookPart.WorksheetParts.FirstOrDefault();
        
        var xmlDoc = new XmlDocument();
        xmlDoc.Load(worksheetPart.GetStream());
        XmlNamespaceManager nsManager = new XmlNamespaceManager(xmlDoc.NameTable);
        nsManager.AddNamespace("ss", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
        
        var rowNodes = xmlDoc.SelectNodes("//ss:row/@r", nsManager);
        if (rowNodes == null || rowNodes.Count == 0)
            return 0;
            
        uint maxRow = 0;
        foreach (XmlNode node in rowNodes)
        {
            if (uint.TryParse(node.Value, out uint rowNum) && rowNum > maxRow)
                maxRow = rowNum;
        }
        return maxRow;
    }
}

// 辅助方法：根据列索引和行号生成正确的单元格引用
private string GetCellReference(int columnIndex, uint rowNum)
{
    string columnName = string.Empty;
    int temp = columnIndex;
    while (temp >= 0)
    {
        int remainder = temp % 26;
        columnName = Convert.ToChar(65 + remainder) + columnName;
        temp = (temp / 26) - 1;
    }
    return $"{columnName}{rowNum}";
}

2. 原DOM模式的内存优化（若不想切换SAX） #

如果坚持使用DOM模式，需要分批处理并重置文档引用，避免内存累积：

• 每次处理1000条记录后，关闭SpreadsheetDocument，重新打开文档继续追加下一批数据
• 替换sheetData.Elements<Row>().Count()的行号获取方式，改用XPath快速查找，避免加载所有行到内存

3. 细节优化 #

• 提前缓存PropertyInfo映射，避免循环中重复调用FirstOrDefault
• 修复CellReference的错误格式：原代码中num + column会生成"1A"这类无效引用，应改为列名+行号（如"A1"）
• 移除循环中不必要的数组复制操作（如原代码里的PropertyInfo[] array = properties;）

效果验证 #

切换到SAX模式后，生成10万条记录的内存占用会控制在几百MB以内，不会出现持续飙升的情况。即使分批处理，内存也能及时释放。

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