How to use Worksheet.ClearCircles in the xlwings API way

The ClearCircles method of the Worksheet object in Excel is used to remove all circles that have been applied to cells via data validation error alert circles. These circles typically appear when data validation rules are violated, visually indicating invalid entries in a worksheet. The method is particularly useful for cleaning up the visual interface after data validation errors have been corrected or when preparing a sheet for presentation or further processing. It operates on the entire worksheet, affecting all cells within it.

In xlwings, the ClearCircles method can be accessed through a Worksheet object. The syntax is straightforward, as it does not require any parameters. The method is called directly on the worksheet instance.

Syntax:

worksheet.api.ClearCircles()

Here, worksheet is an xlwings Worksheet object representing the target sheet. The .api attribute provides access to the underlying Excel object model, allowing direct invocation of the native ClearCircles method. No arguments are needed, as the action applies to all circles from data validation errors on that specific worksheet.

Example:
Consider a scenario where a worksheet contains data validation rules, such as restricting input in column A to whole numbers between 1 and 10. If a user enters text like “abc” in cell A5, Excel may display a red circle around that cell (depending on error alert settings). To programmatically remove all such circles after reviewing and correcting the data, you can use the following xlwings code:

import xlwings as xw

# Connect to the active Excel instance or open a workbook
app = xw.App(visible=False) # Set to True if you want to see Excel
wb = app.books.open('example.xlsx')
sheet = wb.sheets['Sheet1']

# Assume data validation errors exist and circles are visible
# Clear all circles from data validation errors
sheet.api.ClearCircles()

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