无法使用 apache poi 4.0.1 创建数据透视表

Question

我们正在尝试基于我们以编程方式创建的 .xlsx 文件生成数据透视表。

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

这是我们使用的代码示例。testme.xlsx是我们制作的文件，里面有很多数据。数据在Selected Messagesheet里面。我们想从这些数据创建一个数据透视表，在同一个文件的一个新工作表中，然后创建一个包含所有工作表的新文件。

我们的问题是，在创建后，当我们尝试打开新文件时，Excel 会尝试恢复它，但它会删除数据透视表和所有负责它的 .xml 文件。我们得到的错误信息如下所示：

已删除功能：来自 /xl/pivotCache/pivotCacheDefinition1.xml 部分（数据透视表缓存）的数据透视表报告 已删除功能：来自 /xl/pivotTables/pivotTable1.xml 部分（数据透视表视图）的数据透视表报告 已删除记录：来自 /xl/workbook.xml 的工作簿属性部分（工作簿）

在任何以前的版本或最新版本中是否有人有同样的问题？任何解决方案可以帮助我们克服我们的问题？

注意生成的 .xlsx 可以用 LibreOffice 打开。标题是 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

Answer 1

我找到了解决这个问题的方法。我们创建了一个 CTTable ，类似于 Excel 中的表格格式按钮，然后创建了数据透视表。下面是示例。将生成的文件提供给上面发布的代码，并生成最终的 .xlsx 文件。&nbsp; &nbsp; FileInputStream input_document = new FileInputStream(new File("testme.xlsx"));&nbsp; &nbsp; XSSFWorkbook my_xlsx_workbook = new XSSFWorkbook(input_document);&nbsp; &nbsp; XSSFSheet sheet = my_xlsx_workbook.getSheetAt(0);&nbsp; &nbsp; XSSFTable my_table = sheet.createTable();&nbsp; &nbsp; CTTable cttable = my_table.getCTTable();&nbsp; &nbsp; CTTableStyleInfo table_style = cttable.addNewTableStyleInfo();&nbsp; &nbsp; table_style.setName("TableStyleMedium9");&nbsp; &nbsp; table_style.setShowColumnStripes(true);&nbsp; &nbsp; table_style.setShowRowStripes(true);&nbsp; &nbsp; AreaReference my_data_range = new AreaReference(new CellReference(9, 0), new CellReference(18, 19), SpreadsheetVersion.EXCEL2007);&nbsp; &nbsp; cttable.setRef(my_data_range.formatAsString());&nbsp; &nbsp; cttable.setDisplayName("MYTABLE");&nbsp; &nbsp; &nbsp; /* this is the display name of the table */&nbsp; &nbsp; cttable.setName("Test");&nbsp; &nbsp; /* This maps to "displayName" attribute in &lt;table&gt;, OOXML */&nbsp; &nbsp; cttable.setId(1L); //id attribute against table as long value&nbsp; &nbsp; for(int x = my_xlsx_workbook.getSheetAt(0).getRow(2).getRowNum();x < my_xlsx_workbook.getSheetAt(0).getLastRowNum(); x++) {&nbsp; &nbsp; &nbsp; &nbsp; //add columns for each row&nbsp; &nbsp; &nbsp; &nbsp; CTTableColumns columns = cttable.addNewTableColumns();&nbsp; &nbsp; &nbsp; &nbsp; //define number of columns for each row&nbsp; &nbsp; &nbsp; &nbsp; columns.setCount(my_xlsx_workbook.getSheetAt(0).getRow(x).getLastCellNum());&nbsp; &nbsp; &nbsp; &nbsp; //loop the columns to add value and id&nbsp; &nbsp; &nbsp; &nbsp; for (int i = 0; i < my_xlsx_workbook.getSheetAt(0).getRow(x).getLastCellNum(); i++) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CTTableColumn column = columns.addNewTableColumn();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; column.setName(my_xlsx_workbook.getSheetAt(0).getRow(x).getCell(i).getStringCellValue());&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; column.setId(my_xlsx_workbook.getSheetAt(0).getRow(x).getCell(i).getColumnIndex() + i);&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; //add each row into the table&nbsp; &nbsp; &nbsp; &nbsp; cttable.setTableColumns(columns);&nbsp; &nbsp; }&nbsp; &nbsp; sheet.setAutoFilter(new CellRangeAddress(2,2,0,19));&nbsp; &nbsp; /* Write output as File */&nbsp; &nbsp; FileOutputStream fileOut = new FileOutputStream("Excel_Format_As_Table.xlsx");&nbsp; &nbsp; my_xlsx_workbook.write(fileOut);&nbsp; &nbsp; fileOut.close();}