java实现excel下载功能实例

1
2
&nbsp;&nbsp;&nbsp;<button class="saveRH bi-btn bi-btn-primary" onclick="exportUserPage()">导出当前页</button>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
function exportUserPage(){
var pageNow = $("#pageNow").val();
var gender = "";
$('input[name="gender"]:checked').each(function(){
gender += $(this).val()+",";
});
gender = deleteLastComma(gender);
var age = $('input[name="age"]').val();
var experience = $('input[name="experience"]').val();
var json = {
gender:gender,
age:age,
experience:experience,
pageNow:pageNow
}
// 注意这里因为url中有中文等字符，所以需要编码
// 还有这里url中/userFilter前添加了..,取的是相对路径
location.href=encodeURI('../userFilter/exportUserPage.action?parameterMap='+ JSON.stringify(json));
}

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

@RequestMapping(value="/exportUserPage.action")
public
void exportUserPage( HttpServletResponse response,HttpServletRequest request){
Map parameterMap = null;
try {
// 对应前端的编码，在这里进行解码，并解析json为Map
String value = URLDecoder.decode(request.getParameter("parameterMap"), "UTF-8");
parameterMap = (Map)JSON.parse(value);
System.out.println("value");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
Map<String, String> queryCondition = parameterToQueryCondition(parameterMap);
// 1 获取当前页数
Object pageObj = parameterMap.get(GeneralConstants.PAGE_NOW);
Integer pageNow = PageUtil.getPage(pageObj);
// 2 获取实际数据
int start = (pageNow - 1) * PAGE_SIZE;//开始条数
int end = PAGE_SIZE;
List<Map<String, Object>> list = userFilterService.listDataWithPage(start,end,queryCondition);
// 3 生成excel
String []title = {"UID","姓名","学历","姓别","求职状态","求职期望","是否有boss身份","期望工作地区","工作年限","工作经历","教育经历"};
HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook("SheetName",title,list,null);
// 4 响应到客户端,弹出下载提示框
try {
this.setResponseHeader(response, "user"+DateUtil.getCurrentDate()+".xls");
OutputStream os = response.getOutputStream();
wb.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
logger.error("excel导出错误："+e);
}
return ;
}
/**
* 功能描述: 发送响应流方法
*
* @param
* @return
* @auther mazhen
* @date 2018/12/11 下午2:17
*/
public void setResponseHeader(HttpServletResponse response, String fileName) {
try {
try {
fileName = new String(fileName.getBytes(),"ISO8859-1");
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
logger.error("excel导出错误："+e);
}
response.setContentType("application/octet-stream;charset=ISO8859-1");
response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception ex) {
ex.printStackTrace();
logger.error("excel导出错误："+ex);
}
}

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
public class ExcelUtil {
/**
* 导出Excel
* @param sheetName sheet名称
* @param title 标题
* @param mapList 内容
* @param wb HSSFWorkbook对象
* @return
*/
public static HSSFWorkbook getHSSFWorkbook(String sheetName, String []title, List<Map<String,Object>> mapList, HSSFWorkbook wb){
// 第一步，创建一个HSSFWorkbook，对应一个Excel文件
if(wb == null){
wb = new HSSFWorkbook();
}
// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet(sheetName);
// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
HSSFRow row = sheet.createRow(0);
// 第四步，创建单元格，并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
//声明列对象
HSSFCell cell = null;
//创建标题
for(int i=0;i<title.length;i++){
cell = row.createCell(i);
cell.setCellValue(title[i]);
cell.setCellStyle(style);
}
Map<String, Object> map = null;
//创建内容
for(int i=0;i<mapList.size();i++){
map = mapList.get(i);
row = sheet.createRow(i + 1);
//将内容按顺序赋给对应的列对象
row.createCell(0).setCellValue( map.get("user_id").toString());
row.createCell(1).setCellValue((String) map.get("name"));
row.createCell(2).setCellValue((String) map.get("degree"));
row.createCell(3).setCellValue((String) map.get("gender"));
row.createCell(4).setCellValue((String) map.get("apply_status"));
row.createCell(5).setCellValue(map.get("l1_name_a")+","+map.get("city_a")+","+map.get("level_a")+","+map.get("salary_a")+";"+
map.get("l1_name_b")+","+map.get("city_b")+","+map.get("level_b")+","+map.get("salary_b")+";"+
map.get("l1_name_c")+","+map.get("city_c")+","+map.get("level_c")+","+map.get("salary_c")+";"
);
row.createCell(6).setCellValue((String) map.get("is_boss"));
row.createCell(7).setCellValue( map.get("city_a")+","+map.get("city_b")+","+map.get("city_c"));
row.createCell(8).setCellValue((String) map.get("work_years"));
row.createCell(9).setCellValue((String) map.get("work_description"));
row.createCell(10).setCellValue((String) map.get("edu_description"));
}
return wb;
}
}