html表格导出Excel

最新推荐文章于 2024-05-16 18:15:52 发布

iteye_11961

最新推荐文章于 2024-05-16 18:15:52 发布

阅读量116

点赞数

分类专栏： java 文章标签： Excel HTML Office CSS JSP

java 专栏收录该内容

20 篇文章 0 订阅

订阅专栏

<table id="export_table" border="1" cellspacing="0" cellpadding="0">
    	<tr>
    		<th>第一列</th>
    		<th>第二列</th>
    		<th>第三列</th>
    		<th>第四列</th>
    		<th>第五列</th>
    	</tr>
    	<tr>
    		<td>111</td>
    		<td>222</td>
    		<td>333</td>
    		<td>444</td>
    		<td>555</td>
    	</tr>
    	<tr>
    		<td>111</td>
    		<td>222</td>
    		<td>333</td>
    		<td>444</td>
    		<td>555</td>
    	</tr>
    	<tr>
    		<td>111</td>
    		<td>222</td>
    		<td>333</td>
    		<td>444</td>
    		<td>555</td>
    	</tr>
    	<tr>
    		<td>111</td>
    		<td>222</td>
    		<td>333</td>
    		<td>444</td>
    		<td>555</td>
    	</tr>
    	<tr>
    		<td>111</td>
    		<td>222</td>
    		<td>333</td>
    		<td>444</td>
    		<td>555</td>
    	</tr>
    </table>

先把上面那段代码复制下来，新建一个excel文件，把复制的内容粘帖进去，看看什么效果！

1、通过上面的操作，可以得知excel能够解析html代码，这样给导出excel提供了一个思路，我们只有把页面上表格的html代码全部拿来，粘帖到excel中，不就可以实现导出了嘛。

2、

下面提供两种导出excel的方法和实例

第一种:完全通过javascipt导出，这种方法导出设计安全问题，看实例：

example.html:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <title>example.html</title>
	
    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
    <meta http-equiv="description" content="this is my page">
    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
    
    <!--<link rel="stylesheet" type="text/css" href="./styles.css">-->
	
	<script type="text/javascript">
		function exportExcel(){
		    exportTable = document.getElementById('export_table');
		    oApplication    = new ActiveXObject ( 'Excel.Application' ); 
			oApplication.visible = true; 
			oApplication.DisplayAlerts = false; 
			xlBook = oApplication.Workbooks.Add; 
			oActiveSheet = xlBook.ActiveSheet;
		    window.clipboardData.setData('text',exportTable.outerHTML); 
			oActiveSheet.Paste; 
		}
	</script>
  </head>
  
  <body>
  	<input type="button" value="导出" οnclick="exportExcel();">
    <table id="export_table" border="1" cellspacing="0" cellpadding="0">
    	<tr>
    		<th>第一列</th>
    		<th>第二列</th>
    		<th>第三列</th>
    		<th>第四列</th>
    		<th>第五列</th>
    	</tr>
    	<tr>
    		<td>111</td>
    		<td>222</td>
    		<td>333</td>
    		<td>444</td>
    		<td>555</td>
    	</tr>
    	<tr>
    		<td>111</td>
    		<td>222</td>
    		<td>333</td>
    		<td>444</td>
    		<td>555</td>
    	</tr>
    	<tr>
    		<td>111</td>
    		<td>222</td>
    		<td>333</td>
    		<td>444</td>
    		<td>555</td>
    	</tr>
    	<tr>
    		<td>111</td>
    		<td>222</td>
    		<td>333</td>
    		<td>444</td>
    		<td>555</td>
    	</tr>
    	<tr>
    		<td>111</td>
    		<td>222</td>
    		<td>333</td>
    		<td>444</td>
    		<td>555</td>
    	</tr>
    </table>
  </body>
</html>

第二种：通过java在后台实现导出

example.jsp

<%@ page language="java" import="java.util.*" pageEncoding="GBK"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>example.jsp</title>
	<meta http-equiv="pragma" content="no-cache">
	<meta http-equiv="cache-control" content="no-cache">
	<meta http-equiv="expires" content="0">    
	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
	<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->
	
	<script type="text/javascript">
		function exportExcel(){
			var frm = document.exportForm;
			var toExcelStr = document.getElementById('export_table').outerHTML;
			frm.elements['content'].value = toExcelStr;
			frm.elements['title'].value = 'SheetExcel.xls';
			frm.submit();
		}
	</script>
  </head>
  
  <body>
    <input type="button" value="导出" οnclick="exportExcel();">
    <table id="export_table" border="1" cellspacing="0" cellpadding="0">
    	<tr>
    		<th>第一列</th>
    		<th>第二列</th>
    		<th>第三列</th>
    		<th>第四列</th>
    		<th>第五列</th>
    	</tr>
    	<tr>
    		<td>111</td>
    		<td>222</td>
    		<td>333</td>
    		<td>444</td>
    		<td>555</td>
    	</tr>
    	<tr>
    		<td>111</td>
    		<td>222</td>
    		<td>333</td>
    		<td>444</td>
    		<td>555</td>
    	</tr>
    	<tr>
    		<td>111</td>
    		<td>222</td>
    		<td>333</td>
    		<td>444</td>
    		<td>555</td>
    	</tr>
    	<tr>
    		<td>111</td>
    		<td>222</td>
    		<td>333</td>
    		<td>444</td>
    		<td>555</td>
    	</tr>
    	<tr>
    		<td>111</td>
    		<td>222</td>
    		<td>333</td>
    		<td>444</td>
    		<td>555</td>
    	</tr>
    </table>
    <form name="exportForm" action="exportToExcel.jsp" style="display: none" method="post">
    	<!-- excel文件名 -->
    	<input name="title" type="text" />
    	<!-- excel文件内容 -->
    	<input name="content" type="text" />
    </form>
  </body>
</html>

exportToExcel.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>

<%
	request.setCharacterEncoding("GBK");
	String title = request.getParameter("title");
  	String content = request.getParameter("content");
  	
  	//html代码在excel中处理与在html中不一样，在此做一下转化，以便显示出边框(根据自己需要调整)
	content = content.replaceAll("bgColor=black ","");
	content = content.replaceAll("border=0","border=1");
	
	//如果没有border属性，则增加一个
	if(content.indexOf("border")==-1){
		content = content.replaceAll("<TABLE","<TABLE border=1");
	}
	
	//当table中有子table时，父table不显示边框
	//用于调整显示格式，别无它用
	int i = content.indexOf("border=1");
	int j = content.indexOf("border=1",i+8);
	if(j>0)
		content = content.replaceFirst("border=1","border=0");
	
  	System.out.print(content);
  	response.setContentType("application/vnd.ms-excel;charset=GBK");
  	
	if(title != null && !"".equals(title)){
		response.setHeader("Content-disposition","attachment; filename="+title);
	}else{
		response.setHeader("Content-disposition","attachment; filename=SheetExcel.xls");
	}
%>
<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>
<title>Test</title>
<style>
	td{ 
	    font-family: Verdana;
	    color:black;
	    text-overflow:ellipsis;
	    word-wrap:break-word;
	    FONT-SIZE: 12px;
	    overflow: hidden;
		white-space:normal;
	}
  .row1 {font-size: 12px; background-color: white; text-align:left; vertical-align: center; padding: 2px}
  .row2 {font-size: 12px; background-color: gainsboro; text-align:left; vertical-align: center; padding: 2px;} 
 </style>
<meta http-equiv=Content-Type content="text/html; charset=GBK">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 9">
</head>
<body>
	<%--要显示的HTML代码字符窜--%>
	<%=content%>
</body>
</HTML>

iteye_11961

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
html表格导出Excel

&lt;table id="export_table" border="1" cellspacing="0" cellpadding="0"&gt; &lt;tr&gt; &lt;th&gt;第一列&lt;/th&gt; &lt;th&gt;第二列&lt;/th&gt; &
复制链接

扫一扫

专栏目录