Class PivotField
Namespace: Aspose.Cells.Pivot
Assembly: Aspose.Cells.dll (25.2.0)
Represents a field in a PivotTable report.
public class PivotFieldInheritance
Inherited Members
object.GetType(), object.MemberwiseClone(), object.ToString(), object.Equals(object?), object.Equals(object?, object?), object.ReferenceEquals(object?, object?), object.GetHashCode()
Examples
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";
pivot.RefreshData();
pivot.CalculateData();
//do your business
book.Save("out.xlsx");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"
pivot.RefreshData()
pivot.CalculateData()
book.Save("out_vb.xlsx")Properties
AutoShowCount
Represent the number of top or bottom items that are automatically shown in the specified PivotTable field.
public int AutoShowCount { get; set; }Property Value
AutoShowField
Represents auto show field index. -1 means PivotField itself. It should be the index of the data fields.
public int AutoShowField { get; set; }Property Value
AutoSortField
Represents the index of field which is auto sorted. -1 means PivotField itself,others means the position of the data fields.
public int AutoSortField { get; set; }Property Value
BaseIndex
Represents the PivotField index in the base PivotFields.
public int BaseIndex { get; set; }Property Value
CurrentPageItem
Represents the current page item showing for the page field (valid only for page fields).
public short CurrentPageItem { get; set; }Property Value
DisplayName
Represents the PivotField display name.
public string DisplayName { get; set; }Property Value
DragToColumn
Indicates whether the specified field can be dragged to the column position. The default value is true.
public bool DragToColumn { get; set; }Property Value
DragToData
Indicates whether the specified field can be dragged to the data position. The default value is true.
public bool DragToData { get; set; }Property Value
DragToHide
Indicates whether the specified field can be dragged to the hide position. The default value is true.
public bool DragToHide { get; set; }Property Value
DragToPage
Indicates whether the specified field can be dragged to the page position. The default value is true.
public bool DragToPage { get; set; }Property Value
DragToRow
Indicates whether the specified field can be dragged to the row position. The default value is true.
public bool DragToRow { get; set; }Property Value
Function
Represents the function used to summarize the PivotTable data field.
public ConsolidationFunction Function { get; set; }Property Value
GroupSettings
Gets the group settings of the pivot field.
public PivotFieldGroupSettings GroupSettings { get; }Property Value
Remarks
If this field is not grouped, Null will be returned.
InsertBlankRow
Indicates whether inserting blank line after each item.
public bool InsertBlankRow { get; set; }Property Value
IsAscendShow
Indicates whether the specified PivotTable field is autoshown ascending.
public bool IsAscendShow { get; set; }Property Value
IsAscendSort
Indicates whether the specified PivotTable field is autosorted ascending.
public bool IsAscendSort { get; set; }Property Value
IsAutoShow
Indicates whether the specified PivotTable field is automatically shown,only valid for excel 2003.
public bool IsAutoShow { get; set; }Property Value
IsAutoSort
Indicates whether the specified PivotTable field is automatically sorted.
public bool IsAutoSort { get; set; }Property Value
IsAutoSubtotals
Indicates whether the specified field shows automatic subtotals. Default is true.
public bool IsAutoSubtotals { get; set; }Property Value
IsCalculatedField
Indicates whether the specified PivotTable field is calculated field.
public bool IsCalculatedField { get; }Property Value
IsIncludeNewItemsInFilter
Indicates whether including new items to the field in manual filter. The default value is false.
public bool IsIncludeNewItemsInFilter { get; set; }Property Value
IsInsertPageBreaksBetweenItems
Indicates whether inserting page breaks after each item. The default value is false.
public bool IsInsertPageBreaksBetweenItems { get; set; }Property Value
IsMultipleItemSelectionAllowed
indicates whether the field can have multiple items selected in the page field The default value is false.
public bool IsMultipleItemSelectionAllowed { get; set; }Property Value
IsRepeatItemLabels
Indicates whether repeating labels of the field in the region. The default value is false.
public bool IsRepeatItemLabels { get; set; }Property Value
IsValueFields
Indicates whether this field represents values fields.
public bool IsValueFields { get; }Property Value
ItemCount
Gets the count of the base items in this pivot field.
public int ItemCount { get; }Property Value
Items
Get all labels of pivot items in this field.
public string[] Items { get; }Property Value
string[]
Name
Represents the name of PivotField.
public string Name { get; set; }Property Value
NonAutoSortDefault
Indicates whether a sort operation that will be applied to this pivot field is an autosort operation or a simple data sort.
public bool NonAutoSortDefault { get; set; }Property Value
Number
Represents the built-in display format of numbers and dates.
public int Number { get; set; }Property Value
NumberFormat
Represents the custom display format of numbers and dates.
public string NumberFormat { get; set; }Property Value
OriginalItems
Get the original base items;
public string[] OriginalItems { get; }Property Value
string[]
PivotItems
Gets the pivot items of the pivot field
public PivotItemCollection PivotItems { get; }Property Value
Position
Represents the index of Aspose.Cells.Pivot.PivotField in the region.
public int Position { get; }Property Value
RegionType
Specifies the region of the PivotTable that this field is displayed.
public PivotFieldType RegionType { get; }Property Value
ShowAllItems
Indicates whether all items displays in the PivotTable report, even if they don’t contain summary data. show items with no data The default value is false.
public bool ShowAllItems { get; set; }Property Value
ShowCompact
Indicates whether display labels from the next field in the same column on the Pivot Table view
public bool ShowCompact { get; set; }Property Value
ShowInOutlineForm
Indicates whether layout this field in outline form on the Pivot Table view
public bool ShowInOutlineForm { get; set; }Property Value
ShowSubtotalAtTop
when ShowInOutlineForm is true, then display subtotals at the top of the list of items instead of at the bottom
public bool ShowSubtotalAtTop { get; set; }Property Value
Remarks
Only works when ShowInOutlineForm is true.
ShowValuesSetting
Gets the settings of showing values as when the ShowDataAs calculation is in use.
public PivotShowValuesSetting ShowValuesSetting { get; }Property Value
SortSetting
Gets all settings of auto sorting
public PivotFieldSortSetting SortSetting { get; }Property Value
Methods
AddCalculatedItem(string, string)
Add a calculated formula item to the pivot field.
public void AddCalculatedItem(string name, string formula)Parameters
name string
The item’s name.
formula string
The formula of pivot item.
Remarks
Only supports to add calculated item to Row/Column field.
ClearFilter()
Clears filter setting on this pivot field.
public void ClearFilter()FilterByDate(PivotFilterType, DateTime, DateTime)
Filters by date setting of row or column pivot field.
public PivotFilter FilterByDate(PivotFilterType type, DateTime dateTime1, DateTime dateTime2)Parameters
type PivotFilterType
The type of filtering data.
dateTime1 DateTime
The date label of filter condition
dateTime2 DateTime
The upper-bound date label of between filter condition
Returns
FilterByLabel(PivotFilterType, string, string)
Filters by captions of row or column pivot field.
public PivotFilter FilterByLabel(PivotFilterType type, string label1, string label2)Parameters
type PivotFilterType
The type of filtering data.
label1 string
The label of filter condition
label2 string
The upper-bound label of between filter condition
Returns
FilterByValue(int, PivotFilterType, double, double)
Filters by values of data pivot field.
public PivotFilter FilterByValue(int valueFieldIndex, PivotFilterType type, double value1, double value2)Parameters
valueFieldIndex int
The index of value field in the value region.
type PivotFilterType
The type of filtering data.
value1 double
The value of filter condition
value2 double
The upper-bound value of between filter condition
Returns
FilterTop10(int, PivotFilterType, bool, int)
Filters by values of data pivot field.
public PivotFilter FilterTop10(int valueFieldIndex, PivotFilterType type, bool isTop, int itemCount)Parameters
valueFieldIndex int
The index of data field in the data region.
type PivotFilterType
The type of filtering data. Only can be Count,Sum and Percent.
isTop bool
Indicates whether filter from top or bottom
itemCount int
The item count
Returns
GetFilters()
Gets all pivot filters of this pivot field.
public PivotFilter[] GetFilters()Returns
GetFormula()
Gets formula of the calculated field .
public string GetFormula()Returns
GetPivotFilterByType(PivotFilterType)
Gets the pivot filter of the pivot field by type
public PivotFilter GetPivotFilterByType(PivotFilterType type)Parameters
type PivotFilterType
Returns
GetSubtotals(PivotFieldSubtotalType)
Indicates whether showing specified subtotal.
public bool GetSubtotals(PivotFieldSubtotalType subtotalType)Parameters
subtotalType PivotFieldSubtotalType
subtotal type.
Returns
Returns whether showing specified subtotal.
GroupBy(double, bool)
Automatically group the field with internal
public void GroupBy(double interval, bool newField)Parameters
interval double
The internal of group. Automatic value will be assigned if it’s zero,
newField bool
Indicates whether adding a new field to the pivottable.
GroupBy(DateTime, DateTime, PivotGroupByType[], double, bool)
Group the file by the date group types.
public bool GroupBy(DateTime start, DateTime end, PivotGroupByType[] groups, double interval, bool firstAsNewField)Parameters
start DateTime
The start datetime
end DateTime
The end of datetime
groups PivotGroupByType[]
Group types
interval double
The interval
firstAsNewField bool
Indicates whether adding a new field to the pivottable. Only for the first group item.
Returns
False means this field could not be grouped by date time.
GroupBy(double, double, double, bool)
Group the file by number.
public bool GroupBy(double start, double end, double interval, bool newField)Parameters
start double
The start value
end double
The end of value
interval double
The interval
newField bool
Indicates whether adding a new field to the pivottable
Returns
False means this field could not be grouped by date time.
GroupBy(CustomPiovtFieldGroupItem[], bool)
Custom group the field.
public bool GroupBy(CustomPiovtFieldGroupItem[] customGroupItems, bool newField)Parameters
customGroupItems CustomPiovtFieldGroupItem[]
The custom group items.
newField bool
Indicates whether adding a new field to the pivottable
Returns
False means this field could not be grouped by date time.
HideDetail(bool)
Sets whether the PivotItems in a pivot field is hidden detail.That is collapse/expand this field.
public void HideDetail(bool isHiddenDetail)Parameters
isHiddenDetail bool
Whether hide the detail of the pivot field.
HideItem(int, bool)
Sets whether the specific PivotItem in a data field is hidden.
public void HideItem(int index, bool isHidden)Parameters
index int
the index of the pivotItem in the pivotField.
isHidden bool
whether the specific PivotItem is hidden
HideItem(string, bool)
Sets whether the specific PivotItem in a data field is hidden.
public void HideItem(string itemValue, bool isHidden)Parameters
itemValue string
the value of the pivotItem in the pivotField.
isHidden bool
whether the specific PivotItem is hidden
HideItemDetail(int, bool)
Sets whether the specific PivotItem in a pivot field is hidden detail.
public void HideItemDetail(int index, bool isHiddenDetail)Parameters
index int
the index of the pivotItem in the pivotField.
isHiddenDetail bool
whether the specific PivotItem is hidden
InitPivotItems()
Init the pivot items of the pivot field
public void InitPivotItems()IsHiddenItem(int)
Gets whether the specific PivotItem is hidden.
public bool IsHiddenItem(int index)Parameters
index int
The index of the pivotItem in the pivotField.
Returns
whether the specific PivotItem is hidden
IsHiddenItemDetail(int)
Gets whether hidding the detail of the specific PivotItem..
public bool IsHiddenItemDetail(int index)Parameters
index int
The index of the pivotItem in the pivotField.
Returns
whether the specific PivotItem is hidden detail
SetSubtotals(PivotFieldSubtotalType, bool)
Sets whether the specified field shows that subtotals.
public void SetSubtotals(PivotFieldSubtotalType subtotalType, bool shown)Parameters
subtotalType PivotFieldSubtotalType
subtotals type.
shown bool
whether the specified field shows that subtotals.
ShowValuesAs(PivotFieldDataDisplayFormat, int, PivotItemPositionType, int)
Shows values of data field as different display format when the ShowDataAs calculation is in use.
public void ShowValuesAs(PivotFieldDataDisplayFormat displayFormat, int baseField, PivotItemPositionType baseItemPositionType, int baseItem)Parameters
displayFormat PivotFieldDataDisplayFormat
The data display format type.
baseField int
The index to the field which ShowDataAs calculation bases on.
baseItemPositionType PivotItemPositionType
The position type of base iteam.
baseItem int
The index to the base item which ShowDataAs calculation bases on. Only works when baseItemPositionType is custom.
Remarks
Only for data field.
SortBy(SortOrder, int)
Sorts this pivot field.
public void SortBy(SortOrder sortType, int fieldSortedBy)Parameters
sortType SortOrder
The type of sorting this field.
fieldSortedBy int
The index of pivot field sorted by. -1 means sorting by data labels of this field, others mean the index of data field sorted by.
SortBy(SortOrder, int, PivotLineType, string)
Sorts this pivot field.
public void SortBy(SortOrder sortType, int fieldSortedBy, PivotLineType dataType, string cellName)Parameters
sortType SortOrder
The type of sorting this field.
fieldSortedBy int
The index of pivot field sorted by. -1 means sorting by data labels of this field, others mean the index of data field sorted by.
dataType PivotLineType
The type of data sorted by.
cellName string
Sort by values in the row or column
Ungroup()
Ungroup the pivot field.
public void Ungroup()