excelExport

10 posts, 0 answers
  1. carlos
    carlos avatar
    11 posts
    Member since:
    Aug 2020

    Posted 03 Aug 2020 Link to this post

    Hi all
    I am trying to format the date data to this format: "yyyy-MM-dd HH: mm: ss".
    The function I am using is:

    excelExport: function(e) {
        var sheet = e.workbook.sheets[0];
        var grid = e.sender;
        var fields = grid.dataSource.options.fields;
        var fieldsModels = grid.dataSource.options.schema.model.fields;
        var columns = grid.columns;
        var dateCells = [];
        
        var len = fields.length - 1; //subtract one because a column is added with a command button
        for (var i = 0; i < len; i++) {
            var currentField = fields[i].field;
            var currentModel = fieldsModels[currentField];
            
            if (currentModel.type === "date") 
                for (var j = 0; j < columns.length; j++) {
                    if (currentField === columns[j].field) {
                        dateCells.push(j);
                        break;
                    };
                };
            };
        };
        for (var rowIndex = 1; rowIndex < sheet.rows.length; rowIndex++) {
            var row = sheet.rows[rowIndex];
            for (var q = 0; q < dateCells.length; q++) {
                var cellIndex = dateCells[q];
                if(row.cells[cellIndex]){
                    row.cells[cellIndex].format = "yyyy-MM-dd HH:mm:ss";}
            };
        };
    }

    When I export without grouping, the date field in the excell has the expected format, but when I export grouped by another string type field the date field shows a format "yyyy-MM-dd".
    How could I solve this?

  2. Anton Mironov
    Admin
    Anton Mironov avatar
    168 posts

    Posted 05 Aug 2020 Link to this post

    Hi, Carlos, 

    Thank you for the provided code snippets.

    Here is a demo prepared for your needs with the full implementation(groupable excel export with date field). All cells are looped and when the cell has a value of type date, the format is applied:

    excelExport: function(e) {
              var sheet = e.workbook.sheets[0];
              for (var rowIndex = 1; rowIndex < sheet.rows.length; rowIndex++) {
                var row = sheet.rows[rowIndex];
                for (var cellIndex = 0; cellIndex < row.cells.length; cellIndex ++) {
                  if(row.cells[cellIndex].value &&row.cells[cellIndex].value instanceof Date){
                    row.cells[cellIndex].format = "yy-MM-dd hh:mm:ss"
                  }
                }
              }
            },

    Let me know if further assistance is needed.

    Kind Regards,
    Anton Mironov
    Progress Telerik

  3. carlos
    carlos avatar
    11 posts
    Member since:
    Aug 2020

    Posted 06 Aug 2020 in reply to Anton Mironov Link to this post

    Hi Anton,

    It works perfectly. 
    Thank you so much.

    Kind Regards,
    Carlos Conde

  4. carlos
    carlos avatar
    11 posts
    Member since:
    Aug 2020

    Posted 06 Aug 2020 in reply to carlos Link to this post

    Hi again Anton,

    Finally, i'm want export to the excell, the grid with an groupFooterTemplate, the template is this: 
    "<div>Max: #= kendo.toString(max, 'n0') # g</div><div>Avg: #= kendo.toString(average, 'n0') # g</div><div>Min: #= kendo.toString(min, 'n0') # g</div>"

    In the excell it's see like this: 
    <div>Max: 375 g</div><div>Avg: 374 g</div><div>Min: 373 g</div>

    How could be I solve this?
    Thank you in advanced.

    Kind Regards,
    Carlos Conde 
  5. carlos
    carlos avatar
    11 posts
    Member since:
    Aug 2020

    Posted 06 Aug 2020 Link to this post

    I attach an image

    Sorry for the oversight

    Kind Regards,
    Carlos Conde 

  6. Anton Mironov
    Admin
    Anton Mironov avatar
    168 posts

    Posted 10 Aug 2020 Link to this post

    Hello, Carlos,

    Thank you for the provided image.

    In order to export needed calculations without HTML tags try to configure the excelExport event by setting only the text value to the cells with jQuery.

    Here is an example:

    excelExport:function(e){
              var rows = e.workbook.sheets[0].rows;
    
              for (var ri = 0; ri < rows.length; ri++) {
                var row = rows[ri];
    
                for (var ci = 0; ci < row.cells.length; ci++) {
                  var cell = row.cells[ci];
                  if (cell.value && ($(cell.value).text() != "")) {
                    // Use jQuery.fn.text to remove the HTML and get only the text
                    cell.value = $(cell.value).text();
                  }
                }
              }
            },
    Furthermore, you can find the following article interesting that describes exporting the footer templates in more details:

    Full implementation of needed behavior could be found in this dojo example.

    I hope this information helps.

    Greetings,
    Anton Mironov
    Progress Telerik

  7. carlos
    carlos avatar
    11 posts
    Member since:
    Aug 2020

    Posted 11 Aug 2020 in reply to Anton Mironov Link to this post

    Hi, Anton Mironov

    Thank you for your response.

    I was seeing your dojo example, everything seemed to work good, but don't work for me when I tried in my example.
    Checking the dojo example again, I found an error.
    If instead of "John Doe" it were "John. Doe" it no longer exports the excel.

     

    Error:
    jquery-1.12.3.min.js:2 Uncaught Error: Syntax error, unrecognized expression: John. Doe
        at Function.fa.error (jquery-1.12.3.min.js:2)
        at fa.tokenize (jquery-1.12.3.min.js:2)
        at fa.select (jquery-1.12.3.min.js:2)
        at Function.fa [as find] (jquery-1.12.3.min.js:2)
        at n.fn.init.find (jquery-1.12.3.min.js:2)
        at new n.fn.init (jquery-1.12.3.min.js:2)
        at n (jquery-1.12.3.min.js:2)
        at init.excelExport (<anonymous>:11:34)
        at init.trigger (VM31915 kendo.all.min.js:25)
        at init.<anonymous> (VM31915 kendo.all.min.js:32)

  8. Anton Mironov
    Admin
    Anton Mironov avatar
    168 posts

    Posted 13 Aug 2020 Link to this post

    Hi Carlos,

    Thank you for the provided stack trace.

    The main reason for this faulty behavior is in the logic that parses the cells. For the cells with HTML elements(headers and footers), getting the content returns a regular string. The problem occurs when the script tries to get the text from a regular cell. By doing this, the "text()" function failed when trying to parse a special symbol like a dot.

    A good approach for resolving the issue is conditionally checking the current row type, before parsing its cells. For the implementation needs, it will be enough to check if the row type is "group-header" or "footer". Here is an example of the updated "excelExport" event handler:

    excelExport:function(e){
              var rows = e.workbook.sheets[0].rows;
    
              for (var ri = 0; ri < rows.length; ri++) {
                var row = rows[ri];
                if (row.type == "group-header" || row.type == "footer") {
                  for (var ci = 0; ci < row.cells.length; ci++) {
                    var cell = row.cells[ci];
                    if (cell.value && ($(cell.value).text() != "")) {
                      // Use jQuery.fn.text to remove the HTML and get only the text
                      cell.value = $(cell.value).text();
                    }
                  }
                }
              }
            }
    Find the complete implementation in the following dojo:

    Will be glad to assist you further if anything else is needed.

    Best Regards,
    Anton Mironov
    Progress Telerik

  9. carlos
    carlos avatar
    11 posts
    Member since:
    Aug 2020

    Posted 14 Aug 2020 in reply to Anton Mironov Link to this post

    Hi Anton

    Thank you so much for you response

    That code work good for level one groupings, but if you want grouping by two fields, the code of exportExcell fails for level two groupings.

    In the dojo example you can check this error.

    Error: 
    Uncaught Error: Syntax error, unrecognized expression: age: 30
        at Function.fa.error (jquery-1.12.3.min.js:2)
        at fa.tokenize (jquery-1.12.3.min.js:2)
        at fa.select (jquery-1.12.3.min.js:2)
        at Function.fa [as find] (jquery-1.12.3.min.js:2)
        at n.fn.init.find (jquery-1.12.3.min.js:2)
        at new n.fn.init (jquery-1.12.3.min.js:2)
        at n (jquery-1.12.3.min.js:2)
        at init.excelExport (<anonymous>:11:36)
        at init.trigger (VM8328 kendo.all.min.js:25)
        at init.<anonymous> (VM8328 kendo.all.min.js:32)

    I attach an image of the dojo example with level two groupings.

    Kind Regards,
    Carlos conde
  10. Anton Mironov
    Admin
    Anton Mironov avatar
    168 posts

    Posted 17 Aug 2020 Link to this post

    Hi Carlos,

    Thank you for the provided stack trace.

    Based on the provided information, I assume that the issue is caused by a missing header template for the "age" field. Try to set it for all the needed fields and run the application again. Here is an example:

    { field: "age",
               groupHeaderTemplate:'<span style="color:red;">#= value #</span>',
               footerTemplate: "<div>Min: #: min #</div><div>Max: #: max #</div>"
     }

    The complete implementation could be found in the dojo example below:

    Let me know if I could assist you with anything else.

     

    Greetings,
    Anton Mironov
    Progress Telerik

Back to Top