Class PivotTableOptions
Represents the setting for the pivot table in the worksheet.
Inheritance
Implements
Namespace: Syncfusion.XlsIO.Implementation.PivotTables
Assembly: Syncfusion.XlsIO.NET.dll
Syntax
public class PivotTableOptions : Object, IPivotTableOptions
Constructors
PivotTableOptions(PivotTableImpl, ViewExtendedInfoRecord, PivotViewDefinitionRecord)
Initializes pivot table options
Declaration
public PivotTableOptions(PivotTableImpl pivotTable, ViewExtendedInfoRecord extInfo, PivotViewDefinitionRecord definitionInfo)
Parameters
Type | Name | Description |
---|---|---|
PivotTableImpl | pivotTable | |
ViewExtendedInfoRecord | extInfo | |
PivotViewDefinitionRecord | definitionInfo |
Properties
ColumnHeaderCaption
Gets or sets the string to be displayed in column header of pivot table in compact layout mode.
Declaration
public string ColumnHeaderCaption { get; set; }
Property Value
Type |
---|
System.String |
Remarks
If ColumnHeaderCaption is not assigned, the string "Column Labels" will be set as default
Examples
Following code illustrates how to access the ShowFieldList property of the pivot table option.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
IWorksheet pivotSheet = workbook.Worksheets[1];
// Create Pivot cache with the given data range
IPivotCache cache = workbook.PivotCaches.Add(worksheet["A1:H50"]);
IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
IPivotTableOptions options = pivotTable.Options;
// Enable header captions
options.ColumnHeaderCaption = "Units";
CreatedVersion
Gets or sets the application version that created the cache.
Declaration
public byte CreatedVersion { get; set; }
Property Value
Type |
---|
System.Byte |
DataCaption
Gets or sets the name of the value area field header in the PivotTable.
Declaration
public string DataCaption { get; set; }
Property Value
Type |
---|
System.String |
DataPosition
Gets or sets the position for the field representing multiple data field in the PivotTable, whether that field is located in the row area or column area.
Declaration
public ushort DataPosition { get; set; }
Property Value
Type |
---|
System.UInt16 |
DisplayErrorString
Gets or sets a Boolean value indicating whether custom error string in cells that contain errors must be shown or not.
Declaration
public bool DisplayErrorString { get; set; }
Property Value
Type |
---|
System.Boolean |
Remarks
Use the ErrorString property to set the custom error string. This property is particularly useful for suppressing divide-by-zero errors when calculated fields are pivoted.
DisplayFieldCaptions
Gets or sets a Boolean value indicating whether filter buttons and PivotField captions for rows and columns are displayed in the grid or not.The default value is True.
Declaration
public bool DisplayFieldCaptions { get; set; }
Property Value
Type |
---|
System.Boolean |
DisplayNullString
Gets or sets a Boolean value indicating whether custom string in cells that contain null value must be shown or not.
Declaration
public bool DisplayNullString { get; set; }
Property Value
Type |
---|
System.Boolean |
Remarks
Use the NullString property to set the custom null string. The default value is True.
EnableFieldProperties
Gets or sets a Boolean value indicating whether field properties are enabled or not.
Declaration
public bool EnableFieldProperties { get; set; }
Property Value
Type |
---|
System.Boolean |
ErrorString
Gets or sets the string displayed in cells that contain errors when the DisplayErrorString property is True.
Declaration
public string ErrorString { get; set; }
Property Value
Type |
---|
System.String |
GrandTotalCaption
Gets or sets the string to be displayed for grand totals.
Declaration
public string GrandTotalCaption { get; set; }
Property Value
Type |
---|
System.String |
Indent
Gets or sets the indentation increment for compact axis so as to set the Report Layout to Compact Form.
Declaration
public uint Indent { get; set; }
Property Value
Type |
---|
System.UInt32 |
IsAlignAutoFormat
True if legacy table autoformat alignment properties are applied. False otherwise. Read/Wrire Boolean.
Declaration
public bool IsAlignAutoFormat { get; set; }
Property Value
Type |
---|
System.Boolean |
IsAutoFormat
True if legacy table autoformat is applied to the pivot table. False otherwise. Read/Write Boolean.
Declaration
public bool IsAutoFormat { get; set; }
Property Value
Type |
---|
System.Boolean |
IsBorderAutoFormat
True to apply legacy table autoformat border properties. False otherwise. Read/Write Boolean.
Declaration
public bool IsBorderAutoFormat { get; set; }
Property Value
Type |
---|
System.Boolean |
IsDataEditable
Gets or sets a Boolean value indicating whether the data area of the PivotTable is editable.
Declaration
public bool IsDataEditable { get; set; }
Property Value
Type |
---|
System.Boolean |
Remarks
True to disable the alert for when the user overwrites values in the data area of the PivotTable. False otherwise. This allows the user to change data values that previously could not be changed.
IsDefaultAutoSort
Gets or sets a Boolean value indicating whether fields in the PivotTable are sorted in default order in the field list.
Declaration
public bool IsDefaultAutoSort { get; set; }
Property Value
Type |
---|
System.Boolean |
IsFontAutoFormat
True to apply legacy table autoformat font properties. False otherwise. Read/Write Boolean.
Declaration
public bool IsFontAutoFormat { get; set; }
Property Value
Type |
---|
System.Boolean |
IsMultiFieldFilter
Gets or sets a Boolean value that indicates whether the fields of a PivotTable can have multiple filters set on them.
Declaration
public bool IsMultiFieldFilter { get; set; }
Property Value
Type |
---|
System.Boolean |
IsNumberAutoFormat
True to apply legacy table autoformat number format properties. False otherwise. Read/Write Boolean.
Declaration
public bool IsNumberAutoFormat { get; set; }
Property Value
Type |
---|
System.Boolean |
IsPatternAutoFormat
True to apply legacy table autoformat pattern properties. False otherwise. Read/Write Boolean.
Declaration
public bool IsPatternAutoFormat { get; set; }
Property Value
Type |
---|
System.Boolean |
IsSaveData
Gets or sets a Boolean value indicating whether PivotTable report is saved with the workbook or not.
Declaration
public bool IsSaveData { get; set; }
Property Value
Type |
---|
System.Boolean |
Remarks
For OLAP data sources, this property is always set to False.
IsWHAutoFormat
Gets or sets a Boolean value indicating if width/height autoformat is applied. False otherwise. Read/Write Boolean.
Declaration
public bool IsWHAutoFormat { get; set; }
Property Value
Type |
---|
System.Boolean |
MergeLabels
Gets or sets a Boolean value indicating whether the label must be merged or not.
Declaration
public bool MergeLabels { get; set; }
Property Value
Type |
---|
System.Boolean |
Remarks
True if the specified PivotTable report’s outer-row item, column item, subtotal, and grand total labels use merged cells. False otherwise .
MiniRefreshVersion
Gets or sets the minimum version of the application that updated the cache.
Declaration
public byte MiniRefreshVersion { get; set; }
Property Value
Type |
---|
System.Byte |
NullString
Gets or sets the string displayed in cells that contain null values when the DisplayNullString property is True.
Declaration
public string NullString { get; set; }
Property Value
Type |
---|
System.String |
Outline
Gets or sets a Boolean value that indicates whether new fields should have their outline flag set to true.
Declaration
public bool Outline { get; set; }
Property Value
Type |
---|
System.Boolean |
OutlineData
Gets or sets a Boolean value that indicates whether data fields in the PivotTable should be displayed in outline form.
Declaration
public bool OutlineData { get; set; }
Property Value
Type |
---|
System.Boolean |
PageFieldsOrder
Gets or sets the order in which page fields are added to the PivotTable report’s layout. The default value is DownThenOver
Declaration
public PivotPageAreaFieldsOrder PageFieldsOrder { get; set; }
Property Value
Type |
---|
PivotPageAreaFieldsOrder |
PageFieldWrapCount
Gets or sets the number of page fields in each column or row in the PivotTable report.
Declaration
public int PageFieldWrapCount { get; set; }
Property Value
Type |
---|
System.Int32 |
PreserveFormatting
Gets or sets a Boolean value indicating whether to preserve the format. The default value is True.
Declaration
public bool PreserveFormatting { get; set; }
Property Value
Type |
---|
System.Boolean |
Remarks
True if formatting is preserved when the report is refreshed or recalculated by operations such as pivoting, sorting, or changing page field items. False otherwise.
PrintTitles
True if the print titles for the worksheet are set based on the PivotTable report. False if the print titles for the worksheet are used. Read/Write Boolean.
Declaration
public bool PrintTitles { get; set; }
Property Value
Type |
---|
System.Boolean |
Remarks
The row print titles are set to the rows that contain the PivotTable report’s column field items. The column print titles are set to the columns that contain the row items.
RowHeaderCaption
Gets or sets the string to be displayed in Row header of pivot table in compact layout mode.
Declaration
public string RowHeaderCaption { get; set; }
Property Value
Type |
---|
System.String |
Remarks
If RowHeaderCaption is not assigned, the string "Row Labels" will be set as default
Examples
Following code illustrates how to access the ShowFieldList property of the pivot table option.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
IWorksheet pivotSheet = workbook.Worksheets[1];
// Create Pivot cache with the given data range
IPivotCache cache = workbook.PivotCaches.Add(worksheet["A1:H50"]);
IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
IPivotTableOptions options = pivotTable.Options;
// Enable header captions
options.RowHeaderCaption = "Date";
RowLayout
Gets or sets the pivot table row layout settings.
Declaration
public PivotTableRowLayout RowLayout { get; set; }
Property Value
Type |
---|
PivotTableRowLayout |
ShowAsteriskTotals
Gets or sets a Boolean value indicating whether asterisk must be displayed next to totals.
Declaration
public bool ShowAsteriskTotals { get; set; }
Property Value
Type |
---|
System.Boolean |
Remarks
True if an asterisk (*) is displayed next to each subtotal and grand total value in the specified PivotTable report. False otherwise.
ShowCalcMembers
Gets or sets a Boolean value indicating whether to show calculated members in the PivotTable view.
Declaration
public bool ShowCalcMembers { get; set; }
Property Value
Type |
---|
System.Boolean |
ShowCustomSortList
Gets or sets a Boolean value indicating whether the "custom lists" option is offered when sorting this PivotTable. The default value is True.
Declaration
public bool ShowCustomSortList { get; set; }
Property Value
Type |
---|
System.Boolean |
ShowDrillIndicators
Gets or sets a Boolean value indicating whether to toggle the display of drill indicators in the PivotTable.
Declaration
public bool ShowDrillIndicators { get; set; }
Property Value
Type |
---|
System.Boolean |
Remarks
True if drill indicators are displayed in the PivotTable. False Otherwise.
ShowFieldList
Gets or sets a Boolean value indicating whether the field list must be showed or not.
Declaration
public bool ShowFieldList { get; set; }
Property Value
Type |
---|
System.Boolean |
Remarks
False to disable the display of field list for the PivotTable. True otherwise.
Examples
Following code illustrates how to access the ShowFieldList property of the pivot table option.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
IWorksheet pivotSheet = workbook.Worksheets[1];
// Create Pivot cache with the given data range
IPivotCache cache = workbook.PivotCaches.Add(worksheet["A1:H50"]);
IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
IPivotTableOptions options = pivotTable.Options;
options.ShowFieldList = true;
ShowGridDropZone
Gets or sets a Boolean value that indicates whether the in-grid drop zones should be displayed at runtime, and whether classic layout is applied.
Declaration
public bool ShowGridDropZone { get; set; }
Property Value
Type |
---|
System.Boolean |
ShowTooltips
Gets or sets a Boolean value indicating whether tool-tips should been shown or not.The default value is True.
Declaration
public bool ShowTooltips { get; set; }
Property Value
Type |
---|
System.Boolean |
ShowValuesRow
Gets or sets a value indicating whether the values row is displayed in the pivot table. The default value is False.
Declaration
public bool ShowValuesRow { get; set; }
Property Value
Type |
---|
System.Boolean |
UpdatedVersion
Gets or sets the application version that updated the cache.
Declaration
public byte UpdatedVersion { get; set; }
Property Value
Type |
---|
System.Byte |
Methods
RepeatAllLabels(Boolean)
Set repeat labels to all the pivot fields.
Declaration
public void RepeatAllLabels(bool repeat)
Parameters
Type | Name | Description |
---|---|---|
System.Boolean | repeat | Value to set to the repeat all item labels in all pivot fields. |