Search results

Data Binding in JavaScript (ES5) Spreadsheet control

The Spreadsheet uses DataManager, which supports both RESTful JSON data services and local JavaScript object array binding to a range. The dataSource property can be assigned either with the instance of DataManager or JavaScript object array collection.

To bind data to a cell, use cell data binding support.

Local data

To bind local data to the Spreadsheet, you can assign a JavaScript object array to the dataSource property.

Refer to the following code example for local data binding.

Source
Preview
index.js
index.html
// Initialize the Spreadsheet component.

var data= [{
    OrderID: 10248,
    CustomerID: 'VINET',
    EmployeeID: 5,
    ShipName: 'Vins et alcools Chevalier',
    ShipCity: 'Reims',
    ShipAddress: '59 rue de lAbbaye'
  },
  {
    OrderID: 10249,
    CustomerID: 'TOMSP',
    EmployeeID: 6,
    ShipName: 'Toms Spezialitäten',
    ShipCity: 'Münster',
    ShipAddress: 'Luisenstr. 48'
  },
  {
    OrderID: 10250,
    CustomerID: 'HANAR',
    EmployeeID: 4,
    ShipName: 'Hanari Carnes',
    ShipCity: 'Rio de Janeiro',
    ShipAddress: 'Rua do Paço, 67'
  },
  {
    OrderID: 10251,
    CustomerID: 'VICTE',
    EmployeeID: 3,
    ShipName: 'Victuailles en stock',
    ShipCity: 'Lyon',
    ShipAddress: '2, rue du Commerce'
  },
  {
    OrderID: 10252,
    CustomerID: 'SUPRD',
    EmployeeID: 4,
    ShipName: 'Suprêmes délices',
    ShipCity: 'Charleroi',
    ShipAddress: 'Boulevard Tirou, 255'
  }];

var sheet = [{
                ranges: [{ dataSource: data }],
                columns: [{ width: 80 }, { width: 80 },{ width: 80},
                          { width: 160 }, { width: 100 }, {width: 150}]
            }]
var spreadsheet = new ej.spreadsheet.Spreadsheet({
    sheets: sheet
    });

// Render initialized Spreadsheet.
spreadsheet.appendTo('#spreadsheet');
<!DOCTYPE html><html lang="en"><head>
        <title>EJ2 SpreadSheet</title>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <meta name="description" content="Typescript UI Controls">
        <meta name="author" content="Syncfusion">
        <link rel="shortcut icon" href="resources/favicon.ico">
        <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-base/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-inputs/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-buttons/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-splitbuttons/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-lists/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-navigations/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-popups/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-dropdowns/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-grids/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-spreadsheet/styles/material.css" rel="stylesheet">
        <link href="styles.css" rel="stylesheet">
        
        <script src="system.config.js"></script>

<script src="https://cdn.syncfusion.com/ej2/dist/ej2.min.js" type="text/javascript"></script>
</head>

<body>
       <!--Element which is going to render-->
       
       <div id="container">
       <div id="spreadsheet"></div>
       </div>


<script>
var ele = document.getElementById('container');
if(ele) {
    ele.style.visibility = "visible";
 }   
        </script>
<script src="index.js" type="text/javascript"></script>
</body></html>

The local data source can also be provided as an instance of the DataManager. By default, DataManager uses JsonAdaptor for local data-binding.

Remote data

To bind remote data to the Spreadsheet control, assign service data as an instance of DataManager to the dataSource property. To interact with remote data source, provide the service endpoint url.

Refer to the following code example for remote data binding.

Source
Preview
index.js
index.html
var query = new ej.data.Query().select([
        'OrderID', 'CustomerID', 'ShipName', 'ShipCity', 'ShipCountry', 'Freight'
    ]).take(200);

    //Initialize DataManager.
    var data = new ej.data.DataManager({
        url: 'https://js.syncfusion.com/demos/ejServices//wcf/Northwind.svc/Orders',
        crossDomain: true
    });

    //Initialize Spreadsheet component.
    var spreadsheet = new ej.spreadsheet.Spreadsheet({
        sheets: [
            {
                name: 'Shipment Details',
                rows: [{
                    cells: [{ value: 'Order ID' }, { value: 'Customer Name' }, { value: 'Freight' }, { value: 'Ship Name' },
                    { value: 'Ship City' }, { value: 'Ship Country' }]
                }],
                ranges: [{ dataSource: data, query: query, showFieldAsHeader: false, startCell: 'A2' }],
                columns: [{ width: 100 }, { width: 130 }, { width: 100 }, { width: 220 }, { width: 150 }, { width: 180 }]
            }],
        openUrl: 'https://ej2services.syncfusion.com/production/web-services/api/spreadsheet/open',
        saveUrl: 'https://ej2services.syncfusion.com/production/web-services/api/spreadsheet/save',
    });

    //Render initialized Spreadsheet component.
    spreadsheet.appendTo('#spreadsheet');
<!DOCTYPE html><html lang="en"><head>
        <title>EJ2 SpreadSheet</title>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <meta name="description" content="Typescript UI Controls">
        <meta name="author" content="Syncfusion">
        <link rel="shortcut icon" href="resources/favicon.ico">
        <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-base/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-inputs/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-buttons/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-splitbuttons/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-lists/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-navigations/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-popups/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-dropdowns/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-grids/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-spreadsheet/styles/material.css" rel="stylesheet">
        <link href="styles.css" rel="stylesheet">
        
        <script src="system.config.js"></script>

<script src="https://cdn.syncfusion.com/ej2/dist/ej2.min.js" type="text/javascript"></script>
</head>

<body>
       <!--Element which is going to render-->
       
       <div id="container">
       <div id="spreadsheet"></div>
       </div>


<script>
var ele = document.getElementById('container');
if(ele) {
    ele.style.visibility = "visible";
 }   
        </script>
<script src="index.js" type="text/javascript"></script>
</body></html>

By default, DataManager uses ODataAdaptor for remote data-binding.

Cell data binding

The Spreadsheet control can bind the data to individual cell in a sheet . To achive this you can use the value property.

Refer to the following code example for cell data binding.

Source
Preview
index.js
index.html
// Initialize the Spreadsheet component.

var sheet = [{
        rows: [{
                index: 0,
                cells: [
                    { index: 0, value: 'Order ID',  style: { fontWeight: 'bold' }},
                    { value: 'Customer ID', style: { fontWeight: 'bold' }},
                    { value: 'Employee ID', style: { fontWeight: 'bold' }},
                    { value: 'Ship Name', style: { fontWeight: 'bold' }},
                    { value: 'Ship City', style: { fontWeight: 'bold' }},
                    { value: 'Ship Address', style: { fontWeight: 'bold' }}
                ]
            },
            {
                cells: [
                    { value: '10248' },
                    { value: 'VINET' },
                    { value: '5' },
                    { value: 'Vins et alcools Chevalier' },
                    { value: 'Reims' },
                    { value: '59 rue de lAbbaye' }
                ]
            },
            {
                cells: [
                    { value: '10249' },
                    { value: 'TOMSP' },
                    { value: '6' },
                    { value: 'Toms Spezialitäten' },
                    { value: 'Münster' },
                    { value: 'Luisenstr. 48' }
                ]
            },
            {
                cells: [
                    { value: '10250' },
                    { value: 'HANAR' },
                    { value: '4' },
                    { value: 'Hanari Carnes' },
                    { value: 'Rio de Janeiro' },
                    { value: 'Rua do Paço, 67' }
                ]
            },
            {
                cells: [
                    { value: '10251' },
                    { value: 'VICTE' },
                    { value: '3' },
                    { value: 'Victuailles en stock' },
                    { value: 'Lyon' },
                    { value: '2, rue du Commerce' }
                ]
            }],
    columns: [
            { width: 80 }, { width: 80 }, { width: 82 },
            { width: 160 }, { width: 110 }, { width: 130 }
        ]
}];
var spreadsheet = new ej.spreadsheet.Spreadsheet({
    sheets: sheet,
    });

// Render initialized Spreadsheet.
spreadsheet.appendTo('#spreadsheet');
<!DOCTYPE html><html lang="en"><head>
        <title>EJ2 SpreadSheet</title>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <meta name="description" content="Typescript UI Controls">
        <meta name="author" content="Syncfusion">
        <link rel="shortcut icon" href="resources/favicon.ico">
        <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-base/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-inputs/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-buttons/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-splitbuttons/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-lists/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-navigations/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-popups/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-dropdowns/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-grids/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-spreadsheet/styles/material.css" rel="stylesheet">
        <link href="styles.css" rel="stylesheet">
        
        <script src="system.config.js"></script>

<script src="https://cdn.syncfusion.com/ej2/dist/ej2.min.js" type="text/javascript"></script>
</head>

<body>
       <!--Element which is going to render-->
       
       <div id="container">
       <div id="spreadsheet"></div>
       </div>


<script>
var ele = document.getElementById('container');
if(ele) {
    ele.style.visibility = "visible";
 }   
        </script>
<script src="index.js" type="text/javascript"></script>
</body></html>

The cell data binding also supports formula, style, number format, and more.

See Also