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

html excel copy paste
virsir picture virsir · Oct 8, 2012 · Viewed 22.5k times · Source

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

Himanshu padia picture Himanshu padia · Oct 16, 2012

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.

Related questions

Export html table data to Excel using JavaScript / JQuery is not working properly in chrome browser

I have a HTML table in velocity template. I want to export the html table data to excel using either java script or jquery, comatibale with all browser. I am using below script <script type="text/javascript"> function …

Read More
Copy/Paste from Excel to a web page

Is there a standard way or library to copy and paste from a spreasheet to a web form? When I select more than one cell from Excel I (obviously) lose the delimiter and all is pasted into one cell of …

Read More
Format HTML table cell so that Excel formats as text?

I am creating an HTML table that will be opened as a spreadsheet in Excel. What HTML tag or CSS style can I use to "tell" Excel to display the cell's contents as text?

Read More