How to use Worksheet.Cells in the xlwings API way

The Cells property of a Worksheet object in the Excel object model is a fundamental member for accessing and manipulating individual cells or ranges of cells within a worksheet. In xlwings, this is accessed through the api property, which provides direct access to the underlying Excel object model, allowing for precise control similar to VBA. The Cells property is versatile, enabling both reading and writing of cell values, as well as formatting and other cell-specific operations.

Functionality:
The primary function of the Cells property is to return a Range object that represents a single cell or a collection of cells. It is commonly used to refer to cells by their row and column numbers, which is particularly useful in loops or when programmatically determining cell positions. This property is essential for tasks that require iterating over cells, dynamically referencing ranges, or accessing cells based on calculated indices.

Syntax:
In xlwings, the syntax to access the Cells property is:

worksheet.api.Cells(row_index, column_index)

• row_index: Required. An integer that specifies the row number of the cell (1-indexed).
• column_index: Required. An integer that specifies the column number of the cell (1-indexed). Alternatively, a string representing the column letter (e.g., “A”) can be used, but when using the Cells property directly via api, it typically expects numeric indices for consistency with the Excel object model.

The Cells property can also be called with a single argument to return a range encompassing all cells in the worksheet, though this is less common. For example, worksheet.api.Cells without arguments refers to all cells, but in practice, worksheet.used_range or similar methods are often preferred for performance.

Examples:
Here are several xlwings API code examples demonstrating the use of the Cells property:

1. Accessing a Single Cell Value:

import xlwings as xw
wb = xw.Book('example.xlsx')
ws = wb.sheets['Sheet1']
# Get value from cell at row 5, column 3 (C5)
cell_value = ws.api.Cells(5, 3).Value
print(cell_value)
# Set value in cell at row 2, column 1 (A2)
ws.api.Cells(2, 1).Value = "Hello, World!"

2. Iterating Over a Range of Cells:

# Write values to the first 5 rows in column A
for i in range(1, 6):
    ws.api.Cells(i, 1).Value = f"Data {i}"
# Read values from the first 3 rows in column B
for i in range(1, 4):
    print(ws.api.Cells(i, 2).Value)

3. Formatting Cells:

# Change the font color of cell D10 to red
ws.api.Cells(10, 4).Font.Color = 0xFF0000 # RGB color for red
# Set the interior color of cell E5 to yellow
ws.api.Cells(5, 5).Interior.Color = 0xFFFF00

4. Using Cells with Variables for Dynamic References:

row_num = 7
col_num = 4
# Dynamically access cell at row 7, column 4 (D7)
dynamic_cell = ws.api.Cells(row_num, col_num)
dynamic_cell.Value = "Dynamic Entry"

5. Accessing All Cells (Entire Worksheet):

# Refer to all cells in the worksheet (use with caution for large sheets)
all_cells = ws.api.Cells
print(f"Total rows: {all_cells.Rows.Count}, Total columns: {all_cells.Columns.Count}")