Java实现传入数据导出excel文件

前端代码

var url = ‘${ctx}/_data/web/msg/download?memType=2&type=’ + type;
window.open(url, ‘_blank’).location;

java代码

action：

@GetMapping(“/download”)
public void download(@RequestParam(name= “type”,required = true) int type,
BasMember data,HttpServletResponse response) throws UnsupportedEncodingException{
//设置响应头和客户端保存文件名
response.setCharacterEncoding(“utf-8”);
//设置文件ContentType类型，这样设置，会自动判断下载文件类型?
response.setContentType(“multipart/form-data”);
//当设置了该响应头时，使用 response 输出流将会被当作附件提供给客户端下载，反之就是将流中的内容
输出到页面上,对于图片等文件可用于控制是要显示图片还是下载图片
response.setHeader(“Content-Disposition”, “attachment;fileName=” +
URLEncoder.encode(“短信管理.xls”, “utf-8″));
//复制文件流到响应输出流
try {
service.download(response,type,data);
response.getOutputStream().flush();
response.getOutputStream().close();
} catch (IOException e) {
throw new FileDownloadException(CommonError.WEB_FILE_DOWNLOAD_FAILURE,e,”短信管
理.xls”);
}
}

service是action注入的service

serviceImpl实现类代码：

@Override
public void download(HttpServletResponse response, int type, BasMember data) {
if (StringUtils.isNotEmpty(data.getMemType())) {
data.getQuery().addCondition(BasMember.Field.memType, data.getMemType());
}
data.getQuery().addOrderBy(false, BasMember.Field.created);
List<BasMember> list = dao.find(data);
//将数据查出根据自己的需要拼接传到方法createFile中
createFile(list ,response,3);

}

//创建excel文件
private void createFile(List<BasMember> list, HttpServletResponse response,int cols){
// 创建工作簿—->XSSF代表10版的Excel(HSSF是03版的Excel)
HSSFWorkbook wb = new HSSFWorkbook();
// 创建工作表
HSSFSheet sheet = wb.createSheet(“短信管理”);

HSSFCellStyle style = wb.createCellStyle();
sheet.setDefaultColumnWidth(15);//设置列宽
style.setAlignment(HorizontalAlignment.CENTER); // 水平居中
style.setBorderBottom(BorderStyle.THIN); // 下边框
style.setBorderLeft(BorderStyle.THIN);// 左边框
style.setBorderTop(BorderStyle.THIN);// 上边框
style.setBorderRight(BorderStyle.THIN);// 右边框
style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
int rows = vos.size();
// 创建单元格
for (int i = 0; i < rows + 1; i++) {
HSSFRow row = sheet.createRow(i);
for (int j = 0; j < cols; j++) {
HSSFCell cell = row.createCell(j);
cell.setCellStyle(style);
}
}
//定义数据
for (int i = 0; i < rows + 1; i++) {
for (int j = 0; j < cols; j++) {
if (i == 0) {
switch (j) {
case 0:
sheet.getRow(i).getCell(j).setCellValue(“单位”);
break;
case 1:
sheet.getRow(i).getCell(j).setCellValue(“姓名”);
break;
case 2:
sheet.getRow(i).getCell(j).setCellValue(“手机”);
break;
default:
break;
}

} else {
switch (j) {
case 0:
sheet.getRow(i).getCell(j).setCellValue(list.get(i-1).getCompanyName());
break;
case 1:
sheet.getRow(i).getCell(j).setCellValue(list.get(i-1).getName());
break;
case 2:
sheet.getRow(i).getCell(j).setCellValue(list.get(i-1).getMobile());
break;
default:
break;
}
}
}

}
try {
wb.write(response.getOutputStream());
} catch (IOException e) {
e.printStackTrace();
}
}