Tuesday, 5 April, 2016 UTC
Summary
AngularJS is a JavaScript library that is used for creating dynamic displays in web-applications. SpreadJS can work with Angular to make binding to data easier to understand by extending HTML. This blog is a part of the SpreadJS Data Binding series, which centers on binding the same data to SpreadJS using different JavaScript libraries.
To download Angular, go here: AngularJS
To download the sample used in this blog, click here: SpreadJSAngular
To read more about using SpreadJS with Angular, click here: http://sphelp.grapecity.com/webhelp/SpreadJSWeb/webframe.html#angular.html
Set Up the Project
Create a new empty ASP.NET Web project, and add a new html file in Visual Studio 2015. In this file, add references to the SpreadJS script and css files, as well as the Angular and jQuery script files:
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta charset="utf-8" />
<title>SpreadJS Data Binding Angular</title>
<script src="http://code.jquery.com/jquery-2.1.3.min.js" type="text/javascript"></script>
<script src="Scripts/angular.js" type="text/javascript"></script>
<script src="Scripts/angular-route.js" type="text/javascript"></script>
<link href="http://cdn.grapecity.com/spreadjs/hosted/css/gcspread.sheets.excel2013white.9.40.20153.0.css" rel="stylesheet" type="text/css" />
<script type="text/javascript" src="http://cdn.grapecity.com/spreadjs/hosted/scripts/gcspread.sheets.all.9.40.20153.0.min.js"></script>
</head>
<body>
</body>
</html>
In addition, add a reference to the Angular Spread JavaScript file in order to make SpreadJS work with Angular:
<script type="text/javascript" src="http://cdn.grapecity.com/spreadjs/hosted/scripts/interop/angular.gcspread.sheets.9.40.20153.0.min.js"></script>
Once this is done, add a script to the page to contain the Angular code as well as an HTML element that represents the Spread component, setting up the columns that will be bound to data:
<head>
<script>
window.onload = function() {
}
</script>
</head>
<body id="spreadController">
<h1>Classic Cars 1970 - 1982</h1>
<gcspread-sheets id="spreadSheet" style="width: 100%; height: 550px; border: 1px solid gray">
<sheets>
<sheet datasource="cars">
<columns>
<column dataField="Name" headerText="Name" width="150"></column>
<column dataField="Miles_per_Gallon" headerText="Miles/Gallon" width="110"></column>
<column dataField="Cylinders" headerText="Cylinders" width="100"></column>
<column dataField="Displacement" headerText="Displacement (CI)" width="140"></column>
<column dataField="Horsepower" headerText="Horsepower" width="120"></column>
<column dataField="Weight_in_lbs" headerText="Weight (lbs)" width="110"></column>
<column dataField="Acceleration" headerText="Acceleration (sec.)" width="140"></column>
<column dataField="Year" headerText="Year" width="80"></column>
<column dataField="Origin" headerText="Origin" width="80"></column>
<column dataField="Image" headerText="Car Image" width="330"></column>
</columns>
</sheet>
</sheets>
</gcspread-sheets>
</body>
Load Data
Before adding code to load the JSON file, create a controller and a module for the Angular application:
<script>
var initialData = null;
var app = angular.module("spreadApp", ["gcspreadsheets", "ngRoute"]);
app.controller("spreadCtrl", function ($scope, $http) {
});
</script>
Within that controller function, specify a function that will load the data from the JSON file into the “cars” variable for the application:
app.controller("spreadCtrl", function ($scope, $http) {
$scope.loadData = function () {
$http.get('ClassicCars.json').success(function (data) {
$scope.cars = data["Classic Cars"];
});
};
});
Going back to the “gcspread-sheets” HTML element that contains the Spread instance on the page, add this controller and initialization function so that the Spread instance is populated with the data from the JSON file:
<body id="spreadController" ng-controller="spreadCtrl" ng-init="loadData()">
<h1>Classic Cars 1970 - 1982</h1>
<gcspread-sheets id="spreadSheet" style="width: 100%; height: 550px; border: 1px solid gray">
<sheets>
<sheet datasource="cars">
<columns>
//…
</columns>
</sheet>
</sheets>
</gcspread-sheets>
</body>
The data-bound sheet without formatting.
Format SpreadJS
In the controller function for the Spread instance, define the function that formats the SpreadJS component, and apply that formatting to it:
app.controller("spreadCtrl", function ($scope, $http) {
//…
$scope.formatSpread = function () {
// Only format the Spread after the data has been loaded
$http.get('ClassicCars.json').success(function (data) {
// Get the spread control
var spread = GcSpread.Sheets.findControl(document.getElementById("spreadSheet"));
var activeSheet = spread.getActiveSheet();
spread.isPaintSuspended(true);
activeSheet.setRowHeaderVisible(false);
for (var i = 0; i < activeSheet.getRowCount() ; i++) {
activeSheet.getColumn(0).wordWrap(true);
activeSheet.getRow(i).font("12pt arial");
activeSheet.setRowHeight(i, 210);
activeSheet.getRow(i).borderBottom(new GcSpread.Sheets.LineBorder("Green", GcSpread.Sheets.LineStyle.thick));
if (activeSheet.getValue(i, 9) != null) {
var carImage = activeSheet.getValue(i, 9);
activeSheet.setValue(i, 9, null);
activeSheet.getCell(i, 9).backgroundImage(carImage);
}
activeSheet.getRow(i).vAlign(GcSpread.Sheets.VerticalAlign.center);
activeSheet.getRow(i).hAlign(GcSpread.Sheets.HorizontalAlign.center);
}
var cellRange = new GcSpread.Sheets.Range(0, 0, activeSheet.getRowCount(), 10);
var hideRowFilter = new GcSpread.Sheets.HideRowFilter(cellRange);
activeSheet.rowFilter(hideRowFilter);
activeSheet.setRowCount(2, GcSpread.Sheets.SheetArea.colHeader);
activeSheet.getColumn(0).borderRight(new GcSpread.Sheets.LineBorder("Green", GcSpread.Sheets.LineStyle.thin));
activeSheet.setRowHeight(0, 30, GcSpread.Sheets.SheetArea.colHeader);
activeSheet.addSpan(0, 1, 1, 2, GcSpread.Sheets.SheetArea.colHeader);
activeSheet.getCell(0, 1, GcSpread.Sheets.SheetArea.colHeader).value("Fuel Economy & Acceleration");
activeSheet.colRangeGroup.group(1, 2);
activeSheet.getColumn(2).borderRight(new GcSpread.Sheets.LineBorder("Green", GcSpread.Sheets.LineStyle.thin));
activeSheet.addSpan(0, 3, 1, 3, GcSpread.Sheets.SheetArea.colHeader);
activeSheet.getCell(0, 3, GcSpread.Sheets.SheetArea.colHeader).value("Engine Details");
activeSheet.addSpan(0, 6, 1, 4, GcSpread.Sheets.SheetArea.colHeader);
activeSheet.getCell(0, 6, GcSpread.Sheets.SheetArea.colHeader).value("Car Details");
activeSheet.getColumn(5).borderRight(new GcSpread.Sheets.LineBorder("Green", GcSpread.Sheets.LineStyle.thin));
activeSheet.setRowHeight(1, 30, GcSpread.Sheets.SheetArea.colHeader);
var headerStyle = new GcSpread.Sheets.Style();
headerStyle.backColor = "Green";
headerStyle.foreColor = "White";
headerStyle.hAlign = GcSpread.Sheets.HorizontalAlign.center;
headerStyle.vAlign = GcSpread.Sheets.VerticalAlign.center;
for (var i = 0; i < activeSheet.getColumnCount() ; i++) {
activeSheet.setStyle(0, i, headerStyle, GcSpread.Sheets.SheetArea.colHeader);
activeSheet.setStyle(1, i, headerStyle, GcSpread.Sheets.SheetArea.colHeader);
}
spread.isPaintSuspended(false);
});
};
});
Once the formatting function has been defined, call it when the window is loaded:
window.onload = function () {
angular.element(document.getElementById('spreadSheet')).scope().formatSpread();
}
If done correctly, the data from the JSON file should show up in the SpreadJS instance on the page, and the component should be formatted like the screenshot below:
The data-bound sheet with formatting.
In this tutorial, SpreadJS was combined with Angular to implement data binding functionality using an Angular controller and an initialization function. The data was loaded from a JSON file, and the pre-defined column attributes allowed that data to be bound to different columns in the SpreadJS instance. Angular can be combined with SpreadJS to provide an intuitive way to bind data and display it to the user.
To learn more about SpreadJS and to download a trial, click here: http://spread.grapecity.com/Downloads/