Export to Grid hide columns

14 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
    8333 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. UI for ASP.NET MVC is VS 2017 Ready
  4. 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.
  5. 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;
        }
    });
  6. Dimo
    Admin
    Dimo avatar
    8333 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.

  7. 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.

  8. Dimo
    Admin
    Dimo avatar
    8333 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.

  9. 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

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

    Posted 08 Feb 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

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

    Posted 08 Feb 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?
  13. Dimo
    Admin
    Dimo avatar
    8333 posts

    Posted 10 Feb 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
  14. Mike
    Mike avatar
    9 posts
    Member since:
    Apr 2011

    Posted 03 Aug 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?

  15. Dimo
    Admin
    Dimo avatar
    8333 posts

    Posted 04 Aug 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
Back to Top
UI for ASP.NET MVC is VS 2017 Ready