xls cells containing reference to a translated cell not updated, even after recalc ?

[Philippe Henri]

Hello,

Please see the attached example, i translate cells in column A. Cells in column D contain simply references to column A cells.

But these cells in column D are not updated ? (and excel is showing an “unprotected formula” green triangle)

(with okapi 1.43)

‌

[Philippe Henri]

Partial Solution : In fact a F9 does not refresh the formulas (probably because XLS thinks there is nothing to recalc as the file has been updated from behind the scene via OKAPI)…

BUT …on Mac , a Fn-Control-Option-F9 does a full recalc and update the cells (maybe the equivalent on PC is ctrl-alt-F9? )

Is it possible for okapi to activate the flag “something has been modified” in the file? To avoid to force the user finding this complex key combination to update…. (a very frustrating experience since we all know F9 but who knows the Fn-Control-Option-F9 ??)?

[Philippe Henri]

ah ! SUGGESTED SOLUTION WHICH FORCES THE FULL RECALC WHEN THE FILE IS OPENED:

in “xl/workbook.xml“ modify the “CalcPr” tag, add these attributes : <calcPr fullCalcOnLoad="1" calcCompleted="0" forceFullCalc="1" />

I tested with the above files, and it WORKS, can this be implemented please ?

ps: info found here:https://github.com/PHPOffice/PhpSpreadsheet/issues/456

‌

‌

[Denis Konovalyenko]

@Philippe Henri thank you for your investigation and proposal on approaching this issue!

I have also looked into this and found out that there can be another tag sheetCalcPr in the scope of a sheet part. And if its attribute fullCalcOnLoad is set to true, then formula values recalculation is taken place per this particular sheet. Furthermore, I found it reasonable to trigger this recalculation when cells are of str (string) type, which means that only string formulas are being used.

A related solution has been merged in the scope of pull request #660. So, I would appreciate it if you could check the functionality against the latest dev (snapshots should be available as well).

Thanks!