Create Pivot Table/ Pivot Chart in Excel using Java

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. This article will introduce how to create Excel Pivot Table and Pivot Chart with a free Java API.

Installation
Method 1: Download the free API (Free Spire.XLS for Java) and unzip it, then add the Spire.Xls.jar file to your project as dependency.

Method 2: Directly add the jar dependency to maven project 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>3.9.1</version>
    </dependency>
</dependencies>

Create Pivot Table

import com.spire.xls.*;

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

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

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

        // Add a PivotTable to the worksheet
        CellRange dataRange = sheet.getCellRange("A1:D7");
        PivotCache cache = workbook.getPivotCaches().add(dataRange);
        PivotTable pt = sheet.getPivotTables().add("Pivot Table", sheet.getCellRange("F2"), 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("data1.xlsx", ExcelVersion.Version2013);
    }
}

Create Pivot Chart

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

public class CreatePivotChart {
    public static void main(String[] args) {
        //Load the Excel file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("data.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);
        //get the first pivot table in the worksheet
        IPivotTable pivotTable = sheet.getPivotTables().get(0);

        //Add a clustered column chart based on the pivot table data
        Chart chart = sheet.getCharts().add(ExcelChartType.ColumnClustered, pivotTable);
        //Set chart position
        chart.setTopRow(14);
        chart.setBottomRow(25);
        //Set chart title
        chart.setChartTitle("Total");

        //Save the result file
        workbook.saveToFile("CreatPivotChart.xlsx", ExcelVersion.Version2013);
    }
}