How do I export multiple html tables to excel?

Question

I have a webpage that has 3 tables and I'd like to export all 3 of them to same excel file. I'd like each table in its own sheet, but having them all on the same sheet is ok as well. After some googling, all I've seen is exporting one table to one excel sheet.

And how would you like to do this? With what language? In what environment? How much effort have you put into this yourself so far? You have enough badges and rep to know the rules. — Tom Zych, Jul 08 '14 at 17:24
What programming language are you using? Only HTML and Javascript? — alex.pulver, Jul 09 '14 at 07:47
@alex.pulver I'm using ASP.NET Web Pages. But I'm only looking to do this in HTML and Javascript. — dotnetN00b, Jul 09 '14 at 12:40
Let me know if you change your mind. As far as I know you can only save html files with xls extension. It is not a real xls file and you cannot have more than one sheet that is actually an html table. — alex.pulver, Jul 09 '14 at 13:22
Have you tried http://excelbuilderjs.com/ ? It's capable of packing up multiple worksheets into a workbook, and doesn't require a backend (though you'll need something like downloadify to get the file to the user if you don't have a backend to bounce it off of). — Stephen, Jul 09 '14 at 20:43
@Stephen - I took a look at that and it seems that all data must be passed as JSON. Whereas I'm looking to just pass the straight raw html. Am I wrong on it requiring all data be passed as JSON? — dotnetN00b, Jul 10 '14 at 02:58
@dotnetN00b - you are correct. However, depending on how complex your tables are and how much of that complexity you want to copy, you could just grab each row of the table, then each cell and pack them into an appropriate array.. and that's about all you'd need to do. It shouldn't be more than a dozen lines of JS. What you're talking about though is a good feature request to add to the project. — Stephen, Jul 10 '14 at 04:22
You can check my working [example.](https://stackoverflow.com/a/69591366/4398860) — Veysel, Oct 15 '21 at 22:58
You can check my working [example.](https://stackoverflow.com/a/69591366/4398860) — Veysel, Oct 15 '21 at 23:00

score 12 · Accepted Answer · edited May 23 '17 at 11:54

var tablesToExcel = (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>'
    , templateend = '</x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head>'
    , body = '<body>'
    , tablevar = '<table>{table'
    , tablevarend = '}</table>'
    , bodyend = '</body></html>'
    , worksheet = '<x:ExcelWorksheet><x:Name>'
    , worksheetend = '</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet>'
    , worksheetvar = '{worksheet'
    , worksheetvarend = '}'
    , base64 = function (s) { return window.btoa(unescape(encodeURIComponent(s))) }
    , format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) }
    , wstemplate = ''
    , tabletemplate = '';

    return function (table, name, filename) {
        var tables = table;

        for (var i = 0; i < tables.length; ++i) {
            wstemplate += worksheet + worksheetvar + i + worksheetvarend + worksheetend;
            tabletemplate += tablevar + i + tablevarend;
        }

        var allTemplate = template + wstemplate + templateend;
        var allWorksheet = body + tabletemplate + bodyend;
        var allOfIt = allTemplate + allWorksheet;

        var ctx = {};
        for (var j = 0; j < tables.length; ++j) {
            ctx['worksheet' + j] = name[j];
        }

        for (var k = 0; k < tables.length; ++k) {
            var exceltable;
            if (!tables[k].nodeType) exceltable = document.getElementById(tables[k]);
            ctx['table' + k] = exceltable.innerHTML;
        }

        //document.getElementById("dlink").href = uri + base64(format(template, ctx));
        //document.getElementById("dlink").download = filename;
        //document.getElementById("dlink").click();

        window.location.href = uri + base64(format(allOfIt, ctx));

    }
})();

And the HTML

<html>
    <head>
        <title>JS to Excel</title>

    </head>
    <body>
        <table id="1">
            <tr><td>Hi</td></tr>
            <tr><td>Hey</td></tr>
            <tr><td>Hello</td></tr>
        </table>
        <table id="2">
            <tr><td>Night</td></tr>
            <tr><td>Evening</td></tr>
            <tr><td>Nite</td></tr>
        </table>

        <a id="dlink"  style="display:none;"></a>
        <input type="button" onclick="tablesToExcel(['1', '2'], ['first', 'second'], 'myfile.xls')" value="Export to Excel">
        <script src="~/Views/JS/JSExcel.js" type="text/javascript"></script>
    </body>
</html>

NOTE: this doesn't work on IE ('data too small' error) and on Firefox both tables are put on the same sheet.

Credit also to this thread - HTML Table to Excel Javascript

I found this not work well when using multiple sheets. In my case, http://stackoverflow.com/questions/29698796/how-to-convert-html-table-to-excel-with-multiple-sheet this worked well. One thing should be careful is '/' character in the sheet name. — Lion.k, Jun 27 '15 at 13:08
I have this error with this code The prefix "x" for element "x:ExcelWorksheet" is not bound. — Ignacio Chiazzo, Oct 29 '15 at 00:12
I tried this and both tabs are created in excel, but the first tab has data for both tables, and the second tab is empty — Keisha W, Jul 30 '18 at 18:03
For my needs https://github.com/hhurz/tableExport.jquery.plugin/ is doing a great job — pixelDino, Apr 29 '19 at 10:24

score 2 · Answer 2 · edited Feb 18 '15 at 14:32

//function 1

     $scope.exportXlsSheets = function (datasets) {

            var xlsString = '<?xml version="1.0"?>\
            <ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">';        
            for(var key in dict){
                var arr_of_entities=  dict[key].arr;
                xlsString += $scope.getSheet(arr_of_entities);
            }
            xlsString += '</ss:Workbook>';
            var a = document.createElement('a');
            a.href = 'data:application/vnd.ms-excel;base64,' + $scope.base64(xlsString);
            a.target = '_blank';
            a.download = 'test1.xls';

            document.body.appendChild(a);
            a.click();
        }
        $scope.base64 = function (s) {
            return window.btoa(unescape(encodeURIComponent(s)))
        }

//function 2

  $scope.getSheet = function (sheetName, entities) {

        var res = '<ss:Worksheet ss:Name="'+sheetName+"></ss:Worksheet>\
                   <ss:Table>';

             var row = '<ss:Row>';
            for ( i = 0; i < entities.length; i++) {
              var entity = entities[i];


                   row += '<ss:Cell>\
                            <ss:Data ss:Type="String">'+entity.value +'</ss:Data>\
                        </ss:Cell>';
            }
            row += '</ss:Row>';
           res += row;

        return res;
    }

This answer pointed me in the right direction. It supports putting the data on different sheets. It outputs SpreadsheetML not HTML. More info in this answer here: http://stackoverflow.com/a/150368/373981 — John Galambos, Mar 24 '15 at 18:55
@Ehud can you please give the jsfiddle for it? it would be easy to understand in which format `datasets` are being passed — Liz., Nov 21 '17 at 05:30

Stone · Answer 3 · 2016-11-08T01:05:38.667

Here is a better solution that supports exporting table in the latest Excel format i.e. xlsx . The accepted solution would fail in case the total number of rows being exported exceeds 3407 on Chrome.

An example from the link above: http://jsfiddle.net/6ckj281f/

html

<button onclick="saveFile()">Save XLSX file</button>

javascript

window.saveFile = function saveFile () {
var data1 = [{a:1,b:10},{a:2,b:20}];
var data2 = [{a:100,b:10},{a:200,b:20}];
var opts = [{sheetid:'One',header:true},{sheetid:'Two',header:false}];
var res = alasql('SELECT INTO XLSX("restest344b.xlsx",?) FROM ?',
                 [opts,[data1,data2]]);
}

How do I export multiple html tables to excel?

3 Answers3

Linked

Related