Knockout is a JavaScript library that helps you create responsive displays, as well as user interfaces that have an underlying data model. SpreadJS can work with Knockout to make binding to data easier to understand. 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 Knockout, go here: KnockoutJS To download the sample used in this blog, click here: SpreadJS Knockout Data Binding To read more about using SpreadJS with Knockout, click here: http://sphelp.grapecity.com/webhelp/SpreadJSWeb/webframe.html#knockout.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 Knockout and jQuery script files:
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta charset="utf-8" />
<title>SpreadJS Data Binding Knockout</title>
<script src="http://code.jquery.com/jquery-2.1.3.min.js" type="text/javascript"></script>
<script type='text/javascript' src='Scripts/knockout-3.4.0.js'></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>
Once this is done, add a script to the page to contain the knockout code as well as a div element that represents the Spread component:
<script>
window.onload = function() {
}
</script>
</head>
<body>
<div id="spreadSheet" style="width: 100%; height: 550px; border: 1px solid gray"></div>
</body>
Load Data
Before adding code to load the JSON file, add a data-bind element to the spreadSheet DIV element in order to use Knockout:
<div id="spreadSheet" data-bind="gcspread-sheets: {
sheetCount: 1,
sheets: [
{
data: items,
columns: [
{ displayName: 'Name', name: 'Name', size: 150},
{ displayName: 'Miles/Gallon', name: 'Miles\_per\_Gallon', size: 110},
{ displayName: 'Cylinders', name: 'Cylinders', size: 100},
{ displayName: 'Displacement (CI)', name: 'Displacement', size: 140},
{ displayName: 'Horsepower', name: 'Horsepower', size: 120},
{ displayName: 'Weight (lbs)', name: 'Weight\_in\_lbs', size: 110},
{ displayName: 'Acceleration (sec.)', name: 'Acceleration', size: 140},
{ displayName: 'Year', name: 'Year', size: 80},
{ displayName: 'Origin', name: 'Origin', size: 80},
{ displayName: 'Car Image', name: 'Image', size: 330}
]
}
]
}" style="width: 100%; height: 550px; border: 1px solid gray"></div>
Next, define a JavaScript function that loads a JSON file via a XMLHttpRequest:
function loadJSON(file, callback) {
var xobj = new XMLHttpRequest();
xobj.overrideMimeType("application/json");
xobj.open('GET', file, true);
xobj.onreadystatechange = function () {
if (xobj.readyState == 4 && xobj.status == "200") {
// Required use of an anonymous callback as .open will NOT return a value but simply returns undefined in asynchronous mode
callback(xobj.responseText);
}
};
xobj.send(null);
}
After that, call that function and create a new function to pass in as the callback. In this callback function, define the ViewModel function and initialize it with the data from the JSON file. Then apply that binding to Knockout:
loadJSON("ClassicCars.json", function (response) {
// Define the ViewModel
var ViewModel = function (items) {
this.items = ko.observableArray(items);
};
var actual_JSON = JSON.parse(response);
var initialData = actual_JSON["Classic Cars"];
var viewModel = new ViewModel(initialData);
//Apply binding
$(function () {
ko.applyBindings(viewModel);
});
});
The data-bound sheet without formatting.
Format SpreadJS
In this same callback function, define the function that formats the SpreadJS component, and apply that formatting to it:
// Get the spread control
var spread = GcSpread.Sheets.findControl(document.getElementById("spreadSheet"));
var activeSheet = spread.getActiveSheet();
spread.isPaintSuspended(true);
// Format the Spread component
activeSheet.setRowHeaderVisible(false);
formatSpread();
spread.isPaintSuspended(false);
function formatSpread() {
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);
}
}
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 Knockout to implement data binding functionality through a ViewModel and Knockout bindings. The data was loaded from a JSON file, and the bindings allowed that data to be bound to different columns in the SpreadJS instance. The extensibility of Knockout means that binding SpreadJS to different data types becomes a stress-free task. To learn more about SpreadJS and to download a trial, click here.
MESCIUS inc.