java给excel模板填充数据并作为附件发送邮件

1.依赖

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.12</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.12</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.12</version>
        </dependency>
        <!-- mail -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-mail</artifactId>
        </dependency>

2.java代码

public OperationInfo sendExcel(String examTime) throws Exception {
        if (examTime == null || examTime.equals("")){
            return OperationInfo.failure("请筛选日期");
        }
        List<Report> reports = examPaperDAO.findReport(null, null, examTime, "name", null, null, true, "tionName");
        exportTemplateSendEmail("xin.lxxxn@enxxxxech.com",reports,examTime);
        return OperationInfo.success();
    }

public void exportTemplateSendEmail(String userEmail, List<Report> reports, String yesterday) throws Exception {
        if (reports==null||reports.isEmpty()){
            throw new ExamCustomException("抱歉，没有内容可以发送");
        }
        //File f =new File(Thread.currentThread().getContextClassLoader().getResource("/").getPath());
        //文件目录在resource下面
        ClassPathResource resource = new ClassPathResource("examResult.xlsx");

        InputStream in = resource.getInputStream();

        //读取excel模板

        XSSFWorkbook wb = new XSSFWorkbook(in);

        //读取了模板内所有sheet内容

        //如果这行没有了，整个公式都不会有自动计算的效果的

        //sheet.setForceFormulaRecalculation(true);

        //POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));

        //读取excel模板

        //HSSFWorkbook wb = new HSSFWorkbook(fs);

        //读取了模板内所有sheet内容

        //HSSFSheet sheet = wb.getSheetAt(0);
        XSSFSheet sheet = wb.getSheetAt(0);
        int rowNum=sheet.getLastRowNum();
        for(int i = 0;i<reports.size();i++){
            log.info("i:"+i);
            /*if (i>=rowNum){
                int index = i/rowNum;
                log.info("index:"+index);
                sheet = wb.getSheetAt(index);
            }*/
            Report report = reports.get(i);
            sheet.createRow(i+1).createCell(0).setCellValue("全国信息化人才专业技术");
            sheet.getRow(i+1).createCell(1).setCellValue(report.getExamCertNum());
            sheet.getRow(i+1).createCell(2).setCellValue(report.getName());
            sheet.getRow(i+1).createCell(4).setCellValue(report.getCertNum());
            sheet.getRow(i+1).createCell(6).setCellValue("中级");
            if (report.getAuthenticationName()!=null){
                sheet.getRow(i+1).createCell(7).setCellValue(report.getAuthenticationName().toUpperCase()+"工程师培训");
            }
            SimpleDateFormat format = new SimpleDateFormat("yyyy/MM/dd");
            if (report.getExamTime() != null){
                sheet.getRow(i+1).createCell(8).setCellValue(format.format(report.getExamTime()));
            }
            sheet.getRow(i+1).createCell(10).setCellValue(report.getCompanyName());
            sheet.getRow(i+1).createCell(12).setCellValue(report.getAddress());
        }

        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        wb.write(byteArrayOutputStream);
        if (byteArrayOutputStream==null){
            throw new ExamCustomException("生成excel失败");
        }else {
            ArrayList<String> list = new ArrayList<>();
            list.add(userEmail);
            emailService.sendBytesMail(list,yesterday+"考试结果信息"+".xlsx",null,null,byteArrayOutputStream);
        }

    }

    @Autowired
    private JavaMailSender javaMailSender;
    @Value("${spring.mail.username}")
    private String emailFrom;
public void sendBytesMail(List<String> to, String fileName, String title, String text, ByteArrayOutputStream os) throws MessagingException {
        //防止中文名字 base64加密以后 名字太长被截断 导致中文乱码问题
        System.getProperties().setProperty("mail.mime.splitlongparameters", "false");
        MimeMessage message = javaMailSender.createMimeMessage();
        try {
            MimeMessageHelper helper = new MimeMessageHelper(message, true);
            helper.setFrom(emailFrom);
            String[] tos = to.toArray(new String[to.size()]);
            helper.setTo(tos);
            if (StringUtils.isNotBlank(title)){
                helper.setSubject(title);
            }else {
                helper.setSubject("考试结果数据");
            }
            if (StringUtils.isNotBlank(text)){
                helper.setText(text, true);
            }else {
                helper.setText("附件", true);
            }
            InputStreamSource iss = new ByteArrayResource(os.toByteArray());
            helper.addAttachment(MimeUtility.decodeText(fileName), iss);
            javaMailSender.send(message);
        } catch (MessagingException e) {
            log.error("邮件发送附件异常：" + e.getMessage());
        } catch (UnsupportedEncodingException e) {
            log.error("邮件发送附件异常：" + e.getMessage());
        }
    }

3.yml文件

spring:
  mail:
    host: sxxxxx.xxxxx.qq.com
    username: xxxxx@xxxxx.com
    password: xxxxx
    port: xxx
    protocol: xxxx
    properties:
      mail:
        smtp:
          auth: true
          ssl:
            enable: true
          starttls:
            enable: true

4.注意

HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls

XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx