Edit this page

DataSource Export

The Kendo UI API allows you to create Excel documents from any data set and save them to a client machine. In this way you are able to extract the data stored in a Kendo UI DataSource to an Excel file.

Configuration

Requirements

To extract the DataSource to an Excel document (workbook), follow these steps:

  1. Instantiate a Kendo UI DataSource.
  2. Create the rows header structure based on the DataSource data. Save it into an array.
  3. Fetch the remote data and by using the callback of the fetch method, loop through the items and push the data to the rows array.
  4. Instantiate a kendo.ooxml.Workbook. The workbook has an array of sheets, where you can set their width and title, and set the rows property to the already created rows array.
  5. Call the toDataURL method of the workbook to get the output Excel file as a data URI.

Create Excel Workbook

The example below demonstrates how to create an Excel workbook based on the requirements from the previous section.

Example
    <script>
      var ds = new kendo.data.DataSource({
        type: "odata",
        transport: {
          read: "https://demos.telerik.com/kendo-ui/service/Northwind.svc/Orders"
        },
        schema: {
          model: {
            fields: {
              OrderID: { type: "number" },
              Freight: { type: "number" },
              ShipName: { type: "string" },
              OrderDate: { type: "date" },
              ShipCity: { type: "string" }
            }
          }
        }
      });

      var rows = [{
        cells: [
           // First cell
          { value: "OrderID" },
           // Second cell
          { value: "Freight" },
          // Third cell
          { value: "ShipName" },
          // Fourth cell
          { value: "OrderDate" },
          // Fifth cell
          { value: "ShipCity" }
        ]
      }];

      //using fetch, so we can process the data when the request is successfully completed
      ds.fetch(function(){
        var data = this.data();
        for (var i = 0; i < data.length; i++){
          //push single row for every record
          rows.push({
            cells: [
              { value: data[i].OrderID },
              { value: data[i].Freight },
              { value: data[i].ShipName },
              { value: data[i].OrderDate },
              { value: data[i].ShipCity }
            ]
          })
        }
        var workbook = new kendo.ooxml.Workbook({
          sheets: [
            {
              columns: [
                // Column settings (width)
                { autoWidth: true },
                { autoWidth: true },
                { autoWidth: true },
                { autoWidth: true },
                { autoWidth: true }
              ],
              // Title of the sheet
              title: "Orders",
              // Rows of the sheet
              rows: rows
            }
          ]
        });
        //save the file as Excel file with extension xlsx
        kendo.saveAs({dataURI: workbook.toDataURL(), fileName: "Test.xlsx"});
      });
    </script>

See Also

Articles on the Excel export functionality in Kendo UI:

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

Give article feedback

Tell us how we can improve this article

close
Dummy