js导出excel单元格内换行符代码,将带有换行符的HTML字符串粘贴到单个Excel单元格中...

I'm trying to insert HTML/RTF formatted text into a single Excel cell, preserving text formatting (colour, bold, italic etc.).

The problem is that if the text contains line breaks (
), parts of the text between the breaks is inserted into separate cells.

I tried , but it seems to be ignored by Excel's parser and treated like a normal
.

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 = "ABC
EFG" & "
" & "XYZ"

objData.SetText sHTML

objData.PutInClipboard

sht.PasteSpecial Format:="Unicode Text" ', NoHTMLFormatting:=False

Application.EnableEvents = True

End Sub

解决方案

For
to work it needs to be in a

in a

Private Sub Worksheet_Change2(ByVal Target As Range, ByVal sht As Worksheet)

Const br = "
"

Application.EnableEvents = False

Dim objData As MSForms.DataObject

Set objData = New MSForms.DataObject

With CreateObject("System.Text.StringBuilder")

.Append_3 ""

.Append_3 "

.Append_3 "

"

.Append_3 "

"

.Append_3 "ABC"

.Append_3 br

.Append_3 "EFG"

.Append_3 br

.Append_3 "XYZ"

.Append_3 "

"

.Append_3 "

"

.Append_3 "

"

.Append_3 ""

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