Kendo UI Grid - Excel Export processing with hidden columns

4 posts, 0 answers
  1. David
    David avatar
    2 posts
    Member since:
    Jul 2014

    Posted 07 Mar 2016 Link to this post

    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?

  2. Radoslav
    Admin
    Radoslav avatar
    1566 posts

    Posted 09 Mar 2016 Link to this post

    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
     
    Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
     
  3. David
    David avatar
    2 posts
    Member since:
    Jul 2014

    Posted 11 Mar 2016 in reply to Radoslav Link to this post

    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.

  4. Radoslav
    Admin
    Radoslav avatar
    1566 posts

    Posted 14 Mar 2016 Link to this post

    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
     
    Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
     
Back to Top