Class PivotTableOptions

Represents the setting for the pivot table in the worksheet.

Inheritance

System.Object

PivotTableOptions

Implements

IPivotTableOptions

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	Description
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	Description
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	Description
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	Description
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	Description
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	Description
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	Description
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	Description
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	Description
System.String

GrandTotalCaption

Gets or sets the string to be displayed for grand totals.

Declaration

public string GrandTotalCaption { get; set; }

Property Value

Type	Description
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	Description
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	Description
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	Description
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	Description
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	Description
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	Description
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	Description
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	Description
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	Description
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	Description
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	Description
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	Description
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	Description
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	Description
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	Description
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	Description
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	Description
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	Description
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	Description
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	Description
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	Description
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	Description
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	Description
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	Description
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	Description
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	Description
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	Description
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	Description
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	Description
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	Description
System.Boolean

UpdatedVersion

Gets or sets the application version that updated the cache.

Declaration

public byte UpdatedVersion { get; set; }

Property Value

Type	Description
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

IPivotTableOptions