使用Apache POI刷新Pivot表

No/Minimal documentation regarding Apache POI for Pivot tables in the Apache site has got me to write this.

关于Apache站点中的Pivot表的Apache POI的基本文档没有让我写这篇文章。

I want to refresh a pivot table in a Work Book using Apache POI.

我想使用Apache POI在工作簿中刷新一个pivot表。

Please let me know where I can get proper documentation and Examples regarding this.

请告诉我在哪里可以找到合适的文件和例子。

3 个解决方案

#1

The link codeMan refers to has some advice that looks quite specific to Apache POI & Excel. You'll see that there is no great documentation for a reason here (it's not supported): http://poi.apache.org/spreadsheet/limitations.html

link codeMan引用了一些针对Apache POI和Excel的建议。您将看到，这里没有任何优秀的文档(不支持):http://poi.apache.org/spreadsheet/limitations.html

To quote Solitudes answer in codeMans link verbatim:

引用codeMans链接中独白的回答:

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中>可以通过调用setRefreshOnLoad方法(布尔bool)来完成，该方法以一个布尔值作为参数，在CTPivotCacheDefinition上。

If you need to refresh the pivot table before the file is opened, (for example to then use the pivottable calculated data in further calculations and have POI write this) then I'm not sure that this would be possible at all with POI, and potentially hooking up to excel using a COM solution might be the way to go.

如果你需要刷新数据透视表之前打开的文件,然后使用数据透视表计算数据(例如在进一步的计算和POI写这)然后我不确定可能的原发性卵巢功能不全,并可能使用COM excel勾搭的解决方案可能是路要走。

#2

Apart from the limitations, you can check a little information about Package org.apache.poi.hssf.record.pivottable

除了这些限制之外，您还可以检查关于包org.apache.poi.hssf. pivottable的一些信息

Though if I will have to do the same, I would create the table/chart manually once and will update the chart using apache poi as I have done here

尽管如果我必须这样做，我将手工创建表/图表，并使用apache poi更新图表，就像我在这里所做的那样

#3

Kindly follow the followings which i did.

请按照我所做的来做。

Fill rough data for pivot table in your MyFileName.xlsx file.
在MyFileName中填充pivot表的粗略数据。xlsx文件。
Create a Dynamic Range Formula through OFFSET() or Named Table as Source Data for Pivot table and draw pivot table.
通过OFFSET()或命名表为Pivot表的源数据创建动态范围公式，并绘制Pivot表。
Simply Right click your pivot table and choose

只需右键单击pivot表并选择

pivotTable Options->Data-> Check Refresh Data when opening File

数据透视表选项->数据->打开文件时检查刷新数据
Open a MyFileName.xlsx file and fill out the data.

打开一个MyFileName。xlsx文件并填写数据。

That's all... whenever you opening workbook it will be refreshed to current data. :-)

这就是……无论何时打开工作簿，都将刷新到当前数据。:-)

Note: This will not work when you creating Pivot table through POI.

注意:当您通过POI创建Pivot表时，这将不起作用。

#1