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 last user who refreshed this PivotTable
refresh_dateGets the last date time when the PivotTable was 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 only be created when there are two or more data fields in the Data region.
Defaultly it is in row region. You can drag it to the row/column region with PivotTable.AddFieldToArea() method .
base_fieldsReturns all base pivot fields in the PivotTable.
pivot_filtersReturns a list of pivot filters.
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 if the value is null.
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 and sets the auto format type of PivotTable.
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_labelsTrue if 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 and sets whether showing expand/collapse buttons.
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_rowIndicates whether showing 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_ascendingIndicates 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 and sets the title of the alter text.
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 and sets 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.
source_typeGets the data source type of the pivot table.
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_titlesIndicates whether PivotItem names should be repeated at the top of each printed page.
repeat_items_on_each_printed_pageIndicates whether pivot item captions on the row area 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 this 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 stripe formatting is applied for column.
show_pivot_style_last_columnIndicates whether the column 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.
calculate_dataCalculates pivottable’s data to cells.
calculate_dataCalculating pivot tables 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
get_fieldsGets the specific pivot field list by the region.
fieldsGets the specific fields by the field type.
get_source_data_connectionsGets the external connection data sources.
get_names_of_source_data_connectionsGets the name of external source data connections.
change_data_sourceSet pivottable’s source data.
Sheet1!$A$1:$C$3
get_sourceGet pivottable’s source data.
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
show_detailShow the detail of one item in the data region to a new Table.
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

PivotShowValuesSetting class PivotTableCalculateOption class