JAVA批量发送邮件（含excel内容）

EmailSenderHtmlV1 是读取配置文件《批量发送邮件.xlsx》，配置sheet获取 发件人邮箱    邮箱账号    口令，发送excel数据sheet获取收件人邮箱    抄送人邮箱    邮件标题    第N行开始(N>=1,N=0默认表头)    第M行结束(M>=1,M=0默认表头)    附件文件夹    附件名，同时发送excel数据内容详情sheet配合发送excel数据sheet的第N和第M行信息，获取excel内容。

EmailSenderV1是读取配置文件《批量发送邮件.xlsx》，配置sheet获取 发件人邮箱    邮箱账号    口令，发送数据sheet 获取 收件人邮箱    抄送人邮箱    邮件标题    邮件内容    附件文件夹 ,  附件名。

ExcelToHtmlConverter 是将excel数据内容详情sheet内单元格信息转换为html格式，作为邮件内容发送。

1.

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.Properties;import javax.activation.DataHandler;
import javax.activation.FileDataSource;
import javax.mail.MessagingException;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeMultipart;
import javax.mail.internet.MimeUtility;import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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.apache.commons.mail.DefaultAuthenticator;
import org.apache.commons.mail.EmailAttachment;
import org.apache.commons.mail.EmailException;
import org.apache.commons.mail.MultiPartEmail;
import org.apache.commons.mail.SimpleEmail;public class EmailSenderHtmlV1 {private String smtpServer;private String username;private String password;private String senderEmail;private boolean isMany;private String excelFile;private Sheet configSheet;private Sheet dataSheet;private String beginrow;private String endrow;public EmailSenderHtmlV1(boolean many, Properties smtpConfig, String excelFile) {setConfig(many, smtpConfig, excelFile);}public void setConfig(boolean many, Properties smtpConfig, String excelFile) {try {this.isMany = many;if (many) {this.excelFile = excelFile;FileInputStream file = new FileInputStream(new File(excelFile));// System.out.println(file.toString());Workbook workbook = new XSSFWorkbook(file);// System.out.println(workbook.toString());this.configSheet = workbook.getSheet("配置");// System.out.println(this.configSheet.toString());// 从配置表读取SMTP信息Row row = configSheet.getRow(1); // 第二行(index=1)this.smtpServer = row.getCell(0).getStringCellValue();this.senderEmail = row.getCell(1).getStringCellValue();this.username = row.getCell(2).getStringCellValue();this.password = row.getCell(3).getStringCellValue();workbook.close();file.close();} else {this.smtpServer = smtpConfig.getProperty("smtp_server");this.username = smtpConfig.getProperty("username");this.password = smtpConfig.getProperty("password");this.senderEmail = smtpConfig.getProperty("sender_email");}} catch (Exception e) {System.out.println("邮件配置错误：" + e.getMessage());System.exit(1);}}public void MultiSendEmail(Properties contentConfig) {MultiPartEmail email = new MultiPartEmail();// email.setTLS(true);// email.setSSL(true);// email.setDebug(true);email.setHostName(smtpServer);email.setSSLOnConnect(true);email.setSmtpPort(465);// 设置TLS协议（关键配置）System.setProperty("mail.smtp.ssl.protocols", "TLSv1.2");email.setAuthenticator(new DefaultAuthenticator(username, password));try {email.setFrom(senderEmail);// 发件人// 设置收件人String receiverEmail = contentConfig.getProperty("receiver_email");email.addTo(receiverEmail);// 设置抄送人String copyEmail = contentConfig.getProperty("copy_email");if (copyEmail != null && !copyEmail.isEmpty()) {email.addCc(copyEmail);}email.setCharset("UTF-8");email.setSubject(contentConfig.getProperty("subject"));email.setMsg(contentConfig.getProperty("message"));// 创建 HTML 内容部分MimeBodyPart htmlPart = new MimeBodyPart();String htmlContent = contentConfig.getProperty("message");try {htmlPart.setContent(htmlContent, "text/html; charset=utf-8");} catch (MessagingException e) {// TODO Auto-generated catch blocke.printStackTrace();}// 创建多部分内容MimeMultipart multipart = new MimeMultipart("mixed");try {multipart.addBodyPart(htmlPart);} catch (MessagingException e) {// TODO Auto-generated catch blocke.printStackTrace();}String attachmentPath = contentConfig.getProperty("attachment_path");String attachmentNames = contentConfig.getProperty("attachment_name");FileDataSource fds;if (attachmentNames != null && !attachmentNames.isEmpty()) {String[] files = attachmentNames.split(",");for (String fileName : files) {//attachment.setName(fileName);// 添加附件MimeBodyPart attachmentPart = new MimeBodyPart();try {fds = new FileDataSource(attachmentPath + File.separator + fileName);attachmentPart.setDataHandler(new DataHandler(fds));try {attachmentPart.setFileName(MimeUtility.encodeText(fileName, "UTF-8", "B"));} catch (UnsupportedEncodingException e) {// TODO Auto-generated catch blocke.printStackTrace();}multipart.addBodyPart(attachmentPart);} catch (MessagingException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}// 设置完整内容email.setContent(multipart, "multipart/mixed");email.send();System.out.println("邮件发送成功");} catch (EmailException e) {// TODO Auto-generated catch blocke.printStackTrace();}}public void bulkSendEmail() {try {FileInputStream file = new FileInputStream(new File(excelFile));Workbook workbook = new XSSFWorkbook(file);Sheet sheet = workbook.getSheet("发送excel数据");// 从第二行(index=1)开始读取数据，第一行是表头for (int i = 1; i <= sheet.getLastRowNum(); i++) {Row row = sheet.getRow(i);if (row == null)continue;Properties contentConfig = new Properties();contentConfig.setProperty("receiver_email", getCellValue(row.getCell(0)));contentConfig.setProperty("copy_email", getCellValue(row.getCell(1)));contentConfig.setProperty("subject", getCellValue(row.getCell(2)));// contentConfig.setProperty("message", getCellValue(row.getCell(3)));contentConfig.setProperty("beginrow", getCellValue(row.getCell(3)));contentConfig.setProperty("endrow", getCellValue(row.getCell(4)));contentConfig.setProperty("attachment_path", getCellValue(row.getCell(5)));contentConfig.setProperty("attachment_name", getCellValue(row.getCell(6)));// getexcelcontent(getCellValue(row.getCell(3)),getCellValue(row.getCell(4)));ExcelToHtmlConverter eth = new ExcelToHtmlConverter(excelFile, getCellValue(row.getCell(3)),getCellValue(row.getCell(4)));contentConfig.setProperty("message", eth.convertExcelToHtml());MultiSendEmail(contentConfig);}workbook.close();file.close();} catch (IOException e) {System.out.println("读取Excel文件错误：" + e.getMessage());}}public void getexcelcontent(String beginrow, String endrow) {try {FileInputStream file = new FileInputStream(new File(excelFile));Workbook workbook = new XSSFWorkbook(file);Sheet sheet = workbook.getSheet("发送excel数据内容详情");// 从第二行(index=1)开始读取数据，第一行是表头for (int i = 1; i <= sheet.getLastRowNum(); i++) {Row row = sheet.getRow(i);if (row == null)continue;}// contentConfig.setProperty("message", getCellValue(row.getCell(3)));workbook.close();file.close();} catch (IOException e) {System.out.println("读取Excel文件错误：" + e.getMessage());}}private String getCellValue(Cell cell) {if (cell == null)return "";switch (cell.getCellType()) {case STRING:return cell.getStringCellValue();case NUMERIC:if (DateUtil.isCellDateFormatted(cell)) {return cell.getDateCellValue().toString();} else {return String.valueOf((int) cell.getNumericCellValue());}case BOOLEAN:return String.valueOf(cell.getBooleanCellValue());case FORMULA:return cell.getCellFormula();default:return "";}}
/*public static void main(String[] args) {// 批量发送邮件示例String excelFile = "D:/SendMail/批量发送邮件.xlsx";EmailSenderHtmlV1 emailSender = new EmailSenderHtmlV1(true, null, excelFile);emailSender.bulkSendEmail();}
*/
}

2.

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Properties;import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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.apache.commons.mail.DefaultAuthenticator;
import org.apache.commons.mail.EmailAttachment;
import org.apache.commons.mail.EmailException;
import org.apache.commons.mail.MultiPartEmail;
import org.apache.commons.mail.SimpleEmail;public class EmailSenderV1 {private String smtpServer;private String username;private String password;private String senderEmail;private boolean isMany;private String excelFile;private Sheet configSheet;private Sheet dataSheet;public EmailSenderV1(boolean many, Properties smtpConfig, String excelFile) {setConfig(many, smtpConfig, excelFile);}public void setConfig(boolean many, Properties smtpConfig, String excelFile) {try {this.isMany = many;if (many) {this.excelFile = excelFile;FileInputStream file = new FileInputStream(new File(excelFile));// System.out.println(file.toString());Workbook workbook = new XSSFWorkbook(file);// System.out.println(workbook.toString());this.configSheet = workbook.getSheet("配置");// System.out.println(this.configSheet.toString());// 从配置表读取SMTP信息Row row = configSheet.getRow(1); // 第二行(index=1)this.smtpServer = row.getCell(0).getStringCellValue();this.senderEmail = row.getCell(1).getStringCellValue();this.username = row.getCell(2).getStringCellValue();this.password = row.getCell(3).getStringCellValue();workbook.close();file.close();} else {this.smtpServer = smtpConfig.getProperty("smtp_server");this.username = smtpConfig.getProperty("username");this.password = smtpConfig.getProperty("password");this.senderEmail = smtpConfig.getProperty("sender_email");}} catch (Exception e) {System.out.println("邮件配置错误：" + e.getMessage());System.exit(1);}}public void MultiSendEmail(Properties contentConfig) {MultiPartEmail email = new MultiPartEmail();// email.setTLS(true);// email.setSSL(true);// email.setDebug(true);email.setHostName(smtpServer);email.setSSLOnConnect(true);email.setSmtpPort(465);// 设置TLS协议（关键配置）System.setProperty("mail.smtp.ssl.protocols", "TLSv1.2");email.setAuthenticator(new DefaultAuthenticator(username, password));try {email.setFrom(senderEmail);// 发件人// 设置收件人String receiverEmail = contentConfig.getProperty("receiver_email");email.addTo(receiverEmail);// 设置抄送人String copyEmail = contentConfig.getProperty("copy_email");if (copyEmail != null && !copyEmail.isEmpty()) {email.addCc(copyEmail);}email.setCharset("UTF-8");email.setSubject(contentConfig.getProperty("subject"));email.setMsg(contentConfig.getProperty("message"));// 添加附件String attachmentPath = contentConfig.getProperty("attachment_path");String attachmentNames = contentConfig.getProperty("attachment_name");EmailAttachment attachment = new EmailAttachment();if (attachmentNames != null && !attachmentNames.isEmpty()) {String[] files = attachmentNames.split(",");for (String fileName : files) {attachment.setPath(attachmentPath + File.separator + fileName);attachment.setName(fileName);email.attach(attachment);// 添加附件}}email.send();System.out.println("邮件发送成功");} catch (EmailException e) {// TODO Auto-generated catch blocke.printStackTrace();}}public void bulkSendEmail() {try {FileInputStream file = new FileInputStream(new File(excelFile));Workbook workbook = new XSSFWorkbook(file);Sheet sheet = workbook.getSheet("发送数据");// 从第二行(index=1)开始读取数据，第一行是表头for (int i = 1; i <= sheet.getLastRowNum(); i++) {Row row = sheet.getRow(i);if (row == null)continue;Properties contentConfig = new Properties();contentConfig.setProperty("receiver_email", getCellValue(row.getCell(0)));contentConfig.setProperty("copy_email", getCellValue(row.getCell(1)));contentConfig.setProperty("subject", getCellValue(row.getCell(2)));contentConfig.setProperty("message", getCellValue(row.getCell(3)));contentConfig.setProperty("attachment_path", getCellValue(row.getCell(4)));contentConfig.setProperty("attachment_name", getCellValue(row.getCell(5)));MultiSendEmail(contentConfig);}workbook.close();file.close();} catch (IOException e) {System.out.println("读取Excel文件错误：" + e.getMessage());}}private String getCellValue(Cell cell) {if (cell == null)return "";switch (cell.getCellType()) {case STRING:return cell.getStringCellValue();case NUMERIC:if (DateUtil.isCellDateFormatted(cell)) {return cell.getDateCellValue().toString();} else {return String.valueOf((int) cell.getNumericCellValue());}case BOOLEAN:return String.valueOf(cell.getBooleanCellValue());case FORMULA:return cell.getCellFormula();default:return "";}}/*public static void main(String[] args) {// 批量发送邮件示例String excelFile = "D:/SendMail/批量发送邮件.xlsx";EmailSenderV1 emailSender = new EmailSenderV1(true, null, excelFile);emailSender.bulkSendEmail();}
*/}

3.

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;import java.io.*;public class ExcelToHtmlConverter {String excelFilePath;String sheetName;String beginrow;String endrow;public ExcelToHtmlConverter(String excelFilePath, String beginrow, String endrow) {this.excelFilePath = excelFilePath;this.sheetName = "发送excel数据内容详情";this.beginrow = beginrow;this.endrow = endrow;}
/*public static void main(String[] args) {String excelFilePath = "D:/SendMail/批量发送邮件.xlsx";String beginrow = null;String endrow = null;ExcelToHtmlConverter eth = new ExcelToHtmlConverter(excelFilePath, beginrow, endrow);try {eth.convertExcelToHtml();System.out.println("Excel转换为HTML成功！");} catch (IOException e) {System.err.println("转换失败：" + e.getMessage());e.printStackTrace();}}
*/public String convertExcelToHtml() throws IOException {String result = "";Workbook workbook = null;FileInputStream fileInputStream = null;if (excelFilePath.equals("") || excelFilePath == null) {return null;}try {// 读取Excel文件fileInputStream = new FileInputStream(new File(excelFilePath));// 根据文件扩展名创建不同的Workbookif (excelFilePath.toLowerCase().endsWith(".xlsx")) {workbook = new XSSFWorkbook(fileInputStream);} else if (excelFilePath.toLowerCase().endsWith(".xls")) {workbook = new HSSFWorkbook(fileInputStream);} else {throw new IllegalArgumentException("不支持的文件格式：" + excelFilePath);}// 获取指定工作表Sheet sheet = workbook.getSheet(sheetName);if (sheet == null) {throw new IllegalArgumentException("找不到工作表：" + sheetName);}// 写入HTML头部result += "<html>\n";result += "<head>\n";// result += "<meta charset=\"UTF-8\">\n";// result+="<title>Excel转HTML</title>\n";result += "<style>\n";result += "    table { border-collapse: collapse; width: 100%; }\n";result += "    th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }\n";result += "    th { background-color: #f2f2f2; font-weight: bold; }\n";result += "    tr:nth-child(even) { background-color: #f9f9f9; }\n";result += "    tr:hover { background-color: #f5f5f5; }\n";result += "</style>\n";result += "</head>\n";result += "<body>\n";// 创建表格result += "<table>\n";// 第一行写入Row row1 = sheet.getRow(0);if (row1 == null) {return null;}// 写入行开始标签result += "    <tr>\n";// 获取当前行的最大列数int lastCellNum1 = row1.getLastCellNum();if (lastCellNum1 < 0) {lastCellNum1 = 0;}for (int cellIndex1 = 0; cellIndex1 < lastCellNum1; cellIndex1++) {Cell cell = row1.getCell(cellIndex1);String cellValue1 = "";// 获取单元格的值if (cell != null) {switch (cell.getCellType()) {case STRING:cellValue1 = cell.getStringCellValue();break;case NUMERIC:if (DateUtil.isCellDateFormatted(cell)) {cellValue1 = cell.getDateCellValue().toString();} else {cellValue1 = String.valueOf(cell.getNumericCellValue());}break;case BOOLEAN:cellValue1 = String.valueOf(cell.getBooleanCellValue());break;case FORMULA:cellValue1 = cell.getCellFormula();break;default:cellValue1 = "";}}// 写入单元格String cellTag1 = "th"; // 第一行作为表头result += "        <" + cellTag1 + ">" + escapeHtml(cellValue1) + "</" + cellTag1 + ">\n";}// 遍历工作表中的指定的行beginrow到endrowfor (int rowIndex = Integer.parseInt(beginrow); rowIndex <= Integer.parseInt(endrow); rowIndex++) {Row row = sheet.getRow(rowIndex);if (row == null) {continue;}// 写入行开始标签result += "    <tr>\n";// 获取当前行的最大列数int lastCellNum = row.getLastCellNum();if (lastCellNum < 0) {lastCellNum = 0;}// 遍历行中的所有单元格for (int cellIndex = 0; cellIndex < lastCellNum; cellIndex++) {Cell cell = row.getCell(cellIndex);String cellValue = "";// 获取单元格的值if (cell != null) {switch (cell.getCellType()) {case STRING:cellValue = cell.getStringCellValue();break;case NUMERIC:if (DateUtil.isCellDateFormatted(cell)) {cellValue = cell.getDateCellValue().toString();} else {cellValue = String.valueOf(cell.getNumericCellValue());}break;case BOOLEAN:cellValue = String.valueOf(cell.getBooleanCellValue());break;case FORMULA:cellValue = cell.getCellFormula();break;default:cellValue = "";}// 写入单元格String cellTag = "td"; // 表格result += "        <" + cellTag + ">" + escapeHtml(cellValue) + "</" + cellTag + ">\n";// 写入行结束标签}}}// 结束表格result += ("</table>\n");result += ("</body>\n");result += ("</html>\n");} finally {// 关闭资源if (workbook != null) {try {workbook.close();} catch (IOException e) {e.printStackTrace();}}if (fileInputStream != null) {try {fileInputStream.close();} catch (IOException e) {e.printStackTrace();}}}return result;}// HTML转义方法，防止XSS攻击private static String escapeHtml(String input) {if (input == null) {return "";}return input.replace("&", "&amp;").replace("<", "&lt;").replace(">", "&gt;").replace("\"", "&quot;").replace("'", "&#39;");}
}