excelExport

[carlos]

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?

[Anton Mironov]

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:

• https://dojo.telerik.com/@bubblemaster/IdACanUQ

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

[carlos]

Hi Anton,

It works perfectly. 
Thank you so much.

Kind Regards,
Carlos Conde

[carlos]

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 

[carlos]

I attach an image

Sorry for the oversight

Kind Regards,
Carlos Conde 

[Anton Mironov]

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:

• https://docs.telerik.com/kendo-ui/knowledge-base/export-only-the-text-from-the-grid-cell-which-contains-html

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

I hope this information helps.

Greetings,
Anton Mironov
Progress Telerik

[carlos]

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)

[Anton Mironov]

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:

• https://dojo.telerik.com/UjUVENAm

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

Best Regards,
Anton Mironov
Progress Telerik

[carlos]

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

[Anton Mironov]

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:

• https://dojo.telerik.com/IhAmucOQ

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

 

Greetings,
Anton Mironov
Progress Telerik