(Solved) – How to write new line in xml excel

  • by

I am using the below code to set cell value in XML Excel. The code is working fine for normal text but in the case of the multiline string this code is collapsing multiples lines into single line in excel cell.

Example: I am passing the value as:

"First Line
Second Line"

this code is writing "First Line Second Line" in the Excel cell.
But I want it to write multiline text as it is.

Public Const urnS As String = "urn:schemas-microsoft-com:office:spreadsheet"
Public Document As XmlDocument

Private Sub SetText(ByVal field As String, ByVal value As String)

For Each n As XmlElement In Document.GetElementsByTagName("Data")
    If n.InnerText = field Then
        If n.ParentNode.Attributes("ss:Formula") IsNot Nothing Then
            Continue For
        End If
        n.InnerText = value
        Exit For
    End If
End Sub

Solutions I tried, example :
Value = “First Line n Second Line”

n.innerText = value.Replace("n",Envirnment.Newline)

But again this is also giving result in single line as – “First Line Second Line”.

Leave a Reply

Your email address will not be published. Required fields are marked *