0

I want to add carriage returns to an XSLT file that generates an Excel file. My situation is that I have a really long string inside an Excel cell. I want the text to wrap around while remaining inside the cell. Any advice? I tried following the advice here but it didn't work for my situation.

Talking about the styles comment, it seems I have no control over the styles, no matter what I manually put under 'styles', When I look at the generated excel file through a text editor I always see the following:

<Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s62">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Arial" ss:Bold="1"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
 </Styles>
Community
  • 1
  • 1
Art F
  • 3,992
  • 10
  • 49
  • 81

1 Answers1

3

I have found the way to work out how to do something in Excel XML, is to get Excel to show you! In other words, open up Excel, type some text in a single cell including a carriage return (do ALT + ENTER to do the carriage return), save it as Excel XML, and open the file in notepad to see what is going on.

What you should see is that in the Styles element for the document, there should be a specific style for your cell

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

So, you will need to change your XSLT to output such a style. The ss:ID attribute can be anything, but it is the ss:WrapText attribute that is the important thing here. You could obviously combine it with other stylings, like making the cell bold.

And then, for the text in the cell, you will need to ensure the text &#10; is output literally where you want your line break to be.

<Row ss:AutoFitHeight="0" ss:Height="33">
   <Cell ss:StyleID="s62"><Data ss:Type="String">Testing&#10;Linebreak</Data></Cell>
</Row>

Notice the reference to the StyleID here. In terms of XSLT, to add the &#10; string, you would have to do it something like this

  <xsl:value-of select="substring(text(), 1, 50)" />
  <xsl:text disable-output-escaping="yes">&amp;#10;</xsl:text>
  <xsl:value-of select="substring(text(), 51, 50)" />

The 'disable-output-escaping' is to stop XSLT literally outputing a line break rather than the actual text for the escaped value.

Tim C
  • 70,053
  • 14
  • 74
  • 93
  • Hi, your answer looks good but I am still having issues. I included them in the `edit` above (too long for comment). – Art F May 22 '13 at 18:12
  • Hmmm.... I not sure why Excel would ignore your styles. Can you check you are outputting the **Styles** element in the right place? (It should be a child of the **Workbook** element.) It also needs to be in the namespace "urn:schemas-microsoft-com:office:spreadsheet". – Tim C May 23 '13 at 07:29