How can I export tables to Excel from a webpage [closed]

How can I export tables to Excel from a webpage [closed]

How can I export tables to Excel from a webpage. I want the export to contain all the formatting and colours.

Solutions/Answers:

Solution 1:

Far and away, the cleanest, easiest export from tables to Excel is Jquery DataTables Table Tools plugin. You get a grid that sorts, filters, orders, and pages your data, and with just a few extra lines of code and two small files included, you get export to Excel, PDF, CSV, to clipboard and to the printer.

This is all the code that’s required:

  $(document).ready( function () {
    $('#example').dataTable( {
        "sDom": 'T<"clear">lfrtip',
        "oTableTools": {
            "sSwfPath": "/swf/copy_cvs_xls_pdf.swf"
        }
    } );
} );

So, quick to deploy, no browser limitations, no server-side language required, and most of all very EASY to understand. It’s a win-win. The one thing it does have limits on, though, is strict formatting of columns.

If formatting and colors are absolute dealbreakers, the only 100% reliable, cross browser method I’ve found is to use a server-side language to process proper Excel files from your code. My solution of choice is PHPExcel It is the only one I’ve found so far that positively handles export with formatting to a MODERN version of Excel from any browser when you give it nothing but HTML. Let me clarify though, it’s definitely not as easy as the first solution, and also is a bit of a resource hog. However, on the plus side it also can output direct to PDF as well. And, once you get it configured, it just works, every time.

UPDATE – September 15, 2016: TableTools has been discontinued in favor of a new plugin called “buttons” These tools perform the same functions as the old TableTools extension, but are FAR easier to install and they make use of HTML5 downloads for modern browsers, with the capability to fallback to the original Flash download for browsers that don’t support the HTML5 standard. As you can see from the many comments since I posted this response in 2011, the main weakness of TableTools has been addressed. I still can’t recommend DataTables enough for handling large amounts of data simply, both for the developer and the user.

Solution 2:

A long time ago, I discovered that Excel would open an HTML file with a table if we send it with Excel content type. Consider the document above:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
  <title>Java Friends</title>
</head>
<body>
  <table style="font-weight: bold">
    <tr style="background-color:red"><td>a</td><td>b</td></tr>
    <tr><td>1</td><td>2</td></tr>
  </table>    
</body>
</html>

I ran the following bookmarklet on it:

javascript:window.open('data:application/vnd.ms-excel,'+document.documentElement.innerHTML);

and in fact I got it downloadable as a Excel file. However, I did not get the expected result – the file was open in OpenOffice.org Writer. That is my problem: I do not have Excel in this machine so I cannot try it better. Also, this trick worked more or less six years ago with older browsers and an antique version of MS Office, so I really cannot say if it will work today.

Anyway, in the document above I added a button which would download the entire document as an Excel file, in theory:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
  <title>Java Friends</title>
</head>
<body>
  <table style="font-weight: bold">
    <tr style="background-color:red"><td>a</td><td>b</td></tr>
    <tr><td>1</td><td>2</td></tr>
    <tr>
      <td colspan="2">
        <button onclick="window.open('data:application/vnd.ms-excel,'+document.documentElement.innerHTML);">
            Get as Excel spreadsheet
        </button>
      </td>
    </tr>
  </table>    
</body>
</html>

Save it in a file and click on the button. I’d love to know if it worked or not, so I ask you to comment even for saying that it did not work.

Solution 3:

It is possible to use the old Excel 2003 XML format (before OpenXML) to create a string that contains your desired XML, then on the client side you could use a data URI to open the file using the XSL mime type, or send the file to the client using the Excel mimetype “Content-Type: application/vnd.ms-excel” from the server side.

  1. Open Excel and create a worksheet with your desired formatting and colors.
  2. Save the Excel workbook as “XML Spreadsheet 2003 (*.xml)”
  3. Open the resulting file in a text editor like notepad and copy the value into a string in your application
  4. Assuming you use the client side approach with a data uri the code would look like this:
    
    <script type="text/javascript">
    var worksheet_template = '<?xml version="1.0"?><ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">'+
                 '<ss:Styles><ss:Style ss:ID="1"><ss:Font ss:Bold="1"/></ss:Style></ss:Styles><ss:Worksheet ss:Name="Sheet1">'+
                 '<ss:Table>{{ROWS}}</ss:Table></ss:Worksheet></ss:Workbook>';
    var row_template = '<ss:Row ss:StyleID="1"><ss:Cell><ss:Data ss:Type="String">{{name}}</ss:Data></ss:Cell></ss:Row>';
    </script>
    
    
  5. Then you can use string replace to create a collection of rows to be inserted into your worksheet template
    
    <script type="text/javascript">
    var rows = document.getElementById("my-table").getElementsByTagName('tr'),
      row_data = '';
    for (var i = 0, length = rows.length; i < length; ++i) {
    row_data += row_template.replace('{{name}}', rows[i].getElementsByTagName('td')[0].innerHTML);
    }
    </script>
    
    
  6. Once you have the information collected, create the final string and open a new window using the data URI

    
    <script type="text/javascript">
    var worksheet = worksheet_template.replace('{{ROWS}}', row_data);

    window.open(‘data:application/vnd.ms-excel,’+worksheet);
    </script>

Solution 4:

Excel has a little known feature called “Web queries” which let you retrieve data from almost every web page without additional programming.

A web query basicly runs a HTTP request directly from within Excel and copies some or all of the received data (and optionally formatting) into the worksheet.

After you’ve defined the web query you can refresh it at any time without even leaving excel. So you don’t have to actually “export” data and save it to a file – you’d rather refresh the data just like from a database.

You can even make use of URL parameters by having excel prompt you for certain filter criteria etc…

However the cons I’ve noticed so far are:

  • dynamicly loaded data is not accessible, because Javascript is not executed
  • URL length is limited

Here is a question about how to create web queries in Excel. It links to a Microsoft Help site about How-To Get external data from a Web page

Solution 5:

This is a php but you maybe able to change it to javascript:

<?php>
$colgroup = str_repeat("<col width=86>",5);
$data = "";
$time = date("M d, y g:ia");
$excel = "<html xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns=\"http://www.w3.org/TR/REC-html40\">
<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">
<html>
<head>
<meta http-equiv=\"Content-type\" content=\"text/html;charset=utf-8\" />
<style id=\"Classeur1_16681_Styles\">
.xl4566 {
color: red;
}
</style>
</head>
<body>
<div id=\"Classeur1_16681\" align=center x:publishsource=\"Excel\">
<table x:str border=0 cellpadding=0 cellspacing=0 style=\"border-collapse: collapse\">
<colgroup>$colgroup</colgroup>
<tr><td class=xl2216681><b>Col1</b></td><td class=xl2216681><b>Col2</b></td><td class=xl2216681 ><b>Col3</b></td><td class=xl2216681 ><b>Col4</b></td><td class=xl2216681 ><b>Col5</b></td></tr>
<tr><td class=xl4566>1</td><td>2</td><td>3</td><td>4</td><td>5</td></tr>
</table>
</div>
</body>
</html>";
  $fname = "Export".time().".xls";
  $file = fopen($fname,"w+");
  fwrite($file,$excel);
  fclose($file);
  header('Content-Type: application/vnd.ms-excel');
  header('Content-Disposition: attachment; filename="'.basename($fname).'"');
  readfile($fname);
  unlink($fname); ?>    

Solution 6:

First, I would not recommend trying export Html and hope that the user’s instance of Excel picks it up. My experience that this solution is fraught with problems including incompatibilities with Macintosh clients and throwing an error to the user that the file in question is not of the format specified. The most bullet-proof, user-friendly solution is a server-side one where you use a library to build an actual Excel file and send that back to the user. The next best solution and more universal solution would be to use the Open XML format. I’ve run into a few rare compatibility issues with older versions of Excel but on the whole this should give you a solution that will work on any version of Excel including Macs.

Open XML