Generating Excel XML with embedded char(10)s

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?

0
1/30/2009 5:34:14 PM
excel 39879 articles. 2 followers. Follow

1 Replies
618 Views

Similar Articles

[PageSpeed] 0

On Jan 30, 12:34=A0pm, Harvey Schmidlapp <henryhart...@westat.com>
wrote:
> 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).

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 -- &#10; -- everywhere I put the Alt-010 character. So, back
to my script and insert the string &#10; 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.
0
2/13/2009 5:26:11 PM
Reply:

Similar Artilces:

Re: Extracting Exchange User Data From AD 02-24-10
If you do not like scripting, you can try GAL Exporter or Fast User Manager & Reports from IMIBO - http://www.imibo.com > > "Ringholz, Blake" <bringholz@nospam.com> wrote in message > news:76AEFC2F-85A7-4666-8262-27FB0737D09A@microsoft.com... >> Hello All - >> >> I need to get an Excel Spreadsheet that lists everyone first name, last >> name, email address, job title, etc pulled from Active Directory. Is >> there an easy way to do this? >> >> Thanks, >> Blake > > > > > ...

Printing problem from Excel 2007
Greetings, I am having an issue with printing from Excel 2007. Many users when printing from Excel with get several pages of non-sensical "junk" printed out on the pages. If it actually prints what is on the page, none of the items are in the cells, but look like they are just randomly thrown about the page. I have found that this most often is related to the Calibri font somehow, because I instruct them to change the font to Arial and all prints fine. That being said, if the user converts the Excel sheet into a PDF and print, it prints perfect. This is a great workaround, but kind ...

excel charts #24
Is it possible to freeze a chart in excel and then add another chart with different column widths without having the changes affect the first chart? Hi, Move off the chart, press the Shift or Ctrl keys and select the chart. Then right-click it and choose Format Object On the Properties sheet select Don't move or size with cells -- Thanks, Shane Devenshire "Lorena" wrote: > Is it possible to freeze a chart in excel and then add another chart with > different column widths without having the changes affect the first chart? ...

GP 10 WorkFlow Error 05-27-10
Hi I am receiving an error in GP 10 WorkFlow. At the end the workflow fails with the error. "An error has occurred in the Approval Status". Would be very helpful if any lead could be provided. I googled around but no solution was specified to this error. Kindly Advise. Following is the Content of the Event Log Action: SubmitForApproval Current User LogOnName: FZE\Administrator Input parameters: <RequestObjects> <ArrayOfBusinessObjectSubmissionInformation xmlns:xsi="http:// www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www...

Excel Mail Merger Problem
When merging excel with word for..my currrency field is dropping off zeros to the right of decimal point..if field has $ 56.03, it imports correctly, but if it is $56.00, it comes in as $56, if it is $56.30, it comes in as $56.3...this process is for invoices..any advice (I've tried everything)? Debra Dalgleish has given the following reply in newsgroups http://google.com/groups?threadm=3EBB0685.5080002%40contextures.com and has also identified the following MS KB articles in such as : http://google.com/groups?threadm=3DFA8DA8.6000209%40contextures.com 304387 - WD: Date, Phone N...

How do I prevent "Help" from opening when I open Excel?
Whenever I open Excel 2003 the Help feature opens on the right. How do I pevent this? I only want Excel to open with a new sheet. -- Lee P Lee Help or TaskPane. For Help, you should be able to close it using the "x" and next time open Excel should not display. If TaskPane, go to Tools>Options>View and uncheck "Startup Task Pane" Gord Dibben Excel MVP On Mon, 12 Dec 2005 13:05:29 -0800, "Lee P" <LeeP@discussions.microsoft.com> wrote: >Whenever I open Excel 2003 the Help feature opens on the right. >How do I pevent this? I only want Exce...

Excel 2007 Need to permanently change Normal settings for gridlines
I am using Excel 2007 for the first time and find the gridlines delineating the cells are so faint as to be nearly indistinguishable. I can go into the cell formatting and modify the normal style, and it is just the way I want it. But I can't find a way to make Excel remember this and treat it as the new definition of the Normal style. I don't want to have to redefine Normal every time I open a new spreadsheet. Someone please help! Regards Leonard Priestley The changes you describe are changing the Border color and NOT gridlines. Go into Excel Options and cl...

Excel 2002
Have several spreadsheet files I use routinely. Three have recently crashed after I added another sheet. In each case the document recovery created a file missing all the color and text formats that the file contained before the crash. Not sure what other changes may have occurred. Is there something wrong with the copy of Excel on my PC? Could these three files be corrupt? Is there a procedure to "clean-up" these files? Thanks in advance for any suggestions. Mark Hi sounds like they are corrupted. I would suggest to copy the data + formats to a new, 'fresh' workbook. ...

lost data when opening excel workbooks ; text import wizard popup
When opening many of my excel files ,which all have the same modification date, I come across the text import wizard which states that my text in these files is 'delimited'. All of the files ,including a few word doc.s have had their data changed to show all " y " with two dots above the letter for as far as the eye can see. No import or export has been done with the files and no modifications were done on that date, as far as I know. Is this a corruption problem or is their some 'fix' that I am overlooking. Thanks for any ideas. ...

conversion lotus 123 files to excel -- problem
I am converting lotus123 files to excel2002. One problem is that in lotus, literals are ignored when found in a cell within a formula. Excel on the other hand is not doing this and therefore causing #value errors on all the formulas where this occurs. Is there a way to handle this in excel other than manually having to change all the formulas? ...

Message "class not registered" opening sheet with macros Excel 97
I have created a document with Macros and is password protected. I have sent it to several people who are able to use it - the one who can't is using Excel 97 SP2. They are getting error message "class not registered" - could it be the version they are using and how do I make sure they are able to use it. This could simply be a version issue if you developed on a later version and used controls from that versions object libraries. You should always use the lowest version to develop on. If this is not the case look in the VBE on the faulty machine and check tools>ref...

Excel, how do I get ALT F C to work the same as ALT F Enter C ?
The above is an example, but it aplies to any menu. Before, if I pressed say, ALT F, the drop down menu would appear and I could press, say, C and get to the submenu. Now I have to press Enter, before the C, which is a bother. Thanks. On my Windows 2000 version 5.00.2195 with Excel '97 and Excel 2003 the Alt F C still works. What version software / Excel are you using? -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21059 View this thread: http://www....

How do I display two excel pages at the same time?
I want to have two excel pages displayed on my computer at the same time, but when I open both pages it uses the same master excel and when I toggle between the two it won't open both of them up. I'm using office 2003. Thanks for the help. -- snowtime ------------------------------------------------------------------------ snowtime's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25982 View this thread: http://www.excelforum.com/showthread.php?threadid=393499 Open both and then do windows>arrange and vertical (or any of the other choices, I usuall...

How do I add a hyperlink to an individual word in an Excel cell?
I am using Excel 2000 (not by choice) and I need to add a hyperlink to an individual word within the cell, not the whole cell itself. For example in the sentence "Click here or here to go to the appropriate web page." I want the words "here" to each have a separate hyperlink. Any ideas? I can manage some VBA too if necessary. Thanks, Rosalie Hi Rosalie, You can't do that in Excel. You would have to use HTML or Word or some other means. You could fake it, the entire cell would be a link, but you could after assigning the hyperlink select another...

Trying to Get Subset of XML
I want to display a segment of an XML (see below) file in a datagrid. I want to select a specific <EventID> and get all of its children into a dataset so that I can bind a datagrid to those values. I am trying an expression as follows but I get an error "Value of type 'System.Xml.XmlNode' cannot be converted to 'System.Xml.XmlNodeList'." myNodes = xmldoc.ChildNodes("/Dataset/Events/[ShowName=Round 2]") What is the correct way to "get" that set of nodes and convert them to a dataset? ================== Portion of XML File ===============...

How do I get excel files to open automatically from directories?
When I try to open excel files from the directory or from desktop I only get a blank worksheet not the file. I have to then go through File Open to get the file I want. There must be a way to have them open automatically. On Mon, 2 Jan 2006 21:22:01 -0800, Damian <Damian@discussions.microsoft.com> wrote: >When I try to open excel files from the directory or from desktop I only get >a blank worksheet not the file. I have to then go through File Open to get >the file I want. > >There must be a way to have them open automatically. Go to Tools -> Options -> Gen...

FIX at bottom-right of Excel screen. Know what it means?
When ever I open a worksheet the bottom right corner of the screen (by the CAPS, SCRL, etc alerts) FIX is showing. Does anyone know what is causing this? It means you have fixed decimal settings checked under tools>options>edit -- Regards, Peo Sjoblom "Bobbert" <Bobbert@discussions.microsoft.com> wrote in message news:EFFD8EFF-D7A2-42D8-862E-E0B1F535A5BE@microsoft.com... > When ever I open a worksheet the bottom right corner of the screen (by the > CAPS, SCRL, etc alerts) FIX is showing. Does anyone know what is causing > this? Tools>Options&g...

How do I easily draw in Excel the Upper and Lower limits of a con.
How do I easily draw in Excel the Upper and Lower limits of a control chart? I want to draw lines for target, min value and max values. How I can do that without having to make complete data series? Judy, Jon Peltier has just updated his site to include a Run chart that you could revise to be a control chart: http://peltiertech.com/Excel/Charts/RunChtLines.html Jon also has an example of a dynamic control chart: http://www.peltiertech.com/Excel/Charts/statscharts.html#CtrlCht ---- Regards, John Mansfield http://www.pdbook.com "Judy" wrote: > How do I easily draw in ...

BP XML Exception
Using Dynamics GP v9.0- BP 3.0 As the administrator of our Business Portal, I was updating the results viewer for Sales>Customers>Document>Customer Document Detail> and had modified the query to show more information (columns etc). I clicked accept and the admins viewing is fine, HOWEVER, our users can no longer see any information in that viewer. They get the following exception: System.Xml.XmlException "Unable to update the specified query, Customer Document Detail, from the specified XML definition." Any advice? Thank you Pam, Thanks for using the newsgro...

Question Regarding Excel 2007 Formatting Corruption
Hi Folks, I am having a problem with Excel 2007 files losing all formatting (merged cells, colors, borders, and data formatting (99% turns into 0.99)) when I open a file on our office server make edits and then save the new file on the server. Each sheet usually has a mix of locked and unlocked cells and I unprotect the sheet to make edits. Also, something is fundamentally changed with the file as its size doubles or triples. If I reopen the corrupted file and redo any of the formatting and try to save it none of the new formatting is retained either. Has anyone else ever experienced a pr...

How to convert privatekey(string) to XML
Hi all, I need to convert privatekey toXML format. I read privatekey from file. Here is how: -------------------------------- Dim myStream As System.IO.StringWriter = New System.IO.StringWriter Dim testfile As String = "c:\RSAprivate.txt" Dim objStreamReader As System.IO.StreamReader objStreamReader = System.IO.File.OpenText(testfile) Dim readfile As String = objStreamReader.ReadToEnd() TextBox1.Text = readfile objStreamReader.Close() -------------------------------- and the privatekey looks like this: -----BEGIN RSA PRIV...

test103 10-09-07
test This is the body of the article On Mon, 08 Oct 2007 21:57:49 -0700, mark.com wrote: >test This is the body of the article Please use microsoft.public.test.here for testing. If you're testing software to spam data to multiple newsgroups, please turn off your computer, carry it out to the dumpster, and throw it in. John W. Vinson [MVP] *And jump in after while you're at it* Pieter "John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message news:f0lng3h2i6ij3i7e6qu2rjtgk8j90pi5de@4ax.com... > On Mon, 08 Oct 2007 21:57:49 -0700, m...

Advanced mail merge/distribution/attachment ?s
Hey - questions. 1 - I already know how to do mail merges and email distribution lists in Office, but if we don't want each client on our distribution list to see the names/addys of all everyone else on the list (we usually mail to blind cc for this) is there STILL a way to have each client's name show up in the email (preferably in the address or subject header lines, if not in the message? 2 - Does anyone know how to take an attachment and add it in to an email message/embed it for viewing a la HTML, rather than just attaching it? Any help would be appreciated, Jose 1) ...

Textbox's truncated once printed (Excel 2000)
When using text boxs in excel if the data entry is larger than the text box it does not print out everything in the text box. Is there a way to get it to print out all the information contained in the text box. For the only two solutions I can think of is 1) Make the text boxs bigger but my manager rejected the idea. 2)Cut an paste the information onto a excel spreedsheet then print it that way by using a macro but that is a last resort. Dear Robert, 1) 2) and 3) Reject your manager. If it doesn't fit, it doesn't. 4) Perhaps ... use a smaller font? 5) Refrase your text ... Fran...

Using CComboBox embedded in CToolBar
Hi, I've derived a class from CToolBar and overridden OnCreate to replace 2 placeholder buttons with 2 combo boxes. The two combo boxes are also derived from CComboBox, and populate themselves with appropriate strings. My problem is that I'm not sure of how to inform my application when the user has selected a new item in one of the combo boxes. I've added a handler for the CBN_SELCHANGE message to the CComboBox, but I don't know what to put in it. What is the best way to forward the message to the mainframe? Also, when I replace the placeholder buttons, can I reuse their ...