Pivot Table Style | Document Solutions for Excel, Java Edition

Pivot Table Style

In This Topic

DsExcel Java allows users to apply built-in and custom styles to the pivot table.

With the help of this feature, users will be able to save pivot tables with different styles (with respect to the pivot table layout and pivot table fields). Users can customize how their pivot table is displayed including the pivot table’s orientation, page size, pivot table fields and many other characteristics as per their custom display preferences. Further, users can also refer to the topic Export Pivot Table Styles and Format in order export spreadsheets with different pivot table styles in PDF format.

Usually, when users add a pivot table to the worksheet, a default pivot table style is applied automatically. Users can modify the default style of the pivot table added to the worksheet by either copying an existing style (also called built-in style) or creating a custom pivot table style right from the scratch. In order to apply style to the pivot table, you can refer to the following sections:

Apply Built-In Pivot Table Style
Apply Custom Style

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 either use the setStyle() method or use the setTableStyle() method of the IPivotTable interface.

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

Builtin style

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

Java	Copy Code
// Initialize workbook Workbook workbook = new Workbook(); // Fetch default worksheet IWorksheet worksheet = workbook.getWorksheets().get(0); // Create PivotTable Object sourceData = new Object[][] { { "Order ID", "Product", "Category", "Amount", "Date", "Country" }, { 1, "Carrots", "Vegetables", 4270, new GregorianCalendar(2012, 1, 6), "United States" }, { 2, "Broccoli", "Vegetables", 8239, new GregorianCalendar(2012, 1, 7), "United Kingdom" }, { 3, "Banana", "Fruit", 617, new GregorianCalendar(2012, 1, 8), "United States" }, { 4, "Banana", "Fruit", 8384, new GregorianCalendar(2012, 1, 10), "Canada" }, { 5, "Beans", "Vegetables", 2626, new GregorianCalendar(2012, 1, 10), "Germany" }, { 6, "Orange", "Fruit", 3610, new GregorianCalendar(2012, 1, 11), "United States" }, { 7, "Broccoli", "Vegetables", 9062, new GregorianCalendar(2012, 1, 11), "Australia" }, { 8, "Banana", "Fruit", 6906, new GregorianCalendar(2012, 1, 16), "New Zealand" }, { 9, "Apple", "Fruit", 2417, new GregorianCalendar(2012, 1, 16), "France" }, { 10, "Apple", "Fruit", 7431, new GregorianCalendar(2012, 1, 16), "Canada" }, { 11, "Banana", "Fruit", 8250, new GregorianCalendar(2012, 1, 16), "Germany" }, { 12, "Broccoli", "Vegetables", 7012, new GregorianCalendar(2012, 1, 18), "United States" }, { 13, "Carrots", "Vegetables", 1903, new GregorianCalendar(2012, 1, 20), "Germany" }, { 14, "Broccoli", "Vegetables", 2824, new GregorianCalendar(2012, 1, 22), "Canada" }, { 15, "Apple", "Fruit", 6946, new GregorianCalendar(2012, 1, 24), "France" }, }; worksheet.getRange("A20:F33").setValue(sourceData); worksheet.getRange("A:F").setColumnWidth(10); // Add pivot table IPivotCache pivotcache = workbook.getPivotCaches().create(worksheet.getRange("A20:F33")); IPivotTable pivottable = worksheet.getPivotTables().add(pivotcache, worksheet .getRange("A1"), "pivottable1"); // Setting number format for a field worksheet.getRange("D21:D35").setNumberFormat("$#,##0.00"); // Configure pivot table's fields IPivotField field_Date = pivottable.getPivotFields().get("Date"); field_Date.setOrientation(PivotFieldOrientation.PageField); IPivotField field_Category = pivottable.getPivotFields().get("Category"); field_Category.setOrientation(PivotFieldOrientation.RowField); IPivotField field_Product = pivottable.getPivotFields().get("Product"); field_Product.setOrientation(PivotFieldOrientation.ColumnField); IPivotField field_Amount = pivottable.getPivotFields().get("Amount"); field_Amount.setOrientation(PivotFieldOrientation.DataField); field_Amount.setNumberFormat("$#,##0.00"); IPivotField field_Country = pivottable.getPivotFields().get("Country"); field_Country.setOrientation(PivotFieldOrientation.RowField); // Set pivot style pivottable.setTableStyle("PivotStyleMedium20"); worksheet.getPageSetup().setTopMargin(30); worksheet.getPageSetup().setLeftMargin(30); worksheet.getRange("A1:H16").getColumns().autoFit(); // Saving workbook to PDF workbook.save("PivotBuiltInStyle.pdf", SaveFileFormat.Pdf);

Java

Copy Code

// Initialize workbook
Workbook workbook = new Workbook();
        
// Fetch default worksheet
IWorksheet worksheet = workbook.getWorksheets().get(0);

// Create PivotTable
Object sourceData = new Object[][] { 
{ "Order ID", "Product", "Category", "Amount", "Date", "Country" },
{ 1, "Carrots", "Vegetables", 4270, new GregorianCalendar(2012, 1, 6), "United States" },
{ 2, "Broccoli", "Vegetables", 8239, new GregorianCalendar(2012, 1, 7), "United Kingdom" },
{ 3, "Banana", "Fruit", 617, new GregorianCalendar(2012, 1, 8), "United States" },
{ 4, "Banana", "Fruit", 8384, new GregorianCalendar(2012, 1, 10), "Canada" },
{ 5, "Beans", "Vegetables", 2626, new GregorianCalendar(2012, 1, 10), "Germany" },
{ 6, "Orange", "Fruit", 3610, new GregorianCalendar(2012, 1, 11), "United States" },
{ 7, "Broccoli", "Vegetables", 9062, new GregorianCalendar(2012, 1, 11), "Australia" },
{ 8, "Banana", "Fruit", 6906, new GregorianCalendar(2012, 1, 16), "New Zealand" },
{ 9, "Apple", "Fruit", 2417, new GregorianCalendar(2012, 1, 16), "France" },
{ 10, "Apple", "Fruit", 7431, new GregorianCalendar(2012, 1, 16), "Canada" },
{ 11, "Banana", "Fruit", 8250, new GregorianCalendar(2012, 1, 16), "Germany" },
{ 12, "Broccoli", "Vegetables", 7012, new GregorianCalendar(2012, 1, 18), "United States" },
{ 13, "Carrots", "Vegetables", 1903, new GregorianCalendar(2012, 1, 20), "Germany" },
{ 14, "Broccoli", "Vegetables", 2824, new GregorianCalendar(2012, 1, 22), "Canada" },
{ 15, "Apple", "Fruit", 6946, new GregorianCalendar(2012, 1, 24), "France" }, };

worksheet.getRange("A20:F33").setValue(sourceData);
worksheet.getRange("A:F").setColumnWidth(10);
        
// Add pivot table
IPivotCache pivotcache = 
workbook.getPivotCaches().create(worksheet.getRange("A20:F33"));
IPivotTable pivottable = 
worksheet.getPivotTables().add(pivotcache, worksheet
.getRange("A1"), "pivottable1");
        
// Setting number format for a field
worksheet.getRange("D21:D35").setNumberFormat("$#,##0.00");

// Configure pivot table's fields
IPivotField field_Date = 
pivottable.getPivotFields().get("Date");
field_Date.setOrientation(PivotFieldOrientation.PageField);

IPivotField field_Category = 
pivottable.getPivotFields().get("Category");
field_Category.setOrientation(PivotFieldOrientation.RowField);

IPivotField field_Product = 
pivottable.getPivotFields().get("Product");
field_Product.setOrientation(PivotFieldOrientation.ColumnField);

IPivotField field_Amount = 
pivottable.getPivotFields().get("Amount");
field_Amount.setOrientation(PivotFieldOrientation.DataField);
field_Amount.setNumberFormat("$#,##0.00");

IPivotField field_Country = 
pivottable.getPivotFields().get("Country");
field_Country.setOrientation(PivotFieldOrientation.RowField);

// Set pivot style
pivottable.setTableStyle("PivotStyleMedium20");

worksheet.getPageSetup().setTopMargin(30);
worksheet.getPageSetup().setLeftMargin(30);

worksheet.getRange("A1:H16").getColumns().autoFit();
        
// Saving workbook to PDF
workbook.save("PivotBuiltInStyle.pdf", SaveFileFormat.Pdf);

Note: While applying built-in styles to the pivot table, it is important to note that if users apply a TableStyle whose setShowAsAvailableTableStyle method is true, then the InvalidOperationException is thrown.

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 setStyle() method of the IPivotTable interface.

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

Custom style

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

Java	Copy Code
// Initialize workbook Workbook workbook = new Workbook(); // Fetch default worksheet IWorksheet worksheet = workbook.getWorksheets().get(0); // Create PivotTable Object sourceData = new Object[][] { { "Order ID", "Product", "Category", "Amount", "Date", "Country" }, { 1, "Carrots", "Vegetables", 4270, new GregorianCalendar(2012, 1, 6), "United States" }, { 2, "Broccoli", "Vegetables", 8239, new GregorianCalendar(2012, 1, 7), "United Kingdom" }, { 3, "Banana", "Fruit", 617, new GregorianCalendar(2012, 1, 8), "United States" }, { 4, "Banana", "Fruit", 8384, new GregorianCalendar(2012, 1, 10), "Canada" }, { 5, "Beans", "Vegetables", 2626, new GregorianCalendar(2012, 1, 10), "Germany" }, { 6, "Orange", "Fruit", 3610, new GregorianCalendar(2012, 1, 11), "United States" }, { 7, "Broccoli", "Vegetables", 9062, new GregorianCalendar(2012, 1, 11), "Australia" }, { 8, "Banana", "Fruit", 6906, new GregorianCalendar(2012, 1, 16), "New Zealand" }, { 9, "Apple", "Fruit", 2417, new GregorianCalendar(2012, 1, 16), "France" }, { 10, "Apple", "Fruit", 7431, new GregorianCalendar(2012, 1, 16), "Canada" }, { 11, "Banana", "Fruit", 8250, new GregorianCalendar(2012, 1, 16), "Germany" }, { 12, "Broccoli", "Vegetables", 7012, new GregorianCalendar(2012, 1, 18), "United States" }, { 13, "Carrots", "Vegetables", 1903, new GregorianCalendar(2012, 1, 20), "Germany" }, { 14, "Broccoli", "Vegetables", 2824, new GregorianCalendar(2012, 1, 22), "Canada" }, { 15, "Apple", "Fruit", 6946, new GregorianCalendar(2012, 1, 24), "France" }, }; worksheet.getRange("A20:F33").setValue(sourceData); worksheet.getRange("A:F").setColumnWidth(10); // Add pivot table IPivotCache pivotcache = workbook.getPivotCaches().create(worksheet.getRange("A20:F33")); IPivotTable pivottable = worksheet.getPivotTables().add(pivotcache, worksheet.getRange("A1"),"pivottable1"); // Setting number format for a field worksheet.getRange("D21:D35").setNumberFormat("$#,##0.00"); // Configure pivot table's fields IPivotField field_Date = pivottable.getPivotFields().get("Date"); field_Date.setOrientation(PivotFieldOrientation.PageField); IPivotField field_Category = pivottable.getPivotFields().get("Category"); field_Category.setOrientation(PivotFieldOrientation.RowField); IPivotField field_Product = pivottable.getPivotFields().get("Product"); field_Product.setOrientation(PivotFieldOrientation.ColumnField); IPivotField field_Amount = pivottable.getPivotFields().get("Amount"); field_Amount.setOrientation(PivotFieldOrientation.DataField); field_Amount.setNumberFormat("$#,##0.00"); IPivotField field_Country = pivottable.getPivotFields().get("Country"); field_Country.setOrientation(PivotFieldOrientation.RowField); // Create pivot style with name "CustomPivotstyle" ITableStyle pivotStyle = workbook.getTableStyles().add("CustomPivotstyle"); // Set table style as pivot table style pivotStyle.setShowAsAvailablePivotStyle(true); pivotStyle.getTableStyleElements() .get(TableStyleElementType.PageFieldLabels).getInterior() .setColor(com.grapecity.documents.excel.Color.GetLightGreen()); pivotStyle.getTableStyleElements() .get(TableStyleElementType.PageFieldValues).getInterior() .setColor(com.grapecity.documents.excel.Color.GetLightGreen()); pivotStyle.getTableStyleElements() .get(TableStyleElementType.GrandTotalColumn).getInterior() .setColor(com.grapecity.documents.excel.Color.GetPowderBlue()); pivotStyle.getTableStyleElements() .get(TableStyleElementType.GrandTotalRow).getInterior() .setColor(com.grapecity.documents.excel.Color.GetPowderBlue()); pivotStyle.getTableStyleElements() .get(TableStyleElementType.HeaderRow).getInterior() .setColor(com.grapecity.documents.excel.Color.GetMistyRose()); pivotStyle.getTableStyleElements() .get(TableStyleElementType.FirstColumn).getInterior() .setColor(com.grapecity.documents.excel.Color.GetLightPink()); pivotStyle.getTableStyleElements() .get(TableStyleElementType.FirstRowStripe).getInterior() .setColor(com.grapecity.documents.excel.Color.GetSteelBlue()); pivotStyle.getTableStyleElements() .get(TableStyleElementType.SecondRowStripe).getInterior() .setColor(com.grapecity.documents.excel.Color.GetNavajoWhite()); // Set ShowTableStyleRowStripes as true pivottable.setShowTableStyleRowStripes(true); // Set pivot table style pivottable.setStyle(pivotStyle); worksheet.getRange("A1:H16").getColumns().autoFit(); worksheet.getPageSetup().setTopMargin(30); worksheet.getPageSetup().setLeftMargin(30); worksheet.getRange("A1:H16").getColumns().autoFit(); // Saving workbook to PDF workbook.save("PivotTableCustomStyle.pdf", SaveFileFormat.Pdf);

Java

Copy Code

// Initialize workbook
Workbook workbook = new Workbook();
        
// Fetch default worksheet
IWorksheet worksheet = workbook.getWorksheets().get(0);

// Create PivotTable
Object sourceData = new Object[][] { 
{ "Order ID", "Product", "Category", "Amount", "Date", "Country" },
{ 1, "Carrots", "Vegetables", 4270, new GregorianCalendar(2012, 1, 6), "United States" },
{ 2, "Broccoli", "Vegetables", 8239, new GregorianCalendar(2012, 1, 7), "United Kingdom" },
{ 3, "Banana", "Fruit", 617, new GregorianCalendar(2012, 1, 8), "United States" },
{ 4, "Banana", "Fruit", 8384, new GregorianCalendar(2012, 1, 10), "Canada" },
{ 5, "Beans", "Vegetables", 2626, new GregorianCalendar(2012, 1, 10), "Germany" },
{ 6, "Orange", "Fruit", 3610, new GregorianCalendar(2012, 1, 11), "United States" },
{ 7, "Broccoli", "Vegetables", 9062, new GregorianCalendar(2012, 1, 11), "Australia" },
{ 8, "Banana", "Fruit", 6906, new GregorianCalendar(2012, 1, 16), "New Zealand" },
{ 9, "Apple", "Fruit", 2417, new GregorianCalendar(2012, 1, 16), "France" },
{ 10, "Apple", "Fruit", 7431, new GregorianCalendar(2012, 1, 16), "Canada" },
{ 11, "Banana", "Fruit", 8250, new GregorianCalendar(2012, 1, 16), "Germany" },
{ 12, "Broccoli", "Vegetables", 7012, new GregorianCalendar(2012, 1, 18), "United States" },
{ 13, "Carrots", "Vegetables", 1903, new GregorianCalendar(2012, 1, 20), "Germany" },
{ 14, "Broccoli", "Vegetables", 2824, new GregorianCalendar(2012, 1, 22), "Canada" },
{ 15, "Apple", "Fruit", 6946, new GregorianCalendar(2012, 1, 24), "France" }, };

worksheet.getRange("A20:F33").setValue(sourceData);
worksheet.getRange("A:F").setColumnWidth(10);
        
// Add pivot table
IPivotCache pivotcache = 
workbook.getPivotCaches().create(worksheet.getRange("A20:F33"));
IPivotTable pivottable = 
worksheet.getPivotTables().add(pivotcache, worksheet.getRange("A1"),"pivottable1");
        
// Setting number format for a field
worksheet.getRange("D21:D35").setNumberFormat("$#,##0.00");

// Configure pivot table's fields
IPivotField field_Date = pivottable.getPivotFields().get("Date");
field_Date.setOrientation(PivotFieldOrientation.PageField);

IPivotField field_Category = pivottable.getPivotFields().get("Category");
field_Category.setOrientation(PivotFieldOrientation.RowField);

IPivotField field_Product = pivottable.getPivotFields().get("Product");
field_Product.setOrientation(PivotFieldOrientation.ColumnField);

IPivotField field_Amount = pivottable.getPivotFields().get("Amount");
field_Amount.setOrientation(PivotFieldOrientation.DataField);
field_Amount.setNumberFormat("$#,##0.00");

IPivotField field_Country = pivottable.getPivotFields().get("Country");
field_Country.setOrientation(PivotFieldOrientation.RowField);

// Create pivot style with name "CustomPivotstyle"
ITableStyle pivotStyle = workbook.getTableStyles().add("CustomPivotstyle");

// Set table style as pivot table style
pivotStyle.setShowAsAvailablePivotStyle(true);

pivotStyle.getTableStyleElements()
.get(TableStyleElementType.PageFieldLabels).getInterior()
.setColor(com.grapecity.documents.excel.Color.GetLightGreen());
pivotStyle.getTableStyleElements()
.get(TableStyleElementType.PageFieldValues).getInterior()
.setColor(com.grapecity.documents.excel.Color.GetLightGreen());

pivotStyle.getTableStyleElements()
.get(TableStyleElementType.GrandTotalColumn).getInterior()
.setColor(com.grapecity.documents.excel.Color.GetPowderBlue());
pivotStyle.getTableStyleElements()
.get(TableStyleElementType.GrandTotalRow).getInterior()
.setColor(com.grapecity.documents.excel.Color.GetPowderBlue());

pivotStyle.getTableStyleElements()
.get(TableStyleElementType.HeaderRow).getInterior()
.setColor(com.grapecity.documents.excel.Color.GetMistyRose());
pivotStyle.getTableStyleElements()
.get(TableStyleElementType.FirstColumn).getInterior()
.setColor(com.grapecity.documents.excel.Color.GetLightPink());

pivotStyle.getTableStyleElements()
.get(TableStyleElementType.FirstRowStripe).getInterior()
.setColor(com.grapecity.documents.excel.Color.GetSteelBlue());
pivotStyle.getTableStyleElements()
.get(TableStyleElementType.SecondRowStripe).getInterior()
.setColor(com.grapecity.documents.excel.Color.GetNavajoWhite());

// Set ShowTableStyleRowStripes as true
pivottable.setShowTableStyleRowStripes(true);

// Set pivot table style
pivottable.setStyle(pivotStyle);
worksheet.getRange("A1:H16").getColumns().autoFit();
worksheet.getPageSetup().setTopMargin(30);
worksheet.getPageSetup().setLeftMargin(30);
worksheet.getRange("A1:H16").getColumns().autoFit();
        
// Saving workbook to PDF
workbook.save("PivotTableCustomStyle.pdf", SaveFileFormat.Pdf);

Note: While applying custom styles to the pivot table, it is important to note that if users apply a TableStyle whose setShowAsAvailablePivotStyle method is false, then the InvalidOperationException is thrown.