使用 Apache POI 刷新数据透视表时，getPivotCacheDefinition 返回空值 [英] getPivotCacheDefinition return null value when Refresh Pivot Table with Apache POI

问题描述

我想在将数据输入到数据表后刷新数据透视表

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

我阅读了之前的两个问题 [this][1] 和 [this][2].有两种方法可以做到这一点

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

一种方法是

右键单击您的数据透视表 -> 数据透视表选项 -> 数据 -> 打开文件时检查刷新数据

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);

我在这个工作表中有两个数据透视表，所以 sheet.getPrivotTable 返回这个

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]

但是 sheet.getPivotTables().get(0).getPivotCacheDefinition() 返回空值.

有没有办法自动刷新数据透视表?

Is there any way to refresh Pivot table automatically?

推荐答案

你是对的，当从 XSSFPivotTable 读取时，XSSFPivotTable.getPivotCacheDefinition() 不能正常工作现有的 *.xlsx 文件.这是因为从文件中获取 XSSFPivotTable 的方式.见 XSSFSheet.read:从工作表的相关文档部分读取 XSSFPivotTable.但是 XSSFPivotTable 有它自己的关系.但那些根本就没有读过.

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.

您可以向 apache poi 提交关于此的错误报告.

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

解决方法:XSSFPivotTable 扩展了 POIXMLDocumentPart，因此它知道自己的相关文档部分，其中 XSSFPivotCacheDefinition 是其中之一.

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

示例:

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();
 }
}

这篇关于使用 Apache POI 刷新数据透视表时，getPivotCacheDefinition 返回空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！