Sign in

How to Create Excel Pivot Table 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. In addition, PivotTable also has the ability to sort and filter data, and it can meet our daily business reporting requirements. This article will introduce how to create an Excel PivotTable with a free Java API — Free Spire.XLS for Java.

Installation (2 Method)
1# Download the free API and unzip it, then add the Spire.Xls.jar file to your project as dependency.

2# You can also 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("Sample.xlsx");

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

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

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store