How to use Worksheet.PivotTables in the xlwings API way

The PivotTables member of the Worksheet object in the Excel object model is a collection that provides access to all pivot tables on a specific worksheet. Through the xlwings API, this collection allows for programmatic control over pivot tables, enabling tasks such as creating new pivot tables, modifying existing ones, refreshing data, or extracting information. This is particularly useful for automating reporting, data analysis workflows, and ensuring that pivot tables reflect the latest data without manual intervention.

In xlwings, the PivotTables collection is accessed via a Worksheet object. The syntax is straightforward: ws.pivot_tables, where ws is an xlwings Sheet object representing the worksheet. This returns a collection of PivotTable objects. You can iterate through this collection or access a specific pivot table by its name using indexing, e.g., ws.pivot_tables['PivotTable1'].

Key methods and properties available through the PivotTable object in xlwings include:

• refresh(): Updates the pivot table with the latest data from its source.
• name: Gets or sets the name of the pivot table.
• source_data: Gets or sets the range address of the source data (e.g., 'Sheet1!$A$1:$D$100').
• table_range1: Returns an xlwings Range object representing the entire pivot table, useful for copying or formatting.

For example, to list all pivot tables on a worksheet and refresh them:

import xlwings as xw

# Connect to the active workbook
wb = xw.books.active
ws = wb.sheets['SalesData']

# Iterate through all pivot tables and refresh each
for pt in ws.pivot_tables:
    print(f"Refreshing Pivot Table: {pt.name}")
    pt.refresh()

To create a new pivot table using xlwings, you typically use the api property to access the underlying Excel VBA object model, as xlwings does not have a dedicated high-level method for this. Here is an example that creates a pivot table from a source range:

import xlwings as xw

wb = xw.books.active
source_ws = wb.sheets['TransactionData']
pivot_ws = wb.sheets['Analysis']

# Define the source data range
source_range = source_ws.range('A1').expand('table')

# Use the Excel API via xlwings to create the pivot table
pc = wb.api.PivotCaches().Create(SourceType=xw.constants.PivotTableSourceType.xlDatabase,
SourceData=source_range.api)
pt = pc.CreatePivotTable(TableDestination=pivot_ws.range('A3').api,
TableName='MonthlySales')

# Configure the pivot table fields (using the Excel API)
pt.PivotFields('Region').Orientation = xw.constants.PivotFieldOrientation.xlRowField
pt.PivotFields('Month').Orientation = xw.constants.PivotFieldOrientation.xlColumnField
pt.PivotFields('Sales').Orientation = xw.constants.PivotFieldOrientation.xlDataField

Another common task is to modify an existing pivot table’s data source. Suppose you have extended your source data; you can update the pivot cache accordingly:

ws = xw.books.active.sheets['Dashboard']
pt = ws.pivot_tables[0] # Access the first pivot table in the collection

# Update the source data range to include new rows/columns
new_source_range = "TransactionData!$A$1:$F$500"
pt.source_data = new_source_range
pt.refresh()