How to use Application.Range in the xlwings API way

The Application object’s Range member in Excel’s object model is a fundamental interface for accessing and manipulating cells and ranges within a workbook. In xlwings, this is primarily accessed through the app (or xw.apps) object, which represents the Excel application instance. The Range member is not directly called as a method on app in xlwings; instead, it is used via the books, sheets, and range properties to target specific cells. The core functionality revolves around reading, writing, and formatting cell data, as well as performing operations like resizing or selecting ranges.

Syntax and Parameters:
The typical xlwings pattern to get a range starts from the application, through a specific workbook and sheet. The direct equivalent to VBA’s Application.Range is not a single call but a chain:

import xlwings as xw
app = xw.apps.active # Or xw.App() for a new instance
range_obj = app.books['Book1'].sheets['Sheet1'].range('A1:B10')

Alternatively, using the shorter, more common xlwings syntax that implicitly uses the active app:

range_obj = xw.Range('A1:B10') # Uses active sheet in active workbook

The range() method/function accepts arguments to define the range:

• cell1 (str or tuple): The starting cell address (e.g., 'A1') or a tuple of row and column numbers (e.g., (1, 1) for A1).
• cell2 (str or tuple, optional): The ending cell address for defining a rectangular range (e.g., 'B10'). If omitted, a single-cell range is created.

The returned object is an xlwings Range object, which has numerous properties and methods like value, formula, color, autofit(), etc.

Example Usage:
Here are practical examples using xlwings to interact with ranges via the application context:

1. Writing data to a range:

import xlwings as xw
app = xw.App(visible=True) # Start Excel app
wb = app.books.add() # Add a new workbook
ws = wb.sheets[0]
# Write a 2D list to range A1:C3
ws.range('A1').value = [[1, 'Apple', 2.5], [2, 'Banana', 1.8], [3, 'Cherry', 3.2]]

2. Reading data from a range:

data = ws.range('A1:C3').value # Returns a list of lists
print(data) # Output: [[1, 'Apple', 2.5], [2, 'Banana', 1.8], [3, 'Cherry', 3.2]]

3. Using range operations:

# Autofit column widths for range A:C
ws.range('A:C').columns.autofit()
# Add a formula in cell D1
ws.range('D1').formula = '=SUM(C1:C3)'
# Get the address of the used range
used_range = ws.used_range.address
print(used_range) # e.g., '$A$1:$D$3'

4. Dynamic range via app selection:

app = xw.apps.active
# Get the range currently selected in Excel
selected_range = app.selection
if isinstance(selected_range, xw.Range):
    selected_range.value = 'Updated' # Write to all selected cells