项目地址: http://www.andykhan.com/jexcelapi/
下载地址: http://www.andykhan.com/jexcelapi/download.html
目前版本的信息
- Reads data from Excel 95, 97, 2000 workbooks
- Reads and writes formulas (Excel 97 and later only)
- Generates spreadsheets in Excel 2000 format
- Supports font, number and date formatting
- Supports shading and colouring of cells
- Modifies existing worksheets
- Supports image creation
- Preserves macros on copy
- Customizable logging
2.把包放到WEB-INF的lib目录下在开发环境中引入这个包
3.开始写代码了,这里以一个Struts1.2的ActionMethod为例,其实只要能取了request和response对象,操作都是一样的的。
1/**
2* 生成信息的XLS
3* alex 2007-7-3 下午05:01:56
4*/
5public ActionForward makeRichVoteRZ(ActionMapping mapping, ActionForm form,
6HttpServletRequest request, HttpServletResponse response)
7throws Exception {
8
9//读出数据
10String richvote_id = Common.getValue("richvote_id", request);
11String sql = "select user_name,user_sex,user_address,card_id,postalcode,mobile,tel_day,email from tbl_member where member_id in (select user_id from tbl_vote_detail where vote_id in(select vote_id from tbl_vote where vote_board = '"+richvote_id+"'))";
12RowSet rs = table.select(sql);
13
14//生成xls
15try{
16
17response.setContentType("application/vnd.ms-excel");
18response.addHeader("Content-Disposition","attachment; filename=\"" + Common.getFileName()+".xls" + "\"");
19OutputStream os = response.getOutputStream();
20WritableWorkbook wwb = Workbook.createWorkbook(os);
21
22
23int ncout = rs.length();
24int maxnum = 50000; //一次最多写入量
25int times = (ncout+maxnum-1)/maxnum;
26
27//大循环
28for(int t=0; t<times; t++){
29
30//新建一张表
31WritableSheet wsheet = wwb.createSheet("members_"+(t+1),t);
32//设置表头
33Label label = new Label(0,0,"");
34wsheet.addCell(label);
35label = new Label(0,0,"会员姓名");
36wsheet.addCell(label);
37label = new Label(1,0,"卡号");
38wsheet.addCell(label);
39label = new Label(2,0,"联系地址");
40wsheet.addCell(label);
41label = new Label(3,0,"邮编");
42wsheet.addCell(label);
43label = new Label(4,0,"联系电话");
44wsheet.addCell(label);
45label = new Label(5,0,"手机");
46wsheet.addCell(label);
47label = new Label(6,0,"Email");
48wsheet.addCell(label);
49label = new Label(7,0,"性别");
50wsheet.addCell(label);
51
52
53//读出数据
54int base = (t*maxnum);
55for(int i = 0; i < rs.length(); i++){
56Row rw = rs.get(i+base);
57//System.out.println((i+1));
58label = new Label(0,(i+1),(String)rw.get("user_name") );
59wsheet.addCell(label);
60label = new Label(1,(i+1),(String)rw.get("card_id"));
61wsheet.addCell(label);
62label = new Label(2,(i+1),(String)rw.get("user_address"));
63wsheet.addCell(label);
64label = new Label(3,(i+1),(String)rw.get("postalcode"));
65wsheet.addCell(label);
66label = new Label(4,(i+1),(String)rw.get("tel_day"));
67wsheet.addCell(label);
68label = new Label(5,(i+1),(String)rw.get("mobile"));
69wsheet.addCell(label);
70label = new Label(6,(i+1),(String)rw.get("email"));
71wsheet.addCell(label);
72label = new Label(7,(i+1),(String)rw.get("user_sex"));
73wsheet.addCell(label);
74}
75
76}//结束大循环
77
78wwb.write();
79wwb.close();
80os.close();
81response.flushBuffer();
82
83}catch(Exception e){
84System.out.println("生成信息表(Excel格式)时出错:");
85e.printStackTrace();
86}
87
88return null;
89}
代码简单说明:
1.设定好response的相关属性:
response.setContentType("application/vnd.ms-excel");
response.addHeader("Content-Disposition","attachment; filename=\"" + Common.getFileName()+".xls" + "\"");
2.取到response的OutputStream实例,并用这个实例化一个WritableWorkbook对象
OutputStream os = response.getOutputStream();
WritableWorkbook wwb = Workbook.createWorkbook(os);
3.新建一个表
WritableSheet wsheet = wwb.createSheet("members_"+(t+1),t);
4.往表里加行头
Label label = new Label(0,0,"");
wsheet.addCell(label);
label = new Label(0,0,"会员姓名");
wsheet.addCell(label);
label = new Label(1,0,"卡号");
wsheet.addCell(label);
label = new Label(2,0,"联系地址");
wsheet.addCell(label);
label = new Label(3,0,"邮编");
wsheet.addCell(label);
label = new Label(4,0,"联系电话");
wsheet.addCell(label);
label = new Label(5,0,"手机");
wsheet.addCell(label);
label = new Label(6,0,"Email");
wsheet.addCell(label);
label = new Label(7,0,"性别");
wsheet.addCell(label);
5.往表里加数据行
for(int i = 0; i < rs.length(); i++){
Row rw = rs.get(i+base);
//System.out.println((i+1));
label = new Label(0,(i+1),(String)rw.get("user_name") );
wsheet.addCell(label);
label = new Label(1,(i+1),(String)rw.get("card_id"));
wsheet.addCell(label);
label = new Label(2,(i+1),(String)rw.get("user_address"));
wsheet.addCell(label);
label = new Label(3,(i+1),(String)rw.get("postalcode"));
wsheet.addCell(label);
label = new Label(4,(i+1),(String)rw.get("tel_day"));
wsheet.addCell(label);
label = new Label(5,(i+1),(String)rw.get("mobile"));
wsheet.addCell(label);
label = new Label(6,(i+1),(String)rw.get("email"));
wsheet.addCell(label);
label = new Label(7,(i+1),(String)rw.get("user_sex"));
wsheet.addCell(label);
}
6.把生成的excel数据输出到response的OutputStream
wwb.write();
wwb.close();
os.close();
response.flushBuffer();
7.完成