Threaded Comments in Spreadsheet Documents

• Apr 04, 2023
• 4 minutes to read

Users can add comments to a spreadsheet cell and reply to existing comments. Discussions can take place within the context of the cell, without the need to switch to another communication tool (email, instant messaging, or project management software).

The Spreadsheet Document API allows you to manage threaded comments in code. You can add, edit and remove comments. Documents with threaded comments are processed without content loss.

Note

The Spreadsheet Document API does not print or export threaded comments to PDF format.

Access Comments

Use the Worksheet.ThreadedComments property to retrieve a ThreadedCommentCollection that contains all threaded comments in a worksheet. The ThreadedCommentCollection.GetThreadedComments(CellRange) method allows you to obtain all comments applied to a specified cell range.

The code sample below obtains all comments added to the A1:G20 cell range:

C#

using DevExpress.Spreadsheet;

var workbook = new Workbook();
workbook.LoadDocument(@"C:\Docs\Comments.xlsx");
Worksheet worksheet = workbook.Worksheets[0];
var comments = worksheet.ThreadedComments.GetThreadedComments(worksheet["A1:G20"]);

VB.NET

Imports DevExpress.Spreadsheet

Private workbook = New Workbook()
workbook.LoadDocument("C:\Docs\Comments.xlsx")
Dim worksheet As Worksheet = workbook.Worksheets(0)
Dim comments = worksheet.ThreadedComments.GetThreadedComments(worksheet("A1:G20"))

Add a Comment

Call the ThreadedCommentCollection.Add method to add a threaded comment to a specific cell. If you pass a cell range as the method parameter, the comment is added to the top-left cell of this range.

You can pass a string or a ThreadedCommentAuthor instance to specify a comment author. The ThreadedCommentAuthor object allows you to specify login credentials that link the author to their identity provider (Windows Live ID, Office 365, and so on).

The code sample below adds a comment to the G16 cell:

C#

var workbook = new Workbook();
workbook.LoadDocument(@"C:\Docs\Comments.xlsx");
Worksheet worksheet = workbook.Worksheets[0];
var comments = worksheet.ThreadedComments;

ThreadedComment threadedComment =
    comments.Add(worksheet["G16"], "Sales Department",
         "The discount was approved by the Sales Director at the meeting on November 5th.");
threadedComment.Resolved = true;

workbook.SaveDocument(@"C:\Docs\Comments_upd.xlsx");

VB.NET

Dim workbook As New Workbook()
workbook.LoadDocument("C:\Docs\Comments.xlsx")
Dim worksheet As Worksheet = workbook.Worksheets(0)
Dim comments = worksheet.ThreadedComments

Dim threadedComment As ThreadedComment = comments.Add(worksheet("G16"), "Sales Department", "The discount was approved by the Sales Director at the meeting on November 5th.")
threadedComment.Resolved = True

workbook.SaveDocument("C:\Docs\Comments_upd.xlsx")

Edit a Comment

Use ThreadedComment class properties to change comment parameters. You can change the comment’s text and specify whether this comment resolves the thread.

The code sample below resolves the thread:

C#

using DevExpress.Spreadsheet;

var workbook = new Workbook();
workbook.LoadDocument(@"C:\Docs\Comments.xlsx");
Worksheet worksheet = workbook.Worksheets[0];
var comments = worksheet.ThreadedComments;

ThreadedComment threadedComment = comments[0];
threadedComment.Text = "This thread is resolved";
threadedComment.Resolved = true;

VB.NET

Imports DevExpress.Spreadsheet

Private workbook = New Workbook()
workbook.LoadDocument("C:\Docs\Comments.xlsx")
Dim worksheet As Worksheet = workbook.Worksheets(0)
Dim comments = worksheet.ThreadedComments

Dim threadedComment As ThreadedComment = comments(0)
threadedComment.Text = "This thread is resolved"
threadedComment.Resolved = True

Reply to a Comment

Use the ThreadedComment.Reply method to add a reply. You can pass a string or a ThreadedCommentAuthor instance to specify the comment’s author.

The code sample below adds a reply to the first comment:

C#

using DevExpress.Spreadsheet;

var workbook = new Workbook();
workbook.LoadDocument(@"C:\Docs\Comments.xlsx");
Worksheet worksheet = workbook.Worksheets[0];
var comments = worksheet.ThreadedComments;

ThreadedComment threadedComment = comments[0];
threadedComment.Reply("Nancy Davolio", "Thanks for the hint");
workbook.SaveDocument(@"C:\Docs\Comments_upd.xlsx");

VB.NET

Imports DevExpress.Spreadsheet

Private workbook = New Workbook()
workbook.LoadDocument("C:\Docs\Comments.xlsx")
Dim worksheet As Worksheet = workbook.Worksheets(0)
Dim comments = worksheet.ThreadedComments

Dim threadedComment As ThreadedComment = comments(0)
threadedComment.Reply("Nancy Davolio", "Thanks for the hint")
workbook.SaveDocument("C:\Docs\Comments_upd.xlsx")

Use the ThreadedComment.Replies property to access replies in a thread. You can edit or remove replies as your needs dictate.

Remove a Comment

The following methods allow you to remove comments:

ThreadedCommentCollection.Remove

Removes the specified comments or a comment applied to a cell range.

ThreadedCommentCollection.RemoveAt

Removes a comment at the specified index in the collection.

The code sample below removes all comments with the specified author:

C#

var workbook = new Workbook();
workbook.LoadDocument(@"C:\Docs\Comments.xlsx");
Worksheet worksheet = workbook.Worksheets[0];
var comments = worksheet.ThreadedComments.Where(comment => comment.Author.Name == "Nancy Davolio");
if (comments!=null)
{
    foreach (var comment in comments)
    {
        worksheet.ThreadedComments.Remove(comment);
    }
}

VB.NET

Dim workbook As New Workbook()
workbook.LoadDocument("C:\Docs\Comments.xlsx")
Dim worksheet As Worksheet = workbook.Worksheets(0)
Dim comments = worksheet.ThreadedComments.Where(Function(comment) comment.Author.Name = "Nancy Davolio")
If comments IsNot Nothing Then
  For Each comment In comments
    worksheet.ThreadedComments.Remove(comment)
  Next comment
End If

Add a Simple Note

The Worksheet.Comments property allows you to manage simple notes - legacy comments in Excel documents. Refer to the following article for more information: How to: Add a Simple Note To a Cell.

Note

The Worksheet.Comments property retrieves simple notes only. The return value - a CommentCollection - does not contain threaded comments.