Copy values with spaces from Handsontable to Excel

Tags: #<Tag:0x00007fce6a372590>
#1

I found some strange issue when I copy data from handsontable grid to Excel, Excel replaces white space (char 32) with non breaking space (char 160).

For example, if I copy “United States” from the grid in the demo:
https://handsontable.com/demo
The white space between word is char 32. But once I paste it into Excel, the white space becomes char 160.

Thanks!

#2

Hi @SNK

This is an interesting case. I’ll investigate it and come back to you after the weekend.

#3

Hi @SNK

I’ve found the reason for this behaviour, however, there’s not really much we can do about it. The reason is that Excel paste the copied value as text/html instead of text/plain and that is causing the change in the characters.

1 Like
#4

The reason this occurs for handsontable (and not most other apps) is that handsontable is doing a .replace(/\x20/gi, '&nbsp;') in function _dataToHTML(input).

This function creates an html representation of the data for placement on the clipboard, and is called only during onCopy and onCut (instanceToHTML(instance) is called to generate html for the browser representation of the data).

You could argue that only \xA0 should be replaced with a &nbsp;, or perhaps neither should be. I think it’s harder to argue for replacing \x20, even though it makes sense in the context of generating a browser representation of the data.

If you look at the documentation for both \x20 and \xA0, you’ll see the html representation of the former is NOT &nbsp;

I believe the correct code would be to replace line:

var parsedCellData = (0, _mixed.isEmpty)(cellData) ? '' : cellData.toString().replace(/</g, '&lt;').replace(/>/g, '&gt;').replace(/(<br(\s*|\/)>(\r\n|\n)?|\r\n|\n)/g, '<br>\r\n').replace(/\x20/gi, '&nbsp;').replace(/\t/gi, '&#9;');

with

var parsedCellData = (0, _mixed.isEmpty)(cellData) ? '' : cellData.toString().replace(/</g, '&lt;').replace(/>/g, '&gt;').replace(/(<br(\s*|\/)>(\r\n|\n)?|\r\n|\n)/g, '<br>\r\n').replace(/\t/gi, '&#9;');

Sending spaces in their original text representation (whether it be \xA0 or \x20) allows Excel and other apps to resolve the html to a regular space in its target representation. The only difference seems to be Excel turns on cell wrapping if it’s a regular space. This is acceptable, and probably desirable in most cases.

1 Like
#5

Hi @hwithington

Thank your for your detailed insight. Could you please also send it as an improvement suggestion on our GitHub?

1 Like
#6

Will do, thanks Adrian.

#7

Hi @hwithington

Do we have any updates regarding this thread?

#8

No updates. I posted the issue on the support site and await an update there.

#9

Thank you. I will then link this post to the one on Github.

#10

Hi @SNK @hwithington

I have wonderful news! We just released Handsontable v12.4.0 where the mentioned issue (https://github.com/handsontable/handsontable/issues/10017) is fixed.

Thank you for reporting.

If you’d have any other issues please feel free to open a new thread or contact us at support@handsontable.com

1 Like
#11