有没有办法用Apache POI在Excel中创建一个Pivot表?

I am currently working on the automation of Excel, and add such I have made a good use of the Apache POI library.

我目前正在开发Excel的自动化，并添加了Apache POI库。

As I have so much data stored in my excel workbook in various columns, that I'm trying to create a pivot table.

由于我的excel工作簿中存储了大量不同列的数据，所以我试图创建一个pivot表。

Is there any way to create Pivot tables using POI ?

是否存在使用POI创建Pivot表的方法?

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.

我的要求是，我需要在新的excel工作簿或存储数据的同一个工作簿中创建pivot表。

2 个解决方案

#1

The 'Quick Guide' is quite out of date.

“快速指南”已经过时了。

The change log refers to this bugzilla issue as resolved.

更改日志指的是已解决的bugzilla问题。

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

#2

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.

•当前无法创建图表的图表。但是，您可以在Excel中创建一个图表，使用HSSF修改图表数据值，并编写一个新的电子表格。这是可能的，因为POI试图尽可能保持现有记录不变。

• 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.

不支持Pivot表生成数据透视表。据报道，包含pivot表的文件可以安全地读取和重写。

#1