Dheeraj Malik

Posted on Sep 24

How to Create Pivot Tables in Excel with Java

#java #excel #createpivottable

When working with Excel data, raw information is usually stored as detailed records. For example, a sales worksheet may contain fields such as date, region, product, quantity, and sales amount.

As the amount of data grows, analyzing the original table directly becomes less convenient. Excel Pivot Tables provide a simple way to summarize and analyze data from different perspectives, such as:

Calculating sales amounts by region;
Summarizing data by product category;
Comparing values across different dimensions.

If you need to generate analysis reports automatically in a Java application, you can create Pivot Tables programmatically instead of manually opening Excel and configuring them each time.

This article explains how to create Excel Pivot Tables with Java and configure fields for data summarization.

Preparing the Environment

Create a Java project and add the Excel processing dependency through Maven.

Add the following configuration to pom.xml:

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>

<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>latest-version</version>
    </dependency>
</dependencies>

Then import the required classes:

import com.spire.xls.*;

Prepare Excel Data

Before creating a Pivot Table, prepare the data source.

For example, a sales worksheet contains the following data:

Date	Region	Product	Sales Amount
2026-01-01	East	Phone	5000
2026-01-02	South	Laptop	8000
2026-01-03	East	Tablet	3000
2026-01-04	North	Phone	6000

In this worksheet:

The first row contains field names;
The remaining rows contain data records;
The range A1:D5 will be used as the Pivot Table data source.

The following example loads the Excel file and creates a Pivot Table based on the specified data range.

Create an Excel Pivot Table

Creating a Pivot Table usually involves the following steps:

Load the Excel file;
Get the data source range;
Create a PivotCache;
Add a PivotTable;
Apply formatting;
Save the result file.

The complete example is shown below:

import com.spire.xls.*;

public class CreatePivotTable {

    public static void main(String[] args) {

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

        // Load the Excel file containing source data
        workbook.loadFromFile("SalesData.xlsx");

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

        // Get the data source range
        CellRange dataRange = sheet.getCellRange("A1:D5");

        // Create a Pivot Cache from the data range
        PivotCache cache = workbook.getPivotCaches().add(dataRange);

        // Create a Pivot Table at the specified location
        PivotTable pivotTable = sheet.getPivotTables().add("SalesSummary", sheet.getCellRange("F3"), cache);

        // Apply a built-in Pivot Table style
        pivotTable.setBuiltInStyle(PivotBuiltInStyles.PivotStyleMedium10);

        // Save the result file
        workbook.saveToFile("CreatePivotTable.xlsx", ExcelVersion.Version2016);

        // Release resources
        workbook.dispose();
    }
}

After running the code, a Pivot Table named SalesSummary will be created at cell F3.

At this point, the Pivot Table structure is created, but fields still need to be configured.

Configure Pivot Table Fields

Pivot Table fields are mainly divided into two types:

Row Field: Used for grouping and displaying categories;
Data Field: Used for calculating summary values.

For example, to calculate sales amounts by region:

Set Region as the row field;
Add Sales Amount as the data field and calculate the total.

The following complete example opens the existing Pivot Table and configures the fields:

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

public class SetPivotFields {

    public static void main(String[] args) {

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

        // Load the workbook containing the Pivot Table
        workbook.loadFromFile("CreatePivotTable.xlsx");

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

        // Get the first Pivot Table
        PivotTable pivotTable = sheet.getPivotTables().get(0);

        // Get the Region field and set it as a row field
        IPivotField regionField = pivotTable.getPivotFields().get("Region");
        regionField.setAxis(AxisTypes.Row);

        // Add the Sales Amount field and calculate the sum
        pivotTable.getDataFields().add(pivotTable.getPivotFields().get("Sales Amount"), "Total Sales", SubtotalTypes.Sum);

        // Save the result
        workbook.saveToFile("ConfiguredPivotTable.xlsx", ExcelVersion.Version2016);

        // Release resources
        workbook.dispose();
    }
}

The generated Pivot Table will look similar to:

Region	Total Sales
East	8000
South	8000
North	6000

The Region field is used for grouping, while Sales Amount is used for calculation.

Add Multiple Data Fields

A Pivot Table can contain multiple summary fields.

For example, you may want to calculate both total sales amount and total sales quantity.

import com.spire.xls.*;

public class AddMultipleDataFields {

    public static void main(String[] args) {

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

        // Load the workbook containing the Pivot Table
        workbook.loadFromFile("CreatePivotTable.xlsx");

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

        // Get the Pivot Table
        PivotTable pivotTable = sheet.getPivotTables().get(0);

        // Add total sales amount
        pivotTable.getDataFields().add(pivotTable.getPivotFields().get("Sales Amount"), "Total Sales", SubtotalTypes.Sum);

        // Add total quantity
        pivotTable.getDataFields().add(pivotTable.getPivotFields().get("Quantity"), "Total Quantity", SubtotalTypes.Sum);

        // Save the file
        workbook.saveToFile("MultipleDataFields.xlsx", ExcelVersion.Version2016);

        // Release resources
        workbook.dispose();
    }
}

After adding multiple fields, the Pivot Table can display several metrics at the same time.

Save the Generated Excel File

After configuring the Pivot Table, save the result using saveToFile():

import com.spire.xls.*;

public class SavePivotTable {

    public static void main(String[] args) {

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

        // Load the Excel file
        workbook.loadFromFile("ConfiguredPivotTable.xlsx");

        // Save the final file
        workbook.saveToFile("SalesAnalysisReport.xlsx", ExcelVersion.Version2016);

        // Release resources
        workbook.dispose();
    }
}

The generated Excel file can be opened directly in Microsoft Excel for further filtering or analysis.

Common Considerations

The Data Source Should Include Headers

When creating a Pivot Table, the first row should contain field names.

For example:

Correct:

Region	Product	Sales Amount
East	Phone	5000

Avoid:

A	B	C
East	Phone	5000

Field names are required when configuring Pivot Table fields.

Field Names Must Match Exactly

When setting fields:

pivotTable.getPivotFields().get("Region")

The field name must exactly match the header in the Excel data source.

Otherwise, the corresponding field cannot be found correctly.

Processing Large Data Sets

When working with large amounts of data:

Write data in batches instead of modifying cells repeatedly;
Avoid unnecessary worksheet operations;
Release the Workbook object after processing.

Conclusion

Pivot Tables are useful for grouping, summarizing, and analyzing structured Excel data.

By creating Pivot Tables with Java, developers can automate Excel report generation. After preparing the data source, you can configure fields and calculation methods programmatically to generate analysis-ready Excel files.

This approach is suitable for scenarios such as automated report generation and exporting Excel files with summarized data.

DEV Community