PivotTable

class PivotTable

The pivot table object is a member of the pivot_tables collection:

>>> import xlwings as xw
>>> mysheet = xw.books['Book1'].sheets[0]
>>> mysheet.pivot_tables[0]  # or mysheet.pivot_tables['PivotTable1']
<PivotTable 'PivotTable1' in Sheet1>

Fields are placed via the four areas of Excel’s field list, see rows, columns, filters and values:

>>> pt = mysheet.pivot_tables['PivotTable1']
>>> pt.rows.add('Region')
>>> pt.values.add('Sales', function='sum', number_format='#,##0')
>>> pt.layout = 'tabular'
>>> pt.refresh()

Added in version 0.37.3.

property api: Any

Returns the native object (pywin32 or appscript obj) of the engine being used.

property columns: PivotFields

The fields in the Columns area, see PivotFields.

property data_body_range: Range | None

The range of the values area, or None if the pivot table has no value fields.

Not yet available on xlwings Lite and xlwings Server.

delete()

Deletes the pivot table.

property field_names: list[str]

The names of the source fields (the column headers of the source data), i.e. what can be passed to rows.add(), columns.add(), filters.add() and values.add().

property filters: PivotFields

The fields in the Filters area, see PivotFields.

property layout: Literal['compact', 'outline', 'tabular'] | None

"compact", "outline" or "tabular". Returns None if the row fields use mixed layouts.

On xlwings Lite and xlwings Server, reading it returns the layout as it was when the script started, or the value set in the same script.

Type:

Returns or sets the report layout

Setter type:

str

property name: str

Returns or sets the name of the pivot table.

property parent: Sheet

Returns the sheet the pivot table is on.

property range: Range

The range of the pivot table report, excluding the filters area.

Not yet available on xlwings Lite and xlwings Server.

refresh()

Refreshes the pivot table from its source data.

property rows: PivotFields

The fields in the Rows area, see PivotFields.

property show_column_grand_totals: bool

Returns or sets whether the grand totals for columns are shown.

property show_row_grand_totals: bool

Returns or sets whether the grand totals for rows are shown.

property values: PivotValueFields

The fields in the Values area, see PivotValueFields.