The Application object’s Cells member in Excel’s object model is an essential property for accessing and manipulating individual cells or ranges through their row and column indices. In xlwings, this functionality is primarily accessed via the api property, which exposes the underlying Excel object model, allowing for precise control over cell references.
Functionality:
The Cells property returns a Range object representing a single cell, specified by its row and column numbers. This is particularly useful for programmatically referencing cells without relying on A1-style notation, enabling dynamic cell access in loops or calculations based on numerical indices.
Syntax:
In xlwings, you access the Cells property through the Application object’s api. The basic syntax is:
app.api.Cells(RowIndex, ColumnIndex)
- RowIndex (required): An integer specifying the row number of the cell. For example, 1 refers to the first row.
- ColumnIndex (required): An integer specifying the column number of the cell. For example, 1 refers to column A, 2 to column B, and so on. Alternatively, you can use a column letter string, but this is less common in xlwings when using the Cells property directly.
This property is read-write, allowing you to both retrieve and set cell values or properties.
Examples:
Here are practical xlwings API code instances demonstrating the use of the Cells member:
- Accessing a Single Cell Value:
import xlwings as xw
app = xw.App(visible=False) # Start Excel in the background
workbook = app.books.add() # Create a new workbook
sheet = workbook.sheets.active
# Set value in cell B3 (row 3, column 2) using Cells
sheet.api.Cells(3, 2).Value = "Hello, World!"
# Retrieve the value from cell B3
cell_value = sheet.api.Cells(3, 2).Value
print(cell_value) # Output: Hello, World!
app.quit()
- Looping Through a Range of Cells:
import xlwings as xw
app = xw.App(visible=False)
workbook = app.books.add()
sheet = workbook.sheets.active
# Populate a 5x5 grid with numbers using Cells in nested loops
for row in range(1, 6):
for col in range(1, 6):
sheet.api.Cells(row, col).Value = row * col
# Read and print the values from the grid
for row in range(1, 6):
row_values = [sheet.api.Cells(row, col).Value for col in range(1, 6)]
print(row_values)
app.quit()
- Combining Cells with Other Range Properties:
import xlwings as xw
app = xw.App(visible=False)
workbook = app.books.open("example.xlsx") # Open an existing workbook
sheet = workbook.sheets[0]
# Use Cells to define a range from A1 to C3 by specifying start and end cells
start_cell = sheet.api.Cells(1, 1) # A1
end_cell = sheet.api.Cells(3, 3) # C3
range_obj = sheet.api.Range(start_cell, end_cell)
# Apply formatting to the range
range_obj.Interior.Color = 0x00FF00 # Green background
range_obj.Font.Bold = True
workbook.save()
app.quit()
- Dynamic Cell Reference Based on Variables:
import xlwings as xw
app = xw.App(visible=False)
workbook = app.books.add()
sheet = workbook.sheets.active
# Use variables for row and column indices
target_row = 10
target_column = 5 # Column E
sheet.api.Cells(target_row, target_column).Value = "Dynamic Entry"
# Access adjacent cells using offsets from a base cell
base_cell = sheet.api.Cells(target_row, target_column)
base_cell.Offset(0, 1).Value = "Next Column" # Cell F10
base_cell.Offset(1, 0).Value = "Next Row" # Cell E11
app.quit()
