Excel export with special characters

11 posts, 0 answers
  1. Chandra
    Chandra avatar
    1 posts
    Member since:
    Oct 2016

    Posted 28 Apr 2017 Link to this post

    Excel export did not filter out some exportable characters, specifically, hex values 0x0f and 0x10?
  2. Boyan Dimitrov
    Admin
    Boyan Dimitrov avatar
    2062 posts

    Posted 02 May 2017 Link to this post

    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.
  3. Peter
    Peter avatar
    5 posts
    Member since:
    Oct 2016

    Posted 02 May 2017 in reply to Boyan Dimitrov Link to this post

    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>

  4. Boyan Dimitrov
    Admin
    Boyan Dimitrov avatar
    2062 posts

    Posted 04 May 2017 Link to this post

    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.
  5. Peter
    Peter avatar
    5 posts
    Member since:
    Oct 2016

    Posted 04 May 2017 in reply to Boyan Dimitrov Link to this post

    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.

  6. Alex Hajigeorgieva
    Admin
    Alex Hajigeorgieva avatar
    1017 posts

    Posted 08 May 2017 Link to this post

    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 visualization (charts) and form elements.
  7. Peter
    Peter avatar
    5 posts
    Member since:
    Oct 2016

    Posted 08 May 2017 in reply to Alex Hajigeorgieva Link to this post

    Thank you very much, Alex! We'll implement this workaround.
  8. Alex Hajigeorgieva
    Admin
    Alex Hajigeorgieva avatar
    1017 posts

    Posted 10 May 2017 Link to this post

    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.
  9. Peter
    Peter avatar
    5 posts
    Member since:
    Oct 2016

    Posted 24 May 2017 in reply to Alex Hajigeorgieva Link to this post

    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, "");
        };

  10. Alex Hajigeorgieva
    Admin
    Alex Hajigeorgieva avatar
    1017 posts

    Posted 26 May 2017 Link to this post

    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 visualization (charts) and form elements.
  11. Alex Hajigeorgieva
    Admin
    Alex Hajigeorgieva avatar
    1017 posts

    Posted 29 May 2017 Link to this post

    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.
Back to Top