How can I export tables to Excel from a webpage

Question

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

The easiest way is probably to export a HTML document, which Excel can open. — Pekka, Apr 02 '11 at 15:50
@Pekka I've tried that, it loses all formatting/css/column size etc — 124697, Apr 02 '11 at 15:52
@user where are you declaring column sizes and such? I'm not deeply familiar with exporting data to Excel but you may need to declare them inline, i.e. ` — Pekka, Apr 02 '11 at 15:57
@user - there are at least two different issues here: 1) formatting the data so that it appears correctly in Excel, and 2) exporting the data using Javascript so that it sets the mime-type correctly, prompting the user to save the file. Are you trying to solve both of these problems? — nrabinowitz, Apr 21 '11 at 05:16
@user, did you find a solution that answers the question? If so, could you accept? — bpeterson76, Apr 28 '11 at 18:39
simply select entry html table and drag it to excel. if html is valid, then all must work. Like here http://www.mrkent.com/tools/converter/index.htm (if you need automated way, then this is not a solution) — Guntis, Jan 15 '13 at 15:24
Why was this considered "opinion-based"? This is a very straightforward technical question. — brandizzi, Nov 11 '14 at 18:09
I suggest you check out [TableExport](https://github.com/clarketm/TableExport). It is a simple, easy-to-implement plugin to export HTML tables to `xlsx`, `xls`, `csv`, and `txt` files. It also works in all major browsers. — Travis Clarke, Dec 08 '16 at 05:23

score 75 · Answer 1 · edited Aug 28 '17 at 09:27

75

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.

edited Aug 28 '17 at 09:27

Karl

1,814
1
25
37

answered Apr 25 '11 at 14:00

bpeterson76

12,918
5
49
82

2

DataTables is fully Javascript. Just the TableTools element uses Flash, and it's miniscule. I would NEVER use Flash willingly in any of my products! – bpeterson76 Apr 27 '11 at 20:17
16

I understand, and I agree. But still - however minuscule - there's a .swf object there, and it cannot run without Flash. – magma Apr 28 '11 at 02:31
8

Such a great a solution, but such a shame it needs Flash. – jnthnclrk Mar 15 '12 at 13:55
Hello can you show a complete example, i am too noob to make it work without the example! – NoobTom Jul 28 '14 at 12:17
is same library available for ng-table? – Pramod Gaikwad Jan 14 '16 at 15:46
1

@PramodGaikwad, no, Datatables would replace NG-table. They are effectively the same functionality, but Datatables is FAR more mature and has many, many more features. There is a spinoff of Datatables created specifically for Angular: http://l-lin.github.io/angular-datatables/#/welcome – bpeterson76 Jan 14 '16 at 22:51
Ok. @bpeterson76 Thanks for suggestion. – Pramod Gaikwad Jan 15 '16 at 18:34
@Derek朕會功夫 iPad, no saved files, no? – mplungjan Aug 18 '16 at 14:07
@mplungjan Touché. But recently many browsers are going in the direction of dropping Flash support. – Derek 朕會功夫 Aug 19 '16 at 07:00

score 42 · Answer 2 · answered Apr 26 '11 at 21:36

42

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.

answered Apr 26 '11 at 21:36

brandizzi

26,083
8
103
158

I tried it and the result was a normal excel with all the html of the table as text in the first cell...but the idea is a good one nonetheless...i just needed something like this (but working :P).. – VSP Jun 11 '12 at 13:20
Its strange though that comparing the excel content with one exported as excel from asp.net the content is the same except the one exported with your function removed the spaces between attributes if else it would work fine :S example: ... If i made an alert with the same tableelement.outerhtml it would display it fine with its spaces... – VSP Jun 11 '12 at 13:50
11

Made it work adding a replace in the end: window.open('data:application/vnd.ms-excel,' + document.getElementById('table').outerHTML.replace(/ /g, '%20')); – VSP Jun 11 '12 at 14:04
6

Alternative way (recommended): window.open('data:application/vnd.ms-excel,' + encodeURIComponent(document.getElementById('table').outerHTML)); – VSP Jun 11 '12 at 14:14
5

Works perfectly in firefox, wrap your table in a div and then call the id with `document.getElementById('id').innerHTML` to selectively grab the table only, otherwise all your stuff gets exported to the spreadsheet. Doesn't work in old IE though, just opens a new window with all the html in the title – Abraham Brookes Aug 18 '12 at 20:30
why the red line is till the end ? I put a width - and in the excel it takes still 100%... – Royi Namir Oct 31 '12 at 13:40
Yes this is the easiest way by my opinion. Create table in Excel with all the formatting you consider, export it to HTML and observe its contents then use it as a template. – tomasb Oct 11 '13 at 01:17
1

This simple solution works just fine. Look at this duplicate question in order to be able to set filename, and set a work sheet name as well. Same type of solution; http://stackoverflow.com/questions/17126453/html-table-to-excel-javascript – Espen Schulstad Mar 13 '15 at 10:50
It doesn't work in IE. Can anyone give some lights to make it working in IE 9,10,11 – Abdur Rahman Feb 23 '16 at 07:14
2

This no longer works in Office 365 due to more strict security measures. Excel file must be a TRUE Excel document or it will throw error when opening. – Phil Aug 09 '16 at 14:07
@Phil damn you, proper security measures!!! __ – brandizzi Aug 09 '16 at 14:44

score 12 · Answer 3 · edited Apr 29 '15 at 10:17

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.

Open Excel and create a worksheet with your desired formatting and colors.
Save the Excel workbook as "XML Spreadsheet 2003 (*.xml)"
Open the resulting file in a text editor like notepad and copy the value into a string in your application

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>

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>

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>

It is worth noting that older browsers do not support the data URI scheme, so you may need to produce the file server side for those browser that do not support it.

You may also need to perform base64 encoding on the data URI content, which may require a js library, as well as adding the string ';base64' after the mime type in the data URI.

Although it's nice to use OpenXML, this solution wont work on tables with colspans or rowspans without lots of work on the javascript generator — Eduardo Molteni, Oct 11 '12 at 02:35
Thank you for teaching me something rather than telling me to use a plugin, highly appreciated. It's worth mentioning that this approach still works nicely today. — Benjamin Gruenbaum, Apr 29 '15 at 10:15
Interesting, tried this approach. I just get the entire **'+ ''+ '** values with my strings written into one cell, including all rows in one cell. What am I missing? — CromeX, Mar 08 '17 at 12:58

score 6 · Answer 4 · edited May 23 '17 at 11:47

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

It does work with basic and also form based authentication but with the latter you might have to click on "edit query" to re-enter credentials and obtain a new cookie from time time — HAL 9000, Sep 11 '16 at 16:02

score 5 · Answer 5 · answered Apr 02 '11 at 16:05

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); ?>

score 5 · Answer 6 · answered Apr 25 '11 at 19:18

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

score 4 · Answer 7 · answered Apr 27 '11 at 20:46

mozilla still support base 64 URIs. This allows you to compose dynamically the binary content using javascript:

<a href="data:application/vnd.ms-excel<base64 encoded binary excel content here>"> download xls</a>

if your excel file is not very fancy (no diagrams, formulas, macroses) you can dig into the format and compose bytes for your file, then encode them with base64 and put in to the href

refer to https://developer.mozilla.org/en/data_URIs

Aaron Digulla · Answer 8 · 2011-04-21T08:38:06.500

This is actually more simple than you'd think: "Just" copy the HTML table (that is: The HTML code for the table) into the clipboard. Excel knows how to decode HTML tables; it'll even try to preserve the attributes.

The hard part is "copy the table into the clipboard" since there is no standard way to access the clipboard from JavaScript. See this blog post: Accessing the System Clipboard with JavaScript – A Holy Grail?

Now all you need is the table as HTML. I suggest jQuery and the html() method.

score 2 · Answer 9 · answered Apr 22 '11 at 18:57

2

This code is IE only so it is only useful in situations where you know all of your users will be using IE (like, for example, in some corporate environments.)

<script Language="javascript">
function ExportHTMLTableToExcel()
{
   var thisTable = document.getElementById("tbl").innerHTML;
   window.clipboardData.setData("Text", thisTable);
   var objExcel = new ActiveXObject ("Excel.Application");
   objExcel.visible = true;

   var objWorkbook = objExcel.Workbooks.Add;
   var objWorksheet = objWorkbook.Worksheets(1);
   objWorksheet.Paste;
}
</script>

answered Apr 22 '11 at 18:57

NakedBrunch

48,713
13
73
98

I tried using this code it did open the table in excel but not the correct format looks like it just copied the html code into the tables. like this: SARTIN, DAN BALAEZ, BARBARA Any Suggestions? – Fahad Mar 20 '13 at 18:22
That's because he used innerHTML. The element he's getting IS the table, so it should be outerHTML. I made the edit – user1566694 Jul 15 '13 at 15:19
I get the error: "Automation server can't create object" while creating ActiveXObject. How can I fix it? – Nk SP Dec 12 '14 at 14:25

score 2 · Answer 10 · answered Apr 27 '11 at 20:34

Assumptions:

given url
the conversion has to be done on client side
systems are Windows, Mac and linux

Solution for Windows:

python code that open the ie window and has access to it: theurl variable contain the url ('http://')

ie = Dispatch("InternetExplorer.Application")
ie.Visible = 1
ie.Navigate(theurl)

Note: if the page is not accessible directly, but login, you will need to handle this by entering the form data and emulating the user actions with python

here is the example

from win32com.client import Dispatch
ie.Document.all('username').value=usr
ie.Document.all('password').value=psw

the same manner for retrieval of data from web page. Let's say element with id 'el1' contain the data. retrieve the element text to the variable

el1 = ie.Document.all('el1').value

then when data is in python variable, you can open the excel screen in similar manner using python:

from win32com.client import Dispatch
xlApp = Dispatch("Excel.Application")
xlWb = xlApp.Workbooks.Open("Read.xls")
xlSht = xlWb.WorkSheets(1)
xlSht.Cells(row, col).Value = el1

Solution for Mac:

only the tip: use AppleScript - it has simple and similar API as win32com.client Dispatch

Solution for Linux:

java.awt.Robot might work for this it has click, key press (hot keys can be used), but none API for Linux that I am aware about that can work as simple as AppleScript

score 1 · Answer 11 · answered Dec 19 '13 at 23:36

1

And now there is a better way.

OpenXML SDK for JavaScript.

https://openxmlsdkjs.codeplex.com/

answered Dec 19 '13 at 23:36

Eric Hartford

16,464
4
33
50

hmmm, downloaded and tried to run the examples in latest chrome, just got errors :( – Josh Mc Mar 04 '14 at 01:16

score 1 · Answer 12 · answered Apr 25 '11 at 17:26

simple google search turned up this:

If the data is actually an HTML page and has NOT been created by ASP, PHP, or some other scripting language, and you are using Internet Explorer 6, and you have Excel installed on your computer, simply right-click on the page and look through the menu. You should see "Export to Microsoft Excel." If all these conditions are true, click on the menu item and after a few prompts it will be imported to Excel.

if you can't do that, he gives an alternate "drag-and-drop" method:

http://www.mrkent.com/tools/converter/

sra · Answer 13 · 2011-04-27T15:05:28.030

There are practical two ways to do this automaticly while only one solution can be used in all browsers. First of all you should use the open xml specification to build the excel sheet. There are free plugins from Microsoft available that make this format also available for older office versions. The open xml is standard since office 2007. The the two ways are obvious the serverside or the clientside.

The clientside implementation use a new standard of CSS that allow you to store data instead of just the URL to the data. This is a great approach coz you dont need any servercall, just the data and some javascript. The killing downside is that microsoft don't support all parts of it in the current IE (I don't know about IE9) releases. Microsoft restrict the data to be a image but we will need a document. In firefox it works quite fine. For me the IE was the killing point.

The other way is to user a serverside implementation. There should be a lot implementations of open XML for all languages. You just need to grap one. In most cases it will be the simplest way to modify a Viewmodel to result in a Document but for sure you can send all data from Clientside back to server and do the same.

Can the down-voter please comment what's the reason for downvoting? — sra, Jan 04 '12 at 14:39

score 0 · Answer 14 · edited Oct 31 '12 at 13:20

   function normalexport() {

       try {
           var i;
           var j;
           var mycell;
           var tableID = "tblInnerHTML";
           var drop = document.getElementById('<%= ddl_sections.ClientID %>');
           var objXL = new ActiveXObject("Excel.Application");
           var objWB = objXL.Workbooks.Add();
           var objWS = objWB.ActiveSheet;
           var str = filterNum(drop.options[drop.selectedIndex].text);
           objWB.worksheets("Sheet1").activate; //activate dirst worksheet
           var XlSheet = objWB.activeSheet; //activate sheet
           XlSheet.Name = str; //rename


           for (i = 0; i < document.getElementById("ctl00_ContentPlaceHolder1_1").rows.length - 1; i++) {
               for (j = 0; j < document.getElementById("ctl00_ContentPlaceHolder1_1").rows(i).cells.length; j++) {
                   mycell = document.getElementById("ctl00_ContentPlaceHolder1_1").rows(i).cells(j);

                   objWS.Cells(i + 1, j + 1).Value = mycell.innerText;

                   //                                                objWS.Cells(i + 1, j + 1).style.backgroundColor = mycell.style.backgroundColor;
               }
           }

           objWS.Range("A1", "L1").Font.Bold = true;
           //                objWS.Range("A1", "L1").Font.ColorIndex = 2;
           //                 objWS.Range("A1", "Z1").Interior.ColorIndex = 47;

           objWS.Range("A1", "Z1").EntireColumn.AutoFit();

           //objWS.Range("C1", "C1").ColumnWidth = 50;

           objXL.Visible = true;

       } catch (err) {
           alert("Error. Scripting for ActiveX might be disabled")
           return
       }
       idTmr = window.setInterval("Cleanup();", 1);

   }


   function filterNum(str) {

       return str.replace(/[ / ]/g, '');
   }

How can I export tables to Excel from a webpage

14 Answers14

Linked

Related