PivotField class

PivotField class

Represents a field in a PivotTable report.

The PivotField type exposes the following members:

Properties

PropertyDescription
pivot_itemsGets the pivot items of the pivot field
rangeGets the group range of the pivot field
group_settingsGets the group settings of the pivot field.
is_calculated_fieldIndicates whether the specified PivotTable field is calculated field.
base_indexRepresents the PivotField index in the base PivotFields.
positionRepresents the index of PivotField in the region.
nameRepresents the name of PivotField.
display_nameRepresents the PivotField display name.
is_auto_subtotalsIndicates whether the specified field shows automatic subtotals. Default is true.
drag_to_columnIndicates whether the specified field can be dragged to the column position.
The default value is true.
drag_to_hideIndicates whether the specified field can be dragged to the hide position.
The default value is true.
drag_to_rowIndicates whether the specified field can be dragged to the row position.
The default value is true.
drag_to_pageIndicates whether the specified field can be dragged to the page position.
The default value is true.
drag_to_dataIndicates whether the specified field can be dragged to the data position.
The default value is true.
is_multiple_item_selection_allowedindicates whether the field can have multiple items
selected in the page field
The default value is false.
is_repeat_item_labelsIndicates whether repeating labels of the field in the region.
The default value is false.
is_include_new_items_in_filterIndicates whether including new items to the field in manual filter.
The default value is false.
is_insert_page_breaks_between_itemsIndicates whether inserting page breaks after each item.
The default value is false.
show_all_itemsIndicates whether all items displays in the PivotTable report,
even if they don’t contain summary data.
show items with no data
The default value is false.
non_auto_sort_defaultIndicates whether a sort operation that will be applied to this pivot field is an autosort operation or a simple data sort.
is_auto_sortIndicates whether the specified PivotTable field is automatically sorted.
is_ascend_sortIndicates whether the specified PivotTable field is autosorted ascending.
sort_settingGets all settings of auto sorting
auto_sort_fieldRepresents the index of field which is auto sorted.
-1 means PivotField itself,others means the position of the data fields.
is_auto_showIndicates whether the specified PivotTable field is automatically shown,only valid for excel 2003.
is_ascend_showIndicates whether the specified PivotTable field is autoshown ascending.
auto_show_countRepresent the number of top or bottom items
that are automatically shown in the specified PivotTable field.
auto_show_fieldRepresents auto show field index. -1 means PivotField itself.
It should be the index of the data fields.
functionRepresents the function used to summarize the PivotTable data field.
show_values_settingGets the settings of showing values as when the ShowDataAs calculation is in use.
data_display_formatRepresents how to display the values in a data field of the pivot report.
base_field_indexRepresents the base field for a custom calculation when the ShowDataAs calculation is in use.
base_item_positionRepresents the item in the base field for a custom calculation when the ShowDataAs calculation is in use.
Valid only for data fields.
Because PivotItemPosition.Custom is only for read,if you need to set PivotItemPosition.Custom,
please set PivotField.BaseItemIndex attribute.
base_item_indexRepresents the item in the base field for a custom calculation when the ShowDataAs calculation is in use.
Valid only for data fields.
current_page_itemRepresents the current page item showing for the page field (valid only for page fields).
numberRepresents the built-in display format of numbers and dates.
insert_blank_rowIndicates whether inserting blank line after each item.
show_subtotal_at_topwhen ShowInOutlineForm is true, then display subtotals at the top of the list of items instead of at the bottom
show_in_outline_formIndicates whether layout this field in outline form on the Pivot Table view
number_formatRepresents the custom display format of numbers and dates.
itemsGet all labels of pivot items in this field.
original_itemsGet the original base items;
item_countGets the count of the base items in this pivot field.
show_compactIndicates whether display labels from the next field in the same column on the Pivot Table view

Methods

MethodDescription
group_byAutomatically group the field with internal
group_byGroup the file by the date group types.
group_byGroup the file by number.
group_byCustom group the field.
sort_bySorts this pivot field.
sort_bySorts this pivot field.
hide_itemSets whether the specific PivotItem in a data field is hidden.
hide_itemSets whether the specific PivotItem in a data field is hidden.
get_pivot_filter_by_typeGets the pivot filter of the pivot field by type
get_pivot_filtersGets the pivot filters of the pivot field
get_filtersGets all pivot filters of this pivot field.
init_pivot_itemsInit the pivot items of the pivot field
ungroupUngroup the pivot field.
get_calculated_field_formulaGet the formula string of the specified calculated field .
set_subtotalsSets whether the specified field shows that subtotals.
get_subtotalsIndicates whether showing specified subtotal.
show_values_asShows values of data field as different display format when the ShowDataAs calculation is in use.
is_hidden_itemGets whether the specific PivotItem is hidden.
is_hidden_item_detailGets whether hidding the detail of the specific PivotItem..
hide_item_detailSets whether the specific PivotItem in a pivot field is hidden detail.
hide_detailSets whether the PivotItems in a pivot field is hidden detail.That is collapse/expand this field.
add_calculated_itemAdd a calculated formula item to the pivot field.

Example

from aspose.cells import Workbook
from aspose.cells.pivot import PivotFieldType, PivotTableStyleType

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"
pivot.refresh_data()
pivot.calculate_data()
# do your business
book.save("out.xlsx")

See Also