Servlet,JSP 动态生成excel文件并提示下载的简单方法

最新推荐文章于 2021-03-16 13:00:38 发布

转载最新推荐文章于 2021-03-16 13:00:38 发布 · 1.3k 阅读

文章标签：

#excel #servlet #jsp #string #user #email

本文介绍了一种使用Servlet和JSP动态生成Excel文件的方法，并通过HTTP响应实现文件下载。主要步骤包括配置jxl库、设置HTTP响应头、创建工作簿及工作表、填充数据等。

Servlet,JSP 动态生成excel文件并提示下载的简单方法

1.下载jxl.rar包

项目地址： 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     */
5    public ActionForward makeRichVoteRZ(ActionMapping mapping, ActionForm form,
6            HttpServletRequest request, HttpServletResponse response)
7            throws Exception {
8
9        //读出数据
10        String richvote_id = Common.getValue("richvote_id", request);
11        String 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+"'))";
12        RowSet rs = table.select(sql);
13
14        //生成xls
15        try{
16
17            response.setContentType("application/vnd.ms-excel");
18            response.addHeader("Content-Disposition","attachment;   filename=\""   +  Common.getFileName()+".xls"  +   "\"");
19            OutputStream os = response.getOutputStream();
20            WritableWorkbook wwb = Workbook.createWorkbook(os);
21
22
23            int ncout = rs.length();
24            int maxnum = 50000; //一次最多写入量
25            int times = (ncout+maxnum-1)/maxnum;
26
27            //大循环
28            for(int t=0; t<times; t++){
29
30                //新建一张表
31                WritableSheet wsheet = wwb.createSheet("members_"+(t+1),t);
32                //设置表头
33                Label label = new Label(0,0,"");
34                wsheet.addCell(label);
35                label = new Label(0,0,"会员姓名");
36                wsheet.addCell(label);
37                label = new Label(1,0,"卡号");
38                wsheet.addCell(label);
39                label = new Label(2,0,"联系地址");
40                wsheet.addCell(label);
41                label = new Label(3,0,"邮编");
42                wsheet.addCell(label);
43                label = new Label(4,0,"联系电话");
44                wsheet.addCell(label);
45                label = new Label(5,0,"手机");
46                wsheet.addCell(label);
47                label = new Label(6,0,"Email");
48                wsheet.addCell(label);
49                label = new Label(7,0,"性别");
50                wsheet.addCell(label);
51
52
53                //读出数据
54                int base = (t*maxnum);
55                for(int i = 0; i < rs.length(); i++){
56                    Row rw = rs.get(i+base);
57                    //System.out.println((i+1));
58                    label = new Label(0,(i+1),(String)rw.get("user_name")    );
59                    wsheet.addCell(label);
60                    label = new Label(1,(i+1),(String)rw.get("card_id"));
61                    wsheet.addCell(label);
62                    label = new Label(2,(i+1),(String)rw.get("user_address"));
63                    wsheet.addCell(label);
64                    label = new Label(3,(i+1),(String)rw.get("postalcode"));
65                    wsheet.addCell(label);
66                    label = new Label(4,(i+1),(String)rw.get("tel_day"));
67                    wsheet.addCell(label);
68                    label = new Label(5,(i+1),(String)rw.get("mobile"));
69                    wsheet.addCell(label);
70                    label = new Label(6,(i+1),(String)rw.get("email"));
71                    wsheet.addCell(label);
72                    label = new Label(7,(i+1),(String)rw.get("user_sex"));
73                    wsheet.addCell(label);
74                }
75
76            }//结束大循环
77
78            wwb.write();
79            wwb.close();
80            os.close();
81            response.flushBuffer();
82
83        }catch(Exception e){
84            System.out.println("生成信息表(Excel格式)时出错：");
85            e.printStackTrace();
86        }
87
88        return 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.完成