Paste a HTML table into Excel, how to keep the lin

2019-03-23 03:21发布

站内问答 / 移动开发
1502 4 6

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.

4条回答
趁早两清
2楼-- · 2019-03-23 03:55

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.

查看更多
0人赞 添加讨论(0) 举报
聊天终结者
3楼-- · 2019-03-23 03:56

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
查看更多
0人赞 添加讨论(0) 举报
一夜七次
4楼-- · 2019-03-23 04:09

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.

查看更多
0人赞 添加讨论(0) 举报
闹够了就滚
5楼-- · 2019-03-23 04:11

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?

查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(6)