How to Create Pivot Table in Excel using Java?

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

Output

Workbook (Excel file)

FAQs on How to Create Pivot Table in Excel using Java?

Q1: What is a Pivot Table in Excel?

Answer:

A pivot table is one of the most useful features in Excel. They are used to summarize or aggregate lots of data. The summarization of the data can be in the form of average, count, and other statistical methods. It converts a lot of data into a few rows and columns of summary.

Q2: How to define the data range for the Pivot Table?

Answer:

Specify the cell range containing the data you want to include in the Pivot Table. For example, if your data is in cells A1 to C50, you can define the range as “A1:C50” in Excel.

Q3: How to create a Pivot Table in Excel using Java?

Answer:

You can use the Apache POI library to create a Pivot table in Excel using Java. Apache POI library provides support for working with Excel Files. Follow the below steps:

Step 1: Set up your Java project and include the Apache POI library as a dependency.

Step 2: Load the Excel workbook that contains the data you want to use for the Pivot Table.

Step 3: Define the range of the cells you want to include in the Pivot Table.

Step 4: Create a new sheet in the workbook where you want the Pivot table to be located.

Step 5: Use the Apache POI API to create and configure the Pivot table based on the selected data range.

Step 6: Save the modified workbook.