How do I export multiple html tables to excel?

javascript html excel html-table export-to-excel

25,263

Solution 1

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

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

25,263

Author by

dotnetN00b

Updated on January 30, 2021

Comments

dotnetN00b over 3 years

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.
Ehud about 9 years

it is in angular, but it shouldnt matter.
John Galambos about 9 years

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
yountae.kang almost 9 years

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 8 years

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

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

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 about 5 years

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