1.項目增加導(dǎo)出日志信息
2.項目中導(dǎo)入poi-*.jar等操作excel文件的jar文件
- poi-3.7-20120326.jar
- poi-excelant-3.7-20101029.jar
- poi-ooxml-3.7.jar
- poi-ooxml-schemas-3.7.jar
Excel導(dǎo)出就是根據(jù)前臺條件將參數(shù)傳到controller，根據(jù)參數(shù)去數(shù)據(jù)庫中進行查詢，查詢出list集合，將list集合生成excle數(shù)據(jù)下載。
代碼片段：
Contorller.Java
|
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
|
/** * 導(dǎo)出信息 * @param model */ @RequestMapping("exportCustomer.do") @SystemControllerLog(description = "數(shù)據(jù)庫表單導(dǎo)出Excle") public void exportCustomer(ModelMap model) { //TODO 如需添加條件 //model.addAttribute("username", nameStr); //獲取需要導(dǎo)出的數(shù)據(jù)List List<CMcustomer> cusList=customerService.exportCustomer(model); //使用方法生成excle模板樣式 HSSFWorkbook workbook = customerService.createExcel(cusList, request); SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss"); // 定義文件名格式 try { //定義excle名稱 ISO-8859-1防止名稱亂碼 String msg = new String( ("客戶信息_" + format.format(new Date()) + ".xls").getBytes(), "ISO-8859-1"); // 以導(dǎo)出時間作為文件名 response.setContentType("application/vnd.ms-excel"); response.addHeader("Content-Disposition", "attachment;filename=" + msg); workbook.write(response.getOutputStream()); } catch (IOException e) { logger.error(e); } } |
2.Service中createExcel方法
|
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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
|
public HSSFWorkbook createExcel(List<CMcustomer> cusList, HttpServletRequest request) { // 創(chuàng)建一個webbook，對應(yīng)一個excel文件 HSSFWorkbook workbook = new HSSFWorkbook(); // 在webbook中添加一個sheet,對應(yīng)excel文件中的sheet HSSFSheet sheet = workbook.createSheet("客戶信息表"); // 設(shè)置列寬 sheet.setColumnWidth(0, 25 * 100); sheet.setColumnWidth(1, 35 * 100); sheet.setColumnWidth(2, 35 * 100); sheet.setColumnWidth(3, 40 * 100); sheet.setColumnWidth(4, 45 * 100); sheet.setColumnWidth(5, 45 * 100); sheet.setColumnWidth(6, 50 * 100); sheet.setColumnWidth(7, 80 * 100); sheet.setColumnWidth(8, 35 * 100); sheet.setColumnWidth(9, 40 * 100); // 在sheet中添加表頭第0行 HSSFRow row = sheet.createRow(0); // 創(chuàng)建單元格，并設(shè)置表頭，設(shè)置表頭居中 HSSFCellStyle style = workbook.createCellStyle(); // 創(chuàng)建一個居中格式 style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 帶邊框 style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 生成一個字體 HSSFFont font = workbook.createFont(); // 字體增粗 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字體大小 font.setFontHeightInPoints((short) 12); // 把字體應(yīng)用到當前的樣式 style.setFont(font); // 單獨設(shè)置整列居中或居左 HSSFCellStyle style1 = workbook.createCellStyle(); style1.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle style2 = workbook.createCellStyle(); style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); HSSFCellStyle style3 = workbook.createCellStyle(); style3.setAlignment(HSSFCellStyle.ALIGN_LEFT); HSSFFont hssfFont = workbook.createFont(); hssfFont.setColor(HSSFFont.COLOR_RED); hssfFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); style3.setFont(hssfFont); HSSFCellStyle style4 = workbook.createCellStyle(); style4.setAlignment(HSSFCellStyle.ALIGN_LEFT); HSSFFont hssfFont1 = workbook.createFont(); hssfFont1.setColor(HSSFFont.COLOR_NORMAL); hssfFont1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); style4.setFont(hssfFont1); HSSFCell cell = row.createCell(0); cell.setCellValue("序號"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("客戶姓名"); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue("性別"); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue("狀態(tài)"); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue("電話"); cell.setCellStyle(style); cell = row.createCell(5); cell.setCellValue("郵箱"); cell.setCellStyle(style); cell = row.createCell(6); cell.setCellValue("地址"); cell.setCellStyle(style); for (int i = 0; i < cusList.size(); i++) { String logTypeDis = ""; row = sheet.createRow(i + 1); CMcustomer cMcustomer = cusList.get(i); // 創(chuàng)建單元格，并設(shè)置值 // 編號列居左 HSSFCell c1 = row.createCell(0); c1.setCellStyle(style2); c1.setCellValue(i); HSSFCell c2 = row.createCell(1); c2.setCellStyle(style1); c2.setCellValue(cMcustomer.getCustomername());//客戶姓名 String sexStr = cMcustomer.getSex();//性別 0:女,1:男 String sex=""; if ("1".equals(sexStr)) { sex="男"; } if ("0".equals(sexStr)) { sex="女"; } HSSFCell c3 = row.createCell(2);//性別 c3.setCellStyle(style1); c3.setCellValue(sex); String statusStr = cMcustomer.getStatus();//客戶狀態(tài)1.在職，2.離職 String status=""; if ("1".equals(statusStr)) { status="在職"; } if ("2".equals(statusStr)) { status="離職"; } HSSFCell c4 = row.createCell(3);//狀態(tài) c4.setCellStyle(style1); c4.setCellValue(status); String customerid = cMcustomer.getCustomerid();//客戶id List<CMphone> phoneList = cMphoneMapper.selectByCustomerid(customerid); String phone=""; if (phoneList!=null&&phoneList.size()>0) { for (int j = 0; j < phoneList.size(); j++) { phone = phoneList.get(j).getPhone(); } } HSSFCell c5 = row.createCell(4);//電話 c5.setCellStyle(style1); c5.setCellValue(phone); List<CMemail> emailList = cMemailMapper.selectAll(customerid); String email=""; if (emailList!=null&&emailList.size()>0) { for (int j = 0; j < emailList.size(); j++) { email = emailList.get(j).getEmail(); } } HSSFCell c6 = row.createCell(5);//郵箱 c6.setCellStyle(style1); c6.setCellValue(email); CMaddress cMaddress=new CMaddress(); cMaddress.setCustomerid(customerid); List<CMaddress> adderssList = cMaddressMapper.selectAll(cMaddress); String adderss=""; if (adderssList!=null&&adderssList.size()>0) { for (int j = 0; j < adderssList.size(); j++) { adderss = adderssList.get(j).getAddress(); } } HSSFCell c7 = row.createCell(6);//地址 c7.setCellStyle(style1); c7.setCellValue(adderss); //使用默認格式 row.createCell(1).setCellValue(cMcustomer.getCustomername()); row.createCell(2).setCellValue(sex); row.createCell(3).setCellValue(status); row.createCell(4).setCellValue(phone); row.createCell(5).setCellValue(email); row.createCell(6).setCellValue(adderss); } return workbook; } |
3.頁面jsp調(diào)用
|
1
2
3
4
5
6
7
8
9
10
11
|
//導(dǎo)出信息 function exporBtn(){ $.ajax({ type:"POST", url:"<%=path%>/customer/exportCustomer.do", success:function(data){ window.open('<%=path%>/customer/exportCustomer.do'); } }); } |
以上就是本文的全部內(nèi)容，希望對大家的學習有所幫助，也希望大家多多支持服務(wù)器之家。
原文鏈接：http://blog.csdn.net/fiangasdre/article/details/51741580
