Export to Grid hide columns

3 Answers 4573 Views
Grid
Abradax
Top achievements
Rank 1
Abradax asked on 01 Dec 2014, 03:33 PM
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?

3 Answers, 1 is accepted

Sort by
0
Accepted
Dimo
Telerik team
answered on 03 Dec 2014, 12:12 PM
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.

Abradax
Top achievements
Rank 1
commented on 03 Dec 2014, 01:59 PM

Perfect.

That is exactly what I needed.  Thanks.
Phil
Top achievements
Rank 1
commented on 02 Apr 2015, 11:16 AM

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;
    }
});
0
Dimo
Telerik team
answered on 02 Apr 2015, 12:25 PM
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.

Brad
Top achievements
Rank 1
commented on 15 Apr 2015, 07:39 PM

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.

Dimo
Telerik team
commented on 17 Apr 2015, 12:38 PM

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.

Ali
Top achievements
Rank 1
commented on 06 Jun 2015, 11:22 AM

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

Dimo
Telerik team
commented on 08 Jun 2015, 02:17 PM

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
kishan
Top achievements
Rank 1
commented on 08 Feb 2016, 11:53 AM

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

kishan
Top achievements
Rank 1
commented on 08 Feb 2016, 11:56 AM

Is there any way to replace the visible column values with the values from hidden column while exporting grid data to excel?
0
Dimo
Telerik team
answered on 10 Feb 2016, 09:34 AM
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
Mike
Top achievements
Rank 1
commented on 03 Aug 2016, 03:43 PM

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?

Dimo
Telerik team
commented on 04 Aug 2016, 10:17 AM

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
Tim
Top achievements
Rank 1
commented on 20 Sep 2017, 09:47 PM

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.

Tim
Top achievements
Rank 1
commented on 20 Sep 2017, 09:59 PM

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.

Prakash
Top achievements
Rank 1
commented on 27 Jun 2018, 10:46 AM

Thanks. That was the only solution which worked for me.
Tags
Grid
Asked by
Abradax
Top achievements
Rank 1
Answers by
Dimo
Telerik team
Share this question
or