excel数据导出

/**
 * 导出excel格式数据
 * @param request
 * @param response
 */
public void exportDataToDatabases(HttpServletRequest request,HttpServletResponse response){
String ids = request.getParameter("ids");//获取员工id字符串
String tempPath = this.getServletContext().getRealPath("upload/excel/temp/");//导出文件的临时路径(相对路径）
String tempFileName = this.getNewFileName("tempExcelFile.xls");
String tempFilePath = tempPath+"\\"+tempFileName;
System.out.println("tempFilePath="+tempFilePath);
String[] title = {"姓名","性别","出生年月","籍贯","政治面貌","名族","身份证号","户口所在地","工作单位","部门","职务","固定电话","手机","通讯地址","备注"}; 
// 第一步，创建一个webbook，对应一个Excel文件 
HSSFWorkbook wb = new HSSFWorkbook(); 
// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet1 = wb.createSheet("Sheet1");  
// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet1.createRow((int) 0); 
// 第四步，创建单元格，并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle(); 
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
HSSFCell cell = null;
for (short i = 0; i < title.length; i++) {
cell = row.createCell(i);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(style);
cell.setCellValue(title[i]);
}
 // 第五步，查询数据库写入实体数据
List list = new ArrayList<>();
List newList = new ArrayList<>();
//没有选择数据时查询全部数据，有选择数据时按选择数据的id查询
if(ids=="" || ids==null || ids.length()==0){
list = personnelMgtService.findAllHrEmployee();
}else{
String[] empIDs = ids.split("/");
for (int i = 0; i < empIDs.length; i++) {
list.add(personnelMgtService.findEmployeeInfoByID(Integer.parseInt(empIDs[i])));
}
}
//写入数据
try {
newList = personnelMgtService.getHrEmployeeInfoVoList(list);
} catch (ParseException e2) {
System.out.println("personnalManagementController中getHrEmployeeInfoVoList错误");
e2.printStackTrace();
}
for (int i = 0; i < newList.size(); i++) {
HrEmployeeInfoVo vo = new HrEmployeeInfoVo();
vo = (HrEmployeeInfoVo) newList.get(i);
row = sheet1.createRow((int) i + 1);
cell = row.createCell((short) 0);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(style);
cell.setCellValue(vo.getName()); //姓名

cell = row.createCell((short) 1);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(style);
cell.setCellValue(vo.getSex_name()); //性别

cell = row.createCell((short) 2);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
HSSFCellStyle cellStyle = wb.createCellStyle();
HSSFDataFormat format= wb.createDataFormat();
cellStyle.setDataFormat(format.getFormat("yyyy/m/d"));
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(cellStyle);
sheet1.setColumnWidth((short)2,(short)2500);
Date date  = null;
DateFormat df = new SimpleDateFormat("yyyy/MM/dd");
cell.setCellValue(vo.getBirthday()); //出生年月

cell = row.createCell((short) 3);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(style);
sheet1.setColumnWidth((short)3,(short)4000);
cell.setCellValue(vo.getPlaceOrign()); //籍贯

cell = row.createCell((short) 4);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(style);
cell.setCellValue(vo.getPoliticalAffiliation()); //政治面貌

cell = row.createCell((short) 5);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(style);
cell.setCellValue(vo.getMinorities()); //民族

cell = row.createCell((short) 6);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(style);
sheet1.setColumnWidth((short)6,(short)5000);
cell.setCellValue(vo.getIdentityCardNo()); //身份证号

cell = row.createCell((short) 7);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(style);
sheet1.setColumnWidth((short)7,(short)5000);
cell.setCellValue(vo.getResidenceAddress());//户口所在地

cell = row.createCell((short) 8);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(style);
sheet1.setColumnWidth((short)8,(short)5000);
cell.setCellValue(vo.getUnitName());//工作单位 

cell = row.createCell((short) 9);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(style);
sheet1.setColumnWidth((short)9,(short)6000);
cell.setCellValue(vo.getDeptName()); //部门

cell = row.createCell((short) 10);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(style);
sheet1.setColumnWidth((short)10,(short)5000);
cell.setCellValue(vo.getBusiness());//职务

cell = row.createCell((short) 11);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(style);
sheet1.setColumnWidth((short)11,(short)4000);
cell.setCellValue(vo.getMobilePhone()); //固定电话

cell = row.createCell((short) 12);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(style);
sheet1.setColumnWidth((short)12,(short)4000);
cell.setCellValue(vo.getTelePhone()); //手机

cell = row.createCell((short) 13);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(style);
sheet1.setColumnWidth((short)13,(short)6000);
cell.setCellValue(vo.getCommuAddress());//通讯地址

cell = row.createCell((short) 14);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(style);
sheet1.setColumnWidth((short)14,(short)6000);
cell.setCellValue(vo.getRemark());//备注
}


// 第六步，将文件存到指定位置  
try {
FileOutputStream fout = new FileOutputStream(tempFilePath);  
wb.write(fout);  
fout.close();
} catch (Exception e1) {
e1.printStackTrace();
}
//第七步，将存储的临时文件的相对路径放到响应空间中
try {
String relPath = "upload/excel/temp/"+tempFileName;
        String erp = "{success:true,msg:'"+relPath+"'}";
        erp = erp.replaceAll("\\\\", "/");
        //System.out.println("================================="+erp);
        response.setContentType("text/html;charset=UTF-8");
    response.getWriter().write(erp);
} catch (IOException e) {
e.printStackTrace();
}
}