使用C# OpenXml库导出Excel时添加单元格批注失败排查

以下是你的代码中导致批注无法显示的核心问题及修复方案：

问题1：AuthorId与Authors列表索引不匹配 #

你的Authors集合里只添加了1个作者（索引为0），但Comment的AuthorId设置为12，Excel无法找到对应作者，会直接忽略该批注。

修复：将AuthorId改为0（对应第一个作者）：

Comment comment1 = new Comment() { Reference = "A3", AuthorId = (UInt32Value)0U, ShapeId = (UInt32Value)10U, Guid = Guid.NewGuid().ToString() };

问题2：缺少VML绘图部件（VmlDrawingPart） #

Excel的批注需要通过VML（矢量标记语言）定义显示的形状和位置，仅添加WorksheetCommentsPart不足以让批注显示，必须关联VmlDrawingPart并写入对应的VML内容。

修复：添加VmlDrawingPart并生成VML内容，示例代码如下：

// 在添加WorksheetCommentsPart后添加以下代码
VmlDrawingPart vmlDrawingPart = worksheetPart.AddNewPart<VmlDrawingPart>();
using (StreamWriter sw = new StreamWriter(vmlDrawingPart.GetStream()))
{
    sw.Write(@"
<xml xmlns:v=""urn:schemas-microsoft-com:vml"" xmlns:o=""urn:schemas-microsoft-com:office:office"" xmlns:x=""urn:schemas-microsoft-com:office:excel"">
    <o:shapelayout v:ext=""edit"">
        <o:idmap v:ext=""edit"" data=""1"" />
    </o:shapelayout>
    <v:shapetype id=""_x0000_t202"" coordsize=""21600,21600"" o:spt=""202"" path=""m,l,21600r21600,l21600,xe"">
        <v:stroke joinstyle=""miter"" />
        <v:path gradientshapeok=""t"" o:connecttype=""rect"" />
    </v:shapetype>
    <v:shape id=""_x0000_s1025"" type=""#_x0000_t202"" style=""position:absolute;margin-left:54pt;margin-top:1.5pt;width:108pt;height:54pt;z-index:1;visibility:hidden"" fillcolor=""#ffffe1"" o:insetmode=""auto"">
        <v:fill color2=""#ffffe1"" />
        <v:shadow on=""t"" color=""black"" obscured=""t"" />
        <v:path o:connecttype=""none"" />
        <v:textbox style=""mso-direction-alt:auto"">
            <div style=""text-align:left""/>
        </v:textbox>
        <x:ClientData ObjectType=""Note"">
            <x:MoveWithCells />
            <x:SizeWithCells />
            <x:Anchor>1, 0, 2, 0, 3, 0, 5, 0</x:Anchor>
            <x:AutoFill>False</x:AutoFill>
            <x:Row>2</x:Row>
            <x:Column>0</x:Column>
        </x:ClientData>
    </v:shape>
</xml>");
}
// 关联VmlDrawingPart到Worksheet
worksheetPart.Worksheet.Append(new LegacyDrawing() { Id = worksheetPart.GetIdOfPart(vmlDrawingPart) });

说明：VML中的x:Row和x:Column对应A3单元格（行索引2，列索引0），ShapeId要和Comment里的ShapeId一致（这里是10）。

问题3：OpenXmlWriter使用逻辑错误 #

你初始化Worksheet时已经创建了SheetData，之后又用OpenXmlWriter重新写入SheetData，导致文档结构混乱。而且添加批注部件的时机应该在Writer完成写入后，避免破坏文档流。

修复：调整代码顺序，先完成Writer的写入，再添加批注和VML部件：

// 原代码中先处理Writer
DataWriter.WriteStartElement(worksheetPart.Worksheet);
DataWriter.WriteStartElement(new SheetData());
// 这里可以写入单元格数据，比如创建A3单元格
DataWriter.WriteStartElement(new Row() { RowIndex = 3 });
DataWriter.WriteStartElement(new Cell() { CellReference = "A3", DataType = CellValues.String });
DataWriter.WriteElement(new CellValue("A3内容"));
DataWriter.WriteEndElement(); // Cell
DataWriter.WriteEndElement(); // Row
DataWriter.WriteEndElement(); // SheetData
DataWriter.WriteEndElement(); // Worksheet
DataWriter.Close();

// 现在添加批注和VML部件
WorksheetCommentsPart worksheetCommentsPart1 = worksheetPart.AddNewPart<WorksheetCommentsPart>();
GenerateWorksheetCommentsPart1Content(worksheetCommentsPart1);

// 添加VmlDrawingPart（上面的代码）

问题4：未创建目标单元格 #

如果A3单元格不存在，部分Excel版本不会显示批注。确保在SheetData中创建A3单元格。

完整修正后的核心代码片段 #

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

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

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

    // 使用OpenXmlWriter写入SheetData和单元格
    using (OpenXmlWriter DataWriter = OpenXmlWriter.Create(worksheetPart))
    {
        DataWriter.WriteStartElement(worksheetPart.Worksheet);
        DataWriter.WriteStartElement(new SheetData());

        // 创建A3单元格
        DataWriter.WriteStartElement(new Row() { RowIndex = 3 });
        DataWriter.WriteStartElement(new Cell() { CellReference = "A3", DataType = CellValues.String });
        DataWriter.WriteElement(new CellValue("A3单元格内容"));
        DataWriter.WriteEndElement(); // Cell
        DataWriter.WriteEndElement(); // Row

        DataWriter.WriteEndElement(); // SheetData
        DataWriter.WriteEndElement(); // Worksheet
    }

    // 添加批注部件
    WorksheetCommentsPart worksheetCommentsPart1 = worksheetPart.AddNewPart<WorksheetCommentsPart>();
    GenerateWorksheetCommentsPart1Content(worksheetCommentsPart1);

    // 添加VML绘图部件
    VmlDrawingPart vmlDrawingPart = worksheetPart.AddNewPart<VmlDrawingPart>();
    using (StreamWriter sw = new StreamWriter(vmlDrawingPart.GetStream()))
    {
        sw.Write(@"
<xml xmlns:v=""urn:schemas-microsoft-com:vml"" xmlns:o=""urn:schemas-microsoft-com:office:office"" xmlns:x=""urn:schemas-microsoft-com:office:excel"">
    <o:shapelayout v:ext=""edit"">
        <o:idmap v:ext=""edit"" data=""1"" />
    </o:shapelayout>
    <v:shapetype id=""_x0000_t202"" coordsize=""21600,21600"" o:spt=""202"" path=""m,l,21600r21600,l21600,xe"">
        <v:stroke joinstyle=""miter"" />
        <v:path gradientshapeok=""t"" o:connecttype=""rect"" />
    </v:shapetype>
    <v:shape id=""_x0000_s1025"" type=""#_x0000_t202"" style=""position:absolute;margin-left:54pt;margin-top:1.5pt;width:108pt;height:54pt;z-index:1;visibility:hidden"" fillcolor=""#ffffe1"" o:insetmode=""auto"">
        <v:fill color2=""#ffffe1"" />
        <v:shadow on=""t"" color=""black"" obscured=""t"" />
        <v:path o:connecttype=""none"" />
        <v:textbox style=""mso-direction-alt:auto"">
            <div style=""text-align:left""/>
        </v:textbox>
        <x:ClientData ObjectType=""Note"">
            <x:MoveWithCells />
            <x:SizeWithCells />
            <x:Anchor>1, 0, 2, 0, 3, 0, 5, 0</x:Anchor>
            <x:AutoFill>False</x:AutoFill>
            <x:Row>2</x:Row>
            <x:Column>0</x:Column>
        </x:ClientData>
    </v:shape>
</xml>");
    }
    // 关联VML到Worksheet
    worksheetPart.Worksheet.Append(new LegacyDrawing() { Id = worksheetPart.GetIdOfPart(vmlDrawingPart) });

    workbookPart.Workbook.Save();
    spreadsheet.Save();
    spreadsheet.Close();
    memoryStream.Position = 0;
    return memoryStream.ToArray();
}

public void GenerateWorksheetCommentsPart1Content(WorksheetCommentsPart worksheetCommentsPart)
{
    Comments comments1 = new Comments() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "xr" } };
    comments1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
    comments1.AddNamespaceDeclaration("xr", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision");

    Authors authors1 = new Authors();
    Author author1 = new Author();
    author1.Text = "User1";
    authors1.Append(author1);

    CommentList commentList1 = new CommentList();
    // 修正AuthorId为0，ShapeId对应VML中的形状ID
    Comment comment1 = new Comment() { Reference = "A3", AuthorId = (UInt32Value)0U, ShapeId = (UInt32Value)10U, Guid = Guid.NewGuid().ToString() };
    comment1.SetAttribute(new OpenXmlAttribute("xr", "uid", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision", "{811649EF-4CB5-4311-BE14-228133003BE4}"));

    CommentText commentText1 = new CommentText();
    Run run1 = new Run();
    RunProperties runProperties1 = new RunProperties();
    FontSize fontSize3 = new FontSize() { Val = 9D };
    Color color3 = new Color() { Indexed = (UInt32Value)81U };
    RunFont runFont1 = new RunFont() { Val = "Tahoma" };
    RunPropertyCharSet runPropertyCharSet1 = new RunPropertyCharSet() { Val = 1 };

    runProperties1.Append(fontSize3);
    runProperties1.Append(color3);
    runProperties1.Append(runFont1);
    runProperties1.Append(runPropertyCharSet1);
    Text text1 = new Text() { Space = SpaceProcessingModeValues.Preserve };
    text1.Text = "This is my comment!\nThis is line 2!";

    run1.Append(runProperties1);
    run1.Append(text1);
    commentText1.Append(run1);
    comment1.Append(commentText1);
    commentList1.Append(comment1);

    comments1.Append(authors1);
    comments1.Append(commentList1);
    worksheetCommentsPart.Comments = comments1;
}

补充说明 #

• VML中的x:Anchor定义了批注的显示位置，格式为leftColumn, leftOffset, topRow, topOffset, rightColumn, rightOffset, bottomRow, bottomOffset，可以根据需要调整。
• 确保ShapeId在Comment和VML中保持一致，否则批注无法关联到正确的形状。

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