getPivotCacheDefinition return null value when Refresh Pivot Table with Apache POI

Question

I want to refresh pivot table after enter data to data sheet

I read two previous questions [this][1] and [this][2]. there two way to do this

one way is

Right click your pivot table -> pivotTable Options -> Data -> Check Refresh Data when opening File

this is working I want a automated way so I tried in this way

FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
workbook = new XSSFWorkbook(inputStream);
XSSFSheet sheet = workbook.getSheet("Summary");
XSSFPivotTable pivotTable = sheet.getPivotTables().get(0);
pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().setRefreshOnLoad(true);

I have two pivot tables in this sheet so sheet.getPrivotTable return this

[Name: /xl/pivotTables/pivotTable1.xml - Content Type: application/vnd.openxmlformats-officedocument.spreadsheetml.pivotTable+xml, Name: /xl/pivotTables/pivotTable2.xml - Content Type: application/vnd.openxmlformats-officedocument.spreadsheetml.pivotTable+xml]

but sheet.getPivotTables().get(0).getPivotCacheDefinition() return null value.

Is there any way to refresh Pivot table automatically?

Axel Richter · Accepted Answer · 2018-12-12 12:45:42Z

2

You are correct, the XSSFPivotTable.getPivotCacheDefinition() does not work properly when XSSFPivotTable was read from an existing *.xlsx file. This is because of the kind how the XSSFPivotTable is got form the file. See XSSFSheet.read: The XSSFPivotTable is read from the related document parts of the sheet. But the XSSFPivotTable has relations of it's own. But those are not read at all.

You could file a bug report to apache poi about this.

Workaround: The XSSFPivotTable extends POIXMLDocumentPart and so it knows about it's own related document parts where the XSSFPivotCacheDefinition is one of.

Example:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import java.io.FileInputStream;

class ExcelReadPivotTables {

 public static void main(String[] args) throws Exception{

  XSSFWorkbook workbook = (XSSFWorkbook)WorkbookFactory.create(new FileInputStream("ExcelWorkbook.xlsx"));
  XSSFSheet sheet = workbook.getSheet("Summary");

  System.out.println(sheet.getPivotTables());
  if (sheet.getPivotTables().size() > 0) {
   XSSFPivotTable pivotTable = sheet.getPivotTables().get(0); 
   System.out.println(pivotTable);

   XSSFPivotCache pivotCache = pivotTable.getPivotCache();
   System.out.println(pivotCache); // null!
   XSSFPivotCacheDefinition pivotCacheDefinition = pivotTable.getPivotCacheDefinition();
   System.out.println(pivotCacheDefinition); //null!

   for (org.apache.poi.ooxml.POIXMLDocumentPart documentPart : pivotTable.getRelations()) {
    if (documentPart instanceof XSSFPivotCacheDefinition) {
     pivotCacheDefinition = (XSSFPivotCacheDefinition)documentPart;
     System.out.println(pivotCacheDefinition); //not null!
    }
   }
  }
  workbook.close();
 }
}

answered Dec 12, 2018 at 12:45

Axel Richter

62.5k7 gold badges70 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

heshjse Over a year ago

I try to refresh pivot table using this pivotCacheDefinition.getCTPivotCacheDefinition().setRefreshOnLoad(true) but it do not refresh pivot table.

Axel Richter Over a year ago

@heshjse: What do you mean by "it do not refresh pivot table"? The pivotCacheDefinition.getCTPivotCacheDefinition().setRefreshOnLoad(true) sets a marker in the *.xlsx file to force Excel to refresh the pivot cache while opening the *.xlsx file. This is what this marker does, nothing else.

heshjse Over a year ago

I added pivotCacheDefinition.getCTPivotCacheDefinition().setRefreshOnLoad(true) but still pivot table not refresh while opening*.xlsx file.

Axel Richter Over a year ago

@heshjse: Sorry, I cannot reproducing. Just tested. For me pivotCacheDefinition.getCTPivotCacheDefinition().setRefreshOnLoad(true) works as expected. Of course if the source data range also has changed, then this also needs to be set: pivotCacheDefinition.getCTPivotCacheDefinition().getCacheSource().getWorksheetSource().setRef("A1:X100"). Where A1:X100 is the new source data range.

Collectives™ on Stack Overflow

getPivotCacheDefinition return null value when Refresh Pivot Table with Apache POI

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related