I have xlsx document which I modify in c# application set few cell values to get new calculated values and run libreoffice in headless mode to export xlsx file in pdf.
Problem is few values are calculated and few not. Only shared formula will recalculate others formula not.
Why this happening?
I run libreoffice 7.6 on windows 11, in cell application I don’t modify any formula just set cell value.
Hi @TJacken,
Welcome to this forum, ooops, sorry, to this question / answer page!
Just a suggestion, check the value of this parameter for Excel 2007 and newer:
Thanks for suggestion this works, but I run libreoffice in headless mode on docker so I can run only with default settings. So I need to run libreoffice with recalculate set to never recalculate.
Also when I open xlsx file with never recalculate settings some cells are calculated and some not. I think this is some problem with compatibility between Excel and Libreoffice.
This formula don’t work:
=D5-D3
And this works
=($B$5/C11)*2
And what is in the cells D5, D3?
Please upload a sample spreadsheet file here.
Cells only contain numbers D5 is 50 and D3 is 30.
In text format or they are numeric values?
Please uload sample file here.
Numeric values, will upload today when catch some time.
Something to try: Extract the user-profile to a LibreOffice with GUI, change settings and rewrite to the docker-image. Could be even easier, if we find out in wich file the setting is…
In registrymodification.xcu, the following values change accordingly when I play with “Recalculation on File Load”
0 means “always”
1 means “never”
2 means “prompt”
<item oor:path="/org.openoffice.Office.Calc/Formula/Load"><prop oor:name="ODFRecalcMode" oor:op="fuse"><value>1</value></prop></item>
<item oor:path="/org.openoffice.Office.Calc/Formula/Load"><prop oor:name="OOXMLRecalcMode" oor:op="fuse"><value>1</value></prop></item>Hi @Zizi64 here is file on this link, if you open file, cell D8 has 39 in Excel, but in libreoffice has 31 (old value).
Don’t save file in Excel after you open it, can you check now what is problem with formula in libreoffice?
Tools - Options - LibreOffice Calc - Formula - Recalculation on File load - …
The Default settings is “Never recalculate” for the “non-LibreOffice saved spreadsheet documents” and for the Excel type files. (Because a “foreign” file may contain incompatible formulas)
But how formula in D16 cell recalculate when load xlsx document in libreoffice? Why formula in cell D8 don’t work and formula in cell D16 works?
D16 not work for me… (D8 = 31, D16 = 26,4) But both cells work when I set the option to “Always…” before I open the downloaded sample file. ((D8 = 39, D16 = 30,2)
That is odd, for D16 I got 30,2 and for D8 I have 31 in libreoffice, in Excel i got correct values.
So only way to get recalculated values are to set always calculate?
I trust you for your calculation, but on my side it is odd to get partially calculated cells.
I have got same (not recalculated) results are in my LO 7.5.7 version.
Okay, thank you for your help. Will try to analzye problem tomorrow with simple excel file after update try to open it in excel and libreoffice and compare file before and after modification in open xml productivity tool.
