posts - 2, comments - 0, trackbacks - 0

功能需求：
        根據頁面條件查詢出數據記錄存儲到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 閱讀(946) 評論(0) 編輯收藏

新用戶注冊刷新評論列表


只有注冊用戶登錄后才能發表評論。




網站導航: 博客園 IT新聞 Chat2DB C++博客博問管理

<

2009年12月

>

日

一

二

三

四

五

六

29

30

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

1

2

3

4

5

6

7

8

9

常用鏈接

留言簿

隨筆分類

隨筆檔案

2009年12月 (2)

常用鏈接

留言簿

隨筆分類

隨筆檔案

搜索

最新評論

閱讀排行榜

評論排行榜