Paste A Html Table Into Excel, How To Keep The Line Break In Cell

March 31, 2024 Post a Comment

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 p

Solution 1:

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.

Solution 2:

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?

Solution 3:

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() AsStringDim BufObj As MSForms.DataObject
    Set BufObj = New MSForms.DataObject
    BufObj.GetFromClipboard
    GetClipboardText = BufObj.GetText
EndFunctionFunction SetClipboardText(ByReftextAsString)
    Dim BufObj As MSForms.DataObject
    Set BufObj = New MSForms.DataObject
    BufObj.SetText text
    BufObj.PutInClipboard
EndFunctionFunction PreProcess(ByReftextAsString, ByRef find AsString, ByRef replace AsString) AsString
    PreProcess = Application.WorksheetFunction.Substitute(text, find, replace)
EndFunctionFunction PostProcess(ByRef find AsString, ByRef replace AsString)
    Dim rCell As range
    ForEach 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)
    NextEndFunctionSub TransformingPaste1()
    Dim OrigText AsStringDim TempToken AsStringDim PasteText AsStringDim sSelAdd AsString
    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 = FalseEndSubSub TransformingPaste2()
    Dim OrigText AsStringDim TempToken AsStringDim PasteText AsStringDim sSelAdd AsString
    OrigText = GetClipboardText
    PasteText = PreProcess(OrigText, "<br>", vbCrLf)
    SetClipboardText PasteText
    ActiveSheet.Paste
    SetClipboardText OrigText
    Application.CutCopyMode = FalseEndSubSub TransformingPaste3()
    Dim OrigText AsStringDim TempToken AsStringDim PasteText AsStringDim sSelAdd AsString
    OrigText = GetClipboardText
    TempToken = "#mybr#"
    PasteText = PreProcess(OrigText, "<br>", TempToken)
    SetClipboardText PasteText
    ActiveSheet.Paste
    SetClipboardText OrigText
    PostProcess TempToken, vbLf
    Application.CutCopyMode = FalseEndSub

Solution 4:

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.

Solution 5:

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.

Html5 Developer