How to convert html table to excel with multiple sheet?

Question

How can I convert multiple html tables to an excel sheet with multiple worksheets? Could you please help into this.

My example https://jsfiddle.net/kdkd/5p22gdag/

        function tablesToExcel() {
        {

            var tab_text = "<table border='2px'><tr bgcolor='#87AFC6'>";
            var textRange; var j = 0;
            tab = document.getElementById('tbl2'); // id of table


            for (j = 0 ; j < tab.rows.length ; j++) {
                tab_text = tab_text + tab.rows[j].innerHTML + "</tr>";
                //tab_text=tab_text+"</tr>";
            }

            tab_text = tab_text + "</table>";
            var ua = window.navigator.userAgent;
            var msie = ua.indexOf("MSIE ");

            if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./))      // If Internet Explorer
            {
                txtArea1.document.open("txt/html", "replace");
                txtArea1.document.write(tab_text);
                txtArea1.document.close();
                txtArea1.focus();
                sa = txtArea1.document.execCommand("SaveAs", true, "Say Thanks to Sumit.xls");
            }
            else                 //other browser not tested on IE 11
                sa = window.open('data:application/vnd.ms-excel,' + encodeURIComponent(tab_text));


            return (sa);
        }

 }

Answer 1

You can do as below : Here is Full Example

Html :

<table id="tbl1" class="table2excel">         <tr>             <td>Product</td>             <td>Price</td>             <td>Available</td>             <td>Count</td>         </tr>         <tr>             <td>Bred</td>             <td>1</td>             <td>2</td>              <td>3</td>         </tr>         <tr>             <td>Butter</td>             <td>4   </td>             <td>5   </td>             <td >6  </td>         </tr>   </table> <hr>    <table id="tbl2" class="table2excel">         <tr>             <td>Product</td>             <td>Price</td>             <td>Available</td>             <td>Count</td>         </tr>         <tr>             <td>Bred</td>             <td>7</td>             <td>8</td>             <td>9</td>         </tr>         <tr>             <td>Butter</td>             <td>14</td>             <td>15</td>             <td >16</td>         </tr>     </table>   <button  onclick="tablesToExcel(['tbl1','tbl2'], ['ProductDay1','ProductDay2'], 'TestBook.xls', 'Excel')">Export to Excel</button> 

Javascript:

 var tablesToExcel = (function() {     var uri = 'data:application/vnd.ms-excel;base64,'     , tmplWorkbookXML = '<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?><Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">'       + '<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"><Author>Axel Richter</Author><Created>{created}</Created></DocumentProperties>'       + '<Styles>'       + '<Style ss:ID="Currency"><NumberFormat ss:Format="Currency"></NumberFormat></Style>'       + '<Style ss:ID="Date"><NumberFormat ss:Format="Medium Date"></NumberFormat></Style>'       + '</Styles>'        + '{worksheets}</Workbook>'     , tmplWorksheetXML = '<Worksheet ss:Name="{nameWS}"><Table>{rows}</Table></Worksheet>'     , tmplCellXML = '<Cell{attributeStyleID}{attributeFormula}><Data ss:Type="{nameType}">{data}</Data></Cell>'     , 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(tables, wsnames, wbname, appname) {       var ctx = "";       var workbookXML = "";       var worksheetsXML = "";       var rowsXML = "";        for (var i = 0; i < tables.length; i++) {         if (!tables[i].nodeType) tables[i] = document.getElementById(tables[i]);         for (var j = 0; j < tables[i].rows.length; j++) {           rowsXML += '<Row>'           for (var k = 0; k < tables[i].rows[j].cells.length; k++) {             var dataType = tables[i].rows[j].cells[k].getAttribute("data-type");             var dataStyle = tables[i].rows[j].cells[k].getAttribute("data-style");             var dataValue = tables[i].rows[j].cells[k].getAttribute("data-value");             dataValue = (dataValue)?dataValue:tables[i].rows[j].cells[k].innerHTML;             var dataFormula = tables[i].rows[j].cells[k].getAttribute("data-formula");             dataFormula = (dataFormula)?dataFormula:(appname=='Calc' && dataType=='DateTime')?dataValue:null;             ctx = {  attributeStyleID: (dataStyle=='Currency' || dataStyle=='Date')?' ss:StyleID="'+dataStyle+'"':''                    , nameType: (dataType=='Number' || dataType=='DateTime' || dataType=='Boolean' || dataType=='Error')?dataType:'String'                    , data: (dataFormula)?'':dataValue                    , attributeFormula: (dataFormula)?' ss:Formula="'+dataFormula+'"':''                   };             rowsXML += format(tmplCellXML, ctx);           }           rowsXML += '</Row>'         }         ctx = {rows: rowsXML, nameWS: wsnames[i] || 'Sheet' + i};         worksheetsXML += format(tmplWorksheetXML, ctx);         rowsXML = "";       }        ctx = {created: (new Date()).getTime(), worksheets: worksheetsXML};       workbookXML = format(tmplWorkbookXML, ctx);          var link = document.createElement("A");       link.href = uri + base64(workbookXML);       link.download = wbname || 'Workbook.xls';       link.target = '_blank';       document.body.appendChild(link);       link.click();       document.body.removeChild(link);     }   })(); 

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

Answer 3

Did not find anything on the internet to fix this, so I created my own. This is a working example that creates 2 or more sheets (1 sheet per table) and WORKS PROPERLY. Enjoy! :)

Run the example in https://jsfiddle.net/xvkt0yw9/

This is a ~reverse-engineering of .mht representation of XLS export (Web page). The sheet name is defined in the data-SheetName attribute of each table. The function can be easily transposed to other programming languages. (It is basically full of replace mes)

Also provided inline-style for cell number formating (As text, General, 2 decimal)

Note: the cell grid does show regardless of whether you set the option or not... Warning Do not indent the javascript function. (i don't know what happens to the XLS if you do, did not test, did not have to) Html: (Put the following content inside a test.html file and run it in your browser. Click on the button and open the exported Worksheet.xls)

<html>
<head>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.0/jquery.min.js"></script>
<script type="text/javascript">
var tablesToExcel = (function ($) {
    var uri = 'data:application/vnd.ms-excel;base64,'
    , html_start = `<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">`
    , template_ExcelWorksheet = `<x:ExcelWorksheet><x:Name>{SheetName}</x:Name><x:WorksheetSource HRef="sheet{SheetIndex}.htm"/></x:ExcelWorksheet>`
    , template_ListWorksheet = `<o:File HRef="sheet{SheetIndex}.htm"/>`
    , template_HTMLWorksheet = `
------=_NextPart_dummy
Content-Location: sheet{SheetIndex}.htm
Content-Type: text/html; charset=windows-1252

` + html_start + `
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
    <link id="Main-File" rel="Main-File" href="../WorkBook.htm">
    <link rel="File-List" href="filelist.xml">
</head>
<body><table>{SheetContent}</table></body>
</html>`
    , template_WorkBook = `MIME-Version: 1.0
X-Document-Type: Workbook
Content-Type: multipart/related; boundary="----=_NextPart_dummy"

------=_NextPart_dummy
Content-Location: WorkBook.htm
Content-Type: text/html; charset=windows-1252

` + html_start + `
<head>
<meta name="Excel Workbook Frameset">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<link rel="File-List" href="filelist.xml">
<!--[if gte mso 9]><xml>
 <x:ExcelWorkbook>
    <x:ExcelWorksheets>{ExcelWorksheets}</x:ExcelWorksheets>
    <x:ActiveSheet>0</x:ActiveSheet>
 </x:ExcelWorkbook>
</xml><![endif]-->
</head>
<frameset>
    <frame src="sheet0.htm" name="frSheet">
    <noframes><body><p>This page uses frames, but your browser does not support them.</p></body></noframes>
</frameset>
</html>
{HTMLWorksheets}
Content-Location: filelist.xml
Content-Type: text/xml; charset="utf-8"

<xml xmlns:o="urn:schemas-microsoft-com:office:office">
    <o:MainFile HRef="../WorkBook.htm"/>
    {ListWorksheets}
    <o:File HRef="filelist.xml"/>
</xml>
------=_NextPart_dummy--
`
    , 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 (tables, filename) {
        var context_WorkBook = {
            ExcelWorksheets:''
        ,   HTMLWorksheets: ''
        ,   ListWorksheets: ''
        };
        var tables = jQuery(tables);
        $.each(tables,function(SheetIndex){
            var $table = $(this);
            var SheetName = $table.attr('data-SheetName');
            if($.trim(SheetName) === ''){
                SheetName = 'Sheet' + SheetIndex;
            }
            context_WorkBook.ExcelWorksheets += format(template_ExcelWorksheet, {
                SheetIndex: SheetIndex
            ,   SheetName: SheetName
            });
            context_WorkBook.HTMLWorksheets += format(template_HTMLWorksheet, {
                SheetIndex: SheetIndex
            ,   SheetContent: $table.html()
            });
            context_WorkBook.ListWorksheets += format(template_ListWorksheet, {
                SheetIndex: SheetIndex
            });
        });

        var link = document.createElement("A");
        link.href = uri + base64(format(template_WorkBook, context_WorkBook));
        link.download = filename || 'Workbook.xls';
        link.target = '_blank';
        document.body.appendChild(link);
        link.click();
        document.body.removeChild(link);
    }
})(jQuery);
</script>
</head>
<body>
    <button onclick="tablesToExcel('#table_1,#table_2', 'WorkSheet.xls');">HTML Tables to XLS sheets</button>
    <table id="table_1" data-SheetName="My custom sheet 1">
        <thead>
            <tr >
                <td colspan="4" style="border-color:#fff;">
                    Left info
                </td>
                <td colspan="3" class="text-right" style="border-color:#fff;">
                    Right info
                </td>
            </tr>
            <tr>
                <th colspan="7" style="border-color:#fff;text-decoration:underline;font-size:2em;" class="text-center">
                    Title
                </th>
            </tr>
            <tr>
                <th colspan="7" style="border-left-color:#fff; border-right-color:#fff;border-top-color:#fff;text-decoration:underline;font-size:1.5em;" class="text-center">
                    Subtitle
                </th>
            </tr>
            <tr>
                <th colspan="7" style="border-left-color:#fff;border-right-color:#fff;border-top-color:#fff;height:50px;">
                    Spacer
                </th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td style="mso-number-format:'\@'">1</td>
                <td style="mso-number-format:'General'">2</td>
                <td style="mso-number-format:'0\.00'">3</td>
                <td>info</td>
                <td>info</td>
                <td>info</td>
                <td>info</td>
            </tr>
        </tbody>
    </table>
    <table id="table_2" data-SheetName="My custom worksheet 2">
        <thead>
            <tr >
                <td colspan="2" style="border-color:#fff;">
                    Left info 2
                </td>
                <td colspan="2" class="text-right" style="border-color:#fff;">
                    Right info 2
                </td>
            </tr>
            <tr>
                <th colspan="4" style="border-color:#fff;text-decoration:underline;font-size:2em;" class="text-center">
                    Title 2
                </th>
            </tr>
            <tr>
                <th colspan="4" style="border-left-color:#fff; border-right-color:#fff;border-top-color:#fff;text-decoration:underline;font-size:1.5em;" class="text-center">
                    Subtitle 2
                </th>
            </tr>
            <tr>
                <th colspan="4" style="border-left-color:#fff;border-right-color:#fff;border-top-color:#fff;height:50px;">
                    Spacer 2
                </th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td style="mso-number-format:'\@'">3</td>
                <td style="mso-number-format:'General'">4</td>
                <td style="mso-number-format:'0\.00'">5</td>
                <td>info2</td>
                <td>info3</td>
                <td>info4</td>
                <td>info5</td>
            </tr>
        </tbody>
    </table>
</body>
</html>