使用阿里邮箱发送带Excel邮件
需要的maven依赖
<!--导出Excel-->
<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>
链接
工程代码
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");
}
};
}