Class PivotTable
Contents
[
Hide
]PivotTable class
Summary description for PivotTable.
public class PivotTable : IDisposable
Properties
| Name | Description |
|---|---|
| AltTextDescription { get; set; } | Gets the description of the alt text. |
| AltTextTitle { get; set; } | Gets and sets the title of the alter text. |
| AutofitColumnWidthOnUpdate { get; set; } | Indicates whether autofitting column width on update |
| AutoFormatType { get; set; } | Gets and sets the auto format type of PivotTable. |
| BaseFields { get; } | Returns all base pivot fields in the PivotTable. |
| ColumnFields { get; } | Returns a PivotFields object that are currently shown as column fields. |
| ColumnGrand { get; set; } | Indicates whether the PivotTable report shows grand totals for columns. |
| ColumnHeaderCaption { get; set; } | Gets the Column Header Caption of the PivotTable. |
| ColumnRange { get; } | Returns a CellArea object that represents the range that contains the column area in the PivotTable report. Read-only. |
| CustomListSort { get; set; } | Indicates whether consider built-in custom list when sort data |
| DataBodyRange { get; } | 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. |
| DataField { get; } | 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 . |
| DataFieldHeaderName { get; set; } | Gets and sets the name of the value area field header in the PivotTable. |
| DataFields { get; } | 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. |
| DataSource { get; set; } | Gets and sets the data source of the pivot table. |
| DisplayErrorString { get; set; } | Indicates whether the PivotTable report displays a custom string in cells that contain errors. |
| DisplayImmediateItems { get; set; } | 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. |
| DisplayNullString { get; set; } | Indicates whether the PivotTable report displays a custom string if the value is null. |
| EnableDataValueEditing { get; set; } | 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 |
| EnableDrilldown { get; set; } | Gets whether drilldown is enabled. |
| EnableFieldDialog { get; set; } | Indicates whether the PivotTable Field dialog box is available when the user double-clicks the PivotTable field. |
| EnableFieldList { get; set; } | Gets whether enable the field list for the PivotTable. |
| EnableWizard { get; set; } | Indicates whether the PivotTable Wizard is available. |
| ErrorString { get; set; } | Gets the string displayed in cells that contain errors when the DisplayErrorString property is true.The default value is an empty string. |
| ExternalConnectionDataSource { get; } | (Obsolete.) Gets the external connection data source. |
| FieldListSortAscending { get; set; } | Indicates whether fields in the PivotTable are sorted in non-default order in the field list. |
| GrandTotalName { get; set; } | Returns the text string label that is displayed in the grand total column or row heading. The default value is the string “Grand Total”. |
| HasBlankRows { get; set; } | Indicates whether to add blank rows. This property only applies for the PivotTable auto format types which needs to add blank rows. |
| Indent { get; set; } | Specifies the indentation increment for compact axis and can be used to set the Report Layout to Compact Form. |
| IsAutoFormat { get; set; } | Indicates whether the PivotTable report is automatically formatted. Checkbox “autoformat table " which is in pivottable option for Excel 2003 |
| IsExcel2003Compatible { get; set; } | 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 { get; set; } | Indicates whether the PivotTable report displays classic pivottable layout. (enables dragging fields in the grid) |
| IsMultipleFieldFilters { get; set; } | Specifies a boolean value that indicates whether the fields of a PivotTable can have multiple filters set on them. |
| IsSelected { get; set; } | Indicates whether this PivotTable is selected. |
| ItemPrintTitles { get; set; } | (Obsolete.) Indicates whether PivotItem names should be repeated at the top of each printed page. |
| ManualUpdate { get; set; } | Indicates whether the PivotTable report is recalculated only at the user’s request. |
| MergeLabels { get; set; } | True if the specified PivotTable report’s outer-row item, column item, subtotal, and grand total labels use merged cells. |
| MissingItemsLimit { get; set; } | Specifies a boolean value that indicates whether the fields of a PivotTable can have multiple filters set on them. |
| Name { get; set; } | Gets the name of the PivotTable |
| NullString { get; set; } | Gets the string displayed in cells that contain null values when the DisplayNullString property is true.The default value is an empty string. |
| PageFieldOrder { get; set; } | Gets and sets the order in which page fields are added to the PivotTable report’s layout. |
| PageFields { get; } | Returns a PivotFields object that are currently shown as page fields. |
| PageFieldWrapCount { get; set; } | Gets the number of page fields in each column or row in the PivotTable report. |
| PivotFilters { get; } | Returns a list of pivot filters. |
| PivotFormatConditions { get; } | Gets the Format Conditions of the pivot table. |
| PivotFormats { get; } | Gets the collection of formats applied to PivotTable. |
| PivotTableStyleName { get; set; } | Gets and sets the pivottable style name. |
| PivotTableStyleType { get; set; } | Gets and sets the built-in pivot table style. |
| PreserveFormatting { get; set; } | Indicates whether formatting is preserved when the PivotTable is refreshed or recalculated. |
| PrintDrill { get; set; } | Specifies a boolean value that indicates whether drill indicators should be printed. print expand/collapse buttons when displayed on pivottable. |
| PrintTitles { get; set; } | Indicates whether the print titles for the worksheet are set based on the PivotTable report. The default value is false. |
| RefreshDataFlag { get; set; } | Indicates whether Refreshing Data or not. |
| RefreshDataOnOpeningFile { get; set; } | Indicates whether Refresh Data when Opening File. |
| RefreshDate { get; } | Gets the last date time when the PivotTable was refreshed. |
| RefreshedByWho { get; } | Gets the name of the last user who refreshed this PivotTable |
| RepeatItemsOnEachPrintedPage { get; set; } | Indicates whether pivot item captions on the row area are repeated on each printed page for pivot fields in tabular form. |
| RowFields { get; } | Returns a PivotFields object that are currently shown as row fields. |
| RowGrand { get; set; } | Indicates whether the PivotTable report shows grand totals for rows. |
| RowHeaderCaption { get; set; } | Gets the Row Header Caption of the PivotTable. |
| RowRange { get; } | Returns a CellArea object that represents the range that contains the row area in the PivotTable report. Read-only. |
| SaveData { get; set; } | Indicates whether data for the PivotTable report is saved with the workbook. |
| ShowDataTips { get; set; } | Specifies a boolean value that indicates whether tooltips should be displayed for PivotTable data cells. |
| ShowDrill { get; set; } | Gets and sets whether showing expand/collapse buttons. |
| ShowEmptyCol { get; set; } | Specifies a boolean value that indicates whether to include empty columns in the table |
| ShowEmptyRow { get; set; } | Specifies a boolean value that indicates whether to include empty rows in the table. |
| ShowMemberPropertyTips { get; set; } | Specifies a boolean value that indicates whether member property information should be omitted from PivotTable tooltips. |
| ShowPivotStyleColumnHeader { get; set; } | Indicates whether the column header in the pivot table should have the style applied. |
| ShowPivotStyleColumnStripes { get; set; } | Indicates whether stripe formatting is applied for column. |
| ShowPivotStyleLastColumn { get; set; } | Indicates whether the column formatting is applied. |
| ShowPivotStyleRowHeader { get; set; } | Indicates whether the row header in the pivot table should have the style applied. |
| ShowPivotStyleRowStripes { get; set; } | Indicates whether row stripe formatting is applied. |
| ShowRowHeaderCaption { get; set; } | Indicates whether row header caption is shown in the PivotTable report Indicates whether Display field captions and filter drop downs |
| ShowValuesRow { get; set; } | Indicates whether showing values row. |
| SourceType { get; } | Gets the data source type of the pivot table. |
| SubtotalHiddenPageItems { get; set; } | 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. |
| TableRange1 { get; } | Returns a CellArea object that represents the range containing the entire PivotTable report, but doesn’t include page fields. Read-only. |
| TableRange2 { get; } | Returns a CellArea object that represents the range containing the entire PivotTable report, includes page fields. Read-only. |
| Tag { get; set; } | Gets a string saved with the PivotTable report. |
Methods
| Name | Description |
|---|---|
| AddCalculatedField(string, string) | Adds a calculated field to pivot field and drag it to data area. |
| AddCalculatedField(string, string, bool) | Adds a calculated field to pivot field. |
| AddFieldToArea(PivotFieldType, int) | Adds the field to the specific area. |
| AddFieldToArea(PivotFieldType, PivotField) | Adds the field to the specific area. |
| AddFieldToArea(PivotFieldType, string) | Adds the field to the specific area. |
| CalculateData() | Calculates pivottable’s data to cells. |
| CalculateData(PivotTableCalculateOption) | Calculating pivot tables with options |
| CalculateRange() | Calculates pivottable’s range. |
| ChangeDataSource(string[]) | Set pivottable’s source data. Sheet1!$A$1:$C$3 |
| ClearData() | Clear PivotTable’s data and formatting |
| CopyStyle(PivotTable) | Copies named style from another pivot table. |
| Dispose() | Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources. |
| Fields(PivotFieldType) | (Obsolete.) Gets the specific fields by the field type. |
| Format(PivotArea, Style) | Formats selected area of the PivotTable. |
| Format(int, int, Style) | Format the cell in the pivottable area |
| FormatAll(Style) | Format all the cell in the pivottable area |
| FormatRow(int, Style) | Format the row data in the pivottable area |
| GetCellByDisplayName(string) | Gets the Cell object by the display name of PivotField. |
| GetChildren() | Gets the Children Pivot Tables which use this PivotTable data as data source. |
| GetFields(PivotFieldType) | Gets the specific pivot field list by the region. |
| GetHorizontalBreaks() | get pivot table row index list of horizontal pagebreaks |
| GetNamesOfSourceDataConnections() | Gets the name of external source data connections. |
| GetSource() | Get pivottable’s source data. |
| GetSourceDataConnections() | Gets the external connection data sources. |
| Move(string) | Moves the PivotTable to a different location in the worksheet. |
| Move(int, int) | Moves the PivotTable to a different location in the worksheet. |
| RefreshData() | Refreshes pivottable’s data and setting from it’s data source. |
| RefreshData(PivotTableRefreshOption) | Refreshes pivottable’s data and setting from it’s data source with options. |
| RemoveField(PivotFieldType, int) | Removes a field from specific field area |
| RemoveField(PivotFieldType, PivotField) | Remove field from specific field area |
| RemoveField(PivotFieldType, string) | Removes a field from specific field area |
| SetAutoGroupField(int) | (Obsolete.) Sets auto field group by the PivotTable. |
| SetAutoGroupField(PivotField) | (Obsolete.) Sets auto field group by the PivotTable. |
| SetManualGroupField(int, DateTime, DateTime, ArrayList, int) | (Obsolete.) Sets manual field group by the PivotTable. |
| SetManualGroupField(int, double, double, ArrayList, double) | (Obsolete.) Sets manual field group by the PivotTable. |
| SetManualGroupField(PivotField, DateTime, DateTime, ArrayList, int) | (Obsolete.) Sets manual field group by the PivotTable. |
| SetManualGroupField(PivotField, double, double, ArrayList, double) | (Obsolete.) Sets manual field group by the PivotTable. |
| SetUngroup(int) | (Obsolete.) Sets ungroup by the PivotTable |
| SetUngroup(PivotField) | (Obsolete.) Sets ungroup by the PivotTable |
| ShowDetail(int, int, bool, int, int) | 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(PivotField) | Show all the report filter pages according to PivotField, the PivotField must be located in the PageFields. |
| ShowReportFilterPageByIndex(int) | Show all the report filter pages according to the position index in the PageFields |
| ShowReportFilterPageByName(string) | Show all the report filter pages according to PivotField’s name, the PivotField must be located in the PageFields. |
Examples
[C#]
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0];
Cells cells = sheet.Cells;
cells[0, 0].Value = "fruit";
cells[1, 0].Value = "grape";
cells[2, 0].Value = "blueberry";
cells[3, 0].Value = "kiwi";
cells[4, 0].Value = "cherry";
cells[5, 0].Value = "grape";
cells[6, 0].Value = "blueberry";
cells[7, 0].Value = "kiwi";
cells[8, 0].Value = "cherry";
cells[0, 1].Value = "year";
cells[1, 1].Value = 2020;
cells[2, 1].Value = 2020;
cells[3, 1].Value = 2020;
cells[4, 1].Value = 2020;
cells[5, 1].Value = 2021;
cells[6, 1].Value = 2021;
cells[7, 1].Value = 2021;
cells[8, 1].Value = 2021;
cells[0, 2].Value = "amount";
cells[1, 2].Value = 50;
cells[2, 2].Value = 60;
cells[3, 2].Value = 70;
cells[4, 2].Value = 80;
cells[5, 2].Value = 90;
cells[6, 2].Value = 100;
cells[7, 2].Value = 110;
cells[8, 2].Value = 120;
PivotTableCollection pivots = sheet.PivotTables;
int pivotIndex = pivots.Add("=Sheet1!A1:C9", "A12", "TestPivotTable");
PivotTable pivot = pivots[pivotIndex];
pivot.AddFieldToArea(PivotFieldType.Row, "fruit");
pivot.AddFieldToArea(PivotFieldType.Column, "year");
pivot.AddFieldToArea(PivotFieldType.Data, "amount");
pivot.PivotTableStyleType = PivotTableStyleType.PivotTableStyleMedium10;
//Change PivotField's attributes
PivotField rowField = pivot.RowFields[0];
rowField.DisplayName = "custom display name";
//Add PivotFilter
int index = pivot.PivotFilters.Add(0, PivotFilterType.Count);
PivotFilter filter = pivot.PivotFilters[index];
filter.AutoFilter.FilterTop10(0, false, false, 2);
//Add PivotFormatCondition
int formatIndex = pivot.PivotFormatConditions.Add();
PivotFormatCondition pfc = pivot.PivotFormatConditions[formatIndex];
FormatConditionCollection fcc = pfc.FormatConditions;
fcc.AddArea(pivot.DataBodyRange);
int idx = fcc.AddCondition(FormatConditionType.CellValue);
FormatCondition fc = fcc[idx];
fc.Formula1 = "100";
fc.Operator = OperatorType.GreaterOrEqual;
fc.Style.BackgroundColor = Color.Red;
pivot.RefreshData();
pivot.CalculateData();
//do your business
book.Save("out.xlsx");
[Visual Basic]
Dim book As Workbook = New Workbook()
Dim sheet As Worksheet = book.Worksheets(0)
Dim cells As Cells = sheet.Cells
cells(0, 0).Value = "fruit"
cells(1, 0).Value = "grape"
cells(2, 0).Value = "blueberry"
cells(3, 0).Value = "kiwi"
cells(4, 0).Value = "cherry"
cells(5, 0).Value = "grape"
cells(6, 0).Value = "blueberry"
cells(7, 0).Value = "kiwi"
cells(8, 0).Value = "cherry"
cells(0, 1).Value = "year"
cells(1, 1).Value = 2020
cells(2, 1).Value = 2020
cells(3, 1).Value = 2020
cells(4, 1).Value = 2020
cells(5, 1).Value = 2021
cells(6, 1).Value = 2021
cells(7, 1).Value = 2021
cells(8, 1).Value = 2021
cells(0, 2).Value = "amount"
cells(1, 2).Value = 50
cells(2, 2).Value = 60
cells(3, 2).Value = 70
cells(4, 2).Value = 80
cells(5, 2).Value = 90
cells(6, 2).Value = 100
cells(7, 2).Value = 110
cells(8, 2).Value = 120
Dim pivots As PivotTableCollection = sheet.PivotTables
Dim pivotIndex As Int32 = pivots.Add("=Sheet1!A1:C9", "A12", "TestPivotTable")
Dim pivot As PivotTable = pivots(pivotIndex)
pivot.AddFieldToArea(PivotFieldType.Row, "fruit")
Pivot.AddFieldToArea(PivotFieldType.Column, "year")
Pivot.AddFieldToArea(PivotFieldType.Data, "amount")
pivot.PivotTableStyleType = PivotTableStyleType.PivotTableStyleMedium10
'Change PivotField's attributes
Dim rowField As PivotField = pivot.RowFields(0)
rowField.DisplayName = "custom display name"
'Add PivotFilter
Dim filterIndex As Int32 = pivot.PivotFilters.Add(0, PivotFilterType.Count)
Dim filter As PivotFilter = pivot.PivotFilters(filterIndex)
filter.AutoFilter.FilterTop10(0, False, False, 2)
'Add PivotFormatCondition
Dim formatIndex As Int32 = pivot.PivotFormatConditions.Add()
Dim pfc As PivotFormatCondition = pivot.PivotFormatConditions(formatIndex)
Dim fcc As FormatConditionCollection = pfc.FormatConditions
fcc.AddArea(pivot.DataBodyRange)
Dim idx As Int32 = fcc.AddCondition(FormatConditionType.CellValue)
Dim fc As FormatCondition = fcc(idx)
fc.Formula1 = "100"
fc.Operator = OperatorType.GreaterOrEqual
fc.Style.BackgroundColor = Color.Red
pivot.RefreshData()
pivot.CalculateData()
book.Save("out_vb.xlsx")
See Also
- namespace Aspose.Cells.Pivot
- assembly Aspose.Cells
