缺乏关于 Apache 网站中 Apache POI 透视表方面的文档,因此我需要写这篇文章。
我想使用 Apache POI 刷新工作簿中的透视表。
请告诉我在哪里可以获得有关此方面的适当文档和示例。
缺乏关于 Apache 网站中 Apache POI 透视表方面的文档,因此我需要写这篇文章。
我想使用 Apache POI 刷新工作簿中的透视表。
请告诉我在哪里可以获得有关此方面的适当文档和示例。
OFFSET()
或命名表
创建动态范围公式作为数据透视表的源数据,然后绘制数据透视表。右键单击您的数据透视表,选择
数据透视表选项->数据->打开文件时检查刷新数据
打开MyFileName.xlsx
文件并填写数据。
将文件保存为PTs格式,文件名为.xlsm,并插入VBA脚本(ALT+F11):
' Create module and insert this:
Public Const pivotName1 As String = "myPivotName"
Public Const sourceSheetName As String = "source"
Public Const sourceColumnCount As Long = 23
' In "ThisWorkbook" chapter insert this:
Dim lRow As Long
Private Sub Workbook_Open()
Application.ScreenUpdating = False
ActiveWorkbook.Worksheets(sourceSheetName).Activate
' In file should preliminarily insert keyWord "firstOpenFlag" in CV1 cell (sheet sourceSheetName)
' It gona start actions below
If ActiveSheet.Cells(1, 100) = "firstOpenFlag"
Then
ActiveSheet.Cells(1, 100) = ""
lRow = getLastRowForFirstCol(sourceSheetName)
Call updateAllPTCache
ActiveWorkbook.Worksheets(sourceSheetName).Activate
ActiveSheet.Range("A1").Select
End If
Application.ScreenUpdating = True
End Sub
Private Function getLastRowForFirstCol(sourceSheetName As String) As Long
ActiveWorkbook.Worksheets(sourceSheetName).Activate
getLastRowForFirstCol = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
If getLastRowForFirstCol < 2 Then getLastRowForFirstCol = 2
End Function
Private Sub updateAllPTCache()
Dim pt As PivotTable
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=sourceSheetName + "!R1C1:R" + CStr(lRow) + "C" + CStr(sourceColumnCount), _
Version:=xlPivotTableVersion14)
' xlPivotTableVersion14 - work in 2013, 2016 exlApp
' Downgrade xlPivotTableVersion for backward compatibility
pt.RefreshTable
Next pt
Next ws
End Sub
另一种解决方案(无需 VBA 脚本)
在 tempalate.xlsx 中,在源记录集的标题上创建 xlTable 对象。将名称设置为 xlTable,例如 'mySourceTable'。 还要在文件 preSet 中为您的数据透视表进行预设:
在 POI 中:
private void updateXlTableSource() {
XSSFTable sourceTable = ((XSSFWorkbook)workbook).getTable("mySourceTable");
CTTable ctTable = sourceTable.getCTTable();
String sourceRef = getSourceDataRange().formatAsString();
ctTable.setRef(sourceRef);
ctTable.getAutoFilter().setRef(sourceRef);
}
private CellRangeAddress getSourceDataRange() {
XSSFSheet xssfSheet = (XSSFSheet) workbook.getSheet("sourceSheetName");
int uBoundSourceDataRow = findFirstEmptyRowFrom(xssfSheet) - 1;
if (uBoundSourceDataRow < 2) {
uBoundSourceDataRow = 2;
}
int uBoundSourceDataCol = findFirstEmptyColFromFirstRow(xssfSheet) - 1;
return new CellRangeAddress(0, uBoundSourceDataRow, 0, uBoundSourceDataCol);
}
注意:检查您的tempalate.xlsx是否存在未知查询。如果存在,请删除,否则将阻止 PT 更新。
缺点:PT 的自动筛选器包含不存在的元素(来自 PT 模板)。