Data Source Binding
The Spreadsheet supports binding individual sheets to a Data Source instance which allows you to quickly bring data from external data sources into the Spreadsheet and edit it.
For a runnable example, refer to the demo on binding the Spreadsheet to the DataSource. The Spreadsheet DataSource from that example uses the read and submit transport options. The submit option is required to properly handle a scenario in which the user creates, updates and deletes items simultaneously.
When using separate create, update and destroy handlers, it is possible that one of them fails, while the others do not. That will result in a mismatch of the data state between the client (the Spreadsheet) and the remote source. The submit option handles all operations within a single request. It will not save any changes if any of the items is invalid.
The following example demonstrates how to configure the Spreadsheet to use a DataSource.
<button class="k-button" id="save">Save changes</button>
<button class="k-button" id="cancel">Cancel changes</button>
<div id="spreadsheet" style="width: 100%"></div>
<script>
$(function() {
var crudServiceBaseUrl = "https://demos.telerik.com/kendo-ui/service";
var dataSource = new kendo.data.DataSource({
transport: {
read: onRead,
submit: onSubmit
},
batch: true,
change: function() {
$("#cancel, #save").toggleClass("k-disabled", !this.hasChanges());
},
schema: {
model: {
id: "ProductID",
fields: {
ProductID: { type: "number" },
ProductName: { type: "string" },
UnitPrice: { type: "number" },
Discontinued: { type: "boolean" },
UnitsInStock: { type: "number" }
}
}
}
});
$("#spreadsheet").kendoSpreadsheet({
columns: 20,
rows: 100,
toolbar: false,
sheetsbar: false,
sheets: [{
name: "Products",
dataSource: dataSource,
rows: [{
height: 40,
cells: [
{
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white"
},{
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white"
},{
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white"
},{
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white"
},{
bold: "true",
background: "#9c27b0",
textAlign: "center",
color: "white"
}]
}],
columns: [
{ width: 100 },
{ width: 415 },
{ width: 145 },
{ width: 145 },
{ width: 145 }
]
}]
});
function onSubmit(e) {
$.ajax({
url: crudServiceBaseUrl + "/Products/Submit",
data: { models: kendo.stringify(e.data) },
contentType: "application/json",
dataType: "jsonp",
success: function (result) {
e.success(result.Updated, "update");
e.success(result.Created, "create");
e.success(result.Destroyed, "destroy");
},
error: function (xhr, httpStatusMessage, customErrorMessage) {
alert(xhr.responseText);
}
});
}
function onRead(options) {
$.ajax({
url: crudServiceBaseUrl + "/Products",
dataType: "jsonp",
success: function (result) {
options.success(result);
},
error: function (result) {
options.error(result);
}
});
}
$("#save").click(function() {
if (!$(this).hasClass("k-disabled")) {
dataSource.sync();
}
});
$("#cancel").click(function() {
if (!$(this).hasClass("k-disabled")) {
dataSource.cancelChanges();
}
});
});
</script>
Specific Behavior
Data Source binding switches the sheet to a special data-bound mode. It differs from the standard behavior in the following ways:
- Column headers are inferred from the data item fields. Configure the column headers and ordering by using the sheet
setDataSourcemethod. - Cell styles, formulas, and formats are not persisted in the data source.
- Row height and column width are not persisted in the data source.
- Sorting and filtering are applied locally.
CRUD operations are also handled in a specific way:
- Inserted rows are always appended at the end, regardless of the actual row index.
- Updating cell content translates into update operations.
- Deleting rows translates into destroy operations.
- Inserting and removing columns is not supported.
Unsupported Scenarios
- The Sheet cannot be bound to a source which does not contain any items because the header row in the sheet is generated based on the data items fields.
- Records cannot be edited after sorting the sheet (related feature request).
- Records cannot be edited after filtering the sheet (related feature request).