快速学习POI-模板打印

1 模板打印

1.1 概述

自定义生成Excel报表文件还是有很多不尽如意的地方，特别是针对复杂报表头，单元格样式，字体等操作。手写这些代码不仅费时费力，有时候效果还不太理想。那怎么样才能更方便的对报表样式，报表头进行处理呢？答案是使用已经准备好的Excel模板，只需要关注模板中的数据即可。

1.2 模板打印的操作步骤

1. 1. 
      
2. 制作模版文件（模版文件的路径）
3. 导入（加载）模版文件，从而得到一个工作簿
4. 读取工作表
5. 读取行
6. 读取单元格
7. 读取单元格样式
8. 设置单元格内容
9. 其他单元格就可以使用读到的样式了
   1. 
      

1.3 代码实现

@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.加载模板流数据
        Resource resource = new ClassPathResource("excel-template/hr-demo.xlsx");
        FileInputStream fis = new FileInputStream(resource.getFile());
      //3.根据文件流，加载指定的工作簿
        XSSFWorkbook wb = new XSSFWorkbook(fis);
      //4.读取工作表
        Sheet sheet = wb.getSheetAt(0);
      //5.抽取公共的样式
        Row styleRow = sheet.getRow(2);
      CellStyle [] styles = new CellStyle[styleRow.getLastCellNum()];
        for(int i=0;i<styleRow.getLastCellNum();i++) {
            styles[i] = styleRow.getCell(i).getCellStyle();
         }
        //6.构造每行和单元格数据
        AtomicInteger datasAi = new AtomicInteger(2);
        Cell cell = null;
        for (EmployeeReportResult report : list) {
            Row dataRow = sheet.createRow(datasAi.getAndIncrement());
            //编号
            cell = dataRow.createCell(0);
            cell.setCellValue(report.getUserId());
            cell.setCellStyle(styles[0]);
            //姓名
            cell = dataRow.createCell(1);
            cell.setCellValue(report.getUsername());
            cell.setCellStyle(styles[1]);
            //手机
            cell = dataRow.createCell(2);
            cell.setCellValue(report.getMobile());
            cell.setCellStyle(styles[2]);
            //最高学历
            cell = dataRow.createCell(3);
            cell.setCellValue(report.getTheHighestDegreeOfEducation());
            cell.setCellStyle(styles[3]);
            //国家地区
            cell = dataRow.createCell(4);
            cell.setCellValue(report.getNationalArea());
            cell.setCellStyle(styles[4]);
            //护照号
            cell = dataRow.createCell(5);
            cell.setCellValue(report.getPassportNo());
            cell.setCellStyle(styles[5]);
            //籍贯
            cell = dataRow.createCell(6);
            cell.setCellValue(report.getNativePlace());
            cell.setCellStyle(styles[6]);
            //生日
            cell = dataRow.createCell(7);
            cell.setCellValue(report.getBirthday());
            cell.setCellStyle(styles[7]);
            //属相
            cell = dataRow.createCell(8);
            cell.setCellValue(report.getZodiac());
            cell.setCellStyle(styles[8]);
            //入职时间
            cell = dataRow.createCell(9);
            cell.setCellValue(report.getTimeOfEntry());
            cell.setCellStyle(styles[9]);
            //离职类型
            cell = dataRow.createCell(10);
            cell.setCellValue(report.getTypeOfTurnover());
            cell.setCellStyle(styles[10]);
            //离职原因
            cell = dataRow.createCell(11);
            cell.setCellValue(report.getReasonsForLeaving());
            cell.setCellStyle(styles[11]);
            //离职时间
            cell = dataRow.createCell(12);
            cell.setCellStyle(styles[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);
        wb.write(response.getOutputStream());
   }