javascript导出excel

最新推荐文章于 2024-05-29 21:40:25 发布

weixin_33980459

最新推荐文章于 2024-05-29 21:40:25 发布

阅读量108

点赞数

原文链接：https://my.oschina.net/u/232595/blog/719953

版权

2019独角兽企业重金招聘Python工程师标准>>>

<script type="text/javascript" src="js/jquery.min.js"></script>

<script type="text/javascript" src="js/jquery.base64.js"></script>

<script type="text/javascript" src="js/jquery.table2excel.js"></script>

$("#table2excel").table2excel({
// 不被导出的表格行的CSS class类
exclude: ".noExl",
// 导出的Excel文档的名称
name: "Excel Document Name",
// Excel文件的名称，不要加后缀
filename: "myExcelTable"
});

js下载：http://download.csdn.net/detail/liu4071325/9483580

插件：

https://github.com/rainabba/jquery-table2excel

https://github.com/kayalshri/tableExport.jquery.plugin

PS：当表格数据太多会不能正常下载导出

原理：将数据转化为base64的串，通过data:application/vnd.ms-excel;base64,浏览器协议下载。

but浏览器对于URL长度是有限制的，当你的数据太多的时候base64的串会超过这个限制就不能下载了。

HTTP URL最大长度 http://blog.csdn.net/woxueliuyun/article/details/41866611

解决方案：

修正后的jquery.table2excel.js

//table2excel.js
;(function ( $, window, document, undefined ) {
	var pluginName = "table2excel",

		defaults = {
			exclude: ".noExl",
			name: "Table2Excel",
			excludeChild : '',
		};

	// The actual plugin constructor
	function Plugin ( element, options ) {
		this.element = element;
		// jQuery has an extend method which merges the contents of two or
		// more objects, storing the result in the first object. The first object
		// is generally empty as we don't want to alter the default options for
		// future instances of the plugin
		//
		this.settings = $.extend( {}, defaults, options );
		this._defaults = defaults;
		this._name = pluginName;
		this.init();
	}

	Plugin.prototype = {
		init: function () {
			var e = this;

			var utf8Heading = "<meta http-equiv=\"content-type\" content=\"application/vnd.ms-excel; charset=UTF-8\">";
			e.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\">" + utf8Heading + "<head><!--[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]--></head><body>",
				table: {
					head: "<table>",
					tail: "</table>"
				},
				foot: "</body></html>"
			};

			e.tableRows = [];

			// get contents of table except for exclude
			$(e.element).each( function(i,o) {
				var tempRows = "";
				$(o).find("tr").not(e.settings.exclude).each(function (i,o) {
					tempRows += "<tr>";
					$(o).find("th").not(e.settings.exclude).each(function (i,q) {
						var flag = $(q).find(e.settings.exclude) // does this <td> have something with an exclude class
						if(flag.length >= 1) {
							tempRows += "<td> </td>" // exclude it!!
						} else {
							if(e.settings.excludeChild){
								var html = $(q).html();
								$(q).find(e.settings.excludeChild).each(function(){
									html = html.replace($(this).html(), '');
								});
								tempRows += "<td>" + html + "</td>";
							}else {
								tempRows += "<td>" + $(q).html() + "</td>";
							}
						}
					})
					$(o).find("td").not(e.settings.exclude).each(function (i,q) {
						var flag = $(q).find(e.settings.exclude) // does this <td> have something with an exclude class
						if(flag.length >= 1) {
							tempRows += "<td> </td>" // exclude it!!
						} else {
							if(e.settings.excludeChild){
								var html = $(q).html();
								$(q).find(e.settings.excludeChild).each(function(){
									html = html.replace($(this).html(), '');
								});
								tempRows += "<td>" + html + "</td>";
							}else {
								tempRows += "<td>" + $(q).html() + "</td>";
							}
						}
					});
					tempRows += "</tr>";
				});
				e.tableRows.push(tempRows);
			});

			e.tableToExcel(e.tableRows, e.settings.name, e.settings.sheetName);
		},

		tableToExcel: function (table, name, sheetName) {
			var e = this, fullTemplate="", i, link, a;

			e.uri = "data:application/vnd.ms-excel;base64,";
			e.base64 = function (s) {
				return window.btoa(unescape(encodeURIComponent(s)));
			};
			e.format = function (s, c) {
				return s.replace(/{(\w+)}/g, function (m, p) {
					return c[p];
				});
			};

			sheetName = typeof sheetName === "undefined" ? "Sheet" : sheetName;

			e.ctx = {
				worksheet: name || "Worksheet",
				table: table,
				sheetName: sheetName,
			};

			fullTemplate= e.template.head;

			if ( $.isArray(table) ) {
				for (i in table) {
					//fullTemplate += e.template.sheet.head + "{worksheet" + i + "}" + e.template.sheet.tail;
					fullTemplate += e.template.sheet.head + sheetName + i + e.template.sheet.tail;
				}
			}

			fullTemplate += e.template.mid;

			if ( $.isArray(table) ) {
				for (i in table) {
					fullTemplate += e.template.table.head + "{table" + i + "}" + e.template.table.tail;
				}
			}

			fullTemplate += e.template.foot;

			for (i in table) {
				e.ctx["table" + i] = table[i];
			}
			delete e.ctx.table;

			if (typeof msie !== "undefined" && msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./))      // If Internet Explorer
			{
				if (typeof Blob !== "undefined") {
					//use blobs if we can
					fullTemplate = [fullTemplate];
					//convert to array
					var blob1 = new Blob(fullTemplate, { type: "text/html" });
					window.navigator.msSaveBlob(blob1, getFileName(e.settings) );
				} else {
					//otherwise use the iframe and save
					//requires a blank iframe on page called txtArea1
					txtArea1.document.open("text/html", "replace");
					txtArea1.document.write(e.format(fullTemplate, e.ctx));
					txtArea1.document.close();
					txtArea1.focus();
					sa = txtArea1.document.execCommand("SaveAs", true, getFileName(e.settings) );
				}

			} else {
				link = e.uri + e.base64(e.format(fullTemplate, e.ctx));
				if(e.settings.hasOwnProperty('uploadFunc') && link.length > 10000){
					e.settings.uploadFunc({
						'name' : getFileName(e.settings),
						'data' : link
					}, getFileName(e.settings));
				}else{
					a = document.createElement("a");
					a.download = getFileName(e.settings);
					a.href = link;

					document.body.appendChild(a);
					a.click();
				}
				// setTimeout('document.body.removeChild(a)', 2000);
			}
			return true;
		}
	};

	function getFileName(settings) {
		return ( settings.filename ? settings.filename : "table2excel" );
	}

	$.fn[ pluginName ] = function ( options ) {
		var e = this;
		e.each(function() {
			if ( !$.data( e, "plugin_" + pluginName ) ) {
				$.data( e, "plugin_" + pluginName, new Plugin( this, options ) );
			}
		});

		// chain jQuery functions
		return e;
	};

})( jQuery, window, document );

使用

$("#export").on('click',function(){
			$("#table").table2excel({
				exclude : '',
				excludeChild : '.excludeChild',
				name: '',
				filename: buildExportName() + '.xls',
				uploadFunc : openPostWindow
			});
		});

		function openPostWindow(data, name) {
			var tempForm = document.createElement("form");
			tempForm.id= "tempForm1";
			tempForm.method="post";
			tempForm.action= "{:U('Excel/json_export')}";
			tempForm.target= name;

			if( (typeof data == 'string') && data.constructor == String ){
				var hideInput = document.createElement("input");
				hideInput.type="hidden";
				hideInput.name= "content"
				hideInput.value= data;
				tempForm.appendChild(hideInput);
			} else if(( typeof data == 'object' )&& data.constructor == Object ){
				for(var k in data){
					var hideInput = document.createElement("input");
					hideInput.type="hidden";
					hideInput.name= k
					hideInput.value= data[k];
					tempForm.appendChild(hideInput);
				}
			}
			if(tempForm.hasOwnProperty('attachEvent')){
				tempForm.attachEvent("onsubmit",function(){
					window.open('about:blank', name,'height=400, width=400, top=0, left=0, toolbar=yes, menubar=yes, scrollbars=yes, resizable=yes,location=yes, status=yes');
				});
			}else{
				tempForm.addEventListener("submit",function(){
					window.open('about:blank', name,'height=400, width=400, top=0, left=0, toolbar=yes, menubar=yes, scrollbars=yes, resizable=yes,location=yes, status=yes');
				});
			}

			document.body.appendChild(tempForm);
			if(tempForm.hasOwnProperty('fireEvent')){
				tempForm.fireEvent("onsubmit");
			}else{
				var evt = document.createEvent("MouseEvents");
				evt.initEvent("click", true, true);
				tempForm.dispatchEvent(evt);
			}

			tempForm.submit();
			document.body.removeChild(tempForm);
		}

PHP下载

$name = I('post.name');
$data = I('post.data');
$data = str_ireplace('data:application/vnd.ms-excel;base64,', '', $data);
$data = base64_decode($data);
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
header("Cache-Control: no-cache");
header("Pragma: no-cache");
$file = "./Public/tmp/".$name;
file_put_contents($file, $data);
header("Content-type: application/octet-stream");
if(IS_WIN){
    $name = iconv('gb2312','utf-8',$name);
}
header('Content-Disposition: attachment; filename="' . $name . '"');
header("Content-Length: ". filesize($file));
readfile($file);

新问题：新打开的窗口在下载之后如何关闭？

方案一：定时关闭~~~~~~

转载于:https://my.oschina.net/u/232595/blog/719953