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.
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:
Note, if you receive an error like this:
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:
- 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
- Modify the module.ts:
// import { SpreadSheetsModule } from "@grapecity/spread-sheets-angular";
import { SpreadSheetsModule } from "../component/spread-sheets-angular/gc.spread.sheets.angular";
- Use ng build –prod to build again
- 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: