How to use Worksheets.Item in the xlwings API way

The Worksheets.Item member in the Excel object model is a fundamental property used to access a specific Worksheet object within a Workbooks collection. In xlwings, this functionality is seamlessly integrated, allowing users to reference worksheets by their name or index number directly through the sheets or worksheets collection of a Book object. This is essential for navigating and manipulating data in multi-sheet workbooks programmatically.

Functionality:
The primary function of the Item member is to return a single Worksheet object. It enables precise targeting of a sheet for operations such as data reading, writing, formatting, or chart creation. Using the name is the most common and readable approach, while the index is useful for iterating through sheets or accessing them by their positional order.

Syntax in xlwings:
In xlwings, you typically access worksheets via the sheets property of a Book instance. The syntax mirrors the intuitive indexing or key-based access found in Python.

import xlwings as xw
wb = xw.Book('workbook.xlsx') # Open a workbook
# Access by name (string key)
ws_by_name = wb.sheets['Sheet1']
# Access by index (1-based integer)
ws_by_index = wb.sheets[1]

• Parameter (key/index): The argument can be either a str representing the exact worksheet name (case-insensitive in Windows Excel) or an int representing the sheet’s position (1 for the first sheet, 2 for the second, etc.).
• Return Value: Returns an xlwings.main.Sheet object, which corresponds to the Excel Worksheet.

Examples:

1. Basic Access and Data Read:

import xlwings as xw
app = xw.App(visible=False)
wb = xw.Book('Financial_Report.xlsx')
# Access the "Q1 Summary" sheet by name
summary_sheet = wb.sheets['Q1 Summary']
# Read a range from the accessed sheet
data_range = summary_sheet.range('A1:D10').value
print(data_range)
wb.close()
app.quit()

2. Iterating Through All Worksheets Using Index:

import xlwings as xw
wb = xw.Book('Data_Analysis.xlsx')
for i in range(1, len(wb.sheets) + 1):
    ws = wb.sheets[i] # Access each sheet by its index
    print(f"Processing: {ws.name}")
    # Perform operations, e.g., clear a specific column
    ws.range(f'C:C').clear()

3. Dynamic Sheet Access and Writing Data:

import xlwings as xw
wb = xw.Book()
# Create a new sheet and access it immediately by name
new_sheet = wb.sheets.add('Results')
results_sheet = wb.sheets['Results'] # Access via Item using name
# Write a list of lists to the sheet
results_sheet.range('A1').value = [['Region', 'Sales'], ['North', 45000], ['South', 52000]]
# Access the first sheet by index to add a note
wb.sheets[1].range('A1').value = "Main Dashboard"

4. Error Handling for Non-Existent Sheets:

import xlwings as xw
wb = xw.Book('Project_Plans.xlsx')
sheet_name = 'Gantt_Chart'
try:
    target_sheet = wb.sheets[sheet_name]
    print(f"Found sheet: {target_sheet.name}")
except KeyError:
    print(f"Sheet '{sheet_name}' not found. Available sheets: {[s.name for s in wb.sheets]}")