We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Percentage data in pivot tables is not calculated correctly

Aspose.Cells Product Family
#1

Hi,


I have the problem that percentage data in pivot tables is not calculated correctly if you are not updating the data with excel itself. I am using Apsose Cells for Java v.8.8.2

Code:

WorksheetCollection sheets = workbook.getWorksheets();
for (int i = 0; i < sheets.getCount(); i++) {
PivotTableCollection pts = sheets.get(i).getPivotTables();
for (int i2=0; i2 < pts.getCount();i2++) {
PivotTable pt = pts.get(i2);
pt.refreshData();
pt.calculateRange();
pt.calculateData();
}
}

I also tried this, seeing the same result:

WorksheetCollection sheets = workbook.getWorksheets();
for (int i = 0; i < sheets.getCount(); i++) {
ChartCollection charts = sheets.get(i).getCharts();
for (int j = 0; j < charts.getCount(); j++) {
charts.get(j).refreshPivotData();
charts.get(j).calculate();
}
sheets.get(i).refreshPivotTables();
}

Sample file is attached

Best regards Stefanie
#2

Hi,

Thanks for your posting and using Aspose.Cells.

We have tested your issue with your sample excel file and sample code using the latest version: Aspose.Cells for Java v8.8.2.9
and found it is working fine. Please try the latest version and let us know your feedback.

Please also try adding the following line after refresh and calculate data i.e pt.setRefreshDataOnOpeningFile(true). It should also fix your issue.

Java
pt.refreshData();
pt.calculateRange();
pt.calculateData();
pt.setRefreshDataOnOpeningFile(true)

#3

Hi,


thank you for the fast response.

I tried the v.8.8.2.9 and without pt.setRefreshDataOnOpeningFile(true) the problem still exists.
I have a scenario where I also want to show the results in html. For that case pt.setRefreshDataOnOpeningFile(true) is not fixing the problem. Is there also a solution to fix it in html?

And v.8.8.2.9 can not be found in the download section. Is it a stable version that could be used in production?

The issue is also logged for .NET in CELLSNET-44367

I attached some pics of the results without pt.setRefreshDataOnOpeningFile(true) in v8.8.2.9

Best regards
Stefanie
#4

Hi Stefanie,


Thank you for providing more information.

Please note, setting the PivotTable.RefreshDataOnOpeningFile property to true should fix the problem if you are going to save the result in any of the spreadsheet formats. However, if you wish to render the spreadsheet to images/PDF or convert to HTML, the issue persists and cannot be avoided even by setting the PivotTable.RefreshDataOnOpeningFile property. We have logged this incident in our bug tracking system as CELLSJAVA-41887 (Percentage data from a Pivot Table is not showing properly in HTML) for further investigation & correction.

Regarding the ticket CELLSNET-44367, I am afraid, it is currently unresolved, however, in that scenario, user is saving the result in spreadsheet format therefore we have suggested to use the aforementioned property to fix the problem.
#5

Hi,

Thanks for using Aspose.Cells.


We can’t support refreshing the PivotTable with Percentage data formats
for now. We set the “RefreshDataOnOpeningFile” flag to true and let
MS-Excel refresh the PivotTable automatically when opening the result
file. We will support it later.

#6

@stefgt,
The issue is fixed in v21.6.2.
aspose-cells-21.6.2-java.zip (7.3 MB)

#7

The issues you have found earlier (filed as CELLSJAVA-41887) have been fixed in this update.