Java: Create a Pivot Table in Excel

Pivot table is a powerful tool in Excel that supports categorizing, sorting, filtering, and summarizing data. It is often used in situations where data needs to be aggregated and analyzed for reporting. This article will demonstrate how to programmatically create a pivot table in Excel using Spire.XLS for Java.

Install Spire.XLS for Java

First, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.


Create a Pivot Table in Excel

The detailed steps are as follows.

  • 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.getCellRange() method, and then create a PivotCache to save the data information using Workbook.getPivotCaches().add(CellRange range) method.
  • Add a pivot table to the specified worksheet and set the location and cache of it using Worksheet.getPivotTables().add(java.lang.String name, CellRange location, PivotCache cache) method.
  • Define row labels of the pivot table and then add fields to the data area to calculate data using PivotTable.getDataFields().add(IPivotField iField, java.lang.String name, SubtotalTypes subtotal) method.
  • Set the pivot table style using PivotTable.setBuiltInStyle(PivotBuiltInStyles builtInStyle) method.
  • 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

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

        //Select the data source range
        CellRange dataRange = sheet.getCellRange("B1: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.getCellRange("H3"), cache);

        //Define row labels
        PivotField pf=null;
        if (pt.getPivotFields().get("Country") instanceof PivotField){
            pf= (PivotField) pt.getPivotFields().get("Country");

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

        //Add data fields to calculate data
        pt.getDataFields().add(pt.getPivotFields().get("Quantity"), "SUM of Quantity", SubtotalTypes.Sum);
        pt.getDataFields().add(pt.getPivotFields().get("Total Amount"), "SUM of Total Amount", SubtotalTypes.Sum);

        //Set pivot table style

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

Java: Create a Pivot Table in Excel

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.