aspose.cells

Classes

Constants

Class PivotField

Represents a field in a PivotTable report.

Property Getters/Setters Summary
`function`	`getAutoShowCount()`
`function`	`setAutoShowCount(value)`
	Represent the number of top or bottom items that are automatically shown in the specified PivotTable field.
`function`	`getAutoShowField()`
`function`	`setAutoShowField(value)`
	Represents auto show field index. -1 means PivotField itself. It should be the index of the data fields.
`function`	`getAutoSortField()`
`function`	`setAutoSortField(value)`
	Represents the index of field which is auto sorted. -1 means PivotField itself,others means the position of the data fields.
`function`	`getBaseFieldIndex()`
`function`	`setBaseFieldIndex(value)`
	Represents the base field for a custom calculation when the ShowDataAs calculation is in use.
`function`	`getBaseIndex()`
`function`	`setBaseIndex(value)`
	Represents the PivotField index in the base PivotFields.
`function`	`getBaseItemIndex()`
`function`	`setBaseItemIndex(value)`
	Represents the item in the base field for a custom calculation when the ShowDataAs calculation is in use. Valid only for data fields.
`function`	`getBaseItemPosition()`
`function`	`setBaseItemPosition(value)`
	Represents 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. The value of the property is PivotItemPosition integer constant.
`function`	`getCurrentPageItem()`
`function`	`setCurrentPageItem(value)`
	Represents the current page item showing for the page field (valid only for page fields).
`function`	`getDataDisplayFormat()`
`function`	`setDataDisplayFormat(value)`
	Represents how to display the values in a data field of the pivot report. The value of the property is PivotFieldDataDisplayFormat integer constant.
`function`	`getDisplayName()`
`function`	`setDisplayName(value)`
	Represents the PivotField display name.
`function`	`getDragToColumn()`
`function`	`setDragToColumn(value)`
	Indicates whether the specified field can be dragged to the column position. The default value is true.
`function`	`getDragToData()`
`function`	`setDragToData(value)`
	Indicates whether the specified field can be dragged to the data position. The default value is true.
`function`	`getDragToHide()`
`function`	`setDragToHide(value)`
	Indicates whether the specified field can be dragged to the hide position. The default value is true.
`function`	`getDragToPage()`
`function`	`setDragToPage(value)`
	Indicates whether the specified field can be dragged to the page position. The default value is true.
`function`	`getDragToRow()`
`function`	`setDragToRow(value)`
	Indicates whether the specified field can be dragged to the row position. The default value is true.
`function`	`getFunction()`
`function`	`setFunction(value)`
	Represents the function used to summarize the PivotTable data field. The value of the property is ConsolidationFunction integer constant.
`function`	`getGroupSettings()`
	Gets the group settings of the pivot field.
`function`	`getInsertBlankRow()`
`function`	`setInsertBlankRow(value)`
	Indicates whether inserting blank line after each item.
`function`	`isAscendShow()`
`function`	`setAscendShow(value)`
	Indicates whether the specified PivotTable field is autoshown ascending.
`function`	`isAscendSort()`
`function`	`setAscendSort(value)`
	Indicates whether the specified PivotTable field is autosorted ascending.
`function`	`isAutoShow()`
`function`	`setAutoShow(value)`
	Indicates whether the specified PivotTable field is automatically shown,only valid for excel 2003.
`function`	`isAutoSort()`
`function`	`setAutoSort(value)`
	Indicates whether the specified PivotTable field is automatically sorted.
`function`	`isAutoSubtotals()`
`function`	`setAutoSubtotals(value)`
	Indicates whether the specified field shows automatic subtotals. Default is true.
`function`	`isCalculatedField()`
	Indicates whether the specified PivotTable field is calculated field.
`function`	`isIncludeNewItemsInFilter()`
`function`	`setIncludeNewItemsInFilter(value)`
	Indicates whether including new items to the field in manual filter. The default value is false.
`function`	`isInsertPageBreaksBetweenItems()`
`function`	`setInsertPageBreaksBetweenItems(value)`
	Indicates whether inserting page breaks after each item. The default value is false.
`function`	`isMultipleItemSelectionAllowed()`
`function`	`setMultipleItemSelectionAllowed(value)`
	indicates whether the field can have multiple items selected in the page field The default value is false.
`function`	`isRepeatItemLabels()`
`function`	`setRepeatItemLabels(value)`
	Indicates whether repeating labels of the field in the region. The default value is false.
`function`	`getItemCount()`
	Gets the count of the base items in this pivot field.
`function`	`getItems()`
	Get all labels of pivot items in this field.
`function`	`getName()`
`function`	`setName(value)`
	Represents the name of PivotField.
`function`	`getNonAutoSortDefault()`
`function`	`setNonAutoSortDefault(value)`
	Indicates whether a sort operation that will be applied to this pivot field is an autosort operation or a simple data sort.
`function`	`getNumber()`
`function`	`setNumber(value)`
	Represents the built-in display format of numbers and dates.
`function`	`getNumberFormat()`
`function`	`setNumberFormat(value)`
	Represents the custom display format of numbers and dates.
`function`	`getOriginalItems()`
	Get the original base items;
`function`	`getPivotItems()`
	Gets the pivot items of the pivot field
`function`	`getPosition()`
	Represents the index of PivotField in the region.
`function`	`getRange()`
	Gets the group range of the pivot field
`function`	`getShowAllItems()`
`function`	`setShowAllItems(value)`
	Indicates 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.
`function`	`getShowCompact()`
`function`	`setShowCompact(value)`
	Indicates whether display labels from the next field in the same column on the Pivot Table view
`function`	`getShowInOutlineForm()`
`function`	`setShowInOutlineForm(value)`
	Indicates whether layout this field in outline form on the Pivot Table view
`function`	`getShowSubtotalAtTop()`
`function`	`setShowSubtotalAtTop(value)`
	when ShowInOutlineForm is true, then display subtotals at the top of the list of items instead of at the bottom
`function`	`getShowValuesSetting()`
	Gets the settings of showing values as when the ShowDataAs calculation is in use.

Method Summary
`function`	`addCalculatedItem(name, formula)`
`function`	Add a calculated formula item to the pivot field.
`function`	`getCalculatedFieldFormula()`
`function`	Get the formula string of the specified calculated field .
`function`	`getFilters()`
`function`	Gets all pivot filters of this pivot field.
`function`	`getPivotFilterByType(type)`
`function`	Gets the pivot filter of the pivot field by type
`function`	`getPivotFilters()`
`function`	Gets the pivot filters of the pivot field
`function`	`getSubtotals(subtotalType)`
`function`	Indicates whether showing specified subtotal.
`function`	`groupBy(customGroupItems, newField)`
`function`	Custom group the field.
`function`	`groupBy(start, end, groups, interval, firstAsNewField)`
`function`	Group the file by the date group types.
`function`	`groupBy(interval, newField)`
`function`	Automatically group the field with internal
`function`	`groupBy(start, end, interval, newField)`
`function`	Group the file by number.
`function`	`hideDetail(isHiddenDetail)`
`function`	Sets whether the PivotItems in a pivot field is hidden detail.That is collapse/expand this field.
`function`	`hideItem(index, isHidden)`
`function`	Sets whether the specific PivotItem in a data field is hidden.
`function`	`hideItem(itemValue, isHidden)`
`function`	Sets whether the specific PivotItem in a data field is hidden.
`function`	`hideItemDetail(index, isHiddenDetail)`
`function`	Sets whether the specific PivotItem in a pivot field is hidden detail.
`function`	`initPivotItems()`
`function`	Init the pivot items of the pivot field
`function`	`isHiddenItem(index)`
`function`	Gets whether the specific PivotItem is hidden.
`function`	`isHiddenItemDetail(index)`
`function`	Gets whether hidding the detail of the specific PivotItem..
`function`	`setSubtotals(subtotalType, shown)`
`function`	Sets whether the specified field shows that subtotals.
`function`	`showValuesAs(displayFormat, baseField, baseItemPositionType, baseItem)`
`function`	Show value of data field as different display format when the ShowDataAs calculation is in use.
`function`	`ungroup()`
`function`	Ungroup the pivot field.

- Property Getters/Setters Detail
  - getPivotItems : PivotItemCollection
```
function getPivotItems()
```
    Gets the pivot items of the pivot field
  - getRange : SxRng
```
function getRange()
```
    Gets the group range of the pivot field
    NOTE: This method is now obsolete. Instead, please use PivotField.GroupSettings property. This method will be removed 12 months later since October 2023. Aspose apologizes for any inconvenience you may have experienced.
  - getGroupSettings : PivotFieldGroupSettings
```
function getGroupSettings()
```
    Gets the group settings of the pivot field.
    If this field is not grouped, Null will be returned.
  - isCalculatedField : boolean
```
function isCalculatedField()
```
    Indicates whether the specified PivotTable field is calculated field.
  - getBaseIndex/setBaseIndex : Number
```
function getBaseIndex() / function setBaseIndex(value)
```
    Represents the PivotField index in the base PivotFields.
  - getPosition : Number
```
function getPosition()
```
    Represents the index of PivotField in the region.
  - getName/setName : String
```
function getName() / function setName(value)
```
    Represents the name of PivotField.
  - getDisplayName/setDisplayName : String
```
function getDisplayName() / function setDisplayName(value)
```
    Represents the PivotField display name.
  - isAutoSubtotals/setAutoSubtotals : boolean
```
function isAutoSubtotals() / function setAutoSubtotals(value)
```
    Indicates whether the specified field shows automatic subtotals. Default is true.
  - getDragToColumn/setDragToColumn : boolean
```
function getDragToColumn() / function setDragToColumn(value)
```
    Indicates whether the specified field can be dragged to the column position. The default value is true.
  - getDragToHide/setDragToHide : boolean
```
function getDragToHide() / function setDragToHide(value)
```
    Indicates whether the specified field can be dragged to the hide position. The default value is true.
  - getDragToRow/setDragToRow : boolean
```
function getDragToRow() / function setDragToRow(value)
```
    Indicates whether the specified field can be dragged to the row position. The default value is true.
  - getDragToPage/setDragToPage : boolean
```
function getDragToPage() / function setDragToPage(value)
```
    Indicates whether the specified field can be dragged to the page position. The default value is true.
  - getDragToData/setDragToData : boolean
```
function getDragToData() / function setDragToData(value)
```
    Indicates whether the specified field can be dragged to the data position. The default value is true.
  - isMultipleItemSelectionAllowed/setMultipleItemSelectionAllowed : boolean
```
function isMultipleItemSelectionAllowed() / function setMultipleItemSelectionAllowed(value)
```
    indicates whether the field can have multiple items selected in the page field The default value is false.
  - isRepeatItemLabels/setRepeatItemLabels : boolean
```
function isRepeatItemLabels() / function setRepeatItemLabels(value)
```
    Indicates whether repeating labels of the field in the region. The default value is false.
  - isIncludeNewItemsInFilter/setIncludeNewItemsInFilter : boolean
```
function isIncludeNewItemsInFilter() / function setIncludeNewItemsInFilter(value)
```
    Indicates whether including new items to the field in manual filter. The default value is false.
  - isInsertPageBreaksBetweenItems/setInsertPageBreaksBetweenItems : boolean
```
function isInsertPageBreaksBetweenItems() / function setInsertPageBreaksBetweenItems(value)
```
    Indicates whether inserting page breaks after each item. The default value is false.
  - getShowAllItems/setShowAllItems : boolean
```
function getShowAllItems() / function setShowAllItems(value)
```
    Indicates 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.
  - getNonAutoSortDefault/setNonAutoSortDefault : boolean
```
function getNonAutoSortDefault() / function setNonAutoSortDefault(value)
```
    Indicates whether a sort operation that will be applied to this pivot field is an autosort operation or a simple data sort.
  - isAutoSort/setAutoSort : boolean
```
function isAutoSort() / function setAutoSort(value)
```
    Indicates whether the specified PivotTable field is automatically sorted.
  - isAscendSort/setAscendSort : boolean
```
function isAscendSort() / function setAscendSort(value)
```
    Indicates whether the specified PivotTable field is autosorted ascending.
  - getAutoSortField/setAutoSortField : Number
```
function getAutoSortField() / function setAutoSortField(value)
```
    Represents the index of field which is auto sorted. -1 means PivotField itself,others means the position of the data fields.
  - isAutoShow/setAutoShow : boolean
```
function isAutoShow() / function setAutoShow(value)
```
    Indicates whether the specified PivotTable field is automatically shown,only valid for excel 2003.
  - isAscendShow/setAscendShow : boolean
```
function isAscendShow() / function setAscendShow(value)
```
    Indicates whether the specified PivotTable field is autoshown ascending.
  - getAutoShowCount/setAutoShowCount : Number
```
function getAutoShowCount() / function setAutoShowCount(value)
```
    Represent the number of top or bottom items that are automatically shown in the specified PivotTable field.
  - getAutoShowField/setAutoShowField : Number
```
function getAutoShowField() / function setAutoShowField(value)
```
    Represents auto show field index. -1 means PivotField itself. It should be the index of the data fields.
  - getFunction/setFunction : Number
```
function getFunction() / function setFunction(value)
```
    Represents the function used to summarize the PivotTable data field. The value of the property is ConsolidationFunction integer constant.
  - getShowValuesSetting : PivotShowValuesSetting
```
function getShowValuesSetting()
```
    Gets the settings of showing values as when the ShowDataAs calculation is in use.
  - getDataDisplayFormat/setDataDisplayFormat : Number
```
function getDataDisplayFormat() / function setDataDisplayFormat(value)
```
    Represents how to display the values in a data field of the pivot report. The value of the property is PivotFieldDataDisplayFormat integer constant.
    PivotFieldDataDisplayFormat NOTE: This property is now obsolete. Instead, please use PivotField.PivotShowValuesSetting.CalculationType property instead. This method will be removed 12 months later since June 2024. Aspose apologizes for any inconvenience you may have experienced.
  - getBaseFieldIndex/setBaseFieldIndex : Number
```
function getBaseFieldIndex() / function setBaseFieldIndex(value)
```
    Represents the base field for a custom calculation when the ShowDataAs calculation is in use.
    NOTE: This property is now obsolete. Instead, please use PivotField.PivotShowValuesSetting.BaseFieldIndex property instead. This method will be removed 12 months later since June 2024. Aspose apologizes for any inconvenience you may have experienced.
  - getBaseItemPosition/setBaseItemPosition : Number
```
function getBaseItemPosition() / function setBaseItemPosition(value)
```
    Represents 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. The value of the property is PivotItemPosition integer constant.
    PivotItemPosition NOTE: This property is now obsolete. Instead, please use PivotField.PivotShowValuesSetting.BaseItemType property instead. This method will be removed 12 months later since June 2024. Aspose apologizes for any inconvenience you may have experienced.
  - getBaseItemIndex/setBaseItemIndex : Number
```
function getBaseItemIndex() / function setBaseItemIndex(value)
```
    Represents the item in the base field for a custom calculation when the ShowDataAs calculation is in use. Valid only for data fields.
    NOTE: This property is now obsolete. Instead, please use PivotField.PivotShowValuesSetting.BaseItemIndex property instead. This method will be removed 12 months later since June 2024. Aspose apologizes for any inconvenience you may have experienced.
  - getCurrentPageItem/setCurrentPageItem : Number
```
function getCurrentPageItem() / function setCurrentPageItem(value)
```
    Represents the current page item showing for the page field (valid only for page fields).
  - getNumber/setNumber : Number
```
function getNumber() / function setNumber(value)
```
    Represents the built-in display format of numbers and dates.
  - getInsertBlankRow/setInsertBlankRow : boolean
```
function getInsertBlankRow() / function setInsertBlankRow(value)
```
    Indicates whether inserting blank line after each item.
  - getShowSubtotalAtTop/setShowSubtotalAtTop : boolean
```
function getShowSubtotalAtTop() / function setShowSubtotalAtTop(value)
```
    when ShowInOutlineForm is true, then display subtotals at the top of the list of items instead of at the bottom
    Only works when ShowInOutlineForm is true.
  - getShowInOutlineForm/setShowInOutlineForm : boolean
```
function getShowInOutlineForm() / function setShowInOutlineForm(value)
```
    Indicates whether layout this field in outline form on the Pivot Table view
  - getNumberFormat/setNumberFormat : String
```
function getNumberFormat() / function setNumberFormat(value)
```
    Represents the custom display format of numbers and dates.
  - getItems : String[]
```
function getItems()
```
    Get all labels of pivot items in this field.
  - getOriginalItems : String[]
```
function getOriginalItems()
```
    Get the original base items;
  - getItemCount : Number
```
function getItemCount()
```
    Gets the count of the base items in this pivot field.
  - getShowCompact/setShowCompact : boolean
```
function getShowCompact() / function setShowCompact(value)
```
    Indicates whether display labels from the next field in the same column on the Pivot Table view
- Method Detail
  - getPivotFilterByType
```
function getPivotFilterByType(type)
```
    Gets the pivot filter of the pivot field by type
    Parameters:
    type - A PivotFilterType value.
  - getPivotFilters
```
function getPivotFilters()
```
    Gets the pivot filters of the pivot field
    NOTE: This method is now obsolete. Instead, please use PivotField.GetFilters() method. This method will be removed 12 months later since November 2023. Aspose apologizes for any inconvenience you may have experienced.
  - getFilters
```
function getFilters()
```
    Gets all pivot filters of this pivot field.
  - initPivotItems
```
function initPivotItems()
```
    Init the pivot items of the pivot field
  - groupBy
```
function groupBy(interval, newField)
```
    Automatically group the field with internal
    Parameters:
    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
```
function groupBy(start, end, groups, interval, firstAsNewField)
```
    Group the file by the date group types.
    Parameters:
    start: DateTime - The start datetime
    end: DateTime - The end of datetime
    groups: Number Array - Group types
    interval: Number - The interval
    firstAsNewField: boolean - Indicates whether adding a new field to the pivottable. Only for the first group item.
  - groupBy
```
function groupBy(start, end, interval, newField)
```
    Group the file by number.
    Parameters:
    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
  - groupBy
```
function groupBy(customGroupItems, newField)
```
    Custom group the field.
    Parameters:
    customGroupItems: CustomPiovtFieldGroupItem[] - The custom group items.
    newField: boolean - Indicates whether adding a new field to the pivottable
  - ungroup
```
function ungroup()
```
    Ungroup the pivot field.
  - getCalculatedFieldFormula
```
function getCalculatedFieldFormula()
```
    Get the formula string of the specified calculated field .
  - setSubtotals
```
function setSubtotals(subtotalType, shown)
```
    Sets whether the specified field shows that subtotals.
    PivotFieldSubtotalType
    Parameters:
    subtotalType: Number - A PivotFieldSubtotalType value. subtotals type.
    shown: boolean - whether the specified field shows that subtotals.
  - getSubtotals
```
function getSubtotals(subtotalType)
```
    Indicates whether showing specified subtotal.
    Parameters:
    subtotalType: Number - A PivotFieldSubtotalType value. subtotal type.
    Returns:
    Returns whether showing specified subtotal.
  - showValuesAs
```
function showValuesAs(displayFormat, baseField, baseItemPositionType, baseItem)
```
    Show value of data field as different display format when the ShowDataAs calculation is in use.
    Only for data field.
    Parameters:
    displayFormat: Number - A PivotFieldDataDisplayFormat value. The data display format type.
    baseField: Number - The index to the field which ShowDataAs calculation bases on.
    baseItemPositionType: Number - A PivotItemPositionType value. 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.
  - isHiddenItem
```
function isHiddenItem(index)
```
    Gets whether the specific PivotItem is hidden.
    Parameters:
    index: Number - The index of the pivotItem in the pivotField.
    Returns:
    whether the specific PivotItem is hidden
  - hideItem
```
function hideItem(index, isHidden)
```
    Sets whether the specific PivotItem in a data field is hidden.
    Parameters:
    index: Number - the index of the pivotItem in the pivotField.
    isHidden: boolean - whether the specific PivotItem is hidden
  - isHiddenItemDetail
```
function isHiddenItemDetail(index)
```
    Gets whether hidding the detail of the specific PivotItem..
    Parameters:
    index: Number - The index of the pivotItem in the pivotField.
    Returns:
    whether the specific PivotItem is hidden detail
  - hideItemDetail
```
function hideItemDetail(index, isHiddenDetail)
```
    Sets whether the specific PivotItem in a pivot field is hidden detail.
    Parameters:
    index: Number - the index of the pivotItem in the pivotField.
    isHiddenDetail: boolean - whether the specific PivotItem is hidden
  - hideDetail
```
function hideDetail(isHiddenDetail)
```
    Sets whether the PivotItems in a pivot field is hidden detail.That is collapse/expand this field.
    Parameters:
    isHiddenDetail: boolean - Whether hide the detail of the pivot field.
  - hideItem
```
function hideItem(itemValue, isHidden)
```
    Sets whether the specific PivotItem in a data field is hidden.
    Parameters:
    itemValue: String - the value of the pivotItem in the pivotField.
    isHidden: boolean - whether the specific PivotItem is hidden
  - addCalculatedItem
```
function addCalculatedItem(name, formula)
```
    Add a calculated formula item to the pivot field.
    Only supports to add calculated item to Row/Column field.
    Parameters:
    name: String - The item's name.
    formula: String - The formula of pivot item.

aspose.cells

Classes

Constants

Class PivotField

Property Getters/Setters Detail

getPivotItems : PivotItemCollection

getRange : SxRng

getGroupSettings : PivotFieldGroupSettings

isCalculatedField : boolean

getBaseIndex/setBaseIndex : Number

getPosition : Number

getName/setName : String

getDisplayName/setDisplayName : String

isAutoSubtotals/setAutoSubtotals : boolean

getDragToColumn/setDragToColumn : boolean

getDragToHide/setDragToHide : boolean

getDragToRow/setDragToRow : boolean

getDragToPage/setDragToPage : boolean

getDragToData/setDragToData : boolean

isMultipleItemSelectionAllowed/setMultipleItemSelectionAllowed : boolean

isRepeatItemLabels/setRepeatItemLabels : boolean

isIncludeNewItemsInFilter/setIncludeNewItemsInFilter : boolean

isInsertPageBreaksBetweenItems/setInsertPageBreaksBetweenItems : boolean

getShowAllItems/setShowAllItems : boolean

getNonAutoSortDefault/setNonAutoSortDefault : boolean

isAutoSort/setAutoSort : boolean

isAscendSort/setAscendSort : boolean

getAutoSortField/setAutoSortField : Number

isAutoShow/setAutoShow : boolean

isAscendShow/setAscendShow : boolean

getAutoShowCount/setAutoShowCount : Number

getAutoShowField/setAutoShowField : Number

getFunction/setFunction : Number

getShowValuesSetting : PivotShowValuesSetting

getDataDisplayFormat/setDataDisplayFormat : Number

getBaseFieldIndex/setBaseFieldIndex : Number

getBaseItemPosition/setBaseItemPosition : Number

getBaseItemIndex/setBaseItemIndex : Number

getCurrentPageItem/setCurrentPageItem : Number

getNumber/setNumber : Number

getInsertBlankRow/setInsertBlankRow : boolean

getShowSubtotalAtTop/setShowSubtotalAtTop : boolean

getShowInOutlineForm/setShowInOutlineForm : boolean

getNumberFormat/setNumberFormat : String

getItems : String[]

getOriginalItems : String[]

getItemCount : Number

getShowCompact/setShowCompact : boolean

Method Detail

getPivotFilterByType

getPivotFilters

getFilters

initPivotItems

groupBy

groupBy

groupBy

groupBy

ungroup

getCalculatedFieldFormula

setSubtotals

getSubtotals

showValuesAs

isHiddenItem

hideItem

isHiddenItemDetail

hideItemDetail

hideDetail

hideItem

addCalculatedItem