How to use Worksheet.ListObjects in the xlwings API way

In Excel, the ListObjects collection represents all the tables (ListObject) on a specific worksheet. Tables are powerful features for managing and analyzing structured data, offering built-in filtering, sorting, and easy referencing. Through the ListObjects property of a Worksheet object in xlwings, you can programmatically access, create, and manipulate these tables, enabling automation of data organization and analysis tasks.

Functionality:
The ListObjects property provides access to the collection of tables within a worksheet. You can use it to:

• Retrieve a specific table by its name or index.
• Iterate through all tables to perform batch operations.
• Add new tables based on a given range of data.
• Check the number of tables present.

Syntax:
In xlwings, the ListObjects property is accessed from a Worksheet object. The general call format is:

worksheet.api.ListObjects

This returns a COM object representing the Excel ListObjects collection. To work with it more intuitively in xlwings, you often use methods like add() or access items directly.

To create a new table:

worksheet.api.ListObjects.Add(SourceType, Source, LinkSource, HasHeaders, Destination)

• SourceType: Specifies the source of the data. Typically use 1 (xlSrcRange) for a worksheet range.
• Source: The range address as a string (e.g., “A1:D10”) or an xlwings Range object.
• LinkSource: Usually False for data within the workbook.
• HasHeaders: Set to True if the range includes headers; False otherwise.
• Destination: Optional; used if SourceType is xlSrcExternal. Can be omitted for range sources.

To reference an existing table by name:

table = worksheet.api.ListObjects("TableName")

Example:
Consider a worksheet with sales data in the range A1:C5. The following xlwings code demonstrates using ListObjects to create a table, access its properties, and iterate through tables.

import xlwings as xw

# Connect to the active workbook and worksheet
wb = xw.Book.active
ws = wb.sheets['Sheet1']

# Create a table from the range A1:C5
source_range = ws.range('A1:C5')
table = ws.api.ListObjects.Add(
SourceType=1, # xlSrcRange
Source=source_range.api,
LinkSource=False,
HasHeaders=True,
Destination=None
)
table.Name = 'SalesTable' # Set a name for the table

# Access the table by name
sales_table = ws.api.ListObjects('SalesTable')
print(f"Table range: {sales_table.Range.Address}")

# Iterate through all tables in the worksheet
for tbl in ws.api.ListObjects:
    print(f"Found table: {tbl.Name}")

# Count the number of tables
table_count = ws.api.ListObjects.Count
print(f"Total tables: {table_count}")

# Add a total row to the table
sales_table.ShowTotals = True
sales_table.ListColumns(3).TotalsCalculation = -4157 # xlTotalsCalculationSum

# Resize the table to include new data (e.g., extending to row 6)
sales_table.Resize(ws.range('A1:C6').api)