HTML <BR> Generates Multiple Rows in Excel

CF v5, Excel 2000. I'm generating Excel output using the Internet Explorer Excel add-in. I create an HTML table and use the <CFCONTENT TYPE="application/vnd.ms-excel"> tag. My output is mainly text, retrieved from SQL Server, with embedded carriage returns+line feeds (ascii 13+10). I use REPLACE to convert ascii 13+10 to an HTML <BR> tag: #Replace("#getSelection.Supplier_Address#","#CRLF#","<br>","ALL")#. If I view my output in straight HTML, it displays multiple lines (separated by <BR>) within one <TD> cell. When I add my CFCONTENT tag to generate Excel, it displays a separate row for each <BR>, and thus separate cells. I would like to have the carriage returns embedded in my text in a single cell, like in the HTML output. Anyone know how to do this with just straight CF and Excel; no 3rd party add-ins? I've thought of a solution with a CSV file (not tested yet) but I'm using some style properties such as colors, which would be lost if I generated a text file first.

What about only doing the translation of CRLF to BR when the user is looking at it as HTML, but leave it as CRLF when they look at it as Excel? Would that give you the line breaks within the cell?

Leaving the CRLF as is, just wraps the text as if those characters had been replaced by nothing. It seems like it's parsed by HTML first, which would ignore those characters.
I also tried <PRE></PRE> without replacing the CRLF. The result is that it creates separate rows, the same as replacing the CRLF with <BR>; but it also removes the font and the wrap on the <TD>.

If you make some dummy output by hand that has the <br> tag in it, does that also produce the extra rows? If it does, it may just be the way Excel handles the HTML.

I tried it, and as suspected, the HTML <BR> are converted to separate Excel rows. Thanks for your help.

Hi there,

can anyone help me? How do i replace a carriage return to a space. And then insert a <br> after a number of chars?

So i always have the same chars on a row?
If you can help me replace the returns to <br> i will already be happy.

thx, greetz

Just use the replace() or rereplace() functions to replace the carriage return/line feed characters with a <br>.

read last post of mine pls....

Uh, I'm not sure what you want me to tell you. Are you expecting me to write this code for you?

Before you output whatever it is you are outputting to Excel, first use the replace() or REReplace() function to replace carriage return/line feed combinations with a <br> tag. I would start by looking up these functions in the documentation.

Since I'm feeling generous, I looked this up by googling "ascii codes" and then using that information to replace the carriage return/line feed combos with <br>.

#replace( myExcelOutputVariable, '#chr(13)##chr(10)#', '<br>', 'all' )#

I suppose what I am getting at is that I'm here to help, but usually I don't just spit out code to solve people's problems. I expect others to at least try to look up the relevant functions and try to solve the problem themselves.

Youre both right but since i only am into php i only asked you for a code that look for carriage returns in excel and replaces them for <br>. When i a searching the web for hours, i didnt find it and that why i asked.

The problem in when i copy text into a excel cell, and export it then to a .sql file, the transformation is not right. When there is a carriage return in the excel file, the .sql makes a new record.

Now i have to edit the xls and BACKSPACE every line. Thats much work.
Therefor i asked. And now i still dont know how to do it?

Wait, are you saying that you are not generating an HTML table and then sending that to the user via the browser? Are you saying that ALL you have to work with is the actual Excel file itself? If so, this is the first time you stated that and it would probably have been a good thing to note when asking for help!

Since Excel files are binary files, you can't simply replace carriage return/line feed combos with <br>. You'll need to look into options that let you modify the compiled Excel file directly. You might look at Apache POI. However, in this case I won't be able to provide code for you as this will require a throrugh reading of the POI documentation.

There are other options out there but I think you'll need to assess them one at a time. If you type in "coldfusion excel" into Google that should give you a good start.

I have the original Problem and could not yet find a solution:

by writing html - Tabel to excel using "Content-Type", "application/vnd.ms-excel", all <br> are converted to new cells, e.g.

html:
<tr><td>
cell 1 line 1<br>
cell 1 line 2<br>
</td><tr>
<tr><td>
cell 2
</td></tr>

is translated to the following 3 (!) cells:

A1 -> cell 1 line 1
A2 -> cell 1 line 2
A3 -> cell 2

Any idea, how to replace the <br> into Linefeed?

Thomas

The solution I found is to add into a stylesheet:

br {mso-data-placement:same-cell;}


(thanks to google and: http://weblogs.asp.net/michu/archive/2004/06/15/155851.aspx ...)

Thomas