本文介绍通过servlet方式使用jxl.jar包，通过获取数据库中的数据，并导出到excel表格中。
首先，需要先从网上下载jxl包。
本文是通过jxl方式导出excel表格。
将下载好的jxl.jar添加到webroot下的WEB-INF下的lib文件夹中
如图所示：
jsp页面代码：
wpcgjh.jsp
1 <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%> 2 <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> 3 <% 4 String path = request.getContextPath(); 5 String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; 6 %> 7 8 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 9 <html> 10 <head> 11 <base href="<%=basePath%>"> 12 13 <title>My JSP 'wpcgjhlist.jsp' starting page</title> 14 15 <meta http-equiv="pragma" content="no-cache"> 16 <meta http-equiv="cache-control" content="no-cache"> 17 <meta http-equiv="expires" content="0"> 18 <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"> 19 <meta http-equiv="description" content="This is my page"> 20 21 <link rel="stylesheet" type="text/css" href="js/styles.css"> 22 23 <script type="text/javascript"> 24 function exports(id) { 25 if(confirm("确认导出到excel表格？")){ 26 window.location.href="WpcgjhExport?wpcgjh_id="+id; 27 setTimeout("alert('导出成功')",5000); 28 29 //window.location.reload(true); 30 } 31 else{ 32 return false; 33 } 34 } 35 36 </script> 37 </head> 38 39 <body> 40 <table width="100%" border="0" align="center" cellpadding="0" cellspacing="0" bgcolor="d5d4d4" bordercolor="#E1E5EE"> 41 <tr> 42 <td height="22" colspan="9" bgcolor="#FFFFFF" class="STYLE2">您当前所在的位置：物品需求管理-->采购计划查询</td> 43 </tr> 44 <form action="WpcgjhList" method="post"> 45 <tr> 46 <td height="22" colspan="9" bgcolor="#FFFFFF" class="STYLE1">物品名：<input type="text" id="wpm" name="wpm"><input type="submit" id="cx" value="查询" ></td> 47 </tr> 48 </form> 49 </table> 50 <table width="100%" border="1" align="center" cellpadding="0" cellspacing="0" bgcolor="white" bordercolor="#E1E5EE"> 51 <tr> 52 <td height="22" colspan="9" background="images/bg.gif" bgcolor="#FFFFFF" class="STYLE3"><div align="center">物品采购计划表</div></td> 53 </tr> 54 55 <tr> 56 <td height="22" background="images/images/tab_05.gif" bgcolor="#FFFFFF" class="STYLE4"><div align="center">序号</div></td> 57 <td height="22" background="images/images/tab_05.gif" bgcolor="#FFFFFF" class="STYLE4"><div align="center">物品名</div></td> 58 <td height="22" background="images/images/tab_05.gif" bgcolor="#FFFFFF" class="STYLE4"><div align="center">现有库存</div></td> 59 <td height="22" background="images/images/tab_05.gif" bgcolor="#FFFFFF" class="STYLE4"><div align="center">最大库存</div></td> 60 <td height="22" background="images/images/tab_05.gif" bgcolor="#FFFFFF" class="STYLE4"><div align="center">购买数量</div></td> 61 <td height="22" background="images/images/tab_05.gif" bgcolor="#FFFFFF" class="STYLE4"><div align="center">供应商</div></td> 62 <td height="22" background="images/images/tab_05.gif" bgcolor="#FFFFFF" class="STYLE4"><div align="center">价格</div></td> 63 <td height="22" background="images/images/tab_05.gif" bgcolor="#FFFFFF" class="STYLE4"><div align="center">计划采购时间</div></td> 64 <td height="22" background="images/images/tab_05.gif" bgcolor="#FFFFFF" class="STYLE4"><div align="center">操作</div></td> 65 </tr> 66 <c:if test="${requestScope.wpcgjhlist.size()==0 }"> 67 <td height="22" colspan="9" class="STYLE1"><div align="center">数据库中没有相关的信息</div></td> 68 </c:if> 69 <c:forEach items="${requestScope.wpcgjhlist }" var="wpcgjh" varStatus="status"> 70 <tr onmouseover="this.style.background='lightgray'" onmouseout="this.style.background='none'"> 71 <td height="22" class="STYLE1"><div align="center">${status.index+1 }</div></td> 72 <td height="22" class="STYLE1"><div align="center">${wpcgjh.wpcgjh_name }</div></td> 73 <td height="22" class="STYLE1"><div align="center">${wpcgjh.wpcgjh_xykc }</div></td> 74 <td height="22" class="STYLE1"><div align="center">${wpcgjh.wpcgjh_zdkc }</div></td> 75 <td height="22" class="STYLE1"><div align="center">${wpcgjh.wpcgjh_gmsl }</div></td> 76 <td height="22" class="STYLE1"><div align="center">${wpcgjh.wpcgjh_gys }</div></td> 77 <td height="22" class="STYLE1"><div align="center">${wpcgjh.wpcgjh_jg }</div></td> 78 <td height="22" class="STYLE1"><div align="center">${wpcgjh.wpcgjh_jhcgsj }</div></td> 79 80 <td height="22" class="STYLE1"><div align="center"> 81 <a href="javascript:exports(${wpcgjh.wpcgjh_id });" ><img src="images/images/excel.jpg" height="14" width="14">导出</a> 82 </tr> 83 </c:forEach> 84 </table> 85 <table width="100%" border="0" cellspacing="0" cellpadding="0"> 86 <tr> 87 <td background="images/main_03.gif" height="26px" align="left" style="font-size:x-small; color: white;"> 88 当前页：${pageIndex }/${totalPage }页 89 </td> 90 <td background="images/main_03.gif" height="26px" align="right"> 91 <a href="WpcgjhList?pageIndex=1"><img alt="" src="images/images/first.gif"></a> 92 <a href="WpcgjhList?pageIndex=${pageIndex-1 }"><img alt="" src="images/images/back.gif"></a> 93 <a href="WpcgjhList?pageIndex=${pageIndex+1 }"><img alt="" src="images/images/next.gif"></a> 94 <a href="WpcgjhList?pageIndex=${totalPage}"><img alt="" src="images/images/last.gif"></a> 95 <input type="text" id="num" name="num" style="width: 25px;height:20px "> 96 <a href="javascript:tiaozhuan()"><img alt="" src="images/images/go.gif"></a> 97 <input type="hidden" id="totalPage" value="${totalPage}" > 98 </td> 99 </tr> 100 </table> 101 <script type="text/javascript"> 102 function tiaozhuan(){ 103 var num=document.getElementById("num").value; 104 var totalPage=document.getElementById("totalPage").value; 105 if(num==""){ 106 alert("您没输入页数，请输入页数"); 107 }else{ 108 var nums=parseInt(num); 109 if(nums<1||nums>totalPage){ 110 alert("您输入的页码不在合法范围内，请重新输入！！"); 111 }else{ 112 window.location.href="WpcgjhList?pageIndex="+nums; 113 } 114 } 115 116 } 117 </script> 118 </body> 119 </html>
通过js控制跳转，跳转到servlet中：
WpcgjhExport.java
1 package oracle.class4.gzz.web; 2 3 import java.io.IOException; 4 import java.io.OutputStream; 5 6 import javax.servlet.ServletException; 7 import javax.servlet.http.HttpServlet; 8 import javax.servlet.http.HttpServletRequest; 9 import javax.servlet.http.HttpServletResponse; 10 11 import oracle.class4.gzz.actions.WpcgjhAction; 12 import oracle.class4.gzz.entity.Wpcgjh; 13 import oracle.class4.gzz.utils.ExportsUtils; 14 /** 15 * 导出excel 16 * @author 17 * @date 2015年11月19日 下午5:22:14 18 */ 19 public class WpcgjhExport extends HttpServlet { 20 21 22 public void doGet(HttpServletRequest request, HttpServletResponse response) 23 throws ServletException, IOException { 24 25 doPost(request, response); 26 } 27 28 29 public void doPost(HttpServletRequest request, HttpServletResponse response) 30 throws ServletException, IOException { 31 32 try { 33 34 String fileName="物品采购信息表"; 35 36 response.setContentType("application/vnd.ms-excel"); 37 OutputStream os = response.getOutputStream();//取得输出流 38 response.reset();//清空输出流 39 40 //下面是对中文文件名的处理 41 response.setCharacterEncoding("UTF-8");//设置相应内容的编码格式 42 fileName = java.net.URLEncoder.encode(fileName,"UTF-8"); 43 //设置头文件 44 response.setHeader("Content-Disposition","attachment;filename="+new String(fileName.getBytes("UTF-8"),"GBK")+".xls"); 45 response.setContentType("application/msexcel");//定义输出类型 46 String wpcgjh_id=request.getParameter("wpcgjh_id"); 47 int wpcgjhid=Integer.parseInt(wpcgjh_id); 48 WpcgjhAction wpcgjhAction=new WpcgjhAction(); 49 //获取对象数据 50 Wpcgjh wpcgjh=wpcgjhAction.selectById(wpcgjhid); 51 ExportsUtils exports=new ExportsUtils(); 52 //调用导出方法 53 exports.ExportsExcel(os, wpcgjh); 54 //HttpSession session=request.getSession(); 55 // session.setAttribute("test", "导出成功"); 56 //session.setAttribute("url", "WpcgjhList"); 57 //request.getRequestDispatcher("temp.jsp").forward(request, response); 58 //session.removeAttribute(arg0); 59 //response.sendRedirect("temp.jsp"); 60 //request.getRequestDispatcher("tem.jsp"); 61 } catch (Exception e) { 62 e.printStackTrace(); 63 } 64 65 } 66 67 }
ExportsUtils 类中的ExportsExcel方法是对导出的excel表格的样式，字体等的设置：
EcportsUtils.java
1 package oracle.class4.gzz.utils; 2 3 import java.io.OutputStream; 4 5 6 import jxl.Workbook; 7 import jxl.format.UnderlineStyle; 8 import jxl.write.Label; 9 import jxl.write.WritableFont; 10 import jxl.write.WritableSheet; 11 import jxl.write.WritableWorkbook; 12 import oracle.class4.gzz.entity.Wpcgjh; 13 /** 14 * 导出工具类 15 * @author 16 * @date 2015年11月23日 上午8:37:52 17 */ 18 public class ExportsUtils { 19 20 //导出到excel表格 21 public void ExportsExcel(OutputStream os,Wpcgjh wpcgjh){ 22 23 //生成xls 24 try{ 25 26 WritableWorkbook book = Workbook.createWorkbook(os); 27 WritableSheet sheet = book.createSheet(" 第一页 ", 0); 28 // 文字样式 29 jxl.write.WritableFont wfc = new jxl.write.WritableFont( 30 WritableFont.ARIAL, 20, WritableFont.NO_BOLD, false, 31 UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); 32 33 34 jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat( 35 wfc); 36 37 38 // 设置单元格样式 39 wcfFC.setBackground(jxl.format.Colour.WHITE);// 单元格颜色 40 wcfFC.setAlignment(jxl.format.Alignment.CENTRE);// 单元格居中 41 wcfFC.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//垂直居中 42 wcfFC.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN); 43 //设置行高 44 sheet.setRowView(0,1000, false); 45 sheet.setRowView(1,500, false); 46 sheet.setRowView(2,500, false); 47 sheet.setRowView(3,500, false); 48 sheet.setRowView(4,500, false); 49 sheet.setRowView(5,500, false); 50 sheet.setRowView(6,500, false); 51 sheet.setRowView(7,500, false); 52 sheet.setRowView(8,500, false); 53 sheet.setRowView(9,1000, false); 54 //合并单元格 55 sheet.mergeCells(0, 0, 6, 0);//第一列，第一行，第七列，第一行 56 //单元格中插入数据 57 sheet.addCell(new Label(0, 0, "物品采购计划表", wcfFC)); //第一列，第一行 58 59 sheet.mergeCells(0, 1, 2, 1);//第一列，第二行，第三列，第二行 60 sheet.addCell(new Label(0, 1, "物品名", wcfFC)); 61 sheet.mergeCells(3, 1, 6, 1); 62 sheet.addCell(new Label(3, 1, wpcgjh.getWpcgjh_name(), wcfFC)); 63 64 sheet.mergeCells(0, 2, 2, 2); 65 sheet.addCell(new Label(0, 2, "现有库存", wcfFC)); 66 sheet.mergeCells(3, 2, 6, 2); 67 sheet.addCell(new Label(3, 2, wpcgjh.getWpcgjh_xykc()+"", wcfFC)); 68 69 sheet.mergeCells(0, 3, 2, 3); 70 sheet.addCell(new Label(0, 3, "最大库存", wcfFC)); 71 sheet.mergeCells(3, 3, 6, 3); 72 sheet.addCell(new Label(3, 3, wpcgjh.getWpcgjh_zdkc()+"", wcfFC)); 73 74 sheet.mergeCells(0, 4, 2, 4); 75 sheet.addCell(new Label(0, 4, "最大库存", wcfFC)); 76 sheet.mergeCells(3, 4, 6, 4); 77 sheet.addCell(new Label(3, 4, wpcgjh.getWpcgjh_zdkc()+"", wcfFC)); 78 79 sheet.mergeCells(0, 5, 2, 5); 80 sheet.addCell(new Label(0, 5, "购买数量", wcfFC)); 81 sheet.mergeCells(3, 5, 6, 5); 82 sheet.addCell(new Label(3, 5, wpcgjh.getWpcgjh_gmsl()+"", wcfFC)); 83 84 sheet.mergeCells(0, 6, 2, 6); 85 sheet.addCell(new Label(0, 6, "供应商", wcfFC)); 86 sheet.mergeCells(3, 6, 6, 6); 87 sheet.addCell(new Label(3, 6, wpcgjh.getWpcgjh_gys(), wcfFC)); 88 89 sheet.mergeCells(0, 7, 2, 7); 90 sheet.addCell(new Label(0, 7, "价格", wcfFC)); 91 sheet.mergeCells(3, 7, 6, 7); 92 sheet.addCell(new Label(3, 7, wpcgjh.getWpcgjh_jg()+"", wcfFC)); 93 94 sheet.mergeCells(0, 8, 2, 8); 95 sheet.addCell(new Label(0, 8, "计划采购时间", wcfFC)); 96 sheet.mergeCells(3, 8, 6, 8); 97 sheet.addCell(new Label(3, 8, wpcgjh.getWpcgjh_jhcgsj()+"", wcfFC)); 98 99 sheet.mergeCells(0, 9, 2, 9); 100 sheet.addCell(new Label(0, 9, "采购人签名：", wcfFC)); 101 sheet.mergeCells(3, 9, 6, 9); 102 sheet.addCell(new Label(3, 9, "", wcfFC)); 103 104 book.write(); 105 book.close(); 106 }catch(Exception e){ 107 System.out.println("生成信息表(Excel格式)时出错："); 108 e.printStackTrace(); 109 } 110 111 } 112 }
导出的excel表格的样式：
至此，导出excel就完成了。
