springcloud定时发送邮件excel附件

菜蜗牛很菜

已于 2022-03-11 15:41:03 修改

阅读量381

点赞数

分类专栏： springcloud 文章标签： poi 定时任务 spring cloud

于 2022-02-24 20:21:12 首次发布

本文链接：https://blog.csdn.net/weixin_45010894/article/details/123083033

版权

springcloud 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

文章目录

定时任务测试
- 1配置定时时间
- 2定时任务测试
定时发送邮件和附件

定时任务测试

1配置定时时间

在这里插入图片描述

yml配置

#每隔一分钟的定时任务
sendEmail:
  schedule:
    cron: 0 0/1 * * * ?

2定时任务测试

在类上添加@EnableScheduling注解
在要进行定时任务的方法上添加@Scheduled注解

package com.rsi.rc.bs.util;

import lombok.extern.slf4j.Slf4j;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * Created by jixinwei on 2022/2/23 9:42
 * 每天凌晨3点发送昨天的卡口进车流量的统计
 */
@EnableScheduling
@Slf4j
@Component
public class SendEmailScheduledTask {
    private static final SimpleDateFormat dateFormat = new SimpleDateFormat("YYYY-MM-DD HH:mm:ss");

    /**
     * 每隔5秒执行, 单位：ms。测试定时任务
     */
    @Scheduled(cron = "${sendEmail.schedule.cron}")
    public void testFixRate() {
        System.out.println("我每隔一分钟冒泡一次：" + dateFormat.format(new Date()));

    }

}

定时发送邮件和附件

email依赖

<!-- 邮件发送 -->
        <dependency>
            <groupId>org.json</groupId>
            <artifactId>json</artifactId>
            <version>20180813</version><!--注意：20160810版本不支持JSONArray-->
        </dependency>
        <!-- Javamail -->
        <dependency>
            <groupId>javax.mail</groupId>
            <artifactId>mail</artifactId>
            <version>1.4.5</version>
        </dependency>
        <!--读取excel文件-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.16</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.16</version>
        </dependency>
        <!--文件上传组件-->
        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.3.1</version>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.5</version>
        </dependency>

定时任务

package com.rsi.rc.bs.scheduler;

import com.rsi.rc.bs.module.gateRoadReport.vo.GateRoadReportReq;
import com.rsi.rc.bs.module.taskImplement.vo.TaskImplement;
import com.rsi.rc.bs.taskImplement.dao.TaskImplementMapper;
import com.rsi.rc.bs.util.DateTimeUtil;
import com.rsi.rc.bs.util.Mail;
import com.rsi.rc.bs.util.MailUtil;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.output.ByteArrayOutputStream;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashSet;
import java.util.Set;

/**
 * Created by jixinwei on 2022/2/23 9:42
 * 每天凌晨3点发送昨天的卡口进出车流量的统计
 */
@EnableScheduling
@Slf4j
@Component
public class SendEmailScheduledTask {
    private static final SimpleDateFormat dateFormat = new SimpleDateFormat("YYYY-MM-DD HH:mm:ss");
    private static final SimpleDateFormat dateFormat2 = new SimpleDateFormat("YYYY-MM-DD");

    @Resource
    private TaskImplementMapper taskImplementMapper;

    @Resource
    private CreatExcel creatExcel;
    /**
     * 每隔5秒执行, 单位：ms。测试定时任务
     */
    @Scheduled(cron = "${sendEmailTest.schedule.cron}")
    public void testFixRate() {
        System.out.println("我每隔一分钟冒泡一次：" + dateFormat.format(new Date()));

        long implementId = 0;
        try {
            implementId = insertTaskImplement();
//发送邮件
            sendEmailSVP();

            updateTaskImplement(implementId, 1);
        } catch (Exception e) {
            e.printStackTrace();
            updateTaskImplement(implementId, 2);
        }


    }


  

    public void sendEmailSVP(){

        ByteArrayOutputStream baos = creatExcel.creatExcel();
        SendEmail.sendEmail("kingjixinwei@163.com",baos);

    }

    public Long insertTaskImplement() {
        log.info("邮件定时任务开始：{}", new Date());
        TaskImplement taskImplement = new TaskImplement();
        taskImplement.setImplementStatus(0);
        taskImplement.setStartTime(new Date());
        int i = taskImplementMapper.insertSelective(taskImplement);
        return taskImplement.getImplementId();

    }

    public void updateTaskImplement(long implementId, int status) {
        log.info("邮件定时任务结束：{}", new Date());
        TaskImplement taskImplement = new TaskImplement();
        taskImplement.setImplementId(implementId);
        taskImplement.setImplementStatus(status);
        taskImplement.setEndTime(new Date());
        taskImplementMapper.updateByPrimaryKeySelective(taskImplement);
    }

}

创建excel

package com.rsi.rc.bs.scheduler;


import com.rsi.rc.bs.module.gateRoadReport.impl.GateRoadReportServiceImpl;
import com.rsi.rc.bs.module.gateRoadReport.vo.GateRoadReportReq;
import com.rsi.rc.bs.module.gateRoadReport.vo.GateRoadReportResp;
import com.rsi.rc.bs.module.gateRoadReport.vo.GateRoadReportVO;
import com.rsi.rc.bs.util.DateTimeUtil;
import com.rsi.rc.common.vo.SessionUserInfo;
import org.apache.commons.io.output.ByteArrayOutputStream;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.io.IOException;
import java.util.List;

/**
 * Created by jixinwei on 2022/2/23 9:12
 */
@Component
public class CreatExcel {
    @Autowired
    private  GateRoadReportServiceImpl gateRoadReportService;

    public  ByteArrayOutputStream creatExcel() {
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        //创建一个表格
        Workbook workbook = new XSSFWorkbook();


//以下为excel内容
        String yesterday = DateTimeUtil.getYesterdayDate();
        GateRoadReportReq req = new GateRoadReportReq();
        req.setDayTime(yesterday);
        req.setType(1);
        SessionUserInfo sessionUserInfo = new SessionUserInfo();
        sessionUserInfo.setTenantId(String.valueOf(6));
        GateRoadReportResp gateRoadReportResp = gateRoadReportService.gateRoadReportInAndOut(req, sessionUserInfo);
        // 创建一个工作薄对象
        XSSFSheet sheet = (XSSFSheet) workbook.createSheet("卡口进车");

        getSheetContent(workbook,sheet,gateRoadReportResp.getList());

        GateRoadReportReq req2 = new GateRoadReportReq();
        req2.setDayTime(yesterday);
        req2.setType(2);
        GateRoadReportResp gateRoadReportResp2 = gateRoadReportService.gateRoadReportInAndOut(req2, sessionUserInfo);
        XSSFSheet sheet2 = (XSSFSheet) workbook.createSheet("卡口出车");
        getSheetContent(workbook,sheet2,gateRoadReportResp2.getList());


        GateRoadReportReq req3 = new GateRoadReportReq();
        req3.setDayTime(yesterday);
        req3.setType(3);
        GateRoadReportResp gateRoadReportResp3 = gateRoadReportService.gateRoadReportInAndOut(req3, sessionUserInfo);
        XSSFSheet sheet3 = (XSSFSheet) workbook.createSheet("卡口进出车");
        getSheetContent(workbook,sheet3,gateRoadReportResp3.getList());

// 以上为excel内容
        try {
            workbook.write(baos); // write excel data to a byte array
            baos.close();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        System.out.println("创建成功 office excel");
        return baos;
    }

    /*填充sheet内容*/
    public static  void getSheetContent(Workbook workbook,XSSFSheet sheet,List<GateRoadReportVO> reportList){

        // 字体设置
        Font font = workbook.createFont();
        font.setFontName("仿宋");
        font.setColor(XSSFFont.COLOR_NORMAL);
        font.setFontHeightInPoints((short) 15);// 字体大小
        font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); //字体加粗
        // 样式
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(XSSFCellStyle.ALIGN_CENTER);
        cellStyle.setFont(font);
        cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框
        cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框
        cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框
        cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); //设置右边线和颜色
        cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
        cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());// 设置背景色
        getNewCenterStyle(cellStyle);


        // 样式
        Font font1 = workbook.createFont();
        font1.setFontName("仿宋");
        font1.setColor(XSSFFont.COLOR_NORMAL);
        font1.setFontHeightInPoints((short) 12);// 字体大小
        CellStyle cellStyle1 = workbook.createCellStyle();
        getBgStyle(workbook, cellStyle1);

        // 内容样式
        CellStyle cellStyle11 = workbook.createCellStyle();
        cellStyle11.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        getBgStyle(workbook, cellStyle11);
        cellStyle11.setFillForegroundColor(IndexedColors.WHITE.getIndex());// 设置背景色


        //设置首行标题
        int reportType = 1;
        String title = "";
        if (reportType==1) {
            title = DateTimeUtil.getYesterdayCNDate()+"卡口进车流量统计";
        } else if (reportType==2) {
            title = DateTimeUtil.getYesterdayCNDate()+"卡口出车流量统计";
        } else if (reportType==3) {
            title = DateTimeUtil.getYesterdayCNDate()+"卡口进出车总流量统计";
        }
        XSSFRow row0 = sheet.createRow(0);
        XSSFCell titleCell1 = row0.createCell(0);
        titleCell1.setCellValue(title);
        titleCell1.setCellStyle(cellStyle);

        for (int i = 1; i < 8; i++) {
            titleCell1 = row0.createCell((short) i);
            titleCell1.setCellValue("");
            titleCell1.setCellStyle(cellStyle);
            cellStyle1.setBorderRight(XSSFCellStyle.BORDER_THIN); //设置右边线和颜色

        }

        //合并单元格第一行 报表标题
        CellRangeAddress nameCellRange = new CellRangeAddress(0, 0, 0, 8);
        sheet.addMergedRegion(nameCellRange);
        //合并单元格第二行 序号
        CellRangeAddress nameCellRange1 = new CellRangeAddress(1, 2, 0, 0);
        sheet.addMergedRegion(nameCellRange1);
        RegionUtil.setBorderRight(1,nameCellRange1,sheet,workbook);
        //合并单元格第二行 卡口
        CellRangeAddress nameCellRange2 = new CellRangeAddress(1, 2, 1, 1);
        sheet.addMergedRegion(nameCellRange2);
        RegionUtil.setBorderRight(1,nameCellRange2,sheet,workbook);
        //合并单元格第二行 时间
        CellRangeAddress nameCellRange3 = new CellRangeAddress(1, 1, 2, 8);
        sheet.addMergedRegion(nameCellRange3);
        RegionUtil.setBorderRight(1,nameCellRange3,sheet,workbook);

        // 设置第二行

        XSSFRow row1 = sheet.createRow(1);
        for (int i = 0; i <= 8; i++) {
            XSSFCell titleCell2 = row1.createCell(i);
            cellStyle1 = workbook.createCellStyle();
            getBgStyle(workbook, cellStyle1);
            if (i == 0) {
                titleCell2.setCellValue("序号");
                getBgStyle(workbook, cellStyle1);
                cellStyle1.setBorderRight(XSSFCellStyle.BORDER_THIN); //设置右边线和颜色


            } else if (i > 0 && i <= 1) {
                titleCell2.setCellValue("卡口");
                getBgStyle(workbook, cellStyle1);

            } else if (i >= 2 && i <= 8) {
                titleCell2.setCellValue("时间");
                getBgStyle(workbook, cellStyle1);

            }
            titleCell2.setCellStyle(cellStyle11);
        }

//        设置第三行
        XSSFRow row2 = sheet.createRow(2);
        for (int i = 2; i <=8 ; i++) {
            XSSFCell titleCell3 = row2.createCell(i);
            cellStyle1 = workbook.createCellStyle();
            getBgStyle(workbook, cellStyle1);
            if (i == 2 ) {
                titleCell3.setCellValue("0点-7点");
//                getBgStyle(workbook, cellStyle1);

            } else if(i ==3) {
                titleCell3.setCellValue("7点-9点");

            } else if(i == 4) {
                titleCell3.setCellValue("9点-15点30");

            } else if(i == 5 ) {
                titleCell3.setCellValue("15:30-17:30");

            } else if(i ==6) {
                titleCell3.setCellValue("17点30-18点30");

            } else if(i ==7) {
                titleCell3.setCellValue("18点30-24点");

            } else if(i ==8) {
                titleCell3.setCellValue("合计");

            }
            getBgStyle(workbook, cellStyle1);
            titleCell3.setCellStyle(cellStyle11);
        }


// 数据内容
        if (reportList != null && reportList.size() > 0) {
            if (reportList.size() > 65535 - 3) {
                //数据量过载的处理
            }
            int j = 1;

            for (int i = 0; i < reportList.size(); i++) {
                XSSFRow row11 = sheet.createRow((int) i + 3);
                GateRoadReportVO vo = reportList.get(i);
                XSSFCell createCell0 = row11.createCell((short) 0);
                createCell0.setCellValue(j++);
                createCell0.setCellStyle(cellStyle11);
                XSSFCell createCell1 = row11.createCell((short) 1);
                createCell1.setCellValue(vo.getGateName());
                createCell1.setCellStyle(cellStyle11);
                XSSFCell createCell2 = row11.createCell((short) 2);
                createCell2.setCellValue(vo.getPoint0And7());
                createCell2.setCellStyle(cellStyle11);
                XSSFCell createCell3 = row11.createCell((short) 3);
                createCell3.setCellValue(vo.getPoint7And9());
                createCell3.setCellStyle(cellStyle11);
                XSSFCell createCell4 = row11.createCell((short) 4);
                createCell4.setCellValue(vo.getPoint9And1530());
                createCell4.setCellStyle(cellStyle11);
                XSSFCell createCell5 = row11.createCell((short) 5);
                createCell5.setCellValue(vo.getPoint1530And1730());
                createCell5.setCellStyle(cellStyle11);
                XSSFCell createCell6 = row11.createCell((short) 6);
                createCell6.setCellValue(vo.getPoint1730And1830());
                createCell6.setCellStyle(cellStyle11);
                XSSFCell createCell7 = row11.createCell((short) 7);
                createCell7.setCellValue(vo.getPoint1830And24());
                createCell7.setCellStyle(cellStyle11);
                XSSFCell createCell8 = row11.createCell((short) 8);
                createCell8.setCellValue(vo.getTotal());
                createCell8.setCellStyle(cellStyle11);
            }
        }
    }

    /**
     * 定制单元格边框
     */
    private static void getNewCenterStyle(CellStyle style) {
        style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        style.setBorderRight(XSSFCellStyle.BORDER_THIN);
        style.setBorderTop(XSSFCellStyle.BORDER_THIN);
        style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);
        /* return style; */
    }
    /**
     * 定制背景色
     *
     * @param style
     */
    private static void getBgStyle(Workbook wb, CellStyle cellStyle1) {
        Font font1 = wb.createFont();
        font1.setFontName("等线");
        font1.setColor(XSSFFont.COLOR_NORMAL);
        font1.setFontHeightInPoints((short) 11);// 字体大小
        cellStyle1.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        cellStyle1.setVerticalAlignment(XSSFCellStyle.ALIGN_CENTER);
        cellStyle1.setFont(font1);
        cellStyle1.setFillPattern(CellStyle.SOLID_FOREGROUND);
        cellStyle1.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框
        cellStyle1.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框
        cellStyle1.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框
        cellStyle1.setBorderRight(XSSFCellStyle.BORDER_THIN); //设置右边线和颜色
        cellStyle1.setRightBorderColor(HSSFColor.BLACK.index);
    }

}

邮件发送


package com.rsi.rc.bs.scheduler;


import com.rsi.rc.bs.util.DateTimeUtil;
import org.apache.commons.io.output.ByteArrayOutputStream;
import org.json.JSONArray;

import javax.activation.DataHandler;
import javax.activation.DataSource;
import javax.mail.*;
import javax.mail.internet.*;
import javax.mail.util.ByteArrayDataSource;

import java.io.IOException;
import java.util.Properties;
/**
 * Created by jixinwei on 2022/2/23 9:19
 */
public class SendEmail {
    public static boolean sendEmail(String to,ByteArrayOutputStream baos) {
        // 发件人电子邮箱
        String from = "******@qq.com";
        // 获取系统属性
        Properties properties = System.getProperties();
        // 设置邮件服务器 ->QQ 邮件服务器
        properties.setProperty("mail.smtp.host", "smtp.qq.com");
//        properties.setProperty("mail.smtp.host", "smtp.163.com");
        properties.put("mail.smtp.auth", "true");
        // 获取默认session对象
        Session session = Session.getDefaultInstance(properties,new Authenticator(){
            public PasswordAuthentication getPasswordAuthentication()
            {
                return new PasswordAuthentication("*******@qq.com", "*********"); //发件人邮件用户名、授权码               
            }
        });

        try{
            // 创建默认的 MimeMessage 对象
            MimeMessage message = new MimeMessage(session);
            // Set From: 头部头字段
            message.setFrom(new InternetAddress(from));
            // Set To: 头部头字段
            message.addRecipient(Message.RecipientType.TO,new InternetAddress(to));
            // Set Subject: 头部头字段
            String yesterdayCNDate = DateTimeUtil.getYesterdayCNDate();
            String subject=yesterdayCNDate + "卡口进出车总流量统计邮件";
            message.setSubject(subject);
            /*添加附件*/
            Multipart multipart = new MimeMultipart();
            if(baos != null) {
                MimeBodyPart fileBody = new MimeBodyPart();
                DataSource source = new ByteArrayDataSource(baos.toByteArray(), "application/msexcel");
                fileBody.setDataHandler(new DataHandler(source));
                // 中文乱码问题
                fileBody.setFileName(MimeUtility.encodeText(subject+".xlsx"));
                multipart.addBodyPart(fileBody);
            }
            message.setContent(multipart);
            // 发送消息
            Transport.send(message);
        }catch (MessagingException mex) {
            mex.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return false;
    }

}