I'm generating xlsx spreadsheets with PHPExcel. These open perfectly in Excel 2011, LibreOffice 4.2.0 and Apple Numbers. In LibreOffice 4.2.5 the cells display a 0 value for formulae referencing other sheets. If I edit a cell and retype any character, the calculation then shows the correct value from the referenced cell. To be clear, all I have to do is edit the cell in a way that does not change it at all and it starts working.
I notice that LibreOffice changes the cell contents on import. It is originally created like this:
and LibreOffice converts it to:
That formula is perfectly valid, but always shows 0. If I do a null edit (for example, delete the 9 at the end then retype it) and hit return, it shows the correct value, and I have to do this to all cells before they work.
I've also found that cross-sheet named references don't work at all when imported from Excel format, for example:
is flagged as an error, even though the named range exists in the target sheet according to the names dialogue (insert -> names -> manage).
Hi Marcus, please read: https://help.libreoffice.org/Common/Formula_2
I don't see that that applies - the formula is converted correctly from Excel to Libreoffice format as that page describes, but it fails to work after conversion until it is edited.
So two things:
1. Please attach a test document.
2. Please keep 1 report = 1 issue (ie report the second issue separately)
Marking as NEEDINFO - once you attach a simple test document mark as UNCONFIRMED. Thanks
We don't support to address sheet local names through the 'sheet name".rangename syntax.
Most likely you are not generating cached values in your files so you need to change the setting for XLSX import to always recalculate.
Dear Bug Submitter,
This bug has been in NEEDINFO status with no change for at least 6 months. Please provide the requested information as soon as possible and mark the bug as UNCONFIRMED. Due to regular bug tracker maintenance, if the bug is still in NEEDINFO status with no change in 30 days the QA team will close the bug as INVALID due to lack of needed information.
For more information about our NEEDINFO policy please read the wiki located here:
If you have already provided the requested information, please mark the bug as UNCONFIRMED so that the QA team knows that the bug is ready to be confirmed.
Thank you for helping us make LibreOffice even better for everyone!
This NEEDINFO message was generated on: 2015-07-18
Created attachment 117414 [details]
Spreadsheet to demonstrate uncalculated value
Opening the attached doc in LibreOffice 220.127.116.11, you will see cell A1 on sheet 1 contains a zero value. It's actually a formula: "=$sheet2!A1" (converted from the Excel formula "='sheet2'!A1") referring to cell A1 on the second sheet, which contains the literal value 123, which is not the same as the displayed 0. Forcing a recalculation (via F9 or menu command) does NOT update the value. If you edit cell A1 on sheet 1, select the last "1" and then retype it and hit return (i.e. not actually making a change), it updates to show the correct referenced value.
Just FYI, the spreadsheets that trigger this problem are generated using PHPExcel. That the cached values are not set correctly is a bug in PHPExcel which has been fixed here: https://github.com/PHPOffice/PHPExcel/issues/433 but LibreOffice's behaviour when dealing with them is still a bug.
Never independently confirmed - setting to UNCONFIRMED.
*** Bug 92939 has been marked as a duplicate of this bug. ***
Reproducible with Version: 18.104.22.168.alpha1+
Build ID: 74d4168f8830f7bbec6b784c3fb774296d9adafa
TinderBox: Linux-rpm_deb-x86_64@46-TDF, Branch:master, Time: 2015-07-22_06:14:00
Hard recalc CTRL+SHIFT+F9 helps, after recalc is value 123
Works correct with LO 3.5, regression
(In reply to m.a.riosv from comment #10)
> *** Bug 92939 has been marked as a duplicate of this bug. ***
I submitted bug 92939 and it is NOT a duplicate of this bug. However, I have also observed the behavior reported in this bug where some of my formulas converted from Excel 5.0 do not work in Calc (22.214.171.124) until I do a manual edit of those formulas as described by Marcus Bointon. A Hard Recalculate doesn't do it, and neither does a Replace anything with itself. It's an annoyance, but once fixed by manual editing a converted workbook works OK thereafter as long as one saves as .ods. What's odd to me is that not all converted formulas experience this behavior, and there don't appear to be substantive differences between those which do and those which don't.
Migrating Whiteboard tags to Keywords: (bibisectRequest)
Minor: Can slow down but won't prevent high quality/professional work;
High: I think that this is deserved as it's really common functionality and a user might not see the problem (and they might not know that a hard refresh will resolve it)
ee1feb893a4fe94061927eb67f65f82d01e1d047 is the first bad commit
Author: Bjoern Michaelsen <firstname.lastname@example.org>
Date: Sun May 11 23:04:20 2014 +0000
Author: Caolán McNamara <email@example.com>
AuthorDate: Tue Mar 11 10:27:41 2014 +0000
Commit: Caolán McNamara <firstname.lastname@example.org>
CommitDate: Tue Mar 11 12:18:55 2014 +0000
convert load template dialog to .ui
and merge the expander and the "show preview" checkbox
to be the same thing, i.e. expanded is showing, and
not expanded is not showing
:100644 100644 aa54328bb749478eb34c9d6f21114aaf4934a46c f5bf072bce82b837b5f2d70e57a349193f51a94b M ccache.log
:100644 100644 910614ba2ac6f40e795b3a8f637968f83b6673b3 5f305f68af72f960c65aa755b79c323c06295373 M commitmsg
:100644 100644 36312cbc0fd2b385bd738f62b53b6e8e91d58e13 13c8e704ccee359e7ef7ade6d0bbe9ab2a21d474 M make.log
:040000 040000 73850cba32ed62160ab74ed837d4262751582417 0b8277ec6ca461462f3986949b4ce83effb363aa M opt
# bad: [423a84c4f7068853974887d98442bc2a2d0cc91b] source-hash-c15927f20d4727c3b8de68497b6949e72f9e6e9e
# good: [65fd30f5cb4cdd37995a33420ed8273c0a29bf00] source-hash-d6cde02dbce8c28c6af836e2dc1120f8a6ef9932
git bisect start 'latest' 'oldest'
# good: [e02439a3d6297a1f5334fa558ddec5ef4212c574] source-hash-6b8393474974d2af7a2cb3c47b3d5c081b550bdb
git bisect good e02439a3d6297a1f5334fa558ddec5ef4212c574
# good: [4850941efe43ae800be5c76e1102ab80ac2c085d] source-hash-980a6e552502f02f12c15bfb1c9f8e6269499f4b
git bisect good 4850941efe43ae800be5c76e1102ab80ac2c085d
# good: [a900e72b6357882284c5955bdf939bf14269f5fb] source-hash-dd1050b182260a26a1d0ba6d0ef3a6fecc3f4e07
git bisect good a900e72b6357882284c5955bdf939bf14269f5fb
# skip: [e80660c5a1d812cd04586dae1f22767fc3778c4a] source-hash-07c60c8ee2d1465544a6a39e57bc06b3690b8dfb
git bisect skip e80660c5a1d812cd04586dae1f22767fc3778c4a
# bad: [df9bcaed2faa2a8d11b19f877cdff3a12a887278] source-hash-6ba9692d8bbe3e3c245aca9a7c928e81178d05f1
git bisect bad df9bcaed2faa2a8d11b19f877cdff3a12a887278
# bad: [9d57c189d74551d2b3770cc81139ea10a62e672f] source-hash-5b5e62650354788e50b44f32c22b687b2018aba9
git bisect bad 9d57c189d74551d2b3770cc81139ea10a62e672f
# bad: [ce81582766413e76a63c047bfd6227ab12fcd866] source-hash-3d1b1eea83703919c43620f9adef05e5b24c4bed
git bisect bad ce81582766413e76a63c047bfd6227ab12fcd866
# bad: [4e0843c411a14e3065f96f196eeb4d603664f97f] source-hash-51605bf98220d7e54dee20af17c33cebe23a0813
git bisect bad 4e0843c411a14e3065f96f196eeb4d603664f97f
# bad: [6d436a9d8ec82d3192b51a63551bc4640634402d] source-hash-50d20866aa90150680e6d39998081fc148638c73
git bisect bad 6d436a9d8ec82d3192b51a63551bc4640634402d
# good: [3dda83fc3a43afc6af7f5c0ffd029e610ec1b9a3] source-hash-c59b3d6c5c8096486730007d9b9b053793b90b1e
git bisect good 3dda83fc3a43afc6af7f5c0ffd029e610ec1b9a3
# bad: [ee1feb893a4fe94061927eb67f65f82d01e1d047] source-hash-f3609ac092bc520825bfd6b71ed20ecaba293317
git bisect bad ee1feb893a4fe94061927eb67f65f82d01e1d047
# first bad commit: [ee1feb893a4fe94061927eb67f65f82d01e1d047] source-hash-f3609ac092bc520825bfd6b71ed20ecaba293317
I believe this is not a bug, and "0" is the cached value of the cell.
Marcus, can you verify that, and close this as NOTABUG if you agree? (possibly the "<v>0</v>" in the sheet's XML after the formula?)
Nevertheless, here is the bibisect result done with 43max repo for reference.
c1b101da259e405d30710e6a4c131f5b0c5a4910 is the first bad commit
Author: Matthew Francis <email@example.com>
Date: Thu May 28 20:02:49 2015 +0800
Author: Kohei Yoshida <firstname.lastname@example.org>
AuthorDate: Mon Mar 10 17:03:52 2014 -0400
Commit: Kohei Yoshida <email@example.com>
CommitDate: Mon Mar 10 17:28:54 2014 -0400
fdo#74747: Make use of cached string formula results.
Just like we do with cached numeric formula results.
# bad: [74b89c3193673ba9897dc4a4541500ef6e8d9bf7] source-hash-8f97326bdd3f42fc82aa5e1989fd03b0af1daf64
# good: [9c392cfdfe6e9a9bce98555ea989283a957aa3ad] source-hash-fc8f44e82de4ebdd50ac5fbb9207cd1a59a927e3
git bisect start 'latest' 'oldest'
# good: [e289d9d328719fd70e9a2680fd0e4f586a97b3be] source-hash-3c0a7cf4f67720f2cca2c4eb543f838d5b644e7f
git bisect good e289d9d328719fd70e9a2680fd0e4f586a97b3be
# bad: [3e807472869ed7d72b026c12cd1e7c3cb990591f] source-hash-6390dd9777ff63ca75a088e56dd444a355439343
git bisect bad 3e807472869ed7d72b026c12cd1e7c3cb990591f
# bad: [c57068b5699ab30f5721cd6a43f146c3d08d98e6] source-hash-549c3b81190d64ec23254f448ebf69520dd76da9
git bisect bad c57068b5699ab30f5721cd6a43f146c3d08d98e6
# good: [57c2bb1c65ab1ccf260a4c9778672db67569f5bc] source-hash-41ad6531c75a53262933aecb0685f5c876de7251
git bisect good 57c2bb1c65ab1ccf260a4c9778672db67569f5bc
# bad: [9fe110812ebde0d64f2156900e12f2f2d8f75e3f] source-hash-e8f00662a8dbeec61bed08aa6706af54b6d55f14
git bisect bad 9fe110812ebde0d64f2156900e12f2f2d8f75e3f
# good: [57397f2c8b5991e3aafca8c9948b82af80755092] source-hash-21ece490ff555606cb773fd904fd01b01e384fb6
git bisect good 57397f2c8b5991e3aafca8c9948b82af80755092
# skip: [bd8d8893e0aa76bd09b61504b4ab92590bf8426e] source-hash-10041dc7c378901d51c6a72afed155528ed79de0
git bisect skip bd8d8893e0aa76bd09b61504b4ab92590bf8426e
# good: [c40875e18ae8157729f314468016f84299b819b9] source-hash-bf8441444cb00fe89acbd2c1d6f5abcc0d4e1247
git bisect good c40875e18ae8157729f314468016f84299b819b9
# good: [df44ad42c855447f3ad20016c2b5654bf2b980cf] source-hash-c59b3d6c5c8096486730007d9b9b053793b90b1e
git bisect good df44ad42c855447f3ad20016c2b5654bf2b980cf
# bad: [fc4e65744f8f3187b460fb5bc1309d903f2afaa0] source-hash-caf8f777e38e75641dcd13461053ba0bfd44500f
git bisect bad fc4e65744f8f3187b460fb5bc1309d903f2afaa0
# bad: [9a99a6fd405e10a49c27473a29f7530edd86b181] source-hash-bad51b0613db4a9152ffebe43a74a83af42d532b
git bisect bad 9a99a6fd405e10a49c27473a29f7530edd86b181
# bad: [310cb0e449bd066ad10961fd149e6d35573542e1] source-hash-e172825b535da17c1a1d70ee1caebf43c933f39a
git bisect bad 310cb0e449bd066ad10961fd149e6d35573542e1
# bad: [9b1d7115da90217cf2f453271281e93d1aff6a96] source-hash-c1dc7576c18cc534e1934459f5fb210091a5b484
git bisect bad 9b1d7115da90217cf2f453271281e93d1aff6a96
# good: [63c2354dae0a4107948fa09f52e70a8573ea8768] source-hash-304e6144c66affd7adcea66f72fb5757eddfb12f
git bisect good 63c2354dae0a4107948fa09f52e70a8573ea8768
# bad: [c1b101da259e405d30710e6a4c131f5b0c5a4910] source-hash-aa5ad7b8096cd15a55c467b1a23d03849aeb870d
git bisect bad c1b101da259e405d30710e6a4c131f5b0c5a4910
# first bad commit: [c1b101da259e405d30710e6a4c131f5b0c5a4910] source-hash-aa5ad7b8096cd15a55c467b1a23d03849aeb870d
I don't see that this can be reasonably classified as "not a bug". It's unnecessarily different to how other spreadsheets work (particu;arly Excel), it's unexpected behaviour, it's a regression from older versions of LibreOffice, there's no practical workaround, and the problem is still present in version 126.96.36.199. Do you really think it's reasonable to expect users to edit every single cell in a sheet manually in order to make them work correctly? At the very least a hard-recalc should fix it (what does a hard-recalc do, if not ignore cached values??), but even that doesn't work. It's serious enough that we have to tell our users that they can't use LibreOffice to open these spreadsheets.
Okay, I tested hard recalc, and F9 indeed doesn't work as expected in v188.8.131.52 (but Ctrl-Shift-F9 does), that is certainly a bug, and most likely unrelated to the change.
However, both F9 and Ctrl-Shift-F9 works as expected in v184.108.40.206 with the example sheet.
There is definitely another workaround if you change Tools -> Options -> LibreOffice Calc -> Formula -> Recalculation on File Load: Excel 2007 and newer.
I'd ask how you would expect cached values to work, and why 0-s are stored in the file instead of reasonable values? It makes no sense to have invalid values cached there, and the point of having cached values is exactly that the software wouldn't have to calculate formulas during file opening. How does Excel work in this case that is different?
(In reply to Aron Budea from comment #17)
> Okay, I tested hard recalc, and F9 indeed doesn't work as expected in
> v220.127.116.11 (but Ctrl-Shift-F9 does), that is certainly a bug, and most likely
> unrelated to the change.
> However, both F9 and Ctrl-Shift-F9 works as expected in v18.104.22.168 with the
> example sheet.
> There is definitely another workaround if you change Tools -> Options ->
> LibreOffice Calc -> Formula -> Recalculation on File Load: Excel 2007 and
> I'd ask how you would expect cached values to work, and why 0-s are stored
> in the file instead of reasonable values? It makes no sense to have invalid
> values cached there, and the point of having cached values is exactly that
> the software wouldn't have to calculate formulas during file opening. How
> does Excel work in this case that is different?
F9 is not a hard recalc. Only CTRL+SHIFT+F9 is. A soft recalc is only useful if you disable automatic recalculation as it only recalculates dirty cells. The hard recalculation (CTRL+SHIFT+F9) should correctly recalculate the cells.
The mentioned behaviour here is not a bug. We are using cached values and if the file contains wrong values there is nothing we can do.