Bug 60094

Summary: Excel file unreadable due to XSSFPivotTable.addReportFilter
Product: POI Reporter: Gabriel Jones <datasurfer>
Component: XSSFAssignee: POI Developers List <dev>
Status: RESOLVED WORKSFORME    
Severity: normal    
Priority: P2    
Version: 3.15-dev   
Target Milestone: ---   
Hardware: PC   
OS: All   
Attachments: reportFilter.xlsx
reportFilter.xlsx created by hand in Excel 2013
Diff of Apache POI-generated and Excel created reportFilter.xlsx

Description Gabriel Jones 2016-09-07 20:06:29 UTC
package gabrieljones.poi;

import org.apache.poi.ss.usermodel.DataConsolidateFunction;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFPivotTable;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;

public class Main {

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

        XSSFRow row1 = sheet.createRow(0);
        row1.createCell(0).setCellValue("Names");
        row1.createCell(1).setCellValue("#");
        row1.createCell(2).setCellValue("Data");
        row1.createCell(3).setCellValue("Value");
        XSSFRow row2 = sheet.createRow(1);
        row2.createCell(0).setCellValue("Jan");
        row2.createCell(1).setCellValue(10);
        row2.createCell(2).setCellValue("Apa");
        row2.createCell(3).setCellValue(11.11);
        XSSFRow row3 = sheet.createRow(2);
        row3.createCell(0).setCellValue("Ben");
        row3.createCell(1).setCellValue(9);
        row3.createCell(2).setCellValue("Bepa");
        row3.createCell(3).setCellValue(12.12);

        XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:D3"), new CellReference(0,5));

        pivotTable.addRowLabel(2);
        pivotTable.addColumnLabel(DataConsolidateFunction.COUNT, 1);
        pivotTable.addReportFilter(0); //This line renders the resulting xlsx file unreadable

        wb.write(new FileOutputStream("reportFilter.xlsx"));
    }
}
Comment 1 Gabriel Jones 2016-09-07 20:31:06 UTC
I also wrote out the workbook generated in the following test case.

https://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFPivotTable.java?view=markup#l293

It was also unreadable to Excel 2010
Comment 2 Javen O'Neal 2016-09-14 22:59:49 UTC
Created attachment 34251 [details]
reportFilter.xlsx

LibreOffice 4.0.4.2 does not report any problems with the generated file.

However, Excel 2013 reported:
"We found a problem with some content in 'reportFilter.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes."

"There is not enough memory available to create the PivotTable report. To make more memory available, try the following:
* Reduce the number of column or row fields you include in the PivotTable report.
* Close files or programs you aren't using."

"Repairs to 'reportFilter.xlsx'"
Excel was able to open the file by repairing or removing the unreadable content.
Removed Records: Style from /xl/workbook.xml part (Workbook)"

The contents of workbook.xml are below:
<workbook xmlns="...">
  <workbookPr date1904="false"/>
  <bookViews>
    <workbookView activeTab="0"/>
  </bookViews>
  <sheets>
    <sheet name="Sheet0" r:id="rId3" sheetId="1"/>
  </sheets>
  <pivotCaches>
    <pivotCache cacheId="2" r:id="rId4"/>
  </pivotCaches>
</workbook>

There may also be something wrong with /xl/pivotTables/pivotTable1.xml
Comment 3 Javen O'Neal 2016-09-15 20:56:47 UTC
Created attachment 34256 [details]
reportFilter.xlsx created by hand in Excel 2013
Comment 4 Javen O'Neal 2016-09-15 21:29:28 UTC
Created attachment 34257 [details]
Diff of Apache POI-generated and Excel created reportFilter.xlsx
Comment 5 Dominik Stadler 2018-04-20 07:43:10 UTC
This works for me with trunk and a fairly recent version of Excel, there were a number of changes to PivotTables, so I believe this is fixed in the meantime in our latest version. 

You can verify this with packages downloaded from https://builds.apache.org/view/P/view/POI/job/POI-DSL-1.8/, please reopen or create a new ticket if there are still issues remaining.