Class PivotTable

PivotTable class

Summary description for PivotTable.

public class PivotTable : IDisposable

Properties

NameDescription
AllowMultipleFiltersPerField { get; set; }Specifies a boolean value that indicates whether the fields of a PivotTable can have multiple filters set on them.
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; }(Obsolete.) 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.
ConditionalFormats { get; }Gets the conditional formats of the pivot table.
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; }Indicates whether the field list for the PivotTable is available on the view of Excel.
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 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; }(Obsolete.) 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 all filters of pivot fields in the pivot table.
PivotFormatConditions { get; }(Obsolete.) 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; }(Obsolete.) 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; }(Obsolete.) Indicates whether to show grand totals for rows of this pivot table.
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.
ShowColumnGrandTotals { get; set; }Indicates whether to show grand totals for columns of this pivot table.
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; }Indicates whether to include empty columns in the table
ShowEmptyRow { get; set; }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.
ShowRowGrandTotals { get; set; }Indicates whether to show grand totals for rows of the pivot table.
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

NameDescription
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.
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(CellArea, Style)Formats selected area of the PivotTable.
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()(Obsolete.) Gets pivot table row index list of horizontal page breaks
GetHorizontalPageBreaks()Gets horizontal page breaks of this pivot table.
GetNamesOfSourceDataConnections()Gets the name of external source data connections.
GetSource()Get pivottable’s source data.
GetSource(bool)Get pivottable’s source data.
GetSourceDataConnections()Gets the external connection data sources.
Move(string)(Obsolete.) Moves the PivotTable to a different location in the worksheet.
Move(int, int)(Obsolete.) Moves the PivotTable to a different location in the worksheet.
MoveTo(string)Moves the PivotTable to a different location in the worksheet.
MoveTo(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
SelectArea(CellArea)Select an area of pivot table view.
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

using System;
using Aspose.Cells;
using Aspose.Cells.Pivot;

namespace AsposeCellsExamples
{
    public class PivotClassPivotTableDemo
    {
        public static void Run()
        {
            Workbook book = new Workbook();
            Worksheet sheet = book.Worksheets[0];
            Cells cells = sheet.Cells;

            // Set sample data
            cells["A1"].Value = "fruit";
            cells["A2"].Value = "grape";
            cells["A3"].Value = "blueberry";
            cells["A4"].Value = "kiwi";
            cells["A5"].Value = "cherry";
            cells["A6"].Value = "grape";
            cells["A7"].Value = "blueberry";
            cells["A8"].Value = "kiwi";
            cells["A9"].Value = "cherry";

            cells["B1"].Value = "year";
            cells["B2"].Value = 2020;
            cells["B3"].Value = 2020;
            cells["B4"].Value = 2020;
            cells["B5"].Value = 2020;
            cells["B6"].Value = 2021;
            cells["B7"].Value = 2021;
            cells["B8"].Value = 2021;
            cells["B9"].Value = 2021;

            cells["C1"].Value = "amount";
            cells["C2"].Value = 50;
            cells["C3"].Value = 60;
            cells["C4"].Value = 70;
            cells["C5"].Value = 80;
            cells["C6"].Value = 90;
            cells["C7"].Value = 100;
            cells["C8"].Value = 110;
            cells["C9"].Value = 120;

            // Create pivot table
            PivotTableCollection pivots = sheet.PivotTables;
            int pivotIndex = pivots.Add("=Sheet1!A1:C9", "A12", "TestPivotTable");
            PivotTable pivot = pivots[pivotIndex];

            // Add fields
            pivot.AddFieldToArea(PivotFieldType.Row, "fruit");
            pivot.AddFieldToArea(PivotFieldType.Column, "year");
            pivot.AddFieldToArea(PivotFieldType.Data, "amount");

            // Style the pivot table
            pivot.PivotTableStyleType = PivotTableStyleType.PivotTableStyleMedium10;

            // Customize row field
            PivotField rowField = pivot.RowFields[0];
            rowField.DisplayName = "Fruit Category";

            // Refresh and calculate data
            pivot.RefreshData();
            pivot.CalculateData();

            // Save the workbook
            book.Save("PivotTableDemo.xlsx");
        }
    }
}

See Also