使用Open XML导出带批注Excel失败，求代码问题排查及解决方案

问题描述 #

我们尝试使用Open XML导出带有批注的Excel文件，编写了如下代码片段。代码中Sheet对象已包含批注，但导出为Excel文件并打开后，单元格并未显示批注。需排查代码问题，并实现指定单元格显示批注的功能。

原代码 #

public byte[] ExportJsonDatatoExcelWithComments()
{

    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());
    //OpenSheet
    WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
    SheetData sheetData = new SheetData();
    worksheetPart.Worksheet = new Worksheet(sheetData);

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

    OpenXmlWriter DataWriter = OpenXmlWriter.Create(worksheetPart);
    DataWriter.WriteStartElement(sheetData);
    DataWriter.WriteStartElement(worksheetPart.Worksheet);
    
    //closedata
    DataWriter.WriteEndElement();
    DataWriter.Close();
    //
    //render
    // worksheetPart = null;
    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" } };


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

    authors1.Append(author1);

    CommentList commentList1 = new CommentList();

    Comment comment1 = new Comment() { Reference = "A3", AuthorId = (UInt32Value)12, ShapeId = (UInt32Value)10, 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!\n";

    run1.Append(runProperties1);
    run1.Append(text1);

    commentText1.Append(run1);

    comment1.Append(commentText1);

    commentList1.Append(comment1);

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

问题排查 #

代码存在三个核心问题：

• AuthorId不匹配：Authors集合仅添加了1个作者（索引从0开始），但Comment的AuthorId设为12，Excel无法匹配到对应作者，导致批注失效。
• OpenXmlWriter覆盖内容：使用OpenXmlWriter重新写入sheetData和Worksheet时，会覆盖之前配置好的Worksheet对象，清除已建立的批注关联。
• 缺少VML绘图部件：Excel需要VML绘图来渲染单元格右上角的批注标记，代码未创建对应的VmlDrawingPart，这是批注不显示的关键原因。

修正后的代码 #

public byte[] ExportJsonDatatoExcelWithComments()
{
    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>();
    SheetData sheetData = new SheetData();
    Worksheet worksheet = new Worksheet(sheetData);
    worksheetPart.Worksheet = worksheet;

    // 添加工作表到工作簿
    var sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "test" };
    sheets.Append(sheet);

    // 添加批注部件并配置内容
    WorksheetCommentsPart worksheetCommentsPart = worksheetPart.AddNewPart<WorksheetCommentsPart>();
    GenerateWorksheetCommentsPartContent(worksheetCommentsPart);

    // 添加VML绘图部件，用于显示批注标记
    VmlDrawingPart vmlDrawingPart = worksheetPart.AddNewPart<VmlDrawingPart>();
    GenerateVmlDrawingPartContent(vmlDrawingPart, worksheetPart.GetIdOfPart(worksheetCommentsPart));

    // 保存并关闭文档
    workbookPart.Workbook.Save();
    spreadsheet.Save();
    spreadsheet.Close();
    memoryStream.Position = 0;
    return memoryStream.ToArray();
}

public void GenerateWorksheetCommentsPartContent(WorksheetCommentsPart worksheetCommentsPart)
{
    Comments comments = new Comments() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "xr" } };

    // 作者集合，索引从0开始
    Authors authors = new Authors();
    Author author = new Author();
    author.Text = "User1";
    authors.Append(author);

    CommentList commentList = new CommentList();
    // AuthorId对应Authors中的索引（0），ShapeId对应VML中的shape id
    Comment comment = new Comment() { Reference = "A3", AuthorId = (UInt32Value)0, ShapeId = (UInt32Value)0, Guid = Guid.NewGuid().ToString() };
    comment.SetAttribute(new OpenXmlAttribute("xr", "uid", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision", "{811649EF-4CB5-4311-BE14-228133003BE4}"));

    CommentText commentText = new CommentText();
    Run run = new Run();
    RunProperties runProperties = new RunProperties();
    runProperties.Append(new FontSize() { Val = 9D });
    runProperties.Append(new Color() { Indexed = (UInt32Value)81U });
    runProperties.Append(new RunFont() { Val = "Tahoma" });
    runProperties.Append(new RunPropertyCharSet() { Val = 1 });

    Text text = new Text() { Space = SpaceProcessingModeValues.Preserve };
    text.Text = "This is my comment!\nThis is line 2!\n";

    run.Append(runProperties);
    run.Append(text);
    commentText.Append(run);
    comment.Append(commentText);
    commentList.Append(comment);

    comments.Append(authors);
    comments.Append(commentList);
    worksheetCommentsPart.Comments = comments;
}

public void GenerateVmlDrawingPartContent(VmlDrawingPart vmlDrawingPart, string commentsPartId)
{
    using (StreamWriter writer = new StreamWriter(vmlDrawingPart.GetStream()))
    {
        writer.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\">");
        writer.Write("<o:shapelayout v:ext=\"edit\">");
        writer.Write("<o:idmap v:ext=\"edit\" data=\"1\"/>");
        writer.Write("</o:shapelayout>");
        writer.Write("<v:shapetype id=\"_x0000_t202\" coordsize=\"21600,21600\" o:spt=\"202\" path=\"m,l,21600r21600,l21600,xe\">");
        writer.Write("<v:stroke joinstyle=\"miter\"/>");
        writer.Write("<v:path gradientshapeok=\"t\" o:connecttype=\"rect\"/>");
        writer.Write("</v:shapetype>");
        // 对应A3单元格的批注标记，shape id要和Comment的ShapeId一致
        writer.Write($"<v:shape id=\"_x0000_s1025\" type=\"#_x0000_t202\" style=\"position:absolute;margin-left:54pt;margin-top:1.5pt;width:100pt;height:50pt;z-index:1;visibility:hidden\" fillcolor=\"#ffffe1\" o:insetmode=\"auto\">");
        writer.Write("<v:fill color2=\"#ffffe1\"/>");
        writer.Write("<v:shadow on=\"t\" color=\"black\" obscured=\"t\"/>");
        writer.Write("<v:path o:connecttype=\"none\"/>");
        writer.Write("<v:textbox style=\"mso-direction-alt:auto\">");
        writer.Write("<div style=\"text-align:left\"/>");
        writer.Write("</v:textbox>");
        writer.Write($"<x:ClientData ObjectType=\"Note\">");
        writer.Write("<x:MoveWithCells/>");
        writer.Write("<x:SizeWithCells/>");
        writer.Write("<x:Anchor>1, 15, 2, 15, 3, 15, 4, 15</x:Anchor>"); // 对应A3单元格的位置
        writer.Write($"<x:Comment>{commentsPartId}</x:Comment>");
        writer.Write("</x:ClientData>");
        writer.Write("</v:shape>");
        writer.Write("</xml>");
    }
}

关键修正说明 #

1. 匹配AuthorId：将Comment的AuthorId改为0，与Authors集合中的第一个作者（索引0）对应，确保Excel能识别批注作者。
2. 删除错误的OpenXmlWriter逻辑：避免覆盖已配置好的Worksheet和批注关联，保留原有的工作表结构。
3. 添加VmlDrawingPart：创建VML绘图内容，定义批注的显示位置和样式，让Excel能渲染出单元格右上角的批注标记，同时关联到对应的批注部件。

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