功能需求：
根据页面条件查询出数据记录存储到excel中并将此文件压缩至zip文件中，用户点击导出到excel时会弹出选择文件存储路径对话框，选择好后该zip文件会存储在指定位置。
功能注意事项：
1.当数据量大时，每次从数据库中取多少条记录到excel中？
2.临时文件的存储位置
功能代码：
1.功能查询语句
1
public String query(NetClearingResultForm frm)
{
2
StringBuffer sf= new StringBuffer();
3 sf.append("SELECT T.BILL_CYCLE_SEQ,T.PARTNER_ID,A.PARTNER_NAME,T.USAGE_TYPE_ID,B.USAGE_TYPE_NAME,T.UR_KEYS_ID,C.UR_KEY_NAME,T.DATA_SRC,T.USER_FEE,T.BALANCE_FEE,T.PAY_FEE,T.LEAVE_FEE,T.CREATE_DATE FROM t_balance_result T, T_PARTNER A,T_USAGE_TYPE B,t_usage_rate_keys C WHERE T.PARTNER_ID = A.PARTNER_ID AND T.USAGE_TYPE_ID =B.USAGE_TYPE_ID AND T.UR_KEYS_ID = C.UR_KEYS_ID ");
4
if(frm.getBillCycleSeq()!=null&&!"".equals(frm.getBillCycleSeq())){
5 sf.append("and t.BILL_CYCLE_SEQ = "+frm.getBillCycleSeq());
6
}
7 if(frm.getPartnerId()!=null&&!"".equals(frm.getPartnerId())){
8 sf.append("and t.PARTNER_ID ="+frm.getPartnerId());
9 }
10 System.out.println("querySql>>>>>>"+sf.toString());
11 return sf.toString();
12
}
2.规定每次取出的记录数
1/**//*---------------*/
2 /** *//**
3 *@param count 查询结果的总记录数
4 *@param shu 查询结果的总页数，即分多少次查询，每次以30000行为准
5 *@param i 当前查询的页数，即第几次查询
6 *@param frm 表单对应的frm,用来取得页面表单值
7 */
8 public String excelList(int count,int shu,int i,NetClearingResultForm frm){
9 StringBuffer sf= new StringBuffer();
10 int rowNum=0;
11 if(i==0&&shu!=0){
12 //如果是第一次并且总页数不等于0，取30000
13 rowNum=30000;
14 }else if(i==shu){
15 //如果页数等于总页数，则取出最后一页的记录数
16 rowNum=count%30000;
17 }else{
18 //如果都不满足，就取得i*30000条记录
19 rowNum=i*30000;
20 }
21 sf.append("SELECT * from (");
22 sf.append(" select * from ");
23 sf.append(" ( ");
24 sf.append(" ").append(this.query(frm).toString());
25 sf.append(" ) ");
26 sf.append(" where ROWNUM<=").append(rowNum);
27 sf.append(" order by ROWNUM desc ");
28 sf.append(" ) ");
29 sf.append(" where ROWNUM<=").append(i==shu?count%30000:30000);
30 sf.append(" order by ROWNUM asc ");
31 System.out.println("querySql>>>>>>"+sf.toString());
32 return sf.toString();
33 }
3.取出总记录数的方法
1public String count(NetClearingResultForm frm){
2 StringBuffer sf= new StringBuffer();
3 sf.append("SELECT COUNT(*) COUNT FROM t_balance_result t WHERE 1=1 ");
4 if(frm.getBillCycleSeq()!=null&&!"".equals(frm.getBillCycleSeq())){
5 sf.append("and t.BILL_CYCLE_SEQ = "+frm.getBillCycleSeq());
6 }
7 if(frm.getPartnerId()!=null&&!"".equals(frm.getPartnerId())){
8 sf.append("and t.PARTNER_ID ="+frm.getPartnerId());
9 }
10 System.out.println("querySql>>>>>>"+sf.toString());
11 return sf.toString();
12 }
4.根据查询的结果集list，生成相应的excel，这里使用apache的
HSSFWorkbook这个类
1public HSSFWorkbook excelTitle(List record,int recordNum,int j){
2 /**//*---------------创建excel的book-----------------*/
3 HSSFWorkbook wb = new HSSFWorkbook();
4 /**//*---------------创建excel的sheet----------------*/
5 HSSFSheet sheet = wb.createSheet("NetClearing");
6 /**//*---------------创建excel的row -----------------*/
7 HSSFRow row = sheet.createRow(0);
8 /**//*---------------创建excel的头cell----------------*/
9 HSSFCell cell = row.createCell((short) 0);
10 cell.setEncoding(HSSFCell.ENCODING_UTF_16);
11 cell.setCellValue("账期");
12 cell = row.createCell((short) 1);
13 cell.setEncoding(HSSFCell.ENCODING_UTF_16);
14 cell.setCellValue("运营商名称");
15 cell = row.createCell((short) 2);
16 cell.setEncoding(HSSFCell.ENCODING_UTF_16);
17 cell.setCellValue("用户发生费用");
18 cell = row.createCell((short) 3);
19 cell.setEncoding(HSSFCell.ENCODING_UTF_16);
20 cell.setCellValue("结算费用");
21 cell = row.createCell((short) 4);
22 cell.setEncoding(HSSFCell.ENCODING_UTF_16);
23 cell.setCellValue("应付费用");
24 cell = row.createCell((short) 5);
25 cell.setEncoding(HSSFCell.ENCODING_UTF_16);
26 cell.setCellValue("剩余费用");
27 int k=1;
28 /**//*---------------获得excel的记录数----------------*/
29 /**//*---------------j=0开始----------------*/
30 int m=((j*30000+30000)>recordNum?recordNum:(j*30000+30000));
31 for(int i=j*30000;i<m;i++){
32 System.out.println("----------------取得数据库的值-----------------------");
33 HashMap hashMap = new HashMap();
34 /**//*---------------list里的记录数与excel里的一致----------------*/
35 hashMap = (HashMap) record.get(i);
36 HSSFRow row2 = sheet.createRow(k);
37 k++;
38 cell = row2.createCell((short) 0);
39 cell.setEncoding(HSSFCell.ENCODING_UTF_16);
40 cell.setCellValue(hashMap.get("BILL_CYCLE_SEQ")==null?"":hashMap.get("BILL_CYCLE_SEQ").toString());
41 cell = row2.createCell((short) 1);
42 cell.setEncoding(HSSFCell.ENCODING_UTF_16);
43 cell.setCellValue(hashMap.get("PARTNER_NAME")==null?"":hashMap.get("PARTNER_NAME").toString());
44 cell = row2.createCell((short) 2);
45 cell.setEncoding(HSSFCell.ENCODING_UTF_16);
46 cell.setCellValue(hashMap.get("USER_FEE")==null?"":hashMap.get("USER_FEE").toString());
47 cell = row2.createCell((short) 3);
48 cell.setEncoding(HSSFCell.ENCODING_UTF_16);
49 cell.setCellValue(hashMap.get("BALANCE_FEE")==null?"":hashMap.get("BALANCE_FEE").toString());
50 cell = row2.createCell((short) 4);
51 cell.setEncoding(HSSFCell.ENCODING_UTF_16);
52 cell.setCellValue(hashMap.get("PAY_FEE")==null?"":hashMap.get("PAY_FEE").toString());
53 cell = row2.createCell((short) 5);
54 cell.setEncoding(HSSFCell.ENCODING_UTF_16);
55 cell.setCellValue(hashMap.get("LEAVE_FEE")==null?"":hashMap.get("LEAVE_FEE").toString());
56 }
57 return wb;
58
59 }
5.在action中生成zip文件和excel文件并实现下载
1
public String excel(ActionMapping mapping, ActionForm form,
2 HttpServletRequest request, HttpServletResponse response)
3 throws Exception {
4 System.out
5 .println("NetClearingResult query begin>>>>>>>>>>>>>>>>>>>>>>>>>>");
6 NetClearingResultForm frm = (NetClearingResultForm) form;
7 DatabaseAccess dba = new DatabaseAccess("");
8 NetClearingResultBean bean = new NetClearingResultBean();
9 List record = dba.doQueryAll(bean.query(frm));
10 Map map = dba.doQuery(bean.count(frm));
11 int count = Integer.parseInt((map.get("COUNT") == null ? "0" : map
12 .get("COUNT")).toString());
13 /**//*--------------定义多少个3万行,分次从数据库中取出记录，每次3万行------------------*/
14 int shu = 0;
15 if (count == 0) {
16 frm.setMessage("无网间结算统计汇总报表导出！");
17 request.setAttribute("pageList", new ArrayList());
18 return "query";
19 } else {
20 if (count < 30000) {
21 shu = 1;
22 } else {
23 shu = count % 30000 == 0 ? count / 30000 : count / 30000 + 1;
24 }
25 }
26 /**//*-----------在服务器classpath下建立zip压缩文件---------*/
27 Date date = new Date();
28 SimpleDateFormat simpleDateFormat = new SimpleDateFormat(
29 "yyyyMMddkkmmss");
30 String today = simpleDateFormat.format(date);
31 BufferedInputStream bis = null;
32 byte[] data = new byte[BUFFER];
33 File zipFile = new File("netClearing-"
34 + request.getRemoteAddr() + "-" + today + ".zip");
35 FileOutputStream fout = new FileOutputStream(zipFile, true);
36 ZipOutputStream zout = new ZipOutputStream(fout);
37 try {
38 for (int i = 0; i < shu; i++) {
39 System.out.println("--------------------------1");
40 record = dba.doQueryAll(bean.excelList(count, shu, i, frm));
41 int recordNum = record.size();
42 System.out.println("--------------------------recordNum "+recordNum);
43 int count_record = 0;
44 if (recordNum % 30000 == 0) {
45 count_record = recordNum / 30000;
46 } else {
47 count_record = recordNum / 30000 + 1;
48 }
49 System.out.println("count_record --------------->"+count_record);
50 for (int j = 0; j < count_record; j++) {
51 HSSFWorkbook wb = bean.excelTitle(record, recordNum, j);
52 System.out.println("wb -----------------------"+wb.getSheetName(0));
53 /**//*-------将excel存储到文件输出流-----------*/
54 File file = new File("netClearing"
55 + (i + 1) + (j + 1) + "-" + request.getRemoteAddr()
56 + "-" + today + ".xls");
57 FileOutputStream eOut = new FileOutputStream(file);
58 wb.write(eOut);
59 /**//*-------将excel放到zipfile里------------*/
60
61 FileInputStream fi = new FileInputStream(file);
62 bis = new BufferedInputStream(fi, BUFFER);
63 System.out.println("fileName ------------------>>>>"+file.getName());
64 try {
65 ZipEntry zipEntry = new ZipEntry(file.getName());
66 zout.putNextEntry(zipEntry);
67 int lenght;
68 while ((lenght = bis.read(data, 0, BUFFER)) != -1) {
69 zout.write(data, 0, lenght);
70 }
71 } catch (Exception e) {
72 e.printStackTrace();
73 } finally {
74 fi.close();
75 bis.close();
76 eOut.close();
77 file.delete();
78 }
79 }
80 zout.close();
81 }
82 /**//*------------------------下载zip-------------------------*/
83 long filesize = zipFile.length();
84 FileInputStream fileIn = new FileInputStream(zipFile);
85 response.reset();
86 response.setContentType("bin");
87 response.addHeader("content_type", "application/x-msdownload");
88 response.addHeader("Content-Disposition",
89 "attachment;filename=actionProLog-"
90 + request.getRemoteAddr() + "-" + today + ".zip");
91 response.addHeader("content-length", Long.toString(filesize));
92 byte bytes[] = new byte[500];
93 int len;
94 while ((len = fileIn.read(bytes)) != -1) {
95 response.getOutputStream().write(bytes, 0, len);
96 }
97 fileIn.close();
98 } catch (Exception e) {
99 e.printStackTrace();
100 } finally {
101 zipFile.delete();
102 }
103 return "";
104 }
posted on 2009-12-16 09:16
fer2005 阅读(929)
评论(0) 编辑 收藏