使用table2excel做excel表格下载

ゞ浴缸里的玫瑰 2022-03-27 06:36 897阅读 0赞

1、引入table2excel.js

2、html写法，id为exceldown ，触发时需要传，会下载这个id包含的表格：

<div class="table-responsive" id="exceldown"> 
    						<table class="table table-striped table-bordered table-hover table-condensed" id="tabThead">
    							<thead id="thead">
    								<tr></tr>
    							</thead>
    						</table>
    						<div id="scroll_table">
    							<table class="table table-striped table-bordered table-hover table-condensed" id="tb">
    								<tbody id="tbody"></tbody>
    							</table>
    						</div>
    						<div id="win">
    							<div class="winbox">
    								<div class="winMain">
    									工作日时间调整：<input class="timespinner" style="width:80px;">
    								</div>
    								<div class="btnList">
    									<button id="save" class="btns">保存</button>
    									<button id="dispose" class="btns">取消</button>
    								</div>
    							</div>
    						</div>
    					</div>

3.点击按钮触发下载，并且传这个id

<a href="#" id="exportExcel" class="easyui-linkbutton none block" style="height:20px;margin-top:2px;box-sizing: border-box;display:inline-block;float:left"  onclick="method1(exceldown,'电量损耗分析')">下载Excel报表</a>

4、table2excel.js文件

/*
     *  jQuery table2excel - v1.1.1
     *  jQuery plugin to export an .xls file in browser from an HTML table
     *  https://github.com/rainabba/jquery-table2excel
     *
     *  Made by rainabba
     *  Under MIT License
     */
    ! function(a, b, c, d) {
    	function e(b, c) {
    		this.element = b, this.settings = a.extend({}, k, c), this._defaults = k, this._name = j, this.init()
    	}
    
    	function f(a) {
    		return a.filename ? a.filename : "table2excel"
    	}
    
    	function g(a) {
    		var b = /(\s+alt\s*=\s*"([^"]*)"|\s+alt\s*=\s*'([^']*)')/i;
    		return a.replace(/<img[^>]*>/gi, function(a) {
    			var c = b.exec(a);
    			return null !== c && c.length >= 2 ? c[2] : ""
    		})
    	}
    	function h(a) {
    		return a.replace(/<a[^>]*>|<\/a>/gi, "")
    	}
    	function i(a) {
    		var b = /(\s+value\s*=\s*"([^"]*)"|\s+value\s*=\s*'([^']*)')/i;
    		return a.replace(/<input[^>]*>|<\/input>/gi, function(a) {
    			var c = b.exec(a);
    			return null !== c && c.length >= 2 ? c[2] : ""
    		})
    	}
    	var j = "table2excel",
    		k = {
    			exclude: ".noExl",
    			name: "Table2Excel",
    			filename: "table2excel",
    			fileext: ".xls",
    			exclude_img: !0,
    			exclude_links: !0,
    			exclude_inputs: !0
    		};
    	e.prototype = {
    		init: function() {
    			var b = this;
    			b.template = {
    				head: '<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"><meta http-equiv="content-type" content="application/vnd.ms-excel; charset=UTF-8"><head>\x3c!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets>',
    				sheet: {
    					head: "<x:ExcelWorksheet><x:Name>",
    					tail: "</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet>"
    				},
    				mid: "</x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--\x3e</head><body>",
    				table: {
    					head: "<table>",
    					tail: "</table>"
    				},
    				foot: "</body></html>"
    			}, b.tableRows = [], a(b.element).each(function(c, d) {
    				var e = "";
    				a(d).find("tr").not(b.settings.exclude).each(function(c, d) {
    					e += "<tr>", a(d).find("td,th").not(b.settings.exclude).each(function(c, d) {
    						var f = {
    							rows: a(this).attr("rowspan"),
    							cols: a(this).attr("colspan"),
    							flag: a(d).find(b.settings.exclude)
    						};
    						f.flag.length > 0 ? e += "<td> </td>" : f.rows & f.cols ? e += "<td>" + a(d).html() + "</td>" : (e += "<td", f.rows > 0 && (e += " rowspan='" + f.rows + "' "), f.cols > 0 && (e += " colspan='" + f.cols + "' "), e += "/>" + a(d).html() + "</td>")
    					}), e += "</tr>", console.log(e)
    				}), b.settings.exclude_img && (e = g(e)), b.settings.exclude_links && (e = h(e)), b.settings.exclude_inputs && (e = i(e)), b.tableRows.push(e)
    			}), b.tableToExcel(b.tableRows, b.settings.name, b.settings.sheetName)
    		},
    		tableToExcel: function(d, e, g) {
    			var h, i, j, k = this,
    				l = "";
    			if(k.format = function(a, b) {
    					return a.replace(/{(\w+)}/g, function(a, c) {
    						return b[c]
    					})
    				}, g = void 0 === g ? "Sheet" : g, k.ctx = {
    					worksheet: e || "Worksheet",
    					table: d,
    					sheetName: g
    				}, l = k.template.head, a.isArray(d))
    				for(h in d) l += k.template.sheet.head + g + h + k.template.sheet.tail;
    			if(l += k.template.mid, a.isArray(d))
    				for(h in d) l += k.template.table.head + "{table" + h + "}" + k.template.table.tail;
    			l += k.template.foot;
    			for(h in d) k.ctx["table" + h] = d[h];
    			if(delete k.ctx.table, !c.documentMode) {
    				var m = new Blob([k.format(l, k.ctx)], {
    					type: "application/vnd.ms-excel"
    				});
    				b.URL = b.URL || b.webkitURL, i = b.URL.createObjectURL(m), j = c.createElement("a"), j.download = f(k.settings), j.href = i, c.body.appendChild(j), j.click(), c.body.removeChild(j)
    			} else if("undefined" != typeof Blob) {
    				l = k.format(l, k.ctx), l = [l];
    				var n = new Blob(l, {
    					type: "text/html"
    				});
    				b.navigator.msSaveBlob(n, f(k.settings))
    			} else txtArea1.document.open("text/html", "replace"), txtArea1.document.write(k.format(l, k.ctx)), txtArea1.document.close(), txtArea1.focus(), sa = txtArea1.document.execCommand("SaveAs", !0, f(k.settings));
    			return !0
    		}
    	}, a.fn[j] = function(b) {
    		var c = this;
    		return c.each(function() {
    			a.data(c, "plugin_" + j) || a.data(c, "plugin_" + j, new e(this, b))
    		}), c
    	}
    }(jQuery, window, document);
    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 method1(tableid,name) { //整个表格拷贝到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,name)
    	}
    }
    function Cleanup() {
    	window.clearInterval(idTmr);
    	CollectGarbage();
    }
    var tableToExcel = (function() {
    	var uri = 'data:application/vnd.ms-excel;base64,',
    		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></head><body><table>{table}</table></body></html>',
    		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(table, name) {
    		if(name.length == 0){name='导出Excel信息';}
    		console.log(table)
    		if(!table.nodeType) table = document.getElementById(table)
    		console.log(table)
    		var ctx = {
    			worksheet: name || 'Worksheet',
    			table: table.innerHTML
    		}
    		//传name自定义名称
    		var downloadLink = document.createElement("A");
    		downloadLink.href = uri + base64(format(template, ctx));
    		downloadLink.download = name+ new Date().formatime("yyyy_MM_dd_HH_mm_ss") +'.xls';
    		downloadLink.target = '_blank';
    		document.body.appendChild(downloadLink);
    		downloadLink.click();
    		document.body.removeChild(downloadLink);
    		//传name自定义名称
    //		window.location.href = uri + base64(format(template, ctx))
    	}
    })()
    
    Date.prototype.formatime=function(pattern){//日期格式化
    	var returnValue=pattern;
    	var formatime={
    		"y+":this.getFullYear(),
    		"M+":this.getMonth()+1,
    		"d+":this.getDate(),
    		"H+":this.getHours(),
    		"m+":this.getMinutes(),
    		"s+":this.getSeconds(),
    		"S":this.getMilliseconds(),
    		"h+":(this.getHours()%12),
    		"a":(this.getHours()/12)<=1?"AM":"PM"
    	};
    	for(var key in formatime){
    		var regExp=new RegExp("("+key+")");
    		if(regExp.test(returnValue)){
    			var zero="";
    			for(var i=0;i<RegExp.$1.length;i++){
    				zero+="0"
    			}
    			var replacement=RegExp.$1.length==1?formatime[key]:(zero+formatime[key]).substring(((""+formatime[key]).length));
    			returnValue=returnValue.replace(RegExp.$1,replacement)
    		}
    	}
    	return returnValue
    };