Is there any way to create a Pivot Table in Excel using Apache POI?

Question

I am currently working on the automation of Excel, and add such I have made a good use of the Apache POI library.

As I have so much data stored in my excel workbook in various columns, that I'm trying to create a pivot table.

Is there any way to create Pivot tables using POI ?

My requirement is that I need to create the pivot table in a new excel workbook or in the same workbook where I store my data.

Answer 1

The 'Quick Guide' is quite out of date.

The change log refers to this bugzilla issue as resolved.

You can see the code here:

Here is a snippet:

 public static void main(String[] args) throws FileNotFoundException, IOException, InvalidFormatException {
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = (XSSFSheet) wb.createSheet();

        //Create some data to build the pivot table on
        setCellData(sheet);

        XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:D4"), new CellReference("H5"));
        //Configure the pivot table
        //Use first column as row label
        pivotTable.addRowLabel(0);
        //Sum up the second column
        pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 1);
        //Set the third column as filter
        pivotTable.addColumnLabel(DataConsolidateFunction.AVERAGE, 2);
        //Add filter on forth column
        pivotTable.addReportFilter(3);

        FileOutputStream fileOut = new FileOutputStream("ooxml-pivottable.xlsx");
        wb.write(fileOut);
        fileOut.close();
    }

Answer 2

No you cant.refer here

• Charts You can not currently create charts. You can however create a chart in Excel, modify the chart data values using HSSF and write a new spreadsheet out. This is possible because POI attempts to keep existing records intact as far as possible.

• Macros Macros can not be created. However, reading and re-writing files containing macros will safely preserve the macros.

• Pivot Tables Generating pivot tables is not supported. It has been reported that files containing pivot tables can be read and re-written safely.

Answer 3

yes, you can create. Dependency required

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.15</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>ooxml-schemas</artifactId>
            <version>1.3</version>
        </dependency>

Input Excel file

Java code to create pivot table on same sheet

import java.io.File;
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 Test {


    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("input-file-path\\Pivot-Cube.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:C5");
        /* Find out where the Pivot Table needs to be placed */
        CellReference b=new CellReference("I5");
       

        /* Create Pivot Table */
        XSSFPivotTable pivotTable = sheet.createPivotTable(a,b, sheet);
        /* 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("output-file-path\\POI_XLS_Pivot_Example.xlsx"));
        my_xlsx_workbook.write(output_file);
        input_document.close();
    }


}