Hi all,
I’m getting an out of memory error opening an Excel file with a pivot table. It was created with Aspose.cells for Java version 19.11.
There is a post in your forum (link to post) where it is suggested to add: pivotTable.setRefreshDataFlag(true);
It seems to work, but since I get the error only with a particular combination of data and I have not fully understood what that method does, I would like to be sure I don’t break something in other cases.
Could you please explain me what that method does and tell me if it’s safe to add that single line of code in all cases?
The attribute denotes whether to ask MS Excel refresh data in the pivot table or not when opening the file into MS Excel manually. We also recommend you to kindly try our latest version (Aspose.Cells for Java v20.10.x) and do not use this option. If you still find the issue, you may provide us the template file and sample code to show the issue, we will check it soon.
I cannot test the code with version 20.10, but I have tested it with version 20.9 and I get the same error.
It’s difficult to reproduce the problem with an example project, but I will try and I will let you know.
About the setRefreshDataFlag method, from the description you gave me it looks similar to the setRefreshDataOnOpeningFile method, could you please tell me what differences there are between the two methods? Excuse me if I insist on this point, but it would be important for me to be able to get around the problem, before a possible fix, but to do that I need to know if it’s safe to add that single line of code in all cases.
Please disregard my above/previous statement/detail about setRefreshDataFlag API. Let me give complete details about setRefreshDataFlag Vs setRefreshDataOnOpeningFile:
setRefreshDataOnOpeningFile() method indicates whether refreshing (pivot table) data via MS-Excel manually when open the file into it.
setRefreshDataFlag indicates whether refreshing pivot table’s data in code via Aspose.Cells APIs. When you want to refresh the PivotTable dynamically, you will use the setRefreshDataFlag property to true. See the following sample code (in sequence) for your reference:
pivotTable.setRefreshDataFlag(true);
pivotTable.refreshData();
pivotTable.calculateData();
pivotTable.setRefreshDataFlag(false);
(Note: if you don’t set RefreshDataFlag in above order, some calculations will not be executed/affected or you might get some incorrect results.
In short, RefreshDataFlag is basically for refreshing/calculating data via Aspose.Cells APIs in code. RefreshDataOnOpeningFile is MS Excel’s switch whether to refresh pivot table when opening the file into it.
Perhaps I found the problem, it seems that it occurs when we put more than 12 fields in the pivot table page filters.
I attach a simple project that reproduces the issue. If you run it and open the generated file, the error occurs; if you change the PAGE_FIELDS value to 12 or you add the suggested workaround (there is a commented section), the generated file works.
You may use the workaround as we suggested to cope with your issue for the time being, there is no side effect to using it. Moreover, we reproduced/confirmed your issue using your sample code. We have logged an investigation ticket with an id “CELLSJAVA-43346” for your issue. We will look into it soon.
Once we have an update on it, we will let you know.
This is to inform you that we have fixed your issue “CELLSJAVA-43346” now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.
Hello @Amjad_Sahi,
I cannot test the latest version of the library with the original code for now, but I have tested it with the example project and it works.
The issues you have found earlier (filed as CELLSJAVA-43346) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi