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
Also, I have another issue in different excel, where after pivot refresh the static filters are not retain in “Impact Analysis” sheet.
Attached excel for the same. I am using above code for refresh.
ThanksPivotFilterNotRetain.zip (144.0 KB)
@kishore007008,
I have tried both your sample files. Regarding the first sample “PivotRefreshIssue.xlsb” when code is executed and the output file is opened in MS Excel, it raises errors before opening the file. Could you please share if you got similar errors in MS Excel or not?
PivotIssue.png (27.3 KB)
Regarding the second sample file, the output file has no issue as static filters are maintained in the output file. Please give it a try using the latest version 20.8.x and share the feedback.
Hi,
Yes for first excel, I got same error which you shared the screen shot.
For 2nd excel , I used aspose-cell-20.8.3 version, which was shared by Amjad on my last pivot issue. But still the pivot static filter was not retain value after pivot refresh.
When I do manual refresh from excel it retain all static filter value, which was checked in that filter.
Thanks
Kishore
@kishore007008,
We have logged the first issue in our database for detailed analysis and we will write back here once any update is ready for sharing.
This issue is logged as:
CELLSJAVA-43282 - Refresh pivot not working and corrupts the output file
Regarding the second issue I have tried it again but could not observe the issue. Please share some image which shows the issue in the output file for our reference. Comparison of the two files is attached here for your reference.
Output Filters.png (165 KB)
Hi,
Attached screenshot for not retaining filter.
While opening excel file after pivot refresh, getting Yes, No button and after click on yes or no value, all data displaying.
I used the above code for pivot refresh.
Thanks
KishoreRetainFilterIssue1.JPG (41.3 KB)
RetainFilterIssue2.JPG (33.2 KB)
@kishore007008,
It seems to be related to version of MS Excel being used for opening the output Excel file as I do not get any yes/no option while opening output file. I am using Microsoft Excel for Office 365 MSO (16.0.12527.20612) 32-bit. Could you please share which version of MS Excel are you using to open this file? Here is the output file at my end:
PivotFilterNotRetainOutput.zip (142.4 KB)
@ahsaniqbalsidiqui
We use Excel 2016 version.
I downloaded your output file and while opening I don’t see any yes and no. But I don’t see any data refreshed on that sheet. Norefresh.JPG (16.7 KB)
Kindly check
This code refers to the sheets “OTC Pivot” and “Working” which are not present there in the sample Excel file for this issue. Could you please check and share the updated code or relevant Excel file for our reference?
The code is for all pivot refresh. You can change sheetName to “Impact Analysis” for the PivotFilterNotRetain.xlsx filr
@kishore007008,
The second issue regarding losing the static filters is reproduced and logged in our database for further investigation. We will notify you here once any update is ready to share.
This issue is logged as:
CELLSJAVA-43285 - Static filters are lost after refreshing the pivot table
@kishore007008,
Please try our latest version/fix: Aspose.Cells for Java v20.8.8 (attached)
Your issue (logged earlier as “CELLSJAVA-43285”) should be fixed in it.
Please note:
-
MS-Excel will keep the missing data when refreshing PivotTables. But when you call PivotTable.RefreshData() in Aspose.Cells, we will delete the missing data. Only keep the current existing data and set the same item status when the original data item still exists in the datasource.
-
After researching your file, we find that the PivotFiled in row area contains many items which are all numbers. But in you datasource, the items are all strings. So they can’t match. So all the data are not selected. But MS-Excel has some rules, when you refresh the PivotTable, if all the PivotItems of PivotField are not selected, the first item will be selected.
-
For example, in the file “PivotFilterNotRetain.xlsx”, the PivotTable named “PivotTable1” in the worksheet named “Impact Analysis”, the PivotField named “TargetGl” in row area contains many items which are all numbers. But the datasouce are all strings in the same position. So when you refresh the PivotTable , you will find two items (165500101 and “165500101”). The MS-Excel result shows the missing data, not the new data according to data source. So when all the PivotItems are not selected, We will select the first PivotItem.
Let us know your feedback.
aspose-cells-20.8.8-java.zip (7.2 MB)
The issues you have found earlier (filed as CELLSJAVA-43285) have been fixed in Aspose.Cells for Java v20.9. This message was posted using Bugs notification tool by ahsaniqbalsidiqui.
You may also get the new version @ Maven repos. (On Premise File Format API Releases | Aspose)
@kishore007008,
This is to inform you that we have fixed your issue (logged earlier as “CELLSJAVA-43282”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.
The issues you have found earlier (filed as CELLSJAVA-43282) have been fixed in Aspose.Cells for Java 20.10. This message was posted using Bugs notification tool by ahsaniqbalsidiqui