Methods

Methods

addCalculatedField(name, formula, dragToDataArea)

Adds a calculated field to pivot field.

Parameters

Name Type Optional Description

name

String

 

The name of the calculated field

formula

String

 

The formula of the calculated field.

dragToDataArea

boolean

 

True,drag this field to data area immediately

addCalculatedField(name, formula)

Adds a calculated field to pivot field and drag it to data area.

Parameters

Name Type Optional Description

name

String

 

The name of the calculated field

formula

String

 

The formula of the calculated field.

addFieldToArea(fieldType, fieldName) → Number

Adds the field to the specific area. addFieldToArea(int, com.aspose.cells.PivotField)

Parameters

Name Type Optional Description

fieldType

Number

 

PivotFieldType

fieldName

String

 

The name in the base fields.

Returns

Number The field position in the specific fields.If there is no field named as it, return -1.

addFieldToArea(fieldType, baseFieldIndex) → Number

Adds the field to the specific area. addFieldToArea(int, com.aspose.cells.PivotField)

Parameters

Name Type Optional Description

fieldType

Number

 

PivotFieldType

baseFieldIndex

Number

 

The field index in the base fields.

Returns

Number The field position in the specific fields.

addFieldToArea(fieldType, pivotField) → Number

Adds the field to the specific area.

Parameters

Name Type Optional Description

fieldType

Number

 

PivotFieldType

pivotField

PivotField

 

the field in the base fields.

Returns

Number the field position in the specific fields.

calculateData()

Calculates pivottable's data to cells. Cell.Value in the pivot range could not return the correct result if the method is not been called. This method calculates data with an inner pivot cache,not original data source. So if the data source is changed, please call RefreshData() method first.

calculateRange()

Calculates pivottable's range. If this method is not been called,maybe the pivottable range is not corrected.

changeDataSource()

Set pivottable's source data. Sheet1!$A$1:$C$3

clearData()

Clear PivotTable's data and formatting If this method is not called before you add or delete PivotField, Maybe the PivotTable data is not corrected

copyStyle(pivotTable)

Copies named style from another pivot table.

Parameter

Name Type Optional Description

pivotTable

PivotTable

 

Source pivot table.

dispose()

Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.

fields(fieldType) → PivotFieldCollection

Gets the specific fields by the field type.

Parameter

Name Type Optional Description

fieldType

Number

 

PivotFieldType

Returns

PivotFieldCollection the specific fields

format(row, column, style)

Format the cell in the pivottable area

Parameters

Name Type Optional Description

row

Number

 

RowIndex of the cell

column

Number

 

Column index of the cell

style

Style

 

Style which is to format the cell

formatAll(style)

Format all the cell in the pivottable area

Parameter

Name Type Optional Description

style

Style

 

Style which is to format

getAltTextDescription()

Gets the description of the alt text

getAltTextTitle()

Gets the title of the altertext

getAutoFormatType()

Gets the PivotTable auto format type. The value of the property is PivotTableAutoFormatType integer constant.PivotTableAutoFormatType

getBaseFields()

Returns a PivotFields object that includes all fields in the PivotTable report

getCellByDisplayName(displayName) → Cell

Gets the Cell object by the DisplayName of PivotField

Parameter

Name Type Optional Description

displayName

String

 

the DisplayName of PivotField

Returns

Cell the Cell object

getChildren() → Array of PivotTable

Gets the Children Pivot Tables which use this PivotTable data as data source.

Returns

Array of PivotTable the PivotTable array object

getColumnFields()

Returns a PivotFields object that are currently shown as column fields.

getColumnGrand()

Indicates whether the PivotTable report shows grand totals for columns.

getColumnHeaderCaption()

Gets the Column Header Caption of the PivotTable.

getColumnRange()

Returns a CellArea object that represents the range that contains the column area in the PivotTable report. Read-only.

getCustomListSort()

Indicates whether consider built-in custom list when sort data

getDataBodyRange()

Returns 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.

getDataField()

Gets 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.

getDataFields()

Gets 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.

getDataSource()

Gets and sets the data source of the pivot table.

getDisplayErrorString()

Indicates whether the PivotTable report displays a custom string in cells that contain errors.

getDisplayImmediateItems()

Indicates whether items in the row and column areas are visible when the data area of the PivotTable is empty. The default value is true.

getDisplayNullString()

Indicates whether the PivotTable report displays a custom string in cells that contain null values.

getEnableDataValueEditing()

Specifies 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

getEnableDrilldown()

Gets whether drilldown is enabled.

getEnableFieldDialog()

Indicates whether the PivotTable Field dialog box is available when the user double-clicks the PivotTable field.

getEnableFieldList()

Gets whether enable the field list for the PivotTable.

getEnableWizard()

Indicates whether the PivotTable Wizard is available.

getErrorString()

Gets the string displayed in cells that contain errors when the DisplayErrorString property is true.The default value is an empty string.

getExternalConnectionDataSource()

Gets the external connection data source.

getFieldListSortAscending()

Specifies a boolean value that indicates whether fields in the PivotTable are sorted in non-default order in the field list.

getGrandTotalName()

Returns the text string label that is displayed in the grand total column or row heading. The default value is the string "Grand Total".

getHorizontalBreaks() → ArrayList

get pivot table row index list of horizontal pagebreaks

Returns

ArrayList 

getIndent()

Specifies the indentation increment for compact axis and can be used to set the Report Layout to Compact Form.

getItemPrintTitles()

A bit that specifies whether pivot item captions on the row axis are repeated on each printed page for pivot fields in tabular form.

getManualUpdate()

Indicates whether the PivotTable report is recalculated only at the user's request.

getMergeLabels()

Indicates whether the specified PivotTable report's outer-row item, column item, subtotal, and grand total labels use merged cells.

getMissingItemsLimit()

Specifies a boolean value that indicates whether the fields of a PivotTable can have multiple filters set on them. The value of the property is PivotMissingItemLimitType integer constant.

getName()

Gets the name of the PivotTable

getNullString()

Gets the string displayed in cells that contain null values when the DisplayNullString property is true.The default value is an empty string.

getPageFieldOrder()

Gets the order in which page fields are added to the PivotTable report's layout. The value of the property is PrintOrderType integer constant.

getPageFields()

Returns a PivotFields object that are currently shown as page fields.

getPageFieldWrapCount()

Gets the number of page fields in each column or row in the PivotTable report.

getPivotFilters()

Returns a PivotFilterCollection object.

getPivotFormatConditions()

Gets the Format Conditions of the pivot table.

getPivotTableStyleName()

Gets and sets the pivottable style name.

getPivotTableStyleType()

Gets and sets the built-in pivot table style. The value of the property is PivotTableStyleType integer constant.

getPreserveFormatting()

Indicates whether formatting is preserved when the PivotTable is refreshed or recalculated.

getPrintDrill()

Specifies a boolean value that indicates whether drill indicators should be printed. print expand/collapse buttons when displayed on pivottable.

getPrintTitles()

Indicates whether the print titles for the worksheet are set based on the PivotTable report. The default value is false.

getRefreshDataFlag()

Indicates whether Refresh Data or not.

getRefreshDataOnOpeningFile()

Indicates whether Refresh Data when Opening File.

getRefreshDate()

Gets the date when the PivotTable was last refreshed.

getRefreshedByWho()

Gets the name of the user who last refreshed the PivotTable

getRowFields()

Returns a PivotFields object that are currently shown as row fields.

getRowGrand()

Indicates whether the PivotTable report shows grand totals for rows.

getRowHeaderCaption()

Gets the Row Header Caption of the PivotTable.

getRowRange()

Returns a CellArea object that represents the range that contains the row area in the PivotTable report. Read-only.

getSaveData()

Indicates whether data for the PivotTable report is saved with the workbook.

getShowDataTips()

Specifies a boolean value that indicates whether tooltips should be displayed for PivotTable data cells.

getShowDrill()

Gets whether expand/collapse buttons is shown.

getShowEmptyCol()

Specifies a boolean value that indicates whether to include empty columns in the table

getShowEmptyRow()

Specifies a boolean value that indicates whether to include empty rows in the table.

getShowMemberPropertyTips()

Specifies a boolean value that indicates whether member property information should be omitted from PivotTable tooltips.

getShowPivotStyleColumnHeader()

Indicates whether the column header in the pivot table should have the style applied.

getShowPivotStyleColumnStripes()

Indicates whether column stripe formatting is applied.

getShowPivotStyleLastColumn()

Indicates whether column stripe formatting is applied.

getShowPivotStyleRowHeader()

Indicates whether the row header in the pivot table should have the style applied.

getShowPivotStyleRowStripes()

Indicates whether row stripe formatting is applied.

getShowRowHeaderCaption()

Indicates whether row header caption is shown in the PivotTable report Indicates whether Display field captions and filter drop downs

getShowValuesRow()

Specifies a boolean value that indicates whether show values row. show the values row

getSource()

Get pivottable's source data.

getSubtotalHiddenPageItems()

Indicates 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.

getTableRange1()

Returns a CellArea object that represents the range containing the entire PivotTable report, but doesn't include page fields. Read-only.

getTableRange2()

Returns a CellArea object that represents the range containing the entire PivotTable report, includes page fields. Read-only.

getTag()

Gets a string saved with the PivotTable report.

hasBlankRows()

Indicates whether to add blank rows. This property only applies for the PivotTable auto format types which needs to add blank rows.

isAutoFormat()

Indicates whether the PivotTable report is automatically formatted. Checkbox "autoformat table " which is in pivottable option for Excel 2003 Checkbox "autofit column width on update" which is in pivot table Options :Layout Format for Excel 2007

isExcel2003Compatible()

Specifies 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.

isGridDropZones()

Indicates whether the PivotTable report displays classic pivottable layout. (enables dragging fields in the grid)

isMultipleFieldFilters()

Specifies a boolean value that indicates whether the fields of a PivotTable can have multiple filters set on them.

isSelected()

Indicates whether the PivotTable is selected.

move(row, column)

Moves the PivotTable to a different location in the worksheet.

Parameters

Name Type Optional Description

row

Number

 

row index.

column

Number

 

column index.

move(destCellName)

Moves the PivotTable to a different location in the worksheet.

Parameter

Name Type Optional Description

destCellName

String

 

the dest cell name.

refreshData()

Refreshes pivottable's data and setting from it's data source. We will gather data from data source to a pivot cache ,then calculate the data in the cache to the cells. This method is only used to gather all data to a pivot cache.

removeField(fieldType, fieldName)

Removes a field from specific field area removeField(int, com.aspose.cells.PivotField)

Parameters

Name Type Optional Description

fieldType

Number

 

PivotFieldType

fieldName

String

 

The name in the base fields.

removeField(fieldType, baseFieldIndex)

Removes a field from specific field area removeField(int, com.aspose.cells.PivotField)

Parameters

Name Type Optional Description

fieldType

Number

 

PivotFieldType

baseFieldIndex

Number

 

The field index in the base fields.

removeField(fieldType, pivotField)

Remove field from specific field area

Parameters

Name Type Optional Description

fieldType

Number

 

PivotFieldType

pivotField

PivotField

 

the field in the base fields.

setAltTextDescription()

Gets the description of the alt text

setAltTextTitle()

Gets the title of the altertext

setAutoFormat()

Indicates whether the PivotTable report is automatically formatted. Checkbox "autoformat table " which is in pivottable option for Excel 2003 Checkbox "autofit column width on update" which is in pivot table Options :Layout Format for Excel 2007

setAutoFormatType()

Gets the PivotTable auto format type. The value of the property is PivotTableAutoFormatType integer constant.PivotTableAutoFormatType

setAutoGroupField(baseFieldIndex)

Sets auto field group by the PivotTable.

Parameter

Name Type Optional Description

baseFieldIndex

Number

 

The row or column field index in the base fields

setAutoGroupField(pivotField)

Sets auto field group by the PivotTable.

Parameter

Name Type Optional Description

pivotField

PivotField

 

The row or column field in the specific fields

setColumnGrand()

Indicates whether the PivotTable report shows grand totals for columns.

setColumnHeaderCaption()

Gets the Column Header Caption of the PivotTable.

setCustomListSort()

Indicates whether consider built-in custom list when sort data

setDataSource()

Gets and sets the data source of the pivot table.

setDisplayErrorString()

Indicates whether the PivotTable report displays a custom string in cells that contain errors.

setDisplayImmediateItems()

Indicates whether items in the row and column areas are visible when the data area of the PivotTable is empty. The default value is true.

setDisplayNullString()

Indicates whether the PivotTable report displays a custom string in cells that contain null values.

setEnableDataValueEditing()

Specifies 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

setEnableDrilldown()

Gets whether drilldown is enabled.

setEnableFieldDialog()

Indicates whether the PivotTable Field dialog box is available when the user double-clicks the PivotTable field.

setEnableFieldList()

Gets whether enable the field list for the PivotTable.

setEnableWizard()

Indicates whether the PivotTable Wizard is available.

setErrorString()

Gets the string displayed in cells that contain errors when the DisplayErrorString property is true.The default value is an empty string.

setExcel2003Compatible()

Specifies 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.

setFieldListSortAscending()

Specifies a boolean value that indicates whether fields in the PivotTable are sorted in non-default order in the field list.

setGrandTotalName()

Returns the text string label that is displayed in the grand total column or row heading. The default value is the string "Grand Total".

setGridDropZones()

Indicates whether the PivotTable report displays classic pivottable layout. (enables dragging fields in the grid)

setHasBlankRows()

Indicates whether to add blank rows. This property only applies for the PivotTable auto format types which needs to add blank rows.

setIndent()

Specifies the indentation increment for compact axis and can be used to set the Report Layout to Compact Form.

setItemPrintTitles()

A bit that specifies whether pivot item captions on the row axis are repeated on each printed page for pivot fields in tabular form.

setManualGroupField(baseFieldIndex, startVal, endVal, groupByList, intervalNum)

Sets manual field group by the PivotTable.

Parameters

Name Type Optional Description

baseFieldIndex

Number

 

The row or column field index in the base fields

startVal

Number

 

Specifies the starting value for numeric grouping.

endVal

Number

 

Specifies the ending value for numeric grouping.

groupByList

ArrayList

 

Specifies the grouping type list. Specified by PivotTableGroupType

intervalNum

Number

 

Specifies the interval number group by numeric grouping.

setManualGroupField(pivotField, startVal, endVal, groupByList, intervalNum)

Sets manual field group by the PivotTable.

Parameters

Name Type Optional Description

pivotField

PivotField

 

The row or column field in the base fields

startVal

Number

 

Specifies the starting value for numeric grouping.

endVal

Number

 

Specifies the ending value for numeric grouping.

groupByList

ArrayList

 

Specifies the grouping type list. Specified by PivotTableGroupType

intervalNum

Number

 

Specifies the interval number group by numeric grouping.

setManualGroupField(baseFieldIndex, startVal, endVal, groupByList, intervalNum)

Sets manual field group by the PivotTable.

Parameters

Name Type Optional Description

baseFieldIndex

Number

 

The row or column field index in the base fields

startVal

DateTime

 

Specifies the starting value for date grouping.

endVal

DateTime

 

Specifies the ending value for date grouping.

groupByList

ArrayList

 

Specifies the grouping type list. Specified by PivotTableGroupType

intervalNum

Number

 

Specifies the interval number group by in days grouping.The number of days must be positive integer of nonzero

setManualGroupField(pivotField, startVal, endVal, groupByList, intervalNum)

Sets manual field group by the PivotTable.

Parameters

Name Type Optional Description

pivotField

PivotField

 

The row or column field in the base fields

startVal

DateTime

 

Specifies the starting value for date grouping.

endVal

DateTime

 

Specifies the ending value for date grouping.

groupByList

ArrayList

 

Specifies the grouping type list. Specified by PivotTableGroupType

intervalNum

Number

 

Specifies the interval number group by in days grouping.The number of days must be positive integer of nonzero

setManualUpdate()

Indicates whether the PivotTable report is recalculated only at the user's request.

setMergeLabels()

Indicates whether the specified PivotTable report's outer-row item, column item, subtotal, and grand total labels use merged cells.

setMissingItemsLimit()

Specifies a boolean value that indicates whether the fields of a PivotTable can have multiple filters set on them. The value of the property is PivotMissingItemLimitType integer constant.

setMultipleFieldFilters()

Specifies a boolean value that indicates whether the fields of a PivotTable can have multiple filters set on them.

setName()

Gets the name of the PivotTable

setNullString()

Gets the string displayed in cells that contain null values when the DisplayNullString property is true.The default value is an empty string.

setPageFieldOrder()

Gets the order in which page fields are added to the PivotTable report's layout. The value of the property is PrintOrderType integer constant.

setPageFieldWrapCount()

Gets the number of page fields in each column or row in the PivotTable report.

setPivotTableStyleName()

Gets and sets the pivottable style name.

setPivotTableStyleType()

Gets and sets the built-in pivot table style. The value of the property is PivotTableStyleType integer constant.

setPreserveFormatting()

Indicates whether formatting is preserved when the PivotTable is refreshed or recalculated.

setPrintDrill()

Specifies a boolean value that indicates whether drill indicators should be printed. print expand/collapse buttons when displayed on pivottable.

setPrintTitles()

Indicates whether the print titles for the worksheet are set based on the PivotTable report. The default value is false.

setRefreshDataFlag()

Indicates whether Refresh Data or not.

setRefreshDataOnOpeningFile()

Indicates whether Refresh Data when Opening File.

setRowGrand()

Indicates whether the PivotTable report shows grand totals for rows.

setRowHeaderCaption()

Gets the Row Header Caption of the PivotTable.

setSaveData()

Indicates whether data for the PivotTable report is saved with the workbook.

setSelected()

Indicates whether the PivotTable is selected.

setShowDataTips()

Specifies a boolean value that indicates whether tooltips should be displayed for PivotTable data cells.

setShowDrill()

Gets whether expand/collapse buttons is shown.

setShowEmptyCol()

Specifies a boolean value that indicates whether to include empty columns in the table

setShowEmptyRow()

Specifies a boolean value that indicates whether to include empty rows in the table.

setShowMemberPropertyTips()

Specifies a boolean value that indicates whether member property information should be omitted from PivotTable tooltips.

setShowPivotStyleColumnHeader()

Indicates whether the column header in the pivot table should have the style applied.

setShowPivotStyleColumnStripes()

Indicates whether column stripe formatting is applied.

setShowPivotStyleLastColumn()

Indicates whether column stripe formatting is applied.

setShowPivotStyleRowHeader()

Indicates whether the row header in the pivot table should have the style applied.

setShowPivotStyleRowStripes()

Indicates whether row stripe formatting is applied.

setShowRowHeaderCaption()

Indicates whether row header caption is shown in the PivotTable report Indicates whether Display field captions and filter drop downs

setShowValuesRow()

Specifies a boolean value that indicates whether show values row. show the values row

setSubtotalHiddenPageItems()

Indicates 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.

setTag()

Gets a string saved with the PivotTable report.

setUngroup(baseFieldIndex)

Sets ungroup by the PivotTable

Parameter

Name Type Optional Description

baseFieldIndex

Number

 

The row or column field index in the base fields

setUngroup(pivotField)

Sets ungroup by the PivotTable

Parameter

Name Type Optional Description

pivotField

PivotField

 

The row or column field in the base fields

showInCompactForm()

Layouts the PivotTable in compact form.

showInOutlineForm()

Layouts the PivotTable in outline form.

showInTabularForm()

Layouts the PivotTable in tabular form.

showReportFilterPage(pageField)

Show all the report filter pages according to PivotField, the PivotField must be located in the PageFields.

Parameter

Name Type Optional Description

pageField

PivotField

 

The PivotField object

showReportFilterPageByIndex(posIndex)

Show all the report filter pages according to the position index in the PageFields

Parameter

Name Type Optional Description

posIndex

Number

 

The position index in the PageFields

showReportFilterPageByName(fieldName)

Show all the report filter pages according to PivotField's name, the PivotField must be located in the PageFields.

Parameter

Name Type Optional Description

fieldName

String

 

The name of PivotField