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();	}%>