Aspose::Cells::Pivot::PivotTable class
Contents
[
Hide
]PivotTable class
Summary description for PivotTable.
class PivotTable
Methods
| Method | Description |
|---|---|
| AddCalculatedField(const U16String& name, const U16String& formula, bool dragToDataArea) | Adds a calculated field to pivot field. |
| AddCalculatedField(const char16_t* name, const char16_t* formula, bool dragToDataArea) | Adds a calculated field to pivot field. |
| AddCalculatedField(const U16String& name, const U16String& formula) | Adds a calculated field to pivot field and drag it to data area. |
| AddCalculatedField(const char16_t* name, const char16_t* formula) | Adds a calculated field to pivot field and drag it to data area. |
| AddFieldToArea(PivotFieldType fieldType, const U16String& fieldName) | Adds the field to the specific area. |
| AddFieldToArea(PivotFieldType fieldType, const char16_t* fieldName) | Adds the field to the specific area. |
| AddFieldToArea(PivotFieldType fieldType, int32_t baseFieldIndex) | Adds the field to the specific area. |
| AddFieldToArea(PivotFieldType fieldType, const PivotField& pivotField) | Adds the field to the specific area. |
| CalculateData() | Calculates pivottable’s data to cells. |
| CalculateData(const PivotTableCalculateOption& option) | Calculating pivot tables with options. |
| CalculateRange() | Calculates pivottable’s range. |
| ChangeDataSource(const Vector <U16String>& source) | Set pivottable’s source data. Sheet1!$A$1:$C$3. |
| ClearData() | Clear PivotTable’s data and formatting. |
| CopyStyle(const PivotTable& pivotTable) | Copies named style from another pivot table. |
| Dispose() | Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources. |
| Format(const PivotArea& pivotArea, const Style& style) | Formats selected area of the PivotTable. |
| Format(int32_t row, int32_t column, const Style& style) | Format the cell in the pivottable area. |
| FormatAll(const Style& style) | Format all the cell in the pivottable area. |
| FormatRow(int32_t row, const Style& style) | Format the row data in the pivottable area. |
| GetAltTextDescription() | Gets the description of the alt text. |
| GetAltTextTitle() | Gets and sets the title of the alter text. |
| GetAutofitColumnWidthOnUpdate() | Indicates whether autofitting column width on update. |
| GetAutoFormatType() | Gets and sets the auto format type of PivotTable. |
| GetBaseFields() | Returns all base pivot fields in the PivotTable. |
| GetCellByDisplayName(const U16String& displayName) | Gets the Cell object by the display name of PivotField. |
| GetCellByDisplayName(const char16_t* displayName) | Gets the Cell object by the display name of PivotField. |
| GetChildren() | Gets the Children PivotTables which use this PivotTable data as data source. |
| 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 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 . |
| GetDataFieldHeaderName() | Gets and sets the name of the value area field header in the PivotTable. |
| 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 if the value is null. |
| 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() | (Deprecated) Gets the external connection data source. |
| GetFieldListSortAscending() | Indicates whether fields in the PivotTable are sorted in non-default order in the field list. |
| GetFields(PivotFieldType fieldType) | Gets the specific pivot field list by the region. |
| 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”. |
| GetHasBlankRows() | Indicates whether to add blank rows. This property only applies for the PivotTable auto format types which needs to add blank rows. |
| GetIndent() | Specifies the indentation increment for compact axis and can be used to set the Report Layout to Compact Form. |
| GetItemPrintTitles() | (Deprecated) Indicates whether PivotItem names should be repeated at the top of each printed page. |
| GetManualUpdate() | Indicates whether the PivotTable report is recalculated only at the user’s request. |
| GetMergeLabels() | True if 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. |
| GetName() | Gets the name of the PivotTable. |
| GetNamesOfSourceDataConnections() | Gets the name of external source data connections. |
| 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 and sets the order in which page fields are added to the PivotTable report’s layout. |
| 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 list of pivot filters. |
| GetPivotFormatConditions() | Gets the Format Conditions of the pivot table. |
| GetPivotFormats() | Gets the collection of formats applied to PivotTable. |
| GetPivotTableStyleName() | Gets and sets the pivottable style name. |
| GetPivotTableStyleType() | Gets and sets the built-in pivot table style. |
| 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 Refreshing Data or not. |
| GetRefreshDataOnOpeningFile() | Indicates whether Refresh Data when Opening File. |
| GetRefreshDate() | Gets the last date time when the PivotTable was refreshed. |
| GetRefreshedByWho() | Gets the name of the last user who refreshed this PivotTable. |
| GetRepeatItemsOnEachPrintedPage() | Indicates whether pivot item captions on the row area are repeated on each printed page for pivot fields in tabular form. |
| 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 and sets whether showing expand/collapse buttons. |
| 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 stripe formatting is applied for column. |
| GetShowPivotStyleLastColumn() | Indicates whether the column 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() | Indicates whether showing values row. |
| GetSource() | Get pivottable’s source data. |
| GetSourceDataConnections() | Gets the external connection data sources. |
| GetSourceType() | Gets the data source type of the pivot table. |
| 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. |
| IsAutoFormat() | Indicates whether the PivotTable report is automatically formatted. Checkbox “autoformat table " which is in pivottable option for Excel 2003. |
| 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. |
| IsNull() const | Checks whether the implementation object is nullptr. |
| IsSelected() | Indicates whether this PivotTable is selected. |
| Move(int32_t row, int32_t column) | Moves the PivotTable to a different location in the worksheet. |
| Move(const U16String& destCellName) | Moves the PivotTable to a different location in the worksheet. |
| Move(const char16_t* destCellName) | Moves the PivotTable to a different location in the worksheet. |
| explicit operator bool() const | operator bool() |
| operator=(const PivotTable& src) | operator= |
| PivotTable(PivotTable_Impl* impl) | Constructs from an implementation object. |
| PivotTable(const PivotTable& src) | Copy constructor. |
| RefreshData() | Refreshes pivottable’s data and setting from it’s data source. |
| RefreshData(const PivotTableRefreshOption& option) | Refreshes pivottable’s data and setting from it’s data source with options. |
| RemoveField(PivotFieldType fieldType, const U16String& fieldName) | Removes a field from specific field area. |
| RemoveField(PivotFieldType fieldType, const char16_t* fieldName) | Removes a field from specific field area. |
| RemoveField(PivotFieldType fieldType, int32_t baseFieldIndex) | Removes a field from specific field area. |
| RemoveField(PivotFieldType fieldType, const PivotField& pivotField) | Remove field from specific field area. |
| SetAltTextDescription(const U16String& value) | Gets the description of the alt text. |
| SetAltTextDescription(const char16_t* value) | Gets the description of the alt text. |
| SetAltTextTitle(const U16String& value) | Gets and sets the title of the alter text. |
| SetAltTextTitle(const char16_t* value) | Gets and sets the title of the alter text. |
| SetAutofitColumnWidthOnUpdate(bool value) | Indicates whether autofitting column width on update. |
| SetAutoFormatType(PivotTableAutoFormatType value) | Gets and sets the auto format type of PivotTable. |
| SetColumnGrand(bool value) | Indicates whether the PivotTable report shows grand totals for columns. |
| SetColumnHeaderCaption(const U16String& value) | Gets the Column Header Caption of the PivotTable. |
| SetColumnHeaderCaption(const char16_t* value) | Gets the Column Header Caption of the PivotTable. |
| SetCustomListSort(bool value) | Indicates whether consider built-in custom list when sort data. |
| SetDataFieldHeaderName(const U16String& value) | Gets and sets the name of the value area field header in the PivotTable. |
| SetDataFieldHeaderName(const char16_t* value) | Gets and sets the name of the value area field header in the PivotTable. |
| SetDataSource(const Vector <U16String>& value) | Gets and sets the data source of the pivot table. |
| SetDisplayErrorString(bool value) | Indicates whether the PivotTable report displays a custom string in cells that contain errors. |
| SetDisplayImmediateItems(bool value) | 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(bool value) | Indicates whether the PivotTable report displays a custom string if the value is null. |
| SetEnableDataValueEditing(bool value) | 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(bool value) | Gets whether drilldown is enabled. |
| SetEnableFieldDialog(bool value) | Indicates whether the PivotTable Field dialog box is available when the user double-clicks the PivotTable field. |
| SetEnableFieldList(bool value) | Gets whether enable the field list for the PivotTable. |
| SetEnableWizard(bool value) | Indicates whether the PivotTable Wizard is available. |
| SetErrorString(const U16String& value) | Gets the string displayed in cells that contain errors when the DisplayErrorString property is true.The default value is an empty string. |
| SetErrorString(const char16_t* value) | Gets the string displayed in cells that contain errors when the DisplayErrorString property is true.The default value is an empty string. |
| SetFieldListSortAscending(bool value) | Indicates whether fields in the PivotTable are sorted in non-default order in the field list. |
| SetGrandTotalName(const U16String& value) | Returns the text string label that is displayed in the grand total column or row heading. The default value is the string “Grand Total”. |
| SetGrandTotalName(const char16_t* value) | Returns the text string label that is displayed in the grand total column or row heading. The default value is the string “Grand Total”. |
| SetHasBlankRows(bool value) | Indicates whether to add blank rows. This property only applies for the PivotTable auto format types which needs to add blank rows. |
| SetIndent(int32_t value) | Specifies the indentation increment for compact axis and can be used to set the Report Layout to Compact Form. |
| SetIsAutoFormat(bool value) | Indicates whether the PivotTable report is automatically formatted. Checkbox “autoformat table " which is in pivottable option for Excel 2003. |
| SetIsExcel2003Compatible(bool value) | 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. |
| SetIsGridDropZones(bool value) | Indicates whether the PivotTable report displays classic pivottable layout. (enables dragging fields in the grid) |
| SetIsMultipleFieldFilters(bool value) | Specifies a boolean value that indicates whether the fields of a PivotTable can have multiple filters set on them. |
| SetIsSelected(bool value) | Indicates whether this PivotTable is selected. |
| SetItemPrintTitles(bool value) | (Deprecated) Indicates whether PivotItem names should be repeated at the top of each printed page. |
| SetManualUpdate(bool value) | Indicates whether the PivotTable report is recalculated only at the user’s request. |
| SetMergeLabels(bool value) | True if the specified PivotTable report’s outer-row item, column item, subtotal, and grand total labels use merged cells. |
| SetMissingItemsLimit(PivotMissingItemLimitType value) | Specifies a boolean value that indicates whether the fields of a PivotTable can have multiple filters set on them. |
| SetName(const U16String& value) | Gets the name of the PivotTable. |
| SetName(const char16_t* value) | Gets the name of the PivotTable. |
| SetNullString(const U16String& value) | Gets the string displayed in cells that contain null values when the DisplayNullString property is true.The default value is an empty string. |
| SetNullString(const char16_t* value) | Gets the string displayed in cells that contain null values when the DisplayNullString property is true.The default value is an empty string. |
| SetPageFieldOrder(PrintOrderType value) | Gets and sets the order in which page fields are added to the PivotTable report’s layout. |
| SetPageFieldWrapCount(int32_t value) | Gets the number of page fields in each column or row in the PivotTable report. |
| SetPivotTableStyleName(const U16String& value) | Gets and sets the pivottable style name. |
| SetPivotTableStyleName(const char16_t* value) | Gets and sets the pivottable style name. |
| SetPivotTableStyleType(PivotTableStyleType value) | Gets and sets the built-in pivot table style. |
| SetPreserveFormatting(bool value) | Indicates whether formatting is preserved when the PivotTable is refreshed or recalculated. |
| SetPrintDrill(bool value) | Specifies a boolean value that indicates whether drill indicators should be printed. print expand/collapse buttons when displayed on pivottable. |
| SetPrintTitles(bool value) | Indicates whether the print titles for the worksheet are set based on the PivotTable report. The default value is false. |
| SetRefreshDataFlag(bool value) | Indicates whether Refreshing Data or not. |
| SetRefreshDataOnOpeningFile(bool value) | Indicates whether Refresh Data when Opening File. |
| SetRepeatItemsOnEachPrintedPage(bool value) | Indicates whether pivot item captions on the row area are repeated on each printed page for pivot fields in tabular form. |
| SetRowGrand(bool value) | Indicates whether the PivotTable report shows grand totals for rows. |
| SetRowHeaderCaption(const U16String& value) | Gets the Row Header Caption of the PivotTable. |
| SetRowHeaderCaption(const char16_t* value) | Gets the Row Header Caption of the PivotTable. |
| SetSaveData(bool value) | Indicates whether data for the PivotTable report is saved with the workbook. |
| SetShowDataTips(bool value) | Specifies a boolean value that indicates whether tooltips should be displayed for PivotTable data cells. |
| SetShowDrill(bool value) | Gets and sets whether showing expand/collapse buttons. |
| SetShowEmptyCol(bool value) | Specifies a boolean value that indicates whether to include empty columns in the table. |
| SetShowEmptyRow(bool value) | Specifies a boolean value that indicates whether to include empty rows in the table. |
| SetShowMemberPropertyTips(bool value) | Specifies a boolean value that indicates whether member property information should be omitted from PivotTable tooltips. |
| SetShowPivotStyleColumnHeader(bool value) | Indicates whether the column header in the pivot table should have the style applied. |
| SetShowPivotStyleColumnStripes(bool value) | Indicates whether stripe formatting is applied for column. |
| SetShowPivotStyleLastColumn(bool value) | Indicates whether the column formatting is applied. |
| SetShowPivotStyleRowHeader(bool value) | Indicates whether the row header in the pivot table should have the style applied. |
| SetShowPivotStyleRowStripes(bool value) | Indicates whether row stripe formatting is applied. |
| SetShowRowHeaderCaption(bool value) | Indicates whether row header caption is shown in the PivotTable report Indicates whether Display field captions and filter drop downs. |
| SetShowValuesRow(bool value) | Indicates whether showing values row. |
| SetSubtotalHiddenPageItems(bool value) | 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(const U16String& value) | Gets a string saved with the PivotTable report. |
| SetTag(const char16_t* value) | Gets a string saved with the PivotTable report. |
| ShowDetail(int32_t rowOffset, int32_t columnOffset, bool newSheet, int32_t destRow, int32_t destColumn) | Show the detail of one item in the data region to a new Table. |
| ShowInCompactForm() | Layouts the PivotTable in compact form. |
| ShowInOutlineForm() | Layouts the PivotTable in outline form. |
| ShowInTabularForm() | Layouts the PivotTable in tabular form. |
| ShowReportFilterPage(const PivotField& pageField) | Show all the report filter pages according to PivotField, the PivotField must be located in the PageFields. |
| ShowReportFilterPageByIndex(int32_t posIndex) | Show all the report filter pages according to the position index in the PageFields. |
| ShowReportFilterPageByName(const U16String& fieldName) | Show all the report filter pages according to PivotField’s name, the PivotField must be located in the PageFields. |
| ShowReportFilterPageByName(const char16_t* fieldName) | Show all the report filter pages according to PivotField’s name, the PivotField must be located in the PageFields. |
| ~PivotTable() | Destructor. |
Fields
| Field | Description |
|---|---|
| _impl | The implementation object. |
Examples
Aspose::Cells::Startup();
Workbook book;
Worksheet sheet = book.GetWorksheets().Get(0);
Cells cells = sheet.GetCells();
cells.Get(0, 0).PutValue(u"fruit");
cells.Get(1, 0).PutValue(u"grape");
cells.Get(2, 0).PutValue(u"blueberry");
cells.Get(3, 0).PutValue(u"kiwi");
cells.Get(4, 0).PutValue(u"cherry");
cells.Get(5, 0).PutValue(u"grape");
cells.Get(6, 0).PutValue(u"blueberry");
cells.Get(7, 0).PutValue(u"kiwi");
cells.Get(8, 0).PutValue(u"cherry");
cells.Get(0, 1).PutValue(u"year");
cells.Get(1, 1).PutValue(2020);
cells.Get(2, 1).PutValue(2020);
cells.Get(3, 1).PutValue(2020);
cells.Get(4, 1).PutValue(2020);
cells.Get(5, 1).PutValue(2021);
cells.Get(6, 1).PutValue(2021);
cells.Get(7, 1).PutValue(2021);
cells.Get(8, 1).PutValue(2021);
cells.Get(0, 2).PutValue(u"amount");
cells.Get(1, 2).PutValue(50);
cells.Get(2, 2).PutValue(60);
cells.Get(3, 2).PutValue(70);
cells.Get(4, 2).PutValue(80);
cells.Get(5, 2).PutValue(90);
cells.Get(6, 2).PutValue(100);
cells.Get(7, 2).PutValue(110);
cells.Get(8, 2).PutValue(120);
PivotTableCollection pivots = sheet.GetPivotTables();
int pivotIndex = pivots.Add(u"=Sheet1!A1:C9", u"A12", u"TestPivotTable");
PivotTable pivot = pivots.Get(pivotIndex);
pivot.AddFieldToArea(PivotFieldType::Row, u"fruit");
pivot.AddFieldToArea(PivotFieldType::Column, u"year");
pivot.AddFieldToArea(PivotFieldType::Data, u"amount");
pivot.SetPivotTableStyleType(PivotTableStyleType::PivotTableStyleMedium10);
//Change PivotField's attributes
PivotField rowField = pivot.GetRowFields().Get(0);
rowField.SetDisplayName(u"custom display name");
//Add PivotFilter
int index = pivot.GetPivotFilters().Add(0, PivotFilterType::Count);
PivotFilter filter = pivot.GetPivotFilters().Get(index);
filter.GetAutoFilter().FilterTop10(0, false, false, 2);
//Add PivotFormatCondition
int formatIndex = pivot.GetPivotFormatConditions().Add();
PivotFormatCondition pfc = pivot.GetPivotFormatConditions().Get(formatIndex);
FormatConditionCollection fcc = pfc.GetFormatConditions();
fcc.AddArea(pivot.GetDataBodyRange());
int idx = fcc.AddCondition(FormatConditionType::CellValue);
FormatCondition fc = fcc.Get(idx);
fc.SetFormula1(u"100");
fc.SetOperator(OperatorType::GreaterOrEqual);
fc.GetStyle().SetBackgroundColor(Color{ 0xff, 0xff, 0, 0 });//Red
pivot.RefreshData();
pivot.CalculateData();
book.Save("out.xlsx");
Aspose::Cells::Cleanup();
See Also
- Namespace Aspose::Cells::Pivot
- Library Aspose.Cells for C++