Export multiple data sources into one excel file

2 posts, 0 answers
  1. yuhan
    yuhan avatar
    4 posts
    Member since:
    Jan 2013

    Posted 27 Nov 2015 Link to this post

    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. 
  2. Daniel
    Admin
    Daniel avatar
    2118 posts

    Posted 01 Dec 2015 Link to this post

    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!
     
  3. Kendo UI is VS 2017 Ready
Back to Top