HyperLink Function in Calc

OpenOffice.org 2.0

Document - ID	Specification Owner	Last Change	Status
	John Marmion	16th April 2004	Final
Conforms to	StarOffice Specification Workflow v0.1, Aug 2003
Applies to	Calc
Task ID(s)	i27972, i27973, i27974, i27975, i27976, i27977
Category	Feature

Abstract

One of the most powerful features in Documents is the ability to insert a link to any file or site into a document. This feature is available in Calc. But this feature can also be accessed dynamically from a function. This is available in other products. We need to support this hyperlink function in Calc. We need to be capable of successfully importing a document containing this function and support the ability to successfully maintain it by exporting it.

i-Team Members

	Name	E-mail Address
Specification Owner	John Marmion (jm)	jmarmion@openoffice.org
User Experience	Falko Tesch (ft)	ft@openoffice.org
Development	John Marmion (jm)	jmarmion@openoffice.org
Quality Assurance	Oliver Cramer (oc)	oc@openoffice.org
Documentation	Uwe Fischer (uwe)	fpe@openoffice.org

Approved for Implementation

	Approved by	Date
User Experience	Falko Tesch	2004-04-23 and 2003-05-17
Development	Daniel Rentz	2004-04-20
Quality Assurance	Oliver Cramer	2004-04-26
Documentation	Uwe Fisher	2004-04-27
String Review	Elizabeth Matthis	2004-05-03 and 2004-05-18

Document Change History

Rev. Level	Change	Initials	Date
1.0	Initial version	jm	2004-04-19
1.1	Add Competitive Analyses	jm	2004-04-20
1.2	Update approvals	jm	2004-04-28
1.3	String Review approval	jm	2004-05-05
1.4	Add more strings	JM	2004-05-12
1.5	User Experience approved	JM	2004-05-17
1.6	String review changes	JM	2004-05-19
1.7	URL and Cell Text formatting	JM	2004-05-24

Contents

Glossary	1

1	Motivation	1

2	Goals	1

3	Requirements and Dependencies	1

3.1	Requirements	1

3.2	Technical Dependencies	1

4	User Scenarios	1

5	Competitive Analyses	1

6	Detailed Specification	1

6.1	Error Conditions	2

7	Future Tasks	2

8	Legal Issues	2

9	Notes	2

10	References	2

Glossary

Term	Description
HYPERLINK	Is a link location e.g. to a cell in the spreadsheet, or a file or site. The file or site may be stored on a hard disk, network or internet.
URL	Uniform Resource Locater(URL): an address that specifies a protocol (e.g. http or ftp) and a location of an object, document, web page, or other destination on the Internet.

Motivation

To enhance the interoperability with MS Excel , this specification will define the new feature of support for the HYPERLINK function in Calc.

Goals

This feature will increase the compatibility when handling documents from other spreadsheet applications.

Ease-of-Use with competitive products. It is mandatory that the users finds a comparable functionality within the same feature as in competitive products.

Requirements and Dependencies

Requirements

To increase compatibility to Microsoft Excel, Calc needs the HYPERLINK spreadsheet function. This function creates a hyperlink cell based on a formula result string.

Technical Dependencies

None.

User Scenarios

An imported Excel file format document may contain a HYPERLINK() formula. This formula will be ignored and the user will be presented with a “#NAME” cell error. This formula will subsequently be lost on exporting the same document to the Excel file format.

Calc users are familiar with inserting and activating Hyperlinks in documents. A similar experience should be available using the function bar. This function needs to be saved with the document.

Documents containing a HYPERLINK() function need to be successfully imported and exported..

Competitive Analyses

This exists in competitive products.

Microsoft Excel

Microsoft Excel 97 (and later) supports the ability to dynamically create a hyperlink cell using the HYPERLINK() function as described in the Detailed Specification section based on data contained in the spreadsheet.

GNUMERIC

Gnumeric 1.x supports the ability to dynamically create a hyperlink cell using the HYPERLINK() function in similar fashion to Excel.

Lotus 1-2-3

Lotus 1-2-3 Millennium does not support the Hyperlink function.

KSpread

KSpread 1.x supports HYPERLINK cells.

Detailed Specification

The following is a description of how the HYPERLINK function will work in Calc. It will be implemented according with this description.

The syntax of the HYPERLINK function is as follows:

HYPERLINK( URL )

or

HYPERLINK( URL ; CellText ).

The URL parameter specifies the link target used by the hyperlink cell, while the CellText parameter is shown as the formula result in the cell. If CellText is omitted, the URL will be used for both the URL and the cell result.

If a formula in Excel contains a HYPERLINK function, the cell becomes a “hyperlink cell”. Clicking on this cell will execute the contained hyperlink. Therefore it is irrelevant, if the HYPERLINK function is really evaluated (it may i.e. be located in a non-executed part of an IF function) – the cell will always be a hyperlink cell, even if the formula evaluates to a number or a Boolean value. The only exception to this rule is if an error code would be part of the URL. In this case no hyperlink cell will be created.

In a hyperlink cell, all other functions affect both the current URL and the cell text. Therefore the formula must be evaluated twice. In the first run using the URL part of all hyperlink functions, and in the second run using the CellText part.

The following examples show the behaviour of hyperlink cells. Many of them do not make practical sense but they demonstrate all possibilities and side effects of the HYPERLINK function.

The formula =HYPERLINK("http://www.example.org") consists only of the HYPERLINK function. The function adds “http://www.example.org” to the URL of the hyperlink cell and returns the same text which is used as formula result.

The formula =HYPERLINK("http://www.example.org";"Click here") again adds “http://www.example.org” to the URL of the hyperlink cell but returns the text “Click here” which is used as formula result.

The formula =HYPERLINK("http://www.") & "example.org" results in a hyperlink cell. The HYPERLINK function adds “http://www.” to the URL and the cell result. The following text “example.org” is also added to both the URL and the cell result. Therefore this formula is equivalent to example A).

The formula =HYPERLINK("http://www.";”Click “) & "example.org" results in a hyperlink cell with the URL “http://www.example.org”. The visible result of the cell is “Click example.org”.

Even the formula =HYPERLINK("") & "http://www.example.org" is equivalent to example A) and C).

The formula =HYPERLINK("";"Click ") & "http://www.example.org" results in the URL “http://www.example.org” and the visible cell text “Click http://www.example.org”.

The formula =MID(HYPERLINK("http://www.example.org";"Click here");1;5) results in the URL “http:” and the visible cell text “Click”.

The formula =IF(TRUE;HYPERLINK(“http://www.example.org”);”something”) is also equivalent to example A) and C), because only the HYPERLINK function will be evaluated.

The formula =IF(FALSE;HYPERLINK(“http://www.example.org”);”something”) results in a hyperlink cell though the HYPERLINK function will not be evaluated. The cell contains the (not working) URL and the result “something”.

The formula =IF(FALSE;HYPERLINK(“http://www.example.org”);1) results in a hyperlink cell with the numerical result 1 and the URL “1”.

The formula =IF(FALSE;HYPERLINK(“http://www.example.org”);1=1) results in a hyperlink cell with the Boolean result TRUE and the URL “TRUE”.

The formula =HYPERLINK(“http://www.example.org”) & (1/0) does not create a hyperlink cell, because an error code would be part of the URL. The cell result is “#DIV/0!”.

The formula =HYPERLINK($B4) where cell B4 contains “http://www.example.org”. The function adds “http://www.example.org” to the URL of the hyperlink cell and returns the same text which is used as formula result.

To summarise:

	Formula	URL	Cell Result
A	=HYPERLINK("http://www.example.org")	http://www.example.org	"http://www.example.org"
B	=HYPERLINK("http://www.example.org";"Click here")	http://www.example.org	“Click here”
C	=HYPERLINK("http://www.") & "example.org"	http://www.example.org	"http://www.example.org"
D	=HYPERLINK("http://www.";”Click “) & "example.org"	http://www.example.org	"Click example.org"
E	=HYPERLINK("") & "http://www.example.org"	http://www.example.org	"http://www.example.org"
F	=HYPERLINK("";"Click ") & "http://www.example.org"	http://www.example.org	"Click http://www.example.org"
G	=MID(HYPERLINK("http://www.example.org";"Click here");1;5)	http:	"Click"
H	=IF(TRUE;HYPERLINK(“http://www.example.org”);”something”)	http://www.example.org	"http://www.example.org"
I	=IF(FALSE;HYPERLINK(“http://www.example.org”);”something”)	something	”something”
J	=IF(FALSE;HYPERLINK(“http://www.example.org”);1)	1	1
K	=IF(FALSE;HYPERLINK(“http://www.example.org”);1=1)	TRUE	TRUE
L	=HYPERLINK(“http://www.example.org”) & (1/0)	no hyperlink created	Err:503
M	=HYPERLINK($B4) where B4 = “http://www.example.org”	http://www.example.org	"http://www.example.org"

Thus the feature primarily involves supporting this new function in Calc. The processing of the Formula bar record will be extended to successfully parse the formula containing the HYPERLINK(). The URL activation will act in the same way as the HYPERLINK impementation i.e. will be activated on mouse-over action directly on the cell text.

The Cell Text string will be formatted according to the cell format while the URL will default to the standard type format. For Example in example J above, if the cell is formatted as a percentage, then 100% will appear as the cell text and 1 as the URL.

This HYPERLINK() function will be read and written out to the xml file format. This involves no changes to the xml file format as it simply involves writing out the new resource string HYPERLINK.

Imported Excel documents containing a Formula record which include this HYPERLINK() will be supported.

The Excel export will be extended to support writing out this HYPERLINK() function.

Earlier releases will continue to display the “#NAME!” error.

String list

Item	English	German	Comments
Function Name	HYPERLINK	HYPERLINK	
Description	Hyperlink	Hyperlink	
Parameter 1 name	URL	URL	
Param 1 descrption	URL	URL	
Parameter 2 name	CellText	ZellText	Translation: no space allowed, this is a parameter!
Param 2 description	Cell Text	Zell text	

Tab Order

No changes.

Key Board Shortcuts

None.

Error Conditions

Usual error conditions of formula calculation..

Future Tasks

Excel creates a hyperlink cell as outlined in the above examples but the default blue underline URL indicator is only created if its a valid URL. Adding a new hyperlink() will not automatically result in the default hyperlink cell style of light blue colour font with underline. We will look to do this dynamically at a later stage.

Legal Issues

None.

Notes

None.

References

http://www.openoffice.org/project/www/issues/show_bug.cgi?id=27973

HYPERLINK function in Calc

Page 5

http://specs.openoffice.org/calc/compatibility/HyperlinkFunction.sxc

Matthias Müller-Prove

2004-04-16T15:07:33