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

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

John.He · January 30, 2024, 1:43am

@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

gkamdar · January 30, 2024, 4:47pm

@John.He : thanks for update

amjad.sahi · January 30, 2024, 5:57pm

@gkamdar,

You are welcome.

Once we have an update on your issue, we will let you know.

amjad.sahi · February 2, 2024, 8:42am

@gkamdar,

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.

gkamdar · February 2, 2024, 11:22pm

@amjad.sahi : Thanks for the update

John.He · February 3, 2024, 1:34am

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

aspose.notifier · February 6, 2024, 11:24am

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

gkamdar · May 9, 2024, 7:14pm

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

amjad.sahi · May 9, 2024, 7:37pm

@gkamdar,

Thanks for the template files and details.

I tested your scenario/case using your provided (new) XSLM files with Aspose.Cells for Java v24.4 and it works absolutely fine. I used the following sample code with your files:
e.g.,
Sample code:

Workbook workbook = new Workbook("d:\\files\\Service-09-05-2024-1.xlsm");
Cells cells = workbook.getWorksheets().get("Output").getCells();
System.out.println(cells.get("E147").getStringValue());
System.out.println(cells.get("F147").getStringValue());
System.out.println(cells.get("G147").getStringValue());
System.out.println(cells.get("H147").getStringValue());

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

System.out.println("After calculating formulas=====");
System.out.println(cells.get("E147").getStringValue());
System.out.println(cells.get("F147").getStringValue());
System.out.println(cells.get("G147").getStringValue());
System.out.println(cells.get("H147").getStringValue());

output:

1,389
1,887
2,010
2,010
After calculating formulas=====
1,389
1,887
2,257
2,505

gkamdar · May 9, 2024, 9:10pm

Hi @amjad.sahi ,

The values are different, the sample file was already calculated one and we can see the difference

1,389
1,887
2,010
2,010
After calculating formulas=====
1,389
1,887
2,257
2,505

you can rerun the same file, with 4 cases

one time workbook calculation
two time workbook calculation
three time workbook calculation
fourth time workbook calculation.
you will see different results

Thanks,
Priyanka

John.He · May 10, 2024, 1:32am

@gkamdar
Open the file using Excel and manually perform formula calculations. The calculated values for cells E147, F147, G147, and H147 are as follows. Please refer to the attachment. result.png (82.7 KB)

By using sample files and the following test code to test on the latest version v24.4, we can obtain the correct results.

Workbook workbook = new Workbook(filePath + "Service-09-05-2024-1.xlsm");
Cells cells = workbook.getWorksheets().get("Output").getCells();
System.out.println(cells.get("E147").getStringValue());
System.out.println(cells.get("F147").getStringValue());
System.out.println(cells.get("G147").getStringValue());
System.out.println(cells.get("H147").getStringValue());

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

System.out.println("After calculating formulas=====1");
System.out.println(cells.get("E147").getStringValue());
System.out.println(cells.get("F147").getStringValue());
System.out.println(cells.get("G147").getStringValue());
System.out.println(cells.get("H147").getStringValue());

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

System.out.println("After calculating formulas=====2");
System.out.println(cells.get("E147").getStringValue());
System.out.println(cells.get("F147").getStringValue());
System.out.println(cells.get("G147").getStringValue());
System.out.println(cells.get("H147").getStringValue());


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

System.out.println("After calculating formulas=====3");
System.out.println(cells.get("E147").getStringValue());
System.out.println(cells.get("F147").getStringValue());
System.out.println(cells.get("G147").getStringValue());
System.out.println(cells.get("H147").getStringValue());

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

System.out.println("After calculating formulas=====4");
System.out.println(cells.get("E147").getStringValue());
System.out.println(cells.get("F147").getStringValue());
System.out.println(cells.get("G147").getStringValue());
System.out.println(cells.get("H147").getStringValue());

The output:

1,389
1,887
2,010
2,010
After calculating formulas=====1
1,389
1,887
2,257
2,505
After calculating formulas=====2
1,389
1,887
2,257
2,505
After calculating formulas=====3
1,389
1,887
2,257
2,505
After calculating formulas=====4
1,389
1,887
2,257
2,505

gkamdar · May 10, 2024, 7:22pm

@John.He

I rechecked the excel using the v24.4 aspose version and I am still able to see the mismatch.
Pls use the attached excel for the execution as given in previous reply and you will be able to see the difference in values for output tab cells E147, F147, G147, H147

v24.4.zip (1.2 MB)

Pls let me know if you need any more details.

Thanks,
Priyanka

amjad.sahi · May 10, 2024, 8:10pm

@gkamdar,

Thanks for the new XLSM file.

After an initial test, I am able to reproduce the issue as you mentioned by using your sample file and following sample code. I found incorrect results after formula calculations.


        Workbook workbook = new Workbook("d:\\files\\sample_firstexecution_v24.4.xlsm");

        Cells cells = workbook.getWorksheets().get("Output").getCells();
        System.out.println(cells.get("E147").getStringValue());
        System.out.println(cells.get("F147").getStringValue());
        System.out.println(cells.get("G147").getStringValue());
        System.out.println(cells.get("H147").getStringValue());

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

        System.out.println("After calculating formulas=====1");
        System.out.println(cells.get("E147").getStringValue());
        System.out.println(cells.get("F147").getStringValue());
        System.out.println(cells.get("G147").getStringValue());
        System.out.println(cells.get("H147").getStringValue());

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

        System.out.println("After calculating formulas=====2");
        System.out.println(cells.get("E147").getStringValue());
        System.out.println(cells.get("F147").getStringValue());
        System.out.println(cells.get("G147").getStringValue());
        System.out.println(cells.get("H147").getStringValue());


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

        System.out.println("After calculating formulas=====3");
        System.out.println(cells.get("E147").getStringValue());
        System.out.println(cells.get("F147").getStringValue());
        System.out.println(cells.get("G147").getStringValue());
        System.out.println(cells.get("H147").getStringValue());

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

        System.out.println("After calculating formulas=====4");
        System.out.println(cells.get("E147").getStringValue());
        System.out.println(cells.get("F147").getStringValue());
        System.out.println(cells.get("G147").getStringValue());
        System.out.println(cells.get("H147").getStringValue());

output:

70,238
72,872
73,808
78,108
After calculating formulas=====1
1,389
1,887
2,257
2,505
After calculating formulas=====2
1,389
1,887
2,257
2,505
After calculating formulas=====3
1,389
1,887
2,257
2,505
After calculating formulas=====4
1,389
1,887
2,257
2,505

We require thorough evaluation of the issue. 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-45980

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 · May 10, 2024, 9:57pm

@amjad.sahi ,
thanks for the update. We are using the licensed version of the aspose so the subscription should be with paid support. If you need any more info I will let you know.

As I mentioned this is blocking our work, quick support from your end will be really appreciated.

Thanks,
Priyanka