Carriage returns in XML Excel sheet created using XmlDocument and XmlTextWriter

[st]

Hi,

I've a routine that exports a DB query to Excel by building an
XmlDocument and saving to a XmlTextWriter. I'm having trouble with
carriage returns in a mailing address not showing up in the final Excel
sheet.

What's added to the InnerText of an XmlDocument is something similar
to:

"Name \r\nAddress1\r\nAddress2 \r\nAddress3 \r\nAddress4\r\n"

After going through the XmlTextWriter the resulting XML is:

</Data></Cell><Cell ss:StyleID="s26"><Data ss:Type="String">
Name
Address1
Address2
Address3
Address4

And what appears in the address cell of the Excel sheet is:
Name Address1 Address 2 Address 3 Address 4

I posted a similar topic yesterday and spent much of today working on
the issue without success, trying out various combinations of:
space = "preserve" in the XML
XmlDocument.PreserveWhitespace = true
XmlTextWriter.WriteAttributeString("xml", "space", null, "preserve");

Can anyone assist?

Many thanks,

Simon Lane

 

[Martin Dechev]

Hi,

An ugly solution, but it's a problem of Excel I think - try replacing in
your xml Environment.NewLine with "\n" or VbLf for C# and VB.NET
respectively.

Hope This Helps
Martin Dechev