功能需求:
根据页面条件查询出数据记录存储到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.取出总记录数的方法
1
public 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这个类
1
public 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) 编辑 收藏