Using Javascript to Export Multiple HTML Tables into single Excel file (xls)

Question

I'm trying to export multiple html table into single file excel (xls) It should be like this
Thanks Credit : How do I export multiple html tables to excel?

The HTML Code

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

And the Javascript like this

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

    }
})();

It works with 2 tables in a single excel file (xls)
My question is, what if I have 1000 tables? How can i use a DO or for loop to solve my problem?
Im a student and really new in programming, Thanks

Reza Maulana · Accepted Answer · 2016-08-04 03:19:07Z

Finally, got little help from friend and its done :)
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(array1, array2, 'myfile.xls')" value="Export to Excel">
    <script src="~/Views/JS/JSExcel.js" type="text/javascript"></script>
</body>

Javascript

var array1 = new Array();
    var array2 = new Array();
    var n = 2; //Total table
    for ( var x=1; x<=n; x++ ) {
        array1[x-1] = x;
        array2[x-1] = x + 'th';
    }

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

        }
    })();

is it possible to export tables so they are on the same row and not one under other? @rezaMaulana
Btw: the script fails when called a second time (runs through but the result file is broken)

Pran · Accepted Answer · 2021-01-29 20:39:19Z

Export Multiple Tables to Excel, in Single Sheet

HTML

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

<body>
  <table id="export_table_to_excel_1">
    <tr><td>Hi</td></tr>
    <tr><td>Hey</td></tr>
    <tr><td>Hello</td></tr>
  </table>

  <table id="export_table_to_excel_2">
    <tr><td>Night</td></tr>
    <tr><td>Evening</td></tr>
    <tr><td>Nite</td></tr>
  </table>

  <!--export excel button-->
  <a id="dlink" style="display:none;"></a>
  <input type="button" onclick="tablesToExcel(array1, 'Sheet1', 'myfile.xls')" value="Export to Excel">
</body>
</html>

JavaScript

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script>
    //table to excel (multiple table)
    var array1 = new Array();
    var n = 2; //Total table
    for ( var x=1; x<=n; x++ ) {
        array1[x-1] = 'export_table_to_excel_' + x;
    }
    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;
            var wstemplate = '';
            var tabletemplate = '';

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

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

            var ctx = {};
            ctx['worksheet0'] = name;
            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(allOfIt, ctx));;
            document.getElementById("dlink").download = filename;
            document.getElementById("dlink").click();
        }
    })();
</script>

Hi @Pran, this answer is useful to me. in continuation to this, my tables are having few input fields. if am using the above code, its ignoring the those input (editable) cells. how can I export multiple editable tables in to a single excel. please help.

Veysel · Accepted Answer · 2021-10-15 22:55:05Z

This is my working solution:

first give same name to the tables you want to export.

html

<a onclick="tableToExcel('orderTable', 'excel')"></a>

js

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]--><meta http-equiv="content-type" content="text/plain; charset=UTF-8"/></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.getElementsByName("orderTables");
    }
    var tableInnerHtml = "";
    for (var i = 0; i < table.length; i++) {
        //get tbody for once
        if (tableInnerHtml == "") {
            tableInnerHtml += table[i].innerHTML;
        } else {
            tableInnerHtml += table[i].tBodies[0].innerHTML;
        }
    }
    var ctx = {
        worksheet: name || 'Worksheet',
        table: tableInnerHtml
    }
    var a = document.createElement('a');
    a.href = uri + base64(format(template, ctx))
    a.download = name + '.xls';
    //triggering the function
    a.click();
}})()

Collectives™ on Stack Overflow

Using Javascript to Export Multiple HTML Tables into single Excel file (xls)

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions