Skip to main content
Ben Nadel at InVision In Real Life (IRL) 2019 (Phoenix, AZ) with: Joel Hill
Ben Nadel at InVision In Real Life (IRL) 2019 (Phoenix, AZ) with: Joel Hill ( @Jiggidyuo )

Maintaining Line Breaks In An HTML-Excel File

By on
Tags:

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.

Want to use code from this post? Check out the license.

Reader Comments

3 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?

218 Comments

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.

55 Comments

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

15,663 Comments

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

3 Comments

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

15,663 Comments

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

14 Comments

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!

1 Comments

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!

1 Comments

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.

1 Comments

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!

15,663 Comments

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

1 Comments

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

1 Comments

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

1 Comments

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

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel