Excel Export with Client Template columns dynamically

10 posts, 0 answers
  1. Christopher
    Christopher avatar
    24 posts
    Member since:
    Mar 2016

    Posted 09 Jun Link to this post

    Currently, the Grid Excel export will not export columns that have a client template defined on them.

    The answer from Telerik HERE, solves the issue in a static way. Meaning that for each column that has a client template definition, you will have to alter the script to add the other columns.

        function excelExport(e) {
            var sheet = e.workbook.sheets[0];
            var employeeTemplate = kendo.template(this.columns[0].template);
            var employerTemplate = kendo.template(this.columns[1].template);
            //Add more template variables and set them to the column index in the grid.

            var data = this.dataSource.view();

            for (var i = 0; i < data.length; i++) {
                sheet.rows[i + 1].cells[0].value = employeeTemplate(data[i]);
                sheet.rows[i + 1].cells[1].value = employerTemplate (data[i]);
                //Add the cell values by column index from the template v
            }
        }

     

    Here is a script to dynamically get all of the columns with client templates : 

    Grid HTML Helper:

     @(Html.Kendo().Grid<Employee>()
                    .Name("employeeGrid")
                    .ToolBar(t => { t.Create(); t.Save(); t.Excel(); })
                    .Events(e => e.ExcelExport("excelExportDynamic"))
                    .Columns(col =>
                    {
                        col.Bound(c => c.Employee).ClientTemplate("#: data.Employee#");
                        col.Bound(c => c.Employer).ClientTemplate("#: data.Employer#);
                        col.ForeignKey(c => c.Status, Model, "StatusID", "StatusCode").Title("Status");
                        col.Command(c => c.Destroy());
                    })
                   //Removed for brevity

     

    Javascript function:

      <script>
           function excelExportDynamic(e) {
            var sheet = e.workbook.sheets[0];
            var colTemplates = [];  //Initialize new array
            var data = this.dataSource.view();

            for (var i = 0; i < this.columns.length; i++) {
                if (this.columns[i].template) {
                    colTemplates.push(kendo.template(this.columns[i].template)); //Add kendo template to colTemplate array if it exists for the given index's column
                } else {
                    colTemplates.push(null); //Add null to given index if template does not exist for the column index. This ensures columns are mapped to the proper index.
                }
            }

            //colTemplate array has been built now: Example of array at this point)     colTemplate: { templateCol[0], null, templateCol[3], null, null  }

            for (var i = 0; i < colTemplates.length; i++) {
                for (var j = 0; j < data.length; j++) {
                    if (colTemplates[i] != null) {
                        sheet.rows[j + 1].cells[i].value = colTemplates[i](data[j]); //Loop through all colTemplates and all data in the grid to build the excel sheet. Skip null colTemplates.
                    }
                }
            }        
        }
    </script>

     

    Hope this helps!!!!!

  2. Viktor Tachev
    Admin
    Viktor Tachev avatar
    1488 posts

    Posted 14 Jun Link to this post

    Hello Christopher,


    The article you are referring to discusses the same functionality you would like to implement. There are different approaches suggested there that you can use depending on your scenario. Please give the export to excel feature a try. It constructs the file on the client-side.



    Regards,
    Viktor Tachev
    Telerik
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  3. UI for ASP.NET MVC is VS 2017 Ready
  4. Christopher
    Christopher avatar
    24 posts
    Member since:
    Mar 2016

    Posted 14 Jun in reply to Viktor Tachev Link to this post

    Hi Vikotr, thank you for your reply.

    Is there anything in future kendoui roadmaps that would include exporting of client template columns out of the box?

  5. Eyup
    Admin
    Eyup avatar
    3005 posts

    Posted 17 Jun Link to this post

    Hi Christopher,

    I'm afraid this requirement is currently not included in any future product roadmap. You can cast your vote in the following feedback item or create your new idea item in our public portal:
    http://kendoui-feedback.telerik.com/forums/127393-kendo-ui-feedback/suggestions/7015590-add-the-grid-column-template-to-be-used-when-expor

    Regards,
    Eyup
    Telerik
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  6. David
    David avatar
    2 posts
    Member since:
    Sep 2016

    Posted 03 Nov in reply to Christopher Link to this post

    Hi Christopher,

    This snippet has been incredibly helpful, however, the solution breaks if the grid is grouped by 1 or more column. I've been trying to cater for grouped/aggregate data but so far not successful. Can you suggest a modification to the snippet to cater for multiple grouped column ? thanks

  7. Christopher
    Christopher avatar
    24 posts
    Member since:
    Mar 2016

    Posted 04 Nov Link to this post

    Hey David,

    I did not actually test the grouping functionality with my solution.

    Give me a little bit to look into it and see if i can make a modification to it.

  8. Christopher
    Christopher avatar
    24 posts
    Member since:
    Mar 2016

    Posted 04 Nov in reply to David Link to this post

    Ok, after digging and experimenting. I have not been able to modify my original solution to be able to include the grouping as you have requested.  However, i did come up with an alternative, albeit more of custom solution that may or may not work for you. This has only been . Also, this alternative does not rely upon the jszip.min.js script. Nor does it use the OTB Export To Excel functionality. Instead, a custom toolbar object with the HtmlAttributes' id property set to the javascript function name. The javascript function wires up to the custom toolbar object's 'onclick' event on load. 

    I will continue to work on a more stable and performant solution as i feel like this solution isnt the best as is. I just dont have the time right now.

    • Tested in IE 11 and Chrome 54.0.2840.59 only.
      •  
    • Tested the Grid filtering, grouping, sorting exporting.
    • Tested only a limited dataset (10 records).

     

    Here is the View and Javascript:

    Grid HTML Helper (MVC View):

    h2>Reporting</h2>

    @(Html.Kendo().Grid<Report>()
        .Name("grid")
        .HtmlAttributes(new { style = "height:700px" })
        .ToolBar(t =>
        {
            t.Custom().Text("Export").HtmlAttributes(new { id = "exportToExcelCustom" });
        })
        .Columns(col =>
        {
            col.Bound(c => c.ID);
            col.Bound(c => c.Title);
            col.Bound(c => c.Category).ClientTemplate("#: data.Category ? data.Category.CategoryCode : 'N/A' #");
            col.Bound(c => c.Active);
        })
        .Scrollable()
        .Sortable()
        .Groupable()
        .Filterable()
        .Navigatable()
        .DataSource(ds =>
        {
            ds.Ajax()
            .ServerOperation(false)
            .Model(m =>
            {
                m.Id(r => r.ID);
            })
            .Read(r => r.Action("Get_ReportList", "Report"));
        })
    )

     

    JavaScript:

    <script>
        $("#exportToExcelCustom").click(function (e) {
            e.preventDefault(); //IF NOT CALLED, THE PAGE WILL REFRESH AFTER FUNCTION IS COMPLETE.
            var grid = e.target.offsetParent;
            var html = grid.innerHTML;

            var testActiveX;
            //CREATE A TEST XL ACTIVEX OBJECT TO CHECK IF BROWSER SUPPORTS ACTIVEX (IE).
            try {
                 //IF SUCCESSFUL, USE THE ACTIVEX METHOD TO EXPORT TO XL.
                testActiveX = new ActiveXObject("Excel.Application");
            }
            catch (e) {
                //IF UNSUCCESSFUL, USE THE WINDOW.OPEN METHOD TO EXPORT TO XL.
                testActiveX = null;
            }

            if (testActiveX != null) { 
                window.clipboardData.setData("Text", html);
                var ExcelObject = new ActiveXObject("Excel.Application");
                ExcelObject.visible = false;
                var ExcelWorkBook = ExcelObject.Workbooks.Add;
                var ExcelWorkSheet = ExcelWorkBook.Worksheets(1);
                ExcelWorkSheet.Paste;
                ExcelObject.visible = true;
            }
            else { //THIS WILL GENERATE A 'FORMATTING and EXTENSION... DO YOU WANT TO OPEN IT ANYWAY?' ERROR IN EXCEL, BUT IF YOU CLICK 'YES', THE DATA WILL OUTPUT CORRECTLY.
                window.open('data:application/vnd.ms-excel,' + encodeURIComponent(html));
            }
        })
    </script>

     

     

  9. Eyup
    Admin
    Eyup avatar
    3005 posts

    Posted 07 Nov Link to this post

    Hello Christopher,

    Thanks for sharing your specific approach with our community. I hope it will prove helpful to other developers as well.
    However, since this is a custom implementation and not fully tested, its official support is beyond our support scope.

    Regards,
    Eyup
    Telerik by Progress
    Check out the new UI for ASP.NET Core, the most complete UI suite for ASP.NET Core development on the market, with 60+ tried-and-tested widgets, based on Kendo UI.
  10. Christopher
    Christopher avatar
    24 posts
    Member since:
    Mar 2016

    Posted 07 Nov in reply to Eyup Link to this post

    Hi Eyup,

    I completely understand your sentiment.
    Would Telerik prefer that i did not post these types solutions on the forums and instead posted them somewhere like stackoverflow?

    I do not want to impose on Telerik's customer service and community since i think it is awesome and love your products!

  11. Eyup
    Admin
    Eyup avatar
    3005 posts

    Posted 09 Nov Link to this post

    Hi Christopher,

    Thanks for the nice feedback. We really appreciate when customers value our efforts.

    And please don't get me wrong. It is perfectly fine that you share your own approaches with other developers as yourself, we even promote it in our forums. What I had in mind was that someone may take this solution as posted and approved by a Telerik official admin and take it for granted as a tested implementation.

    Thanks once again for your pleasant attitude and contribution. I've updated your Telerik points as a token of gratitude.

    Regards,
    Eyup
    Telerik by Progress
    Check out the new UI for ASP.NET Core, the most complete UI suite for ASP.NET Core development on the market, with 60+ tried-and-tested widgets, based on Kendo UI.
Back to Top
UI for ASP.NET MVC is VS 2017 Ready