Apply Styles to Pivot Tables in Aspose.Cells for Java

Aspose.Cells supports applying both legacy pivot autoformats (intended for .xls files) and modern named or custom pivot table styles (intended for .xlsx, .xlsm, and .xlsb files). The API you should call depends on the file format the workbook is saved to, not the format it was loaded from.

Introduction

Aspose.Cells exposes two parallel style APIs for pivot tables. The decision between them is driven by the file format you save the workbook to, not by the format you read it from. A workbook loaded from an .xls file can be re-saved as .xlsx, and in that case the modern style API applies rather than the legacy one.

For legacy .xls output, use the PivotTable.AutoFormatType property together with the com.aspose.cells.PivotTableAutoFormatType enumeration. This API corresponds to the autoformat picker that classic Excel offered for pivot tables.

For modern .xlsx, .xlsm, and .xlsb output, two flavors of style API are available:

PivotTable.PivotTableStyleType selects one of the built-in named styles (light and dark themes, including the styles added in Excel 2017). These presets are read-only.
PivotTable.PivotTableStyleName selects a custom style you define yourself through Workbook.getWorksheets().getTableStyles().addPivotTableStyle(...). Custom styles are required whenever you want to modify colors, borders, or fonts beyond what the presets offer.

In addition, PivotTable.formatAll(Style) is a shortcut that applies a single Style object to every cell of the pivot, overriding whatever is set through either of the style-name APIs above. This is useful when a uniform appearance is required regardless of the underlying theme.

Apply a Legacy XLS Preset Autoformat

PivotTable.AutoFormatType accepts a value from the com.aspose.cells.PivotTableAutoFormatType enumeration. The available values are REPORT_1 through REPORT_10, CLASSIC, and TABLE_1 through TABLE_10.

The following example loads a fresh workbook, populates the Fruit/Year/Amount sample data, adds a pivot table, applies PivotTableAutoFormatType.REPORT_5, and saves the result as .xls.

Why no column fields? Report-series autoformats (Report1 through Report10, Table1 through Table10) were designed in classic Excel for single-dimension pivot tables with row fields and values only — they have no built-in styling for column-field headers. If your pivot needs column fields, use the modern PivotTableStyleType presets from Scenario 2 instead, which are designed for the two-dimensional layout modern Excel uses.

import com.aspose.cells.*;

// Scenario 1: Apply a legacy XLS preset autoformat
// API in use: PivotTable.AutoFormatType
// Target file format: .xls (legacy)
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET

// Create a new workbook
Workbook workbook = new Workbook();

// Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);

// Populate the source data with header row (Fruit, Year, Amount)
// and 9 data rows covering grape, blueberry, kiwi, cherry across 2020 and 2021
sheet.getCells().get(0, 0).putValue("Fruit");
sheet.getCells().get(0, 1).putValue("Year");
sheet.getCells().get(0, 2).putValue("Amount");

sheet.getCells().get(1, 0).putValue("grape");
sheet.getCells().get(1, 1).putValue(2020);
sheet.getCells().get(1, 2).putValue(50);

sheet.getCells().get(2, 0).putValue("blueberry");
sheet.getCells().get(2, 1).putValue(2020);
sheet.getCells().get(2, 2).putValue(30);

sheet.getCells().get(3, 0).putValue("kiwi");
sheet.getCells().get(3, 1).putValue(2020);
sheet.getCells().get(3, 2).putValue(25);

sheet.getCells().get(4, 0).putValue("cherry");
sheet.getCells().get(4, 1).putValue(2020);
sheet.getCells().get(4, 2).putValue(40);

sheet.getCells().get(5, 0).putValue("grape");
sheet.getCells().get(5, 1).putValue(2021);
sheet.getCells().get(5, 2).putValue(60);

sheet.getCells().get(6, 0).putValue("blueberry");
sheet.getCells().get(6, 1).putValue(2021);
sheet.getCells().get(6, 2).putValue(35);

sheet.getCells().get(7, 0).putValue("kiwi");
sheet.getCells().get(7, 1).putValue(2021);
sheet.getCells().get(7, 2).putValue(28);

sheet.getCells().get(8, 0).putValue("cherry");
sheet.getCells().get(8, 1).putValue(2021);
sheet.getCells().get(8, 2).putValue(45);

sheet.getCells().get(9, 0).putValue("grape");
sheet.getCells().get(9, 1).putValue(2020);
sheet.getCells().get(9, 2).putValue(45);

// Add a pivot table at destination cell E3, named "Pivot1", using source range A1:C10
int pivotIndex = sheet.getPivotTables().add("A1:C10", "E3", "Pivot1");
PivotTable pivotTable = sheet.getPivotTables().get(pivotIndex);

// Assign fields: Fruit -> Rows, Amount -> Data
pivotTable.addFieldToArea(PivotFieldType.ROW, "Fruit");
pivotTable.addFieldToArea(PivotFieldType.DATA, "Amount");

// Apply the legacy XLS preset autoformat "Report5"
// Note: This property is only meaningful when saving as .xls.
// When saved as .xlsx/.xlsm/.xlsb, Excel ignores AutoFormatType
// and uses whatever PivotTableStyleType / PivotTableStyleName specifies.
pivotTable.setAutoFormatType(PivotTableAutoFormatType.REPORT_5);

// Save the workbook in legacy .xls format
workbook.save("output.xls");

Apply a Modern Named Preset Pivot Table Style

This is the recommended API for any modern file format. Unlike the legacy autoformat, the style selected here is rendered faithfully by Excel and survives round-trips through other Office tooling.

Define and Apply a Custom Pivot Table Style

The built-in presets cannot be modified. Whenever you need to override colors, borders, or fonts, you must define a custom pivot style. The workflow has three steps:

Add a custom style to the workbook’s TableStyles collection via Workbook.getWorksheets().getTableStyles().addPivotTableStyle(String name). This returns the index of the newly created style.
Configure the style by adding elements (such as WholeTable or GrandTotalRow) through TableStyle.getTableStyleElements().add(TableStyleElementType), then assign a Style to each element via TableStyleElement.setElementStyle(Style).
Apply the custom style to the pivot by setting PivotTable.PivotTableStyleName to the style’s name. Do not use PivotTableStyleType here, since that property selects built-in presets.

PivotTableStyleName and PivotTableStyleType are not interchangeable. Use PivotTableStyleType for built-in presets, and PivotTableStyleName for custom styles you have defined through addPivotTableStyle. Setting both is harmless, but only the one matching the intended source is rendered.

The available TableStyleElementType values include WHOLE_TABLE, FIRST_ROW, LAST_ROW, FIRST_COLUMN, LAST_COLUMN, GRAND_TOTAL_ROW, GRAND_TOTAL_COLUMN, PAGE_FIELD_LABELS, and PAGE_FIELD_VALUES.

The following example defines a custom pivot style with a thin black border on WholeTable and a bold red font on GrandTotalRow, then applies it via PivotTableStyleName and saves as .xlsx.

import com.aspose.cells.*;

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.getWorksheets().get(0);

// Populate source data: header row + 9 data rows (A1:C10)
worksheet.getCells().get("A1").putValue("Fruit");
worksheet.getCells().get("B1").putValue("Year");
worksheet.getCells().get("C1").putValue("Amount");

worksheet.getCells().get("A2").putValue("Grape");
worksheet.getCells().get("B2").putValue(2020);
worksheet.getCells().get("C2").putValue(100);

worksheet.getCells().get("A3").putValue("Blueberry");
worksheet.getCells().get("B3").putValue(2020);
worksheet.getCells().get("C3").putValue(200);

worksheet.getCells().get("A4").putValue("Kiwi");
worksheet.getCells().get("B4").putValue(2020);
worksheet.getCells().get("C4").putValue(300);

worksheet.getCells().get("A5").putValue("Cherry");
worksheet.getCells().get("B5").putValue(2020);
worksheet.getCells().get("C5").putValue(400);

worksheet.getCells().get("A6").putValue("Grape");
worksheet.getCells().get("B6").putValue(2021);
worksheet.getCells().get("C6").putValue(500);

worksheet.getCells().get("A7").putValue("Blueberry");
worksheet.getCells().get("B7").putValue(2021);
worksheet.getCells().get("C7").putValue(600);

worksheet.getCells().get("A8").putValue("Kiwi");
worksheet.getCells().get("B8").putValue(2021);
worksheet.getCells().get("C8").putValue(700);

worksheet.getCells().get("A9").putValue("Cherry");
worksheet.getCells().get("B9").putValue(2021);
worksheet.getCells().get("C9").putValue(800);

worksheet.getCells().get("A10").putValue("Grape");
worksheet.getCells().get("B10").putValue(2021);
worksheet.getCells().get("C10").putValue(900);

// Add pivot table sourced from A1:C10, anchored at E3, named "Pivot1"
int pivotIndex = worksheet.getPivotTables().add("A1:C10", "E3", "Pivot1");
PivotTable pivotTable = worksheet.getPivotTables().get(pivotIndex);

pivotTable.addFieldToArea(PivotFieldType.ROW, "Fruit");
pivotTable.addFieldToArea(PivotFieldType.COLUMN, "Year");
pivotTable.addFieldToArea(PivotFieldType.DATA, "Amount");

// Step 1: register a new custom pivot table style and capture its index
int styleIndex = workbook.getWorksheets().getTableStyles().addPivotTableStyle("CustomPivotStyle");
TableStyle tableStyle = workbook.getWorksheets().getTableStyles().get(styleIndex);

// Step 2: add a WholeTable element and apply thin black borders on all four sides
int wholeTableElementIndex = tableStyle.getTableStyleElements().add(TableStyleElementType.WHOLE_TABLE);
TableStyleElement wholeTableElement = tableStyle.getTableStyleElements().get(wholeTableElementIndex);
Style wholeTableStyle = workbook.createStyle();
BorderCollection borders = wholeTableStyle.getBorders();
Border borderTop = borders.getByBorderType(BorderType.TOP_BORDER);
borderTop.setLineStyle(CellBorderType.THIN);
borderTop.setColor(Color.getBlack());
Border borderBottom = borders.getByBorderType(BorderType.BOTTOM_BORDER);
borderBottom.setLineStyle(CellBorderType.THIN);
borderBottom.setColor(Color.getBlack());
Border borderLeft = borders.getByBorderType(BorderType.LEFT_BORDER);
borderLeft.setLineStyle(CellBorderType.THIN);
borderLeft.setColor(Color.getBlack());
Border borderRight = borders.getByBorderType(BorderType.RIGHT_BORDER);
borderRight.setLineStyle(CellBorderType.THIN);
borderRight.setColor(Color.getBlack());
wholeTableElement.setElementStyle(wholeTableStyle);

// Step 3: add a GrandTotalRow element and apply bold red font
int grandTotalElementIndex = tableStyle.getTableStyleElements().add(TableStyleElementType.GRAND_TOTAL_ROW);
TableStyleElement grandTotalElement = tableStyle.getTableStyleElements().get(grandTotalElementIndex);
Style grandTotalStyle = workbook.createStyle();
grandTotalStyle.getFont().setBold(true);
grandTotalStyle.getFont().setColor(Color.getRed());
grandTotalElement.setElementStyle(grandTotalStyle);

// Step 4: apply the custom style by name (NOT by PivotTableStyleType, which is for built-in presets)
pivotTable.setPivotTableStyleName("CustomPivotStyle");

workbook.save("output.xlsx");

Apply One Style to Every Pivot Cell with FormatAll

PivotTable.formatAll(Style) is a shortcut that applies a single Style object to every cell of the pivot table, including the data area, row and column headers, and totals. Whatever was previously set through PivotTableStyleType or PivotTableStyleName is overridden.

FormatAll overrides both PivotTableStyleType and PivotTableStyleName. Use it only when a uniform, theme-independent appearance is required across the entire pivot.

The following example creates a Style with a yellow solid fill, a bold dark-blue font, and thin black borders on all sides, then applies it with formatAll and saves as .xlsx.

import com.aspose.cells.*;

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.getWorksheets().get(0);

// Populate source data: header row (row 1) + 9 data rows (rows 2-10)
worksheet.getCells().get("A1").putValue("Fruit");
worksheet.getCells().get("B1").putValue("Year");
worksheet.getCells().get("C1").putValue("Amount");

worksheet.getCells().get("A2").putValue("Grape");
worksheet.getCells().get("B2").putValue(2020);
worksheet.getCells().get("C2").putValue(5000);

worksheet.getCells().get("A3").putValue("Blueberry");
worksheet.getCells().get("B3").putValue(2020);
worksheet.getCells().get("C3").putValue(3000);

worksheet.getCells().get("A4").putValue("Kiwi");
worksheet.getCells().get("B4").putValue(2020);
worksheet.getCells().get("C4").putValue(4000);

worksheet.getCells().get("A5").putValue("Cherry");
worksheet.getCells().get("B5").putValue(2020);
worksheet.getCells().get("C5").putValue(2000);

worksheet.getCells().get("A6").putValue("Grape");
worksheet.getCells().get("B6").putValue(2021);
worksheet.getCells().get("C6").putValue(6000);

worksheet.getCells().get("A7").putValue("Blueberry");
worksheet.getCells().get("B7").putValue(2021);
worksheet.getCells().get("C7").putValue(3500);

worksheet.getCells().get("A8").putValue("Kiwi");
worksheet.getCells().get("B8").putValue(2021);
worksheet.getCells().get("C8").putValue(4500);

worksheet.getCells().get("A9").putValue("Cherry");
worksheet.getCells().get("B9").putValue(2021);
worksheet.getCells().get("C9").putValue(2500);

worksheet.getCells().get("A10").putValue("Grape");
worksheet.getCells().get("B10").putValue(2021);
worksheet.getCells().get("C10").putValue(5500);

// Add pivot table: source range A1:C10, destination cell E3, name "Pivot1"
int pivotIndex = worksheet.getPivotTables().add("A1:C10", "E3", "Pivot1");
PivotTable pivotTable = worksheet.getPivotTables().get(pivotIndex);

// Assign pivot fields: Fruit -> Row area, Year -> Column area, Amount -> Data area
pivotTable.addFieldToArea(PivotFieldType.ROW, "Fruit");
pivotTable.addFieldToArea(PivotFieldType.COLUMN, "Year");
pivotTable.addFieldToArea(PivotFieldType.DATA, "Amount");

// Build a Style that will be forced onto every cell of the pivot table
Style style = workbook.createStyle();
style.setForegroundColor(Color.getYellow());
style.setPattern(BackgroundType.SOLID);
style.getFont().setBold(true);
style.getFont().setColor(Color.getDarkBlue());

style.getBorders().getByBorderType(BorderType.TOP_BORDER).setLineStyle(CellBorderType.THIN);
style.getBorders().getByBorderType(BorderType.TOP_BORDER).setColor(Color.getBlack());

style.getBorders().getByBorderType(BorderType.BOTTOM_BORDER).setLineStyle(CellBorderType.THIN);
style.getBorders().getByBorderType(BorderType.BOTTOM_BORDER).setColor(Color.getBlack());

style.getBorders().getByBorderType(BorderType.LEFT_BORDER).setLineStyle(CellBorderType.THIN);
style.getBorders().getByBorderType(BorderType.LEFT_BORDER).setColor(Color.getBlack());

style.getBorders().getByBorderType(BorderType.RIGHT_BORDER).setLineStyle(CellBorderType.THIN);
style.getBorders().getByBorderType(BorderType.RIGHT_BORDER).setColor(Color.getBlack());

// Apply FormatAll: forces this single style onto every cell of the pivot table,
// overriding any PivotTableStyleType / PivotTableStyleName previously set
pivotTable.formatAll(style);

// Save the workbook in the modern .xlsx format
workbook.save("output.xlsx");

Which Style API Should I Use?

The choice of style API depends on the file format you are saving to. Use the table below as a quick reference.

Target file format	API to use	Notes
`.xls` (legacy)	`PivotTable.AutoFormatType`	Values from `com.aspose.cells.PivotTableAutoFormatType` (e.g. `REPORT_1`–`REPORT_10`, `CLASSIC`, `TABLE_1`–`TABLE_10`). Ignored when saving as modern formats.
`.xlsx` / `.xlsm` / `.xlsb` (modern, built-in style)	`PivotTable.PivotTableStyleType`	Values from `com.aspose.cells.PivotTableStyleType` (light/dark themes, including Excel 2017 additions).
`.xlsx` / `.xlsm` / `.xlsb` (modern, custom style)	`PivotTable.PivotTableStyleName` + `Worksheets.TableStyles.addPivotTableStyle(...)`	Use when the built-in presets are not enough. Configure via `TableStyleElement.setElementStyle(...)`.
Any format (uniform override)	`PivotTable.formatAll(Style)`	Shortcut that overrides every other style setting across the entire pivot.

When in doubt, save as .xlsx and use PivotTableStyleType for built-in themes, or PivotTableStyleName for custom themes.

Add calculated field in Pivot Table Add Pivot Table Row and Column Fields in Aspose.Cells for Java