Hi,
I have already raised a post regarding the issue with calculate formula. Please find the below thread,
2 fixes has been made in the Aspose and released in the below versions,
- 8.2.1.1
- 8.2.1.2
Currently we are using the Version 8.2.1.4 and it is working correctly for a excel sheet calculation.
When we tried to upgrade to latest 8.5.2 version and we are still facing the above issues. Can you please let us know whether the patches (8.2.1.1 & 8.2.1.2) are included in the latest 8.5.2 version?
Hi Vinod,
Please note that there are three tickets attached to your mentioned thread where all of these issues were fixed with the release of Aspose.Cells for Java 8.2.2. The ticket details are as follow.
- CELLSJAVA-41013: Incorrect value returned for INDIRECT function after calling Workbook.calculateFormula.
- CELLSJAVA-41021: SUMPRODUCT with multiple INDIRECT functions calculates the formula as #VALUE.
- CELLSJAVA-41035: Incorrect value returned for AVERAGEIF function after calling Workbook.calculateFormula.
In order to make sure that the aforesaid fixes have propagated to the recent revisions of Aspose.Cells for Java API, I have reevaluated all the cases while using the latest revision
8.5.2.4 against the samples shared earlier on the referenced thread.
Please note, none of the above mentioned issues were replicated.
That said, please give a try to the latest revision on your end. In case you still see any issue with the calculated values then share the sample spreadsheet to properly analyze the matter on our side.
Thank you for your cooperation and understanding.
Thanks Raza.
Please find the attached Excel Sheet.
Excel sheet Result for the Cell E129 is 0 and Aspose Version 8.2.1.4 calculates correctly as 0.0. But Aspose 8.5.2 is failing to calcualte the result and returns error #NAME?
Cell “E129” is higlighted in RED color in the attached excel sheet.
Please do the needful and provide us the fix.
Thanks,
Vinod
Hi Vinod,
Thank you for sharing the sample spreadsheet.
We have evaluated the presented scenario while using the latest version of Aspose.Cells for Java 8.5.2.4 and the following piece of code. We are able to observe the mentioned problem, that is; Aspose.Cells for Java API returns #NAME? after calculating the formulas. This scenario needs proper analysis therefore a ticket with Id CELLSJAVA-41487 has been logged for product team’s review. Please allow us some time for investigation. In the meanwhile, we will keep you posted with updates in this regard.
Java
Workbook book = new Workbook(“D:/Formula+failed+sheet.xlsx”);
System.out.println(book.getWorksheets().get(0).getCells().get(“E129”).getValue());
book.calculateFormula();
System.out.println(book.getWorksheets().get(0).getCells().get(“E129”).getValue());
Hi,
Thanks for using Aspose.Cells.
This is to inform you that we have fixed your issue CELLSNET-41487 now. We will soon provide the fix after performing QA and including other enhancements and fixes.
Just wanted to be sure that this issue is getting addressed for java api as well.
Hi,
Yes, sure, your issue logged earlier as “CELLSJAVA-41487” is also fixed. We will provide a fix after performing QA and including other enhancements and fixes.
Once the fix (Java) is available, we will let you know here.
Thank you.
THanks for the reply & support, can you provide some additional insight on why this fix was broken. we are in a critical phase of the project just wanted to be sure if upgrading to latest version on aspose doesn’t introduce any new issues.
Hi Sundarraj,
We have recorded your comments to the ticket attached to this thread, and have requested the concerned member of the product team to provide the RCA for the presented problem. As soon as we receive any updates, we will post here for your kind reference.
Hi again,
This is to update you that we have received the analysis results from the product team. Please note that we have rebuilt the entire formula calculating engine in recent versions of Aspose.Cells APIs. The new engine has resolved many issues that couldn’t be implemented with old version of the engine, however, recent changes have also introduced a bug of calculating formula where there are recursive references in Names. When calculating the Name, if recursive references were found, then the calculation stops. Anyways, we have fixed this issue now.
Thanks for the fix. We have verified our spreadsheet against the Aspose 8.6.0.2 Version and it produces the wrong result.
Please find the attachments,
- Failing_Sheet.xlsx
- Failing_Sheet_Output.xlsx
- SampleCalculation.java
Cell E127 contains a formula and inputs cells are populated and then calculateFormula() is triggered.
The Excel sheet Result for the Cell E127 is 184.02 . But Aspose 8.6.0.2 is calculating the result as 0.
Cell "E127" is higlighted in RED color in the attached excel sheets.
Please do the needful and provide us the fix.
Hi,
We have evaluated the recently shared scenario while using the latest version of Aspose.Cells for Java 8.6.0.3, and we are able to replicate the problem, that is; Excel calculated the formula in E127 as 184 but Aspose.Cells for Java returns 0 for the same. We have look further into the details of this problem therefore we have logged this incident as CELLSJAVA-41507 in our bug tracking system. Please allow us some time to analyze the scenario and get back to you with updates in this regard.
Hi Vinod,
I am afraid, we haven’t yet received any updates regarding the ticket logged earlier as CELLSJAVA-41507. That said, we have raised the priority of the aforesaid ticket and have requested the concerned member of the product team to share their analysis at earliest possible. As soon as we get any news, we will post here for your kind reference.
Thank you for your patience with us.
Hi,
Well, kindly solve your issue by setting wb.Settings.Iteration as true. And, we will continue to work on it and fix this issue soon.
Thank you.
Hi Vinod,
This is to update you that we have resolved the problem logged earlier as CELLSJAVA-41507. We will notify you here in reference to the aforementioned ticket as soon as the next build of Aspose.Cells for Java is available for public use.
Thank you for your patience with us.
Thanks Raza.
Do we need to use this setting “wb.Settings.Iteration as true” even after the fix? Issue got solved with this setting for the reported spreadsheet. But we are facing issue with another spreadsheet if set this setting.
Can you please share us the Patch for verification?
Thanks,
Vinod
Hi Vinod,
Product team hasn’t yet shared the build containing the aforementioned fix therefore I am not able to verify your concerns, however, I have posted your comments on the relevant ticket for product team’s review. As soon as we get more updates, we will post here for your kind reference.
Hi,
To update you on your issue/ concerns, with the coming fix, you do not need to use Iteration options anymore.
Hope, this helps.
Thank you.