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.
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();
}
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.
