短期工作总结（一）
一、简述
首先，简单描述一下最近的工作需求。
近期接的任务是关于Excel对账文件的拉取，解析，比对，生成，上传以及与邮件的关联。
分析之后，分为以下步骤完成：
1、从邮件拉取Excel附件/从sftp上拉取Excel附件
2、转存到本方对象存储OSS上
3、解析Excel文件
4、落库
5、与数据库中数据进行比对
6、生成对账异常数据
7、生成相应对账完成数据的Excel（csv）文件
8、将生成的文件作为附件发送给相应的收件人
二、解析
详细代码已上传GitHub。
1、从邮件拉取附件-->上传至oss。
配置oss基本信息。如下：
此工具类实现了从邮件拉取附件的功能。
注意：拉取附件的邮箱以及后面发送邮件的邮箱用的都是腾讯企业邮箱。接收邮件的方式为IMAP。此方式中获取时间段的邮件只支持年月日不支持时分秒。
解析邮件代码如下：
package com.example.demo.utils;
import com.sun.mail.imap.IMAPFolder;
import com.sun.mail.imap.IMAPStore;
import javax.mail.*;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;
import javax.mail.internet.MimeUtility;
import javax.mail.search.AndTerm;
import javax.mail.search.ComparisonTerm;
import javax.mail.search.ReceivedDateTerm;
import javax.mail.search.SearchTerm;
import java.io.*;
import java.security.Security;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;
/**
* 使用IMAP协议接收邮件
*/
public class MailExmaple {
public static final String USER = "";
public static final String PASS = "";
public static final String DIR = "F:\\mailtmp\\";
public static void main(String[] args) throws Exception {
receive(null,null);
}
/**
* 接收邮件
*/
public static void receive(Date begindate,Date enddate) throws Exception {
String protocol = "imap";
boolean isSSL = true;
String host = "imap.exmail.qq.com";
int port = 993;
String username = USER;
String password = PASS;
String SSL_FACTORY = "javax.net.ssl.SSLSocketFactory";
Security.addProvider(new com.sun.net.ssl.internal.ssl.Provider());
System.setProperty("mail.mime.splitlongparameters", "false");
Properties props = System.getProperties();
props.setProperty("mail.imap.socketFactory.class", SSL_FACTORY);
props.setProperty("mail.imap.socketFactory.port", "993");
props.setProperty("mail.imapStore.protocol", protocol);
props.setProperty("mail.imap.host", host);
props.setProperty("mail.imap.port", "993");
props.setProperty("mail.imap.auth.login.disable", "true");
Session session = Session.getDefaultInstance(props, null);
IMAPStore imapStore = (IMAPStore) session.getStore(protocol); // 使用imap会话机制，连接服务器
imapStore.connect(host, port, username, password);
IMAPFolder imapFolder = (IMAPFolder) imapStore.getFolder("INBOX"); // 收件箱
imapFolder.open(Folder.READ_WRITE);
// 获得收件箱
Folder folder = imapStore.getFolder("INBOX");
folder.open(Folder.READ_ONLY); // 打开收件箱
System.err.println(DateUtil.getLast(20));
SearchTerm comparisonTermGe = new ReceivedDateTerm(ComparisonTerm.GT, DateUtil.getLast(20));
SearchTerm comparisonTermLe = new ReceivedDateTerm(ComparisonTerm.LE, new Date());
SearchTerm comparisonAndTerm = new AndTerm(comparisonTermGe, comparisonTermLe);
Message[] search = folder.search(comparisonAndTerm);
System.err.println(search.length);
parseMessage(search);
// 释放资源
folder.close(true);
imapStore.close();
}
/**
* 解析邮件
*
* @param messages 要解析的邮件列表
*/
public static void parseMessage(Message... messages) throws MessagingException, IOException {
if (messages == null || messages.length < 1) {
throw new MessagingException("未找到要解析的邮件!");
}
// 解析所有邮件
for (int i = 0, count = messages.length; i < count; i++) {
MimeMessage msg = (MimeMessage) messages[i];
System.out.println("------------------解析第" + msg.getMessageNumber() + "封邮件-------------------- ");
System.out.println("主题: " + getSubject(msg));
System.out.println("发件人: " + getFrom(msg));
System.out.println("收件人：" + getReceiveAddress(msg, null));
System.out.println("发送时间：" + getSentDate(msg, null));
System.out.println("是否已读：" + isSeen(msg));
System.out.println("邮件优先级：" + getPriority(msg));
System.out.println("是否需要回执：" + isReplySign(msg));
System.out.println("邮件大小：" + msg.getSize() * 1024 + "kb");
boolean isContainerAttachment = isContainAttachment(msg);
System.out.println("是否包含附件：" + isContainerAttachment);
if (isContainerAttachment) {
saveAttachment(msg, DIR + msg.getSubject() + "_"); // 保存附件
}
StringBuffer content = new StringBuffer(30);
getMailTextContent(msg, content);
System.out.println("邮件正文：" + (content.length() > 100 ? content.substring(0, 100) + "..." : content));
System.out.println("------------------第" + msg.getMessageNumber() + "封邮件解析结束-------------------- ");
System.out.println();
}
}
/**
* 获得邮件主题
*
* @param msg 邮件内容
* @return 解码后的邮件主题
*/
public static String getSubject(MimeMessage msg) throws UnsupportedEncodingException, MessagingException {
return MimeUtility.decodeText(msg.getSubject());
}
/**
* 获得邮件发件人
*
* @param msg 邮件内容
* @return 姓名 <Email地址>
* @throws MessagingException
* @throws UnsupportedEncodingException
*/
public static String getFrom(MimeMessage msg) throws MessagingException, UnsupportedEncodingException {
String from = "";
Address[] froms = msg.getFrom();
if (froms.length < 1) {
throw new MessagingException("没有发件人!");
}
InternetAddress address = (InternetAddress) froms[0];
String person = address.getPersonal();
if (person != null) {
person = MimeUtility.decodeText(person) + " ";
} else {
person = "";
}
from = person + "<" + address.getAddress() + ">";
return from;
}
/**
* 根据收件人类型，获取邮件收件人、抄送和密送地址。如果收件人类型为空，则获得所有的收件人
* <p>
* Message.RecipientType.TO 收件人
* </p>
* <p>
* Message.RecipientType.CC 抄送
* </p>
* <p>
* Message.RecipientType.BCC 密送
* </p>
*
* @param msg 邮件内容
* @param type 收件人类型
* @return 收件人1 <邮件地址1>, 收件人2 <邮件地址2>, ...
* @throws MessagingException
*/
public static String getReceiveAddress(MimeMessage msg, Message.RecipientType type) throws MessagingException {
StringBuffer receiveAddress = new StringBuffer();
Address[] addresss = null;
if (type == null) {
addresss = msg.getAllRecipients();
} else {
addresss = msg.getRecipients(type);
}
if (addresss == null || addresss.length < 1) {
throw new MessagingException("没有收件人!");
}
for (Address address : addresss) {
InternetAddress internetAddress = (InternetAddress) address;
receiveAddress.append(internetAddress.toUnicodeString()).append(",");
}
receiveAddress.deleteCharAt(receiveAddress.length() - 1); // 删除最后一个逗号
return receiveAddress.toString();
}
/**
* 获得邮件发送时间
*
* @param msg 邮件内容
* @return yyyy年mm月dd日 星期X HH:mm
* @throws MessagingException
*/
public static String getSentDate(MimeMessage msg, String pattern) throws MessagingException {
Date receivedDate = msg.getSentDate();
if (receivedDate == null) {
return "";
}
if (pattern == null || "".equals(pattern)) {
pattern = "yyyy年MM月dd日 E HH:mm ";
}
return new SimpleDateFormat(pattern).format(receivedDate);
}
/**
* 判断邮件中是否包含附件
*
* @param part 邮件内容
* @return 邮件中存在附件返回true，不存在返回false
* @throws MessagingException
* @throws IOException
*/
public static boolean isContainAttachment(Part part) throws MessagingException, IOException {
boolean flag = false;
if (part.isMimeType("multipart/*")) {
MimeMultipart multipart = (MimeMultipart) part.getContent();
int partCount = multipart.getCount();
for (int i = 0; i < partCount; i++) {
BodyPart bodyPart = multipart.getBodyPart(i);
String disp = bodyPart.getDisposition();
if (disp != null && (disp.equalsIgnoreCase(Part.ATTACHMENT) || disp.equalsIgnoreCase(Part.INLINE))) {
flag = true;
} else if (bodyPart.isMimeType("multipart/*")) {
flag = isContainAttachment(bodyPart);
} else {
String contentType = bodyPart.getContentType();
if (contentType.indexOf("application") != -1) {
flag = true;
}
if (contentType.indexOf("name") != -1) {
flag = true;
}
}
if (flag) {
break;
}
}
} else if (part.isMimeType("message/rfc822")) {
flag = isContainAttachment((Part) part.getContent());
}
return flag;
}
/**
* 判断邮件是否已读
*
* @param msg 邮件内容
* @return 如果邮件已读返回true,否则返回false
* @throws MessagingException
*/
public static boolean isSeen(MimeMessage msg) throws MessagingException {
return msg.getFlags().contains(Flags.Flag.SEEN);
}
/**
* 判断邮件是否需要阅读回执
*
* @param msg 邮件内容
* @return 需要回执返回true,否则返回false
* @throws MessagingException
*/
public static boolean isReplySign(MimeMessage msg) throws MessagingException {
boolean replySign = false;
String[] headers = msg.getHeader("Disposition-Notification-To");
if (headers != null) {
replySign = true;
}
return replySign;
}
/**
* 获得邮件的优先级
*
* @param msg 邮件内容
* @return 1(High):紧急 3:普通(Normal) 5:低(Low)
* @throws MessagingException
*/
public static String getPriority(MimeMessage msg) throws MessagingException {
String priority = "普通";
String[] headers = msg.getHeader("X-Priority");
if (headers != null) {
String headerPriority = headers[0];
if (headerPriority.indexOf("1") != -1 || headerPriority.indexOf("High") != -1)
priority = "紧急";
else if (headerPriority.indexOf("5") != -1 || headerPriority.indexOf("Low") != -1) {
priority = "低";
} else {
priority = "普通";
}
}
return priority;
}
/**
* 获得邮件文本内容
*
* @param part 邮件体
* @param content 存储邮件文本内容的字符串
* @throws MessagingException
* @throws IOException
*/
public static void getMailTextContent(Part part, StringBuffer content) throws MessagingException, IOException {
// 如果是文本类型的附件，通过getContent方法可以取到文本内容，但这不是我们需要的结果，所以在这里要做判断
boolean isContainTextAttach = part.getContentType().indexOf("name") > 0;
if (part.isMimeType("text/*") && !isContainTextAttach) {
content.append(part.getContent().toString());
} else if (part.isMimeType("message/rfc822")) {
getMailTextContent((Part) part.getContent(), content);
} else if (part.isMimeType("multipart/*")) {
Multipart multipart = (Multipart) part.getContent();
int partCount = multipart.getCount();
for (int i = 0; i < partCount; i++) {
BodyPart bodyPart = multipart.getBodyPart(i);
getMailTextContent(bodyPart, content);
}
}
}
/**
* 保存附件
*
* @param part 邮件中多个组合体中的其中一个组合体
* @param destDir 附件保存目录
* @throws UnsupportedEncodingException
* @throws MessagingException
* @throws FileNotFoundException
* @throws IOException
*/
public static void saveAttachment(Part part, String destDir)
throws UnsupportedEncodingException, MessagingException, FileNotFoundException, IOException {
if (part.isMimeType("multipart/*")) {
Multipart multipart = (Multipart) part.getContent(); // 复杂体邮件
// 复杂体邮件包含多个邮件体
int partCount = multipart.getCount();
for (int i = 0; i < partCount; i++) {
// 获得复杂体邮件中其中一个邮件体
BodyPart bodyPart = multipart.getBodyPart(i);
// 某一个邮件体也有可能是由多个邮件体组成的复杂体
String disp = bodyPart.getDisposition();
if (disp != null && (disp.equalsIgnoreCase(Part.ATTACHMENT) || disp.equalsIgnoreCase(Part.INLINE))) {
InputStream is = bodyPart.getInputStream();
saveFile(is, destDir, decodeText(bodyPart.getFileName()));
} else if (bodyPart.isMimeType("multipart/*")) {
saveAttachment(bodyPart, destDir);
} else {
String contentType = bodyPart.getContentType();
if (contentType.indexOf("name") != -1 || contentType.indexOf("application") != -1) {
saveFile(bodyPart.getInputStream(), destDir, decodeText(bodyPart.getFileName()));
}
}
}
} else if (part.isMimeType("message/rfc822")) {
saveAttachment((Part) part.getContent(), destDir);
}
}
/**
* 读取输入流中的数据保存至指定目录
*
* @param is 输入流
* @param fileName 文件名
* @param destDir 文件存储目录
* @throws FileNotFoundException
* @throws IOException
*/
private static void saveFile(InputStream is, String destDir, String fileName)
throws FileNotFoundException, IOException {
BufferedInputStream bis = new BufferedInputStream(is);
BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream(new File(destDir + fileName)));
int len = -1;
while ((len = bis.read()) != -1) {
bos.write(len);
bos.flush();
}
bos.close();
bis.close();
}
/**
* 文本解码
*
* @param encodeText 解码MimeUtility.encodeText(String text)方法编码后的文本
* @return 解码后的文本
* @throws UnsupportedEncodingException
*/
public static String decodeText(String encodeText) throws UnsupportedEncodingException {
if (encodeText == null || "".equals(encodeText)) {
return "";
} else {
return MimeUtility.decodeText(encodeText);
}
}
}
2、从ftp拉取文件-->上传至oss。
首先在yml配置文件或者nacos中配置sftp基本信息。如下：
3、生成excel（csv后缀）文件。
1 /** 2 * 3 * 导出生成csv格式的文件 4 * @author ccg 5 * @param titles csv格式头文 6 * @param propertys 需要导出的数据实体的属性，注意与title一一对应 7 * @param list 需要导出的对象集合 8 * @return 9 * @throws IOException 10 * Created 2017年1月5日 上午10:51:44 11 * @throws IllegalAccessException 12 * @throws IllegalArgumentException 13 */ 14 public static <T> String exportCsv(String[] titles,String[] propertys,List<T> list) throws IOException, IllegalArgumentException, IllegalAccessException{ 15 File file = new File("d:\\test.csv"); 16 //构建输出流，同时指定编码 17 OutputStreamWriter ow = new OutputStreamWriter(new FileOutputStream(file), "gbk"); 18 19 //csv文件是逗号分隔，除第一个外，每次写入一个单元格数据后需要输入逗号 20 for(String title : titles){ 21 ow.write(title); 22 ow.write(","); 23 } 24 //写完文件头后换行 25 ow.write("\r\n"); 26 //写内容 27 for(Object obj : list){ 28 //利用反射获取所有字段 29 Field[] fields = obj.getClass().getDeclaredFields(); 30 for(String property : propertys){ 31 for(Field field : fields){ 32 //设置字段可见性 33 field.setAccessible(true); 34 if(property.equals(field.getName())){ 35 ow.write(field.get(obj).toString()); 36 ow.write(","); 37 continue; 38 } 39 } 40 } 41 //写完一行换行 42 ow.write("\r\n"); 43 } 44 ow.flush(); 45 ow.close(); 46 return "0"; 47 }
三、注意
1、注意文件和文件流之间的转换。
2、生成的csv可以直接生成文件。也可以把数据拼接为字符串，然后转换成字节数组再转换成流。
ByteArrayInputStream inputStream = new ByteArrayInputStream(bytes);
3、OSS官方文档地址：OSS官方文档
