Bug 89633

Summary:	CALC does not properly import / edit Excel hyperlinked cells containing formula or numeric
Product:	LibreOffice	Reporter:	bugzilla
Component:	Calc	Assignee:	Not Assigned <libreoffice-bugs>
Status:	NEW ---
Severity:	major	CC:	jluth, raal, susancragin
Priority:	medium
Version:	Inherited From OOo
Hardware:	All
OS:	All
See Also:	https://bugs.documentfoundation.org/show_bug.cgi?id=126024 https://bugs.documentfoundation.org/show_bug.cgi?id=149076 https://bugs.documentfoundation.org/show_bug.cgi?id=149090
Whiteboard:
Crash report or crash signature:		Regression By:
Bug Depends on:
Bug Blocks:	120543
Attachments:	7Zip file contains 3 spreadsheet files illustrating the issue

Description bugzilla 2015-02-24 21:08:52 UTC

Created attachment 113656 [details]
7Zip file contains 3 spreadsheet files illustrating the issue

Cells hyperlinked in Excel (using the hyperlink GUI window), cannot have their URL edited when opened in LibreOffice CALC. Also, if the CALC .ods version of the spreadsheet is subsequently re-saved as .xls, ALL the original Excel hyperlinks are lost i.e. no going back.

The above observation refers to cells containing a numeric value or formula. I have many sheets containing hyperlinked prices which I'm trying to migrate to LO CALC, and this problem makes it unacceptably risky. Text hyperlinks seem ok although the CALC hyperlink formatting of them is lost when saved back as .xls.

The spreadsheet examples attached illustrate the problems:
1) Price_Test.xls		The original Excel .xls spreadsheet with hyperlinked price and text cells
2) Price_Test.xls.ods		The .xls spreadsheet opened in CALC, cells added and saved as .ods
3) Price_Test.xls.ods.xls	The .xls.ods spreadsheet re-saved from CALC as .xls, then opened in Excel

NOTE:  I originally raised enhancement request "bug 89572" to improve the editing of cells containing hyperlinked values, before I realised the significance of the Excel hyperlinks actually vanishing. Hence escalation to serious bug.

Comment 1 raal 2015-02-25 05:18:04 UTC

I can confirm with Version: 4.5.0.0.alpha0+
Build ID: a2fa9e2468aa5c4fd4b610c5d0ebc8959e87a072
TinderBox: Linux-rpm_deb-x86_64@46-TDF, Branch:master, Time: 2015-02-23_02:34:05
Dataloss.

xls-> save as ods -> save as -> xls-> no links

xls-> save as ods -> save as -> xlsx-> file not loaded in LO 4.5. In LO 4.3.3 hyperlinks lost.

Comment 2 bugzilla 2015-02-26 14:18:03 UTC

I've tried to summarise the issues as I understand it, of hyperlinks on CALC cells containing numeric or formula.

a) Excel GUI hyperlinks appear to be applied to the whole cell irrespective of content (text, numeric, formula).
b) CALC GUI hyperlinks can ONLY be added to text, and CANNOT be applied to formula.
c) Excel created hyperlinks with numeric/formula cells cannot be edited when opened in CALC, but do 'work'.
d) Excel spreadsheet opened in CALC then re-saved in Excel format LOSES ALL original hyperlinks.

The ideal solution(s) would ensure:
1) All Excel GUI applied hyperlinks preserved when opened by CALC (cell content text, numeric or formula).
2) All hyperlinks to be fully editable in the CALC GUI.
3) Hyperlinks saved by CALC in Excel format(s).xls to be fully functional and editable in Excel.

Comment 3 m_a_riosv 2015-04-03 15:07:18 UTC

*** Bug 90433 has been marked as a duplicate of this bug. ***

Comment 4 tommy27 2016-04-16 07:22:36 UTC Comment hidden (obsolete)

** Please read this message in its entirety before responding **

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present on a currently supported version of LibreOffice (5.0.5 or 5.1.2 https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior

If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System

Please DO NOT

- Update the version field
- Reply via email (please reply directly on the bug tracker)
- Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case)

If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:

1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3)

http://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug

3. Leave a comment with your results.

4a. If the bug was present with 3.3 - set version to "inherited from OOo";
4b. If the bug was not present in 3.3 - add "regression" to keyword

Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa

Thank you for your help!

-- The LibreOffice QA Team This NEW Message was generated on: 2016-04-16

Comment 5 bugzilla 2016-04-16 14:47:10 UTC

I regret to confirm that this bug still exists on the latest 'Fresh' version of Libre Office 5.1.2.2.  Tested on Win 7, Vista & XP.

Comment 6 QA Administrators 2017-05-22 13:21:50 UTC Comment hidden (obsolete)

** Please read this message in its entirety before responding **

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

If you have time, please do the following:

Test to see if the bug is still present on a currently supported version of LibreOffice
(5.2.7 or 5.3.3 https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the version of LibreOffice and
your operating system, and any changes you see in the bug behavior

If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave
a short comment that includes your version of LibreOffice and Operating System

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not
appropriate in this case)

If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3)

http://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to "inherited from OOo";
4b. If the bug was not present in 3.3 - add "regression" to keyword

Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team

MassPing-UntouchedBug-20170522

Comment 7 bugzilla 2017-07-03 20:00:58 UTC

Again I have to report that the bug is still present with LO 5.3.4.2, T
tested on Win 7 64bit.

In fact things are worse now.  When the original Excel .xls file is opened by CALC then saved in .ods format, any attempt to re-open that .ods in CALC produces the error message "The xxx file is corrupt and cannot be opened", and the Repair option fails. However if "No" is chosen, the file does open but the hyperlinks associated with numbers are all lost.

Comment 8 bugzilla 2017-07-05 20:00:09 UTC

Correction to Comment 7:  
The original problem still persists, but the "File Open error" I mentioned in Comment 7 was caused by me not selecting .ods as the save-as format.  

Sorry for that red herring.

Comment 9 QA Administrators 2018-08-22 02:37:27 UTC Comment hidden (obsolete)

** Please read this message in its entirety before responding **

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

If you have time, please do the following:

Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.

If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.

Please DO NOT

2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword

Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team

MassPing-UntouchedBug

Comment 10 m_a_riosv 2020-09-22 07:44:18 UTC

*** Bug 136777 has been marked as a duplicate of this bug. ***

Comment 11 Susan Cragin 2020-10-01 16:24:56 UTC

As requested, I am adding here the comment from duplicate bug 13677.

Our finance department prepares worksheets in MS Excel and then sends them to me to be filled out. The worksheets contain hyperlinks to items on the Finance Dept server. (They then input submitted worksheets onto the main company budget.) 
I fill out my worksheets, send them back, and the hyperlinks have all been changed. They look fine in LO, but not back on my Finance's machine. 
I have tried doing Tools/Options/ Load/Save / General and unchecking "save URL relative to file system." 
This does not work. 
My office is one of several off-site of the main office, but the only one that uses LO, and I am the only one with this problem. 

My computer runs Gentoo 0.20.7 kernel 5.18.12 and LO 6.4.6.2 40 compiled with dbus, gtk, ldap, mariadb and python 3_9 (in the doubtful case any of this helps).

Comment 12 Susan Cragin 2020-10-01 16:25:18 UTC Comment hidden (obsolete)

As requested, I am adding here the comment from duplicate bug 13677.

Our finance department prepares worksheets in MS Excel and then sends them to me to be filled out. The worksheets contain hyperlinks to items on the Finance Dept server. (They then input submitted worksheets onto the main company budget.) 
I fill out my worksheets, send them back, and the hyperlinks have all been changed. They look fine in LO, but not back on my Finance's machine. 
I have tried doing Tools/Options/ Load/Save / General and unchecking "save URL relative to file system." 
This does not work. 
My office is one of several off-site of the main office, but the only one that uses LO, and I am the only one with this problem. 

My computer runs Gentoo 0.20.7 kernel 5.18.12 and LO 6.4.6.2 40 compiled with dbus, gtk, ldap, mariadb and python 3_9 (in the doubtful case any of this helps).

Comment 13 Matt K 2023-12-19 04:03:42 UTC

From what I see, you can use the "=HYPERLINK("http://www.example.com ",9)" syntax for cells you want to be able to use hyperlink and do things like SUM with.  You can even format the cell as currency.  Also, opening the original .xls in LO and saving changes made using the "=HYPERLINK" syntax as .xls again persists and opens in both Excel and LO fine, and all the original excel links still work.  There should be no need to save as .ods if you ultimately want to get back to .xls format.  So, I don't see any issues that need to be fixed here -- marking NEEDINFO in case there is something I might be missing.

Here is what I tested with:

Version: 24.8.0.0.alpha0+ (X86_64) / LibreOffice Community
Build ID: c498e2acd64688df9aa32bbcc820d8513a5373ea
CPU threads: 16; OS: Windows 10.0 Build 22631; UI render: Skia/Vulkan; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded

Comment 14 bugzilla 2023-12-19 15:08:05 UTC

As the originator if this bug report, I'd like emphasise the context it was raised in.

Originally had I had several spreadsheets created in Excel with thousands of GUI hyperlinked price cells.  My request was for a means to import these successfully into Calc.

Yes the =HYPERLINK() function works in both Excel & Calc if used from the very start, however this doesn't address the problem of existing Excel spreadsheets created (and maintained) using the GUI method.

It's been nearly 9 years since I first raised the bug report and I've transitioned fully to LO now. However it does appear others still see this (rightly) as a problem.