C# .Net MVC使用OpenXml MemoryStream下载Excel文件报错问题

使用MemoryStream + OpenXml 实现Excel文件下载功能时，生成的文件无法正常打开，出现两类报错：

• 部件修复提示：已修复部件: /xl/worksheets/sheet1.xml 部件存在XML错误，加载错误，位置为第1行，第0列。
• 文件损坏提示：我们发现文件中的部分内容存在问题，是否让我们尽可能尝试恢复内容？
  相关报错参考：
• 报错截图1
• 报错截图2

List<string> model = new List<string>();
model.Add("t1");
#region Create Excel
MemoryStream ms = new MemoryStream();

#region Create Document
SpreadsheetDocument doc = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook, false);
WorkbookPart workbookPart = doc.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
#endregion Create Document

#region Create Columns
Columns workSheetColumns = new Columns();
for (int i = 0; i < 1; i++)
{
    Column col = new Column();
    workSheetColumns.Append(col);
}
worksheetPart.Worksheet.AppendChild(workSheetColumns);
workbookPart.Workbook.Save();
#endregion Create Columns

#region Arrange Sheet Data
worksheetPart.Worksheet.Append(new SheetData());
Sheets sheets = doc.WorkbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet() { Id = doc.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Report" };
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = (SheetData)worksheetPart.Worksheet.GetFirstChild<SheetData>();
#endregion Arrange Sheet Data

#region Arrange Headers
Row rw = new Row();
sheetData.Append(rw);
rw.RowIndex = UInt32Value.FromUInt32((UInt32)sheetData.Elements().Count());
{
    Cell c = new Cell();
    c.DataType = CellValues.String;
    c.CellValue = new CellValue("Test");
    c.CellReference = "A" + rw.RowIndex.ToString();
    rw.Append(c);
}

#endregion Arrange Headers

#region Add Rows
foreach (var modelItem in model)
{
    rw = new Row();
    sheetData.Append(rw);
    rw.RowIndex = UInt32Value.FromUInt32((UInt32)sheetData.ChildElements.Count);

    {
        Cell c = new Cell();
        c.DataType = CellValues.String;
        c.CellValue = new CellValue(modelItem);
        c.CellReference = "A" + rw.RowIndex.ToString();
        rw.Append(c);
    }

}
#endregion Add Rows

#region Finalize
worksheetPart.Worksheet.Save();
workbookPart.Workbook.Save();
doc.Close();
ms.Seek(0, SeekOrigin.Begin);
#endregion Finalize
#endregion Create Excel

Response.Clear();
Response.ContentType = @"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml";
Response.AddHeader("content-disposition", "attachment;filename=Report.xlsx");
Response.BinaryWrite(ms.ToArray());
Response.Flush();
Response.Close();
Response.End();

代码存在3个核心问题，直接导致生成的sheet1.xml不符合OpenXml规范：

1. Column元素缺少必填属性：创建的Column对象没有设置任何属性，生成的XML为空标签<column/>，不符合ECMA 376标准——Column必须至少指定Min、Max属性标识配置生效的列范围，否则Excel无法解析该XML节点。
2. 提前保存未完成构建的部件：在Columns、SheetData等子元素未完全追加完成时多次调用Save()方法，可能写入不完整的XML片段。
3. 缺少基础样式部件：默认生成的Workbook没有关联StylesPart，部分版本Excel会直接判定文件结构不完整触发修复提示。

1. 不需要自定义列宽的话，直接删除整个Columns创建逻辑即可；如果需要自定义列宽，必须给Column补全必填属性。
2. 移除构建过程中多余的Save()调用，所有元素追加完成后再统一保存。
3. 给Workbook添加基础的StylesPart，兼容所有Excel版本。

修复后的可运行代码：

List<string> model = new List<string>();
model.Add("t1");
MemoryStream ms = new MemoryStream();

// 创建文档
using (SpreadsheetDocument doc = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook, false))
{
    WorkbookPart workbookPart = doc.AddWorkbookPart();
    workbookPart.Workbook = new Workbook();
    
    // 添加基础样式部件，避免兼容问题
    WorkbookStylesPart stylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
    stylesPart.Stylesheet = new Stylesheet(
        new Fonts(new Font()),
        new Fills(new Fill(new PatternFill() { PatternType = PatternValues.None })),
        new Borders(new Border()),
        new CellFormats(new CellFormat())
    );

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

    // 如需配置列宽则正确初始化Column，不需要可删除此段
    Columns workSheetColumns = new Columns();
    Column col = new Column()
    {
        Min = 1,
        Max = 1,
        Width = 20,
        CustomWidth = true
    };
    workSheetColumns.Append(col);
    worksheetPart.Worksheet.Append(workSheetColumns);

    // 构建SheetData
    SheetData sheetData = new SheetData();
    worksheetPart.Worksheet.Append(sheetData);

    // 绑定工作表到工作簿
    Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
    Sheet sheet = new Sheet() 
    { 
        Id = doc.WorkbookPart.GetIdOfPart(worksheetPart), 
        SheetId = 1, 
        Name = "Report" 
    };
    sheets.Append(sheet);

    // 写入表头
    uint rowIndex = 1;
    Row headerRow = new Row() { RowIndex = rowIndex };
    sheetData.Append(headerRow);
    Cell headerCell = new Cell()
    {
        DataType = CellValues.String,
        CellValue = new CellValue("Test"),
        CellReference = "A" + rowIndex
    };
    headerRow.Append(headerCell);

    // 写入数据行
    foreach (var modelItem in model)
    {
        rowIndex++;
        Row dataRow = new Row() { RowIndex = rowIndex };
        sheetData.Append(dataRow);
        Cell dataCell = new Cell()
        {
            DataType = CellValues.String,
            CellValue = new CellValue(modelItem),
            CellReference = "A" + rowIndex
        };
        dataRow.Append(dataCell);
    }

    // 统一保存所有部件
    worksheetPart.Worksheet.Save();
    workbookPart.Workbook.Save();
}

ms.Seek(0, SeekOrigin.Begin);

// 输出文件流实现下载
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=Report.xlsx");
Response.BinaryWrite(ms.ToArray());
Response.Flush();
Response.Close();
Response.End();

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