How to Create Excel Pivot Table using Java

<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>
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);
}
}

--

--

--

Sharing Java Code

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Python — Calculation & Comparison Operators

Building a Conversational Chatbot for Slack using Rasa and Python -Part 2

Google Data Engineering- Creating a Data Transformation Pipeline with Cloud Dataprep Quest.

[LeetCode]#700. Search in a Binary Search Tree

An Introduction to Microservice Based Architecture Through Story — Part 1

URL shortener System design

Vault K8S Cluster — With TLS Certificate

Plan for Technical Debt

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
Andrew Wilson

Andrew Wilson

Sharing Java Code

More from Medium

How To Read Emails Using Java Imaps? — Devstringx

Java Imaps

File Path separator in Java

Java DateTime API

Spring Boot : Simplest ways to use properties files from custom location different from resource…