(3) 快速学习POI-POI报表导出

1 POI报表导出

1.1 需求分析

完成当月人事报表的导出：包含当月入职员工信息，离职员工信息

1.2 人事报表导出

1.2.1 步骤分析

1. 构造Excel表格数据
2. 创建工作簿
3. 创建sheet
4. 创建行对象
5. 创建单元格对象
6. 填充数据，设置样式
7. 下载

1.2.2 代码实现

（1）配置controller

@RequestMapping(value = "/export/{month}", method = RequestMethod.GET)
    public void export(@PathVariable(name = "month") String month) throws Exception {
        //1.构造数据
        List<EmployeeReportResult> list =
userCompanyPersonalService.findByReport(companyId,month+"%");
        //2.创建工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        //3.构造sheet
        String[] titles = {"编号", "姓名", "手机","最高学历", "国家地区", "护照号", "籍贯", 
"生日", "属相","入职时间","离职类型","离职原因","离职时间"};
        Sheet sheet = workbook.createSheet();
        Row row = sheet.createRow(0);
        AtomicInteger headersAi = new AtomicInteger();
        for (String title : titles) {
            Cell cell = row.createCell(headersAi.getAndIncrement());
            cell.setCellValue(title);
       }
        AtomicInteger datasAi = new AtomicInteger(1);
        Cell cell = null;
        for (EmployeeReportResult report : list) {
            Row dataRow = sheet.createRow(datasAi.getAndIncrement());
            //编号
            cell = dataRow.createCell(0);
            cell.setCellValue(report.getUserId());
            //姓名
            cell = dataRow.createCell(1);
            cell.setCellValue(report.getUsername());
            //手机
            cell = dataRow.createCell(2);
            cell.setCellValue(report.getMobile());
            //最高学历
            cell = dataRow.createCell(3);
            cell.setCellValue(report.getTheHighestDegreeOfEducation());
            //国家地区
            cell = dataRow.createCell(4);
            cell.setCellValue(report.getNationalArea());
            //护照号
            cell = dataRow.createCell(5);
            cell.setCellValue(report.getPassportNo());
            //籍贯
            cell = dataRow.createCell(6);
            cell.setCellValue(report.getNativePlace());
            //生日
            cell = dataRow.createCell(7);
            cell.setCellValue(report.getBirthday());
            //属相
            cell = dataRow.createCell(8);
            cell.setCellValue(report.getZodiac());
            //入职时间
            cell = dataRow.createCell(9);
            cell.setCellValue(report.getTimeOfEntry());
            //离职类型
            cell = dataRow.createCell(10);
            cell.setCellValue(report.getTypeOfTurnover());
            //离职原因
            cell = dataRow.createCell(11);
            cell.setCellValue(report.getReasonsForLeaving());
            //离职时间
            cell = dataRow.createCell(12);
            cell.setCellValue(report.getResignationTime());
       }
        String fileName = URLEncoder.encode(month+"人员信息.xlsx", "UTF-8");
        response.setContentType("application/octet-stream");
        response.setHeader("content-disposition", "attachment;filename=" + new
String(fileName.getBytes("ISO8859-1")));
        response.setHeader("filename", fileName);
        workbook.write(response.getOutputStream());
   }

（2）添加service

//根据企业id和年月查询
    public List<EmployeeReportResult> findByReport(String companyId, String month) {
        return userCompanyPersonalDao.findByReport(companyId,month);
   }

（3）dao层实现

 @Query(value = "select new 
com.ihrm.domain.employee.response.EmployeeReportResult(a,b) " +
            "FROM UserCompanyPersonal a LEFT JOIN EmployeeResignation b ON 
a.userId=b.userId WHERE a.companyId = ?1 AND a.timeOfEntry LIKE ?2 OR 
(b.resignationTime LIKE ?2)")
    List<EmployeeReportResult> findByReport(String companyId, String month);