How to: Apply a Custom Style to a Pivot Table

• Apr 22, 2022
• 4 minutes to read

This example demonstrates how to create a custom style and apply it to a pivot table. By default, the workbook’s collection of pivot table styles (TableStyleCollection) contains built-in styles similar to Microsoft® Excel® and the None style, which specifies that no formatting should be applied to the pivot table.

A pivot table style is defined by the TableStyle object, which consists of a collection of table style elements (TableStyle.TableStyleElements). Each table style element (TableStyleElement) specifies formatting for a particular element of a pivot table. The TableStyleElementType enumerator lists the supported table style elements. Use properties of the TableStyleElement object to customize borders (TableStyleElement.Borders), fill (TableStyleElement.Fill) and font (TableStyleElement.Font) for the corresponding element of a pivot table.

To create a custom pivot table style, do the following.

1. Add a new pivot table style to the IWorkbook.TableStyles collection by calling the TableStyleCollection.Add method. This method returns the TableStyle object that represents the newly created pivot table style.

   Note that you can also create a custom pivot table style based on the existing pivot table style (e.g., a built-in pivot table style). To do this, use the TableStyle.Duplicate method. This method creates a copy of the specified style and returns the TableStyle object representing the created style.

2. Set the TableStyle.IsPivotStyle property to true and the TableStyle.IsTableStyle property to false to indicate that the created style should be applied only to pivot tables. For the custom styles that are based on the built-in pivot table styles, this step can be skipped since such styles copy appropriate values of these properties from the built-in styles.
3. Call the TableStyle.BeginUpdate method.

4. Access the table style element to be modified from the TableStyle.TableStyleElements collection by the corresponding TableStyleElementType enumeration member. Use the TableStyleElement properties to specify the required formatting for the element. If you need to remove existing formatting from the element, use its TableStyleElement.Clear method.

   Repeat this step for all table style elements you wish to modify.

5. Call the TableStyle.EndUpdate method.
6. Apply the created style to the pivot table by using the PivotTable.Style property.

The example below duplicates the built-in pivot table style and modifies the new style by changing formatting characteristics for the entire table, column headers and the grand total row.

C#

// Access the pivot table by its name in the collection
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];

// Get the pivot table style to be duplicated.
TableStyle sourceStyle = workbook.TableStyles[BuiltInPivotStyleId.PivotStyleMedium3];

// Duplicate the pivot table style.
TableStyle customStyle = sourceStyle.Duplicate();

// Modify the required formatting characteristics of the created pivot table style.
customStyle.BeginUpdate();
try
{
    // Specify formatting characteristics for the column headers. 
    TableStyleElement header = customStyle.TableStyleElements[TableStyleElementType.HeaderRow];
    header.Fill.BackgroundColor = Color.FromArgb(0x1F, 0x3E, 0x7E);

    // Specify formatting characteristics for the whole table. 
    TableStyleElement wholeTable = customStyle.TableStyleElements[TableStyleElementType.WholeTable];
    wholeTable.Fill.BackgroundColor = Color.FromArgb(0xF1, 0xF4, 0xD0);
    wholeTable.Borders.RemoveBorders();

    // Specify formatting characteristics for the grand total row. 
    TableStyleElement totalRowStyle = customStyle.TableStyleElements[TableStyleElementType.TotalRow];
    totalRowStyle.Fill.BackgroundColor = Color.FromArgb(166, 166, 166);
    totalRowStyle.Borders.RemoveBorders();
}
finally
{
    customStyle.EndUpdate();
}

// Apply the created custom style to the pivot table.
pivotTable.Style = customStyle;

VB.NET

' Access the pivot table by its name in the collection
Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")

' Get the pivot table style to be duplicated.
Dim sourceStyle As TableStyle = workbook.TableStyles(BuiltInPivotStyleId.PivotStyleMedium3)

' Duplicate the pivot table style.
Dim customStyle As TableStyle = sourceStyle.Duplicate()

' Modify the required formatting characteristics of the created pivot table style.
customStyle.BeginUpdate()
Try
    ' Specify formatting characteristics for the column headers. 
    Dim header As TableStyleElement = customStyle.TableStyleElements(TableStyleElementType.HeaderRow)
    header.Fill.BackgroundColor = Color.FromArgb(&H1F, &H3E, &H7E)

    ' Specify formatting characteristics for the whole table. 
    Dim wholeTable As TableStyleElement = customStyle.TableStyleElements(TableStyleElementType.WholeTable)
    wholeTable.Fill.BackgroundColor = Color.FromArgb(&HF1, &HF4, &HD0)
    wholeTable.Borders.RemoveBorders()

    ' Specify formatting characteristics for the grand total row. 
    Dim totalRowStyle As TableStyleElement = customStyle.TableStyleElements(TableStyleElementType.TotalRow)
    totalRowStyle.Fill.BackgroundColor = Color.FromArgb(166, 166, 166)
    totalRowStyle.Borders.RemoveBorders()
Finally
    customStyle.EndUpdate()
End Try

' Apply the created custom style to the pivot table.
pivotTable.Style = customStyle

The image below illustrates the pivot table appearance when the custom style is applied.