SpreadJS and Angular Data Binding

  • 0 Comments

SpreadJS is an extremely powerful Excel-like spreadsheet component. SpreadJS is framework agnostic, meaning SpreadJS supports being used within several JavaScript frameworks. This includes Angular!

One of the powerful features of SpreadJS is data binding. We have created a tutorial that breaks down how to use Data Binding in SpreadJS with an Angular project.

Download Now!

Angular Project Set-Up

We first will need to set-up an Angular project. For this tutorial we will be using an Angular 10 project.

Create new Angular 10 project: ng new project-name. For this example, the project is named spreadJS-angular:

ng new spreadJS-angular

Now, we will change the directory to our new project

cd spreadJS-angular

Run your project:

ng serve –open

Notice the Angular 10 welcome page is now being served to: http://localhost:4200/

Add SpreadJS and Data Binding to an Angular Project

Now we have an Angular 10 project up and running we are going to remove its welcome page contents and add SpreadJS!

First, install SpreadJS using the npm package: npm install @grapecity/spread-sheets & npm install @grapecity/spread-sheets-angular. Then, configure the SpreadJS CSS in angular.json as shown below:

{ 
  ...
  "projects":{
    "spread-sheets-angular-cli":{
    ...
    "architect":{
      ...
      "build":{
        ...
        options:{
          ...
          "styles": [
            "[node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2016darkGray.css]"         
          ],
          ...
         }
         ... 
       }
       ...
      }
      ...
    }
    }
    ... 
  }

Now we will use SpreadJS in our application. We will modify the module.ts for importing the SpreadJS module:


import { BrowserModule } from "@angular/platform-browser";
import { NgModule } from "@angular/core";
import { AppComponent } from "./app.component";
import { SpreadSheetsModule } from "@grapecity/spread-sheets-angular";
@NgModule({
  declarations: [AppComponent],
  imports: [BrowserModule, SpreadSheetsModule],
  providers: [],
  bootstrap: [AppComponent], 
  }) 
  export class AppModule {}

Modify the componenet.html for viewing the SpreadJS component:

<gc-spread-sheets
[backcolor]="spreadBackColor"
[hoststyle]="hostStyle"
(workbookinitialized)="workbookInit($event)"
>
  <gc-worksheet [name]="sheetName" [datasource]="data">
  <gc-column dataField="Name" width="300"></gc-column>
  <gc-column dataField="Category" [width]="columnwidth"></gc-column>
  <gc-column
    datafield="Price"
    [width]="columnWidth"
    formatter="$ #.00"
    ></gc-column>
    <gc-column dataField="Shopping Place" [width]="columnwidth"></gc-column> 
    </gc-worksheet>
  </gc-spread-sheets>

Modify the component.ts to prepare the data for the SpreadJS component:

import { Component } from "@angular/core";
import * as GC from "@grapecity/spread-sheets";
@Component({
  selector: "app-root",
  templateUrl: "./app.component.html",
  styleUrls: ["./app.component.css"],
})
export class AppComponent {
  spreadBackColor = "aliceblue";
  sheetName = "Goods List";
  hostStyle = {
    width: "800px",
    height: "600px",
  };
  data = [
        {
      Name: "Apple",
      Category: "Fruit",
      Price: 1,
      "Shopping Place": "Wal-Mart",
    },
    {
      Name: "Potato",
      Category: "Fruit",
      Price: 2.01,
      "Shopping Place": "Other",
    },
    {
      Name: "Tomato",
      Category: "Vegetable",
      Price: 3.21,
      "Shopping Place": "Other",
    },
    {
      Name: "Sandwich",
      Category: "Food",
      Price: 2,
      "Shopping Place": "Wal-Mart",
    },
    {
      Name: "Hamburger",
      Category: "Food",
      Price: 2,
      "Shopping Place": "Wal-Mart",
    },
    {
      Name: "Grape",
      Category: "Fruit",
      Price: 4,
      "Shopping Place": "Sun Store",
    },
  ];
  columnWidth = 100;
  workbookInit(args) {
    let spread: GC.Spread.Sheets.Workbook = args.spread;
    let sheet = spread.getActiveSheet();
    sheet.getCell(0, 0).text("My SpreadJS Angular Project").foreColor("blue");
  }
}

Run ng serve –open. In your browser, http://localhost:4200/ you will now see the SpreadJS component with a list of items: Add SpreadJS Data Binding

Note, if you receive an error like this: Error

It can fix by copying the Angular Wrapper typescript source file into the project and refer as a file module.

Here are steps to follow:

  1. npm install, then copy the spread.sheets.angular.ts (only this file) from _nodemodules/@grapecity/spread-sheets-angular/dist folder to src/component/spread-sheets-angular folder
  2. Modify the module.ts:
// import { SpreadSheetsModule } from "@grapecity/spread-sheets-angular";
import { SpreadSheetsModule } from "../component/spread-sheets-angular/gc.spread.sheets.angular";
  1. Use ng build –prod to build again
  2. Run ng serve –open

SpreadJS Data Binding with JSON Data from URL

Get JSON from URL by first storing the URL in a variable and then fetching the JSON data from the URL and have the response be returned as a JSON object.

    // 1.) Store URL in a variable
    let api_url = 'https://api.thedogapi.com/v1/breeds';
    // Invoke
    getJSONFromURL();
    // 1.) Create a getJSONFromURL custom function
    async function getJSONFromURL() {
      // 1 - Fetch the JSON data
      const response = await fetch(api_url);
      // 1 - Store JSON data returned from the Fetch in variable
      const api_data = await response.json();
    }

We will create a custom function that will take specified data, then create a variable to store the JSON data and a variable for each column in the data source:

// 2 - Variable to hold JSON data
    var actual_JSON = api_data;
      // 2 - Variables for each column of the data source
      var idColumn = { name: "id", displayName: "ID", size: 70 };
      var nameColumn = { name: "name", displayName: "Name", size: 250 };
      var originColumn = {
        name: "origin",
        displayName: "Origin",
        size: 250,
        };
        var bred_forColumn = {
          name: "bred_for",
          displayName: "Bred for",
          size: 250,
        };
        var breed_groupColumn = {
          name: "breed_group",
          displayName: "Breed Group",
          size: 150,
        };
          var life_spanColumn = {
          name: "life_span",
          displayName: "Life Span",
          size: 150,
        };
          var tempermentColumn = {
          name: "temperament",
          displayName: "Temperament",
          size: 550,
          };

Next, set the data source using the worksheets setDataSource method and then we will bind the columns to a specified data field using the bindColumn method.

    // 3.) Set data source and bind columns to specified data fields
    // 3 - Set data source with setDataSource method
    sheet.setDataSource(actual_JSON);
    // 3 - Specify what data fields to bind to the columns
    sheet.bindColumn(0, idColumn);
    sheet.bindColumn(1, nameColumn);
    sheet.bindColumn(2, originColumn);
    sheet.bindColumn(3, bred_forColumn);
    sheet.bindColumn(4, breed_groupColumn);
    sheet.bindColumn(5, life_spanColumn);
    sheet.bindColumn(6, tempermentColumn);

Finally, we will set the column count to 7 and set the Spread instances options scrollbarMaxAlign to True to specify for the scrollbar to align with the last row and column of the active sheet remove the additional grey space.

// 4.) Set the column count to 7 and set the spread instances scrollbar max align to True
 // 4 - set the column count with the setColumnCount method
          sheet.setColumnCount(7);
 // 4 - set the scrollbar max align to True to remove the additional grey space
          spread.options.scrollbarMaxAlign = true;

By using the code shared here in this tutorial, your SpreadJS instance will now have the data coming from a URL being bound to specified columns in your Angular project:

Final Json Data

Download Now!

Mackenzie Albitz - Product Marketing Specialist

Mackenzie Albitz

Product Marketing Specialist