JAVA 資料寫入excel併傳送郵件

寫這個的時候PLM系統還沒有開發好，開發這個系統的外包團隊每次開會都會被罵，感覺他們快頂不住了，估計完成不了了，爛尾之後應該會有很多需求扔給我。新領導上任之後說這邊能不能發郵件，先熟悉一下怎麼發郵件吧，這個功能大機率給我來做了

流程：

​ 先導包 => 郵箱開啟配置 => java寫好配置類 => 測試傳送 => 資料寫入excel => 郵件帶附件傳送

郵箱jar包

<dependencies>
    <dependency>
    <groupId>com.sun.mail</groupId>
    <artifactId>javax.mail</artifactId>
    <version>1.6.2</version>
    </dependency>
</dependencies>

郵箱開啟配置

我這個是163的，開啟之後會生成授權碼，記得複製貼上到自己的專案配置檔案裡面去

郵箱配置類

public final class JavaMailUtil {
    //檔案地址 上面是linux地址，下面是我自己本地測試用的
    //public final static String FILEPATH = "/wenjie/javaProject/bomexcelfiles";
    public final static String FILEPATH = "e:/Users/liuwenj/Desktop/";
    
    private JavaMailUtil() {
    }
    public static Session createSession() {
        //賬號資訊
        String username = "";//郵箱傳送賬號
        String password = "";//郵箱授權碼
        //建立一個配置檔案，並儲存
        Properties props = new Properties();
        //SMTP伺服器連線資訊
        //126——smtp.126.com
        //163——smtp.163.com
        props.put("mail.smtp.host", "smtp.126.net");//SMTP主機名
        //126——25
        //163——645 如果645一直連不上，可以換成25試一試
        props.put("mail.smtp.port", "25");// 主機埠號
        props.put("mail.smtp.auth", "true");// 是否需要使用者認證
        props.put("mail.smtp.starttls.enable", "true");// 啟用TlS加密
        Session session = Session.getInstance(props, new Authenticator() {
            @Override
            protected PasswordAuthentication getPasswordAuthentication() {
                // TODO Auto-generated method stub
                return new PasswordAuthentication(username, password);
            }
        });
        //控制檯列印除錯資訊
        session.setDebug(true);
        return session;
    }
}

測試傳送純文字

//建立Session會話
Session session = JavaMailUtil.createSession();

//建立郵件物件
MimeMessage message = new MimeMessage(session);
message.setSubject("主題");
//設定傳送人
message.setFrom(new InternetAddress("傳送者郵箱"));

//setRecipient => 傳送單人
//setRecipients => 傳送多人
//MimeMessage.RecipientType.TO => 收件人
//MimeMessage.RecipientType.CC => 抄送人
//設定收件人
message.setRecipient(MimeMessage.RecipientType.TO, new InternetAddress("收件人郵箱"));
//message.setRecipients(MimeMessage.RecipientType.TO, new InternetAddress[] {new InternetAddress("收件人郵箱")});
//設定抄送人
message.setRecipient(MimeMessage.RecipientType.CC, new InternetAddress("抄送人郵箱"));
//message.setRecipients(MimeMessage.RecipientType.CC, new InternetAddress[] {new InternetAddress("抄送人郵箱")});

//純文字資訊
message.setText("文字資訊:來自於公司PLM系統測試");
//傳送
Transport.send(message);

資料寫入excel，並生成檔案

先導jar

<!-- Apache POI -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.3</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.3</version>
</dependency>

寫入資料到excel

Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Bom");
//第一行的資料名
List<String> labelList = Arrays.asList("物料編碼", "描述");
Row row = sheet.createRow(0);
for (int i = 0; i < labelList.size(); i++) {
    row.createCell(i).setCellValue(labelList.get(i));
}
int rowNo = 1;
//獲取資料然後放入Excel
List<Map<String, Object>> bomList = plmDataService.getBomNotPerfect();
for (Map<String, Object> bom : bomList) {
    String no = (String) bom.get("ITEM_NUMBER");
    String desc = (String) bom.get("DESCRIPTION");
    Row tempRow = sheet.createRow(rowNo);
    rowNo++;

    tempRow.createCell(0).setCellValue(no);
    tempRow.createCell(1).setCellValue(desc);
}
// 寫入資料到Excel
try (FileOutputStream outputStream = new FileOutputStream(JavaMailUtil.FILEPATH + fileName)) {
    workbook.write(outputStream);
} catch (IOException e) {
    e.printStackTrace();
} finally {
    try {
        workbook.close(); //關閉工作簿
    } catch (IOException e) {
        e.printStackTrace();
    }
}

一切準備就緒之後就可以傳送郵件了

傳送郵件（帶附件）

//建立會話
Session session = JavaMailUtil.createSession();

//建立郵件物件
MimeMessage message = new MimeMessage(session);
message.setSubject("主題");
message.setFrom(new InternetAddress("傳送者郵箱"));

//設定收件人
message.setRecipient(MimeMessage.RecipientType.TO, new InternetAddress("收件人郵箱"));
//message.setRecipients(MimeMessage.RecipientType.TO, new InternetAddress[] {new InternetAddress("收件人郵箱")});
//設定抄送人
message.setRecipient(MimeMessage.RecipientType.CC, new InternetAddress("抄送人郵箱"));
//message.setRecipients(MimeMessage.RecipientType.CC, new InternetAddress[] {new InternetAddress("抄送人郵箱")});

//郵件主體，文字換行
MimeBodyPart textPart = new MimeBodyPart();
StringBuilder content = new StringBuilder("以下物料BOM工藝已完整，可釋出標準成本\n111\n");
textPart.setText(content.toString());

//郵件附件
BodyPart filePart = new MimeBodyPart();
filePart.setFileName(fileName);

//提交附件檔案
filePart.setDataHandler(new DataHandler(new ByteArrayDataSource(Files.readAllBytes(Paths.get(JavaMailUtil.FILEPATH + fileName)), "application/octet-stream")));

Multipart multipart = new MimeMultipart();
multipart.addBodyPart(textPart);
multipart.addBodyPart(filePart);

//將郵件裝入信封
message.setContent(multipart);
//傳送
Transport.send(message);

如果有傳送一些特殊需求，比如需要內嵌圖片HTML

傳送郵件（內嵌圖片HTML）

PS: 沒有試過，copy的程式碼，貼上去以後如果有需求了再驗證

Session session = JavaMailUtils.createSession(); //建立session物件
MimeMessage message = new MimeMessage(session); //建立message物件
message.setSubject("測試郵件"); //設定郵件標題
message.setFrom(new InternetAddress("xxxxxx@163.com")); //設定傳送方地址
message.setRecipient(RecipientType.TO, new InternetAddress("xxxxxx@qq.com")); //設定接收方地址
message.setRecipients(RecipientType.CC, new InternetAddress[] {new InternetAddress("xxxxxx@qq.com"),new InternetAddress("xxxxxx@qq.com")}); //群發(抄送多人)
//正文
BodyPart textPart=new MimeBodyPart();
StringBuilder contentText=new StringBuilder();
contentText.append("<h3>網易郵箱/h3>");
contentText.append("<p>給QQ郵箱發訊息了！</p>");
contentText.append("<img src=\"cid:xxx\"/>");  
textPart.setContent(contentText.toString(),"text/html;charset=utf-8");
//附件
BodyPart imagePart=new MimeBodyPart();  
imagePart.setDataHandler(new DataHandler(
    new ByteArrayDataSource(Files.readAllBytes(Paths.get("D://test//1.jpg")), "application/octet-stream"))); //
imagePart.setHeader("Content-ID", "xxx"); //圖片的內容ID
Multipart multipart=new MimeMultipart(); //建立multipart物件
multipart.addBodyPart(textPart); //將textPart物件放入multipart
multipart.addBodyPart(filePart); //將filePartt物件放入multipart
message.setContent(multipart); //將multipart物件放入郵件
Transport.send(message); //傳送郵件