将带有换行符的HTML字符串粘贴到单个Excel单元格中 [英] Pasting HTML string with line breaks into a single Excel cell
问题描述
我正在尝试将HTML/RTF格式的文本插入单个Excel单元格，并保留文本格式(颜色，粗体，斜体等).
I'm trying to insert HTML/RTF formatted text into a single Excel cell, preserving text formatting (colour, bold, italic etc.).
问题在于，如果文本包含换行符(<br>)，则在换行符之间的部分文本将插入到单独的单元格中.
The problem is that if the text contains line breaks (<br>), parts of the text between the breaks is inserted into separate cells.
我尝试了<b style="mso-data-placement:same-cell;" />，但是它似乎被Excel的解析器忽略，并被视为普通的<br>.
I tried <b style="mso-data-placement:same-cell;" />, but it seems to be ignored by Excel's parser and treated like a normal <br>.
论坛上的许多主题都说，mso-data-placement:same-cell可以让您在单元格内有新的换行符，但是在我的情况下，这种风格被忽略了.
A lot of topics on forums say that mso-data-placement:same-cell lets you have new line breaks inside a cell, but in my case this style is ignored.
将文本粘贴到单个单元格中时，如何实现文本格式和换行符?
How do I achieve text formatting and line breaks when pasting text into a single cell?
我的代码(简化了一点):
My code (simplified a bit):
Private Sub Worksheet_Change2(ByVal Target As Range, ByVal sht As Worksheet)
Dim objData As MSForms.DataObject
Dim sHTML As String
Application.EnableEvents = False
Set objData = New MSForms.DataObject
sHTML = "<html>ABC<br>EFG" & "<br style=" & Chr(34) & "mso-data-placement:same-cell;" & Chr(34) & " />" & "XYZ</html>"
objData.SetText sHTML
objData.PutInClipboard
sht.PasteSpecial Format:="Unicode Text" ', NoHTMLFormatting:=False
Application.EnableEvents = True
End Sub
推荐答案
要使<br style="mso-data-placement:same-cell;"/>工作，它必须位于<TABLE>中的<TD>中.
For <br style="mso-data-placement:same-cell;"/> to work it needs to be in a <TD> in a <TABLE>.
Private Sub Worksheet_Change2(ByVal Target As Range, ByVal sht As Worksheet)
Const br = "<br style=""mso-data-placement:same-cell;""/>"
Application.EnableEvents = False
Dim objData As MSForms.DataObject
Set objData = New MSForms.DataObject
With CreateObject("System.Text.StringBuilder")
.Append_3 "<HTML>"
.Append_3 "<TABLE>"
.Append_3 "<TR>"
.Append_3 "<TD>"
.Append_3 "ABC"
.Append_3 br
.Append_3 "EFG"
.Append_3 br
.Append_3 "XYZ"
.Append_3 "</TD>"
.Append_3 "</TR>"
.Append_3 "</TABLE>"
.Append_3 "</HTML>"
objData.SetText .ToString
objData.PutInClipboard
End With
sht.Paste
Application.EnableEvents = True
End Sub
Note: It might be helpful to use the TagBuilder class that I have in my CodeReview post Creating HTML using a Builder Pattern
这篇关于将带有换行符的HTML字符串粘贴到单个Excel单元格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
