How to use Worksheet.CommentsThreaded in the xlwings API way

The Worksheet object’s CommentsThreaded property in xlwings provides access to the collection of threaded comments associated with a specific worksheet. Threaded comments, introduced in newer versions of Microsoft Excel, allow for modern, conversation-style discussions attached to a cell, differing from the older single “Comment” (now often called a “Note”). This property is essential for programmatically managing these collaborative discussions, enabling developers to read, add, reply to, or delete comment threads directly from Python, thereby automating feedback collection, review processes, or annotation systems within workbooks.

Syntax and Key Members

The property is accessed via Worksheet.comments_threaded. It returns a CommentsThreaded collection object. The primary method for adding a new threaded comment is add. The key syntax in xlwings is:

my_comment = ws.comments_threaded.add(cell, text, author=None)

• cell (required): An xlwings Range object or a string address (e.g., "A1") specifying the cell to which the comment thread will be attached.
• text (required): A string containing the content of the initial comment in the thread.
• author (optional): A string specifying the author’s name for the comment. If omitted, it typically defaults to a system or application-defined name.

The add method returns a CommentThreaded object, which itself has useful properties and methods, such as:

• .text: Gets or sets the text of the specific comment (if accessed from a reply within the thread, careful indexing of the .comments collection is needed).
• .replies: A collection of replies within the thread. You can use .replies.add(text) to post a new reply.
• .delete(): Deletes the entire comment thread.

Code Examples

1. Adding a New Threaded Comment:
   This example adds a new threaded comment to cell B5.

import xlwings as xw
wb = xw.Book("example.xlsx")
ws = wb.sheets["Sheet1"]
new_thread = ws.comments_threaded.add("B5", "Initial review: Please verify these figures.", author="AnalysisBot")
wb.save()

2. Reading Existing Threaded Comments:
   This loop iterates through all threaded comments on the sheet and prints their location and the text of the first comment in each thread.

import xlwings as xw
wb = xw.Book("example.xlsx")
ws = wb.sheets["Sheet1"]
for comment_thread in ws.comments_threaded:
    # The CommentThreaded object's .cell property gives its location
    print(f"Cell {comment_thread.cell.address}: {comment_thread.text}")

3. Replying to a Comment Thread:
   This example finds a comment thread in cell D10 and adds a reply to it.

import xlwings as xw
wb = xw.Book("example.xlsx")
ws = wb.sheets["Sheet1"]
# Assuming a thread exists in D10. In practice, you might loop to find it.
for thread in ws.comments_threaded:
    if thread.cell.address == "$D$10":
        thread.replies.add("Reply: Figures have been updated and confirmed.", author="ReviewerJane")
        break
wb.save()

4. Deleting a Specific Comment Thread:
   This deletes the threaded comment located at cell F7.

import xlwings as xw
wb = xw.Book("example.xlsx")
ws = wb.sheets["Sheet1"]
for thread in ws.comments_threaded:
    if thread.cell.address == "$F$7":
        thread.delete()
        break
wb.save()