Hi,
My Pivot table is not refreshed, attached sample code and file for the same.
I have two pivot sheet. One is “OTC Pivot” and another is “Working” pivot sheet. Both was not able to refresh.
I used below code for refreshing pivot table.
public static void main(String[] args) {
String fileName = "PivotRefreshIssue.xlsb";
try {
License license = new License();
license.setLicense("Aspose.Cells.lic");
refreshPivotTable(fileName, "PivotTable1", "OTC Pivot");
refreshPivotTable(fileName, "PivotTable1", "Working");
} catch (Exception e) {
e.printStackTrace();
}
}
public static void refreshPivotTable(String filePath, String pivotTableName, String sheetName) throws Exception {
com.aspose.cells.LoadOptions options = new com.aspose.cells.LoadOptions();
options.setParsingFormulaOnOpen(true);
Workbook workbook = new Workbook(filePath, options);
Worksheet worksheet = workbook.getWorksheets().get(sheetName);
System.out.println("Refresh started..");
if (worksheet != null) {
PivotTable pivotTable = worksheet.getPivotTables().get(pivotTableName);
if (worksheet.getPivotTables().getCount() > 0) {
if (pivotTable != null) {
for (int i = 0; i < pivotTable.getPageFields().getCount(); i++) {
PivotField pf = pivotTable.getPageFields().get(i);
for (int j = 0; j < pf.getPivotItems().getCount(); j++) {
PivotItem pi = pf.getPivotItems().get(j);
if (pi != null) {
pi.setPosition(0); // THIS IS IMPORTANT
} else {
System.out.println("PivotItem is null");
}
}
pf.setShowAllItems(true);
System.out.println("Field- " + pf.getName());
}
pivotTable.setRefreshDataFlag(true);
pivotTable.refreshData();
pivotTable.calculateData();
pivotTable.setRefreshDataOnOpeningFile(true);
}
}
}
workbook.calculateFormula(true);
workbook.save(filePath);
}
PivotRefreshIssue.zip (138.6 KB)
Can you have a look?
Thanks
Kishore