Generating Excel XML with embedded char(10)s

[Harvey Schmidlapp]

I have a Perl script that generates an Excel file in XML. Everything
is working wonderfully except for one column where I want to embed
line feeds in the cells. I know that I want to include chr(10) (which
is the Perl equivalent of char(10) in Excel) for the line breaks. It
appears that the characters are there because when I view the XML file
in a text editor, the contents of those cells are on separate lines
but when I view the file in Excel, the line feed character seems to be
replaced with a single space.

So, for instance, I have:

<Row>
<Cell ss:StyleID="S1"><Data ss:Type="String">ABC Corp</Data></Cell>
<Cell ss:StyleID="S1"><Data ss:Type="String">John Doe</Data></Cell>
<Cell ss:StyleID="S1"><Data ss:Type="String">1234567</Data></Cell>
<Cell ss:StyleID="S1"><Data ss:Type="String">Line Number 1
Line Number 2
Line Number 3
</Data></Cell>
</Row>

But, when opened in Excel, the last cell in the row looks like this:

Line Number 1 Line Number 2 Line Number 3

Wrapping is not turned on for the cell. If I make the column narrow,
it wraps but not where the line feeds are (unless, of course I adjust
it very precisely -- that's not a solution, though because the text
length vary too much from row to row).

Any ideas?

 

[Harvey Schmidlapp]

I had posted this same question on microsoft.public.excel a while ago
but didn't get any response so I thought I'd try here. No response
here either. However, I found a work-around to my problem which then
led to the solution. I'm posting it in case anyone else ever happens
to run into this problem.

In the code that generates the XML file, I put a specific string in
place of the line feeds (NEWLINE worked for me). Then, I opened the
file in Excel and did a search and replace to replace all occurrences
of the word NEWLINE with a char(10) character.

Note that this didn't work for me at first. You cannot, for instance,
simply replace the string NEWLINE with the string CHAR(10). If you do
(well, duh), you get the string CHAR(10) in your text, not the LF
character.

Likewise, if you type Alt-10 in the "replace with" field, you get a
small black square with a circle in it. A search implied that this
character shows up if you don't have wrap turned on for the cell but I
DID have wrap turned on and it was still showing up. That's not it.

The solution for search and replace, it seems, is to replace NEWLINE
with Alt-010 (that is, hold down the Alt key and type zero, one, zero
on the keypad). That for some reason, worked, while Alt-10 did not.
Not sure why the leading zero is important but it is (and I'm sure
it's a good reason).

After making that substitution, I looked at the updated file in a
plain-text editor and behold, the Unicode character reference for
linefeed --
-- everywhere I put the Alt-010 character. So, back
to my script and insert the string
everywhere I want a new line
(instead of the string NEWLINE) and presto. So simple, really and
since this is XML, using Unicode character reference for linefeed
makes perfect sense.