Bind Tables

SpreadJS allows users to bind a table to a data source using cell-level binding.

Table binding can be done in two ways. Users can either use the bindColumns method or the bind method to bind a table to a data source while working with spreadsheets.

The following rules must be kept in mind while carrying out the table binding operations in the worksheets:

Setting a value changes the data source.
Adding or deleting rows changes the data source.
Adding or deleting columns does not change the data source.
Removing, clearing, moving, or resizing the table does not change the data source.
Binding adjusts the table row count automatically (but column count stays the same).
Formulas are not saved to the data source.

Bind Table Columns

For binding all the table columns in a worksheet, simply bind the sheet to a data source and then set the bindColumns and bindingPath methods in the Table class. Users can also specify information for the table column with dataField and name methods in the TableColumn class.

The following code binds a data source to the table using the bindColumns method and the bindingPath method.

$(function ()
{
    // Generate two data sources
    function Company(name, logo, slogan, address, city, phone, email) {
        this.name = name;
        this.logo = logo;
        this.slogan = slogan;
        this.address = address;
        this.city = city;
        this.phone = phone;
        this.email = email;
    }
    function Customer(id, name, company) {
        this.id = id;
        this.name = name;
        this.company = company;
    }
    function Record(description, quantity, amount) {
        this.description = description;
        this.quantity = quantity;
        this.amount = amount;
    }
    function Invoice(company, number, date, customer, receiverCustomer, records) {
        this.company = company;
        this.number = number;
        this.date = date;
        this.customer = customer;
        this.receiverCustomer = receiverCustomer;
        this.records = records;
    }
    var company1 = new Company("Baidu", null, "We know everything!", "Beijing 1st road", "Beijing", "010-12345678", "baidu@baidu.com"),
    company2 = new Company("Tecent", null, "We have everything!", "Shenzhen 2st road", "Shenzhen", "0755-12345678", "tecent@qq.com"),
    company3 = new Company("Alibaba", null, "We sell everything!", "Hangzhou 3rd road", "Hangzhou", "0571-12345678", "alibaba@alibaba.com"),
    customer1 = new Customer("A1", "employee 1", company2),
    customer2 = new Customer("A2", "employee 2", company3),
    records1 = [new Record("Finance charge on overdue balance at 1.5%", 1, 150), new Record("Invoice #100 for $1000 on 2014/1/1", 1, 150)],
    records2 = [new Record("Purchase server device", 2, 15000), new Record("Company travel", 100, 1500), new Record("Company Dinner", 100, 200)],
    invoice1 = new Invoice(company1, "00001", new Date(2014, 0, 1), customer1, customer1, records1),
    invoice2 = new Invoice(company2, "00002", new Date(2014, 6, 6), customer2, customer2, records2),
    dataSource1 = new GC.Spread.Sheets.Bindings.CellBindingSource(invoice1),
    dataSource2 = new GC.Spread.Sheets.Bindings.CellBindingSource(invoice2);
    function BindingPathCellType() {
        GC.Spread.Sheets.CellTypes.Text.call(this);
    }
    BindingPathCellType.prototype = new GC.Spread.Sheets.CellTypes.Text();
    BindingPathCellType.prototype.paint = function (ctx, value, x, y, w, h, style, context) {
        if \(value === null \|\| value === undefined\) \{
            var sheet = context.sheet, row = context.row, col = context.col;
            if \(sheet && \(row === 0 \|\| \!\!row\) && \(col === 0 \|\| \!\!col\)\) \{
                var bindingPath = sheet.getBindingPath(context.row, context.col);
                if (bindingPath) {
                    value = "[" + bindingPath + "]";
                }
            }
        }
        GC.Spread.Sheets.CellTypes.Text.prototype.paint.apply(this, arguments);
    };
    var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {sheetCount: 1});
    activeSheet = spread.getActiveSheet();
    spread.suspendPaint();
    activeSheet.name("FINANCE CHARGE");
    var bindingPathCellType = new BindingPathCellType();
    activeSheet.getCell(1, 2).bindingPath("company.slogan").cellType(bindingPathCellType).vAlign(GC.Spread.Sheets.VerticalAlign.bottom);
    activeSheet.getCell(1, 4).value("INVOICE").foreColor("#58B6C0").font("33px Arial");
    activeSheet.getCell(3, 1).bindingPath("company.name").cellType(bindingPathCellType).foreColor("#58B6C0").font("bold 20px Arial");
    activeSheet.getCell(5, 1).bindingPath("company.address").cellType(bindingPathCellType);
    activeSheet.getCell(5, 3).value("INVOICE NO.").font("bold 15px Arial");
    activeSheet.getCell(5, 4).bindingPath("number").cellType(bindingPathCellType);
    activeSheet.getCell(6, 1).bindingPath("company.city").cellType(bindingPathCellType);
    activeSheet.getCell(6, 3).value("DATE").font("bold 15px Arial");
    activeSheet.getCell(6, 4).bindingPath("date").cellType(bindingPathCellType).formatter("MM/dd/yyyy").hAlign(GC.Spread.Sheets.HorizontalAlign.left);
    activeSheet.getCell(7, 1).bindingPath("company.phone").cellType(bindingPathCellType);
    activeSheet.getCell(7, 3).value("CUSTOMER ID").font("bold 15px Arial");
    activeSheet.getCell(7, 4).bindingPath("customer.id").cellType(bindingPathCellType);
    activeSheet.getCell(8, 1).bindingPath("company.email").cellType(bindingPathCellType);
    activeSheet.getCell(10, 1).value("TO").font("bold 15px Arial");
    activeSheet.getCell(10, 3).value("SHIP TO").font("bold 15px Arial");
    activeSheet.getCell(11, 1).bindingPath("customer.name").cellType(bindingPathCellType).textIndent(10);
    activeSheet.getCell(12, 1).bindingPath("customer.company.name").cellType(bindingPathCellType).textIndent(10);
    activeSheet.getCell(13, 1).bindingPath("customer.company.address").cellType(bindingPathCellType).textIndent(10);
    activeSheet.getCell(14, 1).bindingPath("customer.company.city").cellType(bindingPathCellType).textIndent(10);
    activeSheet.getCell(15, 1).bindingPath("customer.company.phone").cellType(bindingPathCellType).textIndent(10);
    activeSheet.getCell(11, 4).bindingPath("receiverCustomer.name").cellType(bindingPathCellType);
    activeSheet.getCell(12, 4).bindingPath("receiverCustomer.company.name").cellType(bindingPathCellType);
    activeSheet.getCell(13, 4).bindingPath("receiverCustomer.company.address").cellType(bindingPathCellType);
    activeSheet.getCell(14, 4).bindingPath("receiverCustomer.company.city").cellType(bindingPathCellType);
    activeSheet.getCell(15, 4).bindingPath("receiverCustomer.company.phone").cellType(bindingPathCellType);
    activeSheet.addSpan(17, 1, 1, 2);
    activeSheet.getCell(17, 1).value("JOB").foreColor("#58B6C0").font("bold 12px Arial");
    activeSheet.addSpan(17, 3, 1, 2);
    activeSheet.getCell(17, 3).value("PAYMENT TERMS").foreColor("#58B6C0").font("bold 12px Arial");
    activeSheet.addSpan(18, 1, 1, 2);
    activeSheet.getCell(18, 1).backColor("#DDF0F2");
    activeSheet.addSpan(18, 3, 1, 2);
    activeSheet.getCell(18, 3).value("Due on receipt").backColor("#DDF0F2").foreColor("#58B6C0").font("12px Arial");
    activeSheet.getRange(17, 1, 2, 4, GC.Spread.Sheets.SheetArea.viewport).setBorder(new GC.Spread.Sheets.LineBorder("#58B6C0", GC.Spread.Sheets.LineStyle.thin), {
            top: true,
            bottom: true,
            innerHorizontal: true
        });
    var table = activeSheet.tables.add("tableRecords", 20, 1, 4, 4, GC.Spread.Sheets.Tables.TableThemes.light6);
    table.autoGenerateColumns(false);
    var tableColumn1 = new GC.Spread.Sheets.Tables.TableColumn();
    tableColumn1.name("DESCRIPTION");
    tableColumn1.dataField("description");
    var tableColumn2 = new GC.Spread.Sheets.Tables.TableColumn();
    tableColumn2.name("QUANTITY");
    tableColumn2.dataField("quantity");
    var tableColumn3 = new GC.Spread.Sheets.Tables.TableColumn();
    tableColumn3.name("AMOUNT");
    tableColumn3.dataField("amount");
    table.bindColumns([tableColumn1, tableColumn2, tableColumn3]);
    table.bindingPath("records");
    table.showFooter(true);
    table.setColumnName(3, "TOTAL");
    table.setColumnValue(2, "TOTAL DUE");
    table.setColumnDataFormula(3, "=[@QUANTITY]\*[@AMOUNT]");
    table.setColumnFormula(3, "=SUBTOTAL(109,[TOTAL])");
    activeSheet.getCell(26, 1).formula('="Make all checks payable to "&B4&". THANK YOU FOR YOUR BUSINESS!"').foreColor("gray").font("italic 14px Arial");
    activeSheet.options.allowCellOverflow = true;
    //Adjust row height and column width
    activeSheet.setColumnWidth(0, 5);
    activeSheet.setColumnWidth(1, 300);
    activeSheet.setColumnWidth(2, 115);
    activeSheet.setColumnWidth(3, 125);
    activeSheet.setColumnWidth(4, 155);
    activeSheet.setRowHeight(0, 5);
    activeSheet.setRowHeight(1, 40);
    activeSheet.setRowHeight(2, 10);
    activeSheet.setRowHeight(17, 0);
    activeSheet.setRowHeight(18, 0);
    activeSheet.setRowHeight(19, 0);
    activeSheet.setRowHeight(25, 10);
    activeSheet.options.gridline = {showVerticalGridline: false, showHorizontalGridline: false};
    //Set a data source
    //activeSheet.setDataSource(dataSource1);
    activeSheet.setDataSource(dataSource2);
    spread.resumePaint();
})

Bind Formatter and Cell Types To Table Columns

With the advanced column binding feature provided by SpreadJS, users can also bind formatter and various cell types with table columns along with the data fields and names. Further, users can also convert formula functions inside a table column.

The bind method can be used to bind the table to a field containing multiple records and the table columns to the data fields of their corresponding records. When users set a different data source for populating the table data, then the table will automatically bind to their corresponding records.

The following code shows how to use the bind method for binding data in table columns.

// Initializing Spread
var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });
       
// Get the activesheet
var activeSheet = spread.getActiveSheet();
       
// Data
var data = {
    name: 'Jones', region: 'East',
    sales: [
        { orderDate: '1/6/2013', item: 'Pencil', units: 95, cost: 1.99, isMakeMoney: true },
        { orderDate: '4/1/2013', item: 'Binder', units: 60, cost: 4.99, isMakeMoney: false },
        { orderDate: '6/8/2013', item: 'Pen Set', units: 16, cost: 15.99, isMakeMoney: false }
    ]
};

var convert = function (item) {
    return item['cost'] + '$';
}
       
// Add table
var table = activeSheet.tables.add('tableSales', 0, 0, 5, 5);
var tableColumn1 = new GC.Spread.Sheets.Tables.TableColumn(1, "orderDate", "Order Date", "d/M/yy");
var tableColumn2 = new GC.Spread.Sheets.Tables.TableColumn(2, "item", "Item");
var tableColumn3 = new GC.Spread.Sheets.Tables.TableColumn(3, "units", "Units");
var tableColumn4 = new GC.Spread.Sheets.Tables.TableColumn(4, "cost", "Cost", null, null, convert);
var tableColumn5 = new GC.Spread.Sheets.Tables.TableColumn(5, "isMakeMoney", "IsMakeMoney", null, new GC.Spread.Sheets.CellTypes.CheckBox());
  
table.autoGenerateColumns(false);
        
// Bind table using the bind() method
table.bind([tableColumn1, tableColumn2, tableColumn3, tableColumn4, tableColumn5], 'sales', data);
for (var i = 0; i < 5; i++)
    activeSheet.setColumnWidth(i, 110.0, GC.Spread.Sheets.SheetArea.viewport);

Binding to a Data Manager Table

SpreadJS allows you to bind a new/existing worksheet table to a data manager table as a data source, which results in dynamic data interaction and displays updated information in the table. To bind the worksheet table to a data manager table, the table.bind method is used after the table is added.

After binding the tables, if the data manager table's row and column counts exceed the row and column counts of the worksheet then, user should extend the range of the worksheet by using setRowCount/setColumnCount method.

It is not necessary to specify the binding columns and binding path in table.bind method, but it should at least pass an empty array. However, if you specify the binding columns in the table.bind method, then you need to specify the data field of the TableColumn to match the field in the data manager table. Formulas can't be used as the binding field.

Binding columns show different results depending upon the boolean value of autoGenerateColumns property.

The following points should be considered while binding a worksheet table to a data manager table:

You cannot resize the worksheet table using the resize handle to fit the data manager table.
You can only modify how columns are displayed through binding information, but you cannot add/remove/update the actual columns in the data manager table.
Columns that are not in the data manager table can have formulas, while columns that are in the data manager table cannot have formulas.
If column header holds data or not, and you edit the column header:
- By entering a formula, the column will switch to a formula column if the data already exist in column header. Else it will create a new column displaying calculated data. However, you cannot manually edit the individual cells' formula in that column.
- By entering a data field name, the table will try to find that field:
  - If the field exists in the table, it will show the corresponding values.
  - If the field does not exist in the table, the column will show empty, and you cannot set a value in it.
Binding to a data manager table allows you to sort and filter data within the constraints of the table’s structure, focusing on the data columns available in the table.

The following code is used to bind a worksheet table to the data manager table by passing an empty array.

 spread.options.allowDynamicArray = true;
 spread.options.showHorizontalScrollbar = false;
 const dataManager = spread.dataManager();
 const spreadNS = GC.Spread.Sheets;
 // Data Manager Table Binding to Sheet Table
 let sheet1 = spread.getSheet(0);
 sheet1.name("Data Manager Table Binding");
 const productsTable = dataManager.addTable("products", {
     remote: {
         read: {
             url: 'https://northwind.vercel.app/api/products'
         }
     }
 });
 const ordersTable = dataManager.addTable("orders", {
     data: [
         { orderDate: '1/6/2013', item: 'Pencil111', units: 95, cost: 1.99, isDelivered: true },
         { orderDate: '4/1/2013', item: 'Binder', units: 60, cost: 4.99, isDelivered: false },
         { orderDate: '6/8/2013', item: 'Pen Set', units: 16, cost: 15.99, isDelivered: false }
     ]
 });
 Promise.all([productsTable.fetch(), ordersTable.fetch()]).then(() => {
     const table = sheet1.tables.add('tableSales', 0, 0, 5, 5);
     table.bind(
         [], // <--- could specify the binding columns or not, but it should pass an empty array at least
         null, // <--- no need to specify the bind path
         "products" // <--- bind the data manager table by table name
     );

     // Rebind Another Table

     //table.bind(
     //  [],   // <--- could specify the binding columns or not, but it should pass an empty array at least
     // null, // <--- no need to specify the bind path
     //"orders"); // <---  bind the data manager table by table name
     // table.bind([], null, ordersTable); // <--- bind the data manager table

 });

The following code displays different result of binding columns when autoGenerateColumns property is enabled/disabled.

When the autoGenerateColumns property is set to false, the columns in the worksheet table are created as per the columns in data manager table.

 // Auto Generate Columns - False
let sheet4 = spread.getSheet(3);
sheet4.name("AutoGenerate Columns-FALSE");
ordersTable.fetch().then(() => {
    // The table could be existing
    const table = sheet4.tables.add('tableSales3', 0, 0, 5, 7); // << ---- the table column count is bigger than the data manager table column count
    // Define the table columns
    const tableColumn1 = new spreadNS.Tables.TableColumn(0, "orderDate", "Order Date", "yyyy-mm-dd");
    const tableColumn2 = new spreadNS.Tables.TableColumn(1, "item", "Item");
    const tableColumn3 = new spreadNS.Tables.TableColumn(2, "units", "Units", '#,##0');
    const tableColumn4 = new spreadNS.Tables.TableColumn(3, "cost", "Cost");
    const tableColumn5 = new spreadNS.Tables.TableColumn(4, "isDelivered", "IsDelivered", null, new GC.Spread.Sheets.CellTypes.CheckBox());
    // It's necessary to disable auto generate columns for binding Table Columns
    table.autoGenerateColumns(false);
    // Bind the table columns and data manager table to sheet Table
    table.bind([tableColumn1, tableColumn2, tableColumn3, tableColumn4], null, "orders");  // <--- bind the data manager table
    // The table will show the columns: Order Date, Item, Units, Cost, IsDelivered, Column6, Column7
    // And it could set the column data formula for the Column6, Column7, if need
    // table.setColumnDataFormula(5, "=[@Cost] * [@Units]"); // Column6
});

When the autoGenerateColumns property is set to true(default), the worksheet table automatically creates columns based on the columns in the data manager table. The binding of specified columns with this property enabled, shows no effect.

// Auto Generate Columns - True
let sheet3 = spread.getSheet(2);
sheet3.name("AutoGenerate Columns-TRUE");
ordersTable.fetch().then(() => {
    // The table could be exist
    const table = sheet3.tables.add('tableSales2', 0, 0, 5, 5);
    // Define the table columns
    const tableColumn1 = new spreadNS.Tables.TableColumn(0, "orderDate", "Order Date", "yyyy-mm-dd");
    const tableColumn2 = new spreadNS.Tables.TableColumn(1, "item", "Item");
    const tableColumn3 = new spreadNS.Tables.TableColumn(2, "units", "Units", '#,##0');
    const tableColumn4 = new spreadNS.Tables.TableColumn(3, "cost", "Cost");
    const tableColumn5 = new spreadNS.Tables.TableColumn(4, "isDelivered", "IsDelivered", null, new GC.Spread.Sheets.CellTypes.CheckBox());
    // The autoGenerateColumns turns on by default
    // table.autoGenerateColumns(true);
    // Bind the data manager table to sheet Table
    // The columns pre-defined could not work for the autoGenerateColumns be true
    // The code below is similar as table.bind([], null, ordersTable);
    table.bind([tableColumn1, tableColumn2, tableColumn3, tableColumn4, tableColumn5], null, "orders");  // <--- bind the data manager table
});

The following code updates the binding columns using the bindColumns method.

 let sheet5 = spread.getSheet(4);
 sheet5.name("Update Binding");
 ordersTable.fetch().then(() => {
     sheet5.suspendPaint();
     var ordersSheetTable = sheet5.tables.add('tableSales4', 0, 0, 1, 3);
     const tableColumn1 = new GC.Spread.Sheets.Tables.TableColumn(1, "item", "item");
     const tableColumn2 = new GC.Spread.Sheets.Tables.TableColumn(2, "cost", "Cost");
     const tableColumn3 = new GC.Spread.Sheets.Tables.TableColumn(3, "units", "Units", '#,##0');
     let bindingColumns = [tableColumn1, tableColumn2, tableColumn3];
     // bind the columns
     ordersSheetTable.autoGenerateColumns(false)
     ordersSheetTable.bind(bindingColumns, null, 'orders');
     sheet5.resumePaint();
     // update the data field or other properties of the column at other times
     sheet5.suspendPaint();
     bindingColumns[1].dataField(null); // << --- unbind the field
     bindingColumns[1].name('Total Cost'); // << --- specify the column name
     // re-bind the columns
     ordersSheetTable.bindColumns(bindingColumns);
     bindingColumns[0].dataField('cost'); // << --- update the data field
     bindingColumns[0].name('Cost'); // << --- update the table name
     bindingColumns[0].formatter('$#,##0');  // << --- update the column value formatter
     // re-bind the columns
     ordersSheetTable.bindColumns(bindingColumns);
     ordersSheetTable.setColumnDataFormula(1, '=[@Units]*[@Cost]'); // << --- set Total Cost with data formula
     sheet5.resumePaint();
 });

Saving Changes to the Data Manager Table

SpreadJS allows you to save any changes made to the worksheet table when it is bound to a data manager table. When the data manager table is in batch mode, use the tableSubmitChanges command in the GC.Spread.Sheets.Commandsnamespace to save the changes to the bound table. It supports undo/redo operations and can be executed with specific options such as sheet name and table name.

Importing/Exporting a bound sheet table

When exporting a bound worksheet to SJS or SSJSON file format, changes are automatically saved. But when you save it to Xlsx format, you need to set includeBindingSource option in ExportXlsxOptions to true.

When importing an Excel file, you can specify the convertSheetTableToDataTable option in ImportXlsxOptions as true to convert all sheet tables to data manager tables. Cell-based formulas are removed during import. However, column-based formulas are converted during import and export.

Using Designer

Enable the Convert Sheet Tables to Data Tables option to convert sheet tables to data tables while importing an Xlsx format file.

importing in designer

When exporting to Excel, the Include Binding Source option needs to be enabled.

Export

Expand Bound Table Rows

SpreadJS provides support to expand rows in a bound table. For this, you can directly expand the sheet or insert/delete the row in the bound table by using the expandBoundRows method of the Table type.

The following code shows how to use the expandBoundRows method for expanding the rows in the bound table.

// Initializing Spread
var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 3 });
// Get the activesheet
var sheet = spread.getSheet(0);
// Create data
var data = {
    name: 'Jones', region: 'East',
    sales: [
        { orderDate: '1/6/2013', item: 'Pencil', units: 95, cost: 1.99 },
        { orderDate: '4/1/2013', item: 'Binder', units: 60, cost: 4.99 },
        { orderDate: '6/8/2013', item: 'Pen Set', units: 16, cost: 15.99 },
        { orderDate: '8/1/2013', item: 'Pencil', units: 20, cost: 24.99 },
        { orderDate: '10/8/2013', item: 'Binder', units: 31, cost: 16.99 }
    ]
};
// Add table named as "table1"
var table1 = sheet.tables.add('tableRecords', 0, 0, 4, 4);
table1.autoGenerateColumns(true);
// Add another table named as "table2"
var table2 = sheet.tables.add('tableBelow', 4, 0, 4, 7);
// Bind table1
table1.expandBoundRows(true);
table1.bindingPath('sales');
// Set datasource
var dataSource = new GC.Spread.Sheets.Bindings.CellBindingSource(data);
sheet.setDataSource(dataSource);