How to Create a JavaScript Excel Spreadsheet Template with Data Binding Features

August 31 2021

One very common uses case is the need to create master templates and bind data to that template to create the needed company reports. For example, you have standard templates your company uses, such as healthcare/patient record forms, invoices, financial statements, and budgets, and you need to bind the appropriate data to that template to create these reports.

Fortunately, SpreadJS makes it simple to accomplish this using your existing Excel templates or by creating your own. This article demonstrates how to create a template with the SpreadJS Designer, add a data source schema, load the template, and add data to a webpage.

To download the sample and follow along, click here.

Creating a Template

The first step is to open up the SpreadJS Designer (the installer of which is included with the SpreadJS download in the Designer>Runtime folder) and start putting together a design that fits your requirements. For this tutorial, we use the Invoice template that has already been created and is included with the sample download:

invoice

You would just simply create a workbook in the SpreadJS Designer and set cell styles, formulas, and add a table and images. The Designer provides all of the Excel-like features you would expect, so creating a template is simple and intuitive. Once you have a design that suits your liking, you can move on to adding a data source schema to that template.

Download Now!

Adding Data Source Schema

The SpreadJS Designer gives you the ability to add data source bindings directly into your templates. In this case, we already have an idea of the fields that are in the data source:

Customer
- Name
- Company
- Address 1
- Address 2
Company
- Name
- Address 1
- Address 2
Number
Date

To add this schema, we can simply open the DATA tab and click on the "Template" button, which will open up the template:

spreadjs

To add data nodes to it, you just need to hover over the "Source" text and add a node to it:

node

Now you can just click and drag data items from the Field List into cells in the workbook. This will show the data fields as "[Field name]" in the cell. That's all that is needed to create a template with a predefined data schema in it. You can save this to SSJSON and export it to JS in the SpreadJS Designer.

Create Data and Cell Binding Source

Once we have that JS file for our template, we can write some code for importing it. In this case, we will want to reference the SpreadJS library, a separate JS file with the license, the JS file for the template we created (called "billingInvoice.js"), and a separate file to hold our JS code. We can also create a DIV element to host the SpreadJS instance:

<!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" />
    <link rel="stylesheet" type="text/css" href="node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
    <script src="node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
    <script src="license.js" type="text/javascript"></script>
    <script src="billingInvoice.js" type="text/javascript"></script>
    <script src="app.js" type="text/javascript"></script>
</head>
<body style="height:100%">
    <div id="ss" style="width:100%;height:75%"></div>
</body>
</html>

We also need to edit our data file a bit. In this case, we just want to rename the variable to "data" and add brackets around the actual JSON:

window.onload = function () {
    var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 1 });
    initSpread(spread);
};

function initSpread(spread) {
    var sd = data;
    if (sd.length > 0) {
        if (!spread) {
            return;
        }

        function Company(name, address1, address2) {
            this.name = name;
            this.address1 = address1;
            this.address2 = address2;
        }

        function Customer(name, company, address1, address2) {
            this.name = name;
            this.company = company;
            this.address1 = address1;
            this.address2 = address2;
        }

        function Record(quantity, details, unitPrice) {
            this.quantity = quantity;
            this.details = details;
            this.unitPrice = unitPrice;
        }

        function Invoice(customer, company, number, date, records) {
            this.customer = customer;
            this.company = company;
            this.number = number;
            this.date = date;
            this.records = records;
        }
    }
}

With those functions set up, we can now create the actual data and set it to some variables:

var customer = new Customer("KIM ABERCROMBIE", "Fabrikam, Inc.", "1234 First Street", "Forest, OR 12345"),
    company = new Company("ADVENTURE WORKS", "23456 Maple Street", "Orange Grove, CA 09876"),
    records = [new Record(2, "Widgets", 14.95), new Record(5, "Washers", 9.95)],
    invoice = new Invoice(customer, company, "0005", "26 August 2021", records);

The main variable that holds the data is the Invoice, so we will use that as the CellBindingSource, which will connect the data to the data schema we defined earlier in the SpreadJS Designer:

var dataSource = new GC.Spread.Sheets.Bindings.CellBindingSource(invoice);

Set Binding Paths

With the data source completely set up, we can simply set it as the data source for the sheet and set the binding paths for the fields in the table:

spread.suspendPaint();

// Set binding paths     
spread.fromJSON(sd[0]);
var sheet = spread.getSheet(0);
sheet.setDataSource(dataSource);
var table = sheet.tables.findByName("InvoiceDetails");
table.autoGenerateColumns(false);
var tableColumn1 = new GC.Spread.Sheets.Tables.TableColumn();
tableColumn1.name("Quantity");
tableColumn1.dataField("quantity");
var tableColumn2 = new GC.Spread.Sheets.Tables.TableColumn();
tableColumn2.name("Details");
tableColumn2.dataField("details");
var tableColumn3 = new GC.Spread.Sheets.Tables.TableColumn();
tableColumn3.name("Unit Price");
tableColumn3.dataField("unitPrice");
table.bindColumns([tableColumn1, tableColumn2, tableColumn3]);
table.bindingPath("records");

spread.resumePaint();

invoice