How to use Application.Sheets in the xlwings API way

The Application.Sheets property in Excel’s object model provides a collection of all sheets within the open workbook, encompassing both worksheets and chart sheets. In xlwings, this is accessed via the app object, which represents the Excel application instance. The primary function is to retrieve a list or a specific sheet, enabling operations across multiple sheets or referencing sheets by name or index. This is essential for automating tasks that involve iterating through all sheets, checking their properties, or performing bulk operations.

Syntax in xlwings:
The property is accessed as app.sheets. It returns a Sheets collection object. To reference a specific sheet, you can use indexing or a sheet name.

• app.sheets: Returns the collection of all sheets.
• app.sheets[index]: Returns the sheet at the specified index (1-based).
• app.sheets[name]: Returns the sheet with the given name.

Parameters:

• index: An integer representing the sheet’s position in the workbook (starting from 1). For example, app.sheets[1] refers to the first sheet.
• name: A string representing the exact name of the sheet, such as app.sheets["Sheet1"].

Code Examples:

1. Iterate through all sheets and print names:

import xlwings as xw
app = xw.App(visible=False)
wb = app.books.open('example.xlsx')
for sheet in app.sheets:
    print(sheet.name)
wb.close()
app.quit()

2. Access a specific sheet by name and modify a cell:

import xlwings as xw
app = xw.App(visible=False)
wb = app.books.open('example.xlsx')
target_sheet = app.sheets["DataSheet"]
target_sheet.range("A1").value = "Updated Value"
wb.save()
wb.close()
app.quit()

3. Count the number of sheets and check types:

import xlwings as xw
app = xw.App(visible=False)
wb = app.books.open('example.xlsx')
sheet_count = len(app.sheets)
print(f"Total sheets: {sheet_count}")
# To check if a sheet is a worksheet (vs. chart sheet), use its type property
for sheet in app.sheets:
if sheet.type == 'chart':
    print(f"{sheet.name} is a chart sheet.")
else:
    print(f"{sheet.name} is a worksheet.")
wb.close()
app.quit()

4. Add a new sheet and rename it using the collection:

import xlwings as xw
app = xw.App(visible=False)
wb = app.books.open('example.xlsx')
new_sheet = wb.sheets.add()
new_sheet.name = "Analysis"
# Access via app.sheets to confirm
print("Sheet names:", [s.name for s in app.sheets])
wb.save()
wb.close()
app.quit()