Custom Pivot Table Styles

SpreadJS enables you to manage custom pivot table themes by adding, removing, and modifying table styles, which can then be applied to your pivot tables. To apply these styles, you need to just set a style name for the pivot table.

You can customize pivot table styles by using the customPivotTableThemes property of the GC.Spread.Sheets.WorkBook class.

The CustomPivotTableThemesManager class provides the following methods to manage custom pivot table themes:

• all: Get all custom pivot table themes.

• get: Get a specific custom pivot table theme by name.

• add: Add a new custom pivot table theme using a style object or name.

• remove: Remove a custom pivot table theme by name.

• update: Update an existing custom pivot table theme by name or style object.

Additionally, the defaultPivotTableThememethod of the GC.Spread.Sheets.WorkBook class enables you to effectively manage default pivot table styles to ensure a consistent look and feel across all pivot tables.

Notes:

• Pivot table style names must be unique.

  For example, if you already have a custom pivot table style named "TEST", you cannot create another pivot table style with the same name.

• Pivot table style names are case insensitive.

• When a style name is added to a pivot table, it finds the style by the name.

  If the style name matches an internal style, it will be applied directly. If not, it will search for the corresponding style in the customStyles collection. If found, that style will be applied. Otherwise, the default style will be used.

• The defaultPivotTableTheme should be a string representing the style name of the pivot table.

• If a pivot table is created without explicitly specifying a style, and if a style with this default name exists, then the style will be applied to that pivot table.

• Custom pivot table themes do not support older versions of Excel IO.

Using code

The following code samples demonstrate different methods necessary to manage custom pivot table themes.

1. Add a new pivot table theme.

   const spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
   
   // Add a new pivot table theme named “custom0”
   const pivotTableStyle = spread.customPivotTableThemes.add("custom0");
   const firstColumnStripeStyle = new GC.Spread.Pivot.PivotTableStyle();
   firstColumnStripeStyle.backColor = "skyblue";
   pivotTableStyle.firstColumnStripeStyle(firstColumnStripeStyle);

2. Get a pivot table theme.

   // Get pivot table theme
   const pivotTableStyle = spread.customPivotTableThemes.get("custom0");

3. Apply pivot table theme.

   // Create pivot table
   const sourceData = [["Date","Buyer","Type","Amount"],
   ["01-Jan","Mimi","Fuel",74],
   ["15-Jan","Mary","Food",235],
   ["17-Jan","Denny","Sports",20],
   ["21-Jan","Kelly","Books",125]];
   activeSheet.setArray(0, 0, sourceData);
   activeSheet.tables.add('sourceData', 0, 0, 5, 4);
   
   // Set the pivot table theme
   const pivotTable = activeSheet.pivotTables.add(
   "pivottable1",
   "sourceData",
   1,
   1,
   GC.Spread.Pivot.PivotTableLayoutType.compact,
   GC.Spread.Pivot.PivotTableThemes.medium2
   );
   
   // Apply style to pivot table
   pivotTable.theme("custom0");

4. Modify pivot table theme.

   When you make changes to an existing pivot table style, it will automatically update all tables where that style was previously applied.

   // Modify pivot table theme
   const pivotTableStyle = spread.customPivotTableThemes.get("custom0");
   pivotTableStyle.firstColumnStripeStyle().backColor = "red";
   
   // Update pivot table theme
   spread.customPivotTableThemes.update("custom0", new GC.Spread.Pivot.PivotTableTheme());

5. Delete an existing theme.

   When you delete a custom pivot table style, all tables that use the deleted style will switch to the default style.

   // Delete pivot table theme
   spread.customPivotTableThemes.remove("custom0");

6. Set default theme.

   // Set default pivot table theme
   spread.defaultPivotTableTheme("custom0");

Using Designer

You can also format custom pivot table styles using the SpreadJS Designer Component by following the steps below:

1. Click the PivotTableStyles button available in the DESIGN > PivotTable Styles group.

   
   

   
   

   
   

2. Click the dropdown arrow and select the New Pivot Table Style.. option to add a new pivot table style by setting your required style elements.

   
   

   
   

   
   

3. All the user-created pivot table styles are displayed under the Custom section of the pivot table styles collection list.

   
   

   
   

   
   

4. Right-click on the custom pivot table style and perform actions such as apply, modify, duplicate, delete, and set as default actions, as per your requirement.

   
   

   
   

   
   

Note: You cannot modify or delete built-in pivot table styles.