如何将HTML表导出到具有不同工作表的单个Excel工作簿？
问题描述：
我有以下代码，我在单击按钮时调用它，这有助于我传递一个html表ID并将其下载到一个Excel工作簿中。这工作正常，但我想传递多个表ID以获取同一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:
<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);
}
答
您可以使用 SheetJS 。以下是该主题中发布的讨论主题和示例：
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）包含多个工作表的工作簿
- 图书馆： https://github.com/SheetJS/ js-xlsx
- 讨论： https://github.com/SheetJS/js-xlsx/issues/664
- 演示（点击
Excel链接）： https://jsfiddle.net/97ajn9wm/1/ （评论员）
- Library: https://github.com/SheetJS/js-xlsx
- Discussion: https://github.com/SheetJS/js-xlsx/issues/664
- Demo (Click
Excellink): https://jsfiddle.net/97ajn9wm/1/ (by reviewher)
我搬家了 reviewher 从JSFiddle到Stack Overflow的示例代码，以便于查看。运行代码段，然后单击生成的Excel链接以下载两页的Excel文件。
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）带格式的工作表
这是另一个演示相同GitHub线程的演示，显示多个colspan，多行，背景颜色，字体颜色，字体大小等。此示例来自GitHub上的 HeroSony 。
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.
如上所述，单击运行代码段，然后单击生成的 Excel 链接下载Excel文件。
As above, click Run code snippet and then click the resulting Excel link to download the Excel file.
- 图书馆： https://github.com/SheetJS/js-xlsx （与上述相同）
- 讨论： https://github.com/SheetJS/js-xlsx/issues/664（与上述相同）
- 演示（点击
Excel链接）： https://jsfiddle.net/Herosony/97ajn9wm/ （由 HeroSony ）
- Library: https://github.com/SheetJS/js-xlsx (same as above)
- Discussion: https://github.com/SheetJS/js-xlsx/issues/664 (same as above)
- Demo (Click
Excellink): https://jsfiddle.net/Herosony/97ajn9wm/ (by 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>