10 Lines or Less - Export HTML Table to Excel

Nov 09, 2011
Christian Herrera

As Einstein once said: “May things be simple, but not simpler”. So, let’s export a html table to Excel in 10 lines or less.


First of all, this is useful if you can’t set the content type of the html response to “application/vnd.ms-excel”, which is the single line solution.


Now let’s wade bravely into the center of the problem:


<script Language="javascript">
function TableToExcel()
{
var strCopy = document.getElementById("MyTable").innerHTML;
window.clipboardData.setData("Text", strCopy);
var objExcel = new ActiveXObject ("Excel.Application");
objExcel.visible = true;

var objWorkbook = objExcel.Workbooks.Add;
var objWorksheet = objWorkbook.Worksheets(1);
objWorksheet.Paste;
}
</script>



So… 10 lines without counting the curly brackets. Now, the explanation:


The trick is to copy the table to clipboard and then paste it inside an Excel Book created using the ActiveX installed with MS Excel. The html looks like this:


<HTML>
<HEAD>
<title>Export table to Excel in 10 lines</title>
</HEAD>
<BODY>
<a href="javascript:RunScript()">Export to Excel</a><br/>
<span id=” MyTable”>
<table>
<tr><td style=”background-color:Gray;”>10 lines</td></tr>
<tr><td>This table was exported to Excel</td></tr>
</table>
</span>
</BODY>
</HTML>



And the important thing to notice here is the inline style for the table cell. This gets exported to Excel too as a background cell color. Cool, ain’t it?


So all inline styles are sent to excel too, this doesn’t happen with header style elements or external style sheets, so be careful.


One last thing to keep in mind is that this works only in IE. Code can be easily ported to the VBscript flavor. And if you have any problem, probably you should enable the “Initialize and script ActiveX controls not marked as safe” in the security options of IE.


Have fun!