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

Cheryl said:

This was great!  Thanks.  I am just using a regular HTML page but wanted users to be able to copy the report into Excel if they wanted.  Using <BR style="mso-data-placement: same-cell"> for the line breaks works great when the table is copied into Excel.  No other code required.

# December 29, 2009 4:01 PM

Neil said:

Thanks for the post.  This is really helpful!

# January 7, 2010 6:04 PM

Arne said:

Thanks, just what I needed

# May 26, 2010 6:41 AM

KDubs said:

+=1 on all the thanks for this post!

# October 7, 2010 10:41 AM

Mike said:

Saved me some agony and stupid explanations to the client - Thanks!

# March 4, 2011 12:19 PM

Rdagger said:

Brilliant.  Thanks.

# March 9, 2011 3:45 PM

yogesh said:

i am still not able to wrap the excel cell,i have taken gridview.caption at run time need to wrap the caption in a single row. my code is given below please suggest wt should i do..?

Response.ClearHeaders();

       Response.Clear();

       Response.Charset = "";

       Response.ContentType = "application/vnd.ms-excel";

       Response.AddHeader("Content-Disposition", "attachment;filename=Appointments By Location And Practitioner.xls");

       GridView1.Caption = "Appointments By Location And Practitioner " + "br{mso-data-placement:same-cell} Run Date: " + string.Format("{0:MM/dd/yy}", DateTime.Now) + " Report Duration: " + lblStartDate.Text + " to " + lblEndDate.Text;

      // string strHeader = "Run Date: " + string.Format("{0:MM/dd/yy}", DateTime.Now) + "<br/>Report Duration: " + lblStartDate.Text + " to " + lblEndDate.Text;  

       StringWriter strWriter = new StringWriter();

       HtmlTextWriter hwriter = new HtmlTextWriter(strWriter);

      // hwriter.WriteLine(strHeader);      

       GridView1.RenderControl(hwriter);

       Response.Write(strWriter.ToString());      

       Response.End();

# April 13, 2011 6:58 AM

yogeshjadoun said:

i am still not able to wrap the excel cell,i have taken gridview.caption at run time need to wrap the caption in a single row. my code is given below please suggest wt should i do..?

Response.ClearHeaders();

       Response.Clear();

       Response.Charset = "";

       Response.ContentType = "application/vnd.ms-excel";

       Response.AddHeader("Content-Disposition", "attachment;filename=Appointments By Location And Practitioner.xls");

       GridView1.Caption = "Appointments By Location And Practitioner " + "br{mso-data-placement:same-cell} Run Date: " + string.Format("{0:MM/dd/yy}", DateTime.Now) + " Report Duration: " + lblStartDate.Text + " to " + lblEndDate.Text;

      // string strHeader = "Run Date: " + string.Format("{0:MM/dd/yy}", DateTime.Now) + "<br/>Report Duration: " + lblStartDate.Text + " to " + lblEndDate.Text;  

       StringWriter strWriter = new StringWriter();

       HtmlTextWriter hwriter = new HtmlTextWriter(strWriter);

      // hwriter.WriteLine(strHeader);      

       GridView1.RenderControl(hwriter);

       Response.Write(strWriter.ToString());      

       Response.End();

# April 13, 2011 7:04 AM

Adatok export??l??sa csv f??jlba | nova blog said:

Pingback from  Adatok export??l??sa csv f??jlba | nova blog

# July 24, 2011 4:35 AM

md_nayeem said:

Really very helpfull yaar :)

# August 8, 2011 11:40 PM

k said:

How can you do this in libre/open office?

# August 17, 2011 10:10 AM

Rick said:

This post was quite helpful. Thanks.

To add something back, this worked for me:

sHTML = Replace(sHTML, "<html><body>", "<html><head><style>br { mso-data-placement:same-cell; }</style></head><body><table><tr valign=""top""><td>")

sHTML = Replace(sHTML, "</body></html>", "</td></tr></table></body></html>")

# December 15, 2011 11:08 AM

Ausitn said:

I'm struggling with this.  I have a local html file that when I try to copy and paste or import into Excel it is creating new lines for all line the linebreaks within each table cell.  I've tried adding the stylesheet information and re-importing or even copy and pasting into excel but the line breaks persist.  Is there anyway to do this without all the content header stuff?

# January 31, 2012 12:24 PM
Leave a Comment

(required) 

(required) 

(optional)

(required)