GNOME Bugzilla – Bug 81718
HYPERLINK function does not make cell a link.
Last modified: 2018-05-22 13:03:25 UTC
Excel 2000 (don't know about other versions) has an option to insert hyperlinks into a sheet. I especially like the option to insert a link to "Place in This Document" that allows quick navigation through complicated sheets.
I have begun support for this. We can now import this from XL and have it work in gnumeric, including cursor changes and tooltips. We also support urls. Still to do - xml persistence - Creation dialog - Decision on editing/removal semantics
jody: also function HYPERLINK, I guess.
Hmm, the function will require another mechanism. Probably a new value type.
hmm, xml persistence, creation and deletion (via clear all) seems to work. What is left here?
3) hlinks 3.1) Finish the edit dialog to load and store results (DONE) 3.2) Figure out import semantics of the other 2 XL types 3.3) email support ? via system 'evoltion mailto:....' 6.10) hlink export I see xml-sax export, but not import.
Just out of curiosity (I'm perfectly happy already with the hyperlink support that comes with 1.2): is this going to be fully implemented in 1.4?
I'd like to get xls export in place for 1.4
sigh, doesn't look like it's going to be done before the release.
It would indeed be nice,
*** Bug 131110 has been marked as a duplicate of this bug. ***
*** Bug 341874 has been marked as a duplicate of this bug. ***
*** Bug 352588 has been marked as a duplicate of this bug. ***
On 1.6.3, hyperlink function does not make the cell clickable. Insert->Hyperlink works as expected. Not sure if this should be reported as a separate bug.
*** Bug 497191 has been marked as a duplicate of this bug. ***
Ok, xls export is done, and xls import is patched to support some of the new types. The file based links are not terribly useful without some mechanism for mapping the paths into something useable. We can open a new bug for that.
*** Bug 128534 has been marked as a duplicate of this bug. ***
This is quite an old bug now, and every other spreadsheet with HYPERLINK() support has clickable cells. What's the status of this issue?
You can create clickable cells in Gnumeric too by adding a hyperlink. I have yet to see any use case for HYPERLINK that would not be better solved by adding a huperlink. Note that even the OpenFormula draft of ODF does not require the cell to become a hyperlink in the presence of the HYPERLINK function. The behaviour of HYPERLINK between those implementations that create links varies to greatly.
You cannot change a cell that has a hyperlink formula into a clickable hyperlink cell in Gnumeric. And adding a hyperlink doesn't take a forumula, it takes a URL in the dialog. I have seen many use cases for it, and I have one, which prompted this question. And from OpenDocument-v1.2-cd05-part2-editor-revision.odt : "If a formula contains a HYPERLINK function, the cell becomes a "hyperlink cell". Clicking on this cell will execute the contained hyperlink. " See: http://www.oasis-open.org/committees/documents.php?wg_abbrev=office-formula So I'm not sure what the relevance of any of your responses are.
You were asking "What's the status of this issue." As long as there is no reasonable use case it is doubtful that any of the current developers is going to spend time working on it. Your comment "I have seen many use cases for it" doesn't change that unless you can elaborate on it. From OpenDocument-v1.2-csd06-rev02-part2.odt: Semantics: The default for the second argument is the value of the first argument. The second argument value is returned. In addition, hosting environments may interpret expressions containing HYPERLINK function calls as calling for an implementation-dependent creation of a hypertext link based on the expression containing the HYPERLINK function calls. The document you were looking at is outdated. Gnumeric provides a complete implementation of HYPERLINK according to this latest draft (which is likely to become the OASIS ODF 1.2 standard.) The relevance of my responses? Possibly none, but I think a response to your initial question of "The status of this issue is 'NEW'" would have helped even less. Unless there is some real strong use case made for this it doesn't look like the behaviour of the HYPERLINK function will be augmented any time soon. (Of course I only represent 25 to 34% of the currently active developers.)
I see. Well, I stand corrected. I thought I had the latest version of the spec. I do have a use case, but you say you don't have a "strong" use case and so you think it doesn't need to be done. This doesn't make me think supplying a use case will actually help, it makes me think it'll just be met with resistance. The fact is that OpenOffice chooses to make the cell of a HYPERLINK result clickable, and that is for me the standard behaviour, otherwise interoperability is lost. But thank you for your responses.
Corrie, I am seriously interested in hearing about the use case you have. In the past the uses I have seen were all equivalent to simply formatting the cell as a hyperlink. In fact when we discussed the HYPERLINK function in the OASIS OpenFormula SC of the ODF TC nobody presented a good use case (and the behaviour in OOo is already very different from that in Excel). Implementing this augmentation of HYPERLINK in Gnumeric wil require some significant changes to the Gnumeric code (since this crosses format to calculation boundaries) and so it would be important to see how it is supposed to be used to try to get this right. If we were simply opposed to implementing such an augmentation we would have closed this report as a WONTFIX.
To elaborate a little more, I have never understood why in OOo (and Excel) =IF (1=1,2,HYPERLINK("http://www.math.concordia.ab.ca",3)) causes the cell to be a hyperlink and what it would be good for.
Yes, I see why you think that, but you forget that the url string itself can be a calculated value:)
Please accept my apologies if I've misjudged you. Well, without suggesting that my use case is widely applicable, here is the user story: I have several columns of data items in a spreadsheet, and they change from time to time. Changes to the column cells are entered by hand, but can conceivably also be from a formula. I then have another column to make lookups, with cells containing something like =HYPERLINK(CONCATENATE("http://www.google.com/search?&q=", A2, "+", A3)) for a search on google.com of the web. Another column in the same row might have =HYPERLINK(CONCATENATE("http://www.google.co.nz/search?q=", A2, "+", A3, "&cr=countryNZ")) for a search of the web, NZ local sites, from google.co.nz I have other cases which use the same principle, but with more specialized search sites, always with a calculated URL. In OpenOffice Calc, this does the job for me, but in Gnumeric I don't have the same option because the cells with the HYPERLINK formula is not clickable. And there is no way to mark the cell clickable either (that I am aware of).
So, do I understand correctly that in your use cases, the HYPERLINK function will always be the outermost function in the formula for the cell?
Correct, but only because I use HYPERLINK as the outer function to get the cell to be clickable.
Created attachment 257627 [details] [review] Initial patch I'm not sure how the new value type should behave in all cases, I tried to make it look like strings. One strange thing is that a formula like =if(A1=0,"",hyperlink(A1,"link") works when loaded from a gnumeric or ods file but needs a manual recalculation before the link becomes active when loaded from an xls file, the string might even not be correctly displayed (getting 0 instead).
Unless we have rock-solid evidence that XL treats hyperlinks as a separate value type we should not go there. Value types lead to nightmare in evaluation land. Can we get away with just doing approximately what we do for expressions like =PV(...), i.e., auto-format? In this case, recognize that a new expression has been entered and that its outer function is HYPERLINK? Another option is to have hyperlinks be strings with some rich-text formatting.
ok, I tried the new value type solution because of comment #3. The auto-format solution looks beyond my expertise.
I have a variant of your patch that uses a custom pango attribute. It doesn't quite work yet because go_format_new_markup (and later go_format_parse_markup) ignore custom attributes except subscript/superscript. I'll attach it, but it may not apply because it depends trivially of stuff I cannot push from here.
Created attachment 257729 [details] [review] Preliminary VALUE_FMT based patch
Comment on attachment 257627 [details] [review] Initial patch rejecting -- see upcoming description
Comment on attachment 257729 [details] [review] Preliminary VALUE_FMT based patch rejecting -- see upcoming description
HYPERLINK is kind of magic. A cell becomes a hyperlink if its expression uses the HYPERLINK function. (Anywhere -- not necessarily in a part of the expression actually evaluated.) During normal evaluation, HYPERLINK simply returns its second argument, except that an error in the first argument dominates. Any value type seems to be valid. There is a special evaluation mode for the link target in which HYPERLINK returns its first argument. The whole expression is evaluated again for this purpose. Excel has some weirdness as to what it takes for a link to be shown as a link (blue colour and underlining). It appears to me that it happens when the first function call is to HYPERLINK. I don't think we need to emulate that.
Created attachment 257804 [details] [review] Preliminary patch This starts implementing things. Parts of this can go in now, actually: * Moving flags into the structure for function_call_with_exprs * Change sheet_hlink_find to transfer a ref * Change ItemGrid to hold a ref to cur_link
Created attachment 258186 [details] [review] Updated patch Updated patch that does the second evaluation. This patch is uncomfortably intrusive in the core. It messes with dependents in order to clear the old tooltips when evaluation is going on.
If someone are still wondering about use cases: A have a web application and most data are accessible by URL. I use spreadsheet as a draft tool for custom data manipulation and analysis. Clickable HYPERLINK formula is very handy for me to consult (or even change) data in the database. Anyway it's a variation of the google case in Comment 26.
-- GitLab Migration Automatic Message -- This bug has been migrated to GNOME's GitLab instance and has been closed from further activity. You can subscribe and participate further through the new bug through this link to our GitLab instance: https://gitlab.gnome.org/GNOME/gnumeric/issues/15.