CODEWITHSUNDEEP
htmlcssexcelgoogle-chromecopy-paste
prototype
prototype

Reputation: 7975

Copy/paste HTML table with colspan to Excel?

EDIT: On OSX, copy/paste works perfectly from Firefox, but not from Chrome or Safari

Is it possible to copy table cells with colspan to Excel that reflects the structure?

Would like an app to render tables in HTML such that a user can copy/paste it to Excel reasonably well. In particular, would like some table cells with colspan=# to copy to Excel with correct alignment.

The problem is that Excel seems to ignore colspan in copy/paste. Thus in the example below the column title "Size" appears too far to the left, over the column for "Yellow" when it should appear over the column for "small"

I don't particularly care if the Excel cell is "merged", "centered across selection" or just plain separate cells. They key thing is to have the position be semantically correct.

HTML5 okay as well as any hack

CodePen: http://codepen.io/gradualstudent/pen/MYwNpY

HTML table as rendered in browser: enter image description here

Table after copy/paste to Excel: enter image description here

Upvotes: 2

Views: 4151

Answers (1)

Axel Richter
Axel Richter

Reputation: 61985

The Clipboard handling differ in different browsers. Whether Excel can get a table from the clipboard in the correct format depends on which data have the different browsers put in the clipboard. Unfortunately the most actual operating systems comes not more with a clipboard viewer per default. So we cannot simply look there what the differences are.

But Excel can deal with HTML tables. And so if HTML source code of a HTML table is in the clipboard, then Excel can paste this properly.

Example with VBA:

Sub HTMLinClipboardTest()

 sHTML = "<table>" & _
         "<tr><td colspan=""2"" align=""center"">colspan2</td></tr>" & _
         "<tr><td rowspan=""2"" style=""vertical-align:middle;"">rowspan2</td><td>default</td></tr>" & _
         "<tr><td>default</td></tr>" & _
         "<tr><td>default</td><td>default</td></tr>" & _
         "</table>"

 Dim oDataObject As New DataObject 'needs Microsoft Forms 2.0 Object Library
 oDataObject.SetText sHTML
 oDataObject.PutInClipboard
 ActiveSheet.Range("a1").Select
 ActiveSheet.Paste

End Sub

Upvotes: 2

Related Questions