This is to inform you that your issue (logged as “CELLSJAVA-45780”) has been resolved. The fix/enhancement will be included in our upcoming release (Aspose.Cells v24.1) that we plan to release in the first half of January 2024. You will be notified when the next version is released.
@gkamdar
You are welcome. Once Aspose.Cells for Java v24.1 is released, we will notify you immediately.
The issues you have found earlier (filed as CELLSJAVA-45780) have been fixed in Aspose.Cells for Java 24.1.
@amjad.sahi : I am using the v24.1 and see another mismatch. Attached are the files. I have marked the mismatched cells in red in “output sheet”.
mismatch_example.zip (2.8 MB)
I am running v24.1 aspose cells with the below code
workbook.getSettings().getFormulaSettings().setEnableIterativeCalculation(true);
workbook.refreshDynamicArrayFormulas(true, new CalculationOptions());
workbook.calculateFormula();
Can you pls have a look and let me know if you need any more info
Thanks,
Priyanka
@gkamdar
By using sample files for testing, we can reproduce the issue. It was found that some cells obtained incorrect results after calling the formula calculation.
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSJAVA-45838
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
@gkamdar
Through further research, we found that there are some incorrect cache values in the original file. You can first use Aspose.Cells to read the original file and perform formula calculations, and finally save the file. Then use MS Excel to open the original file and click Formulas ->Calculate Now for manual calculation. You will find that MS Excel will obtain the same result as the file saved by Aspose.Cells.
Would you like to test it again? If you have any questions, please feel free to contact us.
@John.He: what are the incorrect cache values that is not calculating correctly. I tried what you suggested, but the requirement is to get the correct results programmatically i.e workbook.calculateFormula();
the file with name test_sample_24_1 have different result with calculateFormula and test_sample_24_2 has different.
Currently I am using the below settings before workbook calculation
workbook.getSettings().getFormulaSettings().setEnableIterativeCalculation(true);
workbook.refreshDynamicArrayFormulas(true, new CalculationOptions());
How can I get both the files with the same output programmatically. Is there setting that will help.
Thanks,
Priyanka
@gkamdar
We use the following sample code for testing, comparing the file calculated using Aspose.Cells and the file manually calculated and saved in Excel, both of which have the same calculation results. Please refer to the attachment. result.zip (4.3 MB)
The sample code as follows:
Workbook workbook = new Workbook(filePath + "test_sample_24_1.xlsm");
workbook.getSettings().getFormulaSettings().setEnableIterativeCalculation(true);
workbook.refreshDynamicArrayFormulas(true, new CalculationOptions());
workbook.calculateFormula();
workbook.save(filePath + "out_java.xlsm");
If you find that there are still differences between two files, it would be helpful for us to further locate the issue if you could highlight the screenshot.
@John.He : the mismatch is between the two excel files that I have attached (test_sample_24_1.xlsm ,
test_sample_24_2.xlsm).
They have same values in the input sheet but after running the above code for each of the files there are mismatch in output sheet.
mismatch_example.zip (2.8 MB)
mismatch_screenshot.png (67.5 KB)
Attached screenshot of both the excels.
Thanks,
Priyanka
@gkamdar
We use the following example code for testing and compare two files calculated using Aspose.Cells to obtain the same result. Manually calculating and saving files can get the same results for both files. Please refer to the attachment. result.zip (6.0 MB)
The sample code as folows:
Workbook workbook = new Workbook(filePath + "test_sample_24_1.xlsm");
workbook.getSettings().getFormulaSettings().setEnableIterativeCalculation(true);
workbook.refreshDynamicArrayFormulas(true, new CalculationOptions());
workbook.calculateFormula();
System.out.println("test_sample_24_1.xlsm==================");
Cells cells = workbook.getWorksheets().get("Output").getCells();
System.out.println(cells.get("F14").getStringValue());
System.out.println(cells.get("F17").getStringValue());
System.out.println(cells.get("F18").getStringValue());
System.out.println(cells.get("F19").getStringValue());
System.out.println(cells.get("F20").getStringValue());
workbook.save(filePath + "out_java1.xlsm");
workbook = new Workbook(filePath + "test_sample_24_2.xlsm");
workbook.getSettings().getFormulaSettings().setEnableIterativeCalculation(true);
workbook.refreshDynamicArrayFormulas(true, new CalculationOptions());
workbook.calculateFormula();
System.out.println("test_sample_24_2.xlsm==================");
cells = workbook.getWorksheets().get("Output").getCells();
System.out.println(cells.get("F14").getStringValue());
System.out.println(cells.get("F17").getStringValue());
System.out.println(cells.get("F18").getStringValue());
System.out.println(cells.get("F19").getStringValue());
System.out.println(cells.get("F20").getStringValue());
workbook.save(filePath + "out_java2.xlsm");
The output result:
test_sample_24_1.xlsm==================
24,027
25,714
4,106
21,608
21,528
test_sample_24_2.xlsm==================
24,027
25,714
4,106
21,608
21,528
If you still have any questions or confusion, please provide your sample code and result file. We will check it soon.
@John.He : May be you were not able to replicate the issue as the files were already calculated by me to replicate the mismatch.
Please run the above code with the attached files in mismatch_example_2
mismatch_example_2.zip (2.9 MB)
you will be able to replicate the issue.output the results from F10 to F23
Please let me know if you need any more info.
Thanks,
Priyanka
@gkamdar
Thanks for your feedback and details. By comparing the manually refreshed result file with the result file calculated using Cells, we can find calculation errors. Please refer to the attachment. result.zip (8.7 MB)
We will investigate your issue further. Hopefully we can figure it out soon. Once we have an update on it, we will let you know.
The sample code as follows:
Workbook workbook = new Workbook(filePath + "og_sample_29_1.xlsm");
workbook.getSettings().getFormulaSettings().setEnableIterativeCalculation(true);
workbook.refreshDynamicArrayFormulas(true, new CalculationOptions());
workbook.calculateFormula();
System.out.println("og_sample_29_1.xlsm==================");
Cells cells = workbook.getWorksheets().get("Output").getCells();
System.out.println(cells.get("F10").getStringValue());//match
System.out.println(cells.get("F11").getStringValue());//match
System.out.println(cells.get("F12").getStringValue());//not match
System.out.println(cells.get("F13").getStringValue());//match
System.out.println(cells.get("F14").getStringValue());//not match
System.out.println(cells.get("F15").getStringValue());//match
System.out.println(cells.get("F16").getStringValue());//match
System.out.println(cells.get("F17").getStringValue());//not match
System.out.println(cells.get("F18").getStringValue());//not match
System.out.println(cells.get("F19").getStringValue());//not match
System.out.println(cells.get("F20").getStringValue());//not match
System.out.println(cells.get("F21").getStringValue());//match
System.out.println(cells.get("F22").getStringValue());//match
System.out.println(cells.get("F23").getStringValue());//match
workbook.save(filePath + "og_sample_29_1_out_java.xlsm");
workbook = new Workbook(filePath + "og_sample_29_2.xlsm");
workbook.getSettings().getFormulaSettings().setEnableIterativeCalculation(true);
workbook.refreshDynamicArrayFormulas(true, new CalculationOptions());
workbook.calculateFormula();
System.out.println("og_sample_29_2.xlsm==================");
cells = workbook.getWorksheets().get("Output").getCells();
System.out.println(cells.get("F10").getStringValue());//match
System.out.println(cells.get("F11").getStringValue());//match
System.out.println(cells.get("F12").getStringValue());//not match
System.out.println(cells.get("F13").getStringValue());//match
System.out.println(cells.get("F14").getStringValue());//not match
System.out.println(cells.get("F15").getStringValue());//match
System.out.println(cells.get("F16").getStringValue());//match
System.out.println(cells.get("F17").getStringValue());//not match
System.out.println(cells.get("F18").getStringValue());//not match
System.out.println(cells.get("F19").getStringValue());//not match
System.out.println(cells.get("F20").getStringValue());//not match
System.out.println(cells.get("F21").getStringValue());//match
System.out.println(cells.get("F22").getStringValue());//match
System.out.println(cells.get("F23").getStringValue());//match
workbook.save(filePath + "og_sample_29_2_out_java.xlsm");
The output result:
24.1.0
og_sample_29_1.xlsm==================
18,560
0
23,670
17,085
23,670
-66
298
25,358
4,049
21,308
21,229
682,228
634,625
og_sample_29_2.xlsm==================
18,560
0
24,027
17,085
24,027
-66
298
25,714
4,106
21,608
21,528
682,228
634,625
We are pleased to inform you that your issue (“CELLSJAVA-45838”) has been resolved. The fix/enhancement will be included in the upcoming release (Aspose.Cells v24.2) that we plan to release in the next week. You will be notified when the next version is released.
The issues you have found earlier (filed as CELLSJAVA-45838) have been fixed in Aspose.Cells for Java 24.2.
Hi John,
We encountered another mismatch issue. Below is the code snippet
workbook.getSettings().getFormulaSettings().setEnableIterativeCalculation(true);
workbook.refreshDynamicArrayFormulas(true,new CalculationOptions());
workbook.calculateFormula();
workbook.calculateFormula();
workbook.calculateFormula();
workbook.calculateFormula();
I am calculating 4 times to get consistent results. But the same input is giving different results intermittently for the output tab cells E147, F147, G147, H147
Below is the attached excel files
Service-09-05.zip (2.2 MB)
Can you look into this. Our development is blocked due to this inconsistency in excel calculations. Do let me know if you need more info.
Thanks,
Priyanka