Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at CFUNITED 2010 (Landsdown, VA) with:

Maintaining Line Breaks In An HTML-Excel File

By Ben Nadel on
Tags: ColdFusion

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:

  • <br style="mso-data-placement:same-cell;" />

Now, just to see a quick little example in action:

  • <!--- 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:


 
 
 

 
HTML-Excel File With Line Breaks In Content  
 
 
 

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.

Tweet This Groovy post by @BenNadel - Maintaining Line Breaks In An HTML-Excel File Thanks my man — you rock the party that rocks the body!



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?

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.

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....

@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.

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

@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.

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!

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!

Neat, but there's a problem: Yes this CSS does allow line breaks in a single cell, but on the other hand excel still uses the full width of the unbroken text to calculate the column width. So it's really an incomplete fix (since the reason to use line breaks in a single cell would almost always be to conserve horizontal space)... Would love to see a solution that also resizes the column appropriately.

I'm running into the same issues as Charlottesville Media Group mentioned. Trying to figure out how to get excel to shrink the column width to fit the wrapped data. I can double click on the column divider after it's open in excel then it is shrunk the the data width. I wish Excel would auto size it this way when opening.

If you are looking for a reference for ALL that you can do... Google "Microsoft Office HTML and XML Reference" You will find a downloadable help file from Microsoft that tells you everything you can do. It is a bit overwhelming how much control you actually have! I just haven't found in that document how to "auto size" the columns the way I want!

@Charlottesville, @Silly,

Hmmm, that must have changed in new versions of Excel. If you look at my screen shot, the column only takes the width of the longest single line of text. My whole system just crashed last week and I actually switched to Mac, so I don't even have an Excel file to test with.

Battled with this for a few hours. Couldn't get it to work on my Mac. Moved to my VM and it worked great.

Thanks a lot for this. Spent 3+ hrs trying to get this to work. Using it with BIRT reporting tool and Java. Thanks!!!

Thx a lot!!! I have built some views in lotus notes to open directly in excel and was getting crazy with this "problem"! thx again!
Alex