C# 使用MemoryStream生成Excel文件提示损坏该如何解决？

• 问题1：内存流读取时机错误
  你当前代码中mem.ToArray()与File.WriteAllBytes()的执行位置在SpreadsheetDocument的using块内部，此时OpenXML SDK还未完成文档结构的最终补全、流刷写操作，得到的字节数组不完整，是导致文件损坏的核心原因。需要将这两句代码移动到SpreadsheetDocument的using块外部，MemoryStream的using块内部。
• 问题2：Worksheet未显式保存
  代码中仅对Workbook执行了Save操作，填充了列配置、行数据的Worksheet没有执行保存，需要在所有SheetData内容填充完成后，添加worksheetPart.Worksheet.Save()。
• 问题3：样式表结构不完整（可选校验项）
  若上述两个问题修复后依旧报错，需要校验GenerateStyleSheet()方法返回的Stylesheet结构是否符合规范：OpenXML要求样式表必须按顺序包含NumberingFormats、Fonts、Fills、Borders、CellStyleFormats、CellFormats节点，即使没有自定义配置也要声明空的对应节点，缺失节点会直接导致文件损坏。

修改后可正常运行的代码示例 #

using (MemoryStream mem = new MemoryStream())
{
    using (var sheetDocument = SpreadsheetDocument.Create(mem, SpreadsheetDocumentType.Workbook))
    {
        //// Add a WorkbookPart to the document.
        WorkbookPart workbookpart = sheetDocument.AddWorkbookPart();
        workbookpart.Workbook = new Workbook();

        WorkbookStylesPart workbookstyle = workbookpart.AddNewPart<WorkbookStylesPart>();

        workbookstyle.Stylesheet = this.GenerateStyleSheet();
        workbookstyle.Stylesheet.Save();

        //// Add a WorksheetPart to the WorkbookPart.
        WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
        worksheetPart.Worksheet = new Worksheet();

        // 可选添加SheetView，避免打开异常
        worksheetPart.Worksheet.AppendChild(new SheetViews(new SheetView() { TabSelected = true, WorkbookViewId = 0 }));

        Columns cols = new Columns();
        Column col = new Column();
        col.BestFit = true;
        col.CustomWidth = true;
        col.Width = 22.33;
        col.Max = 13;
        col.Min = 1;
        cols.Append(col);

        worksheetPart.Worksheet.Append(cols);

        SheetData sheetData = new SheetData();
        worksheetPart.Worksheet.Append(sheetData);

        //// Add Sheets to the Workbook.
        Sheets sheets = sheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

        //// Append a new worksheet and associate it with the workbook.
        Sheet sheet = new Sheet() { Id = sheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = sheetName };
        sheets.Append(sheet);

        workbookpart.Workbook.Save();

        List<string> columns = new List<string>();

        foreach (DataColumn column in table.Columns)
        {
            columns.Add(column.ColumnName);
        }

        DocumentFormat.OpenXml.Spreadsheet.Row headerRow = this.CreateHeaderRow(columns);
        sheetData.AppendChild(headerRow);

        foreach (DataRow dataSetRow in table.Rows)
        {
            DocumentFormat.OpenXml.Spreadsheet.Row newRow = this.CreateDataRow(dataSetRow);
            sheetData.AppendChild(newRow);
        }

        // 新增：保存修改后的Worksheet
        worksheetPart.Worksheet.Save();
        workbookpart.Workbook.Save();
    }
    // 移动到此处：SpreadsheetDocument已完成释放，流内容完整
    var result = mem.ToArray();
    System.IO.File.WriteAllBytes(fileName, result);
}

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