Setting Pivot Table Styles

Spread for WPF allows users to apply built-in or custom styles to pivot tables. By default, a built-in style is automatically applied when a pivot table is added to a worksheet. You can also modify the pivot table style by duplicating an existing style or creating a custom style from scratch.

Apply Built-In Pivot Table Style

You can change the default appearance of the pivot table by applying any of the built-in styles. In order to apply built-in style to the pivot table, users can use the TableStyle property of the IPivotTable interface.

The image shared below depicts a pivot table with built-in style.

Refer to the following example code in order to apply built-in style to the pivot table.

IWorkbook WorkBook = GcSpreadSheet1.Workbook;
IWorksheet Sheet1 = WorkBook.ActiveSheet;

// Set data.
Sheet1.SetValue(0, 0, new object[,]
{
    { "OrderDate", "Region", "City", "Category", "Product", "Quantity" },
    { "2025-07-01", "East", "Jersey", "Breads", "Bakery", 1120 },
    { "2025-07-01", "East", "Jersey", "Breads", "Cookie", 563 },
    { "2025-07-02", "East", "Washington, DC", "Breads", "Bakery", 1281 },
    { "2025-07-02", "East", "Washington, DC", "Breads", "Cookie", 546 },
    { "2025-07-01", "East", "Jersey", "Beverages", "Coffee", 326 },
    { "2025-07-02", "East", "Washington, DC", "Beverages", "Coffee", 205 },
    { "2025-07-02", "East", "Washington, DC", "Beverages", "Juice", 186 },
    { "2025-07-01", "West", "San Francisco", "Breads", "Bakery", 1262 },
    { "2025-07-01", "West", "San Francisco", "Breads", "Cookie", 349 },
    { "2025-07-01", "West", "Seattle", "Breads", "Bakery", 524 },
    { "2025-07-01", "West", "Seattle", "Breads", "Cookie", 196 },
    { "2025-07-01", "West", "San Francisco", "Beverages", "Coffee", 363 },
    { "2025-07-01", "West", "Seattle", "Beverages", "Coffee", 100 },
    { "2025-07-02", "East", "Jersey", "Beverages", "Juice",  120 },
    { "2025-07-02", "West", "San Francisco", "Breads", "Bakery", 350 },
    { "2025-07-02", "West", "Seattle", "Beverages", "Tea", 180 },
    { "2025-07-02", "East", "Jersey", "Breads", "Cookie", 75 },
    { "2025-07-03", "West", "San Francisco", "Beverages", "Juice", 210 },
    { "2025-07-03", "East", "Boston", "Breads", "Bakery", 420 },
});

IPivotCache pvCache = WorkBook.PivotCaches.Create("Sheet1!A1:F20");
IPivotTable pvTable = pvCache.CreatePivotTable(Sheet1.Cells["I1"]);
IPivotFields pvFields = pvTable.PivotFields;

pvTable.AddDataField(pvFields["Quantity"], "Sum of quantity", ConsolidationFunction.Sum);
pvFields["Region"].Orientation = PivotFieldOrientation.Row;
pvFields["City"].Orientation = PivotFieldOrientation.Row;
pvFields["Category"].Orientation = PivotFieldOrientation.Column;
pvFields["Product"].Orientation = PivotFieldOrientation.Column;
pvFields["OrderDate"].Orientation = PivotFieldOrientation.Page;

// Set a built-in style for the PivotTable.
ITableStyle tbStyle1 = WorkBook.TableStyles[BuiltInPivotStyles.PivotStyleDark10];
pvTable.TableStyle = tbStyle1;

Dim WorkBook As IWorkbook = spreadSheet1.Workbook
Dim Sheet1 As IWorksheet = WorkBook.ActiveSheet

' Set data.
Sheet1.SetValue(0, 0, New Object(,) {
    {"OrderDate", "Region", "City", "Category", "Product", "Quantity"},
    {"2025-07-01", "East", "Jersey", "Breads", "Bakery", 1120},
    {"2025-07-01", "East", "Jersey", "Breads", "Cookie", 563},
    {"2025-07-02", "East", "Washington, DC", "Breads", "Bakery", 1281},
    {"2025-07-02", "East", "Washington, DC", "Breads", "Cookie", 546},
    {"2025-07-01", "East", "Jersey", "Beverages", "Coffee", 326},
    {"2025-07-02", "East", "Washington, DC", "Beverages", "Coffee", 205},
    {"2025-07-02", "East", "Washington, DC", "Beverages", "Juice", 186},
    {"2025-07-01", "West", "San Francisco", "Breads", "Bakery", 1262},
    {"2025-07-01", "West", "San Francisco", "Breads", "Cookie", 349},
    {"2025-07-01", "West", "Seattle", "Breads", "Bakery", 524},
    {"2025-07-01", "West", "Seattle", "Breads", "Cookie", 196},
    {"2025-07-01", "West", "San Francisco", "Beverages", "Coffee", 363},
    {"2025-07-01", "West", "Seattle", "Beverages", "Coffee", 100},
    {"2025-07-02", "East", "Jersey", "Beverages", "Juice", 120},
    {"2025-07-02", "West", "San Francisco", "Breads", "Bakery", 350},
    {"2025-07-02", "West", "Seattle", "Beverages", "Tea", 180},
    {"2025-07-02", "East", "Jersey", "Breads", "Cookie", 75},
    {"2025-07-03", "West", "San Francisco", "Beverages", "Juice", 210},
    {"2025-07-03", "East", "Boston", "Breads", "Bakery", 420}
})

Dim pvCache As IPivotCache = WorkBook.PivotCaches.Create("Sheet1!A1:F20")
Dim pvTable As IPivotTable = pvCache.CreatePivotTable(Sheet1.Cells("I1"))
Dim pvFields As IPivotFields = pvTable.PivotFields

pvTable.AddDataField(pvFields("Quantity"), "Sum of quantity", ConsolidationFunction.Sum)
pvFields("Region").Orientation = PivotFieldOrientation.Row
pvFields("City").Orientation = PivotFieldOrientation.Row
pvFields("Category").Orientation = PivotFieldOrientation.Column
pvFields("Product").Orientation = PivotFieldOrientation.Column
pvFields("OrderDate").Orientation = PivotFieldOrientation.Page

' Set a built-in style for the PivotTable.
Dim tbStyle1 As ITableStyle = WorkBook.TableStyles(BuiltInPivotStyles.PivotStyleDark10)
pvTable.TableStyle = tbStyle1

Apply Custom Style

If you don't want to apply any of the built-in styles, you can also create and apply your own custom style to the pivot table. This can be done using the TableStyle property of the IPivotTable interface.

The image shared below depicts a pivot table with custom style.

Refer to the following example code in order to apply custom style to the pivot table.

IPivotCache pvCache = WorkBook.PivotCaches.Create("Sheet1!A1:F20");
IPivotTable pvTable = pvCache.CreatePivotTable(Sheet1.Cells["I1"]);
IPivotFields pvFields = pvTable.PivotFields;

pvTable.AddDataField(pvFields["Quantity"], "Sum of quantity", ConsolidationFunction.Sum);
pvFields["Region"].Orientation = PivotFieldOrientation.Row;
pvFields["City"].Orientation = PivotFieldOrientation.Row;
pvFields["Category"].Orientation = PivotFieldOrientation.Column;
pvFields["Product"].Orientation = PivotFieldOrientation.Column;
pvFields["OrderDate"].Orientation = PivotFieldOrientation.Page;

// Create a custom table style.
ITableStyle tbStyle = WorkBook.TableStyles.Add("Style1");

// Set background colors.
tbStyle[TableStyleElementType.HeaderRow].Interior.Color = Color.FromArgb(255, 78, 167, 46);
tbStyle[TableStyleElementType.TotalRow].Interior.Color = Color.FromArgb(255, 228, 158, 221);

tbStyle[TableStyleElementType.WholeTable].Interior.Color = Color.FromArgb(255, 242, 206, 239);

// Set borders for the whole table.
Border lineBorder = Border.NoBorder;
BorderLine thinBlueSideBorder = new BorderLine(
    BorderLineStyle.Thin,
    Color.FromKnownColor(GrapeCity.Core.KnownColor.DarkBlue)
);
lineBorder.All = thinBlueSideBorder;
tbStyle[TableStyleElementType.WholeTable].Borders.ApplyBorder(lineBorder);

// Set borders for the header row.
Border lineBorder1 = Border.NoBorder;
BorderLine thinBlueSideBorder1 = new BorderLine(
    BorderLineStyle.Thin,
    Color.FromKnownColor(GrapeCity.Core.KnownColor.Moccasin)
);
lineBorder1.All = thinBlueSideBorder1;
tbStyle[TableStyleElementType.HeaderRow].Borders.ApplyBorder(lineBorder1);

// Apply the custom style to the pivot table.
pvTable.TableStyle = tbStyle;

Dim pvCache As IPivotCache = WorkBook.PivotCaches.Create("Sheet1!A1:F20")
Dim pvTable As IPivotTable = pvCache.CreatePivotTable(Sheet1.Cells("I1"))
Dim pvFields As IPivotFields = pvTable.PivotFields

pvTable.AddDataField(pvFields("Quantity"), "Sum of quantity", ConsolidationFunction.Sum)
pvFields("Region").Orientation = PivotFieldOrientation.Row
pvFields("City").Orientation = PivotFieldOrientation.Row
pvFields("Category").Orientation = PivotFieldOrientation.Column
pvFields("Product").Orientation = PivotFieldOrientation.Column
pvFields("OrderDate").Orientation = PivotFieldOrientation.Page

' Create a custom table style.
Dim tbStyle As ITableStyle = WorkBook.TableStyles.Add("Style1")

' Set background colors.
tbStyle(TableStyleElementType.HeaderRow).Interior.Color = Color.FromArgb(255, 78, 167, 46)
tbStyle(TableStyleElementType.TotalRow).Interior.Color = Color.FromArgb(255, 228, 158, 221)
tbStyle(TableStyleElementType.WholeTable).Interior.Color = Color.FromArgb(255, 242, 206, 239)

' Set blue dashed borders for the whole table.
Dim lineBorder As Border = Border.NoBorder
Dim thinBlueSideBorder As New BorderLine(BorderLineStyle.Thin, Color.FromKnownColor(GrapeCity.Core.KnownColor.DarkBlue))
lineBorder.All = thinBlueSideBorder
tbStyle(TableStyleElementType.WholeTable).Borders.ApplyBorder(lineBorder)

' Set red dashed borders for the header row.
Dim lineBorder1 As Border = Border.NoBorder
Dim thinBlueSideBorder1 As New BorderLine(BorderLineStyle.Thin, Color.FromKnownColor(GrapeCity.Core.KnownColor.Moccasin))
lineBorder1.All = thinBlueSideBorder1
tbStyle(TableStyleElementType.HeaderRow).Borders.ApplyBorder(lineBorder1)

' Apply the custom style to the pivot table.
pvTable.TableStyle = tbStyle

Style Compatibility Explanation

By default, Spread for WPF maintains consistency with MsExcel in pivot table style behavior, such as the application rules for row header styles. To address some of MsExcel’s complex and hard-to-understand composite policies when handling pivot table styles, Spread for WPF provides the Compatibility.PivotTable option for optimization. When this option is turned off, Spread for WPF will optimize the related styles, and its display and behavior may differ from MsExcel. For specific differences, please refer to the table below.

Spread for WPF	MsExcel

Refer to the following example code to set Spread for WPF to be incompatible with MsExcel pivot table styles.

GrapeCity.Spreadsheet.WorkbookSet.Compatibility &= ~Compatibility.PivotTable;

GrapeCity.Spreadsheet.WorkbookSet.Compatibility = GrapeCity.Spreadsheet.WorkbookSet.Compatibility And Not Compatibility.PivotTable