Java 数据库数据生成Excel

采用jxl.jar生成Excel

项目开发注意事项：

1:导入从网上下载的jar包：

mail.jar 和 activation.jar

2:删掉C:\Program Files\MyEclipse\Common\plugins\com.genuitec.eclipse.j2eedt.core_10.0.0.me201110301321\data\libraryset\EE_5 下  javaee.jar中的javax-->activation.jar和mail.jar （两个Jar包）。不删除会报错，原因是是jar包版本不统一

3.导入jxl.jar包

/* * @(#)DownloadServlet.java Time: 2013-2-28 * * Copyright 2013 xuedou.com All rights reserved. */package xuedou.skymoni.servlet;import java.io.IOException;import java.io.OutputStream;import java.util.List;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import common.Logger;import jxl.Workbook;import jxl.format.UnderlineStyle;import jxl.write.Label;import jxl.write.WritableCellFormat;import jxl.write.WritableFont;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;import jxl.write.WriteException;import xuedou.skymoni.bean.Exam;import xuedou.skymoni.impl.ExamImpl;import xuedou.skymoni.service.ExamService;/** *<pre>类说明</pre> *<b>功能描述：</b> * 下载Excel * @author  jinmingming [e-mail] * @version 1.0, 2013-2-28 */public class DownloadServlet extends HttpServlet {public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {ExamService examService = new ExamImpl();OutputStream os=null;try {os = response.getOutputStream();} catch (IOException e1) {Logger.getLogger(DownloadServlet.class).error("获取response输出流出错");}    response.reset();    response.setContentType("application/msexcel");    String fileName="市外模拟考信息数据.xls";response.setHeader("Content-Disposition", "attachment;"+ " filename="+ new String(fileName.getBytes(), "ISO-8859-1"));WritableWorkbook wwb=null;WritableSheet ws=null;try {wwb = Workbook.createWorkbook(os);ws=wwb.createSheet("市外模拟考信息数据",0);ws.getSettings().setDefaultColumnWidth(15);//创建表头WritableFont wfc = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.RED);            WritableCellFormat wcfFC = new WritableCellFormat(wfc);            Label topid = new Label(0,0,"编号",wcfFC);Label topsname = new Label(1,0,"姓名",wcfFC);Label topschool = new Label(2,0,"学校",wcfFC);Label toptel = new Label(3,0,"手机",wcfFC);Label topissky = new Label(4,0,"是否在蓝天就读",wcfFC);Label topexamschool = new Label(5,0,"考试校区",wcfFC);Label topexamtime = new Label(6,0,"考试时段",wcfFC);Label topexamnum = new Label(7,0,"考试场次",wcfFC);Label topticknum = new Label(8,0,"准考证号",wcfFC);ws.addCell(topid);ws.addCell(topsname);ws.addCell(topschool);ws.addCell(toptel);ws.addCell(topissky);ws.addCell(topexamschool);ws.addCell(topexamtime);ws.addCell(topexamnum);ws.addCell(topticknum);Label id = null;        Label sname = null;Label school = null;Label tel = null;Label issky = null;Label examschool = null;Label examtime = null;Label examnum = null;Label ticknum = null;List<Exam> list = examService.allExam();  //得到List结果集int listsize = list.size();            for (int i = 1; i <= listsize; i++){                 //遍历封装Exam exam = list.get(i-1);id = new Label(0,i,Integer.toString(i));sname = new Label(1,i,exam.getSname());school = new Label(2,i,exam.getSchool());tel = new Label(3,i,exam.getTel());issky = new Label(4,i,exam.getIssky());examschool = new Label(5,i,exam.getExamschool());examtime = new Label(6,i,exam.getExamtime());examnum = new Label(7,i,exam.getExamnum());ticknum = new Label(8,i,exam.getTicknum());ws.addCell(id);ws.addCell(sname);ws.addCell(school);ws.addCell(tel);ws.addCell(issky);ws.addCell(examschool);ws.addCell(examtime);ws.addCell(examnum);ws.addCell(ticknum);                            }} catch (Exception e) {Logger.getLogger(DownloadServlet.class).error("输出Excel失败");}finally{try {wwb.write();wwb.close();os.close();} catch (WriteException e) {Logger.getLogger(DownloadServlet.class).error("关闭WritableWorkbook出错");} catch (IOException e) {Logger.getLogger(DownloadServlet.class).error("关闭WritableWorkbook出错");}}}public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {doPost(request, response);}}

JSP页面：

<a href="${pageContext.request.contextPath }/DownloadServlet">点击下载市外模拟考信息数据</a>