PivotField

PivotField class

Represents a field in a PivotTable report.

public class PivotField

Properties

Name Description
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 auto sort field index. -1 means PivotField itself,others means the position of the data fields.
BaseFieldIndex { get; set; } Represents the base field for a custom calculation.
BaseIndex { get; set; } Represents the PivotField index in the base PivotFields.
BaseItemIndex { get; set; } Represents the item in the base field for a custom calculation. Valid only for data fields.
BaseItemPosition { get; set; } Represents the item in the base field for a custom calculation. 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 page item showing for the page field (valid only for page fields).
DataDisplayFormat { get; set; } Represents how to display the values contained in a data field.
DisplayName { get; set; } Represents the PivotField display name.
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 data position. The default value is true.
DragToHide { get; set; } Indicates whether the specified field can be dragged to the hide position. 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 position. The default value is true.
Function { get; set; } Represents the function used to summarize the PivotTable data field.
InsertBlankRow { get; set; } Indicates whether inserting blank line after each item.
IsAscendShow { get; set; } Indicates whether the specified PivotTable field is autoshown ascending.
IsAscendSort { get; set; } Indicates whether the specified PivotTable field is autosorted ascending.
IsAutoShow { get; set; } Indicates whether the specified PivotTable field is automatically shown,only valid for excel 2003.
IsAutoSort { get; set; } Indicates whether the specified PivotTable field is automatically sorted.
IsAutoSubtotals { get; set; } Indicates whether the specified field shows automatic subtotals. Default is true.
IsCalculatedField { get; } Indicates whether the specified PivotTable field is calculated field.
IsIncludeNewItemsInFilter { get; set; } indicates whether the field can include new items in manual filter The default value is false.
IsInsertPageBreaksBetweenItems { get; set; } indicates whether the field can insert page breaks between items insert page break after each item The default value is false.
IsMultipleItemSelectionAllowed { get; set; } indicates whether the field can have multiple items selected in the page field The default value is false.
IsRepeatItemLabels { get; set; } indicates whether the field can repeat items labels The default value is false.
ItemCount { get; } Gets the base item count of this pivot field.
Items { get; } Get all base items;
Name { get; } Represents the PivotField name.
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 PivotField index in the PivotFields.
Range { get; } Gets the group range of the pivot field
ShowAllItems { get; set; } Indicates whether all items in the PivotTable report are displayed, even if they don’t contain summary data. show items with no data The default value is false.
ShowCompact { get; set; } Indicates whether display labels from the next field in the same column on the Pivot Table view
ShowInOutlineForm { get; set; } Indicates whether layout this field in outline form on the Pivot Table view
ShowSubtotalAtTop { get; set; } when ShowInOutlineForm is true, then display subtotals at the top of the list of items instead of at the bottom

Methods

Name Description
AddCalculatedItem(string, string) Add a calculated item to the pivot field.
GetCalculatedFieldFormula() Get the formula string of the specified calculated field .
GetPivotFilterByType(PivotFilterType) Gets the pivot filter of the pivot field by type
GetPivotFilters() Gets the pivot filters of the pivot field
GetSubtotals(PivotFieldSubtotalType) Gets whether the specified field shows that subtotals.
HideDetail(bool) Sets whether the PivotItems in a pivot field is hidden detail.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) Indicates whether the specific PivotItem is hidden.
IsHiddenItemDetail(int) Indicates whether the specific PivotItem is hidden detail.
SetSubtotals(PivotFieldSubtotalType, bool) Sets whether the specified field shows that subtotals.

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";

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"

pivot.RefreshData()
pivot.CalculateData()

book.Save("out_vb.xlsx")

See Also