CODEWITHSUNDEEP
javascripthtmlexcelhtml-tableexport-to-excel
Reza Maulana
Reza Maulana

Reputation: 148

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

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

Upvotes: 1

Views: 13576

Answers (3)

Veysel
Veysel

Reputation: 71

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

Upvotes: 0

Pran
Pran

Reputation: 1906

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>

Upvotes: 1

Reza Maulana
Reza Maulana

Reputation: 148

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

        }
    })();

Upvotes: 5

Related Questions