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() | 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() | 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. |
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. |
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() | Specifies a boolean value that indicates whether show values row. show the values row. |
GetSource() | Get pivottable’s source data. |
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) | 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. |
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) | Specifies a boolean value that indicates whether show values row. show the 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. |
ShowDatail(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++