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

Export multiple data sources into one excel file

1 Answer 480 Views
Data Source
This is a migrated thread and some comments may be shown as answers.
yuhan
Top achievements
Rank 1
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

Sort by
0
Daniel
Telerik team
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!
 
Tags
Data Source
Asked by
yuhan
Top achievements
Rank 1
Answers by
Daniel
Telerik team
Share this question
or