Bug 82135 - Cell references don't resolve in imported Excel sheet, but do after edit
Summary: Cell references don't resolve in imported Excel sheet, but do after edit
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.5.2 release
Hardware: All All
: high minor
Assignee: Not Assigned
URL:
Whiteboard: interoperability
Keywords: bibisected, bisected, regression
Depends on:
Blocks:
 
Reported: 2014-08-04 15:04 UTC by Marcus Bointon
Modified: 2017-05-24 18:05 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:

Attachments
Spreadsheet to demonstrate uncalculated value (7.10 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-07-24 09:35 UTC, Marcus Bointon 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Marcus Bointon 2014-08-04 15:04:00 UTC 
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:

='sheet name'!B9

and LibreOffice converts it to:

=$'sheet name'.B9

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:

='sheet name'!myrange

is flagged as an error, even though the named range exists in the target sheet according to the names dialogue (insert -> names -> manage).
Comment 1 ign_christian 2014-08-05 09:58:02 UTC 
Hi Marcus, please read: https://help.libreoffice.org/Common/Formula_2

Seems NOTABUG
Comment 2 Marcus Bointon 2014-08-05 10:03:08 UTC 
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.
Comment 3 Joel Madero 2014-08-06 01:49:51 UTC 
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
Comment 4 Markus Mohrhard 2014-12-29 16:11:48 UTC 
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.
Comment 5 QA Administrators 2015-07-18 17:36:31 UTC Comment hidden (obsolete)
Comment 6 Marcus Bointon 2015-07-24 09:35:43 UTC 
Created attachment 117414 [details]
Spreadsheet to demonstrate uncalculated value
Comment 7 Marcus Bointon 2015-07-24 09:43:49 UTC 
Opening the attached doc in LibreOffice 4.4.4.3, 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.
Comment 8 Marcus Bointon 2015-07-24 10:23:59 UTC 
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.
Comment 9 Joel Madero 2015-07-24 14:43:42 UTC 
Never independently confirmed - setting to UNCONFIRMED.
Comment 10 m_a_riosv 2015-07-26 22:17:16 UTC 
*** Bug 92939 has been marked as a duplicate of this bug. ***
Comment 11 raal 2015-07-29 17:20:59 UTC 
Reproducible with Version: 5.1.0.0.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
Comment 12 Alex 2015-07-30 00:57:21 UTC 
(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 (4.4.4.3) 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.
Comment 13 Robinson Tryon (qubit) 2015-12-14 05:32:44 UTC Comment hidden (obsolete)
Comment 14 Joel Madero 2016-01-09 21:46:29 UTC Comment hidden (obsolete)
Comment 15 Aron Budea 2016-08-31 04:59:47 UTC 
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
commit c1b101da259e405d30710e6a4c131f5b0c5a4910
Author: Matthew Francis <mjay.francis@gmail.com>
Date:   Thu May 28 20:02:49 2015 +0800

    source-hash-aa5ad7b8096cd15a55c467b1a23d03849aeb870d
    
    commit aa5ad7b8096cd15a55c467b1a23d03849aeb870d
    Author:     Kohei Yoshida <kohei.yoshida@collabora.com>
    AuthorDate: Mon Mar 10 17:03:52 2014 -0400
    Commit:     Kohei Yoshida <kohei.yoshida@collabora.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.
    
        Change-Id: Ib8b311b540caeb47d8c2162a456f7490c5882ad5


# 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
Comment 16 Marcus Bointon 2016-08-31 06:21:14 UTC 
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 5.1.4.2. 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.
Comment 17 Aron Budea 2016-08-31 14:28:54 UTC 
Okay, I tested hard recalc, and F9 indeed doesn't work as expected in v5.1.5.2 (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 v5.2.1.2 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?
Comment 18 Markus Mohrhard 2016-10-01 23:02:14 UTC 
(In reply to Aron Budea from comment #17)
> Okay, I tested hard recalc, and F9 indeed doesn't work as expected in
> v5.1.5.2 (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 v5.2.1.2 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?

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.