Paste a HTML table into Excel, how to keep the line break in cell

Question

I have a simple html table, for example, just one cell, but when I copy the dom node, and paste it into excel, it will be recognize as two rows, How to make Excel get the correct paste data.

 <table><tr><td>1<br>2</td><tr></table> 

I tried to add css style

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

But it only works in IE.

Note, copy a plain text out is not OK, i need to add color, font information on cells.

Answer 1

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.

add into a stylesheet:

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

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

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