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

Aspose.Cells Product Family
,
82

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

83

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

84

@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

85

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