I'm currently working on a Java application that uses a template excel file that contains a pivot table.
我目前正在开发一个Java应用程序，它使用包含数据透视表的模板excel文件。
The template file also has a data sheet that seeds the pivot table. This data sheet is dynamically loaded in the java application through the Apache POI api.
模板文件还有一个数据表，用于播放数据透视表。此数据表通过Apache POI api在Java应用程序中动态加载。
When I open the excel file I must refresh the Pivot table manually to get the data loaded correctly.
当我打开excel文件时，我必须手动刷新数据透视表以正确加载数据。
Is there any way to refresh the Pivot table with the POI api so I don't have to manually do it?
有没有办法用POI api刷新Pivot表，所以我不必手动执行它？
5 个解决方案
#1
5
You can simple activate an option that will refresh the pivot table every time the file is opened.
您可以简单地激活一个选项，该选项将在每次打开文件时刷新数据透视表。
This Microsoft documentation says :
这篇Microsoft文档说：
In the PivotTable Options dialog box, on the Data tab, select the Refresh data when opening the file check box.
在“数据透视表选项”对话框的“数据”选项卡上，选中“打开文件时刷新数据”复选框。
#2
4
It is possible. In the PivotCacheDefinition, there is an attribute refreshOnLoad that can be set to true. The cache is then refreshed when the workbook is opened. More information here.
有可能的。在PivotCacheDefinition中，有一个属性refreshOnLoad可以设置为true。然后在打开工作簿时刷新缓存。更多信息在这里。
In POI this can be done by calling the method setRefreshOnLoad(boolean bool), that takes a boolean as parameter, on a CTPivotCacheDefinition.
在POI中，这可以通过在CTPivotCacheDefinition上调用setRefreshOnLoad（boolean bool）方法来完成，该方法将boolean作为参数。
EDIT: The Apache POI now provides the possibility to create pivot tables and the pivot table is refreshed on load as default. Class XSSFPivotTable
编辑：Apache POI现在提供了创建数据透视表的可能性，并且数据透视表在默认情况下在加载时刷新。类XSSFPivotTable
#3
3
This post says you can turn on an option on the pivot table that will cause it to refresh itself automatically every time the workbook is opened:
这篇文章说你可以打开数据透视表上的一个选项，它会在每次打开工作簿时自动刷新它：
How can I refresh all the pivot tables in my excel workbook with a macro?
如何使用宏刷新Excel工作簿中的所有数据透视表？
Hopefully this will still be true after you have used Apache POI to refresh or extend the data rows on which the pivot tables are based.
希望在使用Apache POI刷新或扩展数据透视表所基于的数据行之后，这仍然是正确的。
#4
1
The basic answer to this is no. POI is a document format reader and writer. Updating the Pivot table is an excel engine issue. Sure, another application could try to duplicate the Excel engine behavior here, but that is really going to get ugly. I recommend using Joel's workaround of accessing the excel COM objects over a webservice to get this kind of thing done.
对此的基本答案是否定的。 POI是一种文档格式的读写器。更新数据透视表是一个excel引擎问题。当然，另一个应用程序可能会尝试在这里复制Excel引擎行为，但这确实会变得丑陋。我建议使用Joel的解决方法，通过Web服务访问excel COM对象以完成此类操作。
#5
0
This might work:
这可能有效：
XSSFPivotTable pivotTable = pivotSheet.getPivotTables().get(0);
pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().setRefreshOnLoad(true);
#1
5
You can simple activate an option that will refresh the pivot table every time the file is opened.
您可以简单地激活一个选项，该选项将在每次打开文件时刷新数据透视表。
This Microsoft documentation says :
这篇Microsoft文档说：
In the PivotTable Options dialog box, on the Data tab, select the Refresh data when opening the file check box.
在“数据透视表选项”对话框的“数据”选项卡上，选中“打开文件时刷新数据”复选框。
#2
4
It is possible. In the PivotCacheDefinition, there is an attribute refreshOnLoad that can be set to true. The cache is then refreshed when the workbook is opened. More information here.
有可能的。在PivotCacheDefinition中，有一个属性refreshOnLoad可以设置为true。然后在打开工作簿时刷新缓存。更多信息在这里。
In POI this can be done by calling the method setRefreshOnLoad(boolean bool), that takes a boolean as parameter, on a CTPivotCacheDefinition.
在POI中，这可以通过在CTPivotCacheDefinition上调用setRefreshOnLoad（boolean bool）方法来完成，该方法将boolean作为参数。
EDIT: The Apache POI now provides the possibility to create pivot tables and the pivot table is refreshed on load as default. Class XSSFPivotTable
编辑：Apache POI现在提供了创建数据透视表的可能性，并且数据透视表在默认情况下在加载时刷新。类XSSFPivotTable
#3
3
This post says you can turn on an option on the pivot table that will cause it to refresh itself automatically every time the workbook is opened:
这篇文章说你可以打开数据透视表上的一个选项，它会在每次打开工作簿时自动刷新它：
How can I refresh all the pivot tables in my excel workbook with a macro?
如何使用宏刷新Excel工作簿中的所有数据透视表？
Hopefully this will still be true after you have used Apache POI to refresh or extend the data rows on which the pivot tables are based.
希望在使用Apache POI刷新或扩展数据透视表所基于的数据行之后，这仍然是正确的。
#4
1
The basic answer to this is no. POI is a document format reader and writer. Updating the Pivot table is an excel engine issue. Sure, another application could try to duplicate the Excel engine behavior here, but that is really going to get ugly. I recommend using Joel's workaround of accessing the excel COM objects over a webservice to get this kind of thing done.
对此的基本答案是否定的。 POI是一种文档格式的读写器。更新数据透视表是一个excel引擎问题。当然，另一个应用程序可能会尝试在这里复制Excel引擎行为，但这确实会变得丑陋。我建议使用Joel的解决方法，通过Web服务访问excel COM对象以完成此类操作。
#5
0
This might work:
这可能有效：
XSSFPivotTable pivotTable = pivotSheet.getPivotTables().get(0);
pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().setRefreshOnLoad(true);