Exporting HTML tables to Excel (.xls) in a separate sheet

Question

I've got a simple HTML page (generated by an external application) that contains a table view. I am trying to scrape off the tables from the page and put them in an Excel workbook. I have managed to put the whole HTML contents in a workbook by using the method available here.

Code from the related question:

var tableToExcel = (function() {
  var uri = 'data:application/vnd.ms-excel;base64,'
    , template = '<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"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>'
    , base64 = function(s) { return window.btoa(unescape(encodeURIComponent(s))) }
    , format = function(s, c) { return s.replace(/{(\w+)}/g, function(m, p) { return c[p]; }) }
  return function(table, name) {
    if (!table.nodeType) table = document.getElementById(table)
    var ctx = {worksheet: name || 'Worksheet', table: table.innerHTML}
    window.location.href = uri + base64(format(template, ctx))
  }
})()

JSFiddle

The method however does not support multiple spreadsheets. What I need is for every HTML table being in it's own SpreadSheet in the same Excel workbook. Something like this: enter image description here

I have tried to create a sample Excel document with two spreadsheets and then reverse engineer it by looking at an export in .html format. Unfortunately I failed to understand how to recreate the connection betwee a workbook and it's sheets.

As far as I can understand the format() function does the 'magical' combining of the worksheet data and the Excel template. The function looks very cryptic to me, so I have no idea how to go about modifying it.

What I need as an end game is having the possibility to call something like. tableToExcel(document.getElementsByTagName('table'), 'Workbook Name');

Any ideas if this is at all possible, and if so - how to go about making it happen?

You can't do multiple sheets using just the html tables, however you could probably write a function to export the tables using the [Excel XML format](http://en.wikipedia.org/wiki/Microsoft_Office_XML_formats). It would be a bit of work, but it is doable. — Danny, Jan 09 '14 at 19:59

score 3 · Answer 1 · answered Oct 05 '13 at 13:29

Checkout this blog post: http://www.kubilayerdogan.net/?p=218

 $(document).ready(function() {
    $("#btnExport").click(function(e) {
        //getting values of current time for generating the file name
        var dt = new Date();
        var day = dt.getDate();
        var month = dt.getMonth() + 1;
        var year = dt.getFullYear();
        var hour = dt.getHours();
        var mins = dt.getMinutes();
        var postfix = day + "." + month + "." + year + "_" + hour + "." + mins;
        //creating a temporary HTML link element (they support setting file names)
        var a = document.createElement('a');
        //getting data from our div that contains the HTML table
        var data_type = 'data:application/vnd.ms-excel';
        var table_div = document.getElementById('dvData');
        var table_html = table_div.outerHTML.replace(/ /g, '%20');
        a.href = data_type + ', ' + table_html;
        //setting the file name
        a.download = 'exported_table_' + postfix + '.xls';
        //triggering the function
        a.click();
        //just in case, prevent default behaviour
        e.preventDefault();
    });
});

You can see it in action in jsfiddle: http://jsfiddle.net/kublaios/8ZQN4/1/

Thanks for the response, but I don't understand how this solves the problem I described? — Nasco Bozhkov, Oct 29 '13 at 02:16
This solution worked only for crome.And it is also raise issue like when html table have more data then crome will kill the page and show me as crome is run out of memory. — Nency, Nov 19 '13 at 12:16
i pass my table to this line, `var table_div = document.getElementById('dvData');` and after that, i tried to open the document in live one drive, microsoft couldnt open it, and then google drive, it put html in first cell of xml cell :| — Hassan Faghihi, Jan 02 '17 at 21:39

Exporting HTML tables to Excel (.xls) in a separate sheet

1 Answers1

Linked