Hyperlink
Hyperlinks enable quick access to web pages, places in the workbook or email addresses. This article demonstrates how to use the feature in terms of the API exposed by the document model.
HyperlinkCollection
Each worksheet object maintains a collection of the hyperlinks it contains. This collection can be reached through the Hyperlinks property of the Worksheet class. This property is of type HyperlinkCollection and facilitates the process of searching, adding and removing hyperlinks.
The SpreadsheetHyperlink class is the representation of hyperlink in the document model. The class exposes the following properties:
Range: Property of type CellRange; indicates the range of cells that holds the hyperlink info.
-
HyperlinkInfo: Property of type HyperlinkInfo; indicates the type of the hyperlink and contains information about the target of the hyperlink. There are three supported types of hyperlinks:
Url: The Url hyperlink refers to a page on the internet.
MailTo: The MailTo hyperlink contains an e-mail address and, optionally, a subject.
InDocument: The InDocument hyperlink holds a reference to a cell range in string format, e.g. A1:B3.
Depending on the type of the hyperlink, the HyperlinkInfo object may contain additional information about the target. The class exposes the string properties Address, EmailSubject, ScreenTip and SubAddress and each hyperlink type requires a set of these properties to be filled. Note, however, that some of them are mutually exclusive. For example, if you have an Url hyperlink, you do not need to specify the EmailSubject.
You can create instances of each of those types, using the static methods of the HyperlinkInfo class.
Example 1 creates a hyperlink to a web address.
Example 1: Create link to web address
HyperlinkInfo webAddres = HyperlinkInfo.CreateHyperlink("http://telerik.com", "Telerik");
Example 2 creates a hyperlink to a cell range somewhere in the document.
Example 2: Create link to place in the document
HyperlinkInfo inDocument = HyperlinkInfo.CreateInDocumentHyperlink("A1:B3", "Go to A1:B3");
Example 3 creates a hyperlink to a cell in another worksheet of the document.
Example 3: Create link to a cell in another worksheet of the document
HyperlinkInfo inDocument = HyperlinkInfo.CreateInDocumentHyperlink("'Sheet2'!A1", "Go to Sheet2");
Example 4 shows how you can create a hyperlink to an email address.
Example 4: Create link to email address
HyperlinkInfo mailto = HyperlinkInfo.CreateMailtoHyperlink("someOne@someCompany.com", "someSubject", "Mail to someOne");
Add Hyperlink
To add a hyperlink, you need to specify a cell range that will contain the hyperlink and a hyperlink info that will determine the type of the hyperlink.
Example 5 assigns the hyperlink created in Example 1 to A1.
Example 5: Add hyperlink
CellIndex a1Index = new CellIndex(0, 0);
SpreadsheetHyperlink spreadsheetHyperlink = worksheet.Hyperlinks.Add(a1Index, webAddres);
Search for Hyperlink
There are several ways you can retrieve hyperlinks from the HyperlinkCollection depending on their position relative to a given cell range.
Example 6 defines two indexes and then a cell range out of those indexes.
Example 6: Define cell range
CellIndex a1Index = new CellIndex(0, 0);
CellIndex b3Index = new CellIndex(2, 1);
CellRange a1b3Range = new CellRange(a1Index, b3Index);
Example 7 gets all hyperlinks the ranges of which are contained in the cell range from Example 5.
Example 7: Get hyperlinks in cell range
IEnumerable<SpreadsheetHyperlink> containingHyperlinks = worksheet.Hyperlinks.GetContainingHyperlinks(a1b3Range);
The GetContainingHyperlinks() method has an overload which accepts a collection of cell ranges.
Example 8 gets all hyperlinks the ranges of which intersect with the cell range from Example 5.
Example 8: Get hyperlinks intersecting with cell range
IEnumerable<SpreadsheetHyperlink> intersectingHyperlinks = worksheet.Hyperlinks.GetIntersectingHyperlinks(a1b3Range);
Example 9 gets the last added hyperlink that intersects with the cell range from Example 5.
Example 9: Get last hyperlink intersecting with cell range
bool canGetHyperlink = worksheet.Hyperlinks.TryGetHyperlink(a1Index, out spreadsheetHyperlink);
The TryGetHyperlink method has an overload that accepts a CellIndex instead of CellRange.
Example 10 gets the hyperlink which range matches the cell range from Example 5.
Example 10: Get hyperlink exactly matching cell range
bool canGetHyperlinkExact = worksheet.Hyperlinks.TryGetHyperlinkExact(a1b3Range, out spreadsheetHyperlink);
Remove Hyperlink
To remove a hyperlink you need to retrieve a SpreadsheetHyperlink object and then remove it from the hyperlinks collection.
Example 11 removes a hyperlink.
Example 11: Remove hyperlink
SpreadsheetHyperlink spreadsheetHyperlink;
if (worksheet.Hyperlinks.TryGetHyperlink(a1Index, out spreadsheetHyperlink))
{
worksheet.Hyperlinks.Remove(spreadsheetHyperlink);
}