07 Nov

Save HTML Table to Excel using jQuery

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 (note this is older code – for newer code follow link at bottom):

<form action="/SaveToExcel.php" method="post" target="_blank"
onsubmit='$("#datatodisplay").val( $("&lt;div&gt;").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.

40 thoughts on “Save HTML Table to Excel using jQuery”

  2. John -

    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.

    Reply
  5. Jenna -

    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!

    Reply
    • Dylan -

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

      Reply
  6. Ryan -

    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.

    Reply
  8. Mariusz -

    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

    Reply
  9. Youri -

    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.

    Reply
  11. Sonam -

    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 ?

    Reply
  12. Rob -

    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.

    Reply
  13. Ruz -

    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

    Reply
  15. Tim Robinson -

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

    Reply
  16. Scott -

    Amazing script, exactly what I’ve been searching for! Thank you so much for writing this – so thankful at this early hour!

    Reply
  18. Suman -

    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

    Reply
  19. Netta -

    Lovely! Really helped me a whole lot 🙂 Now I’m looking for a way to remove the styling before the export – any ideas?

    Reply
  20. Javier C. -

    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.

    Reply
    • Dylan -

      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.

      Reply
  24. Walker B. Aguilar -

    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.

    Reply
    • Dylan -

      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.

      Reply
  25. Tech -

    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

    Reply
    • Dylan -

      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.

      Reply
  28. eduardo valverde -

    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

    Reply
    • Dylan -

      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.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *