jsp导出excel

2024-04-27 15:14:48

字体：大中小

来源：转载

供稿：网友

jsp页面导出Excel表，一般浏览器会自带处理插件，IE例外，网上查过一些方法并不适用于IE，以下是自己在项目开发中研究出来的方法，就是先判断浏览器，然后对IE做专门处理。

jsp页面

<html><head></script><title>收票确认</title><META http-equiv="Content-Type" content="text/html; charset=GBK"></head><body style="overflow:hidden;"><jsp:include page="title.jsp"><jsp:param value="收票确认" name="title"/></jsp:include><form method="post" name="txForm">	<table id="exportTable" class="table" width="100%" BORDER="0" CellSpacing="0" CellPadding="0">		<tr>			<th>电子票据号码</th>			<th>收票类型</th>			<th>交易日期</th>			<th>票据种类</th>			<th>票据金额</th>			<th>出票日期</th>			<th>到期日期</th>			<th>承兑人</th>			<th>收票人名称</th>			<th>交易对手</th>			<th>签收情况</th>			<th>出票人</th>		</TR>		<tr>			//如果数字太长时加上style="mso-number-format:'@';"，否则会显示科学计数法			<th style="mso-number-format:'@';">230939100009520161229000925264</th>			<th>2016/12/29</th>			<th>2016/12/29</th>			<th>商业承兑汇票</th>			<th style="mso-number-format:'@';">122,901.00</th>			<th>2016/12/29</th>			<th>2017/5/12</th>			<th>ZX</th>			<th>ZX</th>			<th>ZX</th>			<th>同意签收</th>			<th>ZX</th>		</TR>		<tr>			<th><input type="button" class="button button28" value="导出" onclick="exportExcel('exportTable');"></th>		</TR>	</table></form></body></html>
js
//整个表格拷贝到EXCEL中exportExcel = function (tableId) {	if(getExplorer()=='ie'){//判断是否是IE浏览器		exportExcel4IE(tableId);	} else {		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><!--[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(unescape(encodeURIComponent(s))); },			format = function(s, c) {				return s.replace(/{(/w+)}/g, function(m, p) { return c[p]; });			};		removeLinkAll(tableId,true);//导出前去掉所有超链接，如果有的话		if (!tableId.nodeType) _table = document.getElementById(tableId);		var ctx = {worksheet:'Worksheet', table: _table.innerHTML};		window.location.href = uri + base64(format(template, ctx));		removeLinkAll(tableId,false);//导出后恢复原来的超链接	}};//当浏览器为IE时的导出exportExcel4IE=function(tb){//当浏览器为IE时的导出	var table =$(tb);	if(table==null)alert("找不到待导出的表"+tb);	var frm= document.getElementById('_ExcelForm_');	if(frm==null){		frm = document.createElement('form');		frm.style.display = "none";		frm.name = "_ExcelForm_";		frm.id = "_ExcelForm_";		frm.method = "POST";		frm.action = "GDMS-ROOT/excel.jsp";		frm.target = "message";		var frmInput = document.createElement('input');		frmInput.name = "_ExcelText_";		frmInput.id = "_ExcelText_";		frmInput.type = "hidden";		frm.appendChild(frmInput);		document.body.appendChild(frm);	}	$('_ExcelText_').value=table.outerHTML;	frm.submit();}//判断浏览器getExplorer = function() {	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';	}};//去掉或恢复超链接removeLinkAll = function(tableId,action){   	var otagsA = document.getElementById(tableId).getElementsByTagName("a");    for(var i=0;i <otagsA.length;i++){   	    if (action){ //取消所有链接  			otagsA[i].setAttribute("rel",otagsA[i].href);   	    	otagsA[i].removeAttribute("href");	    }else{  //重新设置链接   	    	if (otagsA[i].rel) otagsA[i].setAttribute("href",otagsA[i].rel);   	    }       }   }; 	
导出处理页面，IE专备，需要exportkit-2.1.0.jar包。（其他浏览器一般会自带插件不需要此页面）
<%@page contentType="text/html;charset=GBK"%><%@page import="java.util.regex.Pattern"%><%@page import="java.util.regex.Matcher"%><%@page import="java.io.OutputStream"%><jsp:useBean id="xml" scope="session" class="com.nstc.exportkit.excel.ExportExcel"/><% try{	String fileName="EXCEL.xls";	response.reset();	response.setContentType( "application/vnd.ms-excel;charset=GBK");	response.setHeader("Content-disposition","attachment; filename=/"" + fileName + "/"");	request.setCharacterEncoding("GBK");	String table = request.getParameter("_ExcelText_");	table = table.replaceAll("//*", "200");	String regex = " on.*?=/".*?/"";	Pattern p = Pattern.compile(regex,Pattern.CASE_INSENSITIVE);	Matcher m = p.matcher(table);	table= m.replaceAll("");	regex = "linkUrl=/".*?/"";	p = Pattern.compile(regex,Pattern.CASE_INSENSITIVE);	m = p.matcher(table);	table= m.replaceAll("");	regex = "<TR.*?>";	p = Pattern.compile(regex,Pattern.CASE_INSENSITIVE);	m = p.matcher(table);	table= m.replaceAll("<TR>");	regex = "hasMnyUnit=/".*?/"";	p = Pattern.compile(regex,Pattern.CASE_INSENSITIVE);	m = p.matcher(table);	table= m.replaceAll("");	regex = "mny=/".*?/"";	p = Pattern.compile(regex,Pattern.CASE_INSENSITIVE);	m = p.matcher(table);	table= m.replaceAll("dataType=/"money/"");		regex = "<br>";	p = Pattern.compile(regex,Pattern.CASE_INSENSITIVE);	m = p.matcher(table);	table= m.replaceAll(System.getPRoperty("line.separator"));	//System.out.println(table);	OutputStream os=response.getOutputStream();	xml.export(table,os);	//注意看以下几句的使用	os.flush();	os.close();	os=null;	response.flushBuffer();	out.clear();	out = pageContext.pushBody();	}	catch(IllegalStateException e)	{	System.out.println(e.getMessage());	e.printStackTrace();	}%>