Data binding in EJ2 JavaScript Spreadsheet control

14 Oct 202424 minutes to read

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.

// 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="https://cdn.syncfusion.com/ej2/27.1.48/ej2-base/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-inputs/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-buttons/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-splitbuttons/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-lists/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-navigations/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-popups/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-dropdowns/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-grids/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-spreadsheet/styles/material.css" rel="stylesheet">
        <link href="styles.css" rel="stylesheet">
        
        <script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>

<script src="https://cdn.syncfusion.com/ej2/27.1.48/dist/ej2.min.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/syncfusion-helper.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.

Customizing column data mapping

By default, when a data source is bound to a sheet, columns are auto-assigned from the data source fields sequentially. This means that the first field in the data source is assigned to Column A, the second to Column B, and so on, sequentially. However, now you can customize the column assignments by specifying the appropriate field names in the desired order using the fieldsOrder property.

You can customize the mapping of column data only in the local data binding support.

The following code example demonstrates how to customize the mapping of column data:

// Initialize the Spreadsheet component.
var columns = [{ width: 100 }, { width: 100 },{ width: 100 , isReadOnly: true},
    { width: 100 }, { width: 100 },{ width: 100 }];

var fieldsOrder = ['Projected Cost', 'Actual Cost', 'Expense Type', 'Difference'];

var spreadsheet = new ej.spreadsheet.Spreadsheet({
    sheets: [{ name: 'Budget', ranges: [{ dataSource: budgetData, fieldsOrder: fieldsOrder }], columns: columns}]
});

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="https://cdn.syncfusion.com/ej2/27.1.48/ej2-base/styles/material.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-inputs/styles/material.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-buttons/styles/material.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-splitbuttons/styles/material.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-lists/styles/material.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-navigations/styles/material.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-popups/styles/material.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-dropdowns/styles/material.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-grids/styles/material.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-spreadsheet/styles/material.css" rel="stylesheet">
    <link href="styles.css" rel="stylesheet">
    
    <script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>
    <script src="es5-datasource.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/27.1.48/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/syncfusion-helper.js" type ="text/javascript"></script>
</head>

<body>
    <!--Element which is going to render-->
    <div id="dialog"></div>
    
    <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>

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.

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://services.syncfusion.com/js/production/api/Orders',
    crossDomain: true
});

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://services.syncfusion.com/js/production/api/spreadsheet/open',
    saveUrl: 'https://services.syncfusion.com/js/production/api/spreadsheet/save',
});

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="JavaScript 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="https://cdn.syncfusion.com/ej2/27.1.48/ej2-base/styles/material.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-inputs/styles/material.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-buttons/styles/material.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-splitbuttons/styles/material.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-lists/styles/material.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-navigations/styles/material.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-popups/styles/material.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-dropdowns/styles/material.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-grids/styles/material.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-spreadsheet/styles/material.css" rel="stylesheet">
  <link href="styles.css" rel="stylesheet">

  <script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>

  <script src="https://cdn.syncfusion.com/ej2/27.1.48/dist/ej2.min.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/syncfusion-helper.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.

Binding with OData services

OData is a standardized protocol for creating and consuming data. You can retrieve data from OData service using the DataManager. Refer to the following code example for remote Data binding using OData service.

//Initialize DataManager.
var data = new ej.data.DataManager({
  url: 'https://services.syncfusion.com/js/production/api/Orders',
  adaptor: new ej.data.ODataAdaptor(),
  crossDomain: true
});

var spreadsheet = new ej.spreadsheet.Spreadsheet({
  sheets: [
    {
      name: 'Order details',
      ranges: [{ dataSource: data }],
      columns: [
        { width: 80 },
        { width: 80 },
        { width: 80 },
        { width: 80 },
        { width: 80 },
        { width: 80 },
        { width: 280 },
        { width: 180 },
        { width: 80 },
        { width: 180 },
        { width: 180 }
      ]
    }
  ],
  openUrl:
    'https://services.syncfusion.com/js/production/api/spreadsheet/open',
  saveUrl:
    'https://services.syncfusion.com/js/production/api/spreadsheet/save',
  created: function () {
    //Applies cell and number formatting to specified range of the active sheet.
    spreadsheet.cellFormat(
      { fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' },
      'A1:K1'
    );
  }
});
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="JavaScript 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="https://cdn.syncfusion.com/ej2/27.1.48/ej2-base/styles/material.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-inputs/styles/material.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-buttons/styles/material.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-splitbuttons/styles/material.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-lists/styles/material.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-navigations/styles/material.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-popups/styles/material.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-dropdowns/styles/material.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-grids/styles/material.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-spreadsheet/styles/material.css" rel="stylesheet">
  <link href="styles.css" rel="stylesheet">

  <script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>

  <script src="https://cdn.syncfusion.com/ej2/27.1.48/dist/ej2.min.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/syncfusion-helper.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>

Web API

You can use WebApiAdaptor to bind spreadsheet with Web API created using OData endpoint.

//Initialize DataManager.
var data = new ej.data.DataManager({
  url: 'https://services.syncfusion.com/js/production/api/Orders',
  adaptor: new ej.data.WebApiAdaptor(),
  crossDomain: true
});

var spreadsheet = new ej.spreadsheet.Spreadsheet({
  sheets: [
    {
      name: 'Order details',
      ranges: [{ dataSource: data }],
      columns: [
        { width: 80 },
        { width: 80 },
        { width: 80 },
        { width: 80 },
        { width: 80 },
        { width: 80 },
        { width: 280 },
        { width: 180 },
        { width: 80 },
        { width: 180 },
        { width: 180 }
      ]
    }
  ],
  openUrl:
    'https://services.syncfusion.com/js/production/api/spreadsheet/open',
  saveUrl:
    'https://services.syncfusion.com/js/production/api/spreadsheet/save',
  created: function () {
    //Applies cell and number formatting to specified range of the active sheet.
    spreadsheet.cellFormat(
      { fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' },
      'A1:K1'
    );
  }
});
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="JavaScript 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="https://cdn.syncfusion.com/ej2/27.1.48/ej2-base/styles/material.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-inputs/styles/material.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-buttons/styles/material.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-splitbuttons/styles/material.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-lists/styles/material.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-navigations/styles/material.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-popups/styles/material.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-dropdowns/styles/material.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-grids/styles/material.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-spreadsheet/styles/material.css" rel="stylesheet">
  <link href="styles.css" rel="stylesheet">

  <script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>

  <script src="https://cdn.syncfusion.com/ej2/27.1.48/dist/ej2.min.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/syncfusion-helper.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>

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.

// 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="https://cdn.syncfusion.com/ej2/27.1.48/ej2-base/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-inputs/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-buttons/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-splitbuttons/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-lists/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-navigations/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-popups/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-dropdowns/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-grids/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-spreadsheet/styles/material.css" rel="stylesheet">
        <link href="styles.css" rel="stylesheet">
        
        <script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>

<script src="https://cdn.syncfusion.com/ej2/27.1.48/dist/ej2.min.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/syncfusion-helper.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.

Dynamic data binding and Datasource change event

You can dynamically change the datasource of the spreadsheet by changing the dataSource property of the range object of the sheet. The dataSourceChanged event handler will be triggered when editing, inserting, and deleting a row in the datasource range. This event will be triggered with a parameter named action which indicates the edit, add and delete actions for the respective ones.

The following table defines the arguments of the dataSourceChanged event.

Property Type Description
action string Indicates the type of action such as edit, add, and delete performed in the datasource range.
data object[] Modified data for edit action; New data for add action; Deleted data for delete action.
rangeIndex number Specifies the range index of the datasource.
sheetIndex number Specifies the sheet index of the datasource.

For add action, the value for all the fields will be null in the data. In the case that you do not want the primary key field to be null which needs to be updated in the backend service, you can use edit action after updating the primary key field to update in the backend service.


For inserting a row at the end of the datasource range, you should insert a row below at the end of the range to trigger the dataSourceChanged event with action add.

var spreadsheet = new ej.spreadsheet.Spreadsheet({
        showRibbon: false,
        showFormulaBar: false,
        sheets: [
            {
                ranges: [{ dataSource: data }],
                columns: [
                    { width: 90 }, { width: 100 }, { width: 96 },
                    { width: 120 }, { width: 130 }, { width: 120 }
                ]
            }],
        dataSourceChanged: (args) => {
            appendElement("Data source changed with" + "<b>&nbsp;" + args.action + "</b> action<hr>");
        }
});

spreadsheet.appendTo('#spreadsheet');

document.getElementById('changeDataBtn').addEventListener('click', ()=> {
    spreadsheet.sheets[0].ranges[0].dataSource = itemData;
});

document.getElementById('clearBtn').addEventListener('click', ()=> {
    document.getElementById('EventLog').innerHTML = "";
});

function appendElement(html) {
     var span = document.createElement("span");
     span.innerHTML = html;
     var log = document.getElementById('EventLog');
     log.insertBefore(span, log.firstChild);
}
<!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="https://cdn.syncfusion.com/ej2/27.1.48/ej2-base/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-inputs/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-buttons/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-splitbuttons/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-lists/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-navigations/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-popups/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-dropdowns/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-grids/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-spreadsheet/styles/material.css" rel="stylesheet">
        <link href="styles.css" rel="stylesheet">
        
        <script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>

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

<body>
       <!--Element which is going to render-->

<div id="container">
        <div>
                <div>
                        <button id="changeDataBtn" class="e-btn">Change Datasource</button>
                        <div id="spreadsheet"></div>
                </div>
                <div>
                        <h4><b>Event Trace</b></h4>
                        <div id="evt">
                                <div style="height:173px;overflow: auto;min-width: 250px;">
                                        <span id="EventLog" style="word-break: normal;"></span>
                                </div>
                                <button id="clearBtn" class="e-btn">Clear</button>
                        </div>
                </div>
        </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>

Dynamic data binding using updateRange method

The updateRange method allows you to dynamically update the dataSource in a spreadsheet without manually iterating through each cell. This method is especially useful for efficiently applying bulk updates to a specific range within the spreadsheet.

To use the updateRange method, provide the new dataSource and specify the starting cell for the update using the startCell property of the RangeModel. Additionally, set the sheetIndex to target the appropriate sheet for the update.

The following code example demonstrates how to dynamically update data using the updateRange method.

let sheet = [{
    ranges: [{ dataSource: defaultData }],
    columns: [
        { width: 180 }, { width: 130 }, { width: 130 }, { width: 180 },
        { width: 130 }, { width: 120 }
    ]
}];

let spreadsheet = new ej.spreadsheet.Spreadsheet({
    sheets: sheet,
    created: function () {
        // Applies cell formatting to specified range of the active sheet
        spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' }, 'A1:F1');
    }
});

spreadsheet.appendTo('#spreadsheet');

document.getElementById("updateDynamicData").onclick = () => {
    let newDataCollection = {
        dataSource: [
          {
            'Payment Mode': 'Debit Card',
            'Delivery Date': '07/11/2015',
            'Amount': '8529.22',
          },
          {
            'Payment Mode': 'Cash On Delivery',
            'Delivery Date': '7/13/2016',
            'Amount': '17866.19',
          },
          {
            'Payment Mode': 'Net Banking',
            'Delivery Date': '09/04/2015',
            'Amount': '13853.09',
          },
          {
            'Payment Mode': 'Credit Card',
            'Delivery Date': '12/15/2017',
            'Amount': '2338.74',
          },
          {
            'Payment Mode': 'Credit Card',
            'Delivery Date': '10/08/2014',
            'Amount': '9578.45',
          },
          {
            'Payment Mode': 'Cash On Delivery',
            'Delivery Date': '7/01/2017',
            'Amount': '19141.62',
          },
          {
            'Payment Mode': 'Credit Card',
            'Delivery Date': '12/20/2015',
            'Amount': '6543.30',
          },
          {
            'Payment Mode': 'Credit Card',
            'Delivery Date': '11/24/2014',
            'Amount': '13035.06',
          },
          {
            'Payment Mode': 'Debit Card',
            'Delivery Date': '05/12/2014',
            'Amount': '18488.80',
          },
          {
            'Payment Mode': 'Net Banking',
            'Delivery Date': '12/30/2014',
            'Amount': '12317.04',
          },
          {
            'Payment Mode': 'Credit Card',
            'Delivery Date': '12/18/2013',
            'Amount': '6230.13',
          },
          {
            'Payment Mode': 'Cash On Delivery',
            'Delivery Date': '02/02/2015',
            'Amount': '9709.49',
          },
          {
            'Payment Mode': 'Debit Card',
            'Delivery Date': '11/19/2014',
            'Amount': '9766.10',
          },
          {
            'Payment Mode': 'Net Banking',
            'Delivery Date': '02/08/2014',
            'Amount': '7685.49',
          },
          {
            'Payment Mode': 'Debit Card',
            'Delivery Date': '08/05/2016',
            'Amount': '18012.45',
          },
          {
            'Payment Mode': 'Credit Card',
            'Delivery Date': '05/30/2016',
            'Amount': '2785.49',
          },
          {
            'Payment Mode': 'Debit Card',
            'Delivery Date': '12/10/2016',
            'Amount': '9967.74',
          },
        ],
        startCell: 'D1',
      };
      // update the multiple cell values
      spreadsheet.updateRange(newDataCollection, 0);
};
<!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="https://cdn.syncfusion.com/ej2/27.1.48/ej2-base/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-inputs/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-buttons/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-splitbuttons/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-lists/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-navigations/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-popups/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-dropdowns/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-grids/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.1.48/ej2-spreadsheet/styles/material.css" rel="stylesheet">
        <link href="styles.css" rel="stylesheet">

        <script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>
        <script src="https://cdn.syncfusion.com/ej2/27.1.48/dist/ej2.min.js" type="text/javascript"></script>
        <script src="es5-datasource.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/syncfusion-helper.js" type ="text/javascript"></script>
</head>

<body>
        <!--Element which is going to render-->

        <div id="container">
                <button class="e-btn custom-btn" id="updateDynamicData">Update Dynamic Data</button>
                <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>

See Also