Show column and customize excel before export

[Zoltan]

I want to show/hide some columns on Kendo Grid and customize export to the excel.

Export conditions:

• use grid filter selection
• use grid seletected items
• show/hide columns programaticaly

Is it possible to combine?

Is posible to export programaticaly only 5000 rows?

For export I use:

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

Customize excel code:

var sheet = e.workbook.sheets[0];
   for (var rowIndex = 0; rowIndex < sheet.rows.length; rowIndex++) {
       var row = sheet.rows[rowIndex];
       if (rowIndex == 0) {
           for (var cellIndex = 0; cellIndex < row.cells.length; cellIndex++) {
               row.cells[cellIndex].background = "#0070C0";
               row.cells[cellIndex].color = "#FFFFFF";
               row.cells[cellIndex].bold = true;
           }
       }

       if (rowIndex > 0 && rowIndex % 2 == 0) {
           for (var cellIndex = 0; cellIndex < row.cells.length; cellIndex++) {
               row.cells[cellIndex].background = "#DCE6F1";
           }
       }
   }

[Georgi Krustev]

Hello Zoltan,

In general, the grid will export the current page with the applied grouping, sorting and filtering. You can find more details about the exported stuff here:

• http://docs.telerik.com/kendo-ui/web/grid/excel-export#what-is-exported

With regards to the specific requirements:

• use grid filter selection - the widget will export only the filtered data. The generated excel file will show filter menu if filtering is enabled
• use grid seletected items - this can be accomplished using excelExport event. In this event, you can get the generated JSON that will be converted to ooxml. You can modify the generated data and show only the selected cells
• show/hide columns programaticaly - the generated data needs to be modified in the excelExport event. Please refer to the previous point.
• Is it possible to combine? - if you would like to export multiple grids, refer to this help topic
• Is posible to export programaticaly only 5000 rows? - the export will either export the current page or all data. That being said, you will need to modify the grid's data source to contain only 5000 items before the export.

For more details about the available export functionality and possible customization please refer to the corresponding help topic.

Regards,
Georgi Krustev
Telerik

 

 

[Zoltan]

Hello Georgi,

Thank for asnwering.

but from your answer I dont know how to export all columns (include hidden) from grid into formatted excel file.

[Georgi Krustev]

Hello Zoltan,

I would suggest you refer to the following thread, which discusses the reverse scenario - how to hide a column during export. The algorithm in your case will be practically the same.

http://www.telerik.com/forums/export-to-grid-hide-columns

Regards,
Georgi Krustev
Telerik

 

 

[Tim]

This approach suffers from making two Read requests per export.  Not good for performance.

[Tim]

This approach is working well for us with just one data source read request instead of two.

    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.

 

[Stefan]

Hello, Tim,

Thank for sharing the approach with the Kendo UI community, it is highly appreciated.

As for the additional requests, if the Grid has client operations, all of the data is on the client and no additional requests will be made. If the Gird has server operation, then only a request for one of the pages will be made, which should not cause a noticeable performance issue as usually when server paging is used the request is not heavy.

Regards,
Stefan
Progress Telerik

[Tim]

Good point about local data.  For server data I'm seeing two requests.  I am not going to post the specific Kendo source code here.

But the first thing the grid's saveAsExcel does is call the ExcelMixin.saveAsExcel.

This function creates a new kendo.ExcelExporter.  It then calls exporter.workbook().then(callback).  Inside the callback passed to then() is the call to the excelExport callback that can be configured with the grid.  At this point in the call stack it seems like exporter.workbook() which happens before the call to excelExport has already made a call to dataSource.fetch().  So dataSource.fetch() will be called twice, once in the first grid call to saveAsExcel(), and once inside the call to excelExport after e.preventDefault() and manipulating the grid columns from within a setTimeout.

Based on the documentation fetch() should only make a remote request the first time it is called.  I will try and debug this later and see why the data source is making a request for both the times .fetch() is called, it may be something specific to our implementation.

[Stefan]

Hello, Tim,

Thank you for the descriptive information.

If the Grid has server operations it will indeed make one additional request, as after the first one the export action will be prevented, and when it is called again a new request will be made.

Still, this will only make a request as big as the page size and should not cause any performance issues.

I will forward this to the developers' team, to check if the event is prevented, to not make any request for the data. 

Regards,
Stefan
Progress Telerik