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