导出Excel大致有以下5个步骤： 1.创建一个excel文件工作薄；（HSSFWorkbook workbook = new HSSFWorkbook()） 2.创建一张表；HSSFSheet sheet = workbook.createSheet(“统计表”) 3.创建一行；HSSFRow row = sheet.createRow(0) 4.填充一列数据; row.createCell(0).setCellValue(“数据”) 5.设置一个单元格样式；cell.setCellStyle(style)
本案例是根据用户名，手机号，部门，权限作条件查找来导出用户信息 首先，添加依赖：
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.14</version> </dependency>然后，在Service层加入以下几个方法：
/** * 生成用户表excel */ public void createTitle(HSSFWorkbook workbook, HSSFSheet sheet){ HSSFRow row = sheet.createRow(0); //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度 sheet.setColumnWidth(1,12*256); sheet.setColumnWidth(3,17*256); //设置为居中加粗 HSSFCellStyle style = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setBold(true); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setFont(font); HSSFCell cell; cell = row.createCell(0); cell.setCellValue("序号"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("用户名"); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue("手机号"); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue("部门"); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue("权限"); cell.setCellStyle(style); cell = row.createCell(5); cell.setCellValue("创建人"); cell.setCellStyle(style); cell = row.createCell(6); cell.setCellValue("变更人"); cell.setCellStyle(style); cell = row.createCell(7); cell.setCellValue("创建时间"); cell.setCellStyle(style); cell = row.createCell(8); cell.setCellValue("创建时间"); cell.setCellStyle(style); } //生成excel文件 public void buildExcelFile(String filename,HSSFWorkbook workbook) throws Exception{ FileOutputStream fos = new FileOutputStream(filename); workbook.write(fos); fos.flush(); fos.close(); } //浏览器下载excel public void buildExcelDocument(String filename,HSSFWorkbook workbook,HttpServletResponse response) throws Exception{ response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(filename, "utf-8")); OutputStream outputStream = response.getOutputStream(); workbook.write(outputStream); outputStream.flush(); outputStream.close(); }最后，在Controller层进行调用：
/** * 生成用户表excel */ @ResponseBody @RequestMapping(value = "/getUserExcel", method = RequestMethod.GET) public String getUserExcel(@RequestParam(value = "username",required = false) String username, @RequestParam(value = "telephone",required = false) String telephone, @RequestParam(value = "deptId",required = false) Integer deptId, @RequestParam(value = "aclId",required = false) List<Integer> aclId, HttpServletResponse response) throws Exception { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("统计表"); sysOcrUserService.createTitle(workbook,sheet); SysOcrUser sysOcrUser = new SysOcrUser(); if(StringUtils.isNotEmpty(username)){ sysOcrUser.setUsername(username); } if(StringUtils.isNotEmpty(telephone)){ sysOcrUser.setTelephone(telephone); } if(deptId !=null ){ sysOcrUser.setDeptId(deptId); } if(aclId !=null && aclId.size() > 0){ sysOcrUser.setAclId(aclId); } List<SysOcrUserDto> rows = sysOcrUserService.getAllUsersByContion(sysOcrUser); //设置日期格式 HSSFCellStyle style = workbook.createCellStyle(); style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));//m/d/yy h:mm //新增数据行，并且设置单元格数据 int rowNum=1; int index = 1; for(SysOcrUserDto user:rows){ HSSFRow row = sheet.createRow(rowNum); row.createCell(0).setCellValue(index);//序号从1自增 index++; row.createCell(1).setCellValue(user.getUsername()); row.createCell(2).setCellValue(user.getTelephone()); row.createCell(3).setCellValue(user.getDeptName()); //遍历权限 StringBuffer acls=new StringBuffer(); for(SysOcrAcl acl:user.getSysOcrAcls()){ acls.append(acl.getAclName()+"，"); } row.createCell(4).setCellValue(acls.substring(0,acls.length()-1)); row.createCell(5).setCellValue(user.getCreateUser()); row.createCell(6).setCellValue(user.getChangeUser()); HSSFCell cell = row.createCell(7); cell.setCellValue(user.getChangeTime()); cell.setCellStyle(style); rowNum++; } String fileName = "导出用户的excel.xls"; //生成excel文件 sysOcrUserService.buildExcelFile(fileName, workbook); //浏览器下载excel sysOcrUserService.buildExcelDocument(fileName,workbook,response); return "download excel"; }导出的Excel效果如图：
