Save HTML Table to Excel using jQuery
November 7, 2008 – 2:47 pmAfter a few hours work looking into this, this turned out really easy to implement using jQuery and server side PHP programming as follows:
On the client side the following HTML code needs adding to the page (note this is older code – for newer code follow link at bottom):
<form action="/SaveToExcel.php" method="post" target="_blank"
onsubmit='$("#datatodisplay").val( $("<div>").append( $("#ReportTable").eq(0).clone() ).html() )'>
<input type="image" src="/images/icons/Floppy-48x48.png" width="12" height="12" >
<input type="hidden" id="datatodisplay" name="datatodisplay" />
</form>
Note – variables used above – ReportTable is the id of the table you want to save and datatodisplay is a hidden variable used to post the table to the server.
The jQuery commands were added to the form’s onsubmit event but could easily be in your $(function(){}); fuction
The hard part here was getting all of the <table> HTML code. The standard jQuery .html() command gets the innerHTML and was cutting off the <table> HTML code. Getting all the HTML code was accomplished using the code $(“#datatodisplay”).val( $(“<div>”).append( $(“#ReportTable”).eq(0).clone() ).html() ) which effectively gets the outerHTML of the named HTML object (in this case the table we want to save to excel).
On the server side create a file called’SaveToExcel.php’ and add the following code:
<?php
header("Content-type: application/vnd.ms-excel; name='excel'");
header("Content-Disposition: filename=export.xls");
// Fix for crappy IE bug in download.
header("Pragma: ");
header("Cache-Control: ");
?>
<html>
<head></head>
<body><?=$_REQUEST['datatodisplay']?>
</body>
</html>
Updated: April 8, 2010 – modified to add fix for crappy IE download bug.
Update: September 11, 2011 – new version of jquery save to excel javascript code at http://www.topsemtips.com/2011/09/jquery-save-to-excel-ii/
No related posts.
well, I am not expert in jQuery but I think, sure this will work.. good luck.
Can you please explain me whats #datatodisplay and #reportTable are?
I get my table displayed in
and here is how is it passed.
ajaxRequest.onreadystatechange = function(){
if(ajaxRequest.readyState == 4){
var ajaxDisplay = document.getElementById(‘display’);
ajaxDisplay.innerHTML = ajaxRequest.responseText;
}
}
Thanks in Advance.
I figured that out.
Thanks,
How do i implement formula and format, any hints will be highly appreciated.
Thanks,
Wow that worked! I was banging my head trying to find a .NET way to do this, but jQuery was much easier. Thanks John.
For some reason this isn’t working in IE for me…I keep getting a pop-up error that says:
“Microsoft Office Excel cannot access the file ‘https://…'. There are several possible reasons:
- The file name or path does not exist
- The file is being used by another program
- The workbook you are trying to save has the same name as a currently open workbook. ”
None of these seem to be the case here…also, it’s working fine in FF, any ideas?
Thanks!
It’s saying it can’t download the data from the URL provided and yet FF is working which shows the link is OK, is sending headers correctly, etc.
Have you tried on a non-secure link (http://….)? A later version of IE (IE8 RC1 is out now).
FYI: If you are doing this on an IIS box you need to modify the SavetoExcel.php file to include the PHP tag:
Should be:
Atleast that’s how I got it to work.
What about security – it’s open for XSS:
Thanks for this tutorial. Works also with jQuery 1.3.2.
Some problems might occur using this tag: <?=
There are some servers who not allow the php short tag; use this one instead: <php echo
** edited by Dylan – blog removed the tags
Thank you for this simple and very nice solution. It’s work very well!!! Thank you.
Thanks for this tutorial, but this isn’t working with office 2003 and it’s work with office 2007, Are you the suggestions for this problems ? Thank you.
How can I get the data from 2 different tables into the export.xls file?
Hi, I used ur code in a jsp. I have unicode native characters in the table data. But in the excel, the unicode data is all crap. The server itself is not passing the data as unicode. How do i got unicode data as it is. Please help me out ?
To get data from 2 different tables just use a div tag with the ReportTable id inside there before the two tables and end it after.
when i click the export button it just creates the excel file but it says its in a different format than specified by the file extension,but when i change the file format to csv it displays the unparsed html code
Do you have a working example/downloadable script? I’m running into problems with the onsubmit portion.
Wow, that works perfectly! I seriously did not expect the script to work but it exported all my data perfectly just how I wanted it to be. Thanks!!!
Amazing script, exactly what I’ve been searching for! Thank you so much for writing this – so thankful at this early hour!
hi
how can we archive the same pdf
thank u
Hey Guys,
I’ve requirement like this..
I need to get the excel version of client PC. Based on that I need to perform some more op’ns. So, can you guys please tell me how can I do this?. Using Java Script or JQuery.
Thanks,
Sunil
Lovely! Really helped me a whole lot
Now I’m looking for a way to remove the styling before the export – any ideas?
Use CSS files and CSS classes rather than inline style statements.
I have a special question for you.
The table that I want to export to excel contains form fields, then, the only thing the script does is send the first 2 lines (titles) to the excel file.
Could you send me an e-mail to explain to you in more detail my problem?
thank you very much.
The .xls file downloads successfully but doesn’t open, neither from browsers Open nor if I Save.
help
Does the file contain data? (open using notepad) – if it looks like a table then Excel is not correctly linked to XLS files which can be fixed using http://answers.yahoo.com/question/index?qid=20080511081944AAD9D3u
EDIT: saved to desktop and opened, the .xls file is completely blank.
Remove the header calls from PHP so the return is displayed back in your browser, it should roughly look like the table you tried to save. If it doesn’t use print_r($_REQUEST) and see if the data got sent to the server. No data sent shows an issue with the jquery and is probably related to the jquery not finding the table to export.
Can someone upload the working example, In my Case it not displaying the content of the table in Excel
Thanks for this tutorial, but this isn’t working with office 2003 and it’s work with office 2007, Are you the suggestions for this problems ? Thank you.
The problem may be that Office 2003 doesn’t understand HTML. I would suggest saving your data to CSV and letting Excel import that. I have a version of this code that handles that and its much more versatile, including saving to PDF. Let me look for it in the morning.
[...] This code is slightly different to previous code as it will add the export buttons to all tables on the page that do not have a class of ‘noExcel’. Run this script from your jQuery ready() function. A simpler version of this code is available at http://www.topsemtips.com/2008/11/save-html-table-to-excel-using-jquery/: [...]
Hi I need some help. I am having the same problem with Ben, .xls file is blank. When I try to open it in notepad it contains only the
following tags:
html>
<body
Sounds like your table is not being sent to the server – use the Web Developer tools for Firefox and look at the outgoing call to ensure the table is attached as a parameter.
Can someone please upload the source files… Thanks
Finally Got it to work. Seems that my jsquery library was in a different folder..
i have a problem when the html documento is too bigger because when i tried with small html it works ok. can you help me how i can solve this issue ?
thanks
This may be related to your upload_max_filesize and post_max_size in your php.ini file. I think the default is 2MB. See http://us3.php.net/manual/en/ini.core.php
This is great! Do you also have similar solution for exporting to a CSV file (i.e. w/o going thru Excel)? Thanks in advance.
The SaveToExcel program would need to be changed to something like:
<?php header("Content-type: application/vnd.ms-excel; name='excel'"); header("Content-Disposition: filename=export.csv"); // Fix for crappy IE bug in download. header("Pragma: "); header("Cache-Control: "); $CSV = $_REQUEST['datatodisplay'] ; $replacements = array("@<table[^>]*>@i" => '' , '@</tabl[^>]*>@i' => '' , // remove </table> '@<thea[^>]*>@i' => '' , // remove <thead> '@<tbod[^>]*>@i' => '' , // remove <tbody> '@</the[^>]*>@i' => '' , // remove </thead> '@</tbo[^>]*>@i' => '' , // remove </trbody> '@<tr[^>]*>@i' => '' , // remove <tr> '@</tr[^>]*>@i' => '' , // remove </tr> '@<th[^>]*>@i' => '' , // remove<th> '@</th[^>]*>@i' => '' , // remove </th> '@<td[^>]*>@i' => '' , // remove<td> '@</td[^>]*>@i' => ',' , // The comma for separating ); $CSV = preg_replace( array_keys( $replacements ), array_values( $replacements ), $CSV ) ; ?> <html> <head></head> <body><?=$CSV?> </body> </html>Let me know if it works.
It works for me. Thanks.