CODEWITHSUNDEEP
c#xmlexcel
Chris Meek
Chris Meek

Reputation: 5839

Excel XML Line Feeds in Fields

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?

Upvotes: 0

Views: 2490

Answers (2)

Jerod Venema
Jerod Venema

Reputation: 44642

I had to add the following to my XML definition (within the "Workbook" element):

<Styles>
  <Style ss:ID="sMultiLine">
    <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
  </Style>
</Styles>

And then, in the "Cell" element, add the appropriate matching style:

<Cell ss:StyleID="sMultiLine">

I actually added a minor XSLT adjustment so that value only showed up if it detected newlines in the content:

<xsl:if test="contains(.,'#10;')">
      <xsl:attribute name="ss:StyleID">sMultiLine</xsl:attribute>
</xsl:if>

Upvotes: 2

Lucero
Lucero

Reputation: 60236

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

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

Upvotes: 1

Related Questions