Using Hyperlinks in Workbooks for .NET

Learn more about Xeed Workbooks for .NET

 

Today we will look into adding another new element to our xlsx document: Hyperlinks.

 

What is a Hyperlink?

A hyperlink is an item like a word, sentence or object that points to another location. When you click on a link, it will take you to that link's target.

In the context of Workbooks for .NET, a hyperlink will be a word or sentence in a given cell or range of cells

The Hyperlink class encapsulates the object that represents a hyperlink, it has the following properties:

• CellRange: the range of cells where the hyperlink is applied.

• DestinationAddress: the destination address of the hyperlink.

• TextToDisplay: the text that should be displayed if the existing text is to be replaced. The default value will be the DestinationAddress if no value is provided when the item is created.

• Tooltip: the text that will be displayed when the user mouses over the hyperlink’s cells range.

• Type: the hyperlink's type, supported values are:

• Email: the hyperlink refers to an email address.

• ExternalFile: The hyperlink refers to an external document.

• Internal: The hyperlink refers to a Cell in the current Worksheet or from another Worksheet of the Workbook.

• WebSite: The hyperlink refers to a website.

 

Accessing Hyperlinks

The hyperlinks in a Workbook are accessed through the HyperlinkCollection on a given Worksheet.

The HyperlinkCollection class contains all the hyperlinks in the Worksheet. It will also allow the user to add new hyperlinks and manipulate them.

The HyperlinkCollection class has the following properties:

• Count: returns the number of Hyperlinks in this collection.

• Item: returns the designated Hyperlink.

 

Adding Hyperlinks to a Worksheet

To add hyperlinks to a Worksheet, we use the Add method available on the HyperlinksCollection class.

The Add method offers 3 overloads, the difference between them is how the location of the hyperlink in the Worksheet is specified.

 

Overload option 1

• destinationLocation: the destination location of the hyperlink (a CellAddress from the current worksheet or from another worksheet, an external document, a website or an email address).

• cellAdress: the address for the hyperlink's location in the worksheet.

• hyperlinkRowLength: specifies on how many vertical cells the hyperlink be active (1 by default).

• hyperlinkColumnLength: specifies on how many horizontal cells the hyperlink be active. (1 by default).

• textToDisplay: the cell's text, if it is different from the existing one (null by default).

• tooltip: the tooltip text that will be displayed on mouse over.

using( var document = Workbook.Load( "testDoc.xlsx" ));
{
	var worksheet = document.Worksheets[ 0 ];

	// Format
	// worksheet.Hyperlinks.Add( destinationLocation, cellAddress, hperlinkRowLength, hyperlinkColumnLength, textToDisplay, tooltip );

	// Hyperlink Type: Internal (same worksheet)
	worksheet.Cells[ "B2" ].Value = "Add an hyperlink to a cell reference in same worksheet:";
	worksheet.Hyperlinks.Add( "Z1", "B3", 1, 1, "Link to another cell.", "A cell reference." );

	// Hyperlink Type: Internal (other worksheet)
	worksheet.Cells[ "B5" ].Value = "Add an hyperlink to a cell reference in another worksheet:";
	worksheet.Hyperlinks.Add( "Sheet2!B1", "B6", 1, 2, "Link to another worksheet's cell.", "Another worksheet cell reference." );

	// Hyperlink Type: ExternalFile
	worksheet.Cells[ "B8" ].Value = "Add an hyperlink to an external document:";
	worksheet.Hyperlinks.Add( "../abc.xlsx", "B9", 2, 3, "Link to another document.", "An external document link." );

	// Hyperlink Type: Email
	worksheet.Cells[ "B12" ].Value = "Add an hyperlink to an email address:";
	worksheet.Hyperlinks.Add( "sales@xceed.com", "B13", 3, 2, null, "An email link." );

	// Hyperlink Type: Website
	worksheet.Cells[ "B17" ].Value = "Add an hyperlink to a web site:";
	worksheet.Hyperlinks.Add( "www.xceed.com", "B18", 1, 2, "Xceed", "A web site link." );
}

 

Overload option 2

• destinationLocation: the destination location of the hyperlink (a CellAddress from the current worksheet or from another worksheet, an external document, a website or an email address).

• topLeftRowId: the Id of the row where the hyperlink is located in the worksheet.

• topLeffColumnId: the Id of the column where the hyperlink is located in the worksheet.

• hyperlinkRowLength: specifies on how many vertical cells the hyperlink be active (1 by default).

• hyperlinkColumnLength: specifies on how many horizontal cells the hyperlink be active. (1 by default).

• textToDisplay: the cell's text, if it is different from the existing one (null by default).

• tooltip: the tooltip text that will be displayed on mouse over.

using( var document = Workbook.Load( "testDoc.xlsx" ));
{
	var worksheet = document.Worksheets[ 0 ];

	// Format
	// worksheet.Hyperlinks.Add( destinationLocation, topLeftRowId, topLeftColumnId, hperlinkRowLength, hyperlinkColumnLength, textToDisplay, tooltip );

	// Hyperlink Type: Internal (same worksheet)
	worksheet.Cells[ "B2" ].Value = "Add an hyperlink to a cell reference in same worksheet:";
	worksheet.Hyperlinks.Add( "Z1", 2, 1, 1, 1, "Link to another cell.", "A cell reference." );

	// Hyperlink Type: Internal (other worksheet)
	worksheet.Cells[ "B5" ].Value = "Add an hyperlink to a cell reference in another worksheet:";
	worksheet.Hyperlinks.Add( "Sheet2!B1", 5, 1, 1, 2, "Link to another worksheet's cell.", "Another worksheet cell reference." );

	// Hyperlink Type: ExternalFile
	worksheet.Cells[ "B8" ].Value = "Add an hyperlink to an external document:";
	worksheet.Hyperlinks.Add( "../abc.xlsx", 8, 1, 2, 3, "Link to another document.", "An external document link." );

	// Hyperlink Type: Email
	worksheet.Cells[ "B12" ].Value = "Add an hyperlink to an email address:";
	worksheet.Hyperlinks.Add( "sales@xceed.com", 12, 1, 3, 2, null, "An email link." );

	// Hyperlink Type: Website
	worksheet.Cells[ "B12" ].Value = "Add an hyperlink to an email address:";
	worksheet.Hyperlinks.Add( "www.xceed.com", 17, 1, 1, 2, "Xceed", "A web site link." );
}

 

Overload option 3

• destinationLocation: the destination location of the hyperlink (a CellAddress from the current worksheet or from another worksheet, an external document, a website or an email address).

• topLeftCellAddress: the top left cell address for the location of the hyperlink in the worksheet.

• bottomRightCellAddress: the bottom right cell address for the location of the hyperlink in the worksheet.

• textToDisplay: the cell's text, if it is different from the existing one (null by default).

• tooltip: the tooltip text that will be displayed on mouse over.

using( var document = Workbook.Load( "testDoc.xlsx" ));
{
	var worksheet = document.Worksheets[ 0 ];

	// Format
	// worksheet.Hyperlinks.Add( destinationLocation, topLeftCellAddress, bottomRightCellAddress, textToDisplay, tooltip );

	// Hyperlink Type: Internal (same worksheet)
	worksheet.Cells[ "B2" ].Value = "Add an hyperlink to a cell reference in same worksheet:";
	worksheet.Hyperlinks.Add( "Z1", "B3", "B3", "Link to another cell.", "A cell reference." );

	// Hyperlink Type: Internal (other worksheet)
	worksheet.Cells[ "B5" ].Value = "Add an hyperlink to a cell reference in another worksheet:";
	worksheet.Hyperlinks.Add( "Sheet2!B1", "B6", "C6", "Link to another worksheet's cell.", "Another worksheet cell reference." );

	// Hyperlink Type: ExternalFile
	worksheet.Cells[ "B8" ].Value = "Add an hyperlink to an external document:";
	worksheet.Hyperlinks.Add( "../abc.xlsx", "B9", "D10", "Link to another document.", "An external document link." );

	// Hyperlink Type: Email
	worksheet.Cells[ "B12" ].Value = "Add an hyperlink to an email address:";
	worksheet.Hyperlinks.Add( "sales@xceed.com", "B13", "C15", null, "An email link." );

	// Hyperlink Type: Website
	worksheet.Cells[ "B12" ].Value = "Add an hyperlink to an email address:";
	worksheet.Hyperlinks.Add( "www.xceed.com", "B18", "C18", "Xceed", "A web site link." );
}

 

For more information, please refer to the documentation.