Hyperlink

SpreadJS allows users to add hyperlinks in cells to link to any relevant information on a web page, any range in the workbook or to send an email. The setHyperlink method can be used to set the following types of hyperlinks in the cells:

• URL for a webpage - For eg. https://developer.mescius.com/

• Email address - For eg. spread.sales@mescius.com

• Any reference in the current workbook - For eg. sjs://Sheet2!A1:B2

Users can also automatically generate a hyperlink by entering a link like string value and setting the allowAutoCreateHyperlink property to true.

• If a link like value starts with www, the hyperlink will consider it as a URL

• If a link like value matches the email link but it doesn't start with mailto:, the hyperlink will consider it as a URL

• If a link like value matches the workbook location type which starts with 'sjs://', the hyperlink will set the value as a URL

Features

• Excel files having hyperlinks can be imported to SpreadJS and vice versa.

• Hyperlinks can be opened in a new window or tab, in the same frame, in the parent frame or in the full body of the window by using HyperlinkTargetType enumeration.

• The tooltip, link color, visited link color, and underline for hyperlink can be defined by using various properties.

• A custom command can be defined for a hyperlink like zooming in a sheet or opening a date picker.

• Hyperlinks support formulas as well as operations such as copy, paste, dragMove, dragFill, headers, and overflow.

Special Cases

• If another hyperlink needs to be set to a cell that already contains a hyperlink, the new hyperlink will replace the old one rather than merge with it.

• If the hyperlink of a cell needs to be modified, getHyperlink can be used to get hyperlink object first, and then the value of the hyperlink data can be changed. Finally, the changed hyperlink data is set by setHyperlink.

• If a hyperlink needs to be removed from a cell, it can be set to null by using the setHyperlink method.

Priority Order for Hyperlink Styles

• If a cell has linkColor or visitedLinkColor in hyperlink and foreColor style, the hyperlink color will have higher priority.

• Hyperlink's drawUnderline property has higher priority than style.

• If the linkColor or visitedLinkColor is undefined or drawUnderline is false, the hyperlink cell will use the corresponding style for cell style.

• When a cell has both, the hyperlink and cell type (e.g. buttonList), the cell type will have higher priority.

Limitations

• If a function is passed to the command property in hyperlink data, the function is lost while using fromJSON or toJSON. Hence, the usage of a command is recommended instead of function.

• When exported to PDF, the hyperlink is shown but can't be clicked.

Using Code

This example code adds different hyperlinks to the cells of a spreadsheet.

// get the activesheet
var activeSheet = spread.getSheet(0);

// Hyperlink to URL with Tooltip and link without underline
activeSheet.setValue(0, 0, 'GrapeCity');
activeSheet.setHyperlink(0, 0, {
    url: 'https://www.grapecity.com/spreadjs',
    tooltip: "GrapeCity Website",
    linkColor: '#0066cc',
    visitedLinkColor: '#3399ff',
    drawUnderline: false
});

// Hyperlink to Sheet Location
activeSheet.setValue(1, 0, 'Sheet2!A1:B2');
activeSheet.setHyperlink(1, 0, { url: 'sjs://Sheet2!A1:B2', tooltip: "Link to Sheet2!A1:B2" }, GC.Spread.Sheets.SheetArea.viewport);

// Hyperlink to Email Address
activeSheet.setValue(2, 0, 'Send email');
activeSheet.setHyperlink(2, 0, { url: 'spread.sales@grapecity.com', tooltip: "Send email to spread.sales@grapecity.com" });

// Hyperlink to Email Address & Subject
activeSheet.setValue(3, 0, 'Send Email With Subject');
activeSheet.setHyperlink(3, 0, {
    url: 'spread.sales@grapecity.com?subject=Purchase Of License',
    tooltip: "Send email to spread.sales@grapecity.com with Subject"
});

// Hyperlink to Custom Command - Zoom Sheet
activeSheet.setValue(4, 0, 'Zoom Sheet');
activeSheet.setHyperlink(4, 0, {
    command: function (activeSheet) {
        if (activeSheet.zoom() === 1) {
            activeSheet.zoom(1.3);
        } else {
            activeSheet.zoom(1);
        }
    }
});
// Set Date Hyperlink
activeSheet.setValue(5, 0, 'Set Date');
activeSheet.setHyperlink(5, 0, { command: 'openMonthPicker' });

// Hyperlink Formula
activeSheet.setValue(6, 1, "www.google.com")
activeSheet.setValue(6, 2, "Google")
activeSheet.setFormula(6, 0, "=HYPERLINK(B7, C7");