This is a migrated thread and some comments may be shown as answers.

Export multiple data sources into one excel file

1 Answer 539 Views

Data Source

This is a migrated thread and some comments may be shown as answers.

yuhan asked on 27 Nov 2015, 12:55 PM

I am trying to export multiple data sources into one excel file, each data source has their own sheet in the excel.
I notice that If I move the following code out from the datasource.fetch() cluster, no data from the data source will be inserted.

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
},
{
columns: [
// Column settings (width)
{ autoWidth: true },
{ autoWidth: true },
{ autoWidth: true },
{ autoWidth: true },
{ autoWidth: true }
],
// Title of the sheet
title: "Orders2",
// Rows of the sheet
rows: rows
}
]
});

//save the file as Excel file with extension xlsx
kendo.saveAs({dataURI: workbook.toDataURL(), fileName: "Test.xlsx"});

The problem is datasource.fetch() are skipped or temporary ignored, the above code will be executed before datasource.fetch() are execute.
To overcome that problem, I have to put a timeout function() at the setTimeout(function(){kendo.saveAs({dataURI: workbook.toDataURL(), fileName: "Test.xlsx"});},20000);. By doing this way, the exported excel file will contain data from the data source which I find it is not a very appropriate way of doing it.

Here is the snippets code of that I used from the sample provided by telerik with some code modification.

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Kendo UI Snippet</title>

<link rel="stylesheet" href="http://kendo.cdn.telerik.com/2015.3.1111/styles/kendo.common.min.css">
<link rel="stylesheet" href="http://kendo.cdn.telerik.com/2015.3.1111/styles/kendo.rtl.min.css">
<link rel="stylesheet" href="http://kendo.cdn.telerik.com/2015.3.1111/styles/kendo.default.min.css">
<link rel="stylesheet" href="http://kendo.cdn.telerik.com/2015.3.1111/styles/kendo.dataviz.min.css">
<link rel="stylesheet" href="http://kendo.cdn.telerik.com/2015.3.1111/styles/kendo.dataviz.default.min.css">
<link rel="stylesheet" href="http://kendo.cdn.telerik.com/2015.3.1111/styles/kendo.mobile.all.min.css">

<script src="http://code.jquery.com/jquery-1.9.1.min.js"></script>
<script src="http://kendo.cdn.telerik.com/2015.3.1111/js/jszip.min.js"></script>
<script src="http://kendo.cdn.telerik.com/2015.3.1111/js/kendo.all.min.js"></script>
</script>
</head>
<body>

<script>
var ds = new kendo.data.DataSource({
type: "odata",
transport: {
read: "http://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
},
{
columns: [
// Column settings (width)
{ autoWidth: true },
{ autoWidth: true },
{ autoWidth: true },
{ autoWidth: true },
{ autoWidth: true }
],
// Title of the sheet
title: "Orders2",
// Rows of the sheet
rows: rows
}
]
});

//save the file as Excel file with extension xlsx
setTimeout(function(){kendo.saveAs({dataURI: workbook.toDataURL(), fileName: "Test.xlsx"});},20000);
</script>
</body>
</html>

Currently I am showing the code exporting the same data source into different sheet in the excel file, just to keep the code short. Or you can access it from here,http://dojo.telerik.com/Otahe/3
It will be much appreciated if there is a better solutions to do this.

1 Answer, 1 is accepted

answered on 01 Dec 2015, 09:49 AM

Hello,

I am not sure if I understand the issue but yes, no data will be exported if the rows are not populated before calling the saveAs method. Using a timeout will not work in all cases because you do not know how long will the request take. Instead you should use the fetch callback or the promise returned from the fetch method to call the saveAs method. For example with multiple dataSources:

var promiseDS = ds.fetch(function () {

...

})

var promiseDS1 = ds1.fetch(function () {

..

});

$.when(promiseDS, promiseDS1).done(function () {

kendo.saveAs({ dataURI: workbook.toDataURL(), fileName: "Test.xlsx" });

});

Regards,
Daniel
Telerik

Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!