Export HTML Table to Excel Using JavaScript / JQuery
Simple Code
|
<script type="text/javascript">
function ExportToExcel(mytblId)
{
var
htmltable= document.getElementById(mytblId);
var
html = htmltable.outerHTML;
// MS OFFICE 2003 :
data:application/vnd.ms-excel
// MS OFFICE 2007 :
application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
window.open('data:application/vnd.ms-excel,'
+ encodeURIComponent(html));
}
</script>
|
We can Customize our table data on the fly using custom Export Function
Customize function to Export Data
|
<script type="text/javascript">
function CustomMethodToExport(tblId)
{
var
html ;
var
numofRows ;
var
gTable = document.getElementById(tblId);
numofRows = gTable.rows.length-1;
var
numofCells ;
var
trhtml = "";
numofCells = gTable.rows[0].cells.length-1 ;
for (
r = 0; r <= numofRows; r++)
{
var
c =0;
var
tdhtml = ""
;
for
(c = 0; c<=numofCells; c++)
{
var
tempstr = gTable.rows[r].cells[c].innerText
;
var
color = "" ;
var
isPercent = ""
if(tempstr.search("%") > 0)
{
if(tempstr.search('-') >= 0)
{
color = "red";
}
else
{
color = "green";
}
}
else
{
color = "black";
}
tdhtml = tdhtml + "<td style='text-align:right;color:"+
color +";'>" + gTable.rows[r].cells[c].innerText.replace(" ") + "</td>";
}
trhtml = trhtml + "<tr>" + tdhtml + "</tr>";
}
html = "<table
border='1'>"+trhtml+"</table>";
// MS OFFICE 2003 :
data:application/vnd.ms-excel
// MS OFFICE 2007 :
application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
window.open('data:application/vnd.ms-excel,'
+ encodeURIComponent(html));
}
</script>
|
Export Data Using Jquery
|
$(window).load(function(){
$("#clickToExcel").click(function() {
var dtltbl = $('#mytbl').html();
window.open('data:application/vnd.ms-excel,' + $('# mytbl').html());
});
});//]]>
|
HTML Body Code
|
<body>
<form id="form1" runat="server">
<div>
<table id="mytbl" border="1">
<tr>
<td>
<b>ID</b></td>
<td>
<b>Name</b></td>
<td>
<b>Profit/Loss</b></td>
</tr>
<tr>
<td>
1</td>
<td>
Tarun</td>
<td>
200%</td>
</tr>
<tr>
<td>
2</td>
<td>
Ajay</td>
<td>
-200%</td>
</tr>
</table>
<a id="clickToExcel" href="javascript:ExportToExcel('mytbl')">Export
To Excel</a> | <a href="javascript:CustomMethodToExport('mytbl')">
Custome Export</a>
</div>
</form>
</body>
|
Note:
If you have special character problem then write down following
code before Window.open method inside script function
while (html.indexOf('á') != -1) html = html.replace('á', 'á');
while (html.indexOf('é') != -1) html = html.replace('é', 'é');
while (html.indexOf('í') != -1) html = html.replace('í', 'í');
while (html.indexOf('ó') != -1) html = html.replace('ó', 'ó');
while (html.indexOf('ú') != -1) html = html.replace('ú', 'ú');
while (html.indexOf('º') != -1) html = html.replace('º', 'º');
Use MIME
Type for MS Office 2003
application/vnd.ms-excel
Use MIME
Type for MS Office 2007 :
application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
nice one sir, keep on please.
ReplyDeleteIt was Helpful
ReplyDeleteGreat! Its very helpful.
ReplyDeleteGreat
ReplyDeleteBut while i go to save file it takes as .part extension. But when i choose open option it works good.
ReplyDeleteThis code how to set user define file name?? Please tell me??
ReplyDeleteIs this code work on IE browsers ??
ReplyDeletecan you plz tell me how to give the name of the file?
ReplyDeletenot working in IE..
ReplyDeletenot working in Chrome Also(sandbox solution).... please advise..
ReplyDeleteIs there a way to export multiple tables to multiple sheets ?
ReplyDeletenot working in IE
ReplyDeleteNot working in IE, if any one come up with 1 solution to this problem plz let me know jishnu.prathap@wipro.com
ReplyDeletehey can u tell me what the code fro IE browser
ReplyDeleteThanks from Colombia ... but please can help me to name the file you download?
ReplyDeleteHow do you specify the filename?
ReplyDeleteAlso, has anyone gotten this to work for xlsx files? when I attempt to open the generated file I get: "Excel cannot open the file because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file".
ReplyDeleteWhen I open the file contents it simply contains the generated html.
I can't export in excel 2007. It show me blank page
ReplyDeletesame with me. if anyone have its solution then please help me.
DeleteNot working in IE, if any one come up with 1 solution to this problem plz let me know cv.fayis@gmail.com
ReplyDeleteIts work for downloading excel 2003 only not for excel 2007.
ReplyDeleteSir, If I use a column like Employee ID and the value is 000521 then the downloaded Excel sheet removes the zeroes and shows 521. How to correct this?
ReplyDelete