TheLazy.Dev
Portfolio GitHub

Blog

java |excel |pivottable |api

Java - How to Create Pivot Tables in Excel

alexis92

Alexis

Posted on September 28, 2022

Java - How to Create Pivot Tables in Excel

With Excel Pivot tables, users can calculate, group, and summarize large amounts of data in a concise, tabular format, which makes reporting and analysis easier. As one of the most powerful tools in Excel, it provides users with the ability to view static data from multiple perspectives. This article introduces how to create a pivot table in Excel in Java, how to sort pivot table data, and how to perform other settings in pivot table, by using Spire.XLS for Java.

Add Spire.Xls jar as dependency

If you are working on a maven project, you can include the dependency in pom.xml file using this:

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>12.8.4</version>
    </dependency>
</dependencies>
Enter fullscreen mode Exit fullscreen mode

If you are not using maven, then you can find the required jar files from the zip file available in this location. Include all the jar files into the application lib folder to run the sample code given in this tutorial.

Create a Pivot Table in Excel in Java

Below are the steps to create a pivot table based on the data in an existing Excel file using Spire.XLS for Java.

  • Create a Workbook object.
  • Load a sample Excel document using Workbook.loadFromFile() method.
  • Get a specified worksheet using Workbook.getWorksheets().get() method.
  • Select the data source range using Worksheet.getRange().get() method, and add the range to the PivotCachesCollection using Workbook.PivotCaches.add() method and return an object of PivotCache.
  • Add a pivot table to the worksheet and set the location and cache of it using Worksheet.getPivotTables().add() method.
  • Drag "Region" and "Product" fields to rows area.
  • Add"Quantity" and "Amount" fields to values area.
  • Save the result document using Workbook.saveToFile() method. 
import com.spire.xls.*;

public class CreatePivotTable {

    public static void main(String[] args) {

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

        //Load a sample Excel document
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\data.xlsx");

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

        //Select the data source range
        CellRange dataRange = sheet.getRange().get("C1:F11");
        PivotCache cache = workbook.getPivotCaches().add(dataRange);

        //Add a PivotTable to the worksheet and set the location and cache of it
        PivotTable pt = sheet.getPivotTables().add("Pivot Table", sheet.getRange().get("H3"), cache);

        //Drag "Region" and "Product" fields to rows area
        PivotField regionField =(PivotField)pt.getPivotFields().get("Region");
        regionField.setAxis(AxisTypes.Row);
        pt.getOptions().setRowHeaderCaption("Region");
        PivotField productField = (PivotField)pt.getPivotFields().get("Product");
        productField.setAxis(AxisTypes.Row);

        //Add "Quantity" and "Amount" fields to values area
        pt.getDataFields().add(pt.getPivotFields().get("Quantity"), "SUM of Quantity", SubtotalTypes.Sum);
        pt.getDataFields().add(pt.getPivotFields().get("Amount"), "SUM of Amount", SubtotalTypes.Sum);

        //Apply a built-in style to the pivot table
        pt.setBuiltInStyle(PivotBuiltInStyles.PivotStyleMedium11);

        //Set column width
        sheet.setColumnWidth(8,16);
        sheet.setColumnWidth(9,16);
        sheet.setColumnWidth(10,16);

        //Save the document
        workbook.saveToFile("output/CreatePivotTable.xlsx", ExcelVersion.Version2016);
    }
}
Enter fullscreen mode Exit fullscreen mode

createPivotTable

Sort Pivot Table by Column Values in Java

A specific field can be accessed by PivotTable.getPivotFields().get() method, and then you can set its sort type using PivotField. setSortType() method. The following code example shows you how to sort pivot table by column values of “Region” field.

import com.spire.xls.*;
import com.spire.xls.core.IPivotDataField;

public class SortPivotTableByRow {

    public static void main(String[] args) {

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

        //Load a sample Excel document
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\data.xlsx");

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

        //Select the data source range
        CellRange dataRange = sheet.getRange().get("A1:F11");
        PivotCache cache = workbook.getPivotCaches().add(dataRange);

        //Add a PivotTable to the worksheet and set the location and cache of it
        PivotTable pt = sheet.getPivotTables().add("Pivot Table", sheet.getRange().get("H3"), cache);

        //Drag "Region" , "Order ID" and "Product" fields to rows area
        PivotField regionField =(PivotField)pt.getPivotFields().get("Region");
        regionField.setAxis(AxisTypes.Row);
        pt.getOptions().setRowHeaderCaption("Region");

        PivotField idField = (PivotField)pt.getPivotFields().get("Order ID");
        idField.setAxis(AxisTypes.Row);

        PivotField productField = (PivotField)pt.getPivotFields().get("Product");
        productField.setAxis(AxisTypes.Row);

        //Add "Quantity" and "Amount" fields to values area
        pt.getDataFields().add(pt.getPivotFields().get("Quantity"), "SUM of Quantity", SubtotalTypes.Sum);
        pt.getDataFields().add(pt.getPivotFields().get("Amount"), "SUM of Amount", SubtotalTypes.Sum);

        //Apply a built-in style to the pivot table
        pt.setBuiltInStyle(PivotBuiltInStyles.PivotStyleMedium11);

        //Calculate data
        pt.calculateData();

        //Sort data in the column of "Order ID" field
        idField.setSortType(PivotFieldSortType.Descending);

        //Set column width
        sheet.setColumnWidth(8,16);
        sheet.setColumnWidth(9,16);
        sheet.setColumnWidth(10,16);

        //Save the document
        workbook.saveToFile("output/SortDataByRow.xlsx", ExcelVersion.Version2016);
    }
}
Enter fullscreen mode Exit fullscreen mode

sortdata

Expand or Collapse Rows in Pivot Table in Java

To collapse the details under a certain pivot field, use PivotField.hideItemDetail(String, Boolean) method and set the second parameter to true; to show the details, use the same method and set the second parameter to false.

import com.spire.xls.*;

public class CollapseRows {

    public static void main(String[] args) {

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

        //Load a sample Excel document
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\data.xlsx");

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

        //Select the data source range
        CellRange dataRange = sheet.getRange().get("C1:F11");
        PivotCache cache = workbook.getPivotCaches().add(dataRange);

        //Add a PivotTable to the worksheet and set the location and cache of it
        PivotTable pt = sheet.getPivotTables().add("Pivot Table", sheet.getRange().get("H3"), cache);

        //Drag "Region" and "Product" fields to rows area
        PivotField regionField =(PivotField)pt.getPivotFields().get("Region");
        regionField.setAxis(AxisTypes.Row);
        pt.getOptions().setRowHeaderCaption("Region");
        PivotField productField = (PivotField)pt.getPivotFields().get("Product");
        productField.setAxis(AxisTypes.Row);

        //Hide item details of the region field
        regionField.hideItemDetail("West",true);
        regionField.hideItemDetail("East",true);

        //Add "Quantity" and "Amount" fields to values area
        pt.getDataFields().add(pt.getPivotFields().get("Quantity"), "SUM of Quantity", SubtotalTypes.Sum);
        pt.getDataFields().add(pt.getPivotFields().get("Amount"), "SUM of Amount", SubtotalTypes.Sum);

        //Apply a built-in style to the pivot table
        pt.setBuiltInStyle(PivotBuiltInStyles.PivotStyleMedium11);

        //Calculate data
        pt.calculateData();

        //Set column width
        sheet.setColumnWidth(8,16);
        sheet.setColumnWidth(9,16);
        sheet.setColumnWidth(10,16);

        //Save the document
        workbook.saveToFile("output/CollapseRows.xlsx", ExcelVersion.Version2016);
    }
}
Enter fullscreen mode Exit fullscreen mode

collapseRows

Other Settings

Refresh Pivot Tale/Update data source:
PivotTable.getCache().isRefreshOnLoad(true);

Display or show subtotals:
PivotTable.isShowSubtotals(true);

Change data source:
PivotTable.changeDataSource();

Add a filter:

PivotReportFilter filter = new PivotReportFilter(String fieldName);
PivotTable.getReportFilters().add(filter);

💖 💪 🙅 🚩
alexis92
Alexis

Posted on September 28, 2022

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related

Java - How to Create Pivot Tables in Excel
java Java - How to Create Pivot Tables in Excel

September 28, 2022