How to Convert HTML Table to Excel with Multiple Sheet

How to export HTML tables to a single Excel workbook with different sheets?

You can create an Excel workbook with multiple sheets and with formatting (including colspan and rowspan) using SheetJS. Here is a discussion thread and examples posted in that thread:

1) Workbook with Multiple Sheets

Library: https://github.com/SheetJS/js-xlsx
Discussion: https://github.com/SheetJS/js-xlsx/issues/664
Demo (Click Excel link): https://jsfiddle.net/97ajn9wm/1/ (by reviewher)

I've moved reviewher's example code from JSFiddle to Stack Overflow for easier viewing. Run the code snippet and then click the resulting Excel link to download an Excel file with two sheets.

function prepareTable(i) { var str = "",  header = "",  graphImg;   header = '<html><h2 style="text-align:center;">Google' + i + '</h2>';  str = '<table border="1">'  +'<tr><td style="text-align:center" colspan="6">Yahoo' + i + '</td></tr>'   +'<tr><td style="font-weight:bold" colspan="6">(2017.03.20)</td></tr>'  +'<thead>'  +'    <tr style="background-color:#788496; color: #ffffff">'  +'      <th scope="col" rowspan="2">'  +'        <div>Yahoo</div>'  +'      </th>'  +'      <th scope="col">'  +'        <div class="tar">Yahoo(2017-01)</div>'  +'      </th>'  +'      <th scope="col" colspan="2">'  +'        <div class="tar">Yahoo(2016-12)</div>'  +'      </th>'  +'      <th scope="col" colspan="2">'  +'        <div class="tar">Yahoo(2016-12)</div>'  +'      </th>'  +'    </tr>'  +'    <tr style="background-color:#788496; color: #ffffff">'  +'      <th height="40" align="right">'  +'        <div>Yahoo</div>'  +'      </th>'  +'      <th align="right">'  +'        <div>Yahoo</div>'  +'      </th>'  +'      <th align="right">'  +'        <div>Yahoo</div>'  +'      </th>'  +'      <th align="right">'  +'        <div>Yahoo</div>'  +'      </th>'  +'      <th align="right">'  +'        <div>Yahoo</div>'  +'      </th>'  +'    </tr>'  +'</thead>'  +'  <tbody>'        +'    <tr style="text-align: right">'    +'      <td style="padding:0 20px 0 0">'    +'        <div>NAME</div>'    +'      </td>'    +'      <td style="width: 150px;">'    +'        <div>311,210</div>'    +'      </td>'    +'      <td style="width: 150px;">'    +'        <div>311,210</div>'    +'      </td>'    +'      <td style="width: 150px;">'    +'        <div>311,210%</div>'    +'      </td>'    +'      <td style="width: 150px;">'    +'        <div>311,210</div>'    +'      </td>'    +'      <td style="width: 150px;">'    +'        <div>311,210%</div>'    +'      </td>'    +'    </tr>'   +'  </tbody>'    +'</table></html>';          return header + str;}
function s2ab(s) {        var buf = new ArrayBuffer(s.length);        var view = new Uint8Array(buf);        for (var i=0; i!=s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;        return buf;}
function doExcel1 () { var blob,   wb = {SheetNames:[], Sheets:{}};        var ws1 = XLSX.read(prepareTable(1), {type:"binary"}).Sheets.Sheet1;        wb.SheetNames.push("Sheet1"); wb.Sheets["Sheet1"] = ws1;          var ws2 = XLSX.read(prepareTable(2), {type:"binary"}).Sheets.Sheet1;        wb.SheetNames.push("Sheet2"); wb.Sheets["Sheet2"] = ws2;        console.log(ws1); console.log(ws2); console.log(wb);        blob = new Blob([s2ab(XLSX.write(wb, {bookType:'xlsx', type:'binary'}))], {     type: "application/octet-stream" });  saveAs(blob, "test.xlsx");}

<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.10.3/xlsx.full.min.js"></script><script src="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/1.3.3/FileSaver.min.js"></script>
<a href="javascript:" class="btn_style1 excel" onclick="doExcel1()"><span>Excel</span></a>

Python: Writing Multiple HTML tables to separate Excel sheets

Hi here is a modification to your code;

from pandas import ExcelWriter

with ExcelWriter('multitest.xlsx') as writer:
for i, df in enumerate(dfhtml):
    df.to_excel(writer,'sheet%s' % i)
writer.save()

How do I export multiple html tables to excel?

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

How to Convert HTML Table to Excel with Multiple Sheet

How to export HTML tables to a single Excel workbook with different sheets?

Python: Writing Multiple HTML tables to separate Excel sheets

How do I export multiple html tables to excel?

Related Topics

Leave a reply