CODEWITHSUNDEEP
javascripthtmlxmlexcelhtml-table
Bondifrench
Bondifrench

Reputation: 1282

Converting multiple html tables to Excel workbook with multiple sheets

I am trying to export multiple tables from a webpage to an Excel workbook with one worksheet per table, has any one managed to do that without needing to transforming the tables into <rows> and leveraging the html <table> xml, ie inside <body></body>.

Currently I am using the below function but, while it does create multiple worksheets, it puts all tables into the first worksheet.

 function arrayToExcel(tablesId, filename) {
  var uri = 'data:application/vnd.ms-excel;base64,';
  var worksheetTemplate = '<x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions><table>{table}</table></x:ExcelWorksheet>';
  var format = function(s, c) { return s.replace(/{(\w+)}/g, function(m, p) { return c[p]; }) }
  var worksheets = tablesId.map(function(name){
      return format(worksheetTemplate, {worksheet: name});
    }).join('');
  var tables = tablesId.map(function(txt){
      var table = document.getElementById(txt).innerHTML;
      return format(tableTemplate, {table});
    }).join('');
  var formattedXML = '<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><o:DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"><o:Author>Dominik Dumaine</o:Author><o:Created>'+ (new Date()).getTime() +'</o:Created></o:DocumentProperties>'
          +'<x:ExcelWorkbook><x:ExcelWorksheets>'
          + worksheets
          +'</x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--><meta http-equiv="content-type" content="text/plain; charset=UTF-8"/></head><body>'
          + tables
          +'</body></html>'
    , base64 = function(s) { return window.btoa(unescape(encodeURIComponent(s))) }
window.location.href = uri + base64(formattedXML);
}

used like this arrayToExcel(["tbl1","tbl2"], "Name of Workbook")

Has anybody have any suggestion as to how I can amend the above so the different tables go to different worksheets? The HTML would look like this with additional inline CSS:

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

N.B.: I have seen Butani Vijay's answer to How to convert html table to excel with multiple sheet?, it does not satisfy my requirements

Upvotes: 4

Views: 4450

Answers (2)

oum
oum

Reputation: 9

Install table2excel (you only need the JavaScript file table2excel.js):

<head><script src="table2excel.js"></script></head>

Then the script is:

const table1 = document.getElementById('table1');
const table2 = document.getElementById('table2');

document.getElementById('downloadexcel').addEventListener('click', function(){
var table2excel = new Table2Excel();
var tables = [table1,table2];
table2excel.export(tables,'multiple_table');
});

If you want to give a name to each sheet, use data-excel-name inside the table tag.
Example:

<table id="tabel1" data-excel-name="sheet_name_1">
<table id="tabel2" data-excel-name="sheet_name_2">

Upvotes: 1

PRASANTH
PRASANTH

Reputation: 1

https://github.com/SheetJS/js-xlsx The table_to_book and table_to_sheet utility functions take a DOM TABLE element and iterate through the child nodes.

Upvotes: 0

Related Questions