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

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

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

@gkamdar
Please note that your issue/ticket was recorded in normal (free) support mode, where the issue is resolved on a first come, first served basis. The resolution time of a issue depends on the number of previously recorded issues or other tasks.

However, we have recorded your concerns and will definitely notify you once we have any updates in this regard.

Furthermore, in the mode we have already implemented, the ticket can only be upgraded to a certain extent. You may check our paid support option where issues are prioritized and posses highest priority if your issue is a real blocker.

We deeply apologize for any inconvenience caused to you.

johnson.shi · May 12, 2024, 2:47am

@gkamdar
We have investigated the issue of calculating formulas with the special template file. In fact ms excel does not re-calculate formulas completely even if you press “Calculate Now” when opening it in ms excel. It is very strange but we do not know what prevents the calculation. However, it can be proved by changing the calculation id directly in the template file:

Manual way:
Edit the xl/workbook.xml directly and change the calcId to smaller value(represents some lower version of ms excel so when you open this file by newer version of ms excel and press “Calculate Now” all formulas will be re-calculated). You may replace the part:
<calcPr calcId=“191028”…
to
<calcPr calcId=“0”…

Or by code:

            Workbook wb = new Workbook("sample_firstexecution_v24.4.xlsm");
            wb.Settings.FormulaSettings.CalculateOnOpen = true;
            wb.Save("Resaved.xlsm");

Then open the modified file with ms excel, you will find the formulas get calculated and give the same values with our component.

The strange part is, in fact other files, such as “Service-09-05-2024-1.xlsm”, “Service-09-05-2024-2.xlsm”, all have the same calcId and other settings for formulas, but they can be calculated automatically by ms excel. Unfortunately, we cannot find the reason of the strange behavior.

gkamdar · May 13, 2024, 12:08pm

@johnson.shi ,

Thanks for the details, but I am reading the file programmatically after the calculation so opening in another version of excel will not help.
Can you suggest any code change which will help me fix this problem.

Thanks,
Priyanka

johnson.shi · May 13, 2024, 1:43pm

@gkamdar

After setting CalculateOnOpen as “true” or changing calcId to smaller value, the re-saved file should work(re-calculated when opening it in ms excel):

wb.Settings.FormulaSettings.CalculateOnOpen = true;

or

wb.Settings.FormulaSettings.CalculationId = "0";

And the re-calculated results of the re-saved file in ms excel are same with the results of Aspose.Cells. So we think what you got after calculation of Aspose.Cells should be the correct result.

Or, do you mean the re-saved file cannot be calculated correctly? Would you please provide more details about your problem when opening it in ms excel so we can investigate it?