24/7/dev&coffee

simply me

Generating Excel files from web - line breaks in cells

As many of you probably know, you can output data (a report, for example) as an Excel file, simply by adding right content-type and content-disposition header:

Response.ContentType = “application/vnd.ms-excel“;
Response.AppendHeader(“content-disposition“, “inline; filename=report.xls“);

If client has MS Excel installed, your output HTML page will be opened in it instead of web browser. Excel will interpret all formating (borders, fonts etc.) and TABLE tags, which can result a nice, formated worksheet, without using heavyweight server-side controls.

The problem I was struggling for some time was with multi-line cells. I needed to wrap text in cell, but when I put <br> tag into HTML output, Excel interpreted it as a new row, not a line-break in existing cell.

The solution I found is to add into a stylesheet:

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

Then it works like a charm. I hope somebody will find it useful :)

Tip: You can make ContentType and header conditional, providing alternate HTML/XLS reports with one file.

Posted: Jun 15 2004, 10:32 AM by michu | with 16 comment(s)
Filed under:

Comments

Ron heywood said:

You saved my life! (Well my career certainly)

I was doing a project for Vodafone, and this was really weighing on me.

Thanks for posting this and thanks to Google for helping me find it!

Regards
Ron
# July 7, 2004 5:29 PM

anonymous said:

Cool!
This is very useful. Thanks, man!
# August 3, 2004 2:51 AM

Alex Huang said:

Thanks!

# June 1, 2007 1:40 AM

Fady said:

You are great!

# June 13, 2007 5:02 AM

Charlie said:

Fantastic tip !  Thanks !!!

# July 17, 2007 12:43 AM

lou said:

THANKS!   I spent hours looking for this until i found a reference to it on www.devshed.com

# August 15, 2007 8:52 AM

Joe said:

I been looking all over the place for this. Thanks

# September 7, 2007 1:09 PM

Jack said:

GREAT! Been struggling with this for some time now. Thanks for sharing!

# November 28, 2007 8:27 AM

Heru Setiawan said:

Hello there! Thanks for posting this!

However, somehow, I could not get this to work...

The following is the code:

<style>

@page

{

mso-page-orientation: landscape;

margin:.75in .25in .75in .25in;

mso-header-margin:.5in;

mso-footer-margin:.4in;

}

td

{

mso-numberformat:general;

text-align: center;

vertical-align: middle;

}

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

</style>

<table width="100%" border="0" cellspacing="0" cellpadding="2">

 <tr>

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

   <td>123<br style="mso-data-placement:same-cell;">

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

   123</td>

 </tr>

</table>

Do I need to put anything to output it into excel?

# March 19, 2008 4:38 AM

Amin said:

Hi,

I aslo have same problem in expoeting data from html to exel but my data is comming from database ,how can i inpleted thisto stored html tags in exel sheet.

Regards'

Amin

# May 5, 2008 4:07 AM

Ian said:

Thanks for saving some of my hair. This issue annoyed me for several hours yesterday, and I was close to rewriting my program completely.

# September 24, 2008 10:33 AM

Bob McHenry said:

I have used this approach with Excel and found that it has worked well.  There seems to be a problem with this in the Office Excel 2007.  All I get is a box with a ? in it in place of the br {mso-data-placement:same-cell;  when you click on the box it preforms the function, but that is a pain for my users as there may be 1000 or more rows to click on.  It also prints the cute little boxes.  If anyone knows a solution the the new MS Excel formating please let me know.  My emai is rmchenry@charter.net

# April 3, 2009 12:17 PM

Harry said:

Bob, I get the exact same problem with a report I'm generating in Excel 2003 and 2007. When I convert a simplistic HTML table though everything works fine, which leads me to believe that the complexity of my report is causing this to happen.

# April 3, 2009 4:53 PM

midaliase said:

hi, my problem is a different of this, well in the next part you wrote:

If client has MS Excel installed, your output HTML page will be opened in it instead of web browser. Excel will interpret all formating (borders, fonts etc.)

you said that get formated cell its possible but i've tried to put color in these cells and i couldn't yet

with this:

Response.Write("<style>");

Response.Write(".xl25 { WHITE-SPACE: normal; mso-number-format: \"mmm\\ d,\\ yyyy\" }");

Response.Write(".head { BACKGROUND: #ccccff; COLOR: green}");

Response.Write("</style>");

Response.Write("</HEAD>");

but the text appears like without color.

Do you know why?

# June 10, 2009 6:44 PM

Red Cat said:

@Bob McHenry and @Harry,

If you used 'noWrap', it's possibly because of the 'noWrap'.

This will produce cute little box or '?' :

<TD noWrap>First inline row<BR style="mso-data-placement: same-cell">Second inline row</TD>

Remove the 'noWrap', it should solve the problem:

<TD>First inline row<BR style="mso-data-placement: same-cell">Second inline row</TD>

# July 27, 2009 4:06 AM

Eleon said:

That saved me a lot of time :)

# October 29, 2009 5:47 PM
Leave a Comment

(required) 

(required) 

(optional)

(required)