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

javascript html excel jsp export-to-excel
11,026

Solution 1

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

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>

2) Worksheet with Formatting

Here is another demo the same GitHub thread that shows multiple colspan, multiple rowspan, background color, font color, font size, etc. This example is from HeroSony on GitHub.

As above, click Run code snippet and then click the resulting Excel link to download the Excel file.

function prepareTable() {
	var str = "",
		header = "",
		graphImg;
	
	
	header = '\uFEFF<h2 style="text-align:center;">Google</h2>';
	
	str = '<table border="1">'
		+'<tr><td style="text-align:center" colspan="6">Yahoo</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>';
        
		return header + str;
}


function doExcel1 () {
	var blob,
		template = prepareTable();

	blob = new Blob([template], {
	    type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"
	});
	
	saveAs(blob, "test.xls");
}
<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>

Solution 2

As per your code you have used data:application/vnd.ms-excel You can do something like this .

JS Function 

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

HTML (sample html code for demonstration)

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

Link to JSFiddle

Share:
11,026
Ashesh Das
Author by

Ashesh Das

Updated on June 05, 2022

Comments

  • Ashesh Das
    Ashesh Das almost 2 years

    I have the following code which I call on a button click and which helps me pass an html table id and have it downloaded in a single Excel workbook. This works fine, but I want to pass multiple table ids to get the data of different tables in different sheets of the same Excel workbook. I am unable to modify this function to address that issue.

    Moreover, I want to retain similar kind of formatting and use the customized file name as I have used here. Can anyone help me? Please find my code below:

      <script>
   function fnExcelReport()
   {
       var tab_text="<table border='2px'><tr bgcolor='#87AFC6'>";
       var textRange; var j=0;
       tab = document.getElementById('data'); // id of table : I want to pass more than one ids here

       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(); 
      var e = document.getElementById("configselect");
      var strUser = e.options[e.selectedIndex].text;
      var f = document.getElementById("configmonth");
      var strUser1 = f.options[e.selectedIndex].text;
      var filename = strUser+"_"+strUser1+"_"+document.getElementById('configkpi').value+"_"+document.getElementById('configyear').value+".xls";
      //alert(filename);
      sa=txtArea1.document.execCommand("SaveAs",true,filename);
   }  
  // else //other browser not tested on IE 11
   //   sa = window.open('data:application/vnd.ms-excel,' + encodeURIComponent(tab_text));  
   //  return (sa);
       else {//other browser 
           var a = document.createElement('a');
           var data_type = 'data:application/vnd.ms-excel';
           var table_div = tab_text;    //Your tab_text   
           var table_html = table_div.replace(/ /g, '%20');
           //alert(table_html)
           a.href = data_type + ', ' + table_html;
           //setting the file name
            var e = document.getElementById("configselect");
            var strUser = e.options[e.selectedIndex].text;
            var f = document.getElementById("configmonth");
            var strUser1 = f.options[e.selectedIndex].text;
           var filename = strUser+"_"+strUser1+"_"+document.getElementById('configkpi').value+"_"+document.getElementById('configyear').value+".xls";
           a.download = filename;
           //triggering the function
           a.click();

       }

       return (sa);
  }

  • oxygen
    oxygen over 5 years
    The 2nd snippet sends an HTML file with a HTML table to download.
  • Ikechukwu
    Ikechukwu over 3 years
    This is a very underrated answer. Thanks

Recents

Why Is PNG file with Drop Shadow in Flutter Web App Grainy?
How to troubleshoot crashes detected by Google Play Store for Flutter app
Cupertino DateTime picker interfering with scroll behaviour
Why does awk -F work for most letters, but not for the letter "t"?
Flutter change focus color and icon color but not works
How to print and connect to printer using flutter desktop via usb?
Critical issues have been reported with the following SDK versions: com.google.android.gms:play-services-safetynet:17.0.0
Flutter Dart - get localized country name from country code
navigatorState is null when using pushNamed Navigation onGenerateRoutes of GetMaterialPage
Android Sdk manager not found- Flutter doctor error
Flutter Laravel Push Notification without using any third party like(firebase,onesignal..etc)
How to change the color of ElevatedButton when entering text in TextField

Related

Exclude Specific Columns when exporting data from HTML table to excel
How do I export multiple html tables to excel?
JavaScript - export HTML table data into Excel
Download Excel file in xlsx format using Javascript (exporting html tables into Excel)
Convert table html to Excel in javascript
Export HTML table to excel with text and images
Multiple form fields with same 'name' attribute not posting
JavaScript function scope between script tags
In jquery how to get the values of a particular row of a table that is selected using checkbox?
How can I asynchronously show/hide div in JSP using Java