js html table转excel文件
参考资料:
70行代码实现vue+sheetJs导出excel功能
sheetJs的git项目代码
代码太长建议直接粘贴复制
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Document</title>
<style>
td,th,p {
background-color:#ffffff;
}
p{
width: 560px;
padding: 20px;
}
body {
background-color: rgb(172, 172, 172);
}
</style>
<script type="text/javascript" language="javascript">
var idTmr;
function getExplorer() {
var explorer = window.navigator.userAgent;
//ie
if (explorer.indexOf("MSIE") >= 0) {
return 'ie';
}
//firefox
else if (explorer.indexOf("Firefox") >= 0) {
return 'Firefox';
}
//Chrome
else if (explorer.indexOf("Chrome") >= 0) {
return 'Chrome';
}
//Opera
else if (explorer.indexOf("Opera") >= 0) {
return 'Opera';
}
//Safari
else if (explorer.indexOf("Safari") >= 0) {
return 'Safari';
}
}
function table2excel(tableid,ExcelName) { //整个表格拷贝到EXCEL中
if (getExplorer() == 'ie') {
var curTbl = document.getElementById(tableid);
var oXL = new ActiveXObject("Excel.Application");
//创建AX对象excel
var oWB = oXL.Workbooks.Add();
//获取workbook对象
var xlsheet = oWB.Worksheets(1);
//激活当前sheet
var sel = document.body.createTextRange();
sel.moveToElementText(curTbl);
//把表格中的内容移到TextRange中
sel.select();
//全选TextRange中内容
sel.execCommand("Copy");
//复制TextRange中内容
xlsheet.Paste();
//粘贴到活动的EXCEL中
oXL.Visible = true;
//设置excel可见属性
try {
var fname = oXL.Application.GetSaveAsFilename("Excel.xls", "Excel Spreadsheets (*.xls), *.xls");
}
catch (e) {
print("Nested catch caught " + e);
}
finally {
oWB.SaveAs(fname);
oWB.Close(savechanges = false);
//xls.visible = false;
oXL.Quit();
oXL = null;
//结束excel进程,退出完成
//window.setInterval("Cleanup();",1);
idTmr = window.setInterval("Cleanup();", 1);
}
}
else {
tableToExcel(tableid,ExcelName)
}
}
function Cleanup() {
window.clearInterval(idTmr);
CollectGarbage();
}
var tableToExcel = (function () {
var uri = 'data:text/xls;charset=utf-8,\ufeff,',
template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>',
base64 = function (s) {
return window.btoa(encodeURIComponent(s))
},
format = function (s, c) {
return s.replace(/{(\w+)}/g,
function (m, p) {
return c[p];
}
)
}
return function (table, excelName,name) {
if (!table.nodeType)
table = document.getElementById(table)
var ctx =
{
worksheet: name || 'Worksheet',
table: table.innerHTML
}
//window.location.href = uri + base64(format(template, ctx))
var downloadLink = document.createElement("a");
downloadLink.href = uri + format(template, ctx);
downloadLink.download = excelName +'.xls';
document.body.appendChild(downloadLink);
downloadLink.click();
document.body.removeChild(downloadLink);
}
}
)()
</script>
</head>
<body>
<p>
第二个列表是可以直接下载,但是第一个列表下载后内容为空,因为第一个列表有style属性设置的样式造成Excel转换失败显示内容为空</p>
<table id="copyTable" style="border-collapse: collapse; margin-top: 1em; margin-bottom: 1em;">
<thead>
<tr>
<td colspan="4" style="text-align: left;">
<div align="center" style="color:#000;font-weight:600;font-size:18px; padding: 8px 8px 8px"> 俄文为
</div>
</td>
</tr>
<tr>
<th
style="color: #112055; border-bottom: 1px solid #000; border-top: 2px solid #000; text-align: center;">
变量</th>
<th
style="color: #112055; border-bottom: 1px solid #000; border-top: 2px solid #000; text-align: center;">
俄文我</th>
<th
style="color: #112055; border-bottom: 1px solid #000; border-top: 2px solid #000; text-align: center;">
俄文我</th>
<th
style="color: #112055; border-bottom: 1px solid #000; border-top: 2px solid #000; text-align: center;">
看i额外为i俄u我</th>
</tr>
</thead>
<tbody>
<tr>
<td colspan="4" style="padding: 5px 50px 5px; color: #000; font-weight: 900; text-align: left;">诶诶我
</td>
</tr>
<tr>
<td style="padding: 5px 50px 5px; color: #000; text-align: center;">1</td>
<td style="padding: 5px 50px 5px; color: #000; text-align: center;">626</td>
<td style="padding: 5px 50px 5px; color: #000; text-align: center;">7622234</td>
<td style="padding: 5px 50px 5px; color: #000; text-align: center;">762234</td>
</tr>
</tbody>
</table>
<table id="copyTable1" width="600px">
<thead>
<tr>
<td colspan="4">
<div align="center"> 萨科睡觉啊哭
</div>
</td>
</tr>
<tr>
<th>变量</th>
<th>变量</th>
<th>变量</th>
<th>变量</th>
</tr>
</thead>
<tbody>
<tr>
<td colspan="4">阿舒拉节暗示
</td>
</tr>
<tr>
<td>1</td>
<td>626</td>
<td>7634</td>
<td>7634</td>
</tr>
<tr>
<td>2</td>
<td>174</td>
<td>2122</td>
<td>976</td>
</tr>
</tbody>
</table>
</br>
<input id="Button1" type="button" value="导出EXCEL-有style样式的" onclick="javascript:table2excel('copyTable','有style样式的')" />
<input id="Button1" type="button" value="导出EXCEL-没有style样式的" onclick="javascript:table2excel('copyTable1','没有style样式的')" />
</body>
</html>
复制table粘贴到excel中
clipboardJS实现(推荐)
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Document</title>
<style>
td,
th,
p {
background-color:#ffffff;
}
p{
width: 560px;
padding: 20px;
}
body {
background-color: rgb(172, 172, 172);
}
</style>
<!-- clipboardJS下载地址 https://github.com/zenorocha/clipboard.js/archive/master.zip -->
<script src="./clipboard.min.js"></script>
<script type="text/javascript" language="javascript">
function copy() {
debugger
let clipboardInstance = new ClipboardJS('.btn', {
target: function (trigger) {
debugger
return trigger.nextElementSibling || document.getElementById('copyTable')
}
})
clipboardInstance.on('success', e => {
console.log('复制成功') //这里你如果引入了elementui的提示就可以用,没有就注释即可
// 释放内存
clipboardInstance.destroy()
})
clipboardInstance.on('error', e => {
// 不支持复制
console.log('该浏览器不支持自动复制')
// 释放内存
clipboardInstance.destroy()
})
}
</script>
</head>
<body>
<p>列表可是使用简单的复制粘贴,粘贴到excel文件中.
<br/>
需要注意的是excel文件只是别 十六进制颜色 “#222222”,不识别rgb和英文描述的颜色 "rgb(1,255,244)", "red".
<br/>
如果是rgb或者英文描述的颜色,复制出来的样式会丢失。
</p>
<table id="copyTable" style="border-collapse: collapse; margin-top: 1em; margin-bottom: 1em;">
<thead>
<tr>
<td colspan="4" style="text-align: left;">
<div align="center" style="color:#000;font-weight:600;font-size:18px; padding: 8px 8px 8px"> 俄文为
</div>
</td>
</tr>
<tr>
<th
style="color: #112055; border-bottom: 1px solid #000; border-top: 2px solid #000; text-align: center;">
变量</th>
<th
style="color: #112055; border-bottom: 1px solid #000; border-top: 2px solid #000; text-align: center;">
俄文我</th>
<th
style="color: #112055; border-bottom: 1px solid #000; border-top: 2px solid #000; text-align: center;">
俄文我</th>
<th
style="color: #112055; border-bottom: 1px solid #000; border-top: 2px solid #000; text-align: center;">
看i额外为i俄u我</th>
</tr>
</thead>
<tbody>
<tr>
<td colspan="4" style="padding: 5px 50px 5px; color: #000; font-weight: 900; text-align: left;">诶诶我
</td>
</tr>
<tr>
<td style="padding: 5px 50px 5px; color: #000; text-align: center;">1</td>
<td style="padding: 5px 50px 5px; color: #000; text-align: center;">626</td>
<td style="padding: 5px 50px 5px; color: #000; text-align: center;">7622234</td>
<td style="padding: 5px 50px 5px; color: #000; text-align: center;">762234</td>
</tr>
</tbody>
</table>
</br>
<button class="btn" data-clipboard-target="#copyTable" onclick="copy()">copy table</button>
</body>
</html>
clipboardJS参考文档
clipboardJS复制demo
clipboard.js实现将某tr下所有td的数据复制到系统粘贴板
execCommand复制(不推荐)
待更新。。。。。。
execCommand 参考文档
execCommand复制demo
execCommand不推荐的原因
js获取复制的内容
document.getSelection().toString()
https://www.cnblogs.com/zimengxiyu/p/11158934.html
js获取粘贴板的内容
https://jingyan.baidu.com/article/425e69e6875d03fe15fc1686.html