Java 有没有办法使用 Apache POI 在 Excel 中创建数据透视表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23885116/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there any way to create a Pivot Table in Excel using Apache POI?
提问by SabareeshSS
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 工作簿的各个列中存储了大量数据，因此我正在尝试创建一个数据透视表。
Is there any way to create Pivot tables using POI ?
有没有办法使用 POI 创建数据透视表？
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 工作簿或存储数据的同一工作簿中创建数据透视表。
采纳答案by Pureferret
The 'Quick Guide' is quite out of date.
“快速指南”已经过时了。
The change logrefers to this bugzilla issueas 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();
}
回答by Hirak
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.
? 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.
? 图表 您目前无法创建图表。但是，您可以在 Excel 中创建图表，使用 HSSF 修改图表数据值并编写新的电子表格。这是可能的，因为 POI 会尽可能地保持现有记录的完整性。
? 宏 无法创建宏。但是，读取和重写包含宏的文件将安全地保留宏。
? 数据透视表 不支持生成数据透视表。据报道，可以安全地读取和重写包含数据透视表的文件。
