问题描述
我想在将数据输入数据表之后刷新数据透视表
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
一种方法是
右键单击数据透视表-> ivotTable选项->数据->打开文件时检查刷新数据
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返回null值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!