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
XLS - Pivot Table - Java - Apache POI - Example

Expected Output after Pivot via Java Program
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.



11 comments:

  1. I tried to add 4 column label(actually i intend to add 17) using

    pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 4);

    but after adding two column label, when I open excel file it gives me error like:
    Excel was able to open the file by repairing or removing the unreadable content.
    Removed Feature: PivotTable report from /xl/pivotTables/pivotTable1.xml part (PivotTable view)
    Removed Records: Workbook properties from /xl/workbook.xml part (Workbook)

    ReplyDelete
    Replies
    1. That's because the pivot table is not being created properly.. I think there's this limitation of apache poi.. I spent last whole week figuring out my problem.. Atlast I found out, apache POI fails to generate a pivot if a particular column (say Col-1) from pivot-data-sheet or pivot-data is used in more than one labels. That mean if the col-1 is used in RowLabel and again we want say count or Sum of col-1 as ColumnLabel then apache POI can't create the pivot.. I solved this problem with creating another column with same data as of col-1 (named that as col-q) and use that col-q in columnLabel while col-1 as RowLabel. And finally hide that col-q from the pivot-data-sheet or pivot-data..
      Thanks..

      Delete
    2. That's because the pivot table is not being created properly.. I think there's this limitation of apache poi.. I spent last whole week figuring out my problem.. Atlast I found out, apache POI fails to generate a pivot if a particular column (say Col-1) from pivot-data-sheet or pivot-data is used in more than one labels. That mean if the col-1 is used in RowLabel and again we want say count or Sum of col-1 as ColumnLabel then apache POI can't create the pivot.. I solved this problem with creating another column with same data as of col-1 (named that as col-q) and use that col-q in columnLabel while col-1 as RowLabel. And finally hide that col-q from the pivot-data-sheet or pivot-data..
      Thanks..

      Delete
  2. Is there a way to format the pivot table , like sorting the columns , disabling the subtotals ?

    ReplyDelete
  3. thanks, its working fine

    ReplyDelete
  4. Removed Feature: PivotTable report from /xl/pivotTables/pivotTable1.xml part (PivotTable view)
    Removed Records: Workbook properties from /xl/workbook.xml part (Workbook)----- I ma getting this error when I try to open my excel

    ReplyDelete
  5. i want to use "% of column total" which is present in values for field setting.. how to use? plz help

    ReplyDelete
  6. Kindly share the exact poi or poi-ooxml versions in which this one is working.. as I am getting the same error as others while opening the workbook..
    Removed Feature: PivotTable report from /xl/pivotTables/pivotTable1.xml part (PivotTable view)
    Removed Records: Workbook properties from /xl/workbook.xml part (Workbook)----

    ReplyDelete
  7. Could you please provide a example for getting area references and cell references dynamically...?i tried doing it using getLastRowNum but how to identify the column name...please help me

    ReplyDelete
  8. Could you help me in doing pivot with dynamic values with an example.?i tried using getLastRowNum() but how to identify column name.

    ReplyDelete