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
– Tom Zych, Commented Jul 8, 2014 at 17:24
What programming language are you using? Only HTML and Javascript? — alex.pulver
– alex.pulver, Commented Jul 9, 2014 at 7:47
@alex.pulver I'm using ASP.NET Web Pages. But I'm only looking to do this in HTML and Javascript. — dotnetN00b
– dotnetN00b, Commented Jul 9, 2014 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
– alex.pulver, Commented Jul 9, 2014 at 13:22
Have you tried 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
– Stephen, Commented Jul 9, 2014 at 20:43

Community · Accepted Answer · 2017-05-23 11:54:56Z

13

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

edited May 23, 2017 at 11:54

CommunityBot

11 silver badge

answered Jul 10, 2014 at 2:53

dotnetN00b

5,15113 gold badges68 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Lion.k Over a year ago

I found this not work well when using multiple sheets. In my case, stackoverflow.com/questions/29698796/… this worked well. One thing should be careful is '/' character in the sheet name.

Ignacio Chiazzo Over a year ago

I have this error with this code The prefix "x" for element "x:ExcelWorksheet" is not bound.

Keisha W Over a year ago

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

pixelDino Over a year ago

For my needs github.com/hhurz/tableExport.jquery.plugin is doing a great job

dotnetN00b · Accepted Answer · 2015-02-18 14:32:28Z

2

//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;
    }

edited Feb 18, 2015 at 14:32

dotnetN00b

5,15113 gold badges68 silver badges97 bronze badges

answered Feb 18, 2015 at 13:59

Ehud

1155 bronze badges

3 Comments

Ehud Over a year ago

it is in angular, but it shouldnt matter.

John Galambos Over a year ago

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: stackoverflow.com/a/150368/373981

Liz. Over a year ago

@Ehud can you please give the jsfiddle for it? it would be easy to understand in which format datasets are being passed

Stone · Accepted Answer · 2016-11-08 01:05:38Z

-2

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]]);
}

edited Nov 8, 2016 at 1:05

answered Nov 7, 2016 at 21:48

Stone

1,17910 silver badges17 bronze badges

Collectives™ on Stack Overflow

How do I export multiple html tables to excel?

3 Answers 3

4 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related