[quoted text, click to view]


[quoted text, click to view]

You could start with creating a spreadsheet in Excel with a cell that
has multiline content and then save/export that as XML. Then look at the
source of the XML and have your .NET create exectly the same element,
attributes and content when you need a multiline cell.

There is also an office.xml group on this server where perhaps someone
is familiar with the Excel XML format.


--

Martin Honnen --- MVP XML

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

[quoted text, click to view]

Excel is normalizing the whitespace in your <Data> tag. Normalization
is the process of reducing all contiguous whitespace, linefeeds and
carriage returns into a single space.

Instead of '\n' you should put the character entity ref "&#xa;" in there
which represents a linefeed (same as pressing ALT-ENTER in Excel).

You also need to make sure that Excel knows that the cell is supposed
to contain multi-line text, otherwise it will display your linefeeds as a
control character (a skinny rectangle, usually). To get around this,
make sure that the <Style> tag identified by ss:StyleID = "26" has
an <Alignment> specifying at least:

<Alignment ss:WrapText="1"/>

This will ensure that Excel treats any cell data associated with that Style
as multiline text.


Derek Harmon

[quoted text, click to view]


[quoted text, click to view]

But that should only happen for certain attribute values, why should
line feeds in element content be normalized to a single space?

[quoted text, click to view]

Would you know any way with DOM/XmlDocument in .NET to make sure that a
text node contains a numeric character reference &#xA; when serialized
and not the character linefeed itself?
Maybe an extension of XmlTextWriter which overrides WriteString to do
some WriteRaw instead with the original argument having "\n" escaped as
"&#xA;" could achieve that. Or is there a simpler way?

--

Martin Honnen --- MVP XML