p
ublic void export(List wechatUserList, HttpServletResponse response) throws IOException, URISyntaxException {
File newFile = createNewFile(getClass().getClassLoader().getResource("../../resources/excel/wechatUserInfoTemplate.xls").toURI().getPath(), new URI("../../resources/xls").getPath());//防止路径中的空格转义为%20
InputStream is = null;
HSSFWorkbook workbook = null;
HSSFSheet sheet = null;
try {
is = new FileInputStream(newFile);// 将excel文件转为输入流
workbook = new HSSFWorkbook(is);// 创建个workbook,
sheet = workbook.getSheetAt(0);
} catch (Exception e1) {
e1.printStackTrace();
}
if (sheet != null) {
try {
// 写数据
FileOutputStream fos = new FileOutputStream(newFile);
HSSFRow row = sheet.createRow(1);
HSSFCell cell = row.createCell(0);
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setWrapText(true);
int i = 1;
for (WechatUser wechatUser : wechatUserList) {
List childrenList = wechatUser.getChildrenList();
int size = childrenList.size();
row = sheet.getRow(i);
if (row == null) {
row = sheet.createRow(i);
}
cell = getCell(row,0,cell);
cell.setCellStyle(style);
cell.setCellValue(wechatUser.getNickname());
cell = getCell(row,1,cell);
cell.setCellStyle(style);
cell.setCellValue(wechatUser.getName());
cell = getCell(row,2,cell);
cell.setCellStyle(style);
cell.setCellValue(wechatUser.getPhone());
cell = getCell(row,3,cell);
cell.setCellStyle(style);
cell.setCellValue((null == wechatUser.getSex() ? "" :
(0 == wechatUser.getSex() ? "男" : "女")));
cell = getCell(row,4,cell);
cell.setCellStyle(style);
cell.setCellValue(wechatUser.getGrade().getName());
cell = getCell(row,5,cell);
cell.setCellStyle(style);
cell.setCellValue(wechatUser.getDefaultAddress());
if (1 <= size) {
cell = getCell(row,6,cell);
cell.setCellStyle(style);
cell.setCellValue(childrenList.get(0).getName());
cell = getCell(row,7,cell);
cell.setCellStyle(style);
cell.setCellValue((null == childrenList.get(0).getGender() ? "" :
(0 == childrenList.get(0).getGender() ? "男" : "女")));
cell = getCell(row,8,cell);
cell.setCellStyle(style);
cell.setCellValue(
(null == childrenList.get(0).getBirthDay() ? "" :
DateUtil.dateToStr(
childrenList.get(0).getBirthDay(),
"yyyy-MM-dd")));
}
if (2 == size) {
cell = getCell(row,9,cell);
cell.setCellStyle(style);
cell.setCellValue(childrenList.get(1).getName());
cell = getCell(row,10,cell);
cell.setCellStyle(style);
cell.setCellValue((null == childrenList.get(1).getGender() ? "" :
(0 == childrenList.get(1).getGender() ? "男" : "女")));
cell = getCell(row,11,cell);
cell.setCellStyle(style);
cell.setCellValue((null == childrenList.get(1).getBirthDay() ? "" :
DateUtil.dateToStr(
childrenList.get(1).getBirthDay(),
"yyyy-MM-dd")));
}
i++;
}
//内容
workbook.write(fos);
fos.flush();
fos.close();
// 下载
InputStream fis = new BufferedInputStream(new FileInputStream(
newFile));
byte[] buffer = new byte[fis.available()];
fis.read(buffer);
fis.close();
response.reset();
response.setContentType("text/html;charset=UTF-8");
OutputStream toClient = new BufferedOutputStream(
response.getOutputStream());
response.setContentType("application/x-msdownload");
String fileName = URLEncoder.encode(
"会员信息" + ".xls",
"UTF-8");
fileName = new String(fileName.getBytes(), "ISO8859-1");
response.addHeader("Content-Disposition",
"attachment;filename=" + fileName);
response.addHeader("Content-Length", "" + newFile.length());
toClient.write(buffer);
toClient.flush();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (null != is) {
is.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
// 删除创建的新文件
this.deleteFile(newFile);
}