HSSF EXCEL 导出
1.所需依赖jar包
1 <dependency> 2 <groupId>org.apache.poi</groupId> 3 <artifactId>poi</artifactId> 4 <version>3.9</version> 5 </dependency> 6 <dependency> 7 <groupId>org.apache.poi</groupId> 8 <artifactId>poi-ooxml</artifactId> 9 <version>3.9</version> 10 </dependency>
2.样式
1 public static HSSFCellStyle getGroomHeaderStyle(HSSFWorkbook wb) { 2 // 设置边框 3 HSSFCellStyle style = wb.createCellStyle(); 4 style.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下边框 5 style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框 6 style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框 7 style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框 8 9 // 单元格字体 10 HSSFFont f = wb.createFont(); 11 f.setFontHeightInPoints((short) 10);// 字号 12 f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);// 加粗 13 style.setFont(f); 14 style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中 15 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中 16 // style.setRotation(short rotation);//单元格内容的旋转的角度 17 18 // 设置背景和字体颜色 19 20 style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); 21 style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 22 23 // 设置自动换行 24 // style.setWrapText(true); 25 return style; 26 }
3.获取数据源并生成excel
1 @RequestMapping(value = "/exportSysUserInfo", method = {RequestMethod.GET, RequestMethod.POST}) 2 public void exportSysUserInfo(HttpServletRequest request, HttpServletResponse response, Integer[] userIds, String usernameTx, String loginTx) throws UnsupportedEncodingException { 3 String filePath = request.getSession().getServletContext() 4 .getRealPath("/").replace("\\", "/") 5 + "/"; 6 filePath = filePath + ExcelExportUtil.TEMP_DOWN_EXCEL_PATH + "/" 7 + ".xls"; 8 9 SysUser sysUser = (SysUser) request.getSession().getAttribute("userSession"); 10 Integer isAdmin = sysUser.getIsAdmin(); 11 Integer comId = sysUser.getComId(); 12 13 try { 14 if (null != usernameTx && !"".equals(usernameTx)) { 15 if (usernameTx.equals(new String(usernameTx.getBytes("iso8859-1"), "iso8859-1"))) { 16 usernameTx = new String(usernameTx.getBytes("iso8859-1"), "utf-8"); 17 } 18 } 19 20 if (null != loginTx && !"".equals(loginTx)) { 21 if (loginTx.equals(new String(loginTx.getBytes("iso8859-1"), "iso8859-1"))) { 22 loginTx = new String(loginTx.getBytes("iso8859-1"), "utf-8"); 23 } 24 } 25 26 List<SysUserExport> sysUserExportList = sysUserService.getListExportSysUser(userIds, usernameTx, loginTx, isAdmin, comId); 27 28 HSSFWorkbook wb = new HSSFWorkbook(); 29 // 工作簿 30 HSSFSheet sheet = wb.createSheet("科技人员信息"); 31 32 // 单元格样式 33 HSSFCellStyle cellStyle = ExcelExportUtil.getGroomCellStyle(wb); 34 // 工作簿 头部 标题样式 35 HSSFCellStyle headStyle = ExcelExportUtil.getGroomHeaderStyle(wb); 36 // 创建表格头部 37 createHeaderRow(sheet, headStyle); 38 39 HSSFRow row = null; 40 // 添加数据 41 for (int i = 0; i < sysUserExportList.size(); i++) { 42 SysUserExport sysUserExport = sysUserExportList.get(i); 43 44 //出生日期 45 sysUserExport.setBirthday(sysUserExport.getBirthday().substring(0, 10)); 46 47 row = sheet.createRow(i + 1); 48 ExcelExportUtil.createCell(row, 0, cellStyle, sysUserExport.getUsername());//用户名 49 ExcelExportUtil.createCell(row, 1, cellStyle, sysUserExport.getGender());//工号(登录名) 50 ExcelExportUtil.createCell(row, 2, cellStyle, sysUserExport.getBirthday());//工号(登录名) 51 ExcelExportUtil.createCell(row, 3, cellStyle, sysUserExport.getTelePhone());//电话 52 ExcelExportUtil.createCell(row, 4, cellStyle, sysUserExport.getPosition());//岗位 53 ExcelExportUtil.createCell(row, 5, cellStyle, sysUserExport.getNote());//工作职责 54 ExcelExportUtil.createCell(row, 6, cellStyle, sysUserExport.getComName());//所属组织 55 ExcelExportUtil.createCell(row, 7, cellStyle, sysUserExport.getDepName());//所属部门 56 //新增字段 57 ExcelExportUtil.createCell(row, 8, cellStyle, sysUserExport.getIsComputer());//计算机相关专业 58 ExcelExportUtil.createCell(row, 9, cellStyle, sysUserExport.getMajorName());//专业名称 59 ExcelExportUtil.createCell(row, 10, cellStyle, sysUserExport.getEducation());//学历 60 ExcelExportUtil.createCell(row, 11, cellStyle, sysUserExport.getWorkingYears());//从事银行工作年限 61 ExcelExportUtil.createCell(row, 12, cellStyle, sysUserExport.getIsBadRecord());//不良记录信息 62 ExcelExportUtil.createCell(row, 13, cellStyle, sysUserExport.getIsSignAgreement());//签订保密协议 63 ExcelExportUtil.createCell(row, 14, cellStyle, sysUserExport.getEducationFileName());//学历证书 64 ExcelExportUtil.createCell(row, 15, cellStyle, sysUserExport.getJobFileName());//工作简历 65 66 } 67 68 FileOutputStream fos = new FileOutputStream(filePath); 69 wb.write(fos); 70 fos.close(); 71 } catch (FileNotFoundException e) { 72 log.info("导出找不到文件！"); 73 } catch (IOException e) { 74 log.info("文件流异常！"); 75 } 76 // 下载文件 77 ExcelExportUtil.downLoadExcel(request, response, filePath, "科技人员信息"); 78 }
4.生成excel表头
1 private void createHeaderRow(HSSFSheet sheet, HSSFCellStyle style) { 2 // 工作表的第0行，表头部分 3 HSSFRow row = sheet.createRow(0); 4 5 // 设置列宽度，第一个参数代表列id(从0开始),第2个参数代表宽度值 参考 ："2012-08-10"的宽度为2500 6 sheet.setColumnWidth(0, 3766); 7 sheet.setColumnWidth(1, 3766); 8 sheet.setColumnWidth(2, 3766); 9 sheet.setColumnWidth(3, 4500); 10 sheet.setColumnWidth(4, 3766); 11 sheet.setColumnWidth(5, 10000); 12 sheet.setColumnWidth(6, 6000); 13 sheet.setColumnWidth(7, 6000); 14 //新增字段 15 sheet.setColumnWidth(8, 3766); 16 sheet.setColumnWidth(9, 6000); 17 sheet.setColumnWidth(10, 3766); 18 sheet.setColumnWidth(11, 6000); 19 sheet.setColumnWidth(12, 3766); 20 sheet.setColumnWidth(13, 3766); 21 sheet.setColumnWidth(14, 6000); 22 sheet.setColumnWidth(15, 6000); 23 24 // 设置行高 25 row.setHeight((short) 500); 26 //人员基本信息 27 ExcelExportUtil.createCell(row, 0, style, "用户名"); 28 ExcelExportUtil.createCell(row, 1, style, "性别"); 29 ExcelExportUtil.createCell(row, 2, style, "出生日期"); 30 ExcelExportUtil.createCell(row, 3, style, "电话"); 31 ExcelExportUtil.createCell(row, 4, style, "岗位"); 32 ExcelExportUtil.createCell(row, 5, style, "工作职责"); 33 ExcelExportUtil.createCell(row, 6, style, "所属组织"); 34 ExcelExportUtil.createCell(row, 7, style, "所属部门"); 35 //人员详细信息 36 ExcelExportUtil.createCell(row, 8, style, "计算机相关专业"); 37 ExcelExportUtil.createCell(row, 9, style, "专业名称"); 38 ExcelExportUtil.createCell(row, 10, style, "学历"); 39 ExcelExportUtil.createCell(row, 11, style, "从事银行工作年限"); 40 ExcelExportUtil.createCell(row, 12, style, "不良记录信息"); 41 ExcelExportUtil.createCell(row, 13, style, "签订保密协议"); 42 ExcelExportUtil.createCell(row, 14, style, "学历证书"); 43 ExcelExportUtil.createCell(row, 15, style, "工作简历"); 44 }
