jsp导出excel教程及常见问题

最新推荐文章于 2017-09-05 10:53:35 发布

pengyufight

最新推荐文章于 2017-09-05 10:53:35 发布

阅读量993

点赞数

分类专栏：教程资源

本文链接：https://blog.csdn.net/pengyufight/article/details/60867477

版权

教程资源专栏收录该内容

8 篇文章 0 订阅

订阅专栏

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

前端代码：

<%@ 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 月"/>  
</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

pengyufight

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
jsp导出excel教程及常见问题

废话不多说，直接上代码：前端代码：function exportExcel(){window.open('${ctx}/exportToExcel.jsp?exportToExcel=YES');} body,table{ font-size:12px; } table{ table-layout:fixed;
复制链接

扫一扫