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

javascript html excel jsp export-to-excel
Ashesh Das picture Ashesh Das · May 16, 2018 · Viewed 7.8k times · Source

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

Answer

Grokify picture Grokify · May 30, 2018

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>

Related questions

Export html table data to Excel using JavaScript / JQuery is not working properly in chrome browser

I have a HTML table in velocity template. I want to export the html table data to excel using either java script or jquery, comatibale with all browser. I am using below script <script type="text/javascript"> function …

Read More
Copy/Paste from Excel to a web page

Is there a standard way or library to copy and paste from a spreasheet to a web form? When I select more than one cell from Excel I (obviously) lose the delimiter and all is pasted into one cell of …

Read More
Exporting HTML tables to Excel (.xls) in a separate sheet

I've got a simple HTML page (generated by an external application) that contains a table view. I am trying to scrape off the tables from the page and put them in an Excel workbook. I have managed to put the …

Read More