使用OpenXML SAX处理大Excel文件时实现自动列宽的优化方案问询

一、优化现有内存占用过高的问题：避免加载完整工作表DOM #

你的代码内存暴涨的核心原因是wp.Worksheet.Descendants<SheetData>().First()会触发整个工作表DOM的加载（包含100万行数据）。改用OpenXmlReader + OpenXmlWriter的流式操作，仅定位到需要插入节点的位置，无需加载全部内容：

private void AutofitLowMemory(string _filename)
{
    using (SpreadsheetDocument excel = SpreadsheetDocument.Open(_filename, true))
    {
        foreach (var worksheetPart in excel.WorkbookPart.WorksheetParts)
        {
            // 创建临时内存流保存修改后的工作表内容
            using (var tempStream = new MemoryStream())
            {
                using (OpenXmlReader reader = OpenXmlReader.Create(worksheetPart))
                using (OpenXmlWriter writer = OpenXmlWriter.Create(tempStream))
                {
                    bool insertedColumns = false;
                    while (reader.Read())
                    {
                        if (reader.ElementType == typeof(SheetData))
                        {
                            // 先写入SheetData起始节点
                            writer.WriteStartElement(reader);
                            writer.WriteAttributes(reader);
                            
                            // 构造并插入Columns节点
                            var cs = new Columns();
                            uint colIndex = 1;
                            foreach (var longestString in MaxText)
                            {
                                double cellWidth = CalculateWidth(new System.Drawing.Font("Arial", 10), longestString) + 1.5;
                                Column c = new Column { Min = colIndex, Max = colIndex, Width = cellWidth, CustomWidth = true };
                                cs.Append(c);
                                colIndex++;
                            }
                            writer.WriteElement(cs);

                            // 流式写入SheetData的子节点
                            while (reader.Read())
                            {
                                if (reader.IsEndElement && reader.ElementType == typeof(SheetData))
                                {
                                    writer.WriteEndElement();
                                    break;
                                }
                                if (reader.IsStartElement)
                                {
                                    writer.WriteStartElement(reader);
                                    writer.WriteAttributes(reader);
                                    if (!reader.IsEmptyElement) continue;
                                }
                                else if (reader.IsEndElement)
                                {
                                    writer.WriteEndElement();
                                }
                                writer.WriteString(reader.Value);
                            }
                            insertedColumns = true;
                        }
                        else
                        {
                            // 流式处理其他节点
                            if (reader.IsStartElement)
                            {
                                writer.WriteStartElement(reader);
                                writer.WriteAttributes(reader);
                                if (!reader.IsEmptyElement) continue;
                            }
                            else if (reader.IsEndElement)
                            {
                                writer.WriteEndElement();
                            }
                            writer.WriteString(reader.Value);
                        }
                    }
                }
                // 将临时流内容写回工作表部件
                tempStream.Position = 0;
                worksheetPart.WorksheetStream.Position = 0;
                tempStream.CopyTo(worksheetPart.WorksheetStream);
                worksheetPart.WorksheetStream.SetLength(tempStream.Length);
            }
        }
    }
}

该方法通过流式读写工作表内容，仅在遇到SheetData节点时插入Columns，全程不会加载完整的工作表数据到内存，内存占用会大幅降低。

二、避免两次遍历DataReader：流式写入时同步记录列最长值 #

如果你之前通过两次遍历DataReader（一次统计长度、一次写入数据）实现自动列宽，可以改成一次遍历同时完成统计和写入，既节省时间又避免重复查询：

private void WriteExcelWithAutofit(string outputPath, DbDataReader reader)
{
    // 初始化每列最长字符串：先以列名作为初始值
    List<string> maxColumnValues = new List<string>(reader.FieldCount);
    for (int i = 0; i < reader.FieldCount; i++)
    {
        maxColumnValues.Add(reader.GetName(i));
    }

    using (SpreadsheetDocument excel = SpreadsheetDocument.Create(outputPath, SpreadsheetDocumentType.Workbook))
    {
        // 创建基础部件
        WorkbookPart workbookPart = excel.AddWorkbookPart();
        workbookPart.Workbook = new Workbook();
        WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();

        // SAX流式写入数据，同步更新每列最长字符串
        using (OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart))
        {
            writer.WriteStartElement(new Worksheet());
            writer.WriteStartElement(new SheetData());

            // 写入表头
            writer.WriteStartElement(new Row());
            for (int i = 0; i < reader.FieldCount; i++)
            {
                writer.WriteElement(new Cell
                {
                    CellValue = new CellValue(maxColumnValues[i]),
                    DataType = CellValues.String
                });
            }
            writer.WriteEndElement();

            // 写入数据行并更新最长值
            while (reader.Read())
            {
                writer.WriteStartElement(new Row());
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    string cellValue = reader.IsDBNull(i) ? "" : reader.GetString(i);
                    // 更新当前列的最长字符串
                    if (cellValue.Length > maxColumnValues[i].Length)
                    {
                        maxColumnValues[i] = cellValue;
                    }
                    writer.WriteElement(new Cell
                    {
                        CellValue = new CellValue(cellValue),
                        DataType = CellValues.String
                    });
                }
                writer.WriteEndElement();
            }

            writer.WriteEndElement(); // 结束SheetData
            writer.WriteEndElement(); // 结束Worksheet
        }

        // 复用低内存Autofit方法
        MaxText = maxColumnValues;
        AutofitLowMemory(outputPath);
    }
}

如果你的CalculateWidth方法需要考虑字符宽度差异（比如中文比英文宽），必须记录实际最长字符串；若仅需长度，可改为记录列最长长度以进一步节省内存。

三、关于XSLT或XStreamingElement的可行性 #

• XSLT：可用于修改工作表XML，但需要提取、转换、写回XML内容，复杂度高于直接使用OpenXmlReader/Writer，无明显优势。
• XStreamingElement：适合生成XML，但修改现有XML仍需结合流式读取，和第一个方案思路一致，没有额外收益。

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