How to Create Excel PivotTable in Java Application

As one of the most powerful tools in Excel, PivotTable has the ability to calculate, summarize, and analyze data, which allows us to see the comparisons and trends of our data more intuitively. In addition, PivotTable also has the ability to sort and filter data, and it can meet our daily business reporting requirements. This article will introduce how to create an Excel PivotTable in Java application.

Installation

Method 1: You need to download the Free Spire.XLS for Java and unzip it. And then add the Spire.Xls.jar file to your project as dependency.

Method 2: If you use maven, you can easily add the jar dependency by adding the following configurations to the pom.xml.

<repositories>
        <repository>
            <id>com.e-iceblue</id>
            <name>e-iceblue</name>
            <url>http://repo.e-iceblue.com/nexus/content/groups/public/</url>
        </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls.free</artifactId>
        <version>2.2.0</version>
    </dependency>
</dependencies>

Create PivotTable

import com.spire.xls.*;

public class CreatePivotTable {
    public static void main(String[] args)  {

        //Load a sample Excel workbook
        Workbook workbook = new Workbook();
        workbook.loadFromFile("Sample.xlsx");

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

        // Add a PivotTable to the worksheet
        CellRange dataRange = sheet.getCellRange("A1:D11");
        PivotCache cache = workbook.getPivotCaches().add(dataRange);
    PivotTable pt = sheet.getPivotTables().add("Pivot Table", sheet.getCellRange("F3"), cache);

        // Add the fields to the row area.
        PivotField pf=null;
        if (pt.getPivotFields().get("Country") instanceof PivotField){
            pf= (PivotField) pt.getPivotFields().get("Country");
        }
        pf.setAxis(AxisTypes.Row);

        PivotField pf2 =null;
        if (pt.getPivotFields().get("Product") instanceof PivotField){
            pf2= (PivotField) pt.getPivotFields().get("Product");
        }
        pf2.setAxis(AxisTypes.Row);

        // Add the field to the data area.
        pt.getDataFields().add(pt.getPivotFields().get("Amount"), "SUM of Amount", SubtotalTypes.Sum);

        //Set PivotTable style
        pt.setBuiltInStyle(PivotBuiltInStyles.PivotStyleMedium12);

        //Save the document
        workbook.saveToFile("CreatePivotTable.xlsx", ExcelVersion.Version2013);
    }
}