//导出excelfunction tableToExcel(){
var arrSor = ["sorttable10","sorttable30","sorttable60","sorttable120"]
let str = ""
for (var k = 0k <arrSor.lengthk++) {
var tab=document.getElementById(arrSor[k])
var rows=tab.rows
const jsonData = []
for(var i=2i<rows.lengthi++){ //遍历表格的行
var json = {}
for(var j=0j<rows[i].cells.lengthj++){ //遍历每行的列
json[(i+1)+"-"+(j+1)] = rows[i].cells[j].innerHTML
}
jsonData.push(json)
}
//列标题
let str1 = "<tr><td align='center' colspan='5'><b>最大"+arrSor[k].substring(9,arrSor[k].length)+"分钟雨量</b></td></tr>"
let str2 = "<tr align='center'><th>站点</th><th>站名</th><th>雨量最大值</th><th>降水时段</th><th>落入最大降水时段</th></tr>"
//循环遍历，每行加入tr标签，每个单元格加td标签
for(let i = 0 i <jsonData.length i++ ){
str2+='<tr>'
for(let item in jsonData[i]){
//增加\t为了不让表格显示科学计数法或者其他格式
var itemTem= jsonData[i][item]
if (itemTem == "暂无数据") {
str2+=`<td colspan='5' align='center'>${ itemTem + '\t'}</td>`
}else {
str2+=`<td align='center'>${ itemTem + '\t'}</td>`
}
}
str2+='</tr>'
}
let str3 = "<tr><td colspan='5'></td></tr>"
str += (str1 + str2 + str3)
}
let worksheet = '雨量最大值'
let uri = 'data:application/vnd.ms-excelbase64,'
//下载的表格模板数据
let 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><meta charset='UTF-8'><!--[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]-->
<style type="text/css">
table {border: 1px solid #000000}
table tr td b {background:#FFFFFFcolor:#3D3D3Dfont-size:24pxborder: 1px solid #000000}
table th {background:#AEE1FEcolor:#3D3D3Dfont-size:20pxborder: 1px solid #000000}
table td {background:#FFFFFFcolor:#3D3D3Dfont-size:20pxborder: 1px solid #000000}</style>
</head><body><table>${str}</table></body></html>`
//下载模板
// window.location.href = uri + this.base64(template)
var link = document.createElement("a")
link.href = uri + this.base64(template)
link.download = "雨量最大值-" +new Date().format("yyyy年MM月dd日 h时")+ ".xls"
link.style = "visibility:hidden"
document.body.appendChild(link)
link.click()
document.body.removeChild(link)}//输出base64编码function base64 (template) {
return window.btoa(unescape(encodeURIComponent(template))) }
解析：
遍历取出表，顺序是行从上往下，列从左往右，将数据存进数组，下面再拼接成表。
image.png
参考文章：https://blog.csdn.net/hhzzcc_/article/details/80419396
第一种方法（大量数据导出）
//导出excelfunction tableToExcel() {
var arrSor = ["sorttable10","sorttable30","sorttable60","sorttable120"]
let str = ""
for (var k = 0k <arrSor.lengthk++) {
var tab=document.getElementById(arrSor[k])
var rows=tab.rows
const jsonData = []
for(var i=2i<rows.lengthi++){ //遍历表格的行
var json = {}
for(var j=0j<rows[i].cells.lengthj++){ //遍历每行的列
if (rows[i].cells[j].outerHTML.indexOf("rgb(255, 255, 0)") != -1) {
json["yellow"+(i+1)+"-"+(j+1)] = rows[i].cells[j].innerHTML
}else {
json[(i+1)+"-"+(j+1)] = rows[i].cells[j].innerHTML
}
}
jsonData.push(json)
}
//列标题
let str1 = "<tr><td colspan='5' align='center' style='background-color:#FFFFFFfont-size:24pxborder: 1px solid #000000'><b>最大"
+arrSor[k].substring(9,arrSor[k].length)+"分钟雨量</b></td></tr>"
let str2 = "<tr>" +
"<th style='background-color:#AEE1FEfont-size:22pxborder: 1px solid #000000' align='center'>站点</th>" +
"<th style='background-color:#AEE1FEfont-size:22pxborder: 1px solid #000000' align='center'>站名</th>" +
"<th style='background-color:#AEE1FEfont-size:22pxborder: 1px solid #000000' align='center'>雨量最大值</th>" +
"<th style='background-color:#AEE1FEfont-size:22pxborder: 1px solid #000000' align='center'>降水时段</th>" +
"<th style='background-color:#AEE1FEfont-size:22pxborder: 1px solid #000000' align='center'>落入最大降水时段</th></tr>"
//循环遍历，每行加入tr标签，每个单元格加td标签
for(let i = 0 i <jsonData.length i++ ){
str2+="<tr align='center'>"
for(let item in jsonData[i]){
if (item.indexOf("yellow") != -1) {//取列数等于3
//增加\t为了不让表格显示科学计数法或者其他格式
var itemTem= jsonData[i][item]
if (itemTem == "暂无数据") {
str2+=`<td colspan='5' style='background-color:#FFFF00font-size:22pxborder: 1px solid #000000'> ${ itemTem + '\t'}</td>`
}else {
str2+=`<td style='background-color:#FFFF00font-size:22pxborder: 1px solid #000000'> ${ itemTem + '\t'}</td>`
}
}else {
//增加\t为了不让表格显示科学计数法或者其他格式
var itemTem= jsonData[i][item]
if (itemTem == "暂无数据") {
str2+=`<td colspan='5' style='background-color:#FFFFFFfont-size:22pxborder: 1px solid #000000'> ${ itemTem + '\t'}</td>`
}else {
str2+=`<td style='background-color:#FFFFFFfont-size:22pxborder: 1px solid #000000'> ${ itemTem + '\t'}</td>`
}
}
}
str2+='</tr>'
}
let str3 = "<tr><td colspan='5' style='background-color:#FFFFFFborder: 1px solid #000000'></td></tr>"
str += (str1 + str2 + str3)
}
var tableHtml="<html><head><meta charset='UTF-8'></head><body><table>"+str+"</body></html>"
var excelBlob = new Blob([tableHtml], {type: 'application/vnd.ms-excel'})
var fileName = "雨量最大值-"+new Date().format("yyyy年MM月dd日 h时")+".xls"
if(isIE()){
window.navigator.msSaveOrOpenBlob(excelBlob,fileName)
}else{
var oa = document.createElement('a')
oa.href = URL.createObjectURL(excelBlob)
oa.download = fileName
document.body.appendChild(oa)
oa.click()
}} //判断是否IE浏览器function isIE() {
if (!!window.ActiveXObject || "ActiveXObject" in window) {
return true
} else {
return false
}}
参考文章：https://blog.csdn.net/qq_34169240/article/details/84231226
一。导出Excel有两种：1.
function ExportExcel(tableid){//读取表格中每个单元到EXCEL中
try
{
var curTbl = document.getElementById(tableid)
var oXL = new ActiveXObject("Excel.Application")//创建AX对象excel
var oWB = oXL.Workbooks.Add()//获取workbook对象
var oSheet = oWB.ActiveSheet//激活当前sheet
var Lenr = curTbl.rows.length//取得表格行数
for (i = 0i <Lenri++){
var Lenc = curTbl.rows(i).cells.length//取得每行的列数
for (j = 0j <Lencj++){
oSheet.Cells(i + 1, j + 1).value = curTbl.rows(i).cells(j).innerText//赋值
}
}
oXL.Visible = true//设置excel可见属性
}
catch(e)
{
alert(e.message)
}
}
2：
function dataToExcel(tableid) {//整个表格拷贝到EXCEL中
var curTbl = document.getElementById(tableid)
var oXL
try {
oXL = GetObject("", "Excel.Application")
}
catch (E) {
try {
oXL = new ActiveXObject("Excel.Application")
}
catch (E2) {
//alert("Please confirm:\n1.Microsoft Excel has been installed.\n2.Internet Options=>Security=>Setting \"Enable unsafe ActiveX\"")
alert("请确认:\n1.机器上Excel已经安装.\n2.Internet 选项=>安全=>Internet \"对没有标记为安全的ActiveX控件进行初始化和脚本运行，设定为启用\"")
return
}
}
//创建AX对象excel
var oWB = oXL.Workbooks.Add()
//获取workbook对象
var oSheet = oWB.ActiveSheet
//激活当前sheet
var sel = document.body.createTextRange()
sel.moveToElementText(curTbl)
//把表格中的内容移到TextRange中
sel.select()
//全选TextRange中内容
sel.execCommand("Copy")
//复制TextRange中内容
oSheet.Paste()
//粘贴到活动的EXCEL中
oXL.Visible = true
//设置excel可见属性
}
