Top SEM and SEO Tips   

Save HTML Table to Excel using jQuery

November 7, 2008 – 2:47 pm

After 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:

<form action="/SaveToExcel.php" method="post" target="_blank"
onsubmit='$("#datatodisplay").val( $("<div>").append( $("#ReportTable").eq(0).clone() ).html() )'>
<pre><input  type="image" src="/images/icons/Floppy-48x48.png" width="12" height="12"></pre>
<pre><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.

Related posts:

  1. jQuery Automatic Trim of Input Fields Really common task with a really easy solution $(function() {...
  2. HTML Meta Tags – What are they and how do you use them on your website? What are they and how do you use them on...
  3. jQuery – Add a default button to a page Normally in HTML you can add a ‘input type=”submit”‘ to...
  4. The Importance of Clean HTML Code HTML encoding errors will break search engine spiders. To understand...
  5. Issues Creating and Downloading File in IE from PHP Took a while to fix this one. The problem is...

  1. 15 Responses to “Save HTML Table to Excel using jQuery”

  2. well, I am not expert in jQuery but I think, sure this will work.. good luck.

    By Kamal on Dec 6, 2008

  3. 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.

    By John on Dec 16, 2008

  4. I figured that out.

    Thanks,

    How do i implement formula and format, any hints will be highly appreciated.

    Thanks,

    By John on Dec 16, 2008

  5. Wow that worked! I was banging my head trying to find a .NET way to do this, but jQuery was much easier. Thanks John.

    By Acrobatic on Dec 30, 2008

  6. 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!

    By Jenna on Jan 29, 2009

  7. 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).

    By Dylan on Jan 29, 2009

  8. 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.

    By Ryan on Mar 30, 2009

  9. What about security – it’s open for XSS:

    By Peter on Apr 20, 2009

  10. 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

    By Mariusz on Jul 13, 2009

  11. Thank you for this simple and very nice solution. It’s work very well!!! Thank you.

    By Youri on Oct 31, 2009

  12. 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.

    By Youri on Nov 2, 2009

  13. How can I get the data from 2 different tables into the export.xls file?

    By Mike on Nov 4, 2009

  14. 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 ?

    By Sonam on Apr 22, 2010

  15. 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.

    By Rob on May 21, 2010

  16. 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

    By Ruz on Jun 30, 2010

Post a Comment