Bug Hunting Session
Bug 45286 - FILESAVE as .xlsx destroys link to linked contents
Summary: FILESAVE as .xlsx destroys link to linked contents
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.3.0 release
Hardware: All All
: medium major
Assignee: Eike Rathke
URL:
Whiteboard: target:4.4.0 target:4.3.2
Keywords:
: 78490 79436 (view as bug list)
Depends on:
Blocks: mab4.2
  Show dependency treegraph
 
Reported: 2012-01-26 12:46 UTC by Ruben Squartini
Modified: 2016-02-22 18:38 UTC (History)
14 users (show)

See Also:
Crash report or crash signature:


Attachments
Test kit (17.89 KB, application/x-zip-compressed)
2012-01-26 23:04 UTC, Rainer Bielefeld Retired
Details
New Test Kit (44.07 KB, application/x-zip-compressed)
2013-03-10 15:36 UTC, Rainer Bielefeld Retired
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Ruben Squartini 2012-01-26 12:46:09 UTC
When creating or saving a file as .xlsx, with cells referenced to other files, the format saved is wrong, turning the spreadsheet unusable.

LibO 3.4.5
OO: 340m1 Build:502



Format when defining the cross file reference:

='file:///I:/SAMPLE SHEET 2012.xls'#'Sheet 1'.N42 



Format when reopening the .xlsx file:

=['file:///i:/sample sheet 2012.xls']$#REF!.N42
Comment 1 Rainer Bielefeld Retired 2012-01-26 23:03:05 UTC
[Reproducible] with "LibreOffice 3.5.0 RC2 German UI/Locale [Build-ID: e371a95-bf68a13-5a1aa2b-d3c1ae9-b938258] on German WIN7 Home Premium (64bit), .xlsx shows "Err:509" instead of linked contents

I created a test kit containing "linked.xls" with small ocntents and several other documents linking to that contents.

Test results with documents from attached  test kit created with a.m. Version:

Linked contents shown correctly with LibO in , "withlink.ods", "withlinkexcel2003.xls"
Problems as described with "withlink2007.xlsx", "withlinkOOX.xlsx", "", "", 

Same Problems with with "LibreOffice Portable 3.3.0  - WIN7  Home Premium (64bit) German UI [OOO330m19 (Build:6) tag libreoffice-3.3.0.4]" 

Other Problems 
--------------
visible when I moved folder with sample documents to a differend HDD: 
"withlinkexcel2003.xls" shows wrong link when open:
='file:///T:/Users/Public/Documents/LibO/45286 - Wrong format for references when saving as .xlsx/new/linked.xls'#$Tabelle1.A1
Should be
='file:///T:/new/linked.xls'#$Tabelle1.A1

"withlinkOOX.xlsx"  shows wrong link when open:
=['file:///c:/users/public/documents/libo/45286 - wrong format for references when saving as .xlsx/new/linked.xls']$Tabelle1.A1
Should be
='file:///T:/new/linked.xls'#$Tabelle1.A1

Unfortunately neither tests with MS EXCEL Viewer nor tests with GNUMERIC delivered reliable FILEOPEN results, so that the only reliable result of my tests is "LibO can't open documents created with LibO", all other is beyond my skills. "Other Problems" might be related to the problem from original report or not, I can't tell.

@Kohei:
Please feel free to reassign (or reset Assignee to default) if it’s not your area or if provided information is not sufficient. Please set Status to ASSIGNED if you accept this Bug.
Comment 2 Rainer Bielefeld Retired 2012-01-26 23:04:12 UTC
Created attachment 56213 [details]
Test kit

See Comment 1
Comment 3 fmgtack+libreoffice 2012-05-07 06:11:23 UTC
Changed "platform": I can reproduce the issue on Ubuntu 12.04.

Also saving to xls breaks links to external sheets, but in a different way. I am still searching whether a bug report exists on that...
Comment 4 Rainer Bielefeld Retired 2013-03-10 15:03:03 UTC
[Reproducible] with "LibreOffice Portable 3.3.0  - WIN7  Home Premium (64bit) German UI [OOO330m19 (Build:6) tag libreoffice-3.3.0.4]", may be that never worked correctly.

Effect:
='file:///path/filename'#$Sheet1.A1       correct link in ods
=['file:///path/filename']$Sheet1.A1      destroyed link in .xlsx

Path and Filename are put into brackets, "#" before sheet reference lost
It does not matter to what filetype (.ods, .xls, .xlsx) the link leads.
Only 'save as .xlsx' seems affected, links stay fine when save as .ods, .xls

Still reproducible with LibO 3.6.5 (my tests) and 4.0.1.2 (dpierret@bigfoot.com).

Major due to dataloss problem
Comment 5 Rainer Bielefeld Retired 2013-03-10 15:36:37 UTC
Created attachment 76273 [details]
New Test Kit

No new info concerning core of the problem, but shows that it does not matter to what file type the link is ad that file save as .xls and .ods is not affected
Comment 6 Rainer Bielefeld Retired 2013-03-10 15:56:59 UTC
@Spreadsheet Team:
Please change  Status to ASSIGNED and add yourself to "Assigned To" if you accept this Bug or forward the Bug if it's not your turf (and remove others in team from CC).
Comment 7 dpierret 2014-01-06 19:43:23 UTC
Tested with LibreOffice v 4.1.4.2. Not yet fixed.
Comment 8 AxaRu 2014-05-13 14:53:06 UTC
LibreOffice Version: 4.2.3.3
Not yet fixed.
Comment 9 m.a.riosv 2014-05-30 13:37:36 UTC
*** Bug 78490 has been marked as a duplicate of this bug. ***
Comment 10 Kevin Suo 2014-05-30 14:50:31 UTC
Added to MAB4.2 list.
Comment 11 bfoman (inactive) 2014-05-30 15:57:19 UTC
*** Bug 79436 has been marked as a duplicate of this bug. ***
Comment 12 Andrew 2014-06-07 02:16:58 UTC
Hello again,
Thanks for identifying this as a duplicate bug.  Question remains that despite update to newest version the problem remains and it's a major issue in interoperability and usability.
are there ANY suggestions on how to resolve this? (other than take the whole world to libre?)
Is it purely related to translation to xlsx or is there no problem saving to xls?
Can someone PLEASE reply as I've spent many many hours on these spreadsheets and they are core documents to a major community festival.
TIA
Comment 13 Eike Rathke 2014-07-08 10:16:23 UTC
In OOXML external references need to be stored completely different, this isn't supported yet and needs to be newly implemented. See OOXML 12.3.9 External Workbook References Part, 18.17.2.3 Cell References and possibly other places talking about external references.

@Robin: as this bug is assigned to you, do you want to tackle that?
Comment 14 xaxiobrandish+freedesktop 2014-07-17 22:11:04 UTC
"FILESAVE as .xlsx destroys link to linked contents" -- it appears that this doesn't apply to just externally linked contents.  Saving as .xlsx or .xls from .ods with links to internally linked contents (in my case, another worksheet in the same workbook) appears to destroy the internal link as well.
Comment 15 Eike Rathke 2014-07-25 17:22:35 UTC
Taking bug, I'm working on this.

@xaxiobrandish:
(In reply to comment #14)
> Saving as .xlsx or .xls
> from .ods with links to internally linked contents (in my case, another
> worksheet in the same workbook) appears to destroy the internal link as well.

How did you define those links? Could you attach a sample .ods document that exposes this behavior when saved to .xlsx?
Comment 16 Eike Rathke 2014-07-25 17:24:07 UTC
@Andrew:
(In reply to comment #12)
> Is it purely related to translation to xlsx or is there no problem saving to
> xls?

This is a .xlsx problem only, .xls works fine.
Comment 18 Eike Rathke 2014-08-11 20:24:17 UTC
Backports pending review at
https://gerrit.libreoffice.org/10872 for 4-3
https://gerrit.libreoffice.org/10873 for 4-2
Comment 19 Commit Notification 2014-08-12 12:02:16 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=8c51532289db36337c4984baec1bf5a7ca9e40af

do not write empty externalReferences element, fdo#45286 follow-up



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 20 Commit Notification 2014-08-12 15:31:57 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=e04a4d2d2c18134556bb4e001d497ca7d66c6828

really do not write empty externalReferences element, fdo#45286 follow-up



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-09-03 19:17:24 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=d3483180e684ab8272b30676a395a53c9c7c6ad2&h=libreoffice-4-3

write OOXML externalReferences, externalLinks, fdo#45286


It will be available in LibreOffice 4.3.2.

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 pharmankur 2014-10-06 17:54:58 UTC
Tried LO 4.3.2
Was expecting that bug is solved ... But sadly its not done :(

Look at file attachments I have uploaded in Bug 78490

https://bugs.freedesktop.org/show_bug.cgi?id=78490


Steps to reproduce:
1. Using Libreoffice 4.3.2; Open the attached file - sales view file - Test.xlsx
2. It will ask 'This file contains links to other files. Should they be updated?'  ... click NO , & It will open showing all the data.
3. type someting in an empty cell & delete is again so that 'Save' option gets enabled. Save the file again & close it.
4. When you open the file again, now the data is missing.

Find attached 
1) sales view file - Test.xlsx
2) the screenshot when file is reopened & data is missing.
Comment 23 Stéphane Aulery 2016-02-22 17:22:34 UTC
Hello,

This bug is not resolved. Like explained in comment #6, I made two XLSX documents with LO, and linked the second to the first.

When I insert the link, it is ok. If I save, close and reopen the document which contains this link, it is broken. The tooltip display that the path has been corrupted.

It keep file name but drop directories path :

    file:///C:\Users\lkppo\myfile.xslx

become

    file:///myfile.xslx

Tested on Windows 7 64bit with LO 5.0.1.2

Regards,

-- 
Stéphane Aulery
Comment 24 jani 2016-02-22 17:26:53 UTC
4.x versions are no longer maintained
Comment 25 V Stuart Foote 2016-02-22 17:42:00 UTC
No. This issue was closed fixed against 4.4.0 and 4.3.2

Please open a new BZ issue--with fresh Steps to Reproduce (STR) and test document/kit (screen clips, sample document, etc.).
Comment 26 Stéphane Aulery 2016-02-22 18:13:08 UTC
Done there : https://bugs.documentfoundation.org/show_bug.cgi?id=98085
Comment 27 V Stuart Foote 2016-02-22 18:38:07 UTC
*** Bug 78490 has been marked as a duplicate of this bug. ***