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 jinmingming@xuedou.com
 * @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>