Data binding in Vue Spreadsheet component
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.
<template>
<ejs-spreadsheet ref="spreadsheet">
<e-sheets>
<e-sheet>
<e-ranges>
<e-range :dataSource="dataSource"></e-range>
</e-ranges>
</e-sheet>
</e-sheets></ejs-spreadsheet>
</template>
<script setup>
import { SpreadsheetComponent as EjsSpreadsheet, RangesDirective as ERanges, RangeDirective as ERange, SheetsDirective as ESheets, SheetDirective as ESheet } from "@syncfusion/ej2-vue-spreadsheet";
import { defaultData } from './data.js';
const dataSource = defaultData;
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
<template>
<ejs-spreadsheet ref="spreadsheet">
<e-sheets>
<e-sheet>
<e-ranges>
<e-range :dataSource="dataSource"></e-range>
</e-ranges>
</e-sheet>
</e-sheets></ejs-spreadsheet>
</template>
<script>
import { SpreadsheetComponent, RangesDirective, RangeDirective, SheetsDirective, SheetDirective } from "@syncfusion/ej2-vue-spreadsheet";
import { defaultData } from './data.js';
export default {
name: "App",
components: {
"ejs-spreadsheet": SpreadsheetComponent,
"e-sheets": SheetsDirective,
"e-sheet": SheetDirective,
"e-ranges": RangesDirective,
"e-range": RangeDirective
},
data: () => {
return {
dataSource: defaultData
}
}
}
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
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:
<template>
<div>
<ejs-spreadsheet ref="spreadsheet">
<e-sheets>
<e-sheet name="Budget">
<e-ranges>
<e-range :dataSource="dataSource1" :fieldsOrder="fieldsOrder"></e-range>
</e-ranges>
<e-columns>
<e-column :width=100></e-column>
<e-column :width=100></e-column>
<e-column :width=100></e-column>
<e-column :width=100></e-column>
<e-column :width=100></e-column>
<e-column :width=100></e-column>
</e-columns>
</e-sheet>
</e-sheets>
</ejs-spreadsheet>
</div>
</template>
<script setup>
import { ref } from "vue";
import { SpreadsheetComponent as EjsSpreadsheet, ColumnsDirective as EColumns, ColumnDirective as EColumn, RangesDirective as ERanges, RangeDirective as ERange, SheetsDirective as ESheets, SheetDirective as ESheet } from "@syncfusion/ej2-vue-spreadsheet";
import { budgetData } from './data.js';
const spreadsheet = ref(null);
const dataSource1 = budgetData;
const fieldsOrder = ['Projected Cost', 'Actual Cost', 'Expense Type', 'Difference']
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
<template>
<div>
<ejs-spreadsheet ref="spreadsheet">
<e-sheets>
<e-sheet name="Budget">
<e-ranges>
<e-range :dataSource="dataSource1" :fieldsOrder="fieldsOrder"></e-range>
</e-ranges>
<e-columns>
<e-column :width=100></e-column>
<e-column :width=100></e-column>
<e-column :width=100></e-column>
<e-column :width=100></e-column>
<e-column :width=100></e-column>
<e-column :width=100></e-column>
</e-columns>
</e-sheet>
</e-sheets>
</ejs-spreadsheet>
</div>
</template>
<script>
import { SpreadsheetComponent, ColumnsDirective, ColumnDirective, RangesDirective, RangeDirective, SheetsDirective, SheetDirective } from "@syncfusion/ej2-vue-spreadsheet";
import { budgetData } from './data.js';
import { ButtonComponent } from "@syncfusion/ej2-vue-buttons";
export default {
name: "App",
components: {
"ejs-spreadsheet": SpreadsheetComponent,
"ejs-button": ButtonComponent,
"e-sheets": SheetsDirective,
"e-sheet": SheetDirective,
"e-ranges": RangesDirective,
"e-range": RangeDirective,
"e-columns": ColumnsDirective,
"e-column": ColumnDirective
},
data: () => {
return {
dataSource1: budgetData,
fieldsOrder: ['Projected Cost', 'Actual Cost', 'Expense Type', 'Difference']
}
},
}
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
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.
<template>
<ejs-spreadsheet>
<e-sheets>
<e-sheet name="Shipment Details" :columns="columns">
<e-ranges>
<e-range :dataSource="dataSource" :query="query" :showFieldAsHeader="false"
startCell="A2"></e-range>
</e-ranges>
<e-rows>
<e-row>
<e-cells>
<e-cell value="Order ID"></e-cell>
<e-cell value="Customer Name"></e-cell>
<e-cell value="Freight"></e-cell>
<e-cell value="Ship Name"></e-cell>
<e-cell value="Ship City"></e-cell>
<e-cell value="Ship Country"></e-cell>
</e-cells>
</e-row>
</e-rows>
</e-sheet>
</e-sheets>
</ejs-spreadsheet>
</template>
<script setup>
import { SpreadsheetComponent as EjsSpreadsheet, RowsDirective as ERows, RowDirective as ERow, CellsDirective as ECells, CellDirective as ECell, RangesDirective as ERanges, RangeDirective as ERange, SheetsDirective as ESheets, SheetDirective as ESheet } from "@syncfusion/ej2-vue-spreadsheet";
import { DataManager, Query } from "@syncfusion/ej2-data";
import { defaultData } from './data.js';
const dataSource = new DataManager({
// Remote service url.
url: "https://services.syncfusion.com/vue/production/api/Orders",
crossDomain: true
})
const query = new Query().select(['OrderID', 'CustomerID', 'Freight', 'ShipName', 'ShipCity', 'ShipCountry']).take(200);
const columns = [{ width: 100 }, { width: 130 }, { width: 100 }, { width: 220 }, { width: 150 }, { width: 180 }];
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
<template>
<ejs-spreadsheet>
<e-sheets>
<e-sheet name="Shipment Details" :columns="columns">
<e-ranges>
<e-range :dataSource="dataSource" :query="query" :showFieldAsHeader="false"
startCell="A2"></e-range>
</e-ranges>
<e-rows>
<e-row>
<e-cells>
<e-cell value="Order ID"></e-cell>
<e-cell value="Customer Name"></e-cell>
<e-cell value="Freight"></e-cell>
<e-cell value="Ship Name"></e-cell>
<e-cell value="Ship City"></e-cell>
<e-cell value="Ship Country"></e-cell>
</e-cells>
</e-row>
</e-rows>
</e-sheet>
</e-sheets>
</ejs-spreadsheet>
</template>
<script>
import { SpreadsheetComponent, RowsDirective, RowDirective, CellsDirective, CellDirective, RangesDirective, RangeDirective, SheetsDirective, SheetDirective } from "@syncfusion/ej2-vue-spreadsheet";
import { DataManager, Query } from "@syncfusion/ej2-data";
export default {
name: "App",
components: {
"ejs-spreadsheet": SpreadsheetComponent,
"e-sheets": SheetsDirective,
"e-sheet": SheetDirective,
"e-ranges": RangesDirective,
"e-range": RangeDirective,
"e-rows": RowsDirective,
"e-row": RowDirective,
"e-cells": CellsDirective,
"e-cell": CellDirective
},
data: () => {
return {
dataSource: new DataManager({
// Remote service url.
url: "https://services.syncfusion.com/vue/production/api/Orders",
crossDomain: true
}),
query: new Query().select(['OrderID', 'CustomerID', 'Freight', 'ShipName', 'ShipCity', 'ShipCountry']).take(200),
columns: [{ width: 100 }, { width: 130 }, { width: 100 }, { width: 220 }, { width: 150 }, { width: 180 }],
}
}
}
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
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.
<template>
<ejs-spreadsheet ref="spreadsheet">
<e-sheets>
<e-sheet name="Order Details" :columns="columns" :created="created">
<e-ranges>
<e-range :dataSource="dataSource"></e-range>
</e-ranges>
</e-sheet>
</e-sheets>
</ejs-spreadsheet>
</template>
<script setup>
import { ref } from "vue"
import { SpreadsheetComponent as EjsSpreadsheet, RangesDirective as ERanges, RangeDirective as ERange, SheetsDirective as ESheets, SheetDirective as ESheet } from "@syncfusion/ej2-vue-spreadsheet";
import { DataManager, ODataAdaptor } from "@syncfusion/ej2-data";
const spreadsheet = ref(null);
const dataSource = new DataManager({
// Remote service url.
url: "https://services.syncfusion.com/vue/production/api/Orders",
adaptor: new ODataAdaptor(),
crossDomain: true
});
const columns = [{ width: 80 }, { width: 80 }, { width: 80 }, { width: 80 }, { width: 80 }, { width: 80 }, { width: 280 }, { width: 180 }, { width: 80 }, { width: 180 }, { width: 180 }];
const created = function () {
//Applies cell and number formatting to specified range of the active sheet.
spreadsheet.value.cellFormat({ fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' }, 'A1:K1');
}
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
<template>
<ejs-spreadsheet ref="spreadsheet">
<e-sheets>
<e-sheet name="Order Details" :columns="columns" :created="created">
<e-ranges>
<e-range :dataSource="dataSource"></e-range>
</e-ranges>
</e-sheet>
</e-sheets>
</ejs-spreadsheet>
</template>
<script>
import { SpreadsheetComponent, RangesDirective, RangeDirective, SheetsDirective, SheetDirective } from "@syncfusion/ej2-vue-spreadsheet";
import { DataManager, ODataAdaptor } from "@syncfusion/ej2-data";
export default {
name: "App",
components: {
"ejs-spreadsheet": SpreadsheetComponent,
"e-sheets": SheetsDirective,
"e-sheet": SheetDirective,
"e-ranges": RangesDirective,
"e-range": RangeDirective
},
data: () => {
return {
dataSource: new DataManager({
// Remote service url.
url: "https://services.syncfusion.com/vue/production/api/Orders",
adaptor: new ODataAdaptor(),
crossDomain: true
}),
columns: [{ width: 80 }, { width: 80 }, { width: 80 }, { width: 80 }, { width: 80 }, { width: 80 }, { width: 280 }, { width: 180 }, { width: 80 }, { width: 180 }, { width: 180 }],
}
},
methods: {
created: function () {
//Applies cell and number formatting to specified range of the active sheet.
this.$refs.spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' }, 'A1:K1');
}
}
}
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
Web API
You can use WebApiAdaptor to bind spreadsheet with Web API created using OData endpoint.
<template>
<ejs-spreadsheet ref="spreadsheet">
<e-sheets>
<e-sheet name="Order Details" :columns="columns" :created="created">
<e-ranges>
<e-range :dataSource="dataSource"></e-range>
</e-ranges>
</e-sheet>
</e-sheets>
</ejs-spreadsheet>
</template>
<script setup>
import { ref } from "vue";
import { SpreadsheetComponent as EjsSpreadsheet, RangesDirective as ERanges, RangeDirective as ERange, SheetsDirective as ESheets, SheetDirective as ESheet } from "@syncfusion/ej2-vue-spreadsheet";
import { DataManager, WebApiAdaptor } from "@syncfusion/ej2-data";
const spreadsheet = ref(null);
const dataSource = new DataManager({
// Remote service url.
url: "https://services.syncfusion.com/vue/production/api/Orders",
adaptor: new WebApiAdaptor(),
crossDomain: true
})
const columns = [{ width: 80 }, { width: 80 }, { width: 80 }, { width: 80 }, { width: 80 }, { width: 80 }, { width: 280 }, { width: 180 }, { width: 80 }, { width: 180 }, { width: 180 }];
const created = function () {
//Applies cell and number formatting to specified range of the active sheet.
spreadsheet.value.cellFormat({ fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' }, 'A1:K1');
}
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
<template>
<ejs-spreadsheet ref="spreadsheet">
<e-sheets>
<e-sheet name="Order Details" :columns="columns" :created="created">
<e-ranges>
<e-range :dataSource="dataSource"></e-range>
</e-ranges>
</e-sheet>
</e-sheets>
</ejs-spreadsheet>
</template>
<script>
import { SpreadsheetComponent, RangesDirective, RangeDirective, SheetsDirective, SheetDirective } from "@syncfusion/ej2-vue-spreadsheet";
import { DataManager, WebApiAdaptor } from "@syncfusion/ej2-data";
export default {
name: "App",
components: {
"ejs-spreadsheet": SpreadsheetComponent,
"e-sheets": SheetsDirective,
"e-sheet": SheetDirective,
"e-ranges": RangesDirective,
"e-range": RangeDirective
},
data: () => {
return {
dataSource: new DataManager({
// Remote service url.
url: "https://services.syncfusion.com/vue/production/api/Orders",
adaptor: new WebApiAdaptor(),
crossDomain: true
}),
columns: [{ width: 80 }, { width: 80 }, { width: 80 }, { width: 80 }, { width: 80 }, { width: 80 }, { width: 280 }, { width: 180 }, { width: 80 }, { width: 180 }, { width: 180 }],
}
},
methods: {
created: function () {
//Applies cell and number formatting to specified range of the active sheet.
this.$refs.spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' }, 'A1:K1');
}
}
}
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
Cell data binding
The Spreadsheet control can bind the data to individual cell in a sheet . To achieve this you can use the
value
property.
Refer to the following code example for cell data binding.
<template>
<ejs-spreadsheet>
<e-sheets>
<e-sheet name="Monthly Budget">
<e-rows>
<e-row>
<e-cells>
<e-cell value="Category"></e-cell>
<e-cell value="Planned cost"></e-cell>
<e-cell value="Actual cost"></e-cell>
</e-cells>
</e-row>
<e-row>
<e-cells>
<e-cell value="Food"></e-cell>
<e-cell value="$7000"></e-cell>
<e-cell value="$8120"></e-cell>
</e-cells>
</e-row>
<e-row>
<e-cells>
<e-cell value="Loan"></e-cell>
<e-cell value="$1500"></e-cell>
<e-cell value="$1500"></e-cell>
</e-cells>
</e-row>
<e-row>
<e-cells>
<e-cell value="Medical"></e-cell>
<e-cell value="$300"></e-cell>
<e-cell value="$0"></e-cell>
</e-cells>
</e-row>
<e-row>
<e-cells>
<e-cell value="Clothing"></e-cell>
<e-cell value="$400"></e-cell>
<e-cell value="$140"></e-cell>
</e-cells>
</e-row>
<e-row>
<e-cells>
<e-cell value="Education"></e-cell>
<e-cell value="$900"></e-cell>
<e-cell value="$750"></e-cell>
</e-cells>
</e-row>
<e-row>
<e-cells>
<e-cell value="Insurance"></e-cell>
<e-cell value="$30"></e-cell>
<e-cell value="$30"></e-cell>
</e-cells>
</e-row>
</e-rows>
<e-columns>
<e-column :width="width1"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width1"></e-column>
</e-columns>
</e-sheet>
</e-sheets>
</ejs-spreadsheet>
</template>
<script setup>
import { SpreadsheetComponent as EjsSpreadsheet, SheetsDirective as ESheets, SheetDirective as ESheet, ColumnsDirective as EColumns, ColumnDirective as EColumn, RowsDirective as ERows, RowDirective as ERow, CellsDirective as ECells, CellDirective as ECell } from "@syncfusion/ej2-vue-spreadsheet";
const width1 = 110;
const width2 = 115;
</script>
<style>
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-spreadsheet/styles/material.css";
</style>
<template>
<ejs-spreadsheet>
<e-sheets>
<e-sheet name="Monthly Budget">
<e-rows>
<e-row>
<e-cells>
<e-cell value="Category"></e-cell>
<e-cell value="Planned cost"></e-cell>
<e-cell value="Actual cost"></e-cell>
</e-cells>
</e-row>
<e-row>
<e-cells>
<e-cell value="Food"></e-cell>
<e-cell value="$7000"></e-cell>
<e-cell value="$8120"></e-cell>
</e-cells>
</e-row>
<e-row>
<e-cells>
<e-cell value="Loan"></e-cell>
<e-cell value="$1500"></e-cell>
<e-cell value="$1500"></e-cell>
</e-cells>
</e-row>
<e-row>
<e-cells>
<e-cell value="Medical"></e-cell>
<e-cell value="$300"></e-cell>
<e-cell value="$0"></e-cell>
</e-cells>
</e-row>
<e-row>
<e-cells>
<e-cell value="Clothing"></e-cell>
<e-cell value="$400"></e-cell>
<e-cell value="$140"></e-cell>
</e-cells>
</e-row>
<e-row>
<e-cells>
<e-cell value="Education"></e-cell>
<e-cell value="$900"></e-cell>
<e-cell value="$750"></e-cell>
</e-cells>
</e-row>
<e-row>
<e-cells>
<e-cell value="Insurance"></e-cell>
<e-cell value="$30"></e-cell>
<e-cell value="$30"></e-cell>
</e-cells>
</e-row>
</e-rows>
<e-columns>
<e-column :width="width1"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width1"></e-column>
</e-columns>
</e-sheet>
</e-sheets>
</ejs-spreadsheet>
</template>
<script>
import { SpreadsheetComponent, SheetsDirective, SheetDirective, ColumnsDirective, ColumnDirective, RowsDirective, RowDirective, CellsDirective, CellDirective } from "@syncfusion/ej2-vue-spreadsheet";
export default {
name: "App",
components: {
"ejs-spreadsheet": SpreadsheetComponent,
"e-sheets": SheetsDirective,
"e-sheet": SheetDirective,
"e-rows": RowsDirective,
"e-row": RowDirective,
"e-cells": CellsDirective,
"e-cell": CellDirective,
"e-columns": ColumnsDirective,
"e-column": ColumnDirective
},
data: () => {
return {
width1: 110,
width2: 115,
width3: 100
}
}
}
</script>
<style>
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-spreadsheet/styles/material.css";
</style>
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 benull
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 useedit
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 thedataSourceChanged
event with actionadd
.
<template>
<div>
<div>
<ejs-button id="changeDataBtn" class="e-btn" v-on:click.native="dataSourceBtnClick"
style="margin-bottom: 10px">Change Datasource</ejs-button>
<ejs-spreadsheet ref="spreadsheet" :dataSourceChanged="dataSourceChanged">
<e-sheets>
<e-sheet name="Car Sales Report">
<e-ranges>
<e-range :dataSource="dataSource"></e-range>
</e-ranges>
<e-columns>
<e-column :width="width1"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width1"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width3"></e-column>
</e-columns>
</e-sheet>
</e-sheets>
</ejs-spreadsheet>
</div>
<div>
<h4><b>Event Trace</b></h4>
<div id="evt" style="border: 1px solid #dcdcdc;padding: 10px;">
<div style="height:173px;overflow: auto;min-width: 250px;">
<span id="EventLog" style="word-break: normal;"></span>
</div>
<ejs-button id="clearBtn" class='e-btn'>Clear</ejs-button>
</div>
</div>
</div>
</template>
<script setup>
import { ref } from "vue";
import { SpreadsheetComponent as EjsSpreadsheet, ColumnsDirective as EColumns, ColumnDirective as EColumn, RangesDirective as ERanges, RangeDirective as ERange, SheetsDirective as ESheets, SheetDirective as ESheet } from "@syncfusion/ej2-vue-spreadsheet";
import { tradeData, defaultData } from './data.js';
import { ButtonComponent as EjsButton } from "@syncfusion/ej2-vue-buttons";
const spreadsheet = ref(null);
const width1 = 110;
const width2 = 115;
const width3 = 100;
const dataSource = tradeData;
const dataSourceChanged = function (args) {
appendElement("Data source changed with" + "<b> " + args.action + "</b> action<hr>");
}
const dataSourceBtnClick = function () {
spreadsheet.value.ej2Instances.sheets[0].ranges[0].dataSource = defaultData;
}
const clearBtnClick = function () {
document.getElementById("EventLog").innerHTML = "";
}
const appendElement = function (html) {
let span = document.createElement("span");
span.innerHTML = html;
let log = document.getElementById("EventLog");
log.insertBefore(span, log.firstChild);
}
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
<template>
<div>
<div>
<ejs-button id="changeDataBtn" class="e-btn" v-on:click.native="dataSourceBtnClick"
style="margin-bottom: 10px">Change Datasource</ejs-button>
<ejs-spreadsheet ref="spreadsheet" :dataSourceChanged="dataSourceChanged">
<e-sheets>
<e-sheet name="Car Sales Report">
<e-ranges>
<e-range :dataSource="dataSource"></e-range>
</e-ranges>
<e-columns>
<e-column :width="width1"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width1"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width3"></e-column>
</e-columns>
</e-sheet>
</e-sheets>
</ejs-spreadsheet>
</div>
<div>
<h4><b>Event Trace</b></h4>
<div id="evt" style="border: 1px solid #dcdcdc;padding: 10px;">
<div style="height:173px;overflow: auto;min-width: 250px;">
<span id="EventLog" style="word-break: normal;"></span>
</div>
<ejs-button id="clearBtn" class='e-btn'>Clear</ejs-button>
</div>
</div>
</div>
</template>
<script>
import { SpreadsheetComponent, ColumnsDirective, ColumnDirective, RangesDirective, RangeDirective, SheetsDirective, SheetDirective } from "@syncfusion/ej2-vue-spreadsheet";
import { tradeData, defaultData } from './data.js';
import { ButtonComponent } from "@syncfusion/ej2-vue-buttons";
export default {
name: "App",
components: {
"ejs-button": ButtonComponent,
"ejs-spreadsheet": SpreadsheetComponent,
"e-sheets": SheetsDirective,
"e-sheet": SheetDirective,
"e-ranges": RangesDirective,
"e-range": RangeDirective,
"e-columns": ColumnsDirective,
"e-column": ColumnDirective
},
data: () => {
return {
width1: 110,
width2: 115,
width3: 100,
dataSource: tradeData
}
},
methods: {
dataSourceChanged: function (args) {
this.appendElement("Data source changed with" + "<b> " + args.action + "</b> action<hr>"
);
},
dataSourceBtnClick: function () {
this.$refs.spreadsheet.ej2Instances.sheets[0].ranges[0].dataSource = defaultData;
},
clearBtnClick: function () {
document.getElementById("EventLog").innerHTML = "";
},
appendElement: function (html) {
let span = document.createElement("span");
span.innerHTML = html;
let log = document.getElementById("EventLog");
log.insertBefore(span, log.firstChild);
}
}
}
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
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 RangeDirective
. 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.
<template>
<div>
<ejs-button class="e-btn custom-btn" @click="updateDataCollection">Update Dynamic Content</ejs-button>
<ejs-spreadsheet ref="spreadsheet" :created="created">
<e-sheets>
<e-sheet>
<e-ranges>
<e-range :dataSource="dataSource"></e-range>
</e-ranges>
<e-columns>
<e-column :width="width1"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width1"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width3"></e-column>
</e-columns>
</e-sheet>
</e-sheets></ejs-spreadsheet>
</div>
</template>
<script setup>
import { ref } from "vue";
import { SpreadsheetComponent as EjsSpreadsheet, ColumnsDirective as EColumns, ColumnDirective as EColumn, RangesDirective as ERanges, RangeDirective as ERange, SheetsDirective as ESheets, SheetDirective as ESheet } from "@syncfusion/ej2-vue-spreadsheet";
import { defaultData } from './data.js';
const spreadsheet = ref(null);
const dataSource = defaultData;
const width1 = 180;
const width2 = 130;
const width3 = 120;
const created= function () {
var spreadsheet = this.$refs.spreadsheet;
spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' }, 'A1:F1');
}
const updateDataCollection = ()=> {
var spreadsheet = this.$refs.spreadsheet;
var 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',
};
spreadsheet.updateRange(newDataCollection, 0);
}
</script>
<style>
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-spreadsheet/styles/material.css";
.custom-btn {
margin: 0 0 10px 0;
}
</style>
<template>
<div>
<ejs-button class="e-btn custom-btn" @click="updateDataCollection">Update Dynamic Content</ejs-button>
<ejs-spreadsheet ref="spreadsheet" :created="created">
<e-sheets>
<e-sheet>
<e-ranges>
<e-range :dataSource="dataSource"></e-range>
</e-ranges>
<e-columns>
<e-column :width="width1"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width1"></e-column>
<e-column :width="width2"></e-column>
<e-column :width="width3"></e-column>
</e-columns>
</e-sheet>
</e-sheets></ejs-spreadsheet>
</div>
</template>
<script>
import Vue from "vue";
import { SpreadsheetPlugin } from "@syncfusion/ej2-vue-spreadsheet";
import { defaultData } from './data.js';
Vue.use(SpreadsheetPlugin);
export default {
data: () => {
return {
dataSource: defaultData,
width1: 180,
width2: 130,
width3: 120,
}
},
methods: {
created: function () {
var spreadsheet = this.$refs.spreadsheet;
spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' }, 'A1:F1');
},
updateDataCollection: function () {
var spreadsheet = this.$refs.spreadsheet;
var 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',
};
spreadsheet.updateRange(newDataCollection, 0);
}
}
}
</script>
<style>
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-spreadsheet/styles/material.css";
.custom-btn {
margin: 0 0 10px 0;
}
</style>
Note
You can refer to our Vue Spreadsheet feature tour page for its groundbreaking feature representations. You can also explore our Vue Spreadsheet example to knows how to present and manipulate data.