1

I'm trying to write a XSLT to produce a Spreadsheet ML to be further read by Excel and run into a problem with newlines. I need to add a newline to cell data. I made a sample in Excel to check the Excel idea of newline and got the following:

<Data>abc&#10;def</Data>

Here the &#10; reference is a newline and Excel understands it just fine when it reads the file back. I try to produce the same with XSLT, but, naturally, I get a true 0x0A (LF) character instead, so my XML looks so:

<Data>abc
def</Data

This doesn't seem to agree with Excel way to represent newlines and when I open the file in Excel I get no newline.

I tried to research the problem, but got nothing except the following short discussion which doesn't really give a solution and suggests it may be a bug in Excel. (UPDATE: As far as I understand this is what the specs call "accidents of encoding" and an XML parser must not rely on them, while Excel does.)

Is there a way to work around this somehow and get a literal &#10; in output instead of the LF character? Or maybe solve this in another way?

Tim C
  • 70,053
  • 14
  • 74
  • 93
Mikhail Edoshin
  • 2,639
  • 16
  • 25

1 Answers1

3

Yes, Excel has its own way of distinguishing between a LF character and a character reference &#10; to that character.

I think with XSLT 1.0 if you want to force a character reference then doing <xsl:text disable-output-escaping="yes"><![CDATA[&#10;]]></xsl:text> is the brute force way.

Martin Honnen
  • 160,499
  • 6
  • 90
  • 110
  • Unfortunately, this doesn't work for me: it produces the same real LF character wrapped into `![CDATA[]]`. – Mikhail Edoshin Nov 07 '12 at 14:23
  • Which XSLT processor do you use, how do you use it? Are you sure you have the right syntax `<![CDATA[ ]]>`? The `![CDATA[]]` in your comment suggests you forgot the `<` before the `!`. – Martin Honnen Nov 07 '12 at 15:38
  • Sorry, it was my bad; I was piping it through `xmllint` and it ate up the entity. Yes, it works! :) Many thanks. – Mikhail Edoshin Nov 07 '12 at 15:42