用java实现Excel用户信息的导出

导出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效果如图：