1.項目增加導出日志信息
2.項目中導入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導出就是根據前臺條件將參數傳到controller，根據參數去數據庫中進行查詢，查詢出list集合，將list集合生成excle數據下載。
代碼片段：
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
|
/** * 導出信息 * @param model */ @RequestMapping("exportCustomer.do") @SystemControllerLog(description = "數據庫表單導出Excle") public void exportCustomer(ModelMap model) { //TODO 如需添加條件 //model.addAttribute("username", nameStr); //獲取需要導出的數據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"); // 以導出時間作為文件名 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) { // 創建一個webbook，對應一個excel文件 HSSFWorkbook workbook = new HSSFWorkbook(); // 在webbook中添加一個sheet,對應excel文件中的sheet HSSFSheet sheet = workbook.createSheet("客戶信息表"); // 設置列寬 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); // 創建單元格，并設置表頭，設置表頭居中 HSSFCellStyle style = workbook.createCellStyle(); // 創建一個居中格式 style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 帶邊框 style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 生成一個字體 HSSFFont font = workbook.createFont(); // 字體增粗 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字體大小 font.setFontHeightInPoints((short) 12); // 把字體應用到當前的樣式 style.setFont(font); // 單獨設置整列居中或居左 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("狀態"); 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); // 創建單元格，并設置值 // 編號列居左 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();//客戶狀態1.在職，2.離職 String status=""; if ("1".equals(statusStr)) { status="在職"; } if ("2".equals(statusStr)) { status="離職"; } HSSFCell c4 = row.createCell(3);//狀態 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調用
|
1
2
3
4
5
6
7
8
9
10
11
|
//導出信息 function exporBtn(){ $.ajax({ type:"POST", url:"<%=path%>/customer/exportCustomer.do", success:function(data){ window.open('<%=path%>/customer/exportCustomer.do'); } }); } |
以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支持服務器之家。
原文鏈接：http://blog.csdn.net/fiangasdre/article/details/51741580
