10 Lines or Less - Export HTML Table to Excel
Nov 09, 2011
As Einstein once said: “May things be simple, but not simpler”. So, let’s export an HTML table to Excel in 10 lines or less.
First, 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>
<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.
-
-
Jonathan Saurez
Unit Testing Using AEM Mocks
Detecting Code Flaws Before They Cause Outages for Your Users
-
-
Oshyn
Transitioning to a Composable Solution in a Regulated Industry
Overcoming Migration Challenges
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.