How to use Range.ClearFormats in the xlwings API way

The ClearFormats member of the Range object in Excel’s object model is a method used to remove all formatting from a specified range of cells. This includes clearing number formats, font styles (such as bold, italic, color, and size), cell borders, fill colors, alignment settings, and any other applied formatting. It is particularly useful when you need to reset the visual appearance of cells to the default state without affecting the underlying data or formulas, ensuring a clean slate for applying new styles or standardizing reports. In xlwings, this functionality is accessible through the API, allowing for automation of formatting cleanup in Excel workbooks via Python.

Syntax in xlwings:
The ClearFormats method is called on a Range object. The syntax is straightforward, as it does not require any parameters. In xlwings, you typically access a range using the range property of a Sheet object or directly from the app context. The method is applied as follows:

range_object.clear_formats()

Here, range_object refers to an xlwings Range instance representing one or more cells in Excel. The method operates in-place and returns None. It is equivalent to the VBA method Range.ClearFormats, but adapted to Python’s naming conventions (using snake_case).

Example Usage:
Below is a practical example demonstrating how to use ClearFormats with xlwings to remove formatting from a specific range. This example assumes you have an Excel workbook open or create one via xlwings.

import xlwings as xw

# Connect to the active Excel instance or start a new one
app = xw.App(visible=True) # Set visible=False for background operation
wb = app.books.add() # Create a new workbook; use app.books['WorkbookName.xlsx'] to open an existing one
sheet = wb.sheets['Sheet1']

# Populate some data with formatting for demonstration
sheet.range('A1').value = 'Sample Data'
sheet.range('A1').font.bold = True
sheet.range('A1').font.color = (255, 0, 0) # Red text
sheet.range('A1:C3').value = [[1, 2, 3], [4, 5, 6], [7, 8, 9]]
sheet.range('A1:C3').api.Interior.Color = 65535 # Yellow fill via Excel API (optional)
sheet.range('B2').number_format = '$#,##0.00'

# Clear formatting from a specific range, e.g., cells A1 to C3
sheet.range('A1:C3').clear_formats()

# Verify by checking that formatting is removed (data remains intact)
print(sheet.range('A1:C3').value) # Output: [['Sample Data', 2, 3], [4, 5, 6], [7, 8, 9]]
print(sheet.range('B2').number_format) # Likely returns 'General' or default

# Save and close
wb.save('ClearedFormats.xlsx')
wb.close()
app.quit()