Paste a HTML table into Excel, how to keep the line break in cell

22

votes

I have a simple html table, for example, just one cell, but when I copy the dom node, and paste it into excel, it will be recognize as two rows, How to make Excel get the correct paste data.

 <table><tr><td>1<br>2</td><tr></table>

I tried to add css style

br {mso-data-placement:same-cell;},

But it only works in IE.

Note, copy a plain text out is not OK, i need to add color, font information on cells.

htmlexcelcopypaste

Hi, could it be exported instead of pasted ? – BrOSs

Is it OK to use a macro? Then one could preprocess the clipboard data and/or postprocess the pasted data… – mousio

22

votes

As many of you probably know, you can output data (a report, for example) as an Excel file, simply by adding right content-type and content-disposition header:

Response.ContentType = “application/vnd.ms-excel“;

Response.AppendHeader(“content-disposition“, “inline; filename=report.xls“);

If client has MS Excel installed, your output HTML page will be opened in it instead of web browser. Excel will interpret all formating (borders, fonts etc.) and TABLE tags, which can result a nice, formated worksheet, without using heavyweight server-side controls.

The problem I was struggling for some time was with multi-line cells. I needed to wrap text in cell, but when I put <br> tag into HTML output, Excel interpreted it as a new row, not a line-break in existing cell.

add into a stylesheet:

br {mso-data-placement:same-cell;}

Then it works like a charm. I hope it useful :)

Tip: You can make ContentType and header conditional, providing alternate HTML/XLS reports with one file.

3

votes

It looks like Firefox entirely ignores the mso-data-placement directive; it doesn't appear in the Firebug style panel. Perhaps that's why it won't paste as you expect?

1

votes

What about using a macro with e.g. the following code?

• TransformingPaste1(): however, this fails on my machine (still multiple cells)

gets the clipboard text
pastes a transformed version using mso-data-placement:same-cell
restores the original clipboard text

• TransformingPaste2(): pastes in single cell on my machine, keeping the formatting etc., but results in a space rather than a newline because you're still pasting HTML

gets the clipboard text
pastes a transformed version using vbCrLf
restores the original clipboard text

• TransformingPaste3(): pastes in single cell on my machine, with a newline, but loses the formatting etc. (current implementation) – see note with link!

gets the clipboard text
pastes a transformed version using a self-defined token
restores the original clipboard text
post-processes the cells, replacing the token by newlines – might be improved…

Modify to what best suits your needs, e.g. using regex to perform the replacements, but I hope this gets you on your way :]

Function GetClipboardText() As String
    Dim BufObj As MSForms.DataObject
    Set BufObj = New MSForms.DataObject
    BufObj.GetFromClipboard
    GetClipboardText = BufObj.GetText
End Function

Function SetClipboardText(ByRef text As String)
    Dim BufObj As MSForms.DataObject
    Set BufObj = New MSForms.DataObject
    BufObj.SetText text
    BufObj.PutInClipboard
End Function

Function PreProcess(ByRef text As String, ByRef find As String, ByRef replace As String) As String
    PreProcess = Application.WorksheetFunction.Substitute(text, find, replace)
End Function

Function PostProcess(ByRef find As String, ByRef replace As String)
    Dim rCell As range
    For Each rCell In Selection
        'TODO: e.g. combine with answers from http://stackoverflow.com/questions/2192730/merge-contents-of-2-excel-cells-keeping-character-format-intact-using-vba
        rCell.Formula = Application.WorksheetFunction.Substitute(rCell.Formula, find, replace)
    Next
End Function

Sub TransformingPaste1()
    Dim OrigText As String
    Dim TempToken As String
    Dim PasteText As String
    Dim sSelAdd As String
    OrigText = GetClipboardText
    PasteText = PreProcess(OrigText, "<html>", "<html><style>br{mso-data-placement:same-cell;}</style>")
    SetClipboardText PasteText
    'Selection.PasteSpecial "Unicode Text"
    ActiveSheet.Paste
    SetClipboardText OrigText
    Application.CutCopyMode = False
End Sub

Sub TransformingPaste2()
    Dim OrigText As String
    Dim TempToken As String
    Dim PasteText As String
    Dim sSelAdd As String
    OrigText = GetClipboardText
    PasteText = PreProcess(OrigText, "<br>", vbCrLf)
    SetClipboardText PasteText
    ActiveSheet.Paste
    SetClipboardText OrigText
    Application.CutCopyMode = False
End Sub

Sub TransformingPaste3()
    Dim OrigText As String
    Dim TempToken As String
    Dim PasteText As String
    Dim sSelAdd As String
    OrigText = GetClipboardText
    TempToken = "#mybr#"
    PasteText = PreProcess(OrigText, "<br>", TempToken)
    SetClipboardText PasteText
    ActiveSheet.Paste
    SetClipboardText OrigText
    PostProcess TempToken, vbLf
    Application.CutCopyMode = False
End Sub

1

votes

I usually face this issue as i have to download excel files from a site which are actually html files wrapped in table tag with xls extension Wherever br tag is in cell, a new row is created. So, i created one small vbscript which replaces br tag with comma and then i open the file in excel. It works for me well.

0

votes

If you really need to do a copy-paste, it would be better to edit the 'Cell properties' in the excel sheet before copying the data. Right-Click on the column name in the excel and select cell properties. Make the changes as applicable for your data and save. Now you can copy your data and hope that it is interpreted correctly in MS-Excel. I haven't used it for HTML content, though.

Paste a HTML table into Excel, how to keep the line break in cell

5 Answers