DataGridXL2
DataGridXL2 copied to clipboard
DataGridXL
Copying selection of empty cells from Excel Online does not work
Example: copy 2x2 empty cells from Excel Online to DGXL in this Codepen demo: https://codepen.io/datagridxl/pen/mdXxXPR. It doesn't work in DGXL; it works in HoT though.
When pasting the values in the "Check Formatting" textarea, the copied string is "". It seems that HoT reads other data than the copied string. Perhaps the clipboard object holds more detailed Excel info, as they cannot possibly interpret "" as meaning 2x2 empty cells.
Yes. When we read e.clipboardData.getData("text/html") instead of plain e.clipboardData.getData("text"), we get this beauty:
<div ccp_infra_version='3' ccp_infra_timestamp='1653996547938' ccp_infra_user_hash='4113596711' ccp_infra_copy_id='113b82ae-ee2c-4000-a33a-768881496d2a' data-ccp-timestamp='1653996547938'><html><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=ProgId content=Excel.Sheet><meta name=Generator content="Microsoft Excel 15"><style>table
{mso-displayed-decimal-separator:"\,";
mso-displayed-thousand-separator:"\.";}
tr
{mso-height-source:auto;}
col
{mso-width-source:auto;}
td
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:black;
font-size:11.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Calibri, sans-serif;
mso-font-charset:0;
text-align:general;
vertical-align:bottom;
border:none;
white-space:nowrap;
mso-rotate:0;}
</style></head><body link="#0563C1" vlink="#954F72"><table width=256 style='border-collapse:collapse;width:192pt'><!--StartFragment--><col width=64 style='width:48pt' span=4><tr height=20 style='height:15.0pt'><td width=64 height=20 style='width:48pt;height:15.0pt'></td><td width=64 style='width:48pt'></td><td width=64 style='width:48pt'></td><td width=64 style='width:48pt'></td></tr><tr height=20 style='height:15.0pt'><td height=20 style='height:15.0pt'></td><td></td><td></td><td></td></tr><tr height=20 style='height:15.0pt'><td height=20 style='height:15.0pt'></td><td></td><td></td><td></td></tr><tr height=20 style='height:15.0pt'><td height=20 style='height:15.0pt'></td><td></td><td></td><td></td></tr><!--EndFragment--></table></body></html></div>
(index):7050 PASTE ACTION! <div ccp_infra_version='3' ccp_infra_timestamp='1653996823020' ccp_infra_user_hash='4113596711' ccp_infra_copy_id='981a66c7-c074-43a3-b052-3034ec8aca14' data-ccp-timestamp='1653996823020'><html><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=ProgId content=Excel.Sheet><meta name=Generator content="Microsoft Excel 15"><style>table
{mso-displayed-decimal-separator:"\,";
mso-displayed-thousand-separator:"\.";}
tr
{mso-height-source:auto;}
col
{mso-width-source:auto;}
td
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:black;
font-size:11.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Calibri, sans-serif;
mso-font-charset:0;
text-align:general;
vertical-align:bottom;
border:none;
white-space:nowrap;
mso-rotate:0;}
</style></head><body link="#0563C1" vlink="#954F72"><table width=128 style='border-collapse:collapse;width:96pt'><!--StartFragment--><col width=64 style='width:48pt' span=2><tr height=20 style='height:15.0pt'><td width=64 height=20 style='width:48pt;height:15.0pt'>dsdasd</td><td width=64 style='width:48pt'></td></tr><tr height=20 style='height:15.0pt'><td height=20 style='height:15.0pt'></td><td></td></tr><tr height=20 style='height:15.0pt'><td height=20 style='height:15.0pt'>E</td><td>F</td></tr><!--EndFragment--></table></body></html></div>
(On Chrome, on ChomeOS)
Likely that HoT is reading that info, instead of just the empty string (""). Can we be sure that this generated HTML has the same format on all browsers/OS systems?
We will need a HTML table parser to go from...
<table width=128 style='border-collapse:collapse;width:96pt'><!--StartFragment--><col width=64 style='width:48pt' span=2><tr height=20 style='height:15.0pt'><td width=64 height=20 style='width:48pt;height:15.0pt'>dsdasd</td><td width=64 style='width:48pt'></td></tr><tr height=20 style='height:15.0pt'><td height=20 style='height:15.0pt'></td><td></td></tr><tr height=20 style='height:15.0pt'><td height=20 style='height:15.0pt'>E</td><td>F</td></tr><!--EndFragment--></table>
... to ...
[
["dsdasd", null],
[null,null],
["E", "F"]
]
Let's always use "text", but when it's empty like "", check "text/html"
This might be a helpful article: https://www.mschaef.com/what_is_in_your_clipboard. Perhaps look at sheetjs or similar to see if they don't already have a nice parser for Excel clipboard data.