{"id":2287,"date":"2026-09-04T07:32:57","date_gmt":"2026-09-03T23:32:57","guid":{"rendered":"https:\/\/xlwings.net\/blog\/?p=2287"},"modified":"2026-03-28T12:31:38","modified_gmt":"2026-03-28T12:31:38","slug":"how-to-use-worksheetpivottables-in-the-xlwings-api-way","status":"publish","type":"post","link":"https:\/\/xlwings.net\/blog\/how-to-use-worksheetpivottables-in-the-xlwings-api-way\/","title":{"rendered":"How to use Worksheet.PivotTables in the xlwings API way"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">The <code>PivotTables<\/code> member of the <code>Worksheet<\/code> object in the Excel object model is a collection that provides access to all pivot tables on a specific worksheet. Through the <code>xlwings<\/code> 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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In <code>xlwings<\/code>, the <code>PivotTables<\/code> collection is accessed via a <code>Worksheet<\/code> object. The syntax is straightforward: <code>ws.pivot_tables<\/code>, where <code>ws<\/code> is an <code>xlwings<\/code> <code>Sheet<\/code> object representing the worksheet. This returns a collection of <code>PivotTable<\/code> objects. You can iterate through this collection or access a specific pivot table by its name using indexing, e.g., <code>ws.pivot_tables['PivotTable1']<\/code>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Key methods and properties available through the <code>PivotTable<\/code> object in <code>xlwings<\/code> include:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>refresh()<\/code>: Updates the pivot table with the latest data from its source.<\/li>\n\n\n\n<li><code>name<\/code>: Gets or sets the name of the pivot table.<\/li>\n\n\n\n<li><code>source_data<\/code>: Gets or sets the range address of the source data (e.g., <code>'Sheet1!$A$1:$D$100'<\/code>).<\/li>\n\n\n\n<li><code>table_range1<\/code>: Returns an <code>xlwings<\/code> <code>Range<\/code> object representing the entire pivot table, useful for copying or formatting.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">For example, to list all pivot tables on a worksheet and refresh them:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>import xlwings as xw\n\n# Connect to the active workbook\nwb = xw.books.active\nws = wb.sheets&#91;'SalesData']\n\n# Iterate through all pivot tables and refresh each\nfor pt in ws.pivot_tables:\n    print(f\"Refreshing Pivot Table: {pt.name}\")\n    pt.refresh()<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">To create a new pivot table using <code>xlwings<\/code>, you typically use the <code>api<\/code> property to access the underlying Excel VBA object model, as <code>xlwings<\/code> does not have a dedicated high-level method for this. Here is an example that creates a pivot table from a source range:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>import xlwings as xw\n\nwb = xw.books.active\nsource_ws = wb.sheets&#91;'TransactionData']\npivot_ws = wb.sheets&#91;'Analysis']\n\n# Define the source data range\nsource_range = source_ws.range('A1').expand('table')\n\n# Use the Excel API via xlwings to create the pivot table\npc = wb.api.PivotCaches().Create(SourceType=xw.constants.PivotTableSourceType.xlDatabase,\nSourceData=source_range.api)\npt = pc.CreatePivotTable(TableDestination=pivot_ws.range('A3').api,\nTableName='MonthlySales')\n\n# Configure the pivot table fields (using the Excel API)\npt.PivotFields('Region').Orientation = xw.constants.PivotFieldOrientation.xlRowField\npt.PivotFields('Month').Orientation = xw.constants.PivotFieldOrientation.xlColumnField\npt.PivotFields('Sales').Orientation = xw.constants.PivotFieldOrientation.xlDataField<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Another common task is to modify an existing pivot table&#8217;s data source. Suppose you have extended your source data; you can update the pivot cache accordingly:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ws = xw.books.active.sheets&#91;'Dashboard']\npt = ws.pivot_tables&#91;0] # Access the first pivot table in the collection\n\n# Update the source data range to include new rows\/columns\nnew_source_range = \"TransactionData!$A$1:$F$500\"\npt.source_data = new_source_range\npt.refresh()<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>The `PivotTables` member of the `Worksheet` object in the Excel object model is a collection that pr&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[25],"tags":[],"class_list":["post-2287","post","type-post","status-publish","format-standard","hentry","category-xlwings-api-reference"],"_links":{"self":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2287","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/comments?post=2287"}],"version-history":[{"count":2,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2287\/revisions"}],"predecessor-version":[{"id":3490,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2287\/revisions\/3490"}],"wp:attachment":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/media?parent=2287"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/categories?post=2287"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/tags?post=2287"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}