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 block
e.printStackTrace();
}
// 创建多部分内容
MimeMultipart multipart = new MimeMultipart("mixed");
try {
multipart.addBodyPart(htmlPart);
} catch (MessagingException e) {
// TODO Auto-generated catch block
e.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 block
e.printStackTrace();
}
multipart.addBodyPart(attachmentPart);
} catch (MessagingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
// 设置完整内容
email.setContent(multipart, "multipart/mixed");
email.send();
System.out.println("邮件发送成功");
} catch (EmailException e) {
// TODO Auto-generated catch block
e.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 block
e.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));
// 根据文件扩展名创建不同的Workbook
if (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到endrow
for (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("&", "&").replace("<", "<").replace(">", ">").replace("\"", """)
.replace("'", "'");
}
}
