Problem description: Sheet references are broken when a document created in Excel as xlsx is opened and subsequently re-saved as an xlsx using Calc. If the document is opened and saved in calc then subsequently opened in Calc the referenced text appears as 0. Opening the file in Excel the text shows correctly, saving the file from Excel fixes the problem. Steps to reproduce: 1. Create a new spreadsheet in Excel 2010 2. Enter 'Example text' in cell a1 on sheet 1 3. On sheet 2 in cell b1 enter a reference to sheet 1 a1 4. save the sheet as docx and close Excel 5. Open the sheet in Calc. The cell sheet 2 b1 shows the expected text. 6. Make a change to another cell so that the sheet has changed 7. click the save button 8. click the 'Use Microsoft Excel 2007/2010/2013 XML Format' 9. close the Calc application 10. Open the file again in Calc 11. Observer the presented value in sheet 2 cell b1. Current behavior: After opening the re-saved sheet in calc the referenced text in b1 appears as '0' Expected behavior: The referenced text should appear in cell b1
Could you attach the xlsx file for those who, like me, don't have MsOffice?
Having inspected the XML for the cell with a reference from both the original save in Excel and the subsequent save in Calc, this is what I've found: Original Excel file: <row r="1" spans="2:2" x14ac:dyDescent="0.25"> <c r="B1" t="str"> <f>Sheet1!A1</f> <v>Example text</v> </c> </row> From the Calc save: <row r="1" customFormat="false" ht="15" hidden="false" customHeight="false" outlineLevel="0" collapsed="false"> <c r="B1" s="0" t="n"> <f aca="false">Sheet1!A1</f> <v>0</v> </c> </row>
Created attachment 93713 [details] Example file as originally saved from Excel
Thank you Steve for your feedback. On pc Debian x86-64 with master sources updated yesterday, I can reproduce the problem. I noticed that if I saved first in ods format, then xlsx format, it worked well.
Julien, That is a possible work around I hadn't considered. However the in use case where I came across the bug I am working with a documents that are shared mainly by Microsoft Office users, I'm a little concerned that using the work around might cause other unexpected changes to the file. Also its somewhat alarming to a novice user of LibreOffice. I did notice that if the document was originated by Calc in the docx format, this problem does not appear.
Steve: of course I didn't mean the situation was ok thanks to this possible workaround, that's why I said "noticed" :-) Kohei/Markus/Eike: one for you?
With the option in Menu/Tools/Options/LibreOffice calc/Formula - Recalculation on file load - Excel 2007 and newer - Always recalculate / Prompt(Yes), it is showed right. Hard recalc [Shift+Ctrl+F9] also update properly. For me saving with the right value showed, then reopens fine. (xlsx). I'm not sure if it is really a bug, maybe the option for recalculate at opening xlsx files is for this situations.
*** Bug 75326 has been marked as a duplicate of this bug. ***
Created attachment 94774 [details] file created in LO that results in a defect xlsx when exporting I created a file in LO that references to another document and results in a defect xlsx when exporting. MSO2013 is claiming that the document needs to be repaired. The bug is reproducible when inserting such a reference as formula and as definedName. It seems that following characters: [] are not encoded properly.
(In reply to comment #9) > Created attachment 94774 [details] > file created in LO that results in a defect xlsx when exporting > > I created a file in LO that references to another document and results in a > defect xlsx when exporting. MSO2013 is claiming that the document needs to > be repaired. The bug is reproducible when inserting such a reference as > formula and as definedName. It seems that following characters: [] are not > encoded properly. This needs to be filed as a separate bug to avoid mixing different issues. Thanks in advance for your help.
I'm looking into this (the originally reported problem).
More fitting summary.
I also discovered a bug in importing cached string formula results while writing a unit test. I'll fix that one here as well.
Kohei Yoshida committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=c59b3d6c5c8096486730007d9b9b053793b90b1e fdo#74747: Write test for this first. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Kohei Yoshida committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=304e6144c66affd7adcea66f72fb5757eddfb12f fdo#74747: Test cached numeric results too just to be safe. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Kohei Yoshida committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=aa5ad7b8096cd15a55c467b1a23d03849aeb870d fdo#74747: Make use of cached string formula results. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Kohei Yoshida committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=c1dc7576c18cc534e1934459f5fb210091a5b484 fdo#74747: Correctly inspect formula result value for xlsx export. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
4.2 backport requests. Note there are 2 commits here. https://gerrit.libreoffice.org/8526 https://gerrit.libreoffice.org/8527
*** Bug 75648 has been marked as a duplicate of this bug. ***
Kohei Yoshida committed a patch related to this issue. It has been pushed to "libreoffice-4-2": http://cgit.freedesktop.org/libreoffice/core/commit/?id=13c4e40ad0b199199e28e01103e0fc67c4a0bf14&h=libreoffice-4-2 fdo#74747: Make use of cached string formula results. It will be available in LibreOffice 4.2.3. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Kohei Yoshida committed a patch related to this issue. It has been pushed to "libreoffice-4-2": http://cgit.freedesktop.org/libreoffice/core/commit/?id=69ecdad805281b2cb6ec2437da18daa19576deae&h=libreoffice-4-2 fdo#74747: Correctly inspect formula result value for xlsx export. It will be available in LibreOffice 4.2.3. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Fixed.
Thanks Kohei. Verified: Win7x64Ultimate Version: 4.2.3.1 Build ID: 3d4fc3d9dbf8f4c0aeb61498a81f91c5b7922f13 Version: 4.2.4.0.0+ Build ID: e1823627f35e4419880769fdd05acddbd0a9c25c TinderBox: Win-x86@42, Branch:libreoffice-4-2, Time: 2014-03-18_14:25:19 Version: 4.3.0.0.alpha0+ Build ID: 12ae7672f285da1d4c730315e8db23b3396b71cc TinderBox: Win-x86@39, Branch:master, Time: 2014-03-14_00:18:00