Documents for Excel, Java Edition Documentation
Manage Data / Use Pivot Table / Pivot Table Settings
In This Topic
    Pivot Table Settings
    In This Topic

    You can modify the settings of the pivot table added in a worksheet by referring to the following tasks:

    Configure pivot table fields

    The fields of a pivot table can be configured using the methods of the IPivotCaches interface and IPivotTables interface, as shown in the example code shared below.

    Java
    Copy Code
    // config pivot table's fields
    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);
    
    IPivotField field_Country = pivottable.getPivotFields().get("Country");
    field_Country.setOrientation(PivotFieldOrientation.PageField);

    Add field function

    In order to add field function in a pivot table, refer to the following example code.

    Java
    Copy Code
    // Change or set data field's summarize function.
    field_Amount.setFunction(ConsolidationFunction.Average);

    Filter pivot table

    In order to execute the filter operation on a pivot table, refer to the following example code.

    Java
    Copy Code
    IPivotField field_Product = pivottable.getPivotFields().get(1);
    field_Product.setOrientation(PivotFieldOrientation.RowField);
    
    IPivotField field_Amount = pivottable.getPivotFields().get(3);
    field_Amount.setOrientation(PivotFieldOrientation.DataField);
    
    IPivotField field_Country = pivottable.getPivotFields().get(5);
    field_Country.setOrientation(PivotFieldOrientation.PageField);
    
    // Apply row field filter.
    field_Product.getPivotItems().get("Apple").setVisible(false);
    field_Product.getPivotItems().get("Beans").setVisible(false);
    field_Product.getPivotItems().get("Orange").setVisible(false);
    
    // Apply page filter.
    field_Country.getPivotItems().get("United States").setVisible(false);
    field_Country.getPivotItems().get("Canada").setVisible(false);

    Managing pivot field level

    In order to manage the field level of a pivot table, refer to the following example code.

    Java
    Copy Code
    // Product in level 1.
    IPivotField field_product = pivottable.getPivotFields().get("Product");
    field_product.setOrientation(PivotFieldOrientation.RowField);
    
    // Category in level 2.
    IPivotField field_category = pivottable.getPivotFields().get("Category");
    field_category.setOrientation(PivotFieldOrientation.RowField);
    
    IPivotField field_Amount = pivottable.getPivotFields().get(3);
    field_Amount.setOrientation(PivotFieldOrientation.DataField);
    
    // Category will be in level 1 and product will be in level 2.
    field_product.setPosition(1);
    field_category.setPosition(0);

    Manage Grand Total Visibility Settings

    The Grand total in pivot table helps in analyzing the total sum of the data in the pivot table. You can display or hide the grand total for the row or column field by setting the visibility of ColumnGrand and RowGrand properties of the IPivotTable interface. These properties take boolean values and are set to true by default. For example, if you want to display the grand total only for rows, then set the RowGrand property to true and ColumnGrand to false.

    Refer to the following example code to manage the visibility settings of the grand total field.

    Java
    Copy Code
    // Set the PivotTable report to show grand totals for columns & rows
    pivottable.setColumnGrand(true);
    pivottable.setRowGrand(true);

    Change row axis layout

    The display of pivot table can be changed to any desired layout using the LayoutRowType enumeration. The following options are provided by this enumeration:

    Note: The SubtotalLocationType enumeration can only be set to Bottom if the LayoutRowType is set to TabularRow.

    Refer to the following example code to set the row axis layout of the pivot table to TabularRow.

    Java
    Copy Code
    // Set the PivotTable LayoutRowType to Tabular Row
    pivottable.setRowAxisLayout(LayoutRowType.TabularRow);

    Rename pivot table fields

    Sometimes, the pivot table fields are not easily comprehendible and hence can be renamed to meaningful and easily understandable names.

    Refer to the following example code to rename the pivot table fields.

    Java
    Copy Code
    // config 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);
    
    // Renaming DataField "Sum of Amount" to "Amount Total"
    pivottable.getDataFields().get(0).setName("Amount Total");

    Refresh pivot table

    In order to refresh a pivot table, refer to the following example code.

    Java
    Copy Code
    IPivotField field_Product = pivottable.getPivotFields().get("Product");
    field_Product.setOrientation(PivotFieldOrientation.RowField);
    
    IPivotField field_Amount = pivottable.getPivotFields().get(3);
    field_Amount.setOrientation(PivotFieldOrientation.DataField);
    
    // change pivot cache's source data.
    worksheet.getRange("D8").setValue(3000);
    
    // sync cache's data to pivot table.
    worksheet.getPivotTables().get(0).refresh();

    Modify pivot table

    In order to modify a pivot table, refer to the following example code.

    Java
    Copy Code
    // Defining source data
    Object sourceData = new Object[][] 
    { 
      { "Order ID", "Product", "Category", "Amount", "Date", "Country" },
      { 1, "Carrots", "Vegetables", 4270, new GregorianCalendar(2018, 0, 6),"United States" },
      { 2, "Broccoli", "Vegetables", 8239, new GregorianCalendar(2018, 0, 7),"United Kingdom" },
      { 3, "Banana", "Fruit", 617, new GregorianCalendar(2018, 0, 8),"United States" },
      { 4, "Banana", "Fruit", 8384, new GregorianCalendar(2018, 0, 10),"Canada" },
      { 5, "Beans", "Vegetables", 2626, new GregorianCalendar(2018, 0, 10),"Germany" },
      { 6, "Orange", "Fruit", 3610, new GregorianCalendar(2018, 0, 11),"United States" },
      { 7, "Broccoli", "Vegetables", 9062, new GregorianCalendar(2018, 0, 11),"Australia" },
      { 8, "Banana", "Fruit", 6906, new GregorianCalendar(2018, 0, 16),"New Zealand" },
      { 9, "Apple", "Fruit", 2417, new GregorianCalendar(2018, 0, 16),"France" },
      { 10, "Apple", "Fruit", 7431, new GregorianCalendar(2018, 0, 16),"Canada" },
      { 11, "Banana", "Fruit", 8250, new GregorianCalendar(2018, 0, 16),"Germany" },
      { 12, "Broccoli", "Vegetables", 7012, new GregorianCalendar(2018, 0, 18),"United States" },
      { 13, "Carrots", "Vegetables", 1903, new GregorianCalendar(2018, 0, 20),"Germany" },
      { 14, "Broccoli", "Vegetables", 2824, new GregorianCalendar(2018, 0, 22),"Canada" },
      { 15, "Apple", "Fruit", 6946, new GregorianCalendar(2018, 0, 24),"France" }, 
    };
            
    // Initialize workbook and fetch the default worksheet
    Workbook workbook = new Workbook();
    IWorksheet worksheet = workbook.getWorksheets().get(0);
            
    // Assigning data to the range
    worksheet.getRange("A1:F16").setValue(sourceData);
            
    // Creating pivot table and modifying it
    IPivotCache pivotcache = workbook.getPivotCaches().create(worksheet.getRange("A1:F16"));
    IPivotTable pivottable = worksheet.getPivotTables().add(pivotcache, 
    worksheet.getRange("I2"), "pivottable1");
            
    worksheet.getRange("D2:D16").setNumberFormat("$#,##0.00");
    worksheet.getRange("J4:J17, J9:J33").setNumberFormat("$#,##0.00");
    
    // Configure pivot table's fields
    IPivotField field_Product = pivottable.getPivotFields().get(1);
    field_Product.setOrientation(PivotFieldOrientation.RowField);
    
    IPivotField field_Category = pivottable.getPivotFields().get(2);
    field_Category.setOrientation(PivotFieldOrientation.RowField);
    
    IPivotField field_Amount = pivottable.getPivotFields().get(3);
    field_Amount.setOrientation(PivotFieldOrientation.DataField);
    
    // Modify subtotals for pivot field.
    field_Category.setSubtotals(EnumSet.of(SubtotalType.Sum, SubtotalType.Count, 
    SubtotalType.Average,SubtotalType.Max, SubtotalType.Min, SubtotalType.CountNums, 
    SubtotalType.StdDev, SubtotalType.StdDevP,SubtotalType.Var, SubtotalType.VarP));
            
    worksheet.getRange("E:E").setColumnWidth(12);
    worksheet.getRange("J:J").setColumnWidth(20);