jsp导出excel教程及常见问题

时间：2022-01-22 13:39:54

废话不多说，直接上代码：


前端代码：

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<c:set var="ctx" value="${pageContext.request.contextPath}"/>
<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html xmlns:x="urn:schemas-microsoft-com:office:excel">


<script type="text/javascript">
function exportExcel(){
window.open('${ctx}/exportToExcel.jsp?exportToExcel=YES');
}
</script>
<style type="text/css"> 
body,table{ 
font-size:12px; 
} 
table{ 
table-layout:fixed; 
empty-cells:show; 
border-collapse: collapse; 
margin:0 auto; 
} 
td{ 
height:30px; 
/* mso-number-format:'\@';*/
} 
h1,h2,h3{ 
font-size:12px; 
margin:0; 
padding:0; 
} 
.table{ 
border:1px solid #cad9ea; 
color:#666; 
} 
.table th { 
background-repeat:repeat-x; 
height:30px; 
} 
.table td,.table th{ 
border:1px solid #cad9ea; 
padding:0 1em 0; 
} 
.table tr.alter{ 
background-color:#f5fafe; 
}
</style>
<head>
<!-- 显示网格线 -->
<xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>工作表标题</x:Name>
<x:WorksheetOptions>
<x:Print>
<x:ValidPrinterInfo />
</x:Print>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
</x:ExcelWorkbook>
</xml>  <br>
<!-- 显示网格线 -->
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Export to Excel</title>
</head>
<body>
<%
String exportToExcel = request.getParameter("exportToExcel");
if (exportToExcel != null && exportToExcel.toString().equalsIgnoreCase("YES")) {
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "inline; filename=" + "excel.xls");
}
%>
<table width="90%" class="table">
<tr>
<th colspan="11" style="font-size:24px;">县级养殖场(户)养殖环节病死猪无害化处理情况登记表</th>
</tr>
<tr>
<th colspan="11" align="right">
<fmt:formatDate value="${create_time}" pattern="yyyy 年 MM 月"/>&nbsp;&nbsp;
</th>
</tr>
<tr>
<th>乡镇名称</th> 
<th>行政村名</th> 
<th>养殖场(户)名称</th> 
<th>身份证号</th> 
<th>联系电话</th> 
<th>一卡通</th> 
<th>生猪饲养量</th> 
<th>病死猪无害化处理量</th>
<th>处理方式</th>
<th>养殖场(户)负责人签名</th>
<th>监管人员签名</th>
</tr>
<c:forEach var="list" items="${list}">
<tr> 
<td>${list.town_name}</td>
<td>${list.village_name}</td>
<td>${list.name}</td>
<td style="mso-number-format:'\@';">${list.card_id}</td>
<td style="mso-number-format:'\@';">${list.tel}</td>
<td style="mso-number-format:'\@';">${list.onecard_id}</td>
<td>${list.feed_quantity}</td>
<td>${list.harmless_quantity}</td>
<td>高温处理</td>
<td></td>
<td></td>
</tr> 
</c:forEach>
<tr class="alter"> 
<td>县级合计</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td>${total}</td>
<td></td>
<td></td>
<td></td>
</tr>
</table> 
    <br><br><br><br><br><br><br><br><br><br><br><br><br><br><br>  
              
    <%  
        if (exportToExcel == null) {
    %>
    <a href="javascript:exportExcel();">导出为Excel</a>  
    <%  
        }
    %>
</body>
</html>





后端代码：

public void exportToExcel() {
String roleId = getSessionAttr("roleId");
String town_name = getPara("town_name");
String village_name = getPara("village_name");
String create_time = getPara("create_time");

try {
String sql = "";
List<Farmers> list = null;
if(!"".equals(create_time) && null!=create_time){
// sql+=" and a.create_time like '%"+create_time+"%'";
if("1".equals(roleId) || "18".equals(roleId)){
sql = " from t_farmers a where 1=1 ";
} else {
sql = " from t_farmers a where a.user_id='"+roleId+"' ";
}

if(!"".equals(town_name) && null!=town_name){
sql+=" and a.town_name like '%"+town_name+"%'";
}
if(!"".equals(village_name) && null!=village_name){
sql+=" and a.village_name like '%"+village_name+"%'";
}

list = Farmers.dao.find("SELECT a.*," +
"(SELECT SUM(death_number) death_number FROM t_farmers_details " +
"WHERE create_time LIKE '%"+create_time+"%' " +
"AND farmer_id =a.id ) harmless_quantity " + sql);
}
Date date = new SimpleDateFormat("yyyy-MM-dd").parse(create_time+"-01");
int total = 0;
if(list!=null && list.size()!=0){
for(int n=0;n<list.size();n++){
BigDecimal num = list.get(n).getBigDecimal("harmless_quantity");
if(!"".equals(num) && !"null".equals(num) && null!=num){
total += Integer.parseInt(num.toString());
}
}
}
setAttr("total", total);
setAttr("list", list);
setAttr("create_time", date);
} catch (Exception e) {
e.printStackTrace();
}
render("/exportToExcel.jsp?exportToExcel=YES");
}




小提示：excel看你数字列超过12位就会显示科学计数。解决办法：前端代码中加上： <td style="mso-number-format:'\@';">${list.card_id}</td>
<td style="mso-number-format:'\@';">${list.tel}</td>
<td style="mso-number-format:'\@';">${list.onecard_id}</td>


这句： style="mso-number-format:'\@';"很有用。





参考文章：

http://www.jb51.net/article/56861.htm

http://www.360doc.com/content/12/1017/21/7851074_242097668.shtml

http://blog.csdn.net/u011974797/article/details/50315147



重点文章：http://wltjack.iteye.com/blog/2251396


标签：

相关文章