如何将HTML表导出到具有不同表的单个excel工作簿?

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:

我有下面的代码，我在单击按钮时调用它，它可以帮助我传递一个html表id，并将它下载到一个excel工作簿中。这工作得很好，但是我想传递多个表id，以便在同一个excel工作簿的不同表中获得不同表的数据。我无法修改这个函数来解决这个问题。此外，我希望保留类似的格式，并使用这里使用的自定义文件名。谁能帮我吗?请找到我的代码如下:

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

2 个解决方案

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

您可以使用SheetJS创建一个包含多个表和格式化(包括colspan和rowspan)的Excel工作簿。这里有一个讨论线程和例子张贴在这个线程:

1) Workbook with Multiple Sheets

1)多张纸的工作簿

Library: https://github.com/SheetJS/js-xlsx
图书馆:https://github.com/SheetJS/js-xlsx
Discussion: https://github.com/SheetJS/js-xlsx/issues/664
讨论:https://github.com/SheetJS/js-xlsx/issues/664
Demo (Click Excel link): https://jsfiddle.net/97ajn9wm/1/ (by reviewher)
演示(点击Excel链接):https://jsfiddle.net/97ajn9wm/1/ (reviewher)

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.

我已经将reviewher的示例代码从JSFiddle转移到Stack Overflow以方便查看。运行代码片段，然后单击生成的Excel链接，下载带有两个表的Excel文件。

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

2)与格式化工作表

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.

这是另一个演示，同样的GitHub线程显示了多个colspan、多个rowspan、背景色、字体颜色、字体大小等等。

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

如上所示，单击Run代码片段，然后单击生成的Excel链接以下载Excel文件。

Library: https://github.com/SheetJS/js-xlsx (same as above)
库:https://github.com/SheetJS/js-xlsx(如上所示)
Discussion: https://github.com/SheetJS/js-xlsx/issues/664 (same as above)
讨论:https://github.com/SheetJS/js-xlsx/issues/664(与上面一样)
Demo (Click Excel link): https://jsfiddle.net/Herosony/97ajn9wm/ (by HeroSony)
演示(点击Excel链接):https://jsfiddle.net/Herosony/97ajn9wm/ (HeroSony)

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>

#2

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

根据您的代码，您使用了数据:application/vnd。ms-excel，你可以做这样的事情。

JS Function

JS函数

  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)

HTML(演示的HTML示例代码)

 <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

链接到JSFiddle

#1