How to use Application.Worksheets in the xlwings API way

In the Excel object model, the Worksheets collection is a crucial component under the Application object, representing all worksheets within a workbook. Through xlwings, a powerful Python library for Excel automation, developers can programmatically access and manipulate these worksheets, enabling dynamic data analysis, reporting, and visualization. The Worksheets member provides methods to add, delete, reference, and iterate over sheets, making it essential for tasks like batch processing or creating dashboards.

Functionality: The Worksheets collection allows you to manage worksheets in an Excel workbook. You can retrieve a specific sheet by name or index, add new sheets, count the total number of sheets, and perform operations across multiple sheets. This is particularly useful for automating repetitive tasks, such as consolidating data from multiple sources or generating standardized reports.

Syntax: In xlwings, the Worksheets collection is accessed via the api property of a workbook or application object. The general syntax is:

• app.worksheets: Returns a collection of all worksheets in the active workbook.
• app.worksheets[index]: Accesses a worksheet by its index (1-based).
• app.worksheets["SheetName"]: Accesses a worksheet by its name.
• app.worksheets.add(): Adds a new worksheet to the workbook.
• app.worksheets.count: Returns the number of worksheets.

Parameters for methods like add() include:

• before: Specifies the sheet before which the new sheet is added (can be a sheet object or index).
• after: Specifies the sheet after which the new sheet is added.
• count: The number of sheets to add (default is 1).
  If not specified, the new sheet is added after all existing sheets.

Example Code: Below are practical xlwings API examples demonstrating the use of the Worksheets member:

1. Accessing Worksheets by Index and Name:

import xlwings as xw
app = xw.App(visible=False) # Start Excel in background
wb = app.books.open("example.xlsx")
# Access first worksheet
ws1 = wb.worksheets[0]
# Access worksheet by name
ws2 = wb.worksheets["DataSheet"]
print(f"First sheet name: {ws1.name}, DataSheet range A1: {ws2.range('A1').value}")
wb.close()
app.quit()

2. Adding and Counting Worksheets:

import xlwings as xw
app = xw.App(visible=True)
wb = app.books.add()
# Add a new sheet after the first one
new_sheet = wb.worksheets.add(after=wb.worksheets[0])
new_sheet.name = "Analysis"
# Count total worksheets
sheet_count = wb.worksheets.count
print(f"Total sheets: {sheet_count}")
# Save and close
wb.save("new_workbook.xlsx")
app.quit()

3. Iterating Over All Worksheets:

import xlwings as xw
app = xw.App(visible=False)
wb = app.books.open("data.xlsx")
# Loop through each worksheet and print names
for ws in wb.worksheets:
    print(f"Processing sheet: {ws.name}")
    # Example: Clear content from column A
ws.range("A:A").clear()
wb.save()
app.quit()

4. Deleting a Worksheet:

import xlwings as xw
app = xw.App(visible=False)
wb = app.books.open("report.xlsx")
# Delete a sheet by name
if "TempSheet" in [sheet.name for sheet in wb.worksheets]:
    wb.worksheets["TempSheet"].delete()
wb.save()
app.quit()