Implementing Excel-Like Hyperlinks with SpreadJS v13.1

SpreadJS v13.1 includes enhanced hyperlink support. In previous versions of SpreadJS, hyperlinks only opened URL links, and could not be exported to Excel. Our latest update addresses these issues and more.

In addition to Excel compatibility, we've added options to create different types of hyperlinks:

  • URL: Open the specified URL in the current web browser
  • Email Address: Open the default mailing application for the system and start a new email
  • Sheet Location: Navigate the active cell to a specific cell in the workbook
  • Custom Commands: Implement a custom action that happens when the user clicks on the hyperlink

In the following tutorial, we import an Excel file and add different types of hyperlinks with SpreadJS v13.1.

Read the full SpreadJS v13.1 release.

Project Setup: Creating the HTML Page

  1. Create an HTML file with SpreadJS references in it.

    • Create an HTML file in a folder, and add the following to it:
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head
    meta charset="utf-8"   />
    <title>SpreadJS Hyperlinks</title>

    <link href=" ./node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css" rel="
stylesheet" type="text/css"   />
    <script type="text/javascript" src=" ./node_modules/@grapecity/spread-sheets/dist/gc.spread.all.min.
js"></script>
    <script type="text/javascript" src=" ./node_modules/@grapecity/spread-excelio/dist/gc.spread.excelio.min.js">
</script>

</head>
<body>
    <div id="spreadSheet" style="width: 1300px; height: 800px; border: 1px solid gray"></div>
</body>
</html>

  1. Install the SpreadJS NPM files

    • Navigate to the project folder with a command prompt and type:
npm install @grapecity/spread-sheets @grapecity/spread-sheets-excelio

This action installs the latest SpreadJS files in the project and makes those references in the HTML page work correctly.

  1. Import an Excel template into SpreadJS.

    • For simplicity, we've created a JS file from the Excel template using the SpreadJS Designer and included it in the demo zip file (at the end of this post).

    • To load this file in the HTML page, add the following line:

<script type="text/javascript" src="./ExcelTemplate.js"></script>

And add script code to initialize the SpreadJS instance and load that template into it:

<script>
    window.onload = function () {
        var spread = new GC.Spread.Sheets.Workbook(document.getElementByID("spreadSheet"),{ sheetCount: 1});
        spread.fromJSON(ExcelTemplate);
        var sheet = spread.getActiveSheet();
    }
</script>
  1. Create a JSON template for that specific sheet that we will use later. We call it "sheetJSONString":
var sheetJSONString = JSON.stringify(sheet.toJSON());

Excel-like Hyperlinks with SpreadJS v13.1.0

We have set up the HTML page, and are ready to add the new hyperlinks to SpreadJS.

In this case, we want the link to appear in the cell next to "Employer Website" in the above template.

  • Use the setHyperlink function, and add the URL
  • Set the value for the text we want to be displayed in the cell 
//URL
sheet.setValue(2, 2, "GrapeCity")
sheet.setHyperlink(2, 2, { url: "https://www.grapecity.com"});

When clicked, it opens the system's default mail application and creates an email draft addressed to your specified recipient.

  • The code is essentially the same as URL hyperlinks 
//Email Address
sheet.setValue(2, 6, "us.sales@grapecity.com")
sheet.setHyperlink(2, 6, { url: "us.sales@grapecity.com"});

Like URL hyperlinks, the email value doesn't have to match the email address.

Automatically navigates to a specified cell and sheet in the workbook.

  • Use the same "set Hyperlink" function
  • Change the syntax for the "url" property to: sjs://<Sheet Name><Cell Reference>
  • The link will navigate to the total cell 
//Sheet Location
sheet.setValue(10, 4, "Personal Totals");
sheet.setHyperlink(10, 4, { url: "sjs://Personal Monthly Budget!G62:J67"});
  • We can't export custom command links to Excel
  • Create custom command hyperlinks with setHyperlink
  • Instead of specifying a "url" property, specify a "command" property
  • Follow this link for a list of command properties

Creating a Custom Command

Below we will create a function for a new custom command and generate a budget sheet. This function accomplishes the following:

  • Adds the budget sheet to the workbook
  • Imports the sheet JSON string we defined earlier
  • Renames the sheet
  • Adds hyperlinks 
//Custom Command
sheet.setValue(11, 4, "New Minthly Budget");
sheet.setHyperlink(11, 4, {
    command: function (sheet) {
        spread.addSheet(spread.sheets.length, new GC.Spread.Sheets.Worksheet ());
        var newSheet = spread.getSheet(spread.sheets.length - 1);
        newSheet.fromJSON(JSON.parse(sheetJSONString));
        newSheet.name("Personal Monthly Budget " + (spread.sheets.length - 1));
        spread.setActiveSheetIndex(spread.sheets.length-1);
        addHyperlinks(spread, sheetJSONString);    
    }
});

Excel-like Hyperlinks with SpreadJS v13.1.0

Download the sample

Download Now!

Kevin Ashley

Product Manager
comments powered by Disqus