Bug 74747 - FILESAVE xlsx export saves incorrect formula results when the result is text.
Summary: FILESAVE xlsx export saves incorrect formula results when the result is text.
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.0.4 release
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Kohei Yoshida
URL:
Whiteboard: target:4.3.0 target:4.2.3
Keywords:
: 75326 75648 (view as bug list)
Depends on:
Blocks:
 
Reported: 2014-02-09 14:08 UTC by Steve Bickle
Modified: 2014-03-18 22:35 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments
Example file as originally saved from Excel (10.37 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-02-09 15:24 UTC, Steve Bickle
Details
file created in LO that results in a defect xlsx when exporting (7.65 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-02-26 15:58 UTC, Sven-Jacobi
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Steve Bickle 2014-02-09 14:08:58 UTC
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
Comment 1 Julien Nabet 2014-02-09 14:36:19 UTC
Could you attach the xlsx file for those who, like me, don't have MsOffice?
Comment 2 Steve Bickle 2014-02-09 14:47:43 UTC
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>
Comment 3 Steve Bickle 2014-02-09 15:24:50 UTC
Created attachment 93713 [details]
Example file as originally saved from Excel
Comment 4 Julien Nabet 2014-02-09 15:44:53 UTC
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.
Comment 5 Steve Bickle 2014-02-09 17:17:25 UTC
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.
Comment 6 Julien Nabet 2014-02-09 19:01:16 UTC
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?
Comment 7 m.a.riosv 2014-02-10 01:56:22 UTC
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.
Comment 8 m.a.riosv 2014-02-21 23:45:31 UTC
*** Bug 75326 has been marked as a duplicate of this bug. ***
Comment 9 Sven-Jacobi 2014-02-26 15:58:06 UTC
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.
Comment 10 Kohei Yoshida 2014-03-10 18:34:41 UTC
(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.
Comment 11 Kohei Yoshida 2014-03-10 19:00:26 UTC
I'm looking into this (the originally reported problem).
Comment 12 Kohei Yoshida 2014-03-10 19:01:43 UTC
More fitting summary.
Comment 13 Kohei Yoshida 2014-03-10 21:29:26 UTC
I also discovered a bug in importing cached string formula results while writing a unit test.  I'll fix that one here as well.
Comment 14 Commit Notification 2014-03-10 21:31:23 UTC
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.
Comment 15 Commit Notification 2014-03-10 21:31:36 UTC
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.
Comment 16 Commit Notification 2014-03-10 21:31:53 UTC
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.
Comment 17 Commit Notification 2014-03-10 21:32:06 UTC
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.
Comment 18 Kohei Yoshida 2014-03-10 21:32:18 UTC
4.2 backport requests. Note there are 2 commits here.

https://gerrit.libreoffice.org/8526
https://gerrit.libreoffice.org/8527
Comment 19 Kohei Yoshida 2014-03-10 21:36:22 UTC
*** Bug 75648 has been marked as a duplicate of this bug. ***
Comment 20 Commit Notification 2014-03-12 00:10:51 UTC
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.
Comment 21 Commit Notification 2014-03-12 00:11:06 UTC
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.
Comment 22 Kohei Yoshida 2014-03-12 00:24:21 UTC
Fixed.
Comment 23 m.a.riosv 2014-03-18 22:35:07 UTC
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