More ergonomic handling of comments

[m-gallesio]

We recently had to add XLSX comment functionality to our project. My starting example was the Threaded Comments sample which I finally managed to adapt, but it was a rather complex job for several reasons:

• I get the sample is for threaded comments, but I think a simpler sample for basic comments would be useful (we did not need threaded comments, nor a strong association with AD users for authors).
• The sample code could use more dedicated properties instead of the default OpenXmlElement constructor.
• The sample specifies several miscellaneous parts which do not seem to be needed for a base case. The only one which we ended up needing is the final VmlDrawingPart.
• I do not like the idea of having to provide a raw XML for said VML drawing. I have noticed there are actual classes for the various components, but I could not find a class for the VML root element.
• The whole comment API is quite annoying to use, with needing to specify separately both the comment data and the shape data (I do realize this is an issue with the format more than the SDK, though).
• Stemming ftom these last two points, the sample is hard-wired to add a comment to the A1 cell and only the A1 cell: adding comments in different cells requires more <x:shape /> elements with dedicated attribute values.

For now we have satisfied our needs, but I still think this could warrant some attention.

[twsouthwick]

@m-gallesio if you can share some pseudocode of what you'd like to see, that would be a great starting point.

[m-gallesio]

I am waiting for my manager to give me permission to share our code.

[m-gallesio]

I have received my manager's permission. Here is a simplified / anonymized version of our code:

Helper code

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Text;
using System.Xml;

namespace OurApplication.Reports;

// This is the main comment helper, based on this example:
// https://github.com/dotnet/Open-XML-SDK/blob/main/samples/ThreadedCommentExample/Program.cs
// I have tried to minimize the required XML markup with much trial and error.

internal sealed class XlsxCommentBuilder
{
    private readonly List<(int Row, int Column)> CommentShapeData = new();

    // Markup for a single comment shape
    private const string CommentXml = """
<v:shape id="_shape_{0}" type="#_x0000_t202" style="visibility:hidden">
<x:ClientData ObjectType="Note">
<x:MoveWithCells/>
<x:SizeWithCells/>
<x:Anchor> {3}, 15, {4}, 2, {5}, 30, {6}, 2</x:Anchor>
<x:AutoFill>False</x:AutoFill>
<x:Row>{1}</x:Row>
<x:Column>{2}</x:Column>
</x:ClientData>
</v:shape>
""";

    // This prepares a single comment
    public void Add(WorksheetCommentsPart wcp, (int Row, int Column) coordinates, string commentText)
    {
        wcp.Comments.CommentList!.AppendChild(new Comment
        {
            CommentText = new(new Text(commentText)),
            Reference = OurInternalHelpers.ConvertColumnIndexToLetters(coordinates.Column) + (coordinates.Row + 1),
            // These are all arbitrary. For our use case we do not need strong identification of commenters.
            AuthorId = 0,
            ShapeId = 0,
            Guid = $"{{{Guid.NewGuid().ToString().ToUpper(CultureInfo.InvariantCulture)}}}"
        });
        CommentShapeData.Add(coordinates);
    }

    // This is called at the end of the main rendering phase and actually renders the comments.
    public void Build(WorksheetPart wp)
    {
        if (CommentShapeData.Count <= 0)
            return;

        // This seems to be arbitrary
        const string rId = "rIdComment";

        // Both parts seem to be needed
        wp.Worksheet.AddChild(new LegacyDrawing { Id = rId });
        VmlDrawingPart wsd = wp.AddNewPart<VmlDrawingPart>(rId);

        // I could not find a class representing the ROOT of this part
        using (XmlTextWriter writer = new(wsd.GetStream(FileMode.Create), Encoding.UTF8))
        {
            // Again, much trial and error went into minimizing this.
            // This creates a rectangular box with a fixed size, which of course could be parametrized.
            writer.WriteRaw("""
<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" path="m,l,21600r21600,l21600,xe">
    <v:stroke joinstyle="miter"/>
    <v:path gradientshapeok="t" o:connecttype="rect"/>
  </v:shapetype>
""");
            for (int i = 0; i < CommentShapeData.Count; i++)
            {
                var (r, c) = CommentShapeData[i];
                // Again, the size for the text box could be parametrized.
                writer.WriteRaw(string.Format(CommentXml, i, r, c, c + 1, r, c + 4, r + 4));
            }
            writer.WriteRaw("</xml>");
            writer.Flush();
        }
    }
}

Consumer

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;

namespace OurApplication.Reports;

// This is a simplified example of how we use the comment helper.

internal abstract class BaseSheetBuilder<TItem>
{
    private XlsxCommentBuilder _commentBuilder;
    protected XlsxCommentBuilder CommentBuilder => _commentBuilder ??= new();

    // This stores a single comment
    protected void CreateComment(WorksheetPart wp, TItem item, string comment, int columnNumber)
    {
        if (!string.IsNullOrWhiteSpace(comment))
        {
            if (wp.WorksheetCommentsPart == null)
            {
                var wcp = wp.AddNewPart<WorksheetCommentsPart>();
                wcp.Comments = new()
                {
                    // Again, our case does not need strong author tracking.
                    Authors = new(new Author("Our application")),
                    CommentList = new(),
                };
            }
            CommentBuilder.Add(wp.WorksheetCommentsPart, (CurrentRowNumber, columnNumber), comment);
        }
    }

    // This builds the current sheet
    public Sheet Build(OpenXmlPartContainer partContainer, uint sheetId)
    {
        var wsp = partContainer.AddNewPart<WorksheetPart>();

        // Very simplified version of the actual rendering code

        int CurrentColumnNumber = 0;

        foreach (var item of ourItems)
        {
            foreach (var field in item.Fields)
            {
                RenderFieldContent(field)
                CurrentColumnNumber++;
                if (field.HasComment)
                    CreateComment(wsp, item, item.CommentText, CurrentColumnNumber);
            }
            CurrentColumnNumber = 0;
        }

        // We finally render the comment markup        
        _commentBuilder?.Build(wsp);

        return new()
        {
            Id = partContainer.GetIdOfPart(wsp),
            SheetId = sheetId,
            Name = "Name of sheet",
        };
    }
}

Again, we do not require further action right now because for now this helper will suffice.

[twsouthwick]

Thanks @m-gallesio. Taking a look at this, it appears that there may be value in some sort of comment builder that could help with generating this kind of markup. Keeping in line with the goals of the SDK, we'd want a general purpose API that can be customized for individual use but in an orthogonal way.

Taking a stab at the public API we could potentially expose would be:

public class CommentBuilder
{
  ... // some APIs to add shape definitions?
}

public interface ICommentFeature
{
  OpenXmlPart CommentPart { get; }
  void AddComment(int row, int column, string commentText); // maybe take a struct that can have additional parameters, i.e. authors that aren't needed in the example
}

public static class CommentExtensions
{
  public static void AddComments(this SpreadsheetDocument, Action<CommentBuilder> configure)
  {
    // Add a shared comment feature that can be used on parts that support it
  }

  public static void AddComment(this WorksheetPart part, int row, int column, string commentText)
  {
    part.Features.GetRequired<ICommentFeature>().AddComment(row, column, commentText);
  }
}

As a note, this kind of thing would fit in great with the new builder APIs I've been adding that centralizes configuration/etc of packages for 3.1 (see #1553):

IPackageFactory<SpreadsheetDocument> factory = SpreadsheetDocument.CreateBuilder()
  .UseMyCustomComments()
  .Build()

using var spreadsheetDocument = factory.Create();

// every document created with this factory will now have a customized comment creation using the in-box handling