Class PivotFieldImpl
Represents a field in a PivotTable report.
Inherited Members
Namespace: Syncfusion.XlsIO.Implementation.PivotTables
Assembly: Syncfusion.XlsIO.NET.dll
Syntax
public class PivotFieldImpl : ICloneParent, IPivotField
Constructors
PivotFieldImpl(PivotCacheFieldImpl, PivotTableImpl)
Initializes new instance of the pivot field.
Declaration
public PivotFieldImpl(PivotCacheFieldImpl cacheField, PivotTableImpl table)
Parameters
| Type | Name | Description |
|---|---|---|
| PivotCacheFieldImpl | cacheField | Cache field that corresponds to this field. |
| PivotTableImpl | table |
Fields
m_iItemInvisibleCount
Specifies the number of items of field set as invisible.
Declaration
public int m_iItemInvisibleCount
Field Value
| Type |
|---|
| int |
m_table
Represents the parent pivot table.
Declaration
public PivotTableImpl m_table
Field Value
| Type |
|---|
| PivotTableImpl |
Properties
Axis
Gets or sets the field axis.
Declaration
public PivotAxisTypes Axis { get; set; }
Property Value
| Type |
|---|
| PivotAxisTypes |
Examples
Following code snippet illustrates how to add a axis to pivot field.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
// Create Pivot cache with the given data range
IPivotCache cache = workbook.PivotCaches.Add(worksheet["A1:H50"]);
//Create "PivotTable1" with the cache at the specified range
IPivotTable pivotTable = worksheet.PivotTables.Add("PivotTable1", worksheet["A1"], cache);
//Add Pivot table fields (Row and Column fields)
pivotTable.Fields[2].Axis = PivotAxisTypes.Row;
pivotTable.Fields[6].Axis = PivotAxisTypes.Row;
pivotTable.Fields[3].Axis = PivotAxisTypes.Column;
CacheField
Gets the parent cache field. Read-only.
Declaration
public PivotCacheFieldImpl CacheField { get; }
Property Value
| Type |
|---|
| PivotCacheFieldImpl |
CanDragOff
Gets or sets a Boolean value indicating if user can remove field from view. Read/Write Boolean. The default value is True.
Declaration
public bool CanDragOff { get; set; }
Property Value
| Type |
|---|
| bool |
CanDragToColumn
Gets or sets a Boolean value indicating if user can drag field to column area. Read/Write Boolean. The default value is True.
Declaration
public bool CanDragToColumn { get; set; }
Property Value
| Type |
|---|
| bool |
CanDragToData
Gets or sets a Boolean value indicating if the specified field can be dragged to the data position. Read/Write Boolean. The default value is True.
Declaration
public bool CanDragToData { get; set; }
Property Value
| Type |
|---|
| bool |
CanDragToPage
Gets or sets a Boolean value indicating if user can drag field to page area. Read/Write Boolean. The default value is True.
Declaration
public bool CanDragToPage { get; set; }
Property Value
| Type |
|---|
| bool |
CanDragToRow
Gets or sets a Boolean value indicating if user can drag field to row area. Read/Write Boolean. The default value is True.
Declaration
public bool CanDragToRow { get; set; }
Property Value
| Type |
|---|
| bool |
Caption
Gets or sets the unique name to be used as a caption for the field and field items.
Declaration
public string Caption { get; set; }
Property Value
| Type |
|---|
| string |
Compact
Gets or sets a boolean value indicating whether the application will display fields compactly in the sheet on which this PivotTable resides.
Declaration
public bool Compact { get; set; }
Property Value
| Type |
|---|
| bool |
EnableQuartersAndYears
Get or set a Boolean value indicating whether the Quarters and Years fields are applied to a date field or not.
Declaration
public bool EnableQuartersAndYears { get; set; }
Property Value
| Type |
|---|
| bool |
FieldGroup
Gets the pivot field group in current pivot field.
Declaration
public IPivotFieldGroup FieldGroup { get; }
Property Value
| Type |
|---|
| IPivotFieldGroup |
FilterValue
Gets or sets the Filter value of Page fields.
Declaration
public string FilterValue { get; set; }
Property Value
| Type |
|---|
| string |
Formula
Gets or sets the formula for the calculated field.
Declaration
public string Formula { get; set; }
Property Value
| Type |
|---|
| string |
IncludeNewItemsInFilter
Gets or sets a Boolean value indicating whether newly added pivot table items should be tracked when manual filtering is applied to pivot field. Read/Write Boolean.
Declaration
public bool IncludeNewItemsInFilter { get; set; }
Property Value
| Type |
|---|
| bool |
IsAutoFiltersByRank
Gets or sets a Boolean value indicating whether AutoShow filter applied to this field is set to show the top ranked values.
Declaration
public bool IsAutoFiltersByRank { get; set; }
Property Value
| Type |
|---|
| bool |
IsAutoShow
True if the number of top or bottom items for a row, page, or column field in the specified PivotTable report is enabled. False otherwise.
Declaration
public bool IsAutoShow { get; set; }
Property Value
| Type |
|---|
| bool |
IsDataField
True if the pivot field is a data field. False otherwise.
Declaration
public bool IsDataField { get; set; }
Property Value
| Type |
|---|
| bool |
IsDragToHide
True if the user can remove field from view. False otherwise.
Declaration
public bool IsDragToHide { get; set; }
Property Value
| Type |
|---|
| bool |
IsFormulaField
Gets or sets a Boolean value indicating if this field is formula field. Read-only.
Declaration
public bool IsFormulaField { get; }
Property Value
| Type |
|---|
| bool |
IsMeasureField
Gets or sets a Boolean value indicating whether field has a measure based filter.
Declaration
public bool IsMeasureField { get; set; }
Property Value
| Type |
|---|
| bool |
IsMultiSelected
Gets or sets a Boolean value indicating whether the field has multiple items selected in the page field.
Declaration
public bool IsMultiSelected { get; set; }
Property Value
| Type |
|---|
| bool |
IsShowAllItems
Gets or sets a Boolean value indicating whether to show all items for this field.
Declaration
public bool IsShowAllItems { get; set; }
Property Value
| Type |
|---|
| bool |
Items
Gets Pivot field items which are the individual data entries in a field category. Read-only.
Declaration
public IPivotFieldItems Items { get; }
Property Value
| Type |
|---|
| IPivotFieldItems |
ItemsPerPage
Gets or sets the number of items showed per page in the PivotTable.
Declaration
public int ItemsPerPage { get; set; }
Property Value
| Type |
|---|
| int |
Name
Gets or sets the name of the pivot field.
Declaration
public string Name { get; set; }
Property Value
| Type |
|---|
| string |
NumberFormat
Gets or sets the number format for the pivot field.
Declaration
public string NumberFormat { get; set; }
Property Value
| Type |
|---|
| string |
NumberFormatIndex
Gets or sets number format index for the pivot field.
Declaration
public int NumberFormatIndex { get; set; }
Property Value
| Type |
|---|
| int |
PivotFilters
Gets the IPivotFilters collection for the pivot field. Read-only.
Declaration
public IPivotFilters PivotFilters { get; }
Property Value
| Type |
|---|
| IPivotFilters |
Position
Gets or sets the position of the field (first,second,third and so on) among all the fields in its Axis (Row,Column,Page,Data).
Declaration
public int Position { get; set; }
Property Value
| Type |
|---|
| int |
PreservedAutoSort
Gets or sets the stream that preserves the sorting elements of the field.
Declaration
public Stream PreservedAutoSort { get; set; }
Property Value
| Type |
|---|
| Stream |
RepeatLabels
Gets or sets visibility of repeated item labels for the specified pivot field.
Declaration
public bool RepeatLabels { get; set; }
Property Value
| Type |
|---|
| bool |
ShowBlankRow
Gets or sets a Boolean value indicating if a blank row is inserted after the specified row field in a PivotTable report. Read/Write Boolean.
Declaration
public bool ShowBlankRow { get; set; }
Property Value
| Type |
|---|
| bool |
ShowDropDown
True if the flag for the specified PivotTable field or PivotTable item is set to "drilled" (expanded, or visible). False otherwise.
Declaration
public bool ShowDropDown { get; set; }
Property Value
| Type |
|---|
| bool |
ShowItemPropAsCaption
Gets or sets a Boolean value indicating whether to show the item property value in a PivotTable cell.
Declaration
public bool ShowItemPropAsCaption { get; set; }
Property Value
| Type |
|---|
| bool |
ShowNewItemsInFilter
Declaration
public bool ShowNewItemsInFilter { get; set; }
Property Value
| Type |
|---|
| bool |
ShowNewItemsOnRefresh
Gets or sets a Boolean value indicating whether newly added pivot table items during manual filtering to pivot field, should be shown after refresh operation.
Declaration
public bool ShowNewItemsOnRefresh { get; set; }
Property Value
| Type |
|---|
| bool |
ShowOutline
Gets or sets a Boolean value that indicates whether the items in this field should be shown in Outline form.
Declaration
public bool ShowOutline { get; set; }
Property Value
| Type |
|---|
| bool |
ShowPageBreak
True if a page break is inserted after each field. False otherwise.
Declaration
public bool ShowPageBreak { get; set; }
Property Value
| Type |
|---|
| bool |
ShowPropAsCaption
Gets or sets a Boolean value indicating whether to show the property as a caption.
Declaration
public bool ShowPropAsCaption { get; set; }
Property Value
| Type |
|---|
| bool |
ShowSubtotalAtTop
Gets or sets a boolean value indicating whether the subtotal is positioned at the top. The default value is True.
Declaration
public bool ShowSubtotalAtTop { get; set; }
Property Value
| Type |
|---|
| bool |
ShowToolTip
Gets or sets a Boolean value that indicates whether to show the item value in a tooltip on the appropriate PivotTable cells.
Declaration
public bool ShowToolTip { get; set; }
Property Value
| Type |
|---|
| bool |
SortType
Gets or sets the type of sort that is applied to this field.
Declaration
public PivotFieldSortType? SortType { get; set; }
Property Value
| Type |
|---|
| PivotFieldSortType? |
Subtotals
Gets or sets the subtotal function used for pivot field.
Declaration
public PivotSubtotalTypes Subtotals { get; set; }
Property Value
| Type |
|---|
| PivotSubtotalTypes |
Methods
AddItemOption(int)
Adds the specified item to the pivot field.
Declaration
public void AddItemOption(int index)
Parameters
| Type | Name | Description |
|---|---|---|
| int | index | Index at which the item is to be added. |
AddItemOption(int, PivotItemOptions)
Adds the specified item at the specified index to the pivot field.
Declaration
public void AddItemOption(int index, PivotItemOptions item)
Parameters
| Type | Name | Description |
|---|---|---|
| int | index | Index at which the item is to be added. |
| PivotItemOptions | item | Pivot field item to be added. |
AutoSort(PivotFieldSortType, int)
Sorts pivot row or column values in the given sort type.
Declaration
public void AutoSort(PivotFieldSortType sortType, int lineNumber)
Parameters
| Type | Name | Description |
|---|---|---|
| PivotFieldSortType | sortType | Type of sort that is applied to this field. |
| int | lineNumber | Row or Column number of the pivot table that the sort is applied to. |
Examples
The following code illustrates sorting of pivot row and column values based on sort type.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open(@"PivotData.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
IPivotTable pivotTable = worksheet.PivotTables[0];
//Pivot table row sorting.
IPivotField columnField = pivotTable.ColumnFields[0];
columnField.AutoSort(PivotFieldSortType.Ascending, 1);
//Pivot table column sorting.
IPivotField rowField = pivotTable.RowFields[0];
rowField.AutoSort(PivotFieldSortType.Descending, 1);
workbook.SaveAs("Output.xlsx");
workbook.Close();
excelEngine.Dispose();
Clone(object)
Creates a new object that is a copy of the current instance.
Declaration
public object Clone(object parent)
Parameters
| Type | Name | Description |
|---|---|---|
| object | parent | Parent object for a copy of this instance. |
Returns
| Type | Description |
|---|---|
| object | A new object that is a copy of this instance. |
Parse(IList, int)
Parses specified data at the offset to extract pivot field.
Declaration
public int Parse(IList data, int iPos)
Parameters
| Type | Name | Description |
|---|---|---|
| IList | data | Records collection with data. |
| int | iPos | Offset to the first pivot field record. |
Returns
| Type | Description |
|---|---|
| int | Offset to the record after field's records. |
Serialize(OffsetArrayList)
Saves pivot table into OffsetArrayList.
Declaration
[CLSCompliant(false)]
public void Serialize(OffsetArrayList records)
Parameters
| Type | Name | Description |
|---|---|---|
| OffsetArrayList | records | OffsetArrayList that will get all pivot table records. |
Sort(string[])
Sorts pivot field items in the given order.
Declaration
public void Sort(string[] orderByArray)
Parameters
| Type | Name | Description |
|---|---|---|
| string[] | orderByArray | Array of strings to be sorted. |
Remarks
To know more about Pivot Tables refer this link.
Examples
The following code illustrates sorting of pivot field items based on custom set of strings.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create a worksheet.
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(2);
IWorksheet sheet = workbook.Worksheets[0];
IWorksheet pivotSheet = workbook.Worksheets[1];
sheet.Range["A1"].Text = "Student ID";
sheet.Range["B1"].Text = "Student Name";
sheet.Range["C1"].Text = "Mark(Out of 100)";
sheet.Range["D1"].Text = "Student City";
sheet.Range["A1:D1"].AutofitColumns();
sheet.Range["A2"].Number = 1;
sheet.Range["A3"].Number = 2;
sheet.Range["A4"].Number = 3;
sheet.Range["A5"].Number = 4;
sheet.Range["A6"].Number = 5;
sheet.Range["B2"].Text = "Andrew";
sheet.Range["B3"].Text = "Marson";
sheet.Range["B4"].Text = "Jack";
sheet.Range["B5"].Text = "Sam";
sheet.Range["B6"].Text = "Breto";
sheet.Range["C2"].Number = 77;
sheet.Range["C3"].Number = 45;
sheet.Range["C4"].Number = 92;
sheet.Range["C5"].Number = 39;
sheet.Range["C6"].Number = 55;
sheet.Range["D2"].Text = "Chennai";
sheet.Range["D3"].Text = "Bangalore";
sheet.Range["D4"].Text = "Chennai";
sheet.Range["D5"].Text = "Mysore";
sheet.Range["D6"].Text = "Bangalore";
IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:D6"]);
IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
pivotTable.Fields[3].Axis = PivotAxisTypes.Column;
IPivotField pivotField = pivotTable.Fields[1];
//Sorts pivot field items in the given order
pivotField.Sort(new string[5] { "Breto", "Marson", "Jack", "Andrew", "Sam" });
IPivotField datafield = pivotTable.Fields[2];
pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
workbook.SaveAs("PivotTables.xlsx");
workbook.Close();
}