This is a migrated thread and some comments may be shown as answers.

Excel export with special characters

10 Answers 789 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Chandra
Top achievements
Rank 1
Chandra asked on 28 Apr 2017, 09:03 PM
Excel export did not filter out some exportable characters, specifically, hex values 0x0f and 0x10?

10 Answers, 1 is accepted

Sort by
0
Boyan Dimitrov
Telerik team
answered on 02 May 2017, 11:53 AM

Hello Chandra,

I am afraid that I am not able to replicate any issues with such values in Kendo UI Grid excel export. Please refer to the http://dojo.telerik.com/EWihO example. 

Regards,
Boyan Dimitrov
Telerik by Progress
Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
0
Peter
Top achievements
Rank 1
answered on 02 May 2017, 03:00 PM

Hi, Boyan --

   I'm working with Chandra on this issue. Your Dojo example uses a literal string of "0x0f", which is not the same as using a character with a hexadecimal value of 0x0f. Here's the same code using that character of 0x0f, which replicates the problem when exporting to Excel:

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8"/>
    <title>Kendo UI Snippet</title>

    <link rel="stylesheet" href="http://kendo.cdn.telerik.com/2017.1.118/styles/kendo.common.min.css"/>
    <link rel="stylesheet" href="http://kendo.cdn.telerik.com/2017.1.118/styles/kendo.rtl.min.css"/>
    <link rel="stylesheet" href="http://kendo.cdn.telerik.com/2017.1.118/styles/kendo.silver.min.css"/>
    <link rel="stylesheet" href="http://kendo.cdn.telerik.com/2017.1.118/styles/kendo.mobile.all.min.css"/>

    <script src="http://code.jquery.com/jquery-1.12.4.min.js"></script>
    <script src="http://kendo.cdn.telerik.com/2017.1.118/js/jszip.min.js"></script>
    <script src="http://kendo.cdn.telerik.com/2017.1.118/js/kendo.all.min.js"></script>
  </head>
  <body>

    <div id="grid"></div>
    <script>
      $("#grid").kendoGrid({
        toolbar: ["excel"],
        excel: {
          allPages: true,
          filterable: true
        },
        filterable: true,
        dataSource: {
          data:[{name: "", age: 1}, {name: "", age: 2}],
          schema: {
            model: {
              fields: {
                name: {type: "string"},
                age: {type: "number"}
              }
            }
          },
          pageSize: 10
        },
        pageable: true
      });
    </script>
  </body>
</html>

0
Boyan Dimitrov
Telerik team
answered on 04 May 2017, 11:19 AM

Hello Peter,

Thank you for the example. 

Indeed we strip some special characters because they break the XML and eventually this results in corrupted excel file. This is why we decided to strip/remove such characters during the excel export in order to avoid any issues with corrupted excel files. Please refer to the https://github.com/telerik/kendo/issues/5608 issue that gives more information about the problem. 

Regards,
Boyan Dimitrov
Telerik by Progress
Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
0
Peter
Top achievements
Rank 1
answered on 04 May 2017, 01:13 PM

Hello, Boyan. Thank you for your reply. Just two issues:

1. The link you posted gives me a 404 error

2. The problem, which you'll see if you copy and paste my code into Dojo, is that the special characters are NOT being stripped in the Excel export. If they were being stripped, there would be no problem.

0
Alex Hajigeorgieva
Telerik team
answered on 08 May 2017, 10:10 AM
Hi Peter,

Please accept our apology for the missing link. The link is in a private repository, hence the 404 error.

Nonetheless, we do strip the Excel Export of characters \x00 through to \x10. The characters provided in your example are out of the aforementioned range. I will suggest to the team that we increase the range. Meanwhile, you may add an event handler function to the  excelExport event and take care of it programmatically:

http://dojo.telerik.com/uCapUd

function stripFunnyChars(value) {
  return (value+"").replace(/[\x09-\x10]/g, "");
}
 
// grid excelExport
 
excelExport: function(e){
 var sheet = e.workbook.sheets[0];
 for (var i = 0; i < sheet.rows.length; i++) {
   for (var ci = 0; ci < sheet.rows[i].cells.length; ci++) {
    sheet.rows[i].cells[ci].value = stripFunnyChars(sheet.rows[i].cells[ci].value)
   }
  }
},

Kind Regards,
Alex Hajigeorgieva
Telerik by Progress
Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data (charts) and form elements.
0
Peter
Top achievements
Rank 1
answered on 08 May 2017, 02:57 PM
Thank you very much, Alex! We'll implement this workaround.
0
Alex Hajigeorgieva
Telerik team
answered on 10 May 2017, 02:31 PM
Hi Peter,

I will let you know if we extend the type of stripped characters as soon as we have an assignee for the issue. At this point, the stripFunnyChars function will become obsolete and you will be able to remove it.

Kind Regards,
Alex Hajigeorgieva
Telerik by Progress
Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
0
Peter
Top achievements
Rank 1
answered on 24 May 2017, 02:09 PM

Hi, Alex -- I recommend this update to stripFunnyChars. The existing one converts all values to strings, which breaks sorting by dates in the exported Excel files and led to a production issue for us.

 

    function stripFunnyChars(value) {
        return value === undefined ? undefined : value === null ? null : (typeof value).toLowerCase() !== "string" ? value : value.replace(/[\x09-\x10]/g, "");
    };

0
Alex Hajigeorgieva
Telerik team
answered on 26 May 2017, 11:02 AM
Hello Peter,

Thank you very much for the proposal. 

I have forwarded it to the lead developer of our Excel Export framework and will get back to you later on with his feedback.

Have a nice weekend!

Regards,
Alex Hajigeorgieva
Progress Telerik
Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data (charts) and form elements.
0
Alex Hajigeorgieva
Telerik team
answered on 29 May 2017, 06:07 AM
Hi Peter,

Are you able to provide me with a sample to illustrate the behaviour? The feedback I received was that it might be something we need to address separately if the function breaks the Excel export without the modification to the function. 

If we can reproduce the kendo OOXML producing invalid Excel file, then I will file a bug report and we can have it fixed.

Kind Regards,
Alex Hajigeorgieva
Progress Telerik
Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
Tags
Grid
Asked by
Chandra
Top achievements
Rank 1
Answers by
Boyan Dimitrov
Telerik team
Peter
Top achievements
Rank 1
Alex Hajigeorgieva
Telerik team
Share this question
or