[apache poi xssf]:creating pivot table in new sheet(Java)

Question

I've modified the basic example to create a pivot table in a new worksheet. But upon opening the new xlsx file, I get an error (Excel found unreadable content in...followed by:

Removed Part: /xl/pivotTables/pivotTable1.xml part with XML error.  (PivotTable view) Load error. Line 2, column 561.
Removed Records: Workbook properties from /xl/workbook.xml part (Workbook)

)

Here's the code snippet I modified:

XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("plain");

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

XSSFSheet sheet2 = wb.createSheet("pivot");

XSSFPivotTable pivotTable = sheet2.createPivotTable(new AreaReference("plain!$A$1:$D$4", null), new CellReference("pivot!$A$1"));
//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);

I've debugged through the code, and don't see an apparent issue...

Thoughts on how this can be handled? or if this is a bug with the library?

Thanks

EDIT

The problem is with starting from cell reference A1 (new CellReference("pivot!$A$1")) in the above code. It seems, if we start from A1, there is not enough space left on the sheet to do some other formatting pivot grid does. So changing that to A5 does the work. Though I still think POI should explicitly prevent people from doing this by throwing an error

Which version of POI is this? And if not the latest one, can you try upgrading to the latest version 3.15-beta1 to check if it still happens there? — centic
This sounds like a bug in POI, I think the best option would be to report a bug at bz.apache.org/bugzilla/enter_bug.cgi?product=POI — centic
I will try the new version...though hesitant to use it in production env as its still "beta" — labheshr

Chris Hagn Chris Hagn · Accepted Answer · 2016-12-02T13:40:12

I had this issue in POI verion 3.14 and 3.16 Beta, but I found when I called the method createPivotTable with the source table reference as the third option, this worked in those versions.

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

    AreaReference source = new AreaReference("A1:D4", SpreadsheetVersion.EXCEL2007);
    XSSFSheet sheet2 = wb.createSheet("pivot");

    CellReference position = new CellReference("A3"); //convertColStringToIndex

    XSSFPivotTable pivotTable = sheet2.createPivotTable(source, position, sheet);

This ran in POI 3.16 Beta and 3.14.
I also had the same issue with CellReference at A1 and yes, I agree that it should produce some warning/error.

[apache poi xssf]:creating pivot table in new sheet(Java)

1 Answers