How to use Worksheet.Range in the xlwings API way

The Range member of the Worksheet object in the xlwings API is a fundamental and versatile tool for interacting with cells, ranges, and data within an Excel worksheet. It is the primary gateway for reading, writing, and manipulating cell content, formatting, and formulas. Functionally, it allows you to select a specific cell, a rectangular block of cells, or even non-contiguous cells, enabling operations like data input, extraction, and formatting adjustments.

Syntax and Parameters:
The basic syntax to access a range is through a Worksheet object. The most common call is:
ws.range(cell1, cell2=None)

• ws: The xlwings Worksheet object.
• cell1: A required argument that defines the start of the range. It can be a string address (e.g., "A1"), a tuple of integers for the row and column (e.g., (1, 1) for A1), or an xlwings Range object.
• cell2: An optional argument that defines the end of the range for a multi-cell selection. It accepts the same formats as cell1. If omitted, the range is a single cell defined by cell1.

You can also use the shorthand indexing syntax: ws["A1"] or ws["A1:C5"].

Key Properties and Methods of the Range Object:
Once you have a Range object, you can use its properties and methods. Key members include:

• .value: Gets or sets the values in the range. For a single cell, it returns a scalar (e.g., number, string). For multiple cells, it returns a nested list (list of lists).
• .formula: Gets or sets the Excel formula string for the range.
• .address: Returns the absolute address of the range as a string.
• .row / .column: Return the first row or column number of the range (1-indexed).
• .rows / .columns: Return a Range object representing the rows or columns within the range.
• .autofit(): Automatically adjusts the width of columns or height of rows to fit their contents.
• .clear(): Clears the content and formatting of the range.
• .copy(destination): Copies the range to a specified destination range.
• .paste(): Pastes the contents of the Clipboard into the range.

Code Examples:

1. Reading and Writing Values:

import xlwings as xw
wb = xw.Book("data.xlsx")
ws = wb.sheets["Sheet1"]

# Write a single value
ws.range("B2").value = "Total Revenue"

# Write a list of lists (2D array)
data = [[1, 2, 3], [4, 5, 6]]
ws.range("A4").value = data # Expands from A4 to C5

# Read a single cell
revenue = ws.range("D10").value
print(revenue)

# Read a contiguous range into a 2D list
table_data = ws.range("A1:C10").value
for row in table_data:
    print(row)

2. Using Formulas and Formatting:

# Insert a SUM formula
ws.range("C15").formula = "=SUM(C5:C14)"

# Autofit columns for a specific range
ws.range("A1:F20").autofit()

# Clear a range
ws.range("OldData!A1:Z100").clear()

3. Working with Rows and Columns:

# Get the address of a dynamically defined range
start_cell = (5, 2) # Row 5, Column 2 (B5)
end_cell = (15, 5) # Row 15, Column 5 (E15)
my_range = ws.range(start_cell, end_cell)
print(f"Range address: {my_range.address}")

# Access entire rows/columns
ws.range("5:5").value = ["Q1", "Q2", "Q3", "Q4"] # Entire row 5
ws.range("C:C").column_width = 15 # Set width of entire column C