How to Create Pivot Table in Excel using Java?

Last Updated : 06 Dec, 2023

A pivot table is needed to quickly analyze data of a table with very little effort (and no formulas) and sometimes not everyone has time to look at the data in the table and see what’s going on and use it to build good-looking reports for large data sets in an Excel worksheet. Let's discuss a step-by-step proper explanation to create a pivot table in an Excel file in Java using Free Spire.XLS for Java API.

Requirements to Create Pivot Table in Excel using Java

Firstly, we need to add necessary dependencies to the Maven project including the Free Spire.XLS project before starting to write the code.

How to Install Spire.XLS for Java

By adding the following code to your project's pom.xml file, you can quickly import the JAR file into your program if you use Maven.

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>javax.xml.bind</groupId>
    <artifactId>jaxb-api</artifactId>
    <version>2.3.1</version> </dependency>
  <dependency>  
    <groupId>e-iceblue</groupId>  
    <artifactId>spire.xls.free</artifactId> 
    <version>3.9.1</version>
  </dependency>
</dependencies>

How to Create a Pivot Table in Excel using Java

Here are the steps for using Java to make a pivot table in Excel.

Step 1: Create a workbook

Workbook workbook = new Workbook();

Step 2: Create a sheet

Worksheet sheet = workbook.getWorksheets().get(0);

Step 3: Add some data to the worksheet in table form.

sheet.getCellRange("A1").setValue("Student Name");

Step 4: Pivot Cache is something that automatically gets generated when you create a Pivot Table. Create an object of PivotCache and add a range.

PivotCache cache = workbook.getPivotCaches().add(dataRange);

Step 5: Create an object of PivotTable to get PivotTable and add table name, Cell Range from where a table is starting to arg1, PivotCache arg2

PivotTable pivotTable = sheet.getPivotTables().add("Pivot Table", sheet.getCellRange("A16"), cache);

Step 6: Create a pivot field to configure the pivot table's fields after that set layout of the pivot table by using the set Axis method and then select type.

PivotField pivotField1 = null;

if (pivotTable.getPivotFields().get("Student Name") instanceof PivotField) {

pivotField1 = (PivotField) pivotTable.getPivotFields().get("Student Name");

}

pivotField1.setAxis(AxisTypes.Row);

Step 7: Drag the field to the data area

pivotTable.getDataFields().add(pivotTable.getPivotFields().get("Attendance"), "SUM of Attendance", SubtotalTypes.Sum);

Step 8: Set PivotTable style

pivotTable.setBuiltInStyle(PivotBuiltInStyles.PivotStyleMedium12);

Step 9: Calculate the data

pivotTable.calculateData();

Step 10: Set column width with setColumnWidth method having two parameters, first is columnIndex in int datatype, width size in double datatype.

sheet.setColumnWidth(1, 14);

sheet.setColumnWidth(2, 14);

Step 11: Save workbook by using saveToFile(String fileName, ExcelVersion version)

workbook.saveToFile(workbookName, ExcelVersion.Version2013);

How to write a Java Program to create a pivot table in a spreadsheet

Java

import com.spire.xls.AxisTypes;
import com.spire.xls.CellRange;
import com.spire.xls.ExcelVersion;
import com.spire.xls.PivotBuiltInStyles;
import com.spire.xls.PivotCache;
import com.spire.xls.PivotField;
import com.spire.xls.PivotTable;
import com.spire.xls.SubtotalTypes;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

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

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

        // Create a sheet
        Worksheet sheet = workbook.getWorksheets().get(0);
        // Add data to the worksheet in table form

        // Header
        sheet.getCellRange("A1").setValue("Student Name");
        sheet.getCellRange("B1").setValue("Month");
        sheet.getCellRange("C1").setValue("Attendance");

        // Data
        sheet.getCellRange("A2").setValue("Harry");
        sheet.getCellRange("A3").setValue("Harry");
        sheet.getCellRange("A4").setValue("Harry");
        sheet.getCellRange("A5").setValue("Nicole");
        sheet.getCellRange("A6").setValue("Nicole");
        sheet.getCellRange("A7").setValue("Nicole");
        sheet.getCellRange("A8").setValue("Peter");
        sheet.getCellRange("A9").setValue("Peter");
        sheet.getCellRange("A10").setValue("Peter");
        sheet.getCellRange("A11").setValue("Lisa");
        sheet.getCellRange("A12").setValue("Lisa");
        sheet.getCellRange("A13").setValue("Lisa");

        sheet.getCellRange("B2").setValue("January");
        sheet.getCellRange("B3").setValue("February");
        sheet.getCellRange("B4").setValue("March");
        sheet.getCellRange("B5").setValue("January");
        sheet.getCellRange("B6").setValue("February");
        sheet.getCellRange("B7").setValue("March");
        sheet.getCellRange("B8").setValue("January");
        sheet.getCellRange("B9").setValue("February");
        sheet.getCellRange("B10").setValue("March");
        sheet.getCellRange("B11").setValue("January");
        sheet.getCellRange("B12").setValue("February");
        sheet.getCellRange("B13").setValue("March");

        sheet.getCellRange("C2").setValue("25");
        sheet.getCellRange("C3").setValue("22");
        sheet.getCellRange("C4").setValue("24");
        sheet.getCellRange("C5").setValue("24");
        sheet.getCellRange("C6").setValue("23");
        sheet.getCellRange("C7").setValue("24");
        sheet.getCellRange("C8").setValue("22");
        sheet.getCellRange("C9").setValue("15");
        sheet.getCellRange("C10").setValue("23");
        sheet.getCellRange("C11").setValue("25");
        sheet.getCellRange("C12").setValue("20");
        sheet.getCellRange("C13").setValue("18");

        // Add a PivotTable to the worksheet
        // Get Range of Table
        CellRange dataRange = sheet.getCellRange("A1:C13");

        PivotCache cache
            = workbook.getPivotCaches().add(dataRange);
        PivotTable pivotTable = sheet.getPivotTables().add(
            "Pivot Table", sheet.getCellRange("A16"),
            cache);

        // Drag the fields to the row area
        PivotField pivotField1 = null;
        if (pivotTable.getPivotFields().get("Student Name")
                instanceof PivotField) {
            pivotField1
                = (PivotField)pivotTable.getPivotFields()
                      .get("Student Name");
        }
        pivotField1.setAxis(AxisTypes.Row);

        PivotField pivotField2 = null;
        if (pivotTable.getPivotFields().get("Month")
                instanceof PivotField) {
            pivotField2
                = (PivotField)pivotTable.getPivotFields()
                      .get("Month");
        }
        pivotField2.setAxis(AxisTypes.Row);

        // Drag the field to the data area
        pivotTable.getDataFields().add(
            pivotTable.getPivotFields().get("Attendance"),
            "SUM of Attendance", SubtotalTypes.Sum);

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

        // Calculate data
        pivotTable.calculateData();
      
        // Set column width
        sheet.setColumnWidth(1, 14);
        sheet.setColumnWidth(2, 14);

        // Save the result file
        String workbookName = "Geeks_For_Geeks.xlsx";
        workbook.saveToFile(workbookName,
                            ExcelVersion.Version2013);
        System.out.println(workbookName
                           + " is written successfully");
    }
}

Output

On the console window

When the program is successfully executed.

Geeks_For_Geeks.xlsx is written successfully