Refresh Pivot Table with Apache POI

Question

I'm currently working on a Java application that uses a template excel file that contains a pivot table.

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.

When I open the excel file I must refresh the Pivot table manually to get the data loaded correctly.

Is there any way to refresh the Pivot table with the POI api so I don't have to manually do it?

Solution 1

You can simple activate an option that will refresh the pivot table every time the file is opened.

This Microsoft documentation says :

In the PivotTable Options dialog box, on the Data tab, select the Refresh data when opening the file check box.

Solution 2

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.

EDIT: The Apache POI now provides the possibility to create pivot tables and the pivot table is refreshed on load as default. Class XSSFPivotTable

Solution 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?

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.

Solution 4

 protected void setRefreshPtOnLoad(boolean refreshOnLoad){
        List<POIXMLDocumentPart.RelationPart> relationParts = ((XSSFWorkbook) workbook).getRelationParts();
        for (POIXMLDocumentPart.RelationPart part : relationParts) {
            PackageRelationship relationship = part.getRelationship();
            if (relationship.getRelationshipType().contains("pivotCacheDefinition")){
                String relationId = relationship.getId();
                XSSFPivotCacheDefinition cache = (XSSFPivotCacheDefinition)((XSSFWorkbook) workbook).getRelationById(relationId);
                CTPivotCacheDefinition ctCache = cache.getCTPivotCacheDefinition();
                ctCache.setRefreshOnLoad(refreshOnLoad);
                break; // if only pivot table cache exists in workbook, else - remove *break*
            }
        }
    }

Solution 5

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.

View more solutions

Comments

[Adam Arold]

Call the method on which object? I mean setRefreshOnLoad(boolean bool).

[Solidtubez]

On a CTPivotCacheDefinition.

[Adam Arold]

Apache POI overwrites it.

[SebastianH]

This does not work when the pivot table already existed in the excel document (i.e. it is read via Apache POI, not created). The call to myXSSFPivotTable.getPivotCacheDefinition() always returns null.

[SebastianH]

+1! This answer provided not only a solution to this problem, but showed me a very useful Excel option even when not working with Apache POI :) And in my case Apache POI does not overwrite it! I do not create or manipulate the Pivot Table as such through POI. I only update the data sheets where the pivot charts rever to.

[Muhammad Salman Farooq]

Can I achieve the same in JXL? I am writing the data sheets but when JXL writes the file, it just removes the Pivot Table. Need help in this regard.

[Amol Dixit]

there is no such method getPivotTables() for XSSFPivotTable object. POI does the job of reading and writing of Excel documents. The Pivot Table is internal to Excel and the Excel engine take care of it. Excel Engine make sure that as and when the data changes the Pivot table is updated. There is a AutoRefresh on Load of the Excel document

[heshjse]

pivot table have a value but pivotTable.getPivotCacheDefinition() gives null value.

[LW001]

Please edit your answer to provide more information as to how the code you've provided works, what you have changed, etc. Code-only answers might solve the problem of the original asker but they don't help future readers understand the solution.

[fedor]

I try it. It works with excel v.16 and file.xlsx