Class PivotTableOptions

Represents the setting for the pivot table in the worksheet.

Inheritance
System.Object
PivotTableOptions
Implements
Namespace: Syncfusion.XlsIO.Implementation.PivotTables
Assembly: Syncfusion.XlsIO.Portable.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.

Implements