SpreadJS 13
SpreadJS Documentation / Developer's Guide / Features / Manage Data Visualization and Objects / Tables / Bind Tables
In This Topic
    Bind Tables
    In This Topic

    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 tble 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:

    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.

    Using the bindColumns() method

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

    JavaScript
    Copy Code
    $(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.

    Using the bind() method

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

    JavaScript
    Copy Code
    // 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);