Refresh Pivot table using Apache POI

Question

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

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

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

Answer 1

Kindly follow the followings which i did.

1. Fill rough data for pivot table in your MyFileName.xlsx file.
2. Create a Dynamic Range Formula through OFFSET() or Named Table as Source Data for Pivot table and draw pivot table.

3. Simply Right click your pivot table and choose

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

4. Open a MyFileName.xlsx file and fill out the data.

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.

Answer 2

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

To quote Solitudes answer in codeMans link verbatim:

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.

> In POI this can be done by calling the method setRefreshOnLoad(boolean bool), that takes a boolean as parameter, on a 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.