OpenXML in Microsoft Excel for Comments

Question

I am running the latest Office 365 Excel version, 1901. I have updated to the latest OpenXml SDK but am unable to figure out how to programmatically read information about threaded comments, as all I'm seeing is a full summary comment. Even using the latest OpenXml Nuget package.

If I convert the Excel document to a .zip file, I can see "threadedComments.xml" files which has what I need, but do not know how to go about it programmatically in C# .NET.

Answer 1

You can access to content programmatically, if you know exact location in .zip archieve:

    static class Program
    {
        static void Main(string[] args)
        {
            using (var archive = ZipFile.OpenRead(args[0]))
            {
                var entry = archive.Entries.Where(_ => _.FullName.Equals("xl/comments1.xml", StringComparison.OrdinalIgnoreCase)).FirstOrDefault();
                if (entry != null)
                {
                    var stopwatch = new Stopwatch();
                    stopwatch.Start();
                    var data = new List<string>(Decompress(entry.Open()));
                    var graph = new Graph(data);
                    stopwatch.Watch();
                    Console.ReadLine();
                }
            }
        }

        public static IEnumerable<string> Decompress(Stream stream)
        {
            using (var reader = new StreamReader(stream, Encoding.ASCII))
            {
                string line;
                while ((line = reader.ReadLine()) != null)
                {
                    yield return line;
                }
            }
        }
    }

Answer 2

I know, you didn't watch for VBA, but there the new CommentThreaded object now works at least (Excel version 1906, tested June 2019).
I actually tested it in Visual Studio C#, but it still seems to be not supported.

As of May 15th 2019 the new object CommentThreaded is described by Microsoft.
In my Excel version 1906, it's fully supported in VBA.

Here's some VBA-code to explain the handling a little:

Private Sub ExcelsNewCommentThreaded()
    Dim AllCommentsThreaded As Excel.CommentsThreaded
    Dim OneCommentThreaded As Excel.CommentThreaded
    Dim AllReplies As Excel.CommentsThreaded
    Dim OneReply As Excel.CommentThreaded
    Dim r As Range

    Set AllCommentsThreaded = ActiveSheet.CommentsThreaded

    ' loop over all threaded comments of a worksheet and get their info
    For Each OneCommentThreaded In AllCommentsThreaded
        With OneCommentThreaded
            Debug.Print .Author.Name, .Date, .Text
            For Each OneReply In .Replies
                With OneReply
                    Debug.Print .Author.Name, .Date, OneReply.Text
                End With
            Next OneReply
        End With
    Next OneCommentThreaded

    Set r = Selection.Cells(1)

    ' check if the selected cell already contains a threaded comment
    If r.CommentThreaded Is Nothing Then
        r.AddCommentThreaded ("my new comment")
    End If

    With r.CommentThreaded
        ' get text of comment
        Debug.Print .Text

        ' add some replies
        .AddReply ("my reply 1")
        .AddReply ("my reply 2")

        ' change text of comment
        Debug.Print .Text(Text:="text of comment changed")
        Debug.Print .Text

        ' change text of a reply
        .Replies(1).Text Text:="text of reply 1 changed"
        Debug.Print .Replies(1).Text

        ' delete second reply
        .Replies(2).Delete

        ' delete whole comment including its replies
        .Delete
    End With
End Sub