java poi导出数据和图片到excel
@ResponseBody
@RequestMapping(value = "/exportExcel", method = RequestMethod.GET)
public void exportExcel(HttpServletResponse response,PeopleQueryVO para) {
if (StringUtils.isBlank(para.getOrgId())) {
para.setOrgId(getUser(para.getUserId()).getOrgId());
}
List<People> people = this.peopleService.selPeople(para);
String[] title = {"姓名","身份证号","注册照","证件照","性别","民族","出生日期","政治面貌","婚姻状态","手机号","人员类别","是否入住","常驻地址","地址编码","工号","单位","职位","系统名称"};
String filename = "staff.xls";
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("人员表");
sheet.setDefaultColumnWidth(20);
HSSFRow row = sheet.createRow(0);
row.setHeightInPoints(80);
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFCell cell = null;
for (int i = 0; i < title.length; i++) {
cell = row.createCell(i);
cell.setCellValue(title[i]);
cell.setCellStyle(style);
}
for (int i = 0; i < people.size(); i++) {
row = sheet.createRow(i + 1);
row.setHeightInPoints(160);
row.createCell(0).setCellValue(people.get(i).getPeopleName());
row.createCell(1).setCellValue(people.get(i).getCredentialNo());
if(StringUtils.isNotBlank(people.get(i).getFacePic())) {
try {
BufferedImage bufferImg = null;
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
URL url = new URL(FilePropertiesUtil.getHttpUrl() + people.get(i).getFacePic());
HttpURLConnection conn = (HttpURLConnection) url.openConnection();
conn.setConnectTimeout(3 * 1000);
InputStream inputStream = conn.getInputStream();
bufferImg = ImageIO.read(inputStream);
ImageIO.write(bufferImg, "jpg", byteArrayOut);
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1002, 250, (short) 2, i + 1, (short) 2, i + 1);
patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
} catch (IOException io) {
io.printStackTrace();
}
}
row.createCell(4).setCellValue(people.get(i).getGender());
row.createCell(5).setCellValue(people.get(i).getNation());
row.createCell(6).setCellValue(people.get(i).getBirthDate());
row.createCell(7).setCellValue(people.get(i).getPolitical());
row.createCell(8).setCellValue(people.get(i).getMaritalStatus());
row.createCell(9).setCellValue(people.get(i).getPhoneNo());
row.createCell(10).setCellValue(people.get(i).getPeopleCategoryDesc());
if(people.get(i).getPeopleCategories().get(0).getLiveFlag() != null){
row.createCell(11).setCellValue(String.valueOf(people.get(i).getPeopleCategories().get(0).getLiveFlag()));
}
row.createCell(12).setCellValue(people.get(i).getAddressDesc());
row.createCell(13).setCellValue(String.valueOf(people.get(i).getAddressId()));
row.createCell(14).setCellValue(people.get(i).getWorkNo());
row.createCell(15).setCellValue(people.get(i).getOrgName());
row.createCell(16).setCellValue(people.get(i).getPeopleCategories().get(0).getWorkTitle());
row.createCell(17).setCellValue("智能安防管理系统PROD");
}
try {
this.setResponseHeader(response, filename);
OutputStream os = response.getOutputStream();
wb.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public void setResponseHeader(HttpServletResponse response, String fileName) {
try {
try {
fileName = new String(fileName.getBytes(), "UTF-8");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
} catch (Exception ex) {
ex.printStackTrace();
}