使用阿里邮箱发送带Excel邮件

浅浅的花香味﹌ 2022-04-21 22:24 642阅读 0赞

# 使用阿里邮箱发送带Excel邮件 #

## 需要的maven依赖 ##

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.14</version>
            </dependency>
    
            
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-mail</artifactId>
            </dependency>
            <dependency>
                <groupId>javax.activation</groupId>
                <artifactId>activation</artifactId>
                <version>1.1</version>
            </dependency>
            <dependency>
                <groupId>javax.mail</groupId>
                <artifactId>mail</artifactId>
                <version>1.4.7</version>
            </dependency>

#### 链接 ####

[activation.jar][]  
[mail.jar][]

## 工程代码 ##

package com.ghkj.schedule;
    
    import com.ghkj.common.utils.Utils;
    import com.ghkj.services.impl.RepayUnBindCardServiceImpl;
    import org.apache.poi.hssf.usermodel.*;
    import org.apache.poi.ss.usermodel.CreationHelper;
    import org.junit.Test;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.scheduling.annotation.EnableScheduling;
    import org.springframework.scheduling.annotation.Scheduled;
    
    import javax.activation.DataHandler;
    import javax.activation.DataSource;
    import javax.mail.*;
    import javax.mail.internet.InternetAddress;
    import javax.mail.internet.MimeBodyPart;
    import javax.mail.internet.MimeMessage;
    import javax.mail.internet.MimeMultipart;
    import javax.mail.util.ByteArrayDataSource;
    import java.io.ByteArrayOutputStream;
    import java.io.IOException;
    import java.io.UnsupportedEncodingException;
    import java.math.BigDecimal;
    import java.sql.*;
    import java.text.DateFormat;
    import java.text.ParseException;
    import java.text.SimpleDateFormat;
    import java.util.*;
    import java.util.Date;
    ···
    ···
    /**
     * @author lsq
     * @date:20181031
     * 待打款订单定时发送邮件
     */
    @Configuration
    @EnableScheduling
    public class ShippedOrderMail {
        private static final Logger log = LoggerFactory.getLogger(RepayUnBindCardServiceImpl.class);
        DateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        int orderNums = 0;
        Double totalAmount = 0.0;
        String mailContent = "";
        String startDateStr = "";
        String endDateStr = "";
    
    
        /**
         * 周一上午8点定时发送待打款订单邮件
         */
        @Scheduled(cron = "0 0 8 * * MON")
        public void MONShippedOrderMail(){
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(new Date());
            calendar.add(Calendar.DATE,-4);
            Date startDate = calendar.getTime();
            calendar.add(Calendar.DATE,+3);
            Date endDate = calendar.getTime();
            startDateStr = sdf.format(startDate);
            endDateStr = sdf.format(endDate);
            totalAmount = 0.0;
            log.info("周一进行发送待打款订单邮件");
            getShippedOrderMail();
            log.info("周一发送待打款订单邮件成功");
        }
    
        /**
         * 周四上午8点定时发送待打款订单邮件
         */
        @Scheduled(cron = "0 0 8 * * Thu")
        public void ThuShippedOrderMail(){
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(new Date());
            calendar.add(Calendar.DATE,-3);
            Date startDate = calendar.getTime();
            calendar.add(Calendar.DATE,+2);
            Date endDate = calendar.getTime();
            startDateStr = sdf.format(startDate);
            endDateStr = sdf.format(endDate);
            totalAmount = 0.0;
            log.info("周四进行发送待打款订单邮件");
            getShippedOrderMail();
            log.info("周四发送待打款订单邮件成功");
        }
    
       /* @Scheduled(cron = "0 0 16 * * Wed")
        @Test
        public void WedShippedOrderMail(){
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(new Date());
            calendar.add(Calendar.DATE,-3);
            Date startDate = calendar.getTime();
            calendar.add(Calendar.DATE,+2);
            Date endDate = calendar.getTime();
            startDateStr = sdf.format(startDate);
            endDateStr = sdf.format(endDate);
            totalAmount = 0.0;
            log.info("测试进行发送待打款订单邮件");
            getShippedOrderMail();
            log.info("测试发送待打款订单邮件成功");
        }*/
    
        /**
         * 发送待打款订单邮件方法
         */
        public void getShippedOrderMail(){
            String sql = "select a.id, a.ious_id, a.order_no, b.name, b.paperwork_no, " +
                   "b.phone, c.bank_code, b.bank_card_no, a.amount-a.refund_amount, a.shipped_time, a.received_time " +
                   "from o_order a left join a_borrow b on a.borrow_id = b.id left join a_borrow_bank c on b.borrow_bank_id = c.id " +
                   "where (a.status = 3 or a.status = 4 ) and date_format(a.shipped_time,'%Y%m%d') " +
                   "between " +  startDateStr + " and " + endDateStr ;
            String table_desc = "序号\t白条ID\t订单号\t借款人姓名\t身份证号码\t手机号码\t开户行\t银行编码\t银行卡号\t借款金额\t发布利率\t借款日（发货日）\t还款日\t预计手续费\t收货日\n";
            List<Map<String,String>> list = selectSql(sql);
            orderNums = list.size();
            try {
                sendMail(list);
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    
        /**
         * 查询数据返回到list
         * @param sql
         * @return
         */
        private List<Map<String,String>> selectSql(String sql) {
            Connection conn = getConn();
            PreparedStatement pstmt;
            List<Map<String,String>> list = new ArrayList<>();
            log.info("进行sql查询并赋给list");
            try {
                pstmt = (PreparedStatement)conn.prepareStatement(sql);
                ResultSet rs = pstmt.executeQuery();
                int col = rs.getMetaData().getColumnCount();
                while (rs.next()) {
                    try {
                    Map<String,String> map = new HashMap<>();
                    map.put("id",rs.getString("id"));
                    map.put("iousId",rs.getString("ious_id"));
                    map.put("orderNo",rs.getString("order_no"));
                    map.put("name",rs.getString("name"));
                    map.put("paperworkNo",rs.getString("paperwork_no"));
                    map.put("phone",rs.getString("phone"));
                    map.put("bankCode",rs.getString("bank_code"));
                    map.put("bankCardNo",rs.getString("bank_card_no"));
                    map.put("shippedTime",rs.getString("shipped_time"));
                    map.put("receivedTime",rs.getString("received_time"));
                    Double amount = new BigDecimal(rs.getString("a.amount-a.refund_amount")).setScale(2,BigDecimal.ROUND_HALF_UP).doubleValue();
                    Date shippedTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(map.get("shippedTime"));
                    String repayTime =Utils.getRepaymentDayStr(shippedTime);
                    Double serviceFee = Utils.calcIousServiceFee(amount,shippedTime);
                    Double amountSum = new BigDecimal(amount/10000).setScale(6,BigDecimal.ROUND_HALF_UP).doubleValue();;
                    map.put("amount",amountSum.toString());
                    map.put("repayTime",repayTime);
                    map.put("serviceFee",serviceFee.toString());
                    totalAmount += amount;
                    list.add(map);
                    } catch (ParseException e) {
                        e.printStackTrace();
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            log.info("带打款订单List【{}】",list);
            return list;
        }
    
        /**
         * 连接JDBC配置
         * @return
         */
        private static Connection getConn() {
            String driver = "com.mysql.jdbc.Driver";
    //        String url = "jdbc:mysql://172.31.31.152:3306/gh_smloan?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true";
    //        String username = "root";
    //        String password = "guohe.mysql_r";
            String url = "jdbc:mysql://47.104.24.10:3306/gh_smloan_lsq_1031?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true";
            String username = "root";
            String password = "guohe.mysql_r";
            Connection conn = null;
            try {
                Class.forName(driver); //classLoader,加载对应驱动
                conn = (Connection) DriverManager.getConnection(url, username, password);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return conn;
        }
    
    
        /**
         * 发送带Excel附件的阿里企业邮件
         * @param list
         * @throws IOException
         */
        public void sendMail(List<Map<String,String>> list) throws IOException {
            /**
             * 1，创建邮件对象
             */
            String to1 = "liutong@guohetech.com"; // 收件人1的邮箱
            String to2 = "lishuaiqi@supplyfintech.com";//收件人2的邮箱
    //        String to1 = "lsqiemail@163.com"; // 抄送人邮箱
            String from = "service@supplyfintech.com"; // 发件人的邮箱
            String authoCode = "xxx"; // 使用qq邮箱发送时的授权码
            String smtpServer = "smtp.mxhichina.com";
    
            log.info("收件人为【{}】，【{}】",to1,to2);
            Properties props = new Properties();
            // 表示SMTP发送邮件，需要进行身份验证
            props.put("mail.smtp.auth", "true");
            props.put("mail.smtp.host", smtpServer);
            props.put("mail.smtp.socketFactory.class", "javax.net.ssl.SSLSocketFactory");
            props.put("mail.smtp.socketFactory.port", "465");
            props.put("mail.smtp.port", "465");
    
            // 发件人的账号
            props.put("mail.user", from);
            // 访问SMTP服务时需要提供的密码(邮箱密码)
            props.put("mail.password", "Jinhe2018+");
    
            // 构建授权信息，用于进行SMTP进行身份验证
            Authenticator authenticator = new Authenticator() {
                @Override
                protected PasswordAuthentication getPasswordAuthentication() {
                    // 用户名、密码
                    String userName = props.getProperty("mail.user");
                    String password = props.getProperty("mail.password");
                    return new PasswordAuthentication(userName, password);
                }
            };
    
    
            Session session = Session.getInstance(props,authenticator);
            MimeMessage message = new MimeMessage(session);
            try {
            /**
             * 2. 设置发件人
             * 其中 InternetAddress 的三个参数分别为: 邮箱, 显示的昵称(只用于显示, 没有特别的要求), 昵称的字符集编码
             */
            message.setFrom(new InternetAddress(from, "待打款订单", "UTF-8"));
    
            /**
             * 3，设置收件人
             * To收件人   CC 抄送  BCC密送
             */
            message.setRecipient(MimeMessage.RecipientType.TO,new InternetAddress(to1, "", "UTF-8"));
            message.addRecipient(MimeMessage.RecipientType.TO,new InternetAddress(to2, "", "UTF-8"));
    
            /**
             * 4，设置标题
             */
            log.info("导出到Excel");
            message.setSubject("待打款订单报表","UTF-8");
    
            HSSFWorkbook workbook = new HSSFWorkbook();
            CreationHelper helper = workbook.getCreationHelper();
            HSSFSheet sheet = workbook.createSheet("待打款订单信息表");
            //设置列宽度
            sheet.setColumnWidth(0, 5 * 256);
            sheet.setColumnWidth(1, 20 * 256);
            sheet.setColumnWidth(2, 20 * 256);
            sheet.setColumnWidth(3, 10 * 256);
            sheet.setColumnWidth(4, 20 * 256);
            sheet.setColumnWidth(5, 12 * 256);
            sheet.setColumnWidth(6, 8 * 256);
            sheet.setColumnWidth(7, 10 * 256);
            sheet.setColumnWidth(8, 20 * 256);
            sheet.setColumnWidth(9, 10 * 256);
            sheet.setColumnWidth(10, 8 * 256);
            sheet.setColumnWidth(11, 20 * 256);
            sheet.setColumnWidth(12, 20 * 256);
            sheet.setColumnWidth(13, 10 * 256);
            sheet.setColumnWidth(14, 20 * 256);
    //        List<Teacher> classmateList = teacherservice.teacherinfor();
            String fileName = "待打款订单"  + ".xls";//设置要导出的文件的名字
            //新增数据行，并且设置单元格数据
            int rowNum = 1;
            String[] headers = { "序号","白条ID","订单号","借款人姓名","身份证号码","手机号码","开户行","银行编码","银行卡号","借款金额","发布利率","借款日（发货日）","还款日","预计手续费","收货日"};
            //headers表示excel表中第一行的表头
            HSSFRow row = sheet.createRow(0);
            //在excel表中添加表头
            for(int i=0;i<headers.length;i++){
                HSSFCell cell = row.createCell(i);
                HSSFRichTextString text = new HSSFRichTextString(headers[i]);
                cell.setCellValue(text);
            }
            //在表中存放查询到的数据放入对应的列
            for (Map<String,String> map : list) {
                HSSFRow row1 = sheet.createRow(rowNum);
                row1.createCell(0).setCellValue(rowNum);
                row1.createCell(1).setCellValue(map.get("iousId"));
                row1.createCell(2).setCellValue(map.get("orderNo"));
                row1.createCell(3).setCellValue(map.get("name"));
                row1.createCell(4).setCellValue(map.get("paperworkNo"));
                row1.createCell(5).setCellValue(map.get("phone"));
                row1.createCell(6).setCellValue(bankCodeMap.get(map.get("bankCode")));
                row1.createCell(7).setCellValue(bankCodeNoMap.get(map.get("bankCode")));
                row1.createCell(8).setCellValue(map.get("bankCardNo"));
                row1.createCell(9).setCellValue(map.get("amount"));
                row1.createCell(10).setCellValue("18%");
                row1.createCell(11).setCellValue(map.get("shippedTime"));
                row1.createCell(12).setCellValue(map.get("repayTime"));
                row1.createCell(13).setCellValue(map.get("serviceFee"));
                row1.createCell(14).setCellValue(map.get("receivedTime"));
                rowNum++;
            }
    
            /******workBook写入输出流**/
            log.info("workBook写入输出流");
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            workbook.write(baos);
            baos.flush();
            baos.close();
    
            DataSource fds = new ByteArrayDataSource(baos.toByteArray(), "application/vnd.ms-excel");
    
            MimeBodyPart mbp1 = new MimeBodyPart();
            mailContent = startDateStr + "-" + endDateStr + "待打款订单：\n" + "共计 " + orderNums + " 单， 总金额为：" + totalAmount + "\n" +"辛苦确认\n详情见附件：\n";
            mbp1.setText(mailContent);
    
            MimeBodyPart mbp2 = new MimeBodyPart();
            mbp2.setDataHandler(new DataHandler(fds));
            mbp2.setFileName(startDateStr + "-" + endDateStr + "--ShippedOrderMail.xls");
    
            Multipart mp = new MimeMultipart();
            mp.addBodyPart(mbp1);
            mp.addBodyPart(mbp2);
            message.setContent(mp);
    
            /**
             * 6，保存邮件并发送
             */
             log.info("保存邮件并发送");
            message.saveChanges();
            Transport transport = session.getTransport("smtp");
            transport.connect(smtpServer, from, authoCode);
            transport.sendMessage(message,message.getAllRecipients());
            transport.close();
            } catch (MessagingException e) {
                e.printStackTrace();
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
        }
    
    
    
        public static final HashMap<String,String> bankCodeMap = new HashMap<String,String>() {
            {
                put("ICBC","工商银行");
                put("BOC","中国银行");
                put("CCB","建设银行");
                put("PSBC","邮政储蓄");
                put("ECITIC","中信银行");
                put("CEB","光大银行");
                put("CMBCHINA","招商银行");
                put("CIB","兴业银行");
                put("SPDB","浦发银行");
                put("SZPA","平安银行");
                put("CMBC","民生银行");
                put("GDB","广发银行");
                put("BCCB","北京银行");
                put("ABC","农业银行");
                put("BOCO","交通银行");
                put("HX","华夏银行");
                put("SHB","上海银行");
            }
        };
        public static final HashMap<String,String> bankCodeNoMap = new HashMap<String,String>() {
            {
                put("ICBC", "01020000");
                put("BOC", "01040000");
                put("CCB", "01050000");
                put("PSBC", "01000000");
                put("ECITIC", "03020000");
                put("CEB", "03030000");
                put("CMBCHINA", "03080000");
                put("CIB", "03090000");
                put("SPDB", "03100000");
                put("SZPA", "0");
                put("CMBC", "03050000");
                put("GDB", "03060000");
                put("BCCB", "04031000");
                put("ABC", "01030000");
                put("BOCO", "03010000");
                put("HX", "03040000");
                put("SHB", "04012900");
            }
        };
    
    }

[activation.jar]: http://static.runoob.com/download/activation.jar
[mail.jar]: http://static.runoob.com/download/mail.jar