公司需求：根据业务需要定时发送邮件，并且邮件需要有excel附件，附件内容为查询到的数据。
开发思路：由于服务器环境本身空间有限，而且不确定环境中读写权限，生成excel文件比较浪费资源，出现异常时可能导致删除临时文件失败。因此生成excel文件时不生成实际文件，直接获取到excel写入流，并且将流直接写入到邮件附件中。
查询了很多网上资料以及java邮件API，最终得以实现，分享下供大家参考，互相学习！
转载请标明出处：https://blog.csdn.net/yixin605691235/article/details/82429156
详细代码：https://download.csdn.net/download/yixin605691235/10648074
核心代码如下：
配置邮箱服务器端口及发送人接收人
mail.smtp.host=smtp.exmail.qq.com
mail.smtp.port=465
mail.send.address=xxx@xxx.xx
mail.send.password=xxxxxx
mail.accept.address=qqqqqq@qq.com
mail.accept.address.other=qqqqqq@qq.com,qqqqqq@163.com生成excel工具类
package com.yx.mail;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang3.StringUtils;
import jxl.Workbook;
import jxl.common.Logger;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
/**
* java excel api(jxl) 导出excel工具类
* @author Yanyanhui
*
*/
public class JxlUtil {
private static Logger logger = Logger.getLogger(JxlUtil.class);
private static int MAX_SHEET_SIZE = 65530;
private static String DOUBLE_FORMAT = "#.00";
private static String INTER_FORMAT = "#";
private static WritableCellFormat INTER_CELL_FORMAT = new WritableCellFormat(new NumberFormat(INTER_FORMAT));
private static WritableCellFormat DOUBLE_CELL_FORMAT = new WritableCellFormat(new NumberFormat(DOUBLE_FORMAT));
/**
* 列表转换成excel导出
* @param list 要转换的列表
* @param title 转换列表的标题
* @param sheetName 工作表名称
* @param request 请求
* @param response 响应
* @throws Exception
*/
public static <T> void listToExcel(List<T> list,LinkedHashMap<String, String> title,String sheetName,HttpServletRequest request,HttpServletResponse response) throws Exception{
listToExcel(list, title, sheetName, sheetName, 0, 0, request, response);
}
/**
* 列表转换成excel导出
* @param list 要转换的列表
* @param title 转换列表的标题
* @param sheetName 工作表名称
* @param fileName excel文件名称
* @param request 请求
* @param response 响应
* @throws Exception
*/
public static <T> void listToExcel(List<T> list,LinkedHashMap<String, String> title,String sheetName, String fileName,HttpServletRequest request,HttpServletResponse response) throws Exception{
listToExcel(list, title, sheetName, fileName, 0, 0, request, response);
}
/**
* 列表转换成excel导出
* @param list 要转换的列表
* @param title 转换列表的标题
* @param sheetName 工作表名称
* @param fileName excel文件名称
* @param startRow 从哪行开始输出
* @param request 请求
* @param response 响应
* @throws Exception
*/
public static <T> void listToExcel(List<T> list,LinkedHashMap<String, String> title,String sheetName, String fileName,int startRow,HttpServletRequest request,HttpServletResponse response) throws Exception{
listToExcel(list, title, sheetName, fileName, 0, startRow, request, response);
}
/**
* 列表转换成excel导出
* @param list 要转换的列表
* @param title 转换列表的标题
* @param sheetName 工作表名称
* @param fileName excel文件名称
* @param sheetSize 工作表的大小
* @param startRow 从哪行开始输出
* @param request 请求
* @param response 响应
* @throws Exception
*/
public static <T> void listToExcel(List<T> list,LinkedHashMap<String, String> title,String sheetName, String fileName,int sheetSize,int startRow,HttpServletRequest request,HttpServletResponse response) throws Exception{
if (list == null || list.size() == 0) {
// throw new Exception("列表中没有任何数据");
}
if (sheetSize > MAX_SHEET_SIZE || sheetSize < 1) {
sheetSize = MAX_SHEET_SIZE;
}
try {
setResp(request, response, fileName);
OutputStream outputStream = response.getOutputStream();
WritableWorkbook wwb = Workbook.createWorkbook(outputStream);
int size = 0;
// 因为2003的Excel一个工作表最多可以有65536条记录，除去列头剩下65530条
// 所以如果记录太多，需要放到多个工作表中，其实就是个分页的过程
// 1.计算一共有多少个工作表
double sheetNum = Math.ceil(size / new Integer(sheetSize).doubleValue());
// 2.创建相应的工作表，并向其中填充数据
WritableSheet sheet = wwb.createSheet(sheetName, 0);
fillVeticalSheet(sheet, list, title, startRow, 0);
// setColumnAutoSize(sheet, 2);
wwb.write();
wwb.close();
} catch (WriteException e) {
e.printStackTrace();
logger.error(e.getMessage());
} catch (IOException e) {
e.printStackTrace();
logger.error(e.getMessage());
} catch (Exception e) {
e.printStackTrace();
logger.error(e.getMessage());
}
}
/**
* 以流的形式导出excel
* @param list 要转换的列表
* @param title 转换列表的标题
* @param sheetName 工作表名称
* @param fileName excel文件名称
* @param sheetSize 工作表的大小
* @param startRow 从哪行开始输出
* @param request 请求
* @param response 响应
* @return
* @throws Exception
*/
public static <T> ByteArrayOutputStream listToExcelReturnOut(List<T> list,LinkedHashMap<String, String> title,String sheetName, String fileName,int sheetSize,int startRow,HttpServletRequest request,HttpServletResponse response) throws Exception{
if (list == null || list.size() == 0) {
// throw new Exception("列表中没有任何数据");
}
if (sheetSize > MAX_SHEET_SIZE || sheetSize < 1) {
sheetSize = MAX_SHEET_SIZE;
}
// OutputStream outputStream = response.getOutputStream();
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
setResp(request, response, fileName);
try {
WritableWorkbook wwb = Workbook.createWorkbook(outputStream);
int size = 0;
// 因为2003的Excel一个工作表最多可以有65536条记录，除去列头剩下65530条
// 所以如果记录太多，需要放到多个工作表中，其实就是个分页的过程
// 1.计算一共有多少个工作表
double sheetNum = Math.ceil(size / new Integer(sheetSize).doubleValue());
// 2.创建相应的工作表，并向其中填充数据
WritableSheet sheet = wwb.createSheet(sheetName, 0);
fillVeticalSheet(sheet, list, title, startRow, 0);
// setColumnAutoSize(sheet, 2);
wwb.write();
wwb.close();
} catch (WriteException e) {
e.printStackTrace();
logger.error(e.getMessage());
} catch (IOException e) {
e.printStackTrace();
logger.error(e.getMessage());
} catch (Exception e) {
e.printStackTrace();
logger.error(e.getMessage());
}
return outputStream;
}
/**
* 填充竖直方向的sheet
* @param sheet
* @param data
* @param title
* @param row
* @param col
* @throws Exception
*/
public static <T> void fillVeticalSheet(WritableSheet sheet, List<T> data, LinkedHashMap<String, String> title, int row,
int col) throws Exception {
if (null == data || null == title) {
return;
}
// 定义存放英文字段名和中文字段名的数组
String[] enFields = new String[title.size()];
String[] cnFields = new String[title.size()];
// 填充数组
int count = 0;
for (Entry<String, String> entry : title.entrySet()) {
enFields[count] = entry.getKey();
cnFields[count] = entry.getValue();
count++;
}
// 填充表头
for (int i = 0; i < cnFields.length; i++) {
String cnValue=cnFields[i];
Label label = new Label(col + i, row,cnValue);
sheet.addCell(label);
sheet.setColumnView(col+i, cnValue.getBytes().length);//一个中文占2个字节，经过调试+4后效果比较理想
}
// 填充内容
int rowNo = row + 1;
int colNo = col;
for (int i = 0; i < data.size(); i++) {
T item = data.get(i);
for (int j = 0; j < enFields.length; j++) {
Object objValue = getFieldValueByNameSequence(enFields[j], item);
WritableCell cell = getWritableCell(colNo+j, rowNo, objValue);
sheet.addCell(cell);
}
rowNo++;
}
}
public static WritableCell getWritableCell(int column, int row, Object object){
if (object == null) {
return new Label(column, row, "");
}
String tempObj = object.toString();
if (object instanceof Integer) {
if (tempObj.equals("0")) {
return new Number(column, row, 0);
}
return new Number(column, row, Double.valueOf(tempObj),INTER_CELL_FORMAT);
}
if (object instanceof Double) {
return new Number(column, row, Double.valueOf(tempObj),DOUBLE_CELL_FORMAT);
}
if (object instanceof BigDecimal) {
if (tempObj.indexOf(".00") > 0) {
tempObj = tempObj.substring(0, tempObj.indexOf("."));
return tempObj.equals("0") ? new Number(column, row, 0)
: new Number(column, row, Double.valueOf(tempObj),INTER_CELL_FORMAT);
}
return new Number(column, row, Double.valueOf(tempObj),DOUBLE_CELL_FORMAT);
}
return new Label(column, row, tempObj);
}
/**
* 根据带路径或不带路径的属性名获取属性值,即接受简单属性名，如userName等，又接受带路径的属性名，如student.department.
* name等
*
* @param fieldNameSequence
* 带路径的属性名或简单属性名
* @param o
* 对象
* @return 属性值
* @throws Exception
* 异常
*/
public static Object getFieldValueByNameSequence(String fieldNameSequence, Object o) throws Exception {
Object value = null;
// 将fieldNameSequence进行拆分
String[] attributes = fieldNameSequence.split("\\.");
if (attributes.length == 1) {
value = PropertyUtils.getProperty(o, fieldNameSequence);
} else {
// 根据属性名获取属性对象
Object fieldObj = PropertyUtils.getProperty(o, attributes[0]);
String subFieldNameSequence = fieldNameSequence.substring(fieldNameSequence.indexOf(".") + 1);
value = getFieldValueByNameSequence(subFieldNameSequence, fieldObj);
}
if (value instanceof Date) {
value = new SimpleDateFormat("yyyy-MM-dd").format(
value).toString();
}
// if (value.toString().endsWith(".0000")) {
// String txt =value.toString();
// value = txt.substring(0, txt.lastIndexOf("."));
// }
return value;
}
/**
* 根据字段名获取字段
*
* @param fieldName
* 字段名
* @param clazz
* 包含该字段的类
* @return 字段
*/
public static Field getFieldByName(String fieldName, Class<?> clazz) {
// 拿到本类的所有字段
Field[] selfFields = clazz.getDeclaredFields();
// 如果本类中存在该字段，则返回
for (Field field : selfFields) {
if (field.getName().equals(fieldName)) {
return field;
}
}
// 否则，查看父类中是否存在此字段，如果有则返回
Class<?> superClazz = clazz.getSuperclass();
if (superClazz != null && superClazz != Object.class) {
return getFieldByName(fieldName, superClazz);
}
// 如果本类和父类都没有，则返回空
return null;
}
public static <T> LinkedList<WritableCell> addCellInVertical(List<T> data, Map<String, String> dataTitle,
Map<String, WritableCellFormat> cellFormatMap, int rowIndex, int colIndex) {
Label label = new Label(1, 1, "中午");
LinkedList<WritableCell> cells = new LinkedList<>();
cells.add(label);
return cells;
}
/**
*
* @param fileName
* @param request
* @param response
*/
private static void setResp(HttpServletRequest request, HttpServletResponse response, String fileName) {
final String userAgent = request.getHeader("USER-AGENT");
response.reset();
// response.setContentType("application/vnd.ms-excel"); // 改成输出excel文件
response.setContentType("application/x-msdownload");
try {
String finalFileName = null;
if (StringUtils.contains(userAgent, "MSIE")) {// IE浏览器
finalFileName = URLEncoder.encode(fileName, "UTF8");
}
else if (StringUtils.contains(userAgent, "Mozilla")) {// google,火狐浏览器
finalFileName = new String(fileName.getBytes(), "ISO8859-1");
}
else {
finalFileName = URLEncoder.encode(fileName, "UTF8");// 其他浏览器
}
String dataStr = new SimpleDateFormat("yyyyMMddHHmmss").format(
new Date()).toString();
// 这里设置一下让浏览器弹出下载提示框，而不是直接在浏览器中打开
response.setHeader("Content-Disposition", "attachment; filename=" + finalFileName+"_"+dataStr + ".xls");
} catch (UnsupportedEncodingException e) {
}
}
/**
* 设置工作表自动列宽和首行加粗
*
* @param ws
* 要设置格式的工作表
* @param extraWith
* 额外的宽度
*/
public static void setColumnAutoSize(WritableSheet ws, int extraWith) {
// 获取本列的最宽单元格的宽度
for (int i = 0; i < ws.getColumns(); i++) {
int colWith = 0;
for (int j = 0; j < ws.getRows(); j++) {
String content = ws.getCell(i, j).getContents().toString();
int cellWith = content.length();
if (colWith < cellWith) {
colWith = cellWith;
}
}
// 设置单元格的宽度为最宽宽度+额外宽度
ws.setColumnView(i, colWith + extraWith);
}
}
// public static NumberFormat getNumFormate(Object object){
// NumberFormat numberFormat = new NumberFormat("");
//
// }
public static void testExport(HttpServletRequest request,HttpServletResponse response) {
try {
setResp(request,response,"test");
OutputStream outputStream = response.getOutputStream();
WritableWorkbook wwb = Workbook.createWorkbook(outputStream);
WritableSheet sheet = wwb.createSheet("test", 0);
Label label = new Label(1, 1, "中午");
sheet.addCell(label);
wwb.write();
wwb.close();
} catch (WriteException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
邮件工具类
package com.yx.mail;
import java.io.ByteArrayInputStream;
import java.security.GeneralSecurityException;
import java.util.Properties;
import javax.activation.DataHandler;
import javax.activation.DataSource;
import javax.mail.Address;
import javax.mail.Authenticator;
import javax.mail.Message;
import javax.mail.Part;
import javax.mail.PasswordAuthentication;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.AddressException;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;
import javax.mail.internet.MimeUtility;
import javax.mail.internet.MimeMessage.RecipientType;
import javax.mail.util.ByteArrayDataSource;
import com.sun.mail.util.MailSSLSocketFactory;
public class BaseMail {
/**
* 发送邮件
*
* @param to
* 邮件收件人地址
* @param copyEmail
* 邮件抄送地址
* @param title
* 邮件标题
* @param text
* 内容
* @param fileDs
* 附件流
*/
public void sendMsgFileDs(String to, String copyEmail, String title, String text,ByteArrayInputStream inputstream) {
Session session = assembleSession();
Message msg=new MimeMessage(session);
try {
msg.setFrom(new InternetAddress(MailConfig.sendAddress));
msg.setSubject(title);
msg.setRecipients(RecipientType.TO, acceptAddressList(to,copyEmail));
MimeBodyPart contentPart=(MimeBodyPart) createContent(text,inputstream);//参数为正文内容和附件流
// MimeBodyPart stream = new MimeBodyPart();
// MimeBodyPart part=(MimeBodyPart) createAttachment("D:/test/1.jpg");//可增加多个附件
MimeMultipart mime=new MimeMultipart("mixed");
mime.addBodyPart(contentPart);
// mime.addBodyPart(part);//可增加多个附件
msg.setContent(mime);
Transport.send(msg);
} catch (Exception e) {
e.printStackTrace();
}
}
public Address[] acceptAddressList(String acceptAddress,String acceptAddressOther) {
// 创建邮件的接收者地址，并设置到邮件消息中
Address[] tos = null;
String [] copyEmail= acceptAddressOther.split(",");
try {
if (copyEmail != null) {
// 为每个邮件接收者创建一个地址
tos = new InternetAddress[copyEmail.length + 1];
tos[0] = new InternetAddress(acceptAddress);
for (int i = 0; i < copyEmail.length; i++) {
tos[i + 1] = new InternetAddress(copyEmail[i]);
}
} else {
tos = new InternetAddress[1];
tos[0] = new InternetAddress(acceptAddress);
}
} catch (AddressException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return tos;
}
public Session assembleSession() {
Session session = null;
Properties props=new Properties();
props.setProperty("mail.smtp.auth", "true");
props.setProperty("mail.transport.protocol", "smtp");
props.setProperty("mail.smtp.port", MailConfig.mailSmtpPort);
props.setProperty("mail.smtp.host", MailConfig.mailSmtpHost);//邮件服务器
//开启安全协议
MailSSLSocketFactory sf = null;
try {
sf = new MailSSLSocketFactory();
sf.setTrustAllHosts(true);
} catch (GeneralSecurityException e1) {
e1.printStackTrace();
}
props.put("mail.smtp.ssl.socketFactory", sf);
props.put("mail.smtp.ssl.enable", "true");
session = Session.getDefaultInstance(props, new MyAuthenricator(MailConfig.sendAddress,MailConfig.sendPassWord));
return session;
}
static Part createContent(String content,ByteArrayInputStream inputstream){
MimeBodyPart contentPart=null;
try {
contentPart=new MimeBodyPart();
MimeMultipart contentMultipart=new MimeMultipart("related");
MimeBodyPart htmlPart=new MimeBodyPart();
htmlPart.setContent(content, "text/html;charset=gbk");
contentMultipart.addBodyPart(htmlPart);
//附件部分
MimeBodyPart excelBodyPart=new MimeBodyPart();
DataSource dataSource=new ByteArrayDataSource(inputstream, "application/excel");
DataHandler dataHandler=new DataHandler(dataSource);
excelBodyPart.setDataHandler(dataHandler);
excelBodyPart.setFileName(MimeUtility.encodeText("通知.xls"));
// excelBodyPart.setDataHandler(new DataHandler(fileDs));
// excelBodyPart.setFileName(fileDs.getName());
// excelBodyPart.setContentID("excel");
contentMultipart.addBodyPart(excelBodyPart);
contentPart.setContent(contentMultipart);
} catch (Exception e) {
e.printStackTrace();
}
return contentPart;
}
//用户名密码验证，需要实现抽象类Authenticator的抽象方法PasswordAuthentication
static class MyAuthenricator extends Authenticator{
String u = null;
String p = null;
public MyAuthenricator(String u,String p){
this.u=u;
this.p=p;
}
@Override
protected PasswordAuthentication getPasswordAuthentication() {
return new PasswordAuthentication(u,p);
}
}
}
测试类：
package com.yx.mail;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
@Controller
@Scope(value = "prototype")
public class TestMain extends BaseController{
private Logger logger = LogManager.getLogger(TestMain.class);
@Autowired
private SendEmailService sendEmailService;
@RequestMapping(value = "/mail", method = RequestMethod.GET)
@ResponseBody
public String sendEmail() {
List<Order> rechecklist = new ArrayList();
Order Order1 = new Order();
Order1.setOrderNo("1");
Order1.setProvinceId("黑龙江");
Order Order2 = new Order();
Order2.setOrderNo("2");
Order2.setProvinceId("吉林");
Order Order3 = new Order();
Order3.setOrderNo("3");
Order3.setProvinceId("辽宁");
Order Order4 = new Order();
Order4.setOrderNo("4");
Order4.setProvinceId("福建");
rechecklist.add(Order1);rechecklist.add(Order2);rechecklist.add(Order3);rechecklist.add(Order4);
LinkedHashMap<String, String> recheckMap = new LinkedHashMap<>();
recheckMap.put("provinceId", "省份");
recheckMap.put("orderNo", "订单号");
try {
ByteArrayOutputStream baos = JxlUtil.listToExcelReturnOut(rechecklist, recheckMap, "test", "testexport", 0, 0, request,
response);
ByteArrayInputStream InputStream = new ByteArrayInputStream(baos.toByteArray());
String email = MailConfig.acceptAddress;//接收人
String copyEmail = MailConfig.acceptAddressOther;//抄送多人以逗号分隔
String title = "XX通知";
String context = "当前系统存在"+rechecklist.size()+"条需要处理的订单。详情请留意附件。";
sendEmailService.sendMsgFileDs(email,copyEmail,title,context,InputStream);
// file.deleteOnExit();
} catch (Exception e) {
logger.info(e.getMessage());
logger.info("【MailNoticeController】sendEmail 异常");
}
return null;
}
}
876
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
