Carriage Returns

[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 Honnen]

[e-mail] wrote:

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

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
http://JavaScript.FAQTs.com/

[Derek Harmon]

<[e-mail]> wrote in message news:11**********************@g49g2000cwa.googlegr oups.com...

"Name \r\nAddress1\r\nAddress2 \r\nAddress3 \r\nAddress4\r\n" : : <Cell ss:StyleID="s26"><Data ss:Type="String">
Name
Address1 : : And what appears in the address cell of the Excel sheet is:
Name Address1 Address 2 Address 3 Address 4

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

[Martin Honnen]

Derek Harmon wrote:

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.
But that should only happen for certain attribute values, why should
line feeds in element content be normalized to 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).

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
http://JavaScript.FAQTs.com/