spring boot导出excel文件报表并发送至邮箱实现步骤

1：pom.xml文件中引入POI相关依赖

        <!--POI报表-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>LATEST</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>LATEST</version>
        </dependency>
        <!--邮件依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-mail</artifactId>
        </dependency>

2：配置文件：

# @author 噎翔

spring:
	mail:
	  host: smtp.qq.com #邮箱服务商的protocol服务器主机 #
	  username: 4448956@qq.com #7557*****@qq.com指定邮箱服务商的邮箱账号
	  default-encoding: UTF-8
	  password: jhtkhdbwnobhhi #jwgteykojlf*****邮箱账号密码或者三方登录授权码
	  port: 25

3：controller映射方法

/**
 * @author 噎翔
 * @date 2020/7/27 8:41
 */
    @RequestMapping(value = "/export/v1", method = {RequestMethod.POST})
    public ResponseEntity<?> transactionExport(@RequestBody WithdrawUpdateRequest request) {

        Date startTime = DateUtils.format(request.getStartTime(), "yyyy-MM-dd HH:mm:ss");
        Date endTime = DateUtils.format(request.getEndTime(), "yyyy-MM-dd HH:mm:ss");


        Result<?> result = bizGpoTransactionService.transactionExport(startTime, endTime,request.getEmail());

        return new ResponseEntity<>(result, HttpStatus.OK);
    }

4：查询数据库封装数据

/**
 * @author 噎翔
 * @date 2020/7/27 8:41
 */
@Override
    public Result<?> transactionExport(Date startTime, Date endTime,  String toUser) {
  	  	//封装报表所需数据
        List<Map<String, Object>> List = new ArrayList<>();
        List<GpoTransaction> gpoList = gpoTransactionService.getListByTime(startTime, endTime);
        for (GpoTransaction gpoTransaction : gpoList ) {
            Map<String, Object> bean = new HashMap<>();
            String name = gpoMap.get(gpoTransaction.getGpoId()) == null ? "团组已删除" : gpoMap.get(gpoTransaction.getGpoId()).getName();
            
            //每个map对应excel表中的每一行数据，key用来定位value在哪个格内显示
            bean.put("0", DateUtils.format(gpoTransaction.getCreateTime(), "yyyy-MM-dd HH:mm:ss"));  //结算日期
            bean.put("1", name);                                        //团组名称
            bean.put("2", gpoTransaction.getPrice() / 100);
            bean.put("3", gpoTransaction.getPrice() / 100);
            bean.put("4", gpoTransaction.getPrice() / 100);
            bean.put("5", gpoTransaction.getPrice() / 100);
            bean.put("6", gpoTransaction.getAvailableAmount() / 100);   //余额
            List.add(bean);
        }
        //调用邮件服务，传入数据，创建基于模板文件生成的报表文件，并以附件形式发送到邮箱
        try {
            mailService.sendsd(List, "template.xlsx", toUser);
            return ResultUtils.success();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return ResultUtils.error(ResultEnum.ACQ_INVALID_PARAMETER);
    }  

5：邮件服务实现流程：

/**
 * @author 噎翔
 * @date 2020/7/27 8:41
 */
@Service
public class MailServiceImpl implements MailService {
	//获取配置文件参数，封装邮件内容是会用到
    @Value("${spring.mail.username}")
    private String userName;
	//将邮件工具放入容器
    @Resource
    private JavaMailSender javaMailSender;
	//定义模板文件所在的相对路径， File.separator表示文件分隔符，可自动适配Windows和Linux系统
    public static String PASH = "src" + File.separator + "main" + File.separator + "resources" + File.separator + "templates" + File.separator;

    @Override
    public Boolean sendsd(List<Map<String, Object>> rtnList, String templateId, String to) throws Exception {
    	//生成随机的报表名
        String dataExcel= UUID.randomUUID().toString() + ".xlsx";
        //读取模板文件（相对路径+模板文件名）
        FileInputStream fileInputStream = new FileInputStream(PASH + templateId);
        XSSFWorkbook excel = new XSSFWorkbook(fileInputStream);
        //创建居中样式
        XSSFCellStyle cellStyle = excel.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //封装数据
        for (int i = 0; i < rtnList.size(); i++) {
            XSSFRow row = excel.getSheetAt(0).createRow(i + 1);
            Map<String, Object> map = rtnList.get(i);
            for (int j = 0; j < map.size(); j++) {
                XSSFCell statTime = row.createCell(j);
                statTime.setCellValue(map.get(j + "").toString());
                statTime.setCellStyle(cellStyle);
            }
        }
        //生成文件
        FileOutputStream fileOutputStream = new FileOutputStream(PASH + dataExcel);
        excel.write(fileOutputStream);
        //刷新
        fileOutputStream.flush();
        //关闭流
        fileOutputStream.close();
        fileInputStream.close();
        //调用下面的sendMail方法，完成有邮件的发送
        sendMail(to, "内容：提现流水", "名头：提现流水", dataExcel);
        return true;
    }

    public void sendMail(String to, String text, String title, String excelName) throws Exception {
    	//新建带附件的邮件内容对象
        MimeMessage mimeMessage = javaMailSender.createMimeMessage();
        MimeMessageHelper mimeMessageHelper = new MimeMessageHelper(mimeMessage, true);
		mimeMessageHelper.setFrom(userName);   //上面取的配置文件中的值，作为发件人
        mimeMessageHelper.setTo(to);			//收件人邮箱
        mimeMessageHelper.setSubject(title);	//标题
        mimeMessageHelper.setText(text);		//邮件正文
        File file = new File(PASH + excelName);	//读取中项目中生成的报表文件
        mimeMessageHelper.addAttachment(excelName, file);//将文件加入附件（附件名，附件）
        //发送邮件
        javaMailSender.send(mimeMessage);
        //删除刚刚生成的报表文件
        file.delete();
    }
}

            

扩展：与上文无关，该代码为将文件传回浏览器，供用户下载保存

            //创建输出流，用于从服务器写数据到客户端浏览器
            ServletOutputStream out = response.getOutputStream();
            response.setContentType("application/vnd.ms-excel"); //代表excel类型为文件类型
            response.setHeader("content-Disposition", "attachment;filename=report.xlsx"); //指定以附件形式进行打开
            excel.write(out);
            out.flush();
            out.close();
            excel.close();