Enhanced Hyperlink Support in Spread. NET v14

Hyperlinks in Excel workbooks are set on a cell or a shape object. There are three hyperlink types: cell location, email address, and web site. Link to cell location is the most widely used type. Cell location links create a convenient index or table of contents for a workbook with links to key areas for input or calculated results in a dashboard or summary worksheet.

Spread. NET v14 introduces enhanced support for hyperlinks. Features include full support for the import and export of hyperlinks in Excel XLSX workbook documents and a new API and built-in user interface support for hyperlinks.

This blog includes the following sections:

• Hyperlink Basic Usage
• Edit Hyperlink UI
• Formulas and the Hyperlink Function
• Demo Samples

Hyperlink Basic Usage

Figure 1 Hyperlinks Basic Usage

The Hyperlinks basic usage demo shows a variety of hyperlinks in cells and shapes.

The links in row five go to other cell locations in the workbook. The first link in B5 goes to Formulas!C6, the next link in D5 goes to the custom name FormulaExample1 (which is also Formulas!C6). The last link in F5 goes to Table1, which goes back to B5.

The link in B8 is an email link to the customer support email address, and the link in B11 is a website link to the GrapeCity website.

Each of those links is set in code and initialized with the new hyperlink API to navigate to the target and show tooltip text (see attached sample code for details).

Note that Spread. NET v14 operates in run-time like Microsoft Excel and allows the user to click and hold to select the cell instead of navigating to the hyperlink target.

Shape objects can also have hyperlinks. In this example, the ReturnButton shape in cells B14:B15 is linked to Formulas!C10, and the image of the spreadsheet eagle links to Formulas!C14.

Shape objects with hyperlinks always navigate immediately upon clicking the shape, as the action is invoked on the mouse down. Users selecting a shape that has a hyperlink or action associated with it must use a right-click instead.

How to Edit the Hyperlink UI

Figure 2 Edit Hyperlink Dialog

Edit the hyperlink using the built-in Edit Hyperlink dialog. Select a cell or shape in the worksheet, select the menu item Edit Hyperlink, or press Ctrl+K to invoke the built-in keyboard shortcut action. Select the type of link on the left, and specify the target on the right.

Use the ScreenTip button to open the Set Hyperlink ScreenTip dialog:

Figure 3 Set Hyperlink ScreenTip Dialog

The new Edit Hyperlink built-in action is defined in the new Excel-compatible keyboard shortcuts. Enable these shortcuts using FpSpread.Features:

[C#]

fpSpread1.Features.AutoCreateHyperlink = true; // automatically create hyperlinks with separate undo action
fpSpread1.Features.EnhancedShapeEngine = true; // enable enhanced shapes
fpSpread1.Features.ExcelCompatibleKeyboardShortcuts = true; // enable new Excel compatible keyboard shortcuts, allows Ctrl+K for Edit Hyperlink dialog

[VB]

fpSpread1.Features.AutoCreateHyperlink = True  ' automatically create hyperlinks With separate undo action
fpSpread1.Features.EnhancedShapeEngine = True  ' enable enhanced shapes
fpSpread1.Features.ExcelCompatibleKeyboardShortcuts = True  ' enable New Excel compatible keyboard shortcuts, allows Ctrl+K For Edit Hyperlink dialog

Set FpSpread.Features.AutoCreateHyperlink = true to enable automatic creation of hyperlinks when editing cells. When enabled, this feature lets the user type a hyperlink directly into a cell:

Formulas and the HYPERLINK Function

Figure 4 Formulas and HYPERLINK Function

Hyperlinks can also be created using the HYPERLINK function in a cell formula. The HYPERLINK function defines the link target and link text to show in the cell:

HYPERLINK(link_location, [friendly_name]) 
link_location: target reference or URL 
linktext: display text for link

Creating a link to a cell location uses a different syntax. The '#' character precedes the target reference, name, table name, or structured reference.

Links created with the HYPERLINK function automatically show tooltip text with the name of the control, the link location, and brief instructions about clicking the link versus using click and hold to select the cell.

Note that hyperlinks created using the HYPERLINK function are separate from cell hyperlinks created with the new hyperlink API or the new Edit Hyperlink dialog. Open the Edit Hyperlink dialog for a cell containing a HYPERLINK formula. The hyperlink in the cell created by the HYPERLINK formula will not be visible to the Edit Hyperlink dialog. In such instances applying a hyperlink to the cell using the dialog will overwrite the HYPERLINK formula in the cell and replace it with the specified link text in the Edit Hyperlink dialog.

Enhanced Hyperlink Demo Samples

Demo samples for the new enhanced hyperlink support with full source showing the examples above are available for both C# and VB:

• C# Hyperlink Examples
• VB Hyperlink Examples

These demo samples are also included as part of the Control Explorer demos in the trial download.