Class PivotField
Contents
[
Hide
]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 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 PivotField index in the base PivotFields. |
| 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 page item showing for the page field (valid only 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 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. |
| GroupSettings { get; } | Gets the group settings of the pivot 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 including new items to the field in manual filter. The default value is false. |
| IsInsertPageBreaksBetweenItems { get; set; } | Indicates whether inserting page breaks 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 repeating labels of the field in the region. The default value is false. |
| IsValueFields { get; } | Indicates whether this field represents values fields. |
| 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. |
| Range { get; } | (Obsolete.) Gets the group range of the pivot field |
| RegionType { get; } | Specifies the region of the PivotTable that this field is displayed. |
| ShowAllItems { get; set; } | 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. |
| 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 |
| 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
| Name | Description |
|---|---|
| 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 setting 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 of this pivot field. |
| GetFormula() | Gets formula of the 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 showing specified subtotal. |
| 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 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) | Gets whether the specific PivotItem is hidden. |
| IsHiddenItemDetail(int) | Gets whether hidding the detail of the specific PivotItem.. |
| SetSubtotals(PivotFieldSubtotalType, bool) | Sets 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. |
| SortBy(SortOrder, int) | Sorts this pivot field. |
| SortBy(SortOrder, int, PivotLineType, string) | Sorts this pivot field. |
| Ungroup() | Ungroup the pivot field. |
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
- namespace Aspose.Cells.Pivot
- assembly Aspose.Cells
