Maintaining Line Breaks In An HTML-Excel File
Posted December 19, 2007 at 2:24 PM
Just a minor thing that I learned yesterday. As you might know, creating a pseudo Excel document is really easy using HTML. However, when you start to play around with idea, you will quickly notice that standard break tags (<br />) will create a new cell in the next row rather than creating a line break within the current cell. To get around this, all you have to do is add some Microsoft proprietary CSS to the BR tag:
Launch code in new window » Download code as text file »
- <br style="mso-data-placement:same-cell;" />
Now, just to see a quick little example in action:
Launch code in new window » Download code as text file »
- <!--- Store Excel-HTML output. --->
- <cfsavecontent variable="strData">
-
- <table border=".5pt">
- <thead>
- <tr>
- <th>
- Degree
- </th>
- <th>
- Year
- </th>
- </tr>
- </thead>
- <tbody>
- <tr valign="top">
- <td>
- Highschool Diploma
- <br style="mso-data-placement:same-cell;" />
-
- Hackley School
- </td>
- <td>
- 1998
- </td>
- </tr>
- <tr valign="top">
- <td>
- Bachelor of Science in Computer Science
- <br style="mso-data-placement:same-cell;" />
-
- Tufts University
- </td>
- <td>
- 2002
- </td>
- </tr>
- </tbody>
- </table>
-
- </cfsavecontent>
-
-
- <!--- Set header for file attachment. --->
- <cfheader
- name="content-disposition"
- value="attachment; filename=data.xls"
- />
-
- <!--- Stream the content. --->
- <cfcontent
- type="application/excel"
- variable="#ToBinary( ToBase64( strData ) )#"
- />
Running this code, we immediately get prompted to save this Excel file:
| | | | ||
| |  | | ||
| | | |
Notice that the line breaks within the first column did not create new cells. Again, a minor note but hugely useful when you want to whip together a quick little pseudo Excel file using HTML.
Download Code Snippet ZIP File
Comments (12) | Post Comment | Ask Ben | Permalink | Other Searches | Print Page
ColdFusion GetTempDirectory() Stops Working
CFQueryParam Data Conversion Caveat (Thanks Tony Petruzzi)
Reader Comments
Hi Ben, sort of off topic, but I was wondering if you can add some insight. I'm using your POIUtility to read an excel file I get from a customer, and the header takes up the top 7 rows (the column headers start at row 8). The HasHeaderRow feature only ignores the top row. Any way I can get it to ignore the top 8 and start reading at row 9?
Posted by Ken F. on Dec 19, 2007 at 3:08 PM
I clicked the wrong link! Sorry, I meant to report a typo to you, not "Ask Ben." The typo is "is really easing using HTML." < Really EASY.
Posted by Todd Rafferty on Dec 19, 2007 at 4:27 PM
@Todd,
No worries man. Thanks for the catch. It has been fixed.
Posted by Ben Nadel on Dec 20, 2007 at 8:05 AM
That's great to know :)
the problem with the pseudo html xls files is that excel chokes when the file gets too big
the way around that is then to wack your data into a database and then suck it in via odbc into excel, but use '~' or '|' as your new line character and then do a search a replace on that character and type alt-0010 and the replacement character and click replace all
long winded but it works....
Posted by zac spitzer on Dec 21, 2007 at 12:54 AM
@Zac,
Also a good solution to the problem. For small, ad-hoc reports, however, HTML is a really quick and easy solution - I just don't want to down play that fact.
Posted by Ben Nadel on Dec 21, 2007 at 7:23 AM
Ben,
I'm working on some data export tools that use use the HTML-Excel shortcut. Is there somewhere that you know of that has a good reference for these Microsoft pseudo classes?
Thanks,
Daniel Shaw
Posted by Daniel Shaw on Dec 27, 2007 at 9:42 AM
@Daniel,
I am not aware of any solid resource. I found this tip by doing Googling around for the specific problem. One way that I found to learn about the formatting (at least in Word documents) is to do a File > Save As > Web Page, and then look at the resultant page source.
Posted by Ben Nadel on Dec 28, 2007 at 8:43 AM
@Ben - Excellent suggestion. Thanks.
Posted by Daniel Shaw on Dec 29, 2007 at 10:51 AM
man oh man, the time you saved me posting this - exactly what I needed for exactly what I am working on right now, and Google found it for me right away. Great job ... thanks so much!
Posted by Michael Evangelista on Mar 9, 2008 at 3:05 AM
Great post!
I understood the HTML-Excel-Line feed problem, but I couldn't figure out the solution. I'll never get back the last 4 hours of futility, but, thanks to your post, I won't have struggle with this again.
Thanks!
Posted by mrando on Apr 9, 2008 at 12:55 PM
@Mrando,
Glad to have helped.
Posted by Ben Nadel on Apr 9, 2008 at 12:58 PM
Thanks a lot! First hit and could never have figured it out myself.
Posted by Marcel Dix on Aug 15, 2008 at 5:04 PM
