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

报表并发送至邮箱实现步骤"/>

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 #邮箱服务商的protocol服务器主机 #username: 4448956@qq #7557*****@qq指定邮箱服务商的邮箱账号default-encoding: UTF-8password: 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*/
@Overridepublic 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;//将邮件工具放入容器@Resourceprivate JavaMailSender javaMailSender;//定义模板文件所在的相对路径， File.separator表示文件分隔符，可自动适配Windows和Linux系统public static String PASH = "src" + File.separator + "main" + File.separator + "resources" + File.separator + "templates" + File.separator;@Overridepublic 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();