https://bugs.documentfoundation.org/show_bug.cgi?id=158175
Bug ID: 158175
Summary: FILEOPEN does not initialize some formulas/cells
correctly
Product: LibreOffice
Version: 7.6.2.1 release
Hardware: All
OS: All
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
Created attachment 190787
--> https://bugs.documentfoundation.org/attachment.cgi?id=190787&action=edit
Erroneous formula values after load.
Hi!
There are some very finicky circumstances where some formula are not evaluated
correctly when opening specific spreadsheets. I'm fairly sure it is a bug,
because simply reordering any of the sheets in the workbook will result in the
formulas in question to update and display the correct values.
History and debugging of the issue
1. First I've received an XLSX file with the issue of some "VLOOKUP" formulas
not working. Upon analysis I could discern that the file opened in MS Office
works as expected, but not in LibreOffice. Furthermore I noticed that saving
the file as ODS and opening that file would work as expected. This lead me to
believe there is an MS Office compatibility issue, but was proven wrong later.
2. With the original XLSX is opened, the formula evaluations that were wrong
had search values with trailing spaces. Removing the spaces seemed to fix the
issue, but after saving and opening the file, the fix would not always "stick".
I have not investigated this further, because trailing-spaces or not the
formula was valid and should work regardless.
3. The LibreOffice version that originally manifested the issue was Collabora
23.05 master (f64dcce7) while the same issue seemed to not be present in
desktop LibreOffice 7.5.1-7.5.7 or 7.6.2.1. So I bisected the sourcetree and
found that reverting bb9dcec7 on the master would "fix" the issue on the
Collabora branch. All this lead me to believe I've found the issue, so I
started to strip the privacy-sensitive information from the spreadsheet to
produce a submitable reproduction example.
4. By complete chance I've made just the "right" changes for the issue to
manifest even with the commit reverted and also in all of the mainline
LibreOffice versions I've mentioned. What is even more interesting, the issue
now is present regardless of the file being saved as XLSX or ODS.
5. Further investigation lead me to believe that the erroneous values are
wrongly assumed to be correct at FILEOPEN and they are not re-calculated to be
correct. The commit bb9dcec7 on the Collabora branch deals with a flag
`mbGeneratorKnownGood` set in `WorkbookGlobals::initialize()`. If this flag is
not set, the original XLSX is correctly loaded. So finally it seems that at
some point the formula results got stored in a way that gets persisted across
document saving and loading.
Reproduction
There are 5 sheets in the document, with the "RH" containing the issue
described. The faulty evaluations are in $RH.E45:E74. The cells E45, E47, E52,
E55, E61 and E69 of RH fail to evaluate and display "#N/A" while the other
cells display "0" instead of being empty.
To trigger some evaluation update and see what it should look like, just grab
the RH sheet and move it to be the last one for example.
Reproduction stability
The issue is not CPU/hardware dependent, I've reproduced on two different
desktops (AMD64) and an ARM single-board-device as well.
Original cause
After falling down the rabbit hole, it seems that there could have been a
specific operation (maybe the first XLSX opening by LibreOffice) that broke the
"stored" value of cells with the formula. I'm not sure if I can find that out.
I guess it would be nice if LibreOffice would fix the obvious inconsistency at
least when opening the document, even if we can't find out the original source
of the issue. I'll try to get a hold of the original XLSX file, before it was
ever opened in LibreOffice and see if there is any more information I can
gather.
Let me know what more information would be useful, if any.
Version: 7.6.2.1 (X86_64) / LibreOffice Community
Build ID: 60(Build:1)
CPU threads: 6; OS: Linux 6.1; UI render: default; VCL: kf5 (cairo+xcb)
Locale: en-US (en_US.UTF-8); UI: en-US
7.6.2-3
Calc: threaded
--
You are receiving this mail because:
You are the assignee for the bug.
