Generating Excel XML with embedded char(10)s

  • Thread starter Thread starter Harvey Schmidlapp
  • Start date Start date
H

Harvey Schmidlapp

I have a 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. 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="Default"><Data ss:Type="String">ABC Corp</Data></
Cell>
<Cell ss:StyleID="Default"><Data ss:Type="String">John Doe</Data></
Cell>
<Cell ss:StyleID="Default"><Data ss:Type="String">1234567</Data></
Cell>
<Cell ss:StyleID="Default"><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 turned on and 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).

The character I'm putting in is an ASCII 10 -- chr(10) in Perl, which
is what's generating the XML.

Any ideas?
 
Wrapping is turned on and 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).
Click to expand...

Following up on my own question...

I found a work-around to my problem which then led to the solution. In
the code that generates the XML file, I put a specific string in place
of the line feeds (NEWLINE works 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.
 
Back
Top