How to use Worksheet.Index in the xlwings API way

The Index member of the Worksheet object in xlwings provides a read-only property that returns the index number of the worksheet within its parent workbook’s Worksheets collection. This index is a 1-based integer, meaning the first worksheet in a workbook has an Index of 1, the second has an index of 2, and so on. This property is particularly useful when you need to programmatically reference or manipulate worksheets based on their positional order, rather than relying on their names. It can be essential for loops, conditional logic, or when organizing sheets dynamically.

Functionality:
The primary function is to retrieve the positional index of a worksheet. This index reflects the sheet’s order as seen in the Excel application’s tab bar. It is determined by the sheet’s position from left to right. If sheets are rearranged, their index values change accordingly. The Index property is often used in conjunction with the Worksheets collection to access specific sheets.

Syntax:
In xlwings, the property is accessed directly from a Worksheet object. The syntax is straightforward as it does not accept any parameters.

worksheet.index

• worksheet: This is an xlwings Sheet object representing the target worksheet. It is typically obtained via wb.sheets['SheetName'] or wb.sheets[0].
• The property returns an integer (int).

Example Usage:
Consider a workbook with three worksheets named “Data”, “Summary”, and “Chart”, in that order from left to right.

import xlwings as xw

# Connect to an existing workbook
wb = xw.Book("report.xlsx")

# Get a worksheet by its name
data_sheet = wb.sheets['Data']
summary_sheet = wb.sheets['Summary']

# Retrieve their indices
print(f"Index of 'Data' sheet: {data_sheet.index}") # Output: 1
print(f"Index of 'Summary' sheet: {summary_sheet.index}") # Output: 2

# Example 1: Using index in a loop to perform an action on every other sheet
for i in range(1, len(wb.sheets) + 1, 2): # Start at 1, step by 2
    sheet = wb.sheets[i-1] # xlwings collection is 0-based for indexing
    print(f"Processing sheet at workbook index {i}: {sheet.name}")

# Example 2: Conditionally act based on sheet position
if summary_sheet.index > data_sheet.index:
    print("The Summary sheet is to the right of the Data sheet.")

# Example 3: Activating a specific sheet by its known index (e.g., the first sheet)
wb.sheets[0].activate() # Uses 0-based index for the collection
# This is equivalent to wb.sheets[wb.sheets[0].index - 1].activate()