How to: Control Style Options

• Jun 11, 2023
• 3 minutes to read

A pivot table style consists of a collection of table style elements (TableStyleElement), each of which specifies formatting for a particular element of a pivot table. The TableStyleElementType enumerator lists supported table style element types. The PivotTable object provides specific properties that allow you to configure PivotTable style options by specifying table elements to be formatted as defined by the corresponding elements of the applied pivot table style.

Format Row Headers

Use the PivotTable.ShowRowHeaders property to enable or disable style formatting for row headers in a pivot table. The appearance of row headers is specified by the TableStyleElementType.FirstRowSubheading, TableStyleElementType.SecondRowSubheading and TableStyleElementType.ThirdRowSubheading elements of the applied pivot table style.

View Example

PivotTableFormattingActions.cs

Worksheet worksheet = workbook.Worksheets["Report1"];
workbook.Worksheets.ActiveWorksheet = worksheet;

// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Add the "Region" field to the column axis area. 
pivotTable.ColumnFields.Add(pivotTable.Fields["Region"]);
// Remove formatting from row headers.
pivotTable.ShowRowHeaders = false;

PivotTableFormattingActions.vb

Dim worksheet As Worksheet = workbook.Worksheets("Report1")
workbook.Worksheets.ActiveWorksheet = worksheet

' Access the pivot table by its name in the collection.
Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")
' Add the "Region" field to the column axis area. 
pivotTable.ColumnFields.Add(pivotTable.Fields("Region"))
' Remove formatting from row headers.
pivotTable.ShowRowHeaders = False

Format Column Headers

Use the PivotTable.ShowColumnHeaders property to enable or disable style formatting for column headers in a pivot table. The appearance of column headers is specified by the TableStyleElementType.HeaderRow, TableStyleElementType.FirstColumnSubheading, TableStyleElementType.SecondColumnSubheading and TableStyleElementType.ThirdColumnSubheading elements of the applied pivot table style.

View Example

PivotTableFormattingActions.cs

Worksheet worksheet = workbook.Worksheets["Report1"];
workbook.Worksheets.ActiveWorksheet = worksheet;

// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Remove formatting from column headers.
pivotTable.ShowColumnHeaders = false;

PivotTableFormattingActions.vb

Dim worksheet As Worksheet = workbook.Worksheets("Report1")
workbook.Worksheets.ActiveWorksheet = worksheet

' Access the pivot table by its name in the collection.
Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")
' Remove formatting from column headers.
pivotTable.ShowColumnHeaders = False

Apply Banded Row Formatting

Set the PivotTable.BandedRows property to true to shade alternate rows in a pivot table as specified by the TableStyleElementType.FirstRowStripe and TableStyleElementType.SecondRowStripe elements of the applied pivot table style. To specify a number of PivotTable rows to be included into odd and even row stripes, use the TableStyleElement.StripeSize property of these table style elements.

View Example

PivotTableFormattingActions.cs

Worksheet worksheet = workbook.Worksheets["Report4"];
workbook.Worksheets.ActiveWorksheet = worksheet;

// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Apply the banded row formatting to the pivot table. 
pivotTable.BandedRows = true;

PivotTableFormattingActions.vb

Dim worksheet As Worksheet = workbook.Worksheets("Report4")
workbook.Worksheets.ActiveWorksheet = worksheet

' Access the pivot table by its name in the collection.
Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")
' Apply the banded row formatting to the pivot table. 
pivotTable.BandedRows = True

Apply Banded Column Formatting

Set the PivotTable.BandedColumns property to true to shade alternate columns in a pivot table as specified by the TableStyleElementType.FirstColumnStripe and TableStyleElementType.SecondColumnStripe elements of the applied pivot table style. To specify the number of PivotTable columns to be displayed as odd and even column stripes, use the TableStyleElement.StripeSize property of these table style elements.

View Example

PivotTableFormattingActions.cs

Worksheet worksheet = workbook.Worksheets["Report4"];
workbook.Worksheets.ActiveWorksheet = worksheet;

// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Apply the banded column formatting to the pivot table. 
pivotTable.BandedColumns = true;

PivotTableFormattingActions.vb

Dim worksheet As Worksheet = workbook.Worksheets("Report4")
workbook.Worksheets.ActiveWorksheet = worksheet

' Access the pivot table by its name in the collection.
Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")
' Apply the banded column formatting to the pivot table. 
pivotTable.BandedColumns = True