Search results

Data Binding in JavaScript 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
app.ts
index.html
import { Spreadsheet } from '@syncfusion/ej2-spreadsheet';
import { data } from './datasource.ts';

//Initialize the SpreadSheet control
let spreadsheet: Spreadsheet = new Spreadsheet({
  sheets: [{
                ranges: [{ dataSource: data }],
                columns: [{ width: 80 }, { width: 80 },{ width: 80},
                          { width: 160 }, { width: 100 }, {width: 150}]
            }],
  }
});
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="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
        <script src="system.config.js"></script>

</head>

<body>
       <!--Element which is going to render-->
       <div id='loader'>Loading....</div>
       <div id='container'>
       <div id="spreadsheet"></div>
       </div>
</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
app.ts
index.html
import { DataManager, ODataAdaptor } from '@syncfusion/ej2-data';
import { Spreadsheet } from '@syncfusion/ej2-spreadsheet';

class CustomAdaptor extends ODataAdaptor {
    public processResponse(): Object {
        let result: Object[] = [];
        let original: { result: Object[], count: number } = super.processResponse.apply(this, arguments);
        original.result.forEach((item: { SNo: number }, idx: number) => {
            result[idx] = {};
            Object.keys(item).forEach((key: string) => {
                if (['OrderID', 'CustomerID', 'ShipName', 'ShipCity', 'ShipCountry'].indexOf(key) > -1) {
                    result[idx][key] = item[key];
                }
            });
        });
        return { result: result, count: original.count };
    }
}
    //Initialize DataManager
    let data: DataManager = new DataManager({
        //Remote service url
        url: 'https://ej2services.syncfusion.com/production/web-services/api/Orders',
        adaptor: new CustomAdaptor,
        crossDomain: true
    });

    //Initialize Spreadsheet control
    let spreadsheet: Spreadsheet = new Spreadsheet({
        sheets: [
            {
                rows: [{
                     cells: [{ value: 'Order ID' }, { value: 'Customer Name' }, { value: 'Ship Name' },
                    { value: 'Ship City' }, { value: 'Ship Country' }]
                }],
                ranges: [{ dataSource: data, showFieldAsHeader: false, startCell: 'A2' }],
                columns: [
                    { width: 80 }, { width: 100 }, { width: 82 },
                    { width: 160 }, { width: 110 }, { width: 130 }
                ]
            }]
    });

    //Render the initialized Spreadsheet control
    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="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
        <script src="system.config.js"></script>

</head>

<body>
       <!--Element which is going to render-->
       <div id='loader'>Loading....</div>
       <div id='container'>
       <div id="spreadsheet"></div>
       </div>
</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
app.ts
index.html
import { Spreadsheet } from '@syncfusion/ej2-spreadsheet';

//Initialize the SpreadSheet control
let spreadsheet: Spreadsheet = new Spreadsheet({
    sheets: [
        {
            name: 'Monthly Budget',
            selectedRange: 'D13',
            rows: [
                {
                    cells: [
                        { value: 'Category', style: { fontWeight: 'bold', textAlign: 'center' } },
                        { value: 'Planned cost', style: { fontWeight: 'bold', textAlign: 'center' } },
                        { value: 'Actual cost', style: { fontWeight: 'bold', textAlign: 'center' } },
                        { value: 'Difference', style: { fontWeight: 'bold', textAlign: 'center' } }
                    ]
                },
                {
                    cells: [
                        { value: 'Food' },
                        { value: '$7000' },
                        { value: '$8120' },
                        { formula: '=B2-C2', format: '$#,##0.00' }
                    ]
                },
                {
                    cells: [
                        { value: 'Loan' },
                        { value: '$1500' },
                        { value: '$1500' },
                        { formula: '=B3-C3', format: '$#,##0.00' }
                    ]
                },
                {
                    cells: [
                        { value: 'Medical' },
                        { value: '$300' },
                        { value: '$0' },
                        { formula: '=B4-C4', format: '$#,##0.00' }
                    ]
                },
                {
                    index: 5,
                    cells: [
                        { index: 2, value: 'Total Difference:', style: { fontWeight: 'bold', textAlign: 'right' } },
                        { formula: '=D2+D4', format: '$#,##0.00', style: { fontWeight: 'bold' } }
                    ]
                }
            ],
            columns: [
                { width: 110 }, { width: 115 }, { width: 110 }, { width: 100 }
            ]
        }
    ]
});

//Render the 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="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
        <script src="system.config.js"></script>

</head>

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

</html>

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

See Also