PivotTable class

PivotTable class

Summary description for PivotTable.

The PivotTable type exposes the following members:

Properties

PropertyDescription
is_excel_2003_compatibleSpecifies whether the PivotTable is compatible for Excel2003 when refreshing PivotTable,
if true, a string must be less than or equal to 255 characters, so if the string is greater than 255 characters,
it will be truncated. if false, a string will not have the aforementioned restriction.
The default value is true.
refreshed_by_whoGets the name of the user who last refreshed the PivotTable
refresh_dateGets the date when the PivotTable was last refreshed.
pivot_table_style_nameGets and sets the pivottable style name.
pivot_table_style_typeGets and sets the built-in pivot table style.
column_fieldsReturns a PivotFields object that are currently shown as column fields.
row_fieldsReturns a PivotFields object that are currently shown as row fields.
page_fieldsReturns a PivotFields object that are currently shown as page fields.
data_fieldsGets a PivotField object that represents all the data fields in a PivotTable.
Read-only.It would be init only when there are two or more data fields in the DataPiovtFiels.
It only use to add DataPivotField to the PivotTable row/column area . Default is in row area.
data_fieldGets a PivotField object that represents all the data fields in a PivotTable.
Read-only.It would be init only when there are two or more data fields in the DataPiovtFiels.
It only use to add DataPivotField to the PivotTable row/column area . Default is in row area.
base_fieldsReturns a PivotFields object that includes all fields in the PivotTable report
pivot_filtersReturns a PivotFilterCollection object.
column_rangeReturns a CellArea object that represents the range
that contains the column area in the PivotTable report. Read-only.
row_rangeReturns a CellArea object that represents the range
that contains the row area in the PivotTable report. Read-only.
data_body_rangeReturns a CellArea object that represents the range that contains the data area
in the list between the header row and the insert row. Read-only.
table_range1Returns a CellArea object that represents the range containing the entire PivotTable report,
but doesn’t include page fields. Read-only.
table_range2Returns a CellArea object that represents the range containing the entire PivotTable report,
includes page fields. Read-only.
column_grandIndicates whether the PivotTable report shows grand totals for columns.
is_grid_drop_zonesIndicates whether the PivotTable report displays classic pivottable layout.
(enables dragging fields in the grid)
row_grandIndicates whether the PivotTable report shows grand totals for rows.
display_null_stringIndicates whether the PivotTable report displays a custom string
in cells that contain null values.
null_stringGets the string displayed in cells that contain null values
when the DisplayNullString property is true.The default value is an empty string.
display_error_stringIndicates whether the PivotTable report displays a custom string in cells that contain errors.
data_field_header_nameGets and sets the name of the value area field header in the PivotTable.
error_stringGets the string displayed in cells that contain errors
when the DisplayErrorString property is true.The default value is an empty string.
is_auto_formatIndicates whether the PivotTable report is automatically formatted.
Checkbox “autoformat table " which is in pivottable option for Excel 2003
autofit_column_width_on_updateIndicates whether autofitting column width on update
auto_format_typeGets the PivotTable auto format type.
has_blank_rowsIndicates whether to add blank rows.
This property only applies for the PivotTable auto format types which needs to add blank rows.
merge_labelsIndicates whether the specified PivotTable report’s outer-row item, column item, subtotal,
and grand total labels use merged cells.
preserve_formattingIndicates whether formatting is preserved when the PivotTable is refreshed or recalculated.
show_drillGets whether expand/collapse buttons is shown.
enable_drilldownGets whether drilldown is enabled.
enable_field_dialogIndicates whether the PivotTable Field dialog box is available
when the user double-clicks the PivotTable field.
enable_field_listGets whether enable the field list for the PivotTable.
enable_wizardIndicates whether the PivotTable Wizard is available.
subtotal_hidden_page_itemsIndicates whether hidden page field items in the PivotTable report
are included in row and column subtotals, block totals, and grand totals.
The default value is False.
grand_total_nameReturns the text string label that is displayed in the grand total column or row heading.
The default value is the string “Grand Total”.
manual_updateIndicates whether the PivotTable report is recalculated only at the user’s request.
is_multiple_field_filtersSpecifies a boolean value that indicates whether the fields of a PivotTable can have multiple filters set on them.
missing_items_limitSpecifies a boolean value that indicates whether the fields of a PivotTable can have multiple filters set on them.
enable_data_value_editingSpecifies a boolean value that indicates whether the user is allowed to edit the cells in the data area of the pivottable.
Enable cell editing in the values area
show_data_tipsSpecifies a boolean value that indicates whether tooltips should be displayed for PivotTable data cells.
show_member_property_tipsSpecifies a boolean value that indicates whether member property information should be omitted from PivotTable tooltips.
show_values_rowSpecifies a boolean value that indicates whether show values row.
show the values row
show_empty_colSpecifies a boolean value that indicates whether to include empty columns in the table
show_empty_rowSpecifies a boolean value that indicates whether to include empty rows in the table.
field_list_sort_ascendingSpecifies a boolean value that indicates whether fields in the PivotTable are sorted in non-default order in the field list.
print_drillSpecifies a boolean value that indicates whether drill indicators should be printed.
print expand/collapse buttons when displayed on pivottable.
alt_text_titleGets the title of the altertext
alt_text_descriptionGets the description of the alt text
nameGets the name of the PivotTable
column_header_captionGets the Column Header Caption of the PivotTable.
indentSpecifies the indentation increment for compact axis and can be used to set the Report Layout to Compact Form.
row_header_captionGets the Row Header Caption of the PivotTable.
show_row_header_captionIndicates whether row header caption is shown in the PivotTable report
Indicates whether Display field captions and filter drop downs
custom_list_sortIndicates whether consider built-in custom list when sort data
pivot_format_conditionsGets the Format Conditions of the pivot table.
page_field_orderGets the order in which page fields are added to the PivotTable report’s layout.
page_field_wrap_countGets the number of page fields in each column or row in the PivotTable report.
tagGets a string saved with the PivotTable report.
save_dataIndicates whether data for the PivotTable report is saved with the workbook.
refresh_data_on_opening_fileIndicates whether Refresh Data when Opening File.
refresh_data_flagIndicates whether Refreshing Data or not.
external_connection_data_sourceGets the external connection data source.
data_sourceGets and sets the data source of the pivot table.
pivot_formatsGets the collection of formats applied to PivotTable.
item_print_titlesA bit that specifies whether pivot item captions on the row axis
are repeated on each printed page for pivot fields in tabular form.
print_titlesIndicates whether the print titles for the worksheet are set based
on the PivotTable report. The default value is false.
display_immediate_itemsIndicates whether items in the row and column areas are visible
when the data area of the PivotTable is empty. The default value is true.
is_selectedIndicates whether the PivotTable is selected.
show_pivot_style_row_headerIndicates whether the row header in the pivot table should have the style applied.
show_pivot_style_column_headerIndicates whether the column header in the pivot table should have the style applied.
show_pivot_style_row_stripesIndicates whether row stripe formatting is applied.
show_pivot_style_column_stripesIndicates whether column stripe formatting is applied.
show_pivot_style_last_columnIndicates whether column stripe formatting is applied.

Methods

MethodDescription
remove_fieldRemoves a field from specific field area
remove_fieldRemoves a field from specific field area
remove_fieldRemove field from specific field area
add_field_to_areaAdds the field to the specific area.
add_field_to_areaAdds the field to the specific area.
add_field_to_areaAdds the field to the specific area.
add_calculated_fieldAdds a calculated field to pivot field.
add_calculated_fieldAdds a calculated field to pivot field and drag it to data area.
moveMoves the PivotTable to a different location in the worksheet.
moveMoves the PivotTable to a different location in the worksheet.
refresh_dataRefreshes pivottable’s data and setting from it’s data source.
refresh_dataRefreshes pivottable’s data and setting from it’s data source with options.
formatFormats selected area of the PivotTable.
formatFormat the cell in the pivottable area
set_auto_group_fieldSets auto field group by the PivotTable.
set_auto_group_fieldSets auto field group by the PivotTable.
set_manual_group_fieldSets manual field group by the PivotTable.
set_manual_group_fieldSets manual field group by the PivotTable.
set_manual_group_fieldSets manual field group by the PivotTable.
set_manual_group_fieldSets manual field group by the PivotTable.
set_ungroupSets ungroup by the PivotTable
set_ungroupSets ungroup by the PivotTable
copy_styleCopies named style from another pivot table.
show_report_filter_pageShow all the report filter pages according to PivotField, the PivotField must be located in the PageFields.
show_report_filter_page_by_nameShow all the report filter pages according to PivotField’s name, the PivotField must be located in the PageFields.
show_report_filter_page_by_indexShow all the report filter pages according to the position index in the PageFields
fieldsGets the specific fields by the field type.
change_data_sourceSet pivottable’s source data.
Sheet1!$A$1:$C$3
get_sourceGet pivottable’s source data.
calculate_dataCalculates pivottable’s data to cells.
clear_dataClear PivotTable’s data and formatting
calculate_rangeCalculates pivottable’s range.
format_allFormat all the cell in the pivottable area
format_rowFormat the row data in the pivottable area
get_horizontal_breaksget pivot table row index list of horizontal pagebreaks
show_in_compact_formLayouts the PivotTable in compact form.
show_in_outline_formLayouts the PivotTable in outline form.
show_in_tabular_formLayouts the PivotTable in tabular form.
get_cell_by_display_nameGets the Cell object by the display name of PivotField.
get_childrenGets the Children Pivot Tables which use this PivotTable data as data source.

Example

from aspose.cells import FormatConditionType, OperatorType, Workbook
from aspose.cells.pivot import PivotFieldType, PivotFilterType, PivotTableStyleType
from aspose.pydrawing import Color

book = Workbook()
sheet = book.worksheets[0]
cells = sheet.cells
cells.get(0, 0).value = "fruit"
cells.get(1, 0).value = "grape"
cells.get(2, 0).value = "blueberry"
cells.get(3, 0).value = "kiwi"
cells.get(4, 0).value = "cherry"
cells.get(5, 0).value = "grape"
cells.get(6, 0).value = "blueberry"
cells.get(7, 0).value = "kiwi"
cells.get(8, 0).value = "cherry"
cells.get(0, 1).value = "year"
cells.get(1, 1).value = 2020
cells.get(2, 1).value = 2020
cells.get(3, 1).value = 2020
cells.get(4, 1).value = 2020
cells.get(5, 1).value = 2021
cells.get(6, 1).value = 2021
cells.get(7, 1).value = 2021
cells.get(8, 1).value = 2021
cells.get(0, 2).value = "amount"
cells.get(1, 2).value = 50
cells.get(2, 2).value = 60
cells.get(3, 2).value = 70
cells.get(4, 2).value = 80
cells.get(5, 2).value = 90
cells.get(6, 2).value = 100
cells.get(7, 2).value = 110
cells.get(8, 2).value = 120
pivots = sheet.pivot_tables
pivotIndex = pivots.add("=Sheet1!A1:C9", "A12", "TestPivotTable")
pivot = pivots[pivotIndex]
pivot.add_field_to_area(PivotFieldType.ROW, "fruit")
pivot.add_field_to_area(PivotFieldType.COLUMN, "year")
pivot.add_field_to_area(PivotFieldType.DATA, "amount")
pivot.pivot_table_style_type = PivotTableStyleType.PIVOT_TABLE_STYLE_MEDIUM10
# Change PivotField's attributes
rowField = pivot.row_fields[0]
rowField.display_name = "custom display name"
# Add PivotFilter
index = pivot.pivot_filters.add(0, PivotFilterType.COUNT)
filter = pivot.pivot_filters[index]
filter.auto_filter.filter_top10(0, False, False, 2)
# Add PivotFormatCondition
formatIndex = pivot.pivot_format_conditions.add()
pfc = pivot.pivot_format_conditions[formatIndex]
fcc = pfc.format_conditions
fcc.add_area(pivot.data_body_range)
idx = fcc.add_condition(FormatConditionType.CELL_VALUE)
fc = fcc[idx]
fc.formula1 = "100"
fc.operator = OperatorType.GREATER_OR_EQUAL
fc.style.background_color = Color.red
pivot.refresh_data()
pivot.calculate_data()
# do your business
book.save("out.xlsx")

See Also

PivotPageFields class PivotTableCollection class