Bug 113541 - EDITING - Link to external documents is not created correctly - incorrect URL formation when path contains upper case, spaces and '!', '#' or '$' characters
Summary: EDITING - Link to external documents is not created correctly - incorrect URL...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.4.2.2 release
Hardware: All All
: medium major
Assignee: Eike Rathke
URL:
Whiteboard: target:6.4.0 target:6.3.0.1 target:6....
Keywords:
: 115244 (view as bug list)
Depends on:
Blocks:
 
Reported: 2017-10-30 22:59 UTC by bunkem
Modified: 2022-03-25 16:43 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description bunkem 2017-10-30 22:59:49 UTC
LO 5.x Mac will transpose the link location for an external link between two spreadsheets in such a way that ERR:501/ERR:507/ERR:509 will occur.

The link created by LO Mac will not work on the Win or Linux version of LO.

However linked cells created by Win or Linux versions of LO do work in Mac version.

I've tested LO 5.1, 5.3 & 5.4 on Mac, 
LO 5.1 Win, LO 3.5.7.2 Linux, 
and OO 3.3 and OO 4.1 on Mac.

In each case I followed these steps.
1) open two linked spreadsheets
2) place "=" in a cell, switch to other spreadsheet, select the linked cell and press enter key.
3) in each case on the Mac (LO 5.1/5.3/5.4); LO changes all the upper case letters to lower case, changes some of the characters ie. #, $ and ! are removed and you get an error message.

If you try to manually change the text entry changes from (3), when you press the enter key it reverts to the incorrect text.

Using the same spreadsheets and following steps (1) & (2) and using Win LO 5.2 & Linux Xubuntu LO 3.5.7.2, the linking works without error.

Using the same spreadsheets and following steps (1) & (2) and using Mac OO 3.3 and 4.1, the links work without error.

So at this point I opened the spreadsheet in LO Mac and the links created by Win and Linux versions are still OK.

If I use Mac LO to copy and paste the entire cell created by Win & Linux versions of LO and OO,there is no transformation and the linking works.

If I use Mac LO and select in the cell, highlight and copy the link formula, and paste the formula into another cell, Mac LO transposes the formula and ERROR. 

Further check of Automatic Spell Checking on/off yields the same behaviour on Mac.  Please note that with Automatic Spell Check off, the transformation of case occurs but the "!" does not get removed.

The link created by Win LO, Linux LO, OO Mac are all the same and work.
ie. here is a link that works created by Win/Linux/OO Mac
[code]=['file:///Users/me/Documents/Time & Expense Forms/TIMEEX201703.ods']'Details of Expenses 3'!I54[/code]


and here is the links created by LO Mac:
[code]='file:///users/me/documents/time & expense forms/timeex201703.ods']'details of expenses 3' I54[/code]

Please note the chase difference and the missing "!".
Comment 1 Alex Thurgood 2017-11-06 09:17:18 UTC
(In reply to bunkem from comment #0)

 
> In each case I followed these steps.
> 1) open two linked spreadsheets
> 2) place "=" in a cell, switch to other spreadsheet, select the linked cell
> and press enter key.
> 3) in each case on the Mac (LO 5.1/5.3/5.4); LO changes all the upper case
> letters to lower case, changes some of the characters ie. #, $ and ! are
> removed and you get an error message.
> 

No repro with

Version: 6.0.0.0.alpha1+
Build ID: 57c5f980835f834f6ea96c6f5ca841b5372aa61d
CPU threads: 4; OS: Mac OS X 10.13; UI render: default; 
Locale: fr-FR (fr_FR.UTF-8); Calc: group

I can't reproduce the upper case to lower case letter change. My path has mixed case letters, starting with capitals. None of them are changed.
Note that my path has neither spaces nor !,# or $.

If the issue is with interpretation of these characters in URLs (which is the case when linking to an external cell, then the title should be revised accordingly.
Comment 2 Alex Thurgood 2017-11-06 09:23:37 UTC
Tried with a link  to a cell having the following path :

='file:///Users/ajt/Downloads/Calc/GIMME LOTS AND & MORE!/Item price comparison 0.ods'#$'Memory cards'.A13


Still no repro with :

Version: 6.0.0.0.alpha1+
Build ID: 57c5f980835f834f6ea96c6f5ca841b5372aa61d
CPU threads: 4; OS: Mac OS X 10.13; UI render: default; 
Locale: fr-FR (fr_FR.UTF-8); Calc: group
Comment 3 Alex Thurgood 2017-11-06 09:26:10 UTC
No repro with

Version: 5.4.2.2
Build ID: 22b09f6418e8c2d508a9eaf86b2399209b0990f4
Threads CPU : 4; OS : Mac OS X 10.13; UI Render : par défaut; 
Locale : fr-FR (fr_FR.UTF-8); Calc: group
Comment 4 Alex Thurgood 2017-11-06 09:33:57 UTC
@bunkem : does the result depend on the type of content being linked ?

I only tested with links to contents of cells that were text.
Comment 5 bunkem 2017-11-06 16:46:05 UTC
(In reply to Alex Thurgood from comment #4)
> @bunkem : does the result depend on the type of content being linked ?
> 
> I only tested with links to contents of cells that were text.

In each case the linked cell in the other spreadsheet is a calculation (i.e. =i53+i52)

--
I just tested with a Text cell but I still get the hyperlink case change from mixed to lower.

Question:
Is it possible that this happens because the files were originally Excel (updated over the years from xls to xlsx) and recently saved as ods when I stopped using Excel?
Comment 6 bunkem 2017-11-06 16:47:22 UTC
I am on OSX 10.11.6 if that makes a difference?

When I posted the issue I was on OSX 10.10.x.
Comment 7 bunkem 2017-11-06 16:51:24 UTC
One more thought.  I see your locale if fr_FR.

My locale is English Canada (?en_ca?) but customized as I don't use Metric at all.

I use inch, letter & legal paper & 24 hr clock settings.

Not sure if that affects anything.??
Comment 8 bunkem 2017-11-06 18:25:50 UTC
One more thought.

Did some more testing with spreadsheets created in LO 5.4.2 but never had anything to do with Excel.

The link looks OK visually but when you hit "enter/return" on MBP, but you get the err:507.  You have to move to another cell and then go back to see the case change to lower.  The case change happens when you press "enter/return" on the keyboard.
Comment 9 bunkem 2017-12-28 18:23:10 UTC
The error still exists in LO 6.0 RC1.  

The links created in OOo 3.3, 4.1 and Linux 3.5.4. LO Win 5.2 are still OK.  All links created by LO5.2->5.4 still give an error.

====
For a lark, I decided to try the Dec 28th nightly.  The links created in LO 5.2->5.4 Mac are still giving an error.  But I can now create new links properly between spreadsheets.

The version that is working is:
Version: 6.0.0.1.0+
Build ID: 248834d1c2e105a6bf8627c2635c50e758eeb337
CPU threads: 8; OS: Mac OS X 10.11.6; UI render: default; 
TinderBox: MacOSX-x86_64@49-TDF, Branch:libreoffice-6-0, Time: 2017-12-28_09:12:51
Locale: en-US (en.UTF-8); Calc: group
Comment 10 bunkem 2018-01-25 20:45:26 UTC
I've been testing the 6.0RC2 and this issue has cropped up again.

Calc is changing all the links to lowercase and as the Mac file system is cap sensitive, the links are throwing ERR:507 again.

Version: 6.0.0.2
Build ID: 06b618bb6f431d27fd2def25aa19c833e29b61cd
CPU threads: 8; OS: Mac OS X 10.11.6; UI render: default; 
Locale: en-CA (en.UTF-8); Calc: group
Comment 11 bunkem 2018-01-25 20:52:27 UTC
Trying the nightly version:
Version: 6.0.1.0.0+
Build ID: d76fc1a87a31bf513f844be933996334ea85446c
CPU threads: 8; OS: Mac OS X 10.11.6; UI render: default; 
TinderBox: MacOSX-x86_64@49-TDF, Branch:libreoffice-6-0, Time: 2018-01-25_13:52:46
Locale: en-US (en.UTF-8); Calc: group

This one does the linking correctly. 

What's up with this ????
Comment 12 Xisco Faulí 2018-01-26 11:13:09 UTC
It means the issue is fixed in master but not yet backported to 6-0 branch.
Closing as RESOLVED WORKSFORME and add the whiteboard 'backportRequest' to see if someone can check when it was fixed
Comment 13 bunkem 2018-03-31 21:00:11 UTC
I have been using 
Version: 6.0.2.1
Build ID: f7f06a8f319e4b62f9bc5095aa112a65d2f3ac89
CPU threads: 8; OS: Mac OS X 10.11.6; UI render: default; 
Locale: en-CA (en.UTF-8); Calc: group

and assumed that the fix had been implemented.

But the problem still exists in this version.

Would you please let us know when the fix will be applied to the Fresh production version?

Thank you.
Comment 14 bunkem 2018-03-31 21:22:36 UTC
Still not fixed in 6.0.3.1 Beta either.
Version: 6.0.3.1
Build ID: 62abb169b0efa1520d7bee1f586865354060b989
CPU threads: 8; OS: Mac OS X 10.11.6; UI render: default; 
Locale: en-CA (en.UTF-8); Calc: group

Please let us know which production version will contain the fix.

Thank you.
Comment 15 bunkem 2018-04-01 17:53:20 UTC
I tried the master of 6.1 and it does the linking correctly.
Version: 6.1.0.0.alpha0+
Build ID: a488c7ad2763b944713997911c1ddb0315d8c93f
CPU threads: 8; OS: Mac OS X 10.11.6; UI render: default; 
TinderBox: MacOSX-x86_64@49-TDF, Branch:master, Time: 2018-03-26_00:38:29
Locale: en-US (en.UTF-8); Calc: group

Why was it that Version: 6.0.1.0.0+
Build ID: d76fc1a87a31bf513f844be933996334ea85446c
worked correctly but production versions 6.0.2 and beta 6.0.3 don't work?  If the fix was installed in the nightly for 6.0.1 shouldn't 6.0.2 & 6.0.3 have the fix??
Comment 16 bunkem 2018-07-06 21:16:44 UTC
Today while using 6.2alpha I had the need to link spreadsheets and the problem is found in the most recent master that I've been able to download.  

Version: 6.2.0.0.alpha0+
Build ID: c8d95ccecfcd31b720fdff67bbd6acbdceaf2546
CPU threads: 8; OS: Mac OS X 10.11.6; UI render: default; 
TinderBox: MacOSX-x86_64@49-TDF, Branch:master, Time: 2018-06-18_00:27:45
Locale: en-CA (en.UTF-8); Calc: group threaded


Incidentally it is also found in 6.0.5.2
Version: 6.0.5.2
Build ID: 54c8cbb85f300ac59db32fe8a675ff7683cd5a16
CPU threads: 8; OS: Mac OS X 10.11.6; UI render: default; 
Locale: en-CA (en.UTF-8); Calc: group

Sigh.  I wish this would get fixed in all new versions ...

I will now test 6.1.0b1.
Comment 17 bunkem 2018-07-06 21:25:08 UTC
OK just tested 6.1b and the linking problem is present again.

Version: 6.1.0.1
Build ID: 378e26bd4f22a135cef5fa17afd5d4171d8da21a
CPU threads: 8; OS: Mac OS X 10.11.6; UI render: default; 
Locale: en-CA (en.UTF-8); Calc: group threaded

Major frustration.  Now I have to find version
Version: 6.0.1.0.0+
Build ID: d76fc1a87a31bf513f844be933996334ea85446c
as that was the last one that linked properly.

Could we please get this fixed?
Comment 18 bunkem 2018-07-06 21:38:57 UTC
Problem is found in this version too.

Version: 6.1.0.0.beta1
Build ID: 8c76dfe1284e211954c30f219b3a38dcdd82f8a0
CPU threads: 8; OS: Mac OS X 10.11.6; UI render: default; 
Locale: en-CA (en.UTF-8); Calc: group

So it's back to 6.1.0.0alpha1
Comment 19 David Vinograd 2018-07-07 17:57:58 UTC
Hi,

I am trying to move from Excel 2002 to calc. Most of my spread sheets have links to other spread sheets to named cells. 

What happens is really weird. After much playing round I was able to get a set of linked spread sheets to work. The playing consisted of reading in the file and writing it out, editing the names of the external named cells in the link and editing the names of the cells in the link target. The edits deleted characters and / or reset the complete name.

I have no idea why some links work and some don't, nor any consistent way to repair them. I have seen the exact same link fail in different cells for example. The error is #REF - can that be expanded in some way?

Can anyone propose the next steps?

This is my first bug report so if I have missed any info, please query. Am running windows 10, April release.

Dave
Comment 20 David Vinograd 2018-07-08 03:36:25 UTC
Hi,

I also observe that I can not change the form of a link to remove capital letters. The data appears to be protected though there is no specific indication. Neither find and replace or just local edit on the cell link will alter the link.

Any suggestions??

Dave
Comment 21 bunkem 2018-07-26 19:21:27 UTC
The inability to link properly continues in the recent alpha build

Version: 6.2.0.0.alpha0+
Build ID: b86ef3b7fa3713031858f7d39c084445a5976a83
CPU threads: 8; OS: Mac OS X 10.11.6; UI render: default; 
TinderBox: MacOSX-x86_64@49-TDF, Branch:master, Time: 2018-07-22_00:03:50

This is getting very frustrating as I have need for linking spreadsheet at least once per week.

Please help.
Comment 22 bunkem 2018-08-06 22:42:10 UTC
Still not fixed in 
Version: 6.2.0.0.alpha0+
Build ID: 2b5d38649f6f8db8532f9944af87f5dbc1a05a63
CPU threads: 8; OS: Mac OS X 10.11.6; UI render: default; 
TinderBox: MacOSX-x86_64@49-TDF, Branch:master, Time: 2018-08-06_03:22:12
Locale: en-CA (en.UTF-8); Calc: group threaded

Please fix.
Comment 23 bunkem 2018-08-24 15:23:42 UTC
Still not fixed in
Version: 6.2.0.0.alpha0+
Build ID: 85818da3307a28b2d9c6fa5c1c97ca7833dfe24c
CPU threads: 8; OS: Mac OS X 10.11.6; UI render: default; 
TinderBox: MacOSX-x86_64@49-TDF, Branch:master, Time: 2018-08-22_04:15:46
Locale: en-CA (en.UTF-8)
Comment 24 bunkem 2018-08-24 17:14:11 UTC
An FYI.

Windows version 6.1003 will create the links properly in the ods files.

So there is something wrong with the specific mac code.
Comment 25 bunkem 2018-10-10 02:54:31 UTC
This continues in 6.2.0 alpha.

Version: 6.2.0.0.alpha0+
Build ID: a1d4c2419097169cb2d51f7c11d90073766f6d9d
CPU threads: 8; OS: Mac OS X 10.11.6; UI render: default; VCL: osx; 
TinderBox: MacOSX-x86_64@49-TDF, Branch:master, Time: 2018-10-09_07:41:59
Locale: en-CA (en.UTF-8); Calc: threaded

There was a fix temporarily.  Can you reinstate the fix please?
Comment 26 bunkem 2018-12-15 20:11:37 UTC
It's been a while since I checked this but decided to try in LO6.3alpha.

The problem still exists and makes linking between spreadsheets using LO completely non functional.

Version: 6.3.0.0.alpha0+
Build ID: abd9272ef1be9da58d405a0ddf3913a816a7e362
CPU threads: 8; OS: Mac OS X 10.11.6; UI render: default; VCL: osx; 
TinderBox: MacOSX-x86_64@49-TDF, Branch:master, Time: 2018-12-14_22:25:51
Locale: en-CA (en.UTF-8); UI-Language: en-US
Calc: threaded

Does anyone read these?  Is anyone going to do anything about this?

It was fixed for a version or two but then ended up broken again.

It would be really appreciated if it could be reviewed and fixed again.

Thank you.
Comment 27 bunkem 2019-04-13 15:36:51 UTC
Still a problem in both 6.3 alpha
Version: 6.3.0.0.alpha0+
Build ID: bea44e5c1b982d05314b72452b94fe288071a496
CPU threads: 8; OS: Mac OS X 10.11.6; UI render: default; VCL: osx; 
TinderBox: MacOSX-x86_64@49-TDF, Branch:master, Time: 2019-03-13_05:31:30
Locale: en-CA (en.UTF-8); UI-Language: en-US
Calc: threaded

and

Version: 6.2.2.2
Build ID: 2b840030fec2aae0fd2658d8d4f9548af4e3518d
CPU threads: 8; OS: Mac OS X 10.11.6; UI render: default; VCL: osx; 
Locale: en-CA (en.UTF-8); UI-Language: en-US
Calc: threaded
Comment 28 bunkem 2019-04-13 15:38:44 UTC
I thought that someone had found a solution and fixed this but that it was going to take some time to get into production.

It still isn't working in the alpha build and I've been using it on the hope that it would be the first to get fixed.  This is very important functionality for me.  I have to be able to link spreadsheets.

Thanks.
Comment 29 bunkem 2019-05-12 17:11:00 UTC
Does anyone who is working on the Mac version of Calc actually read this bug?

I posted this bug in October 2017.  The problem still exists 2.5 years later. 

It was fixed on one version but the error still came back.  I wish I can remember and had saved the version that worked.

The alpha version still changes the case of the links and gives err:507.
Version: 6.3.0.0.alpha0+
Build ID: ea9c13be02ba731074fa4207944ff7df40a0fb5c
CPU threads: 8; OS: Mac OS X 10.11.6; UI render: default; VCL: osx; 
TinderBox: MacOSX-x86_64@49-TDF, Branch:master, Time: 2019-04-10_20:43:17
Locale: en-CA (en.UTF-8); UI-Language: en-US
Calc: threaded

Yes, there is a work around. YOu can run the windows version in a VM and create the links there but as soon as you click in the cell while on a Mac, the link is again broken.  

Please can someone fix this?
Comment 30 Eike Rathke 2019-05-24 09:43:23 UTC
@bunkem:
Looks like you have activated the Excel A1 reference syntax style in your MacOSX installation, in which creating external references by clicking in another document seems to create broken formula strings, independent of operating system. Can you please confirm, and check if switching to Calc A1 cures the problem? Go to menu Tools -> Options -> Calc -> Formula, Formula Options, Formula syntax.
Comment 31 bunkem 2019-05-25 17:00:40 UTC
Hello Eike.

Thank you for your post and suggestion.

It worked.  Thank you, thank you, thank you!  This has been so frustrating.

Very interesting that just changing this preference fixes the linking problem.

This can't be by design??  Shouldn't the linking work even with using Excel A1 reference syntax?
Comment 32 Eike Rathke 2019-06-13 17:48:00 UTC
Taking.
Comment 33 Commit Notification 2019-06-13 21:09:12 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/69903b5c5f9b9015c88931c0eb8a47b52ea3de12%5E%21

Resolves: tdf#113541 handle external reference in Excel syntax

It will be available in 6.4.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 34 Eike Rathke 2019-06-13 21:12:32 UTC
Pending review
https://gerrit.libreoffice.org/73987 for 6-3
https://gerrit.libreoffice.org/73988 for 6-2
Comment 35 Commit Notification 2019-06-13 21:24:55 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/2d36e43d3d3ac69f4cacd532308cfc8c81982864%5E%21

Related: tdf#113541 use reference syntax of the document being edited

It will be available in 6.4.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 36 Commit Notification 2019-06-14 00:50:14 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-3":

https://git.libreoffice.org/core/+/6e68110a916500a92a15357b6bf90e62dc631a01%5E%21

Resolves: tdf#113541 handle external reference in Excel syntax

It will be available in 6.3.0.1.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 37 Commit Notification 2019-06-14 00:50:22 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-3":

https://git.libreoffice.org/core/+/eb7729d7740b2b16a7111d8fec15b268a03a1f44%5E%21

Related: tdf#113541 use reference syntax of the document being edited

It will be available in 6.3.0.1.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 38 Commit Notification 2019-06-14 05:52:11 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-2":

https://git.libreoffice.org/core/+/4ca3ac84912ab387b27d3f6c1a288a1ba12eb730%5E%21

Resolves: tdf#113541 handle external reference in Excel syntax

It will be available in 6.2.5.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 39 NISZ LibreOffice Team 2021-09-21 11:31:14 UTC
*** Bug 115244 has been marked as a duplicate of this bug. ***
Comment 40 Commit Notification 2022-03-25 16:43:12 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/1d4c4e82f1704eb66fb130ae03ee9ad28ddb2067

tdf#113541: sc_uicalc: Add unittest

It will be available in 7.4.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.