Class PivotField

PivotField class

Represents a field in a PivotTable report.

public class PivotField

Properties

NameDescription
AutoShowCount { get; set; }Represent the number of top or bottom items that are automatically shown in the specified PivotTable field.
AutoShowField { get; set; }Represents auto show field index. -1 means PivotField itself. It should be the index of the data fields.
AutoSortField { get; set; }Represents the index of field which is auto sorted. -1 means PivotField itself,others means the position of the data fields.
BaseFieldIndex { get; set; }(Obsolete.) Represents the base field for a custom calculation when the ShowDataAs calculation is in use.
BaseIndex { get; set; }Represents the index in the source pivot fields.
BaseItemIndex { get; set; }(Obsolete.) Represents the item in the base field for a custom calculation when the ShowDataAs calculation is in use. Valid only for data fields.
BaseItemPosition { get; set; }(Obsolete.) Represents the item in the base field for a custom calculation when the ShowDataAs calculation is in use. Valid only for data fields. Because PivotItemPosition.Custom is only for read,if you need to set PivotItemPosition.Custom, please set PivotField.BaseItemIndex attribute.
CurrentPageItem { get; set; }Represents the current selected page item of the page field to filter data. Only valid for page fields.
DataDisplayFormat { get; set; }(Obsolete.) Represents how to display the values in a data field of the pivot report.
DisplayName { get; set; }Represents the display name of pivot field in the pivot table view.
DragToColumn { get; set; }Indicates whether the specified field can be dragged to the column position. The default value is true.
DragToData { get; set; }Indicates whether the specified field can be dragged to the values region. The default value is true.
DragToHide { get; set; }Indicates whether the specified field can be dragged to the hide region. The default value is true.
DragToPage { get; set; }Indicates whether the specified field can be dragged to the page position. The default value is true.
DragToRow { get; set; }Indicates whether the specified field can be dragged to the row region. The default value is true.
Function { get; set; }Represents the function used to summarize this PivotTable data field.
GroupSettings { get; }Gets the group settings of the pivot field.
InsertBlankRow { get; set; }Indicates whether to insert a blank line after each item.
IsAscendShow { get; set; }Indicates whether the specified PivotTable field is autoshown ascending.
IsAscendSort { get; set; }Indicates whether the items of this pivot field is autosorted ascending.
IsAutoShow { get; set; }Indicates whether the specified PivotTable field is automatically shown.
IsAutoSort { get; set; }Indicates whether the items of this PivotTable field are automatically sorted.
IsAutoSubtotals { get; set; }Indicates whether the specified field shows automatic subtotals. Default is true.
IsCalculatedField { get; }Indicates whether the this pivot field is calculated field.
IsIncludeNewItemsInFilter { get; set; }Indicates whether to include new items to the field in manual filter. The default value is false.
IsInsertPageBreaksBetweenItems { get; set; }Indicates whether to insert page breaks after each item. The default value is false.
IsMultipleItemSelectionAllowed { get; set; }Indicates whether multiple items could be selected in the page field. The default value is false.
IsRepeatItemLabels { get; set; }Indicates whether to repeat labels of the field in the region. The default value is false.
IsValueFields { get; }(Obsolete.) Indicates whether this field represents values fields.
IsValuesField { get; }Indicates whether this field represents values field.
ItemCount { get; }Gets the count of the base items in this pivot field.
Items { get; }Get all labels of pivot items in this field.
Name { get; set; }Represents the name of PivotField.
NonAutoSortDefault { get; set; }Indicates whether a sort operation that will be applied to this pivot field is an autosort operation or a simple data sort.
Number { get; set; }Represents the built-in display format of numbers and dates.
NumberFormat { get; set; }Represents the custom display format of numbers and dates.
OriginalItems { get; }Get the original base items;
PivotItems { get; }Gets the pivot items of the pivot field
Position { get; }Represents the index of PivotField in the region.
RegionType { get; }Specifies the region of the PivotTable that this field is displayed.
ShowAllItems { get; set; }Indicates whether to display all items in the PivotTable view, even if they don’t contain summary data. The default value is false.
ShowCompact { get; set; }Indicates whether to display labels of the next field in the same column on the Pivot Table view
ShowInOutlineForm { get; set; }Indicates whether to layout this field in outline form on the Pivot Table view.
ShowSubtotalAtTop { get; set; }Indicates whether to display subtotals at the top or bottom of items when ShowInOutlineForm is true, then
ShowValuesSetting { get; }Gets the settings of showing values as when the ShowDataAs calculation is in use.
SortSetting { get; }Gets all settings of auto sorting

Methods

NameDescription
AddCalculatedItem(string, string)Add a calculated formula item to the pivot field.
ClearFilter()Clears filter setting on this pivot field.
FilterByDate(PivotFilterType, DateTime, DateTime)Filters by date values of row or column pivot field.
FilterByLabel(PivotFilterType, string, string)Filters by captions of row or column pivot field.
FilterByValue(int, PivotFilterType, double, double)Filters by values of data pivot field.
FilterTop10(int, PivotFilterType, bool, int)Filters by values of data pivot field.
GetCalculatedFieldFormula()(Obsolete.) Get the formula string of the specified calculated field .
GetFilters()Gets all pivot filters applied for this pivot field.
GetFormula()Gets the formula of the calculated field . Only works for calculated field.
GetPivotFilterByType(PivotFilterType)Gets the pivot filter of the pivot field by type
GetPivotFilters()(Obsolete.) Gets the pivot filters of the pivot field
GetSubtotals(PivotFieldSubtotalType)Indicates whether to show specified subtotal for this pivot field.
GroupBy(CustomPiovtFieldGroupItem[], bool)Custom group the field.
GroupBy(double, bool)Automatically group the field with internal
GroupBy(double, double, double, bool)Group the file by number.
GroupBy(DateTime, DateTime, PivotGroupByType[], double, bool)Group the file by the date group types.
HideDetail(bool)Sets whether the detail of all PivotItems in a pivot field are hidden. That is collapse/expand this field.
HideItem(int, bool)Sets whether the specific PivotItem in a data field is hidden.
HideItem(string, bool)Sets whether the specific PivotItem in a data field is hidden.
HideItemDetail(int, bool)Sets whether the specific PivotItem in a pivot field is hidden detail.
InitPivotItems()Init the pivot items of the pivot field
IsHiddenItem(int)Gets whether the specific PivotItem is hidden.
IsHiddenItemDetail(int)Gets whether to hide the detail of the specific PivotItem..
SetSubtotals(PivotFieldSubtotalType, bool)Sets how to subtotal the specified field.
ShowValuesAs(PivotFieldDataDisplayFormat, int, PivotItemPositionType, int)Shows values of data field as different display format when the ShowDataAs calculation is in use.
SortBy(SortOrder, int)Sorts this pivot field.
SortBy(SortOrder, int, PivotLineType, string)Sorts this pivot field.
Ungroup()Ungroup the pivot field.

Examples

namespace AsposeCellsExamples
{
    using Aspose.Cells;
    using Aspose.Cells.Pivot;
    using System;

    public class PivotFieldDemo
    {
        public static void PivotFieldExample()
        {
            // Create a new workbook
            Workbook workbook = new Workbook();
            Worksheet worksheet = workbook.Worksheets[0];
            Cells cells = worksheet.Cells;

            // Add some data to the worksheet
            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;

            // Add a pivot table to the worksheet
            PivotTableCollection pivotTables = worksheet.PivotTables;
            int pivotIndex = pivotTables.Add("=Sheet1!A1:C9", "A12", "TestPivotTable");
            PivotTable pivotTable = pivotTables[pivotIndex];

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

            // Set pivot table style
            pivotTable.PivotTableStyleType = PivotTableStyleType.PivotTableStyleMedium10;

            // Access the row field
            PivotField rowField = pivotTable.RowFields[0];

            // Set properties of the PivotField
            rowField.DisplayName = "Custom Display Name";
            rowField.IsAutoSubtotals = false;
            rowField.DragToColumn = true;
            rowField.DragToHide = true;
            rowField.DragToRow = true;
            rowField.DragToPage = true;
            rowField.DragToData = true;
            rowField.IsMultipleItemSelectionAllowed = true;
            rowField.IsRepeatItemLabels = true;
            rowField.IsIncludeNewItemsInFilter = true;
            rowField.IsInsertPageBreaksBetweenItems = true;
            rowField.ShowAllItems = true;
            rowField.NonAutoSortDefault = true;
            rowField.IsAutoSort = true;
            rowField.IsAscendSort = true;
            rowField.AutoSortField = -1;
            rowField.IsAutoShow = true;
            rowField.IsAscendShow = true;
            rowField.AutoShowCount = 5;
            rowField.AutoShowField = -1;
            rowField.Function = ConsolidationFunction.Sum;
            rowField.DataDisplayFormat = PivotFieldDataDisplayFormat.PercentageOfTotal;
            rowField.BaseFieldIndex = 0;
            rowField.BaseItemPosition = PivotItemPosition.Next;
            rowField.BaseItemIndex = 0;
            rowField.CurrentPageItem = 1;
            rowField.Number = 0;
            rowField.InsertBlankRow = true;
            rowField.ShowSubtotalAtTop = true;
            rowField.ShowInOutlineForm = true;
            rowField.NumberFormat = "0.00";
            rowField.ShowCompact = true;

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

            // Save the workbook
            workbook.Save("PivotFieldExample.xlsx");
        }
    }
}

See Also