Exporting Data Sources
Kendo UI 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 that is stored in a Kendo UI DataSource to an Excel file.
Getting Started
To extract the DataSource to an Excel document (workbook):
- Instantiate a Kendo UI DataSource.
- Create the rows header structure based on the DataSource data. Save it into an
array. - Fetch the remote data and by using the callback of the
fetchmethod, loop through the items and push the data to therowsarray. - Instantiate a
kendo.ooxml.Workbook. The workbook has an array of sheets, where you can set their width and title, and set therowsproperty to the already createdrowsarray. - Call the
toDataURLortoDataURLAsyncmethods of the workbook to get the output Excel file as a data URI.
Creating Excel Workbooks
The following example demonstrates how to create an Excel workbook based on the steps from the previous section.
<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: [
// The first cell.
{ value: "OrderID" },
// The second cell.
{ value: "Freight" },
// The third cell.
{ value: "ShipName" },
// The fourth cell.
{ value: "OrderDate" },
// The fifth cell.
{ value: "ShipCity" }
]
}];
// Use fetch so that you 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 }
],
// The title of the sheet.
title: "Orders",
// The rows of the sheet.
rows: rows
}
]
});
// Save the file as an Excel file with the xlsx extension.
kendo.saveAs({dataURI: workbook.toDataURL(), fileName: "Test.xlsx"});
});
</script>