Top SEM Tips

SEO and Link Building Tips

Save HTML Table to Excel using jQuery

with 40 comments

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/

Written by Dylan

November 7th, 2008 at 2:47 pm

Posted in Accessibility,jQuery

40 Responses to 'Save HTML Table to Excel using jQuery'

Subscribe to comments with RSS or TrackBack to 'Save HTML Table to Excel using jQuery'.

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

    Kamal

    6 Dec 08 at 9:42 am

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

    John

    16 Dec 08 at 4:17 pm

  3. I figured that out.

    Thanks,

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

    Thanks,

    John

    16 Dec 08 at 4:20 pm

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

    Acrobatic

    30 Dec 08 at 10:09 am

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

    Jenna

    29 Jan 09 at 10:56 am

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

    Dylan

    29 Jan 09 at 7:47 pm

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

    Ryan

    30 Mar 09 at 10:45 am

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

    Peter

    20 Apr 09 at 5:13 am

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

    Mariusz

    13 Jul 09 at 2:17 am

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

    Youri

    2 Nov 09 at 3:15 pm

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

    Mike

    4 Nov 09 at 3:48 am

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

    Sonam

    22 Apr 10 at 8:30 am

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

    Rob

    21 May 10 at 8:29 am

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

    Ruz

    30 Jun 10 at 3:16 am

  15. Do you have a working example/downloadable script? I’m running into problems with the onsubmit portion.

    Tim

    26 Oct 10 at 5:50 pm

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

    Tim Robinson

    3 Feb 11 at 7:21 am

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

    Scott

    18 Apr 11 at 6:43 am

  18. hi
    how can we archive the same pdf

    thank u

    manjunath

    27 May 11 at 8:26 am

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

    Suman

    21 Jul 11 at 2:26 am

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

    Netta

    9 Aug 11 at 9:43 am

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

    Javier C.

    10 Aug 11 at 7:24 pm

  22. The .xls file downloads successfully but doesn’t open, neither from browsers Open nor if I Save.
    help :)

    Ben

    18 Aug 11 at 8:46 am

  23. EDIT: saved to desktop and opened, the .xls file is completely blank.

    Ben

    18 Aug 11 at 8:54 am

  24. Can someone upload the working example, In my Case it not displaying the content of the table in Excel

    Paul

    27 Aug 11 at 11:28 am

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

    Walker B. Aguilar

    9 Sep 11 at 6:41 pm

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

    Dylan

    12 Sep 11 at 1:03 am

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

    Dylan

    12 Sep 11 at 1:07 am

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

    Dylan

    12 Sep 11 at 1:08 am

  29. Use CSS files and CSS classes rather than inline style statements.

    Dylan

    12 Sep 11 at 1:09 am

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

    Tech

    15 Sep 11 at 4:37 am

  31. Can someone please upload the source files… Thanks :)

    Tech

    15 Sep 11 at 4:41 am

  32. Finally Got it to work. Seems that my jsquery library was in a different folder.. :D

    Tech

    15 Sep 11 at 5:06 am

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

    Dylan

    26 Sep 11 at 4:16 pm

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

    eduardo valverde

    12 Jan 12 at 1:03 pm

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

    Dylan

    12 Jan 12 at 2:28 pm

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

    SWC

    12 Jan 12 at 9:35 pm

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

    Dylan

    13 Jan 12 at 5:02 pm

  38. It works for me. Thanks.

    SWC

    17 Jan 12 at 4:23 pm

  39. Muito bom, gostei!

    Antério

    3 May 12 at 11:32 pm

  40. I got it working..

    but i have a question. How can I export multiple tables in one excel file? is it posible?

    Daniel

    10 May 12 at 6:17 am

Leave a Reply