Bind to Data Source

The Spreadsheet widget supports binding individual sheets to a Data Source instance.

This allows you to quickly bring data from external data sources into the Spreadsheet and, optionally, edit it. Here is a small example of a Spreadsheet using DataSource:

Example
    <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-state-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-state-disabled")) {
            dataSource.sync();
          }
        });

        $("#cancel").click(function() {
          if (!$(this).hasClass("k-state-disabled")) {
            dataSource.cancelChanges();
          }
        });
      });
    </script>

Note that the Spreadsheet DataSource in the above example uses 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 handles all operations within a single request. It will not save any changes if any of the items is invalid.

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 setDataSource method.
  • 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.

For a functional example, refer to the demo on Spreadsheet/DataSource binding.

Currently Not Supported Scenarios

At the moment, Spreadsheet Sheet with DataSource binding does not offer support for the following scenarios:

See Also

In this article
Not finding the help you need? Improve this article