How to use Worksheet.Rows in the xlwings API way

The Rows member of the Worksheet object in xlwings provides access to a collection representing all the rows within a specific worksheet. This is a powerful feature for performing operations on entire rows, such as selecting, formatting, hiding, or deleting them. It is analogous to the Rows property in the Excel VBA object model and allows for efficient, row-level manipulations without needing to iterate through individual cells manually.

Functionality
The primary function of the Rows property is to return a Range object that represents one or more rows. You can use it to apply formatting (like font color or row height), adjust visibility, clear contents, or delete rows entirely. It is particularly useful for applying uniform changes across a row or for dynamically handling rows based on conditions.

Syntax
The basic syntax to access rows in xlwings is:

ws.rows

This returns a Range object for all rows in the worksheet ws. You can also specify a particular row or a range of rows using indexing or slicing:

• ws.rows[0] or ws.rows[1]: Accesses the first row. Note: xlwings uses 1-based indexing by default for rows and columns when using the rows property in this context, aligning with Excel’s row numbering (row 1, row 2, etc.).
• ws.rows[1:5]: Accesses rows 1 through 4 (the slice is 1-based and end-exclusive, similar to Python slicing, but here it refers to Excel rows 1 to 4).
• ws.rows['1:5']: Alternative string notation to access rows 1 to 5 (inclusive).

Once you have the Range object, you can call various methods and properties. Common parameters include:

• For formatting: Use properties like row_height, font, color, or methods like autofit().
• For operations: Use methods like delete(), clear(), hide, or unhide.
• To get values: Use the value property to retrieve data as a list of lists.

Examples
Here are some practical xlwings API code examples using the Rows member:

1. Selecting and Formatting All Rows: Set a uniform row height for the entire worksheet.

import xlwings as xw
wb = xw.Book('example.xlsx')
ws = wb.sheets['Sheet1']
ws.rows.row_height = 20 # Sets height of all rows to 20 points

2. Hiding Specific Rows: Hide rows 5 to 10 based on a condition.

ws.rows['5:10'].hidden = True # Hides rows 5 through 10

3. Deleting Rows: Remove rows 3 to 7 from the worksheet.

ws.rows['3:7'].delete() # Deletes rows 3 to 7, shifting rows up

4. Clearing Contents: Clear data from the first 5 rows without deleting the rows.

ws.rows[:5].clear_contents() # Clears values but keeps formatting (using 0-based slicing for the first 5 rows)
# Alternatively, with 1-based: ws.rows['1:5'].clear_contents()

5. Autofitting Rows: Adjust row heights to fit the content automatically.

ws.rows.autofit() # Autofits all rows based on cell content

6. Iterating Over Rows: Loop through each row to perform custom operations, such as checking values.

for row in ws.rows:
    if row.value and row.value[0] == 'Target': # Check first cell in each row
row.color = (255, 200, 200) # Highlight row with a light red color