Export to Grid hide columns

16 posts, 1 answers
  1. Abradax
    Abradax avatar
    14 posts
    Member since:
    Feb 2014

    Posted 01 Dec 2014 Link to this post

    I have a column that has HTML formatted data, and excel doesn't play nice with HTML.  Since it doesn't display HTML without jumping through hoops, I just decided to hide that column when I do my export.

    I saw there was an excelExport event in the Javascript library that I could intercept and hide the column, but I don't seem to have that option in the .Events wrapper in the MVC library, is it possible to hide a column before exporting to excel since my first choice of writing the HTML probably isn't going to work?
  2. Answer
    Dimo
    Admin
    Dimo avatar
    8406 posts

    Posted 03 Dec 2014 Link to this post

    Hi Robert,

    The excelExport event is missing from the Grid's MVC wrapper in the official Q3 2014 release. You can either use the latest internal build, which resolves this, or bind the event after Grid initialization.

    http://docs.telerik.com/kendo-ui/basics/events-and-methods#bind-to-events-after-widget-initialization

    However, simply hiding the column in the handler will not achieve the desired effect, because the event is fired after the table data has been collected. You can use the following approach (be careful not to enter an endless loop, because the saveAsExcel() method fires the excelExport event):


    var exportFlag = false;
    $("#grid").data("kendoGrid").bind("excelExport", function (e) {
        if (!exportFlag) {
            e.sender.hideColumn(1);
            e.preventDefault();
            exportFlag = true;
            setTimeout(function () {
                e.sender.saveAsExcel();
            });
        } else {
            e.sender.showColumn(1);
            exportFlag = false;
        }
    });


    Regards,
    Dimo
    Telerik

    Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

  3. Abradax
    Abradax avatar
    14 posts
    Member since:
    Feb 2014

    Posted 03 Dec 2014 in reply to Dimo Link to this post

    Perfect.

    That is exactly what I needed.  Thanks.
  4. Phil
    Phil avatar
    3 posts
    Member since:
    Feb 2012

    Posted 02 Apr 2015 Link to this post

    Is there any way to do this without it affecting the visual rendering? I'm using a slight variant of the code above to display hidden columns when exporting and you briefly see the extra columns in the grid during export.

    var exportFlag = false;
    $("#grid").data("kendoGrid").bind("excelExport", function (e) {
        if (!exportFlag) {
            e.sender.showColumn(1);
            e.preventDefault();
            exportFlag = true;
            setTimeout(function () {
                e.sender.saveAsExcel();
            });
        } else {
            e.sender.hideColumn(1);
            exportFlag = false;
        }
    });
  5. Dimo
    Admin
    Dimo avatar
    8406 posts

    Posted 02 Apr 2015 Link to this post

    Hello Phil,

    You can display a loading panel over the Grid to make the column toggling less conspicuous.

    http://docs.telerik.com/kendo-ui/api/javascript/ui/ui#methods-progress

    Regards,
    Dimo
    Telerik

    See What's Next in App Development. Register for TelerikNEXT.

  6. Brad
    Brad avatar
    1 posts
    Member since:
    Nov 2014

    Posted 15 Apr 2015 in reply to Dimo Link to this post

    I tried to implement this w/ the code below...  Our site allows the users to hide and show columns, so i'm capturing the hidden status of the RequestGuid column so I don't redisplay it if it was originally hidden.  The below works fine UNLESS i have a filter on the grid, if i have a filter it loops, calling the Export over and over again for whatever reason...

     Grid is defined w/ AllPages set to true..

     Code is attached in a word doc as I could not get it to paste in a readable format in this response.

    Any ideas??

    Thanks for your time.

  7. Dimo
    Admin
    Dimo avatar
    8406 posts

    Posted 17 Apr 2015 Link to this post

    Hello Brad,

    Here is a test page, based on your code, which seems to work as expected, unless I am missing something. I applied a "greater than 20" filter to the UnitPrice column and tried with a hidden or shown UnitsOnOrder column. In both cases, exporting worked as expected.

    http://dojo.telerik.com/obUYa

    Regards,
    Dimo
    Telerik

    See What's Next in App Development. Register for TelerikNEXT.

  8. Roohul
    Roohul avatar
    6 posts
    Member since:
    Apr 2009

    Posted 06 Jun 2015 Link to this post

    Can we use this event to format the cell as well? For example, I have a column with amount, and I want to apply the format in excel export.

     

    Thank you for your help.

     

    Regards,

    Ali

  9. Dimo
    Admin
    Dimo avatar
    8406 posts

    Posted 08 Jun 2015 Link to this post

    Hello Ali,

    Please refer to the answer, which my colleague has provided in your support ticket. Do not ask the same question at more than one place, thank you.

    Regards,
    Dimo
    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
  10. kishan
    kishan avatar
    2 posts
    Member since:
    Jun 2015

    Posted 08 Feb 2016 in reply to Roohul Link to this post

    Hi Roohul,

    Did you find any solution?

    I have a similar requirement where I want to hide some columns and format the cells while exporting grid data to excel.

    Kindly advise the workaround. ThkU

  11. kishan
    kishan avatar
    2 posts
    Member since:
    Jun 2015

    Posted 08 Feb 2016 in reply to Dimo Link to this post

    Is there any way to replace the visible column values with the values from hidden column while exporting grid data to excel?
  12. Dimo
    Admin
    Dimo avatar
    8406 posts

    Posted 10 Feb 2016 Link to this post

    Hi Kishan,

    Grid Excel export works with the DataSource values, not the cells' content. From this point of view, you need to follow one of the following alternatives:

    - adjust the column visibility, as discussed in this thread

    - edit the exported cells' values manually
    http://docs.telerik.com/kendo-ui/controls/data-management/grid/excel-export#excel-customization

    Regards,
    Dimo
    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
  13. Mike
    Mike avatar
    9 posts
    Member since:
    Apr 2011

    Posted 03 Aug 2016 in reply to Dimo Link to this post

    I successfully implemented the above code but ran into an issue with the column sizes not being reset back after the export is finished. All of the columns in the grid are defined with a fixed width so I'm not sure if that is an issue. Any ideas?

  14. Dimo
    Admin
    Dimo avatar
    8406 posts

    Posted 04 Aug 2016 Link to this post

    Hello Mike,

    If all Grid columns have explicit widths, then their sum must be greater than the Grid width, so that a horizontal scrollbar appears. Otherwise you may experience unexpected column width changes when a column is resized, hidden or shown.

    http://docs.telerik.com/kendo-ui/controls/data-management/grid/appearance#widths

    Regards,
    Dimo
    Telerik by Progress
    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
  15. Tim
    Tim avatar
    6 posts
    Member since:
    Oct 2014

    Posted 20 Sep Link to this post

    I've noticed with this solution the datasource read method is called twice, once during the first call to saveAsExcel and a second time during the e.sender.saveAsExcel() inside the setTimeout.

    This is not performant at all with a complicated grid or larger data set.  I think we may be better off intercepting the button click and implementing this a different way.

  16. Tim
    Tim avatar
    6 posts
    Member since:
    Oct 2014

    Posted 20 Sep Link to this post

    This approach is working well for us with just one data source read request instead of two.  We don't use the built in kendo grid toolbar so if you are then you will need to replace its click event with your own.  Otherwise the click event can be something like

        function exportToExcel() {
           const grid = $('#grid').data('kendoGrid')';
            
            grid.showColumn("Column You Want to Show");
            grid.hideColumn("Column You Want to Hide");

            grid.saveAsExcel();
        };

    // use this function for the "exportExcel" callback in the grid configuration

        function resetGridAfterExport(e) {
            e.sender.hideColumn("Column You Want to Show");
            e.sender.showColumn("Column You Want to Hide");
        };
    // Luckily the exportExcel callback is called at a point we know the grid is done needing the columns to be visible so we can safely hide them.  Otherwise we would need some kind of "excel finished" event which there currently is none.

Back to Top