Is there a way to create a pivot table in Excel using Apache POI?

Question

I am currently working on Excel Automation and add that I made good use of the Apache POI library.

Since I have so much data stored in my excel workbook in different columns, I am trying to create a pivot table.

Is there a way to create pivot tables using POI?

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

Answer 1

The Quick Start Guide is 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 cannot create charts at this time. However, you can create a chart in Excel, change the values ​​of the chart data using HSSF, and write a new table. This is possible because the POI is trying to keep existing records as intact as possible.

• Macros Macros cannot be created. However, reading and writing files containing macros will safely save macros.

• Pivot tables Creating pivot tables is not supported. It has been advised that files containing pivot tables can be read and written safely.