基于POI和JavaMail實現(xiàn)讀取Excel表格內(nèi)容一鍵群發(fā)郵箱功能

公司財務(wù)要求做一個Excel表格工資條內(nèi)容群發(fā)郵箱的功能，由于我是菜鳥項目老大就把這任務(wù)交給我來做，開始時需求沒談好做了很多白費的功夫，在這里我算是長教訓(xùn)！一定要談好需求才開始做而且要找到對的人去談需求！最終還好在各同事指點下做出來了，好了廢話就不多說了下面直接上代碼。
1.上傳的Excel表格的內(nèi)容格式


2.前端代碼form表單multipart文件上傳

<div class="panel-title">
        <div>excel上傳:</div>
</div>
<form action="${pageContext.request.contextPath }/employee/excelpath.action"
   role="form" enctype="multipart/form-data" method="post" onsubmit="return submitTest()">
                <div class="panel-title">
                          <div align="left">
                          <input type="file" id="file" name="file" >
                          </div>            
                </div>
                <div class="panel-title">
                  <input type="submit" value="發(fā)送郵件" class="btn btn-success btn-sm">
                </div>
    </form>
<script src="${pageContext.request.contextPath }/js/jquery-1.9.1.js"></script>
<script type="text/javascript">
    /*導(dǎo)入員工工資表格*/          
    function submitTest(){
        var fileName = $("#file").val();  
         if(fileName.length > 1 && fileName ) {    
             var ldot = fileName.lastIndexOf(".");  
             var type = fileName.substring(ldot + 1).toLowerCase();  
             if(type == "xls" || type == "xlsx") { 
                 return true;
               }
             else{
                 alert("文件格式不正確,請重新選擇!");
                 $("#file").val(""); 
                 return false;
                 
                 
             }
          }else{
              alert("請選擇你要發(fā)送郵箱的exce表格文件!");
              return false;
          }
    }
    </script>

3.java后臺代碼
maven需要的下的jar

<!-- excel表格 -->
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.8</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.8</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.8</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-scratchpad -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-scratchpad</artifactId>
    <version>3.8</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.xmlbeans/xmlbeans -->
<dependency>
    <groupId>org.apache.xmlbeans</groupId>
    <artifactId>xmlbeans</artifactId>
    <version>2.3.0</version>
    <!-- 郵箱 -->
</dependency>  
        <dependency> 
        <groupId>javax.mail</groupId>
        <artifactId>mail</artifactId>
        <version>1.4.5</version>
    </dependency>

controller層代碼

package com.boyue.controller;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import javax.annotation.Resource;
import javax.mail.MessagingException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import com.boyue.core.utils.EmailUtil;

@Controller
@RequestMapping("employee")
public class EmployeeController {
    private final static String xls = "xls";  
    private final static String xlsx = "xlsx";
    /**  
     * 讀入excel文件，解析后返回  
     * @param file  
     * @throws IOException  
     * @throws MessagingException 
     */ 
   @RequestMapping(value = "excelpath")
  public String readExcel(Model model,MultipartFile file) throws IOException, MessagingException{ 
        //檢查文件  
        checkFile(file);
        //System.out.println(file);
        //獲得Workbook工作薄對象  
        Workbook workbook = getWorkBook(file);  
        int cellNum=0;
        //創(chuàng)建返回對象，把每行中的值作為一個數(shù)組，所有行作為一個集合返回  
        List<String[]> list = new ArrayList<String[]>();  
        if(workbook != null){  
            for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){  
                //獲得當前sheet工作表  
                Sheet sheet = workbook.getSheetAt(sheetNum);  
                if(sheet == null){  
                    continue;  
                }  
                //獲得當前sheet的開始行  
                int firstRowNum  = sheet.getFirstRowNum();  
                //獲得當前sheet的結(jié)束行  
                int lastRowNum = sheet.getLastRowNum();  
                //循環(huán)除了第一行的所有行  
               for(int rowNum = firstRowNum+1;rowNum <= lastRowNum;rowNum++){ //為了過濾到第一行因為我的第一行是數(shù)據(jù)庫的列</span>  
                    //獲得當前行  
                    Row row = sheet.getRow(rowNum);  
                    if(row == null){  
                        continue;  
                    }  
                    //獲得當前行的開始列  
                    int firstCellNum = row.getFirstCellNum();  
                    //獲得當前行的列數(shù)  
                    //int lastCellNum = row.getPhysicalNumberOfCells();//為空列不獲取  
                   //String[] cells = new String[row.getPhysicalNumberOfCells()];  
                   int lastCellNum = row.getLastCellNum();//為空列獲取  
                    String[] cells = new String[row.getLastCellNum()];
                    //循環(huán)當前行  
                    for(cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){  
                        Cell cell = row.getCell(cellNum);  
                        cells[cellNum] = getCellValue(cell);  
                    }  
                    list.add(cells); 
                }  
                
            }  
            for (int i = 0; i < list.size(); i++) {
               //EmailUtil.send("收件人郵箱號"，"郵件標題"，"郵件內(nèi)容")
                EmailUtil.send(list.get(i)[1],"工資情況",list.get(i)[0]+"你好:<br><pre>   以下是你"+list.get(i)[2]+"份的工資條，"+list.get(i)[3]+"<br>  如有疑問請找前臺統(tǒng)計好，前臺上交財務(wù)部統(tǒng)一處理。謝謝合作！</pre>");
            }
         
        }  
        //logger.info(gson.toJson(list));
       
         return "employee/success";
    }  
    public static void checkFile(MultipartFile file) throws IOException{  
        //判斷文件是否存在  
        if(null == file){  
            throw new FileNotFoundException("文件不存在！");  
        }  
        //獲得文件名  
        String fileName = file.getOriginalFilename();  
        //判斷文件是否是excel文件  
        if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx)){  
            throw new IOException(fileName + "不是excel文件");  
        }  
    }  
    public static Workbook getWorkBook(MultipartFile file) {  
        //獲得文件名  
        String fileName = file.getOriginalFilename();  
        //創(chuàng)建Workbook工作薄對象，表示整個excel  
        Workbook workbook = null;  
        try {  
            //獲取excel文件的io流  
            InputStream is = file.getInputStream();  
            //根據(jù)文件后綴名不同(xls和xlsx)獲得不同的Workbook實現(xiàn)類對象  
            if(fileName.endsWith(xls)){  
                //2003  
                workbook = new HSSFWorkbook(is);  
            }else if(fileName.endsWith(xlsx)){  
                //2007  
                workbook = new XSSFWorkbook(is);  
            }  
        } catch (IOException e) {  
            //logger.info(e.getMessage());
            System.out.println(e.getMessage());
        }  
        return workbook;  
    }  
    public static String getCellValue(Cell cell){  
        String cellValue = "";  
        if(cell == null){  
            return cellValue;  
        }  
        //把數(shù)字當成String來讀，避免出現(xiàn)1讀成1.0的情況  
        if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){  
            cell.setCellType(Cell.CELL_TYPE_STRING);  
        }  
        //判斷數(shù)據(jù)的類型  
        switch (cell.getCellType()){  
            case Cell.CELL_TYPE_NUMERIC: //數(shù)字  
                cellValue = String.valueOf(cell.getNumericCellValue());  
                break;  
            case Cell.CELL_TYPE_STRING: //字符串  
                cellValue = String.valueOf(cell.getStringCellValue());  
                break;  
            case Cell.CELL_TYPE_BOOLEAN: //Boolean  
                cellValue = String.valueOf(cell.getBooleanCellValue());  
                break;  
            case Cell.CELL_TYPE_FORMULA: //公式  
//                cellValue = String.valueOf(cell.getCellFormula());  
                cellValue = String.valueOf(cell.getStringCellValue());  
                break;  
            case Cell.CELL_TYPE_BLANK: //空值  
                cellValue = "";  
                break;  
            case Cell.CELL_TYPE_ERROR: //故障  
                cellValue = "非法字符";  
                break;  
            default:  
                cellValue = "未知類型";  
                break;  
        }  
        return cellValue;  
    }
}

4.獲取發(fā)件人的IMAP授權(quán)碼

在qq郵箱或者網(wǎng)易郵箱里開啟POP3和IMAP并記錄IMAP的授權(quán)碼

5.創(chuàng)建郵箱發(fā)送的工具類 
package com.boyue.core.utils;

import java.util.Properties;

import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;

import com.sun.mail.util.MailSSLSocketFactory;

public class EmailUtil {
    /**
     * 發(fā)送郵件
     * @param to       收件人郵箱
     * @param subject  標題
     * @param msg      消息內(nèi)容
     * @return
     */
    public static boolean send(String to,String subject,String msg){

        Properties props = new Properties();
        
        //郵件傳輸?shù)膮f(xié)議
        props.put("mail.transport.protocol", "smtp");
        //連接的郵件服務(wù)器
        props.put("mail.host","smtp.qq.com");
        //發(fā)送人
        props.put("mail.from","xxxxxxxxx@qq.com");

        //第一步：創(chuàng)建session
        Session session = Session.getDefaultInstance(props);
        session.setDebug(true);

        try {
            MailSSLSocketFactory sf = new MailSSLSocketFactory();
            sf.setTrustAllHosts(true);
            props.put("mail.smtp.ssl.enable", "true");
            props.put("mail.smtp.ssl.socketFactory", "true");
            //第二步：獲取郵件傳輸對象
            Transport ts= session.getTransport();
            //連接郵件服務(wù)器
            ts.connect("xxxxxxxxxx@qq.com", "你的IMAP授權(quán)碼");
            //第三步：創(chuàng)建郵件消息體
            MimeMessage message = new MimeMessage(session);
            //設(shè)置郵件的標題
            message.setSubject(subject, "utf-8");
            //設(shè)置郵件的內(nèi)容
            message.setContent(msg,"text/html;charset=utf-8");
            //第四步：設(shè)置發(fā)送昵稱
            String nick="";
            nick = javax.mail.internet.MimeUtility.encodeText("財務(wù)部");
            message.setFrom(new InternetAddress(nick+"'<xxxxxxxxx@qq.com>'"));
            //第五步：設(shè)置接收人信息
            ts.sendMessage(message, InternetAddress.parse(to));

        } catch (Exception ex) {
            // TODO Auto-generated catch block
            ex.printStackTrace();
        }

        return false;

    }
}

6.成功的截圖
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容