Java - How to Create Pivot Tables in Excel
Tags: java, excel, pivottable, api
Published: 2022-09-28 06:23:27

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](https://www.e-iceblue.com/Introduce/xls-for-java.html).

- [Create a Pivot Table in Excel in Java](#create-a-pivot-table-in-excel-in-java)
- [Sort Pivot Table by Column Values in Java](#sort-pivot-table-by-column-values-in-java)
- [Expand or Collapse Rows in Pivot Table in Java](#expand-or-collapse-rows-in-pivot-table-in-java)
- [Preform Other Settings in Pivot Table](#other-settings)

##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:

```java
<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>
```
If you are not using maven, then you can find the required jar files from the zip file available in [this location](https://www.e-iceblue.com/Download/xls-for-java.html). 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.

```java
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);
    }
}
```

![createPivotTable](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g0114sxiy9p2s1zg5e4y.png)
##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.

```java
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);
    }
}
```

![sortdata](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lj3a08vh5fbjmhk6649l.png)
##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.

```java
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);
    }
}
```

![collapseRows](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u2x9778e76wd8jwm73wk.png)

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