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.

No ideas? Somebody out there has to know a little bit of OpenXML — Nexxas, Feb 12 '19 at 06:01
Could you attach an excel file to play around with? I guess a lot of people have an older version of Excel and hence cannot generate "threadedComments.xml". Only have the option for older comments which is Notes now I think. — FortyTwo, Feb 18 '19 at 17:59
@Nexxas Please have a look at this link (https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c4400c1f-e4b4-43ed-b037-2f531274ea78/dynamic-comments-in-excel-using-open-xml?forum=exceldev) This may help you get dynamic comments. Let me know if this helps! — VikrantMore, Feb 19 '19 at 08:26
@VikrantMore adding/reading comments is possible with openXML. He is wants to read threaded comments which is a new feature in excel. — FortyTwo, Feb 22 '19 at 21:17
Take a look at this: [2.6.207 CT_ThreadedComments](https://learn.microsoft.com/en-us/openspecs/office_standards/ms-xlsx/0cfb2f05-87a2-4b5f-a9ad-fb11ca39e2f8) and [2.6.205 CT_ThreadedComment](https://learn.microsoft.com/en-us/openspecs/office_standards/ms-xlsx/42f9b03d-9662-4204-9783-dbeb324a691c). It might help you to resolve your issue. — Maciej Los, Feb 25 '19 at 08:01
@Nexxas can you give a link to this particular file with threadedCommments.xml? — Alan Turing, Feb 25 '19 at 13:41

Asger · Answer 1 · 2019-06-22T10:54:29.030

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

score -1 · Answer 2 · answered Feb 25 '19 at 14:20

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;
                }
            }
        }
    }

OpenXML in Microsoft Excel for Comments

2 Answers2