How to use Worksheet.Comments in the xlwings API way

The Worksheet.Comments property in xlwings provides access to the collection of all comments (also known as notes in newer Excel versions) within a specific worksheet. This property is essential for programmatically managing cell annotations, enabling developers to add, retrieve, modify, or delete comments. Comments are useful for adding explanatory notes, instructions, or feedback directly to cells, enhancing the interactivity and clarity of Excel workbooks. Through xlwings, you can automate comment-related tasks, such as bulk updates or extracting comment text for reporting purposes.

The syntax for accessing comments via xlwings is straightforward. The property returns a Comments object that represents a collection of individual Comment objects. You can reference it as: sheet.comments, where sheet is an xlwings Worksheet object. To interact with a specific comment, you can index it by its cell address or use iteration over all comments. Key methods and properties include:

• add(cell, text): Adds a new comment to a specified cell with the given text. The cell parameter can be a string (e.g., “A1”) or a tuple (e.g., (1, 1)), and text is a string containing the comment content.
• count: Returns the number of comments in the worksheet.
• item(index): Retrieves a Comment object by index (0-based) or by cell address.
• clear(): Removes all comments from the worksheet.
  Each Comment object has properties like text (to get or set the comment text), author (to get or set the author name), and delete() (to remove the comment). Note that in Excel, comments and notes are distinct; xlwings primarily handles traditional comments, but it may adapt based on the Excel version.

For example, consider a scenario where you need to add a comment to cell B5 with a reminder and then list all comments in the worksheet. Here is a code instance using xlwings:

import xlwings as xw

# Open an existing workbook and select a worksheet
wb = xw.Book('example.xlsx')
sheet = wb.sheets['Sheet1']

# Add a new comment to cell B5
sheet.comments.add('B5', 'Review this value for accuracy.')

# Get the count of comments
comment_count = sheet.comments.count
print(f"Total comments: {comment_count}")

# Iterate through all comments and print their details
for comment in sheet.comments:
    print(f"Cell: {comment.cell.address}, Text: {comment.text}, Author: {comment.author}")

# Modify an existing comment in cell A3 (if it exists)
if sheet.comments.count > 0:
    # Assuming A3 has a comment; you can check with try-except or conditions
    try:
        comment_a3 = sheet.comments['A3']
        comment_a3.text = 'Updated note: Please verify.'
    except Exception as e:
        print(f"Comment not found in A3: {e}")

# Delete a comment from cell B5
sheet.comments['B5'].delete()

# Clear all comments from the worksheet
sheet.comments.clear()

# Save and close
wb.save()
wb.close()