Hyperlinks

xlsgen > overview > Hyperlinks

Hyperlinks are powerful objects in Excel workbooks. Typical hyperlinks are urls, and can link an Excel workbook to virtually any resource on the web. But hyperlinks can be much more than that since, in addition to supporting agnostic document linking, that'll make you link to Word documents, PDF documents and so on, hyperlinks can also and foremost link to the same or other Excel workbooks in a very accurate manner. Whenever an Excel hyperlink targets the same or another Excel workbook, a cell target or a named range can be specified, allowing for very smart context-passing across links.

Excel hyperlinks allow powerful context-passing

Hyperlink properties

type : it can be a url or a regular file path. When the hyperlink is a url, you can use any web protocol generally supported including http://, https://, ftp://, mailto:, news://. When dealing with a file path, the file path is either absolute (begins with a drive letter, or a network UNC name), or relative to the Excel workbook file path where the hyperlink is added.
anchor : either type can be associated to an anchor. The anchor will scroll the target web page or document to reach a named element. In the case of urls, this named element is the standard anchor element. For instance, you can link to http://www.cnn.com, and to the WeatherBlock anchor in particular. In the case of file paths, the anchor has a meaning with Excel workbooks only.
Excel anchors : when specifying an Excel workbook anchor, the programmatic API lets you choose a cell or a named range.
rendering : by default, hyperlinks are rendered in the typical font style, i.e. blue and underlined. When this property is set to false, the hyperlink is rendered with the current style, allowing for richer combinations. Use this with caution though, it is disturbing to find hyperlinks in Excel without an explicit hyperlink rendering users would expect.

In addition, hyperlinks can be added to both regular cells and merged cells.

Hyperlink code samples

For more information on the IXlsHyperlink interface, see here. Hyperlinks are added in the current worksheet, or in the current merged cells block.

Code sample 1 : a simple url

VB code

Dim hlink as IXlsHyperlink
Set hlink = wksht.NewHyperlink
hlink.Label = "behind label"
hlink.UrlTarget = "http://www.cnn.com/"
wksht.Hyperlink(4,4) = hlink

C# code

xlsgen.IXlsHyperlink hlink = wksht.NewHyperlink();
hlink.Label = "behind label";
hlink.UrlTarget = "http://www.cnn.com/";
wksht.set_Hyperlink(4,4, hlink);

C/C++ code

xlsgen::IXlsHyperlinkPtr hlink = wksht->NewHyperlink();
hlink->Label = L"behind label";
hlink->UrlTarget = L"http://www.cnn.com/";
wksht->Hyperlink[4][4] = hlink;

Code sample 2 : an url with an anchor

VB code

Dim hlink as IXlsHyperlink
Set hlink = wksht.NewHyperlink
hlink.Label = "behind label"
hlink.UrlTarget = "http://www.cnn.com/"
hlink.NamedAnchor = "WeatherBlock"
wksht.Hyperlink(4,4) = hlink

C# code

xlsgen.IXlsHyperlink hlink = wksht.NewHyperlink();
hlink.Label = "behind label";
hlink.UrlTarget = "http://www.cnn.com/";
hlink.NamedAnchor = "WeatherBlock";
wksht.set_Hyperlink(4,4, hlink);

C/C++ code

xlsgen::IXlsHyperlinkPtr hlink = wksht->NewHyperlink();
hlink->Label = L"behind label";
hlink->UrlTarget = L"http://www.cnn.com/";
hlink->NamedAnchor = L"WeatherBlock";
wksht->Hyperlink[4][4] = hlink;

Code sample 3 : a link to a local file relative to the workbook path

VB code

Dim hlink as IXlsHyperlink
Set hlink = wksht.NewHyperlink
hlink.Label = "behind label"
hlink.FileTarget = "mydocument.doc"
hlink.RelativeFilepath = True
wksht.Hyperlink(4,4) = hlink

C# code

xlsgen.IXlsHyperlink hlink = wksht.NewHyperlink();
hlink.Label = "behind label";
hlink.FileTarget = "mydocument.doc";
hlink.RelativeFilepath = 1;
wksht.set_Hyperlink(4,4, hlink);

C/C++ code

xlsgen::IXlsHyperlinkPtr hlink = wksht->NewHyperlink();
hlink->Label = L"behind label";
hlink->FileTarget = L"mydocument.doc";
hlink->RelativeFilepath = TRUE;
wksht->Hyperlink[4][4] = hlink;

Code sample 4 : a link to a local Excel workbook, anchor at row 8 column 3, not relative to the workbook path

VB code

Dim hlink as IXlsHyperlink
Set hlink = wksht.NewHyperlink
hlink.Label = "behind label"
hlink.FileTarget = "C:\Book2.xls"
hlink.CellAnchor("Sheet2", 8, 3)
wksht.Hyperlink(4,4) = hlink

C# code

xlsgen.IXlsHyperlink hlink = wksht.NewHyperlink();
hlink.Label = "behind label";
hlink.FileTarget = @"C:\Book2.xls";
hlink.CellAnchor("Sheet2", 8, 3);
wksht.set_Hyperlink(4,4, hlink);

C/C++ code

xlsgen::IXlsHyperlinkPtr hlink = wksht->NewHyperlink();
hlink->Label = L"behind label";
hlink->FileTarget = L"C:\\Book2.xls";
hlink->CellAnchor(L"Sheet2", 8, 3);
wksht->Hyperlink[4][4] = hlink;

Code sample 5 : another link to a local Excel workbook, not relative to the workbook path

VB code

Dim hlink as IXlsHyperlink
Set hlink = wksht.NewHyperlink
hlink.Label = "behind label"
hlink.FileTarget = "C:\Book2.xls"
hlink.CellAnchor("Sheet2!F5", 0, 0)
wksht.Hyperlink(4,4) = hlink

C# code

xlsgen.IXlsHyperlink hlink = wksht.NewHyperlink();
hlink.Label = "behind label";
hlink.FileTarget = @"C:\Book2.xls";
hlink.CellAnchor("Sheet2!F5", 0, 0);
wksht.set_Hyperlink(4,4, hlink);

C/C++ code

xlsgen::IXlsHyperlinkPtr hlink = wksht->NewHyperlink();
hlink->Label = L"behind label";
hlink->FileTarget = L"C:\\Book2.xls";
hlink->CellAnchor(L"Sheet2!F5", 0, 0);
wksht->Hyperlink[4][4] = hlink;

Code sample 6 : a link to a local named range of an Excel workbook, not relative to the workbook path

VB code

Dim hlink as IXlsHyperlink
Set hlink = wksht.NewHyperlink
hlink.Label = "behind label"
hlink.FileTarget = "C:\Book2.xls"
hlink.NamedAnchor = "namedrange"
wksht.Hyperlink(4,4) = hlink

C# code

xlsgen.IXlsHyperlink hlink = wksht.NewHyperlink();
hlink.Label = "behind label";
hlink.FileTarget = @"C:\Book2.xls";
hlink.NamedAnchor = "namedrange";
wksht.set_Hyperlink(4,4, hlink);

C/C++ code

xlsgen::IXlsHyperlinkPtr hlink = wksht->NewHyperlink();
hlink->Label = L"behind label";
hlink->FileTarget = L"C:\\Book2.xls";
hlink->NamedAnchor = L"namedrange";
wksht->Hyperlink[4][4] = hlink;

Code sample 7 : a link to a local named range of an Excel workbook, not relative to the workbook path

VB code

Dim hlink as IXlsHyperlink
Set hlink = wksht.NewHyperlink
hlink.Label = "behind label"
hlink.FileTarget = "C:\Book2.xls"
hlink.NamedAnchor = "Sheet2!namedrange"
wksht.Hyperlink(4,4) = hlink

C# code

xlsgen.IXlsHyperlink hlink = wksht.NewHyperlink();
hlink.Label = "behind label";
hlink.FileTarget = @"C:\Book2.xls";
hlink.NamedAnchor = "Sheet2!namedrange";
wksht.set_Hyperlink(4,4, hlink);

C/C++ code

xlsgen::IXlsHyperlinkPtr hlink = wksht->NewHyperlink();
hlink->Label = L"behind label";
hlink->FileTarget = L"C:\\Book2.xls";
hlink->NamedAnchor = L"Sheet2!namedrange";
wksht->Hyperlink[4][4] = hlink;

Introspecting Hyperlinks

xlsgen exposes the existing hyperlinks in the current spreadsheet by way of a collection exposed at a worksheet level, the IXlsHyperlinks interface.

It is possible to list existing hyperlinks by index, or by location (row, col). It is also possible to delete hyperlinks :

ItemByIndex(i)
ItemByLocation(row, col)
Delete

C/C++ code

// how many hyperlinks
int count = wksht->Hyperlinks->Count;

// retrieve the second hyperlink
xlsgen::IXlsHyperlinkPtr hlink = wksht->Hyperlinks->ItemByIndex[2];