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?

Answer 1

 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*
            }
        }
    }

Answer 2

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.

Answer 3

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