Comments

Since R2 2022 the SpreadProcessing library supports working with comments. Comments are used for marking information about a cell's data and can have one or multiple Replies. All comments can be found in the Comments property of the worksheet, which is of the type CommentCollection. This collection holds SpreadsheetComment objects, which represent the comments. Each comment has the following members:

Properties:

CellIndex: Gets or sets the cell index the comment is assigned to.
Text: Gets or sets the text of the comment.
CreationDate: Gets or sets the date when the comment is created. Nullable.
Author: Gets or sets the author assigned to the comment.
Replies: Gets the comment replies. The list is sorted by CreationDate.
IsResolved: Gets or sets a value indicating whether the comment is resolved.

All of the above properties will push a change to the undo stack when modified.

Methods:

AddReply: Adds a SpreadsheetCommentReply to the ReplySortedCollection. The collection will be re-sorted by SpreadsheetReply`s CreationDate in ascending order after adding an object.
RemoveReply: Removes the specified reply from the collection.

Working with CommentCollection

Adding comments

To add a comment you need to specify the cell index to which the comment will be related, the author, the text content, and the creation date. Specifying the creation date is optional and by default, its value is the current date and time.

Example 1: Add comment

CellIndex relatedCellIndex = new CellIndex(1, 1); 
string author = "John Doe"; 
string text = "Comment Content"; 
DateTime creationDate = DateTime.Now; 
 
worksheet.Comments.Add(relatedCellIndex, author, text, creationDate);

The above snippet will add a comment in cell B2.

Removing Comments

To remove a comment, you should specify the comment instance. This instance can be obtained from the CommentCollection.

Example 2: Remove comment

SpreadsheetComment comment = worksheet.Comments[0]; 
worksheet.Comments.Remove(comment);

Replies

Each comment can be replied to, forming a thread of information. All replies can be found in the Replies property of the comment, which is of the type ReplySortedCollection. This collection holds SpreadsheetCommentReply objects which represent the replies. The ReplySortedCollection has the following members:

Properties:

Count: Gets the number of elements contained in the ReplySortedCollection.

Methods:

Add: Adds a SpreadsheetCommentReply to the ReplySortedCollection. The collection will be re-sorted by SpreadsheetReply`s CreationDate in ascending order after adding an object. Requires an object of type SpreadsheetCommentReply and can be used for adding existing replies. For adding a new reply, it is best to use the SpreadsheetComment.AddReply() method.
Remove: Removes the specified SpreadsheetCommentReply object from the ReplySortedCollection.
RemoveAt: Removes the element at the specified index of the ReplySortedCollection.
Clear: Removes all elements from the ReplySortedCollection.
Contains: Determines whether an element is in the ReplySortedCollection.
CopyTo: Copies the entire ReplySortedCollection to a compatible one-dimensional array, starting at the specified index of the target array.

Example 3: Working with Replies

void MyProgram() 
    { 
        Workbook workbook = new Workbook(); 
        Worksheet worksheet = workbook.Worksheets.Add(); 
 
        string text = "First Comment"; 
        CellIndex relatedCellIndex = new CellIndex(0, 0); // Cell A1 
        AddCommentWithRepliesToWorksheet(worksheet, relatedCellIndex, text, 2); // First comment will have 2 replies 
 
        relatedCellIndex = new CellIndex(1, 1); // Cell B2 
        text = "Second Comment"; 
        AddCommentWithRepliesToWorksheet(worksheet, relatedCellIndex, text, 0); // Second comment will have 0 replies 
 
        // Add existing reply using the Add() method of SpreadsheetCommentReply 
        var firstComment = worksheet.Comments[0]; 
        var secondComment = worksheet.Comments[1]; 
 
        SpreadsheetCommentReply reply = firstComment.Replies[0]; 
        secondComment.Replies.Add(reply); // Copies reply #1 from firstComment to secondComment 
 
        //Remove 
        firstComment.Replies.Remove(reply); 
 
        //Clear 
        firstComment.Replies.Clear(); 
 
        //Contains 
        firstComment.Replies.Contains(reply); // Returns false 
 
        //CopyTo 
        SpreadsheetCommentReply[] replyArray = new SpreadsheetCommentReply[1]; 
        secondComment.Replies.CopyTo(replyArray, 0); 
    } 
 
    void AddCommentWithRepliesToWorksheet(Worksheet worksheet, CellIndex relatedCellIndex, string commentText, int repliesCount) 
    { 
        string authorName = "Jane Doe"; 
        DateTime creationDate = DateTime.Now; 
        SpreadsheetComment comment = worksheet.Comments.Add(relatedCellIndex, authorName, commentText, creationDate); 
 
        for (int i = 0; i < repliesCount; i++) 
        { 
            string replyText = "Reply #" + (i + 1); 
            comment.AddReply(authorName, replyText, creationDate); // Add new reply using the SpreadsheetComment.AddReply() method  
        } 
    }

Events

Both CommentCollection and ReplySortedCollection expose the following events, which work identically for both types:

Changing: Occurs prior to changing the collection.
Changed: Occurs after the collection has changed.

The two events for both collections use similar enumeration types for event arguments, with two possible values:

Add: Used when Adding a Comment or Reply
Remove: Used when Removing a Comment or Reply

Example 4: Changing the author of a comment upon adding it to the CommentCollection using the Changing event

void Comments_Changing(object sender, ShapeCollectionChangingEventArgs<SpreadsheetComment> e) 
{ 
    SpreadsheetComment comment = e.Shape; 
    if (e.ChangeType == ShapeCollectionChangeType.Add) 
    { 
        comment.Author = "Comment Author"; 
    } 
}

Example 5: Changing the author of a reply upon adding it to the ReplySortedCollection using the Changing event

void Replies_Changing(object sender, ReplySortedCollectionChangingEventArgs e) 
{ 
    if (e.ChangeType == ReplySortedCollectionChangeType.Add) 
    { 
        e.Reply.Author = "Reply Author"; 
    } 
}