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