Recalculate formulas settings on opening into MS Excel in Java

Aspose.Cells Product Family
1

Hi Aspose Cells team,

  • Open input.xlsx in Excel, check Formulas / Calculation Options: note that it is set to “Automatic”

  • Open input.xlsx with Aspose Cells Java (18.12-19.1): note that reCalculateOnOpen=true
    Workbook inputWorkBook = new Workbook(“input.xlsx”);
    Assert.assertEquals(inputWorkBook.getSettings().getReCalculateOnOpen(), true);

  • Open input.xlsx with Aspose Cells Java (19.2-19.9): note that reCalculateOnOpen=false
    Workbook inputWorkBook = new Workbook(“input.xlsx”);
    Assert.assertEquals(inputWorkBook.getSettings().getReCalculateOnOpen(), false);
    … which is wrong!

Can you check on this issue?
May be related to Workbook setting "ReCalculateOnOpen" not persisted - #10 by TarasTielkes

Regards,
Jean.input.zip (28.0 KB)

2

@jeankst,

Thanks for the template file and details.

After an initial test, I am able to reproduce the issue as you mentioned by using the sample code with your template file. The Workbook’s setting “ReCalculateOnOpen” is retrieved as “false” instead of “true”:
e.g
Sample code:

System.out.println(CellsHelper.getVersion());
        Workbook workbook = new Workbook("f:\\files\\Input.xlsx");
        System.out.println(workbook.getSettings().getReCalculateOnOpen());//false - Not Ok

I have logged a ticket with an id “CELLSJAVA-43030” for your issue. We will look into it soon.

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

3

@jeankst,

We evaluated your issue further. This is not an issue with Aspose.Cells. We found the default value is false in the template file, so Aspose.Cells is retrieving correct value.

4

Hi Amjad,
Sorry but how did you find that the default value is false in the template?
If you open input.xlsx in Excel, under Formulas / Calculation Options, value is set to “Automatic”
So how can “Automatic” translate to ReCalculateOnOpen=false in Aspose cells?
Regards,
Jean.

5

@jeankst,

Well, the relevant properties are there for some nodes e.g calcPr in the source .xml file(s).

Let me provide you complete details of the relevant properties for your reference.

calcMode

Specifies when the application should calculate formulas in the workbook.

The default value for this attribute is "auto."

fullCalcOnLoad

Specifies a boolean value that indicates whether the application shall perform a full

recalculation when the workbook is opened. After load and successful calculation, the

application should set this value to false. The application should set this value to true

when cell formulas or values are modified by another process while the application has

the workbook opened.

A value of on, 1, or true indicates the application will perform a full recalculation of

workbook values when the workbook is opened.

A value of off, 0, or false indicates the application will not perform a full recalculation

when the workbook is opened.

Note: If manual calcMode is true, then a full recalculation will not be performed on load,

even when this attribute is set.

Conclusion

CalcMode and fullCalcOnLoad are two attributes and fullCalcOnLoad is not visible in MS Excel UI. And please check the Note about fullCalcOnLoad, only calcMode is Automatic, fullCalcOnLoadd works.

Hope, this helps a bit.