How do I export multiple html tables to excel?

6
votes

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.

3
javascripthtmlexcelhtml-tableexport-to-excel
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
What programming language are you using? Only HTML and Javascript? - alex.pulver
@alex.pulver I'm using ASP.NET Web Pages. But I'm only looking to do this in HTML and Javascript. - dotnetN00b
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
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

3 Answers

12
votes
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

2
votes

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

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