java数据生成excel_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;

/**

*

类说明

*功能描述：

* 下载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 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页面：

点击下载市外模拟考信息数据