Kendo UI Grid - Excel Export processing with hidden columns

[David]

I'm attempting to use the Grid component's built-in support for exporting to excel, applying custom cell formatting as shown in these Telerik docs:

http://docs.telerik.com/kendo-ui/controls/data-management/grid/how-to/excel/cell-format

My question is - how can I identify the actual field / column relating to the cell being formatted from within this handler?  The approach using row / cell indexes comes with the rather obvious issue when exporting a grid with a prior hidden column displayed - best way to reproduce is to refer to this jsfiddle:

https://jsfiddle.net/3anqpnqt/1/

1. Run fiddle
2. Click export to excel - observe custom number formatting
3. Unhide subcategory column (using column menu)
4. Click export to excel - observe custom number formatting on column 2 which is now 'subcategory'

What I need to be able to do is identify the cell to format as the 'unitPrice' and apply the format, but inspection of the object model within the excelExport handler doesn't give me any way to make this link.  In my real application, I have several custom formats to apply (percentages, n0, n2 etc) so it's not as simple as going $.isNumeric(cell.value) or otherwise.

Am I missing something, or is this simply not possible?

[Radoslav]

Hello David,

You can get the column names from the first row in the sheet. For example: sheet.rows[0].cells[2].value
In your case:

excelExport: function(e) {
     …
            if (row.type === "data") {
                if (sheet.rows[0].cells[cellIndex].value == 'unitPrice') {
                    cell.format = numericFormat;
                    cell.hAlign = "right";
                }
…

On the following link you can find modified example which shows described approach:
https://jsfiddle.net/3anqpnqt/3/

I hope this helps.

Regards,
Radoslav
Telerik

 

 

[Balaji]

Hi Radoslav,

Is there a way to hide a column from the exported excel?

Thanks,

Balaji.V

[Georgi Denchev]

Hello, Balaji,

You can stop certain columns from being exported with the help of the exportable configuration.

Best Regards,

Georgi

[David]

Thanks for the reply.  The value in the header row is not the field name, it's the 'title' of the column which defaults to the field name if a title is not specified.

https://jsfiddle.net/3anqpnqt/4/

In different scenarios, such as a globalised site, the title value is volatile depending on browser language, so coding against it is a bad idea.

Also, when adding a column group, this approach of rows[0] is going to break.  The header rows are a representation of how the workbook is going to look, and column groups will add additional rows.  Mix this in to a dynamic implementation (MVC, programmatic column groupings etc) and yet more issues appear.

These are additional scenarios to what was mentioned in the original post, but should all be manageable in the control and customisation of the Excel output.  My initial thoughts on the correct solution would be to somehow emit the real 'field' name into the workbook 'cell' generated by the grid Excel generator.

If a direct, reliable solution to this problem is not possible, can you offer advice on how best to raise this as an issue and change to the framework; I believe this would be a problem common to many end users.

[Radoslav]

Hi David,

You can try using following approach:
Get all visible column in the grid and then when export the grid find if currently exported cell belongs to the grid’s column.

excelExport: function(e) {
      var sheet = e.workbook.sheets[0];
      var shownColumns = e.sender.columns.filter(function(obj){
              return obj.hidden != true;
      });
      for (var rowIndex = 0; rowIndex < sheet.rows.length; rowIndex++) {
        var row = sheet.rows[rowIndex];
        var numericFormat = "#,##0.00;[Red](#,##0.00);-";
        for (var cellIndex = 0; cellIndex < row.cells.length; cellIndex++) {
            var cell = row.cells[cellIndex];
            if (row.type === "data") {
                if (shownColumns[cellIndex].field == 'unitPrice') {
                    cell.format = numericFormat;
                    cell.hAlign = "right";
                }
            }
}
      }

This needs to be modified in the case with multicolumn headers, because the = e.sender.columns will contains only the first level. So you need to flat the hierarchical structure of columns in this case, because they are flatten in the export sheet.

Additionally you may create an entry at our UserVoice page - the community interest in the idea will influence on our decision towards implementing it.

Regards,
Radoslav
Telerik