Data Binding in JavaScript Spreadsheet

23 Jun 202024 minutes to read

Spreadsheet can be populated with external datasource using dataSource property. The dataSource property can be assigned either with the instance of ej.DataManager or JSON data array collection. Spreadsheet supports three different kinds of Data binding.

  • Local Data
  • Remote Data
  • HTML Table Data

Local Data

To bind local data to the Spreadsheet, you can assign a JSON array to the worksheet dataSource property. The following code illustrates how to bind local data to the Spreadsheet,

  • HTML
  • <div id="Spreadsheet"></div>
    
    <script>
    $(function () {
        $("#Spreadsheet").ejSpreadsheet({                                
            sheets: [{
                // the datasource "window.filterData" is referred from 'http://js.syncfusion.com/demos/web/scripts/xljsondata.js'
                dataSource: window.filterData
            }]
        });
    });
    </script>

    The following output is displayed as a result of the above code snippets.
    Bind Local data using Spreadsheet in JavaScript

    Remote Data

    To bind remote data to the Spreadsheet, you can assign a service data as an instance of ej.DataManager to the worksheet dataSource property. The following code illustrates how to bind remote data to the Spreadsheet,

  • HTML
  • <div id="Spreadsheet"></div>
    
    <script>
    $(function () {
        $("#Spreadsheet").ejSpreadsheet({                
            sheets: [{
                dataSource: ej.DataManager("http://mvc.syncfusion.com/Services/Northwnd.svc/Orders/"),
                query: ej.Query().take(50).select(["OrderID", "CustomerID", "EmployeeID", "ShipName", "ShipAddress"]),                    
                primaryKey: "OrderID"
            }]
        });
    });
    </script>

    The following output is displayed as a result of the above code snippets.
    Bind Remote data using Spreadsheet in JavaScript

    Offline Mode

    To avoid sending post back request to server on every action, Spreadsheet allows user to create, update and delete data on client side. To enable this, set offline property of ej.DataManager as true to fetch all data from server on initial rendering of Spreadsheet and perform all operation on client side.

    The following code illustrates offline data binding for Spreadsheet,

  • HTML
  • <div id="Spreadsheet"></div>
    
    <script>
    $(function () {
        $("#Spreadsheet").ejSpreadsheet({
            sheets: [{
                dataSource: ej.DataManager({
                    url: "http://mvc.syncfusion.com/Services/Northwnd.svc/Orders/",
                    offline: true
                }),
                query: ej.Query().select(["OrderID", "CustomerID", "EmployeeID", "ShipName",  "ShipAddress"]),
                primaryKey: "OrderID"
            }],
        });
    });
    </script>

    The following output is displayed as a result of the above code snippets.
    Offline Mode Data Binding using Spreadsheet in JavaScript

    NOTE

    For further reference about offline property in ej.DataManager refer following link

    HTML Table Data

    An HTML Table element can also be used as the data source of Spreadsheet. To use HTML Table as data source, the table element should be passed to worksheet dataSource property of Spreadsheet as an instance of the ej.DataManager. The following code illustrates how to bind HTML Table data to the Spreadsheet,

  • HTML
  • <div id="Spreadsheet"></div>
    
    <table id="Table1">
        <thead>
            <tr>
                <th>Laptop</th>
                <th>Model</th>
                <th>Price</th>
                <th>OS</th>
                <th>RAM</th>
                <th>ScreenSize</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>Dell Vostro</td>
                <td>2520</td>
                <td>39990</td>
                <td>Windows 8</td>
                <td>4GB</td>
                <td>15.6</td>
            </tr>
            <tr>
                <td>HP Pavilion Sleekbook</td>
                <td>14-B104AU</td>
                <td>22800</td>
                <td>Windows 8</td>
                <td>2GB</td>
                <td>14</td>
            </tr>
            <tr>
                <td>Sony Vaio</td>
                <td>E14A15</td>
                <td>42500</td>
                <td>Windows 7 Home Premium</td>
                <td>4GB DDR3 RAM</td>
                <td>14</td>
            </tr>
            <tr>
                <td>Lenovo</td>
                <td>Yoga 13</td>
                <td>57000</td>
                <td>Windows 8 RT</td>
                <td>2GB DDR3 RAM</td>
                <td>11.6</td>
            </tr>
            <tr>
                <td>Toshiba</td>
                <td>L850-Y3110</td>
                <td>57700</td>
                <td>Windows 8 SL</td>
                <td>8GB DDR3 RAM</td>
                <td>15.6</td>
            </tr>
        </tbody>
    </table>
    
    <script>
    $(function () {
        $("#Spreadsheet").ejSpreadsheet({                
            sheets: [{
                dataSource: ej.DataManager($("#Table1"))
            }]
        });
    });
    </script>

    The following output is displayed as a result of the above code snippets.
    HTML Table Data binding using Spreadsheet in JavaScript

    Ways to bind data in Spreadsheet

    You can bind data to Spreadsheet in following ways,

    • Cell binding
    • Range binding
    • Sheet binding

    Cell Binding

    Spreadsheet can bind data for individual cells in a sheet. The data may contain value, style, format, comment and hyperlink. The individual cell properties are listed below,

    Properties Description

    index

    To specify particular cell

    value

    To specify value. It may be string, integer, formula etc.

    style

    To specify style in the cell.
    Properties Description

    backgroundColor

    Specifies the background color of a cell in the Spreadsheet.

    color

    Specifies the font color of a cell in the Spreadsheet.

    fontWeight

    Specifies the font weight of a cell in the Spreadsheet.

    format

    To specify number format in the cell.
    Properties Description

    type

    Specifies the type of the format in Spreadsheet.

    decimalPlaces

    Specifies the number of decimal places for the given input.

    formatStr

    Specifies the string format for the given input.

    thousandSeparator

    Specifies the thousand separator for the given input.

    comment

    To specify comment in the cell
    Properties Description

    isVisible

    Indicates whether to show or hide comments in Spreadsheet.

    value

    Specifies the value for the comment in Spreadsheet.

    hyperlink

    To specify hyperlink in the cell.
    Properties Description

    webAddr

    Specifies the web address for the hyperlink of a cell.

    cellAddr

    Specifies the cell address for the hyperlink of a cell.

    sheetIndex

    Specifies the sheet index to which the cell is referred.

    isLocked

    To lock or unlock a specified cell

    The individual row properties are listed below,

    Properties Description

    index

    To specify particular row

    height

    To specify height in the row

    You can specify particular row with index property and its height with height property in the rows and cells property collection. The following code illustrates cell binding in Spreadsheet,

  • HTML
  • <div id="Spreadsheet"></div>
    
    <script>
    $(function () {
        $("#Spreadsheet").ejSpreadsheet({
            sheets: [{
                rows: [{
                    height: 30,
                    cells: [
                    { value: "Item Name", style: { "font-weight": "bold", "color": "#FFFFFF", "background-color": "#428bca" } },
                    { value: "Quantity", style: { "font-weight": "bold", "color": "#FFFFFF", "background-color": "#428bca" } },
                    { value: "Price", style: { "font-weight": "bold", "color": "#FFFFFF", "background-color": "#428bca" } },
                    { value: "Amount", style: { "font-weight": "bold", "color": "#FFFFFF", "background-color": "#428bca" } },
                    { value: "Stock Detail", style: { "font-weight": "bold", "color": "#FFFFFF", "background-color": "#428bca" } },
                    { value: "Website", style: { "font-weight": "bold", "color": "#FFFFFF", "background-color": "#428bca" } }
                    ]
                },
                {
                    cells: [
                    { value: "Casual Shoes", comment: { value: "Casual FootWears with wide variety of colors." } },
                    { value: "20", index: 2, format: { type: "currency" } },
                    { value: "=B2*C2" },
                    { value: "OUT OF STOCK" },
                    { value: "Amazon", hyperlink: { webAddr: "www.amazon.com" } }
                    ]
                },
                {
                    cells: [
                    { value: "Sports Shoes", style: { "background-color": "#E5F3FF" } },
                    { value: "20", style: { "background-color": "#E5F3FF" } },
                    { value: "30", format: { type: "currency" }, style: { "background-color": "#E5F3FF" } },
                    { value: "=B3*C3", style: { "background-color": "#E5F3FF" } },
                    { value: "IN STOCK", style: { "background-color": "#E5F3FF" } },
                    { value: "AliExpress", hyperlink: { webAddr: "www.aliexpress.com" }, style: { "background-color": "#E5F3FF" } }
                    ]
                },
                {
                    cells: [
                    { value: "Formal Shoes", comment: { value: "Formal FootWears with wide range of sizes." } },
                    { value: "20" },
                    { value: "15", format: { type: "currency" } },
                    { value: "=B4*C4" },
                    { value: "IN STOCK" },
                    { value: "Amazon", hyperlink: { webAddr: "www.amazon.com" } }
                    ]
                },
                {
                    height: 30,
                    index: 5,
                    cells: [
                    { style: { "background-color": "#428bca" } },
                    { style: { "background-color": "#428bca" } },
                    { value: "Total Amount", index: 2, style: { "font-weight": "bold", "color": "#FFFFFF", "background-color": "#428bca" } },
                    { value: "=Sum(D2:D4)", style: { "font-weight": "bold", "color": "#FFFFFF", "background-color": "#428bca" } },
                    { style: { "background-color": "#428bca" } },
                    { style: { "background-color": "#428bca" } }
                    ]
                }]
            }]
        });
    });
    </script>

    The following output is displayed as a result of the above code snippets.
    Cell Binding using Spreadsheet in JavaScript

    NOTE

    To get the active sheet index and data settings in Spreadsheet, use getActiveSheetIndex and getDataSettings methods.

    Range Binding

    Spreadsheet can bind data for one or more range in a sheet using rangeSettings. The individual range properties are listed below,

    Properties Description

    dataSource

    To specify JSON or

    ej.DataManager

    query

    To specify query for

    ej.DataManager

    startCell

    To specify start cell of a range

    primarykey

    To specify data source primary key

    showHeader

    To show data source header

    headerStyles

    To specify header styles

    The following code illustrates range binding in Spreadsheet

  • HTML
  • <div id="Spreadsheet"></div>
    
    <script>
    $(function () {
        $("#Spreadsheet").ejSpreadsheet({               
            sheets: [{
                rangeSettings: [{
                    // the datasource "window.markList" is referred from 'http://js.syncfusion.com/demos/web/scripts/xljsondata.js'
                    dataSource: window.markList,
                    startCell: "C2",
                    showHeader: true,
                    headerStyles: { "font-weight": "bold" }
                }]
            }]
        });
    });
    </script>

    The following output is displayed as a result of the above code snippets.
    Range Binding using Spreadsheet in JavaScript

    Sheet Binding

    Spreadsheet can bind data for a sheet. The individual sheet properties are listed below,

    Properties Description

    dataSource

    To specify JSON or

    ej.DataManager

    query

    To specify query for

    ej.DataManager

    startCell

    To specify start cell of a range

    primarykey

    To specify data source primary key

    showHeader

    To show data source header

    headerStyles

    To specify header styles

    fieldAsColumnHeader

    To show data source fields in column header

    colCount

    To define column count in the Spreadsheet.

    columnWidth

    To define column width in the Spreadsheet.

    rowCount

    To define row count in the Spreadsheet.

    sheetName

    To specify the name for sheet in the Spreadsheet.

    The following code illustrates sheet binding in Spreadsheet

  • HTML
  • <div id="Spreadsheet"></div>
    
    <script>
    $(function () {
        $("#Spreadsheet").ejSpreadsheet({                
            sheets: [{
                dataSource: ej.DataManager("http://mvc.syncfusion.com/Services/Northwnd.svc/Orders/"),
                query: ej.Query().take(50).select(["OrderID", "CustomerID", "EmployeeID", "ShipName", "ShipAddress"]),
                fieldAsColumnHeader: true,
                primaryKey: "OrderID"
            }]
        });
    });
    </script>

    The following output is displayed as a result of the above code snippets.
    Sheet Binding using Spreadsheet in JavaScript