使用OpenXML生成Excel后打开报错，求问题排查建议

一、初始代码导致Excel文件损坏的原因 #

你一开始给worksheetPart.Worksheet赋值了new Worksheet()，随后用OpenXmlWriter写入这个已关联的Worksheet实例，这会造成重复写入Worksheet根节点的问题：

• 执行worksheetPart.Worksheet = new Worksheet();时，内存中已创建空的Worksheet结构并关联到worksheetPart；
• 调用DataWriter.WriteStartElement(worksheetPart.Worksheet);时，又将这个已存在的Worksheet节点写入到worksheetPart的数据流中，最终生成的Excel里Worksheet的XML结构出现重复根节点，导致Excel解析失败，弹出内容损坏提示。

二、修改后访问Worksheet属性抛IO异常的原因 #

改成DataWriter.WriteStartElement(new Worksheet());后文件能正常生成，但访问worksheetPart.Worksheet会触发System.IO异常，原因是：

• OpenXmlWriter是直接向worksheetPart的底层数据流写入内容，不会更新内存中worksheetPart.Worksheet的对象引用；
• 你之前赋值的worksheetPart.Worksheet = new Worksheet();并未被写入文件，反而被OpenXmlWriter写入的新Worksheet覆盖。后续访问该属性时，OpenXML SDK会尝试从底层数据流加载Worksheet对象，但此时数据流已关闭或内容与内存对象不匹配，因此抛出异常。

正确写法示例 #

方式1：统一使用OpenXmlWriter构建Worksheet #

public byte[] ExportDatatoExcel()
{
    using MemoryStream memoryStream = new MemoryStream();
    using SpreadsheetDocument spreadsheet = SpreadsheetDocument.Create(memoryStream, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);
    
    WorkbookPart workbookPart = spreadsheet.AddWorkbookPart();
    workbookPart.Workbook = new Workbook();
    Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());

    WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
    var sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "test" };
    sheets.Append(sheet);

    using OpenXmlWriter dataWriter = OpenXmlWriter.Create(worksheetPart);
    dataWriter.WriteStartElement(new Worksheet());
    dataWriter.WriteStartElement(new SheetData());
    dataWriter.WriteEndElement(); // 关闭SheetData节点
    dataWriter.WriteEndElement(); // 关闭Worksheet节点
    dataWriter.Close();

    workbookPart.Workbook.Save();
    spreadsheet.Save();

    memoryStream.Position = 0;
    return memoryStream.ToArray();
}

方式2：直接操作内存对象（无需OpenXmlWriter） #

public byte[] ExportDatatoExcel()
{
    using MemoryStream memoryStream = new MemoryStream();
    using SpreadsheetDocument spreadsheet = SpreadsheetDocument.Create(memoryStream, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);
    
    WorkbookPart workbookPart = spreadsheet.AddWorkbookPart();
    workbookPart.Workbook = new Workbook();
    Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());

    WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
    // 直接创建包含SheetData的Worksheet
    worksheetPart.Worksheet = new Worksheet(new SheetData());
    var sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "test" };
    sheets.Append(sheet);

    workbookPart.Workbook.Save();
    spreadsheet.Save();

    memoryStream.Position = 0;
    return memoryStream.ToArray();
}

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