PivotField
PivotField class
Represents a field in a PivotTable report.
class PivotField;
Properties
| Property | Type | Description |
|---|---|---|
| pivotItems | PivotItemCollection | Readonly. Gets the pivot items of the pivot field |
| groupSettings | PivotFieldGroupSettings | Readonly. Gets the group settings of the pivot field. |
| isCalculatedField | boolean | Readonly. Indicates whether the this pivot field is calculated field. |
| isValuesField | boolean | Readonly. Indicates whether this field represents values field. |
| baseIndex | number | Represents the index in the source pivot fields. |
| position | number | Readonly. Represents the index of PivotField in the region. |
| regionType | PivotFieldType | Readonly. Specifies the region of the PivotTable that this field is displayed. |
| name | string | Represents the name of PivotField. |
| displayName | string | Represents the display name of pivot field in the pivot table view. |
| isAutoSubtotals | boolean | Indicates whether the specified field shows automatic subtotals. Default is true. |
| dragToColumn | boolean | Indicates whether the specified field can be dragged to the column position. The default value is true. |
| dragToHide | boolean | Indicates whether the specified field can be dragged to the hide region. The default value is true. |
| dragToRow | boolean | Indicates whether the specified field can be dragged to the row region. The default value is true. |
| dragToPage | boolean | Indicates whether the specified field can be dragged to the page position. The default value is true. |
| dragToData | boolean | Indicates whether the specified field can be dragged to the values region. The default value is true. |
| isMultipleItemSelectionAllowed | boolean | Indicates whether multiple items could be selected in the page field. The default value is false. |
| isRepeatItemLabels | boolean | Indicates whether to repeat labels of the field in the region. The default value is false. |
| isIncludeNewItemsInFilter | boolean | Indicates whether to include new items to the field in manual filter. The default value is false. |
| isInsertPageBreaksBetweenItems | boolean | Indicates whether to insert page breaks after each item. The default value is false. |
| showAllItems | boolean | Indicates whether to display all items in the PivotTable view, even if they don’t contain summary data. The default value is false. |
| nonAutoSortDefault | boolean | Indicates whether a sort operation that will be applied to this pivot field is an autosort operation or a simple data sort. |
| isAutoSort | boolean | Indicates whether the items of this PivotTable field are automatically sorted. |
| isAscendSort | boolean | Indicates whether the items of this pivot field is autosorted ascending. |
| sortSetting | PivotFieldSortSetting | Readonly. Gets all settings of auto sorting |
| autoSortField | number | Represents the index of field which is auto sorted. -1 means PivotField itself,others means the position of the data fields. |
| isAutoShow | boolean | Indicates whether the specified PivotTable field is automatically shown. |
| isAscendShow | boolean | Indicates whether the specified PivotTable field is autoshown ascending. |
| autoShowCount | number | Represent the number of top or bottom items that are automatically shown in the specified PivotTable field. |
| autoShowField | number | Represents auto show field index. -1 means PivotField itself. It should be the index of the data fields. |
| function | ConsolidationFunction | Represents the function used to summarize this PivotTable data field. |
| showValuesSetting | PivotShowValuesSetting | Readonly. Gets the settings of showing values as when the ShowDataAs calculation is in use. |
| currentPageItem | number | Represents the current selected page item of the page field to filter data. Only valid for page fields. |
| insertBlankRow | boolean | Indicates whether to insert a blank line after each item. |
| showSubtotalAtTop | boolean | Indicates whether to display subtotals at the top or bottom of items when ShowInOutlineForm is true, then |
| showInOutlineForm | boolean | Indicates whether to layout this field in outline form on the Pivot Table view. |
| number | number | Represents the built-in display format of numbers and dates. |
| numberFormat | string | Represents the custom display format of numbers and dates. |
| items | string[] | Readonly. Get all labels of pivot items in this field. |
| originalItems | string[] | Readonly. Get the original base items; |
| itemCount | number | Readonly. Gets the count of the base items in this pivot field. |
| showCompact | boolean | Indicates whether to display labels of the next field in the same column on the Pivot Table view |
Methods
| Method | Description |
|---|---|
| initPivotItems() | Init the pivot items of the pivot field |
| groupBy(number, boolean) | Automatically group the field with internal |
| groupBy(Date, Date, PivotGroupByType[], number, boolean) | Group the file by the date group types. |
| groupBy(number, number, number, boolean) | Group the file by number. |
| groupBy(CustomPiovtFieldGroupItem[], boolean) | Custom group the field. |
| ungroup() | Ungroup the pivot field. |
| getPivotFilterByType(PivotFilterType) | Gets the pivot filter of the pivot field by type |
| getFilters() | Gets all pivot filters applied for this pivot field. |
| clearFilter() | Clears filter setting on this pivot field. |
| filterTop10(number, PivotFilterType, boolean, number) | Filters by values of data pivot field. |
| filterByValue(number, PivotFilterType, number, number) | Filters by values of data pivot field. |
| filterByLabel(PivotFilterType, string, string) | Filters by captions of row or column pivot field. |
| filterByDate(PivotFilterType, Date, Date) | Filters by date values of row or column pivot field. |
| getFormula() | Gets the formula of the calculated field . Only works for calculated field. |
| setSubtotals(PivotFieldSubtotalType, boolean) | Sets how to subtotal the specified field. |
| getSubtotals(PivotFieldSubtotalType) | Indicates whether to show specified subtotal for this pivot field. |
| sortBy(SortOrder, number) | Sorts this pivot field. |
| sortBy(SortOrder, number, PivotLineType, string) | Sorts this pivot field. |
| showValuesAs(PivotFieldDataDisplayFormat, number, PivotItemPositionType, number) | Shows values of data field as different display format when the ShowDataAs calculation is in use. |
| isHiddenItem(number) | Gets whether the specific PivotItem is hidden. |
| hideItem(number, boolean) | Sets whether the specific PivotItem in a data field is hidden. |
| hideItem(string, boolean) | Sets whether the specific PivotItem in a data field is hidden. |
| isHiddenItemDetail(number) | Gets whether to hide the detail of the specific PivotItem.. |
| hideItemDetail(number, boolean) | Sets whether the specific PivotItem in a pivot field is hidden detail. |
| hideDetail(boolean) | Sets whether the detail of all PivotItems in a pivot field are hidden. That is collapse/expand this field. |
| addCalculatedItem(string, string) | Add a calculated formula item to the pivot field. |
pivotItems
Readonly. Gets the pivot items of the pivot field
pivotItems : PivotItemCollection;
groupSettings
Readonly. Gets the group settings of the pivot field.
groupSettings : PivotFieldGroupSettings;
Remarks
If this field is not grouped, Null will be returned.
isCalculatedField
Readonly. Indicates whether the this pivot field is calculated field.
isCalculatedField : boolean;
isValuesField
Readonly. Indicates whether this field represents values field.
isValuesField : boolean;
Remarks
Only works when there are two or more data fields in the pivot table view.
baseIndex
Represents the index in the source pivot fields.
baseIndex : number;
position
Readonly. Represents the index of PivotField in the region.
position : number;
regionType
Readonly. Specifies the region of the PivotTable that this field is displayed.
regionType : PivotFieldType;
name
Represents the name of PivotField.
name : string;
displayName
Represents the display name of pivot field in the pivot table view.
displayName : string;
isAutoSubtotals
Indicates whether the specified field shows automatic subtotals. Default is true.
isAutoSubtotals : boolean;
dragToColumn
Indicates whether the specified field can be dragged to the column position. The default value is true.
dragToColumn : boolean;
dragToHide
Indicates whether the specified field can be dragged to the hide region. The default value is true.
dragToHide : boolean;
dragToRow
Indicates whether the specified field can be dragged to the row region. The default value is true.
dragToRow : boolean;
dragToPage
Indicates whether the specified field can be dragged to the page position. The default value is true.
dragToPage : boolean;
dragToData
Indicates whether the specified field can be dragged to the values region. The default value is true.
dragToData : boolean;
isMultipleItemSelectionAllowed
Indicates whether multiple items could be selected in the page field. The default value is false.
isMultipleItemSelectionAllowed : boolean;
isRepeatItemLabels
Indicates whether to repeat labels of the field in the region. The default value is false.
isRepeatItemLabels : boolean;
isIncludeNewItemsInFilter
Indicates whether to include new items to the field in manual filter. The default value is false.
isIncludeNewItemsInFilter : boolean;
isInsertPageBreaksBetweenItems
Indicates whether to insert page breaks after each item. The default value is false.
isInsertPageBreaksBetweenItems : boolean;
showAllItems
Indicates whether to display all items in the PivotTable view, even if they don’t contain summary data. The default value is false.
showAllItems : boolean;
nonAutoSortDefault
Indicates whether a sort operation that will be applied to this pivot field is an autosort operation or a simple data sort.
nonAutoSortDefault : boolean;
isAutoSort
Indicates whether the items of this PivotTable field are automatically sorted.
isAutoSort : boolean;
isAscendSort
Indicates whether the items of this pivot field is autosorted ascending.
isAscendSort : boolean;
sortSetting
Readonly. Gets all settings of auto sorting
sortSetting : PivotFieldSortSetting;
autoSortField
Represents the index of field which is auto sorted. -1 means PivotField itself,others means the position of the data fields.
autoSortField : number;
isAutoShow
Indicates whether the specified PivotTable field is automatically shown.
isAutoShow : boolean;
Remarks
Only valid for excel 2003.
isAscendShow
Indicates whether the specified PivotTable field is autoshown ascending.
isAscendShow : boolean;
autoShowCount
Represent the number of top or bottom items that are automatically shown in the specified PivotTable field.
autoShowCount : number;
autoShowField
Represents auto show field index. -1 means PivotField itself. It should be the index of the data fields.
autoShowField : number;
function
Represents the function used to summarize this PivotTable data field.
function : ConsolidationFunction;
showValuesSetting
Readonly. Gets the settings of showing values as when the ShowDataAs calculation is in use.
showValuesSetting : PivotShowValuesSetting;
currentPageItem
Represents the current selected page item of the page field to filter data. Only valid for page fields.
currentPageItem : number;
insertBlankRow
Indicates whether to insert a blank line after each item.
insertBlankRow : boolean;
showSubtotalAtTop
Indicates whether to display subtotals at the top or bottom of items when ShowInOutlineForm is true, then
showSubtotalAtTop : boolean;
Remarks
Only works when ShowInOutlineForm is true.
showInOutlineForm
Indicates whether to layout this field in outline form on the Pivot Table view.
showInOutlineForm : boolean;
number
Represents the built-in display format of numbers and dates.
number : number;
numberFormat
Represents the custom display format of numbers and dates.
numberFormat : string;
items
Readonly. Get all labels of pivot items in this field.
items : string[];
originalItems
Readonly. Get the original base items;
originalItems : string[];
itemCount
Readonly. Gets the count of the base items in this pivot field.
itemCount : number;
showCompact
Indicates whether to display labels of the next field in the same column on the Pivot Table view
showCompact : boolean;
initPivotItems()
Init the pivot items of the pivot field
initPivotItems() : void;
groupBy(number, boolean)
Automatically group the field with internal
groupBy(interval: number, newField: boolean) : void;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| interval | number | The internal of group. /// Automatic value will be assigned if it’s zero, |
| newField | boolean | Indicates whether adding a new field to the pivottable. |
groupBy(Date, Date, PivotGroupByType[], number, boolean)
Group the file by the date group types.
groupBy(start: Date, end: Date, groups: PivotGroupByType[], interval: number, firstAsNewField: boolean) : boolean;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| start | Date | The start datetime |
| end | Date | The end of datetime |
| groups | PivotGroupByType[] | Group types |
| interval | number | The interval |
| firstAsNewField | boolean | Indicates whether adding a new field to the pivottable. /// Only for the first group item. |
Returns
False means this field could not be grouped by date time.
groupBy(number, number, number, boolean)
Group the file by number.
groupBy(start: number, end: number, interval: number, newField: boolean) : boolean;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| start | number | The start value |
| end | number | The end of value |
| interval | number | The interval |
| newField | boolean | Indicates whether adding a new field to the pivottable |
Returns
False means this field could not be grouped by date time.
groupBy(CustomPiovtFieldGroupItem[], boolean)
Custom group the field.
groupBy(customGroupItems: CustomPiovtFieldGroupItem[], newField: boolean) : boolean;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| customGroupItems | CustomPiovtFieldGroupItem[] | The custom group items. |
| newField | boolean | Indicates whether adding a new field to the pivottable |
Returns
False means this field could not be grouped by date time.
ungroup()
Ungroup the pivot field.
ungroup() : void;
getPivotFilterByType(PivotFilterType)
Gets the pivot filter of the pivot field by type
getPivotFilterByType(type: PivotFilterType) : PivotFilter;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| type | PivotFilterType |
Returns
getFilters()
Gets all pivot filters applied for this pivot field.
getFilters() : PivotFilter[];
Returns
clearFilter()
Clears filter setting on this pivot field.
clearFilter() : void;
filterTop10(number, PivotFilterType, boolean, number)
Filters by values of data pivot field.
filterTop10(valueFieldIndex: number, type: PivotFilterType, isTop: boolean, itemCount: number) : PivotFilter;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| valueFieldIndex | number | The index of data field in the data region. |
| type | PivotFilterType | The type of filtering data. Only can be Count,Sum and Percent. |
| isTop | boolean | Indicates whether filter from top or bottom |
| itemCount | number | The item count |
Returns
filterByValue(number, PivotFilterType, number, number)
Filters by values of data pivot field.
filterByValue(valueFieldIndex: number, type: PivotFilterType, value1: number, value2: number) : PivotFilter;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| valueFieldIndex | number | The index of value field in the value region. |
| type | PivotFilterType | The type of filtering data. |
| value1 | number | The value of filter condition |
| value2 | number | The upper-bound value of between filter condition |
Returns
filterByLabel(PivotFilterType, string, string)
Filters by captions of row or column pivot field.
filterByLabel(type: PivotFilterType, label1: string, label2: string) : PivotFilter;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| type | PivotFilterType | The type of filtering data. |
| label1 | string | The label of filter condition |
| label2 | string | The upper-bound label of between filter condition |
Returns
filterByDate(PivotFilterType, Date, Date)
Filters by date values of row or column pivot field.
filterByDate(type: PivotFilterType, dateTime1: Date, dateTime2: Date) : PivotFilter;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| type | PivotFilterType | The type of filtering data. |
| dateTime1 | Date | The date label of filter condition |
| dateTime2 | Date | The upper-bound date label of between filter condition |
Returns
getFormula()
Gets the formula of the calculated field . Only works for calculated field.
getFormula() : string;
setSubtotals(PivotFieldSubtotalType, boolean)
Sets how to subtotal the specified field.
setSubtotals(subtotalType: PivotFieldSubtotalType, shown: boolean) : void;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| subtotalType | PivotFieldSubtotalType | PivotFieldSubtotalType |
| shown | boolean | Whether the specified field shows that subtotals. |
getSubtotals(PivotFieldSubtotalType)
Indicates whether to show specified subtotal for this pivot field.
getSubtotals(subtotalType: PivotFieldSubtotalType) : boolean;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| subtotalType | PivotFieldSubtotalType | Subtotal type. |
Returns
Returns whether showing specified subtotal.
sortBy(SortOrder, number)
Sorts this pivot field.
sortBy(sortType: SortOrder, fieldSortedBy: number) : void;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| sortType | SortOrder | The type of sorting this field. |
| fieldSortedBy | number | The index of pivot field sorted by. /// -1 means sorting by data labels of this field, others mean the index of data field sorted by. |
sortBy(SortOrder, number, PivotLineType, string)
Sorts this pivot field.
sortBy(sortType: SortOrder, fieldSortedBy: number, dataType: PivotLineType, cellName: string) : void;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| sortType | SortOrder | The type of sorting this field. |
| fieldSortedBy | number | The index of pivot field sorted by. /// -1 means sorting by data labels of this field, others mean the index of data field sorted by. |
| dataType | PivotLineType | The type of data sorted by. |
| cellName | string | Sort by values in the row or column |
showValuesAs(PivotFieldDataDisplayFormat, number, PivotItemPositionType, number)
Shows values of data field as different display format when the ShowDataAs calculation is in use.
showValuesAs(displayFormat: PivotFieldDataDisplayFormat, baseField: number, baseItemPositionType: PivotItemPositionType, baseItem: number) : void;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| displayFormat | PivotFieldDataDisplayFormat | The data display format type. |
| baseField | number | The index to the field which ShowDataAs calculation bases on. |
| baseItemPositionType | PivotItemPositionType | The position type of base iteam. |
| baseItem | number | The index to the base item which ShowDataAs calculation bases on. /// Only works when baseItemPositionType is custom. |
Remarks
Only for data field.
isHiddenItem(number)
Gets whether the specific PivotItem is hidden.
isHiddenItem(index: number) : boolean;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| index | number | The index of the pivotItem in the pivotField. |
Returns
whether the specific PivotItem is hidden
hideItem(number, boolean)
Sets whether the specific PivotItem in a data field is hidden.
hideItem(index: number, isHidden: boolean) : void;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| index | number | the index of the pivotItem in the pivotField. |
| isHidden | boolean | whether the specific PivotItem is hidden |
hideItem(string, boolean)
Sets whether the specific PivotItem in a data field is hidden.
hideItem(itemValue: string, isHidden: boolean) : void;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| itemValue | string | The name of the pivotItem in the pivotField. |
| isHidden | boolean | Whether the specific PivotItem is hidden |
isHiddenItemDetail(number)
Gets whether to hide the detail of the specific PivotItem..
isHiddenItemDetail(index: number) : boolean;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| index | number | The index of the pivotItem in the pivotField. |
Returns
whether the specific PivotItem is hidden detail
hideItemDetail(number, boolean)
Sets whether the specific PivotItem in a pivot field is hidden detail.
hideItemDetail(index: number, isHiddenDetail: boolean) : void;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| index | number | the index of the pivotItem in the pivotField. |
| isHiddenDetail | boolean | whether the specific PivotItem is hidden |
hideDetail(boolean)
Sets whether the detail of all PivotItems in a pivot field are hidden. That is collapse/expand this field.
hideDetail(isHiddenDetail: boolean) : void;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| isHiddenDetail | boolean | Whether hide the detail of the pivot field. |
addCalculatedItem(string, string)
Add a calculated formula item to the pivot field.
addCalculatedItem(name: string, formula: string) : void;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| name | string | The item’s name. |
| formula | string | The formula of pivot item. |
Remarks
Only supports to add calculated item to Row/Column field.