Detail Grid Excel Export All Pages All Details - No Details.

[Dustin]

I am trying to export to excel a grid that has detail grids. I want to be able to export all pages of the detail grids as well.

I have followed the example provided here: http://docs.telerik.com/kendo-ui/aspnet-mvc/helpers/grid/how-to/excel/detail-grid-export

I get the master grid export fine, I end up with empty rows where the detail data should be.

 Here is my View code:  The export scripts start at line 85.

001.<div class="row">
002.    <div class="col-lg-12">
003.        <p>
004.            @(Html.Kendo().Grid<User_Tracking.Models.SystemTypesModel>()
005.                  .Name("grid")
006.                  .Columns(columns =>
007.                  {
008.                      columns.Bound(c => c.TypeName).Title("System Name");
009.                      columns.Bound(c => c.Enabled);
010.                      columns.Bound(c => c.AccessCount).Title("Users with system access");
011.                  })
012.                  .ToolBar(toolbar =>
013.                  {
014.                      toolbar.Excel();
015.                  })
016.                  .Excel(e => e.AllPages(true))
017.                  .ClientDetailTemplateId("template")
018.                  .HtmlAttributes(new { style = "height:500px;" })
019.                  .ColumnMenu()
020.                  .Pageable()
021.                  .Events(e => e.ExcelExport("systems_excelExport"))
022.                  .Selectable(selectable =>
023.                  {
024.                      selectable.Mode(GridSelectionMode.Multiple);
025.                      selectable.Type(GridSelectionType.Cell);
026.                  })
027.                  .Sortable(sortable =>
028.                  {
029.                      sortable.SortMode(GridSortMode.MultipleColumn);
030.                  })
031.                  .Filterable()
032.                  .Scrollable()
033.                  .DataSource(dataSource => dataSource
034.                      .Ajax()
035.                      //.ServerOperation(false)
036.                      .Read(read => read.Action("SystemAccess_Read", "SystemAccess"))
037.                  )
038.                  .Events(e => e.DataBound("dataBound"))
039.            )
040.        </p>
041.    </div>
042.</div>
043.
044.
045.<script id="template" type="text/kendo-tmpl">
046.    @(Html.Kendo().Grid<User_Tracking.Models.UserViewModel>()
047.        .Name("grid_#=SystemId#")
048.        .Columns(r =>
049.        {
050.            r.Bound(c => c.UserName);
051.            r.Bound(c => c.FirstName);
052.            r.Bound(c => c.LastName);
053.            r.Bound(c => c.EmailAddress);
054.            r.Bound(c => c.Dept.DeptName);
055.            r.Bound(c => c.Loc.Name);
056.            r.Bound(c => c.Con.Name);
057.            r.Bound(c => c.Comments);
058.            r.Bound(c => c.DateEntered).Format("{0:MM-dd-yyyy}");
059.            r.Bound(c => c.Terminated);
060.            r.Bound(c => c.DateTerminated).Format("{0:MM-dd-yyyy}");
061.        })
062.        .DataSource(ds => ds
063.            .Ajax()
064.            .Read(r => r.Action("HBUsers_Read", "SystemAccess", new { SysId = "#=SystemId#" }))
065.            .PageSize(10)
066.        )
067.        .Events(e => e.ExcelExport("users_excelExport"))
068.        .Pageable()
069.        .Selectable(selectable =>
070.        {
071.            selectable.Mode(GridSelectionMode.Multiple);
072.            selectable.Type(GridSelectionType.Cell);
073.        })
074.      .Sortable(sortable =>
075.      {
076.          sortable.SortMode(GridSortMode.MultipleColumn);
077.      })
078.        //.Filterable()
079.        .Scrollable()
080.        //.ColumnMenu()
081.        .ToClientTemplate()
082.    )
083.</script>
084.
085.<script>
086.    var detailExportPromises = [];
087.
088.    var dataSource = new kendo.data.DataSource({
089.        type: "aspnetmvc-ajax",
090.        transport: {
091.            read: "@Url.Action("HBCUsers_Read", "SystemAccess")",
092.            type: "POST"
093.        },
094.        schema: {
095.            data: "Data",
096.            total: "Total",
097.            error: "Errors"
098.        }
099.    });
100.
101.    dataSource.read();
102.
103.    function dataBound() {
104.        detailExportPromises = [];
105.    }
106.
107.    function users_excelExport(e) {
108.        e.preventDefault();
109.    }
110.
111.    function systems_excelExport(e) {
112.        e.preventDefault();
113.
114.        var workbook = e.workbook;
115.
116.        detailExportPromises = [];
117.
118.        var masterData = e.data;
119.
120.        for (var rowIndex = 0; rowIndex < masterData.length; rowIndex++) {
121.            exportChildData(masterData[rowIndex].SystemId, rowIndex);
122.        }
123.
124.        // wait for all detail grids to finish exporting
125.        $.when.apply(null, detailExportPromises)
126.        .then(function () {
127.            // get the export results
128.            var detailExports = $.makeArray(arguments);
129.
130.            // sort by masterRowIndex
131.            detailExports.sort(function (a, b) {
132.                return a.masterRowIndex - b.masterRowIndex;
133.            });
134.
135.            // add an empty column
136.            workbook.sheets[0].columns.unshift({ width: 30 });
137.
138.            // prepend an empty cell to each row
139.            for (var i = 0; i < workbook.sheets[0].rows.length; i++) {
140.                workbook.sheets[0].rows[i].cells.unshift({});
141.            }
142.
143.            // merge the detail export sheet rows with the master sheet rows
144.            // loop backwards so the masterRowIndex doesn't need to be updated
145.            for (var i = detailExports.length - 1; i >= 0; i--) {
146.                var masterRowIndex = detailExports[i].masterRowIndex + 1;
147.
148.                var sheet = detailExports[i].sheet;
149.
150.                // prepend an empty cell to each row
151.                for (var ci = 0; ci < sheet.rows.length; ci++) {
152.     //Error occurs here, the cells[0].value is undefined. So I put a check for length
153.                    if (sheet.rows[ci].cells.length > 0) {
154.                        if (sheet.rows[ci].cells[0].value) {
155.                            sheet.rows[ci].cells.unshift({});
156.                        }
157.                    }
158.                }
159.
160.       // insert the detail sheet rows after the master row
161.       [].splice.apply(workbook.sheets[0].rows, [masterRowIndex + 1, 0].concat(sheet.rows));
162.            }
163.
164.            // save the workbook
165.            kendo.saveAs({
166.                dataURI: new kendo.ooxml.Workbook(workbook).toDataURL(),
167.                fileName: "Users by System Export.xlsx"
168.            });
169.        });
170.    }
171.
172.    function exportChildData(SystemID, rowIndex) {
173.        var deferred = $.Deferred();
174.
175.        detailExportPromises.push(deferred);
176.
177.        var rows = [{
178.            cells: [
179.                { value: "UserName" },
180.                { value: "FirstName" },
181.                { value: "LastName" },
182.                { value: "EmailAddress" },
183.                { value: "Dept.DeptName" },
184.                { value: "Loc.Name" },
185.                { value: "Con.Name" },
186.                { value: "Comments" },
187.                { value: "DateEntered)" },
188.                { value: "Terminated" },
189.                { value: "DateTerminated" }
190.            ]
191.        }];
192.
193.        dataSource.filter({ field: "SystemId", operator: "eq", value: SystemID });
194.        dataSource.fetch(function () {
195.            var view = dataSource.view();
196.            if (view.length > 0) {
197.                console.log(view.length);
198.                console.log(view[0].FirstName);
199.                console.log(view[0].Loc.Name)
200.            }
201.        });
202.
203.
204.        var exporter = new kendo.ExcelExporter({
205.            columns: [
206.                { value: "UserName" },
207.                { value: "FirstName" },
208.                { value: "LastName" },
209.                { value: "EmailAddress" },
210.                { value: "Dept.DeptName" },
211.                { value: "Loc.Name" },
212.                { value: "Con.Name" },
213.                { value: "Comments" },
214.                { value: "DateEntered)" },
215.                { value: "Terminated" },
216.                { value: "DateTerminated" }
217.            ],
218.            dataSource: dataSource
219.        });
220.
221.        exporter.workbook().then(function (book, data) {
222.            deferred.resolve({
223.                masterRowIndex: rowIndex,
224.                sheet: book.sheets[0]
225.            });
226.        });
227.    }
228.</script>

[Chinmay]

Thanks Dustin ! This is helpful.

[Ivan Danchev]

Here's working link to the example Dustin mentioned: https://docs.telerik.com/aspnet-mvc/html-helpers/data-management/grid/how-to/Export/detail-grid-export

 

[Dustin]

Nevermind... found out why it was doing it.

 Where the kendo.ExcelExporter is, I had columns: [{ value: "whatever"}] instead of columns: [{ field: "whatever"}]

 Now just to figure out how to get the date formatted right. and some spacing issues... yay!

[Dimiter Madjarov]

Hello Dustin,

Thanks for the update. I am glad the issue is resolved.

Regards,
Dimiter Madjarov
Telerik