How to use Worksheet.UsedRange in the xlwings API way

The UsedRange property of a Worksheet object in Excel is a powerful feature that returns a Range object representing the area of a worksheet that is currently in use. This includes all cells that have ever contained data, formatting, or formulas, even if some of those cells are now empty. It is particularly useful for dynamically determining the extent of your data without needing to know the exact row and column boundaries beforehand. In xlwings, accessing this property allows you to programmatically interact with the actual used portion of a sheet, making your code more robust and adaptable to changing data sizes.

Syntax in xlwings:
The UsedRange property is accessed as an attribute of a Worksheet object. There are no parameters to specify.

used_range = ws.used_range

Here, ws is an xlwings Worksheet object (e.g., obtained via wb.sheets['Sheet1']). The returned used_range is an xlwings Range object. This Range object has properties like .address, .row, .column, .rows.count, and .columns.count which you can use to get the location and dimensions of the used area.

Key Functionality and Parameters:
Since UsedRange is a property, not a method, it does not take direct parameters. Its behavior is controlled by the state of the Excel worksheet. However, understanding what constitutes the “used range” is crucial:

• Inclusion Criteria: Any cell that has ever been edited, formatted, or contained a formula is considered part of the used range. This means clearing cell contents might not remove it from the used range if formatting remains.
• Dynamic Nature: The used range can expand when new data is added at its edges and can sometimes be reset by saving the workbook after deleting all content and formatting from unused cells.

Code Examples:
Here are practical examples of using ws.used_range in xlwings:

1. Getting the Address and Dimensions:

import xlwings as xw
app = xw.App(visible=False)
wb = xw.Book('data.xlsx')
ws = wb.sheets[0]

used_rng = ws.used_range
print(f"Used Range Address: {used_rng.address}")
print(f"Number of used rows: {used_rng.rows.count}")
print(f"Number of used columns: {used_rng.columns.count}")
print(f"Top-left cell: ({used_rng.row}, {used_rng.column})")

wb.close()
app.quit()

2. Clearing All Content and Formatting in the Used Range:

import xlwings as xw
wb = xw.Book('report.xlsx')
ws = wb.sheets['Summary']
ws.used_range.clear() # Clears both content and formatting
wb.save()

3. Dynamically Reading All Data into a Python List:

import xlwings as xw
wb = xw.Book('inventory.xlsx')
ws = wb.sheets['Stock']

# Read all values from the used range into a 2D list
all_data = ws.used_range.value
for row in all_data:
    print(row)

# If you need the data as a pandas DataFrame
import pandas as pd
df = pd.DataFrame(all_data)
print(df.head())

4. Applying Formatting to the Entire Used Area:

import xlwings as xw
from xlwings.constants import LineStyle
wb = xw.Book('financials.xlsx')
ws = wb.sheets['Q1']

used_rng = ws.used_range
# Add borders around all used cells
used_rng.api.Borders.LineStyle = LineStyle.xlContinuous
used_rng.api.Borders.Weight = 2
wb.save()