Copy and Paste from Excel

Tags: #<Tag:0x00007f8b1dbdc9a0>
#1

The copy and paste function seems to be experiencing issues whenever I copy from an excel spreadsheet that does not have its columns width extended to the full length.

For example, if you have an excel cell with a date in it, and you adjust the column width small enough where the date turns into ##### in excel, then when you copy that cell and paste it into the handsontable grid, it pastes the #### instead of the date that is in the excel cell.

And this issue isn’t just with excel cells with dates. Any time there is an excel column that is not at the maximum width to visibly display all the characters in cells of the column, it does not paste values in the correct columns of the handsontable grid.

*Furthermore, I tried uploading screen shots but kept receiving the error that my file is too big and the maximum size is 0KB.

#2

Hey @lhs224

Can you share a recording and yout Handsontable settings?

#3

Column B is a date column. Column C is a text column. Both columns are not at full width. The dates in column B are seen as ##### and the text in column C is visibly cut off due to small width.

When copying and pasting into handsontable, column B pasted over ##### instead of dates. All the data after column C pasted over in the incorrect columns.

Picture2.jpg1500×322 55.4 KB

But, when I extend columns B and C to full length. You can now visibly see the dates in Column B and the full text in Column C.

Picture3.jpg718×256 28.9 KB

When copying and pasting, everything pasted over in the correct columns.

Picture4.jpg1500×311 56.1 KB

Here are my handsontable settings:
data: $scope.massloadGridOrders,
columns: $scope.gridColumns,
dateFormat: ‘MM/DD/YYYY’,
minSpareRows: 1,
maxRows: 50000,
rowHeights: 25,
rowHeaders: true,
colHeaders: true,
columnSorting: true,
dropdownMenu: true,
fillHandle: true,
manualColumnFreeze: true,
autoColumnSize: { useHeaders: true },
filters: true,
contextMenu: { items: { ‘cut’: {}, ‘copy’: {}, ‘paste’: { key: ‘paste’, name: ‘Paste (Use CTRL + V)’, disabled: function () { return true; } }, ‘---------’: {}, ‘row_above’: {}, ‘row_below’: {}, ‘remove_row’: {}, ‘---------’: {}, ‘freeze_column’: {}, ‘unfreeze_column’: {}, ‘---------’: {}, ‘undo’: { name: ‘Undo (CTRL + Z)’ }, ‘redo’: { name: ‘Redo (CTRL + Y)’ } } },
stretchH: ‘all’,
className: “htCenter”,
fixedRowsTop: 0,
currentRowClassName: ‘currentRow’,
currentColClassName: ‘currentCol’,
viewportRowRenderingOffset: 10,

#4

Thank you for sharing the example and the settings. Unfortunately there is not much we can do, Google Sheets also pastes the replacement chars (hashes). Even when I copy the cell from Excel and paste it in another field in Excel I get a hashed field. Excel does not allow you to get the correct format copied .

#5

Thank you Aleksandra,

So I understand that you may not be able to do anything regards to the hashes (###) for dates where the column is not in full width, but what about the other issue in regards to the text field not being in full width in column C? If all the columns are at full width except for column C, the values in column D, E, F, and G do not paste over in their respective columns. (See second screenshot). The 1’s in column G from the spreadsheet (Screen shot 1) did not get pasted in the correct column (screenshot 2).

#6

sorry but I guess that I’m missing something, crucial.

What do you copy and where do you paste it (which cell: location, type)?

#7

Capture1.JPG1864×471 109 KB

I am copying the rows from excel into handsontable.
In column D, rows 4 through 10 are blank. They have no quantity.
Since column C is not at full length, when i copied and pasted into handsontable, it moved the quantities from column E rows 4-10 into column C rows 4-10 and moved the quantities from column F rows 4-10 into column E rows 4-10

#8

Thank you for sharing @lhs224

you’re right, the data is shifted. Do you have the same Excel file and can send it? I do not see a similar issue on our Github board so we can report it there.

#9

Yes, I still have the excel file.

How do I send you an excel file?It isn’t one of the extensions that you accept for upload.

#10

You can send me a message at support@handsontable.com or use wetransfer

#11

I have sent the attachment via support@handsontable.com
Ticket #22507

#12

Thank you for sharing the document. I got it.

However when I copy/paste the data here https://handsontable.com/docs/6.2.2/demo-dropdown-menu.html it does not shift it. It has to be related to your settings.

Can you share a demo with the settings? Simplifying the code should let us spot the issue.

#13

Hmm… this is becoming stranger and stranger.

I created a jsFiddle: DEMO
It shifts here.

When I tried to use the demo link you sent from above, for me, it shifted! I have a screenshot.

Capture.JPG1091×680 79.9 KB

I am using Google Chrome. I also tried on Microsoft EDGE and Mozilla Fox and I still get the same result.

#14

GIF.gif1186×762 116 KB

Windows 10 / Chrome 71.

Do you use CTRL/CMD + V? Is that Windows?

#15

I am using Windows 10 and Chrome Version 72.0.3626.81

When I copy and paste, I am using CTRL + V

#16

Hm… I cannot replicate the issue. Can you test it on another device? I’ve tried Windows 10/ Chrome 71 and Mac Mojave with the same browser but the result is the same.

#17

I have tried on several other different computers, I even tried using chrome on Mac and Safari, yet it is still occurring…

MacSafari.png1438×829 109 KB

macChrome.png1390×850 115 KB

I am running out of ideas… sorry…

#18

I was wondering what can be the difference between our cases and maybe it’s the Excel version?

#19

I was thinking the same thing. When I created this scenario solely in Google Doc Sheets, and pasted into the JSFiddle, it worked perfectly fine.

So I tried a couple of things…

If I copy from excel into Google Doc Sheets and then from Google Doc Sheets into the JSFiddle, it does not work… but…

If I copy from excel into Google Doc Sheets and do a ‘PASTE SPECIAL VALUES ONLY’ and then copy from Google Doc Sheets into the JSFiddle, it works!

So it looks like the culprit is the excel version. The excel version I am using is MS Office 2010. :grimacing:
However, I have tried this using MS Office 2016 and the issue still occurs.

Is there a way for you guys to recreate the issue with MS Office 2010 or 2016?

#20

We have only two devices with Windows and both work with Excel for Office 365.