Format Pivot Table Cells

Sometimes, you want to format pivot table cells. For example, you want to apply a background color to pivot table cells. Aspose.Cells provides two methods PivotTable.formatAll() and PivotTable.format(), which you can use for this purpose.

PivotTable.formatAll() applies the style to the entire pivot table while PivotTable.format() applies the style to a single cell of the pivot table.

The following sample code formats the entire pivot table with a light blue color and then formats the second table row yellow.

The input pivot table, before executing the code

The ouput pivot table, after the executing the code

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getDataDir(FormatPivotTableCells.class);
// Create workbook object from source file containing pivot table
Workbook workbook = new Workbook(dataDir + "pivotTable_test.xlsx");

// Access the worksheet by its name
Worksheet worksheet = workbook.getWorksheets().get("PivotTable");

// Access the pivot table
PivotTable pivotTable = worksheet.getPivotTables().get(0);

// Create a style object with background color light blue
Style style = workbook.createStyle();
style.setPattern(BackgroundType.SOLID);
style.setBackgroundColor(Color.getLightBlue());

// Format entire pivot table with light blue color
pivotTable.formatAll(style);

// Create another style object with yellow color
style = workbook.createStyle();
style.setPattern(BackgroundType.SOLID);
style.setBackgroundColor(Color.getYellow());

// Format the cells of the first row of the pivot table with yellow color
for (int col = 0; col < 5; col++) {
	pivotTable.format(1, col, style);
}

// Save the workbook object
workbook.save(dataDir + "out.xlsx");