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

amjad.sahi · December 14, 2023, 5:27pm

Could you please provide your complete environment details? Are you using some dedicated Azure VM or something else? If true, then we have to verify your IP address, so you will require to kindly share the IP address for verifications.

gkamdar · December 15, 2023, 7:05pm

@amjad.sahi : I got the above issue sorted. But I still find the mismatches. will attach the files in sometime. Is there any additional code that I need to add to make this work?

amjad.sahi · December 15, 2023, 7:16pm

@gkamdar,

Good to know that your issue regarding maven repository downloads is sorted out now.

You meant there are still some differences in formula calculations by Aspose.Cells for Java? Please provide more details, sample Excel file(s), and sample code that you are working with. We will check your issue soon.

johnson.shi · December 18, 2023, 1:55am

@gkamdar

There may be some dynamic array formulas which need to be refreshed when data they depend on changed. So, please call refreshDynamicArrayFormulas(boolean calculate, CalculationOptions copts) before calculating formulas. Code sample:

Workbook wb = ...
...
wb.refreshDynamicArrayFormulas(true, new CalculationOptions());
wb.calculateFormula();
...

If you still find some formulas have not been calculated with expected results, please send us your template file and code to reproduce the issue. If you are using the file you have attached in this post, please point out the cells which have not been calculated correctly. With those resources we will look into the issue for you soon.

gkamdar · December 18, 2023, 5:20pm

@johnson.shi : Here is the attached files along with code. I can see mismatches for a lot of columns in output sheet (E167, F167, G167 , D57:D70, E57:E70,F57:F70)
I have colored the mismatch columns in red in both the excels.

I am using the <aspose-cells.version>23.12</aspose-cells.version>

Code snippet:

workbook.refreshDynamicArrayFormulas(true, new CalculationOptions());
workbook.calculateFormula();

Please let me know if you need any more details from my end.
excel-mismatch-example2.zip (3.0 MB)

Thanks,
Priyanka

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

@gkamdar,

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.