Finding inconsistency in excel output for the same input using Aspose Cells java

gkamdar · December 19, 2023, 3:42pm

@John.He : through manual formula calculation I do not get same result, it is different. any aspose settings that need to be done? We are finding this scenario for other excels as well where the 1st run and 2nd run json do not match and after that it is same.

johnson.shi · December 19, 2023, 3:55pm

@gkamdar
When opening your template in ms excel and make ms excel calculate the formulas(such as press “Calculate Now”), we found we can get the same values with what we got by code(printed by the code such as at here). Would you please point out those cells which you found mismatched?

For those formulas that may give different results for the first and second calculation, the reason should be that there are some circular references, for such kind of situation, the calculated result depend on the count of iteration.

gkamdar · December 19, 2023, 4:06pm

@johnson.shi : I did “calculate now” and I get the same results.
but my question here is why I don’t get the same values when I am calculating using the workbook.calculateFormula() method ?
The excel that I shared with you is saved after using the “workbook.calculateFormula()” method

Can you pls elaborate on this

the reason should be that there are some circular references, for such kind of situation, the calculated result depend on the count of iteration.

Thanks,
Priyanka

amjad.sahi · December 19, 2023, 5:20pm

@gkamdar,

We get same results (and don’t get mismatches) when calculating formulas by Aspose.Cells API and MS Excel (manual) for your Excel spreadsheets. Could you please provide details of your environment (OS details with locale/regional settings, MS Excel version, JDK version, etc.).

We will get back to you soon.

gkamdar · December 19, 2023, 8:15pm

@amjad.sahi : Excel version that I am using version 2302, JDK version jdk1.8.0_341.

If I run the below code snippet for the file attached it will give me consistent results

       workbook.getSettings().getFormulaSettings().setEnableIterativeCalculation(true);
       workbook.refreshDynamicArrayFormulas(true, new CalculationOptions());

       workbook.calculateFormula();
       workbook.calculateFormula();
       workbook.calculateFormula();

I have to use calculateFormula thrice to get consistent results.

if you use the file attached,
test-file.zip (1.4 MB)

run it with below 3 scenarios

once workbook calculation
twice workbook calculation
thrice workbook calculation

you will see there are different results for the code for each scenario
System.out.println(cells.get(“E167”).getStringValue());
System.out.println(cells.get(“F167”).getStringValue());
System.out.println(cells.get(“G167”).getStringValue());

Please let me know if you need any more details to replicate this

John.He · December 20, 2023, 1:06am

@gkamdar
By using sample code and files for testing, we can reproduce the issue. Found different incorrect results obtained after calling multiple formula calculation.
Test code as follows:

Workbook workbook = new Workbook(filePath + "test_file.xlsm");

Cells cells = workbook.getWorksheets().get("Output").getCells();
System.out.println(cells.get("E167").getStringValue());
System.out.println(cells.get("F167").getStringValue());
System.out.println(cells.get("G167").getStringValue());

workbook.getSettings().getFormulaSettings().setEnableIterativeCalculation(true);	       
workbook.refreshDynamicArrayFormulas(true, new CalculationOptions());
workbook.calculateFormula();
System.out.println("After calculating formulas=====once");
System.out.println(cells.get("E167").getStringValue());
System.out.println(cells.get("F167").getStringValue());
System.out.println(cells.get("G167").getStringValue());

workbook.calculateFormula();
System.out.println("After calculating formulas=====twice");
System.out.println(cells.get("E167").getStringValue());
System.out.println(cells.get("F167").getStringValue());
System.out.println(cells.get("G167").getStringValue());

workbook.calculateFormula();
System.out.println("After calculating formulas=====Three times");
System.out.println(cells.get("E167").getStringValue());
System.out.println(cells.get("F167").getStringValue());
System.out.println(cells.get("G167").getStringValue());

The output result:

0
0
0
After calculating formulas=====once
111,429
169,997
210,363
After calculating formulas=====twice
163,568
204,450
235,435
After calculating formulas=====Three times
163,568
163,568
163,568

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-45780

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 · December 20, 2023, 7:08pm

@John.He: Please keep us posted with the timeline for the solution.

amjad.sahi · December 20, 2023, 7:21pm

@gkamdar,

Sure, we will keep you posted with the ETA (if possible) or other updates once available.

amjad.sahi · December 29, 2023, 9:58am

@gkamdar,

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 · January 3, 2024, 8:56pm

@amjad.sahi : thank you for the update

John.He · January 4, 2024, 1:07am

@gkamdar
You are welcome. Once Aspose.Cells for Java v24.1 is released, we will notify you immediately.

aspose.notifier · January 11, 2024, 12:33pm

The issues you have found earlier (filed as CELLSJAVA-45780) have been fixed in Aspose.Cells for Java 24.1.

gkamdar · January 24, 2024, 8:59pm

@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

John.He · January 25, 2024, 1:44am

@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.

John.He · January 25, 2024, 2:13pm

@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.

gkamdar · January 25, 2024, 4:34pm

@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

John.He · January 26, 2024, 1:10am

@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.

gkamdar · January 26, 2024, 6:50pm

@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

John.He · January 27, 2024, 1:33am

@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.

gkamdar · January 29, 2024, 11:24pm

@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