Can not create a pivot table with apache poi 4.0.1

Question

We are trying to produce a pivot table based on .xlsx file which we create programmatically.

      FileInputStream input_document = new FileInputStream(new File("testme.xlsx"));

    XSSFWorkbook wb = new XSSFWorkbook(input_document);
    XSSFSheet pivotSheet = wb.createSheet("Pivot sheet");


    //create pivot table
     XSSFPivotTable pivotTable = pivotSheet.createPivotTable(
            new AreaReference(new CellReference("\'Selected Messages\'!A3"), new CellReference("\'Selected Messages\'!T4620"), //make the reference big enough for later data
                    SpreadsheetVersion.EXCEL2007),
            new CellReference("\'Pivot sheet\'!C5"), wb.getSheet("Selected Messages"));
    //Configure the pivot table
    //Use first column as row label
    pivotTable.addRowLabel(0);

    pivotTable.addRowLabel(2);

    pivotTable.addColumnLabel(DataConsolidateFunction.COUNT, 5, "Number of messages");

    pivotTable.addColLabel(4);

    pivotTable.addReportFilter(11);



    wb.write(new FileOutputStream("SXSSFPivotTableTest.xlsx"));
    wb.close();

Here is our code sample we use. The testme.xlsx is the produced file by us and it contains a lot of data.The data are in the Selected Message sheet. We want to create a pivot table from those data, in a new sheet in the same file and then create a new file which will contain all the sheets.

Our problem is that after the creation when we try to open the new file Excel tries to recover it but it removes the pivot table and all the .xml file which are responsible for it. The error message we get is shown below :

Removed Feature: PivotTable report from /xl/pivotCache/pivotCacheDefinition1.xml part (PivotTable cache) Removed Feature: PivotTable report from /xl/pivotTables/pivotTable1.xml part (PivotTable view) Removed Records: Workbook properties from /xl/workbook.xml part (Workbook)

Does anyone have the same problem in any previous version or in the latest? Any solution that may help us overcome our problem?

NOTE The produced .xlsx can be opened with LibreOffice. The headers are Type,MRN or Correl ID,From,Sent To,Received,CoA,CoD,Exp,Exc,Size,Type Error,Pointer,Reason,Original Value,Action by recipient,Interchange Error code,Rejected Msg,Action by recipient2,Error code

Cannot reproduce. But are you sure 'Selected Messages'!A3:T3 contains the headings of the table? Those strings are the pivot table field names then and must be unique. Can you at least post the content of 'Selected Messages'!A3:T3? — Axel Richter
Also sure 100% they are all unique? Please post the content of 'Selected Messages'!A3:T3 in your question. — Axel Richter
Btw.: You can revert the code edit CellReference("\'Selected Messages\'!A3").... It is not necessary. Before my first now deleted comment, I had not seen that you are using the additional sheet parameter in createPivotTable. — Axel Richter
But nevertheless, exact the error you mentioned is reproducible when the headers are not unique. Pivot field names must be unique. — Axel Richter

Σωτήρης Ραφαήλ Σωτήρης Ραφαήλ · Accepted Answer · 2019-03-01T14:50:32

I found a work around for this. We created a CTTable ,which is similar to format as table button in Excel and then the pivot table was created. Below is the example. The produced file were given to the code posted above and the final .xlsx file is produced.

    FileInputStream input_document = new FileInputStream(new File("testme.xlsx"));
    XSSFWorkbook my_xlsx_workbook = new XSSFWorkbook(input_document);
    XSSFSheet sheet = my_xlsx_workbook.getSheetAt(0);
    XSSFTable my_table = sheet.createTable();

    CTTable cttable = my_table.getCTTable();
    CTTableStyleInfo table_style = cttable.addNewTableStyleInfo();
    table_style.setName("TableStyleMedium9");
    table_style.setShowColumnStripes(true);
    table_style.setShowRowStripes(true);
    AreaReference my_data_range = new AreaReference(new CellReference(9, 0), new CellReference(18, 19), SpreadsheetVersion.EXCEL2007);
    cttable.setRef(my_data_range.formatAsString());
    cttable.setDisplayName("MYTABLE");      /* this is the display name of the table */
    cttable.setName("Test");    /* This maps to "displayName" attribute in &lt;table&gt;, OOXML */
    cttable.setId(1L); //id attribute against table as long value
    for(int x = my_xlsx_workbook.getSheetAt(0).getRow(2).getRowNum();x < my_xlsx_workbook.getSheetAt(0).getLastRowNum(); x++) {
        //add columns for each row
        CTTableColumns columns = cttable.addNewTableColumns();
        //define number of columns for each row
        columns.setCount(my_xlsx_workbook.getSheetAt(0).getRow(x).getLastCellNum());
        //loop the columns to add value and id
        for (int i = 0; i < my_xlsx_workbook.getSheetAt(0).getRow(x).getLastCellNum(); i++) {
            CTTableColumn column = columns.addNewTableColumn();
            column.setName(my_xlsx_workbook.getSheetAt(0).getRow(x).getCell(i).getStringCellValue());
            column.setId(my_xlsx_workbook.getSheetAt(0).getRow(x).getCell(i).getColumnIndex() + i);
        }
        //add each row into the table
        cttable.setTableColumns(columns);
    }
    sheet.setAutoFilter(new CellRangeAddress(2,2,0,19));

    /* Write output as File */
    FileOutputStream fileOut = new FileOutputStream("Excel_Format_As_Table.xlsx");
    my_xlsx_workbook.write(fileOut);
    fileOut.close();
}

Can not create a pivot table with apache poi 4.0.1

1 Answers