Edit this page

Create Header and Footer Rows for a Spreadsheet with DataSource

Environment

Product Progress Kendo UI Spreadsheet

Description

I am using a Spreadsheet that is bound to a DataSource which is configured for one of its sheets. The data is loading as expected from a remote call.

How can I have additional header and footer rows that are not part of the returned data?

Solution

  1. Manually insert the header and the footer by using the client-side API of the Spreadsheet.
  2. Handle the requestEnd event of the DataSource.

    The header row is inserted through the Sheet.insertRow() method. Then, the range for the row is retrieved through Sheet.range(). The values are set Range.values().

This approach is not applicable if the data is edited and saved by using the other DataSource transport methods (Update, Create, and Destroy). Such scenarios do not support the insertion of a custom header or footer.

<div id="spreadsheet" style="width: 100%"></div>

<script>
  $(function() {
    var crudServiceBaseUrl = "https://demos.telerik.com/kendo-ui/service";

    function onRequestEnd(e) {
      // Check the request type
      if (e.type === 'read') {
        setTimeout(function() {
          var spread = $("#spreadsheet").getKendoSpreadsheet();
          var sheet = spread.activeSheet();
          var responseLength = e.response.length;
          // Insert the custom header row
          sheet.insertRow(0);
          var headerRange = sheet.range('A1:E1');
          headerRange.values([["Test", "This", "Custom", "Header", "Once"]]);
          headerRange.fontSize(30);
          // Get the respective row for the footer
          var footerRowNumber = (responseLength + 3).toString();
          var footerRange = sheet.range('A' + footerRowNumber + ':E' + footerRowNumber);
          footerRange.values([["Test", "This", "Custom", "Footer", "Ho!"]]);
          footerRange.fontSize(20);
        }, 0);
      }
    }

    var dataSource = new kendo.data.DataSource({
      requestEnd: onRequestEnd,
      transport: {
        read:  {
          url: crudServiceBaseUrl + "/Products",
          dataType: "jsonp"
        }
      },
      schema: {
        model: {
          id: "ProductID"
        }
      }
    });

    $("#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 }
        ]
      }]
    });
  });
</script>

See Also

Is this article helpful? Yes / No
Thank you for your feedback!

Give article feedback

Tell us how we can improve this article

close
Dummy