1

I have a code which export html table into excel file. But now i have to export html tables in multiple tabs of single excel file. Here is my code for reference. In this code i have three different tables. Once i click "Export To Excel". Excel will be downloaded. For now there having single tab in the excel file but now i want excel file should hold multiple tabs. Each tab should hold different table records.

<script type="text/javascript">
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.getElementById(table)
    var ctx = {worksheet: name || 'Worksheet', table: table.innerHTML}
    window.location.href = uri + base64(format(template, ctx))
  }
})()
</script>

<input type="button" onclick="tableToExcel('testTable', 'W3C Example Table')" value="Export to Excel">
<br><br>


<p>Table 1</p>
<table id="testTable" border="1">
  <thead>
    <tr>
      <th>Num1</th>
      <th>Num2</th>
      <th>Num3</th>
      <th>Num4</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>11</td>
      <td>12</td>
      <td>13</td>
      <td>14</td>
    </tr>
    <tr>
      <td>21</td>
      <td>22</td>
      <td>23</td>
      <td>24</td>
    </tr>
    <tr>
      <td>31</td>
      <td>32</td>
      <td>33</td>
      <td>34</td>
    </tr>
    <tr>
      <td>41</td>
      <td>42</td>
      <td>43</td>
      <td>44</td>
    </tr>
  </tbody>
</table>


<p>Table 2</p>
<table id="" border="1">
  <thead>
    <tr>
      <th>Alpha1</th>
      <th>Alpha2</th>
      <th>Alpha3</th>
      <th>Alpha4</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>a</td>
      <td>b</td>
      <td>c</td>
      <td>d</td>
    </tr>
    <tr>
      <td>e</td>
      <td>f</td>
      <td>g</td>
      <td>h</td>
    </tr>
    <tr>
      <td>i</td>
      <td>j</td>
      <td>k</td>
      <td>l</td>
    </tr>
    <tr>
      <td>m</td>
      <td>n</td>
      <td>o</td>
      <td>p</td>
    </tr>
  </tbody>
</table>

<p>Table 3</p>
<table id="" border="1">
  <thead>
    <tr>
      <th>Fruit1</th>
      <th>Fruit2</th>
      <th>Fruit3</th>
      <th>Fruit4</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>apple</td>
      <td>apple</td>
      <td>apple</td>
      <td>apple</td>
    </tr>
    <tr>
      <td>orange</td>
      <td>orange</td>
      <td>orange</td>
      <td>orange</td>
    </tr>
    <tr>
      <td>mango</td>
      <td>mango</td>
      <td>mango</td>
      <td>mango</td>
    </tr>
    <tr>
      <td>graphs</td>
      <td>graphs</td>
      <td>graphs</td>
      <td>graphs</td>
    </tr>
  </tbody>
</table>

Sample Excel File Pic

Nithya
  • 1,029
  • 2
  • 14
  • 27
  • You need to create a native spreadheet format file such as BIFF (xls) or OfficeOpenXML (xlsx) using one of the many libraries like PHPExcel that can be used to create native format files – Mark Baker Dec 11 '17 at 10:24
  • Like this? : https://stackoverflow.com/questions/24636956/how-do-i-export-multiple-html-tables-to-excel – Nope Dec 11 '17 at 10:25

3 Answers3

0

I think You Need This

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

//console.log(workbookXML);

      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);
    }
  })();
  
  <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>
Karan Mehta
  • 395
  • 2
  • 9
0

I suggest you use the DataTables - https://datatables.net/ for this. They have inbuilt methods to do so.

But if you're looking to customize more, then there's no better library than PHPExcel - https://github.com/PHPOffice/PHPExcel

Hardik Somaiya
  • 41
  • 6
0

html tables to excel conversion into multi table through jquery libary

through this you easily convert your html table into excel file into multiple tabs for more help please take a look at following

html file 

<script src="https://cdn.jsdelivr.net/alasql/0.3/alasql.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.7.12/xlsx.core.min.js"></script>

<button onclick="saveFile()">Save XLSX file</button>

javascript file 

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]]);
}
pardeep
  • 359
  • 1
  • 5
  • 7