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.

Step by Step Implementation

Step 1: First of all, need to add needed dependencies into the maven project for including the Free Spire.XLS project before starting writing the code.

Step 2: Create a workbook 

Workbook workbook = new Workbook();

Step 3: Create a sheet 

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

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

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

Step 5: 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 6: 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 7: Create pivot field to configure pivot table’s fields after that set layout of pivot table by using setAxis 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 8: Drag the field to the data area

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

Step 9: Set PivotTable style

pivotTable.setBuiltInStyle(PivotBuiltInStyles.PivotStyleMedium12);

Step 10: Calculate data

pivotTable.calculateData();

Step 11: 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 12: Save workbook by using saveToFile(String fileName, ExcelVersion version)

workbook.saveToFile(workbookName, ExcelVersion.Version2013);

Let’s write Java Program to create a pivot table in a spreadsheet.

Output: On the console window

When the program is successfully executed.

Geeks_For_Geeks.xlsx is written successfully

Output: Workbook(excel file)