Excel export - line breaks in cell not appearing in spreadsheet

[Charlie]

     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

 

[Alex Hajigeorgieva]

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

[Charlie]

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

[Alex Hajigeorgieva]

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

[Charlie]

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

[Alex Hajigeorgieva]

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

[Charlie]

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

[PhilS]

Hi Alex,

you said: 

[quote]

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

[/quote]

Is there any ticket on github which i can follow? 

 

Regards 

Philipp 

 

 

[Angel Petrov]

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

[Alex Hajigeorgieva]

Hello, Charlie,

Thank you for the great explanations.

I followed the exact same steps, however, instead of a project, I ran the Dojo which you have provided  using the locally hosted modified minified file and I got the same results as before - with a line break:




I just changed the expression to include the range to 08, then used HTTP-server in git bash from the folder in which I had the 2018.2.516 versioned kendo.all.min.js





I am wondering why it does not work for you. Could you doublecheck it is the correct file which is being used, perhaps change its name temporarily and ensure Kendo UI is not sourced from another place?

Kind Regards,
Alex Hajigeorgieva
Progress Telerik

[Charlie]

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

[Alex Hajigeorgieva]

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

[Alex Hajigeorgieva]

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

[Rod]

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.

[Alex Hajigeorgieva]

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

[Alex Hajigeorgieva]

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