Documents for Excel, Java Edition Documentation
Features / Pivot Table / Pivot Table Style
In This Topic
    Pivot Table Style
    In This Topic

    GcExcel 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

    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);

    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);

    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.