How to Use Range Template Cell Types in a JavaScript Spreadsheet Application
Tags: webdev, devops, javascript, tutorial
Published: 2023-07-26 18:57:59

---
canonical_url: https://developer.mescius.com/blogs/how-to-use-range-template-cell-types-in-javascript-spreadsheet
description: Learn how to use Range Template cell types in a JavaScript spreadsheet application. See more about SpreadJS today.
---
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](https://developer.mescius.com/spreadjs/docs/features/cells/celltypes/range-template-cell#site_main_content-doc-content_title), 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,](https://developer.mescius.com/spreadjs "spreadjs") to create a Range Template Cell and display [PokéAPI](https://pokeapi.co/) data to showcase the capabilities of Range Template Cells.

To follow along with this blog, [download the sample application](https://gccontent.blob.core.windows.net/gccontent/blogs/spread/20230614-How-to-Use-Range-Template-Cell-Types-in-JavaScript-Spreadsheet-Applications/SpreadJS_Range_Template_Cell_Pok%C3%A9mon.zip).

![Range Template Cell Types](https://files.grapecity.com/gc-website-media/luah5lue/01-pokemon-app_1.gif?width=500&height=311.20783460282917)

## Steps to Follow:

1.  [Design Range Template](#design-template)
2.  [Set Up JavaScript Spreadsheet Application](#set-up)
3.  [Configure Data and Set the Range Template Cell](#configure)
4.  [Design the Spreadsheet Component](#design-spreadsheet)

## <a id="design-template"></a>Design the Range Template

To design the Range Template, use [SpreadJS’s code-free spreadsheet designer](https://developer.mescius.com/spreadjs/docs/spdesigner/spdesignerwork). GrapeCity offers an [online Spread Designer here](https://developer.mescius.com/spreadjs/designer/index.html). 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.

![Range Template Cell Types](https://files.grapecity.com/gc-website-media/nxifrraw/02-range.png?width=500&height=359.744094488189)

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

*   name - text
*   img - hyperlink
*   desc - text
*   types - text

![Range Template Cell Types](https://files.grapecity.com/gc-website-media/m35ikqci/03-designer-data-binding.png?width=500&height=359.882005899705)

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.

![Range Template Cell Types](https://files.grapecity.com/gc-website-media/s2nbvlja/04-data-binding-designer.gif?width=500&height=357.4257425742574)

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](https://developer.mescius.com/spreadjs/docs/features/sparkmain/image-sparkline#site_main_content-doc-content_title) 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 Template Cell Types](https://files.grapecity.com/gc-website-media/ed4jqmio/05-range.png?width=398&height=319)

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**:

![Range Template Cell Types](https://files.grapecity.com/gc-website-media/5eydkcmx/06-range.png?width=500&height=306.77816901408454)

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.

![Range Template Cell Types](https://files.grapecity.com/gc-website-media/wnofzmo1/07-hide-rows.png?width=395&height=441)

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:

![Range Template Cell Types](https://files.grapecity.com/gc-website-media/at0d34fp/08-range.png?width=345&height=75)

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](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**.

![Range Template Cell Types](https://files.grapecity.com/gc-website-media/noohk2hk/09-range.png?width=379&height=241)

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**.

![Range Template Cell Types](https://files.grapecity.com/gc-website-media/iuqbagkn/10-save-js-file.png?width=500&height=256.4841498559078)

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

## <a id="set-up"></a>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="https://www.grapecity.com/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="https://www.grapecity.com/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>
```

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:

![Range Template Cell Types](https://files.grapecity.com/gc-website-media/gbufnil5/11-range.png?width=500&height=353.9731682146543)

With SpreadJS initialized, import the spreadsheet data exported from the designer in Step 2 using SpreadJS’s [**fromJSON** method](https://developer.mescius.com/spreadjs/api/v16/classes/GC.Spread.Sheets.Workbook#fromjson).

```
    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();
    };
```

## <a id="configure"></a>Configure Data and Set the Range Template Cell

To retrieve the [PokéAPI](https://pokeapi.co/) data, we will use the [Fetch API’s **fetch()** method](https://developer.mozilla.org/en-US/docs/Web/API/Fetch_API/Using_Fetch) 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](https://developer.mescius.com/spreadjs/api/v16/classes/GC.Spread.Sheets.Worksheet#setarray).

```
    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](https://developer.mescius.com/spreadjs/api/classes/GC.Spread.Sheets.CellTypes.RangeTemplate#constructor), [create a new spreadsheet style with the new cell type](https://developer.mescius.com/spreadjs/api/v16/classes/GC.Spread.Sheets.Style#constructor), and [apply the style to a cell range](https://developer.mescius.com/spreadjs/api/v16/classes/GC.Spread.Sheets.CellRange#setstyle) 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:

![Range Template Cell Types](https://files.grapecity.com/gc-website-media/5qdjypkv/12-range.png?width=500&height=457.8512396694215)

## <a id="design-spreadsheet"></a>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](https://developer.mescius.com/spreadjs/api/classes/GC.Spread.Sheets.Worksheet#setrowheight) and [column width](https://developer.mescius.com/spreadjs/api/classes/GC.Spread.Sheets.Worksheet#setcolumnwidth) of the renderSheet.
*   [Align the scroll bar](https://developer.mescius.com/spreadjs/api/interfaces/GC.Spread.Sheets.IWorkbookOptions#scrollbarmaxalign) with the last row and column of the spreadsheet.
*   [Enable precision pixel scrolling](https://developer.mescius.com/spreadjs/api/interfaces/GC.Spread.Sheets.IWorkbookOptions#scrollbypixel) and [setting the scrollPixel](https://developer.mescius.com/spreadjs/api/interfaces/GC.Spread.Sheets.IWorkb).
*   Hide the [row header](https://developer.mescius.com/spreadjs/api/interfaces/GC.Spread.Sheets.IWorksheetOptions#rowheadervisible), [column headers](https://developer.mescius.com/spreadjs/api/interfaces/GC.Spread.Sheets.IWorksheetOptions#colheadervisible), and [tab strip](https://developer.mescius.com/spreadjs/api/interfaces/GC.Spread.Sheets.IWorkbookOptions#tabstripvisible).
*   [Freeze](https://developer.mescius.com/spreadjs/api/classes/GC.Spread.Sheets.Worksheet#frozenrowcount) 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:

![Range Template Cell Types](https://files.grapecity.com/gc-website-media/luah5lue/01-pokemon-app_1.gif?width=500&height=311.20783460282917)

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](https://gccontent.blob.core.windows.net/gccontent/blogs/spread/20230614-How-to-Use-Range-Template-Cell-Types-in-JavaScript-Spreadsheet-Applications/SpreadJS_Range_Template_Cell_Pok%C3%A9mon.zip). This article only scratches the surface of the full capabilities of the [**GrapeCity SpreadJS**](https://developer.mescius.com/spreadjs) spreadsheet component.

Review the [documentation](https://developer.mescius.com/spreadjs/docs/overview) to see some of the many available features, and check out our [online demos](https://developer.mescius.com/spreadjs/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](https://developer.mescius.com/blogs/spreadjs-v-16-service-pack-1-released).