Excel export - line breaks in cell not appearing in spreadsheet

15 posts, 0 answers
  1. Charlie
    Charlie avatar
    5 posts
    Member since:
    Dec 2014

    Posted 28 Jun 2018 Link to this post

         This may not be specific just to the grid, but when using the Excel export functionality from a grid, with the 2018 R2 release, line breaks within cells appear not to be present in the generated spreadsheet, when this was not happening with previous releases. The line breaks are definitely present in the data as /r/n characters (I can output it to the console and they appear there).

    Has anyone else experienced anything like this?

    Regards,

    Charlie

     

  2. Alex Hajigeorgieva
    Admin
    Alex Hajigeorgieva avatar
    1082 posts

    Posted 02 Jul 2018 Link to this post

    Hi, Charlie,

    To ensure that the exported Kendo UI Grid content uses the line breaks in the text, the "cell.wrap" option should be set to true.

    This can be achieved through the excelExport event handler. The snippet below will affect the text in the second column:

    excelExport: function(e){
      var sheet = e.workbook.sheets[0];
      for (var rowIndex = 0; rowIndex < sheet.rows.length; rowIndex++) {
        var row = sheet.rows[rowIndex];        
        var cell = row.cells[1].wrap = true;
      }
    }

    Here is a runnable example for your reference:

    https://dojo.telerik.com/@bubblemaster/UxEXuheH

    Finally, I had a quick look and the last work we have done on text wrapping in the grid excel export is from 2016 r2. In case you have noticed anything unusual in the latest version, please let me know how I can reproduce it, perhaps use the Dojo above as a start.

    Kind Regards,
    Alex Hajigeorgieva
    Progress Telerik
    Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  3. Charlie
    Charlie avatar
    5 posts
    Member since:
    Dec 2014

    Posted 02 Jul 2018 in reply to Alex Hajigeorgieva Link to this post

    Hi Alex,

    Thanks for the reply. I was already using the cell wrap functionality actually and line breaks were appearing as expected until I upgraded to the Kendo 2018 R2 release.

    I've attempted to recreate the issue using this example: https://dojo.telerik.com/iMeNaVEw/2. I have taken one of the existing Kendo examples and slightly modified it by disabling the Excel filter property and adding a line break (\r\n) in the "Product Name" column title, between the two words. After this I simply applied the change and clicked the Excel Export button at the top of the grid.

    When viewing the generated spreadsheet I can see a difference depending on which version of Kendo I had chosen in the "Choose Library" drop down. If I use 2018 R1 SP1 or earlier and click on the "Product Name" cell in the spreadsheet, then only the word Product is visible in the formula bar by default, due to the presence of the line break. If I do the same thing but using Kendo 2018 R2 (or 2018 R2 SP1) then both words are visible in the formula bar as "ProductName" without the line break being present. Note that cell wrapping is not enabled here but I don't believe this affects how the content is displayed in the formula bar.

    Can you run this example and let me know if this also occurs for you? I believe this is what is causing my issue with line breaks when running Excel exports from a grid.

    Thanks.

    Charlie

  4. Alex Hajigeorgieva
    Admin
    Alex Hajigeorgieva avatar
    1082 posts

    Posted 04 Jul 2018 Link to this post

    Hello, Charlie,

    Thank you for explaining the steps to reproduce this behaviour.

    It turns out that a change was made to the source to strip potentially harmful characters which may break the XML  - the ones which meet the criteria of this regex expression:

    https://www.asciitable.com/

    /[\x00-\x1F]/g

    Here is another forum thread that you can read more about it:

    https://www.telerik.com/forums/excel-export-with-special-characters

    Nonetheless, does wrapping the cell work for you correctly? 

    Look forward to hearing back from you.

    Kind Regards,
    Alex Hajigeorgieva
    Progress Telerik
    Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  5. Charlie
    Charlie avatar
    5 posts
    Member since:
    Dec 2014

    Posted 04 Jul 2018 in reply to Alex Hajigeorgieva Link to this post

    Hi Alex,

    Thanks for the reply. It looks like this change you have made will be stripping out both newline (\x0A) and carriage return (\x0D) characters. The cell wrapping does work as expected, but does not unfortunately resolve this issue. The data contains text entered into a textarea, which often includes line breaks, and is intended to be displayed in the spreadsheet in the same format as it is within the textarea inside the grid.

    Is there any way I can allow the line breaks so that they will appear within the cells of the generated spreadsheet? Since Excel itself allows line breaks to entered into cells (using ALT+Enter in the formula bar) then I believe that including these characters would not result in a corrupted spreadsheet. Perhaps in the future the regular expression used to filter out certain characters could be adjusted to not remove newline, carriage return (and any other relevant characters) that are allowed and often used in spreadsheets.

    I have a client project that I have had to delay deploying an update on due to this change in the newest version of Kendo, so would appreciate any suggestions you may have to resolve this.

    Thanks.

    Charlie

  6. Alex Hajigeorgieva
    Admin
    Alex Hajigeorgieva avatar
    1082 posts

    Posted 05 Jul 2018 Link to this post

    Hello, Charlie,

    Thank you for getting back to us. I have discussed it with the team and reopened the issue. 

    We will go back to keeping "\r\n" in our next release. Please accept my apology for the inconvenience that this has caused.

    At present, I am not sure what you could do other than finding the stripFunnyChars function and changing the expression back to:

    /[\x00-\x08]/g

    If we have another option, I will update the thread.

    Regards,
    Alex Hajigeorgieva
    Progress Telerik
    Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  7. Charlie
    Charlie avatar
    5 posts
    Member since:
    Dec 2014

    Posted 05 Jul 2018 in reply to Alex Hajigeorgieva Link to this post

    Hi Alex,

    I am happy to put a temporary fix like this in place in the short term, but may need some assistance. In my project I am referencing kendo.all.min.js only, which obviously does not contain the function name "stripFunnyChars", but I have found one match for the string "/[\x00-\x1F]/g" within a function and replaced it with "/[\x00-\x08]/g". After reloading the project and ensuring that kendo.all.min.js was not cached in the browser from previous use I have run the export again, but the line breaks are still not appearing.

    The function, as it appears in kendo.all.min.js, after modification, is as follows:

    function p(e){return(e+"").replace(/[\x00-\x08]/g,"").replace(/\n/g,"\r\n")}

    If you can provide any further instructions to get this working I'd be grateful.

    Note: I am currently using Kendo 2018 R2 (stated as Kendo UI v2018.2.516 in kendo.all.min.js).

    Thanks.

    Charlie

  8. Charlie
    Charlie avatar
    5 posts
    Member since:
    Dec 2014

    Posted 09 Jul 2018 Link to this post

    Actually I've got this working now with the change described above to kendo.all.min.js - some other code I'd put in my custom Excel export routine was interfering and removing the line breaks so it appeared that the change had no effect. This should work nicely until a new version of Kendo is released with an official fix.

    Thanks a lot for the assistance!

    Charlie

  9. Alex Hajigeorgieva
    Admin
    Alex Hajigeorgieva avatar
    1082 posts

    Posted 10 Jul 2018 Link to this post

    Hello, Charlie,

    Thank you for the feedback. It is great to hear that you were able to restore the old behaviour in the project. 

    Apologies for the situation once again.

    Kind Regards,
    Alex Hajigeorgieva
    Progress Telerik
    Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  10. PhilS
    PhilS avatar
    1 posts
    Member since:
    Feb 2018

    Posted 23 Aug 2018 in reply to Alex Hajigeorgieva Link to this post

    Hi Alex,

    you said: 

    [...]
    We will go back to keeping "\r\n" in our next release. Please accept my apology for the inconvenience that this has caused.
    [...]   

    Is there any ticket on github which i can follow? 

     

    Regards 

    Philipp 

     

     

  11. Angel Petrov
    Admin
    Angel Petrov avatar
    1153 posts

    Posted 27 Aug 2018 Link to this post

    Hi Philipp,

    Please accept our apologies for the delayed response.

    Currently we can not provide a github issue that you can track. However we will do so as soon as possible after discussing the problem with other team members. We will get back to you in this thread with additional information.

    Regards,
    Angel Petrov
    Progress Telerik
    Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
  12. Alex Hajigeorgieva
    Admin
    Alex Hajigeorgieva avatar
    1082 posts

    Posted 29 Aug 2018 Link to this post

    Hi, Charlie,

    The issue is in our private repository as it was related to stripping other characters in the excel export that could break the exported file. While we were working on it, accidentally the carriage return and the new lines were also stripped so the new lines went missing between 2018 R2 and R3 which is upcoming in the next couple of weeks.

    Finally, as a token of appreciation for helping us improve and spot this issue, I have added some Telerik points to your account. Please accept our apology for the inconvenience that this has caused.

    Kind Regards,
    Alex Hajigeorgieva
    Progress Telerik
    Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
  13. Rod
    Rod avatar
    3 posts
    Member since:
    Nov 2013

    Posted 18 Dec 2019 in reply to Alex Hajigeorgieva Link to this post

    Hello, I wonder if there is an update on this. I've run into the same issue with the current release

    (as also described here, https://www.telerik.com/forums/including-new-lines-in-kendo-grid-excel-export)

    ooxml cell.wrap: true worked great for me in a project using v2016.1.112
    However, when I upgraded to yesterday v2019.3.1023, with no change to my code, wrap stopped working.

    Seems to be the same issue as here.  Thank you.

  14. Alex Hajigeorgieva
    Admin
    Alex Hajigeorgieva avatar
    1082 posts

    Posted 20 Dec 2019 Link to this post

    Hi, Rod,

    Thank you for pointing out the version in which the row height was calculated automatically. While this forum thread was opened for a different reason - the "\r\n" literals which were being removed from the value of the cell, the question is related to it and the behaviour is indeed different.

    I verified that the change comes from these scripts in particular:

    <script src="https://kendo.cdn.telerik.com/2016.1.112/js/kendo.ooxml.min.js"></script>
    <script src="https://kendo.cdn.telerik.com/2016.1.112/js/kendo.excel.min.js"></script>

    However, we will need more time than usual to confirm whether it is a bug due to the holiday season.

    Meanwhile you can add height to the row to remedy the situation:

    https://docs.telerik.com/kendo-ui/api/javascript/ooxml/workbook/configuration/sheets.rows.height

    Thank you for your understanding in advance.

    Kind Regards,
    Alex Hajigeorgieva
    Progress Telerik

    Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
  15. Alex Hajigeorgieva
    Admin
    Alex Hajigeorgieva avatar
    1082 posts

    Posted 27 Jul 2020 Link to this post

    Hello,

    Just a quick update - the fix was released in 2020 R1 SP 2 - 2020.1.406 version.

    https://www.telerik.com/support/whats-new/kendo-ui/release-history/kendo-ui-r1-2020-sp2

    Kind Regards,
    Alex Hajigeorgieva
    Progress Telerik

Back to Top