bulk email server

Results 1 to 4 of 4
  1. #1
    Member
    Join Date
    Feb 2018
    Location
    Hyderabad
    Posts
    33
    Feedback Score
    0

    Export HTML table to excel worksheet?

    VPS Hosting
    How to export HTML tables to a single excel workbook with different sheets?


    You can create an Excel workbook with multiple sheets and retain 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)

  2. #2
    Member
    Join Date
    Jul 2016
    Posts
    62
    Feedback Score
    0
    Use the following code to get that:

    Code:
    <script>
    
    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>
    <a href="javascript:" class="btn_style1 excel" onclick="doExcel1()"><span>Excel</span></a>

  3. #3
    Member
    Join Date
    May 2014
    Location
    India
    Posts
    66
    Feedback Score
    0
    Quote Originally Posted by sandysmilz View Post
    Use the following code to get that:

    Code:
    <script>
    
    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>
    <a href="javascript:" class="btn_style1 excel" onclick="doExcel1()"><span>Excel</span></a>
    I would suggest manual task is error free but if you are running out of time then you can try this.

  4. #4
    Member
    Join Date
    May 2014
    Location
    India
    Posts
    66
    Feedback Score
    0
    Quote Originally Posted by sandysmilz View Post
    Use the following code to get that:

    Code:
    <script>
    I would suggest manual task is error free but if you are running out of time then you can try this.

 

 

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •