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

amjad.sahi · December 18, 2023, 6:35pm

I could not find the issue using your Excel files. I used the following sample code, which works fine. The cell values are not mismatched and are the same before and after calculating formulas.
e.g.
Sample code:

Workbook workbook = new Workbook("f:\\files\\excel-mismatch-example1.xlsm");
//Workbook workbook = new Workbook("f:\\files\\excel-mismatch-example2.xlsm");
workbook.getSettings().getFormulaSettings().setEnableIterativeCalculation(true);

Cells cells = workbook.getWorksheets().get("Output").getCells();
System.out.println(cells.get("D57").getStringValue());
System.out.println(cells.get("E57").getStringValue());
System.out.println(cells.get("F57").getStringValue());
System.out.println(cells.get("D70").getStringValue());
System.out.println(cells.get("E70").getStringValue());
System.out.println(cells.get("F70").getStringValue());
System.out.println(cells.get("E167").getStringValue());
System.out.println(cells.get("F167").getStringValue());
System.out.println(cells.get("G167").getStringValue());

workbook.refreshDynamicArrayFormulas(true, new CalculationOptions());
workbook.calculateFormula();
System.out.println("After calculating formulas");
System.out.println(cells.get("D57").getStringValue());
System.out.println(cells.get("E57").getStringValue());
System.out.println(cells.get("F57").getStringValue());
System.out.println(cells.get("D70").getStringValue());
System.out.println(cells.get("E70").getStringValue());
System.out.println(cells.get("F70").getStringValue());
System.out.println(cells.get("E167").getStringValue());
System.out.println(cells.get("F167").getStringValue());
System.out.println(cells.get("G167").getStringValue());
//workbook.save("f:\\files\\out1.xlsm");

console output:

77.73
77.73
77.73
1.58
1.58
1.58
163,568
163,568
163,568
After calculating formulas
77.73
77.73
77.73
1.58
1.58
1.58
163,568
163,568
163,568

Could you please provide more details and sample code on how we could reproduce the issue on our end?

gkamdar · December 18, 2023, 7:13pm

@amjad.sahi : I have attached two files. they both have same input values and when I use the code mentioned above I get different results. you can see the output sheet for both the excels they are mismatched.

amjad.sahi · December 18, 2023, 7:29pm

@gkamdar,

I got same (output) results when using your both files. Please run the sample code (I shared in my previous post, please update file path to the Excel file(s) for your environment) and paste the console output for our reference. By the way, could you please also print the Aspose.Cells version that you are using in your application, see the document with example on how to print/get the version of Aspose.Cells for Java that is being used on your end dynamically.

gkamdar · December 18, 2023, 8:21pm

test-example.zip (1.4 MB)

@amjad.sahi : Pls find attached the sample excel file that I used and the screenshot attached as mentioned from you.
I see mismatches as seen below.

95.75
75.63
66.28
1.90
1.90
1.90
111,429
169,997
210,363
After calculating formulas
77.73
67.47
62.58
1.58
1.58
1.58
163,568
204,450
235,435
Aspose Version
23.12.0

John.He · December 19, 2023, 3:00am

@gkamdar
Through further testing, we found that if you manually calculate the formula in Excel, you will also get the same result. This situation may be caused by not calling the formula calculation during file creation, resulting in some data errors. You can verify by manually calculating the formula in Excel and saving the file, and then running the test code. You will get the same result.

The output result:

23.12.0
77.73
77.73
77.73
1.58
1.58
1.58
163,568
163,568
163,568
After calculating formulas
77.73
77.73
77.73
1.58
1.58
1.58
163,568
163,568
163,568

gkamdar · December 19, 2023, 2:37pm

@John.He: what do you mean by “This situation may be caused by not calling the formula calculation during file creation,”
I have tried doing the below approch and it doesn’t work

call the workbook.calculateFormula(); when I am creating the file?
and then again
workbook.refreshDynamicArrayFormulas(true, new CalculationOptions());
workbook.calculateFormula();

I can see the mismatch for the same input when I run for 1st time. 2nd execution and henceforth it is stable.
Any inputs will be really helpful

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.