Create Pivot Table - Java Apache POI Example

In out last post, we discussed a basic example of creating pivot tables in Excel workbook with Apache POI. We will aim to extend that example in this post which will explain how to create pivot tables on existing workbooks and in a new worksheet. Download a latest copy of POI and follow the code below;

Prepare Pivot Table Input:

The input data that we will use and the expected output from the program are provided below:

XLS - Pivot Table - Java - Apache POI - Example

Expected Output after Pivot via Java Program

Create Pivot Table in POI

The program to create Pivot table on existing workbooks using Apache POI is provided below:

import java.io.FileOutputStream;
import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.xssf.usermodel.*;
public class readxlsx {  
        public static void main(String[] args) throws Exception{
                /* Read the input file that contains the data to pivot */
                FileInputStream input_document = new FileInputStream(new File("inputFile.xlsx"));    
                /* Create a POI XSSFWorkbook Object from the input file */
                XSSFWorkbook my_xlsx_workbook = new XSSFWorkbook(input_document); 
                /* Read Data to be Pivoted - we have only one worksheet */
                XSSFSheet sheet = my_xlsx_workbook.getSheetAt(0); 
                /* Get the reference for Pivot Data */
                AreaReference a=new AreaReference("A1:C51");
                /* Find out where the Pivot Table needs to be placed */
                CellReference b=new CellReference("I5");
                /* Create Pivot Table */
                XSSFPivotTable pivotTable = sheet.createPivotTable(a,b);
                /* Add filters */
                pivotTable.addReportFilter(0);
                pivotTable.addRowLabel(1);
                pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 2); 
                /* Write Pivot Table to File */
                FileOutputStream output_file = new FileOutputStream(new File("POI_XLS_Pivot_Example.xlsx")); 
                my_xlsx_workbook.write(output_file);
                input_document.close(); 
        }
}

The Code is commented for reference. There are some smart changes that you can perform to the code above to refine the way pivot tables work - these are provided below.

Create Pivot Table on New WorkSheet

In some scenarios, you may have a need to create Pivot tables on new worksheet. This can be easily achieved through the following changes to the code above: (only the changes are provided)

                /* Add a new sheet to create Pivot Table */
                XSSFSheet pivot_sheet=my_xlsx_workbook.createSheet();

                /* Create Pivot Table on a separate worksheet */
                XSSFPivotTable pivotTable = pivot_sheet.createPivotTable(a,b,sheet);

You have to pass the sheet reference in createPivotTable method, else you will get a runtime error similar to the below:

Exception in thread "main" java.lang.NullPointerException
        at org.apache.poi.xssf.usermodel.XSSFPivotCacheDefinition.createCacheFields(XSSFPivotCacheDefinition.java:134)
        at org.apache.poi.xssf.usermodel.XSSFSheet.createPivotTable(XSSFSheet.java:3656)
        at org.apache.poi.xssf.usermodel.XSSFSheet.createPivotTable(XSSFSheet.java:3673)
        at readxlsx.main(readxlsx.java:22)

You can also calculate the AreaReference dynamically if you do not know the number of rows and columns before hand. There are methods available in XSSFSheet like getLastRowNum, that would do the job for you. Give a try and let us know if you need any assistance.