How to Use Range Template Cell Types in a JavaScript Spreadsheet Application

Efficiently managing and presenting data is a crucial aspect of any JavaScript spreadsheet application. A feature that can greatly simplify this process is the Range Template Cell, allowing the developer or user to define a template of cell ranges as a single CellType.

This template can then be applied to a cell to load different data, data binding paths, and formatting. This extremely powerful feature allows JavaScript developers to present large amounts of different types of data into a spreadsheet in an organized way, such as for creating dashboards.

We will use SpreadJS, a JavaScript spreadsheet component, to create a Range Template Cell and display PokéAPI data to showcase the capabilities of Range Template Cells.

To follow along with this blog, download the sample application.

Steps to Follow:

1. Design Range Template
2. Set Up JavaScript Spreadsheet Application
3. Configure Data and Set the Range Template Cell
4. Design the Spreadsheet Component

Design the Range Template

To design the Range Template, use SpreadJS’s code-free spreadsheet designer. GrapeCity offers an online Spread Designer here. The SpreadJS Designer provides a WYSIWYG environment for customizing your spreadsheet. You can specify settings for cells, columns, rows, and sheets, as well as for the entire spreadsheet, using the menu options in the designer. To get started with the sample we are creating, make the following changes to the spreadsheet using the designer’s tools.

Next, create data fields by going to the designer’s DATA tab and clicking Sheet Binding; the Field Listsidebar is now showing. Using this sidebar, create the following data fields:

• name - text
• img - hyperlink
• desc - text
• types - text

Set the data binding paths on the spreadsheet by either dragging and dropping the data fields onto the template or by selecting a cell and clicking on the field.

With the binding paths set, we can now apply styles and functions to help better visualize the data. One of the most interesting features of Range Templates is the support for formula functions, including sparkline functions. SpreadJS offers an IMAGE sparkline function that allows users to show an image in a cell from an image URL. For this example, we will use this function to show an image of each Pokémon in the spanned cells using the img data bound cell; this data field will display the image URL data provided by PokéAPI.

Range Templates cells also support setting Excel-like conditional formatting for data visualization. For this example, we want to easily identify the types of Pokémon (water, electric, grass, etc.), so we will apply the following conditional formatting rules in cell B5:

Lastly, SpreadJS gives the users entire control of the spreadsheet template; this support allows us to hide rows and columns to remove unneeded information from being viewed. For example, it is not important for the end-user to see the Pokémon's image URL. To hide it, right-click row 2 and select Hide from the content menu.

Let’s do this again, but on a new worksheet that will be used to render the range template cells. First, create the new worksheet and name it Render. Ensure that it is before the Template worksheet we have been working on:

The render sheet will be used to display Range Template Cells. Let’s add a Pokémon logo to this sheet by adding the image URL https://www.freepnglogos.com/uploads/pokemon-logo-transparent-png-2.png to cell A1, and again using the IMAGE sparkline function in the cell span (B1:C1). After setting the IMAGE sparkline, hide the A column the same way we hide row 2.

Now that we have designed the Range Template, and set up the render sheet, let's export the spreadsheet data as a JavaScript file. Using the FILE tab, go to Export, select SpreadJS JSON, and click the Export JavaScript File button. Name the file template.

The template.js file will now be included in our JavaScript Spreadsheet Application during the next step.

Set Up JavaScript Spreadsheet Application

To display the Range Template Cell, initialize a SpreadJS Workbook instance in a simple HTML and JavaScript project. To do this, first, create a simple HTML webpage and include the following:

• SpreadJS script files and CSS file
• The designer-exported template.js file
• A new apps.js file

<!DOCTYPE html>
<html style="height: 100%; font-size: 14px">
  <head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>Range Template Cell</title>
    <!-- SpreadJS script and CSS files -->
    <script src="/spreadjs/demos/en/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
    <link rel="stylesheet" type="text/css" href="/spreadjs/demos/en/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
    <style>
      .sample-tutorial {
         position: relative;
         height: 100%;
         overflow: hidden;
      }
      body {
        position: absolute;
        top: 0;
        bottom: 0;
        left: 0;
        right: 0;
      }
    </style>
    <script src="template.js" type="text/javascript"></script>
  </head>
  <body>
    <div class="sample-tutorial">
      <!-- Workbook host element -->
      <h2>SpreadJS: Range Template Cell</h2>
      <div id="ss" style="width: 60%; height: 80%;border-style: groove;"></div>
    </div>
  </body>
</html>

Download a free trial of SpreadJS today!

Create an app.js file in the project folder and initialize the SpreadJS workbook.

window.onload = async function () {
  var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
};

SpreadJS is now displayed on the webpage:

With SpreadJS initialized, import the spreadsheet data exported from the designer in Step 2 using SpreadJS’s fromJSON method.

window.onload = async function () {
  var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
  
  spread.suspendPaint();
  // Import the template worksheet created using the Designer
  spread.fromJSON(template);
  var renderSheet = spread.getSheet(0);
  var templateSheet = spread.getSheet(1);
  spread.resumePaint();
};

Configure Data and Set the Range Template Cell

To retrieve the PokéAPI data, we will use the Fetch API’s fetch() method and then store the API data in an object called pokedex – keep in mind the data fields we used when creating the range template in the designer: name, img, desc, types. Next, we can display the stored API data on the renderSheet using SpreadJS’s setArray method.

const pokemonCount = 200;
var pokedex = {};

window.onload = async function () {
  var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));

  // Invoke the getPokemon function to retrive the API data
  for (let i = 1; i <= pokemonCount; i++) {
    await getPokemon(i);
  }

  spread.suspendPaint();
  // Import the template worksheet created using the Designer
  spread.fromJSON(template);
  var renderSheet = spread.getSheet(0);
  var templateSheet = spread.getSheet(1);

  // Set the retrived API data to the renderSheet
  var pokemon = Object.entries(pokedex);
  renderSheet.setArray(1, 1, pokemon, false);
  spread.resumePaint();
};

// Retrieve the PokéAPI data
async function getPokemon(num) {
  let url = "https://pokeapi.co/api/v2/pokemon/" + num.toString();
  // Fetch the
  let res = await fetch(url);
  let pokemon = await res.json();
  let pokemonName = pokemon["name"].toUpperCase();
  let pokemonType = pokemon["types"];
  let pokemonImg = pokemon["sprites"]["front_default"];
  res = await fetch(pokemon["species"]["url"]);
  let pokemonDesc = await res.json();
  pokemonDesc = pokemonDesc["flavor_text_entries"][9]["flavor_text"];
  pokedex[num] = {
    name: pokemonName,
    img: pokemonImg,
    types: pokemonType,
    desc: pokemonDesc,
  };
}

Next, define the Range Template as a new cell type, create a new spreadsheet style with the new cell type, and apply the style to a cell range on the renderSheet.

window.onload = async function () {
  var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
  spread.options.allowDynamicArray = true;

  // Invoke the getPokemon function to retrive the API data
  for (let i = 1; i <= pokemonCount; i++) {
    await getPokemon(i);
  }

  spread.suspendPaint();
  // Import the template worksheet created using the Designer
  spread.fromJSON(template);
  var renderSheet = spread.getSheet(0);
  var templateSheet = spread.getSheet(1);

  // Set the retrived API data to the renderSheet
  var pokemon = Object.entries(pokedex);
  renderSheet.setArray(1, 1, pokemon, false);

  // Create the Range Template as a cell type
  var rangeTemplateCelltype = new GC.Spread.Sheets.CellTypes.RangeTemplate(
    templateSheet,
    0,
    0,
    5,
    2
  );
  
  // Create style using the range template cell type
  var style = new GC.Spread.Sheets.Style();
  style.cellType = rangeTemplateCelltype;
  
  // Add Range Template cell as a style to a range on the renderSheet
  var rowcount = renderSheet.getRowCount();
  var range = renderSheet.getRange(
    1,
    2,
    rowcount,
    1,
    GC.Spread.Sheets.SheetArea.viewport
  );
  range.setStyle(style);

  spread.resumePaint();

};

With the data set and the Range Template cell type applied to the worksheet, the JavaScript spreadsheet will look like this:

Design the Spreadsheet and the Component

To better visualize the data and fit our application's needs, we can programmatically apply changes to the JavaScript spreadsheet and the component itself. For this example, we will do the following:

• Set the row height and column width of the renderSheet.
• Align the scroll bar with the last row and column of the spreadsheet.
• Enable precision pixel scrolling and setting the scrollPixel.
• Hide the row header, column headers, and tab strip.
• Freeze the first row of the spreadsheet.

  ...
  range.setStyle(style);
  // Set the row height and column width of renderSheet
  var rowcount = renderSheet.getRowCount();
  for (var i = 1; i < rowcount; i++) {
    renderSheet.setRowHeight(i, 350);
  }
  renderSheet.setColumnWidth(2, 450);

  // Align the scrollbar with the last row and column of the sheet
  spread.options.scrollbarMaxAlign = true;

  // Enable the precision scrolling by pixel
  spread.options.scrollByPixel = true;
  spread.options.scrollPixel = 15;
  
  // Hide column headers.
  renderSheet.options.colHeaderVisible = false;
  // Hide row headers.
  renderSheet.options.rowHeaderVisible = false;
  // Hide tab strip
  spread.options.tabStripVisible = false; // true: show, false: hide

  // Freeze first row - Pokemon logo
  renderSheet.frozenRowCount(1);

  spread.resumePaint();
  ...

This is how the app will look after applying this final step:

Download a free trial of SpreadJS today!

We hope you enjoyed learning how to use a Range Template Cell to display large amounts of data in a well-organized way. You can download the sample application from the blog here. This article only scratches the surface of the full capabilities of the GrapeCity SpreadJS spreadsheet component.

Review the documentation to see some of the many available features, and check out our online demos to see the features in action and interact with the sample code. Integrating a spreadsheet component into your applications allows you to customize your users' experience and provide them with familiar spreadsheet functionality.

To learn more about SpreadJS and the new features added to the v16 release, check out our release blog.