Excel XML Line Feeds in Fields

Question

I' trying to get the following string to output to a cell in Excel using the 2003 XML capabilities "Cheese\nBread" and at the moment it ignores the line break.

If I create a file with the desired effect then I get the following output

<Cell ss:StyleID="s62"><Data ss:Type="String">Cheese&#10;Monkey</Data></Cell>

Okay, so I try doing...

text.Replace('\n',(char)10);

or text.Replace("\n"," ");

To no avail, the first does nothing useful and in the second the XmlWriter simply escapes it with an & which is really helpful.

Any ideas, maybe a setting on the .NET XmlWriter?

Answer 1

Try setting xml:space="preserve" on the Data element:

<Cell ss:StyleID="s62"><Data ss:Type="String" xml:space="preserve">Cheese
Monkey</Data></Cell>