公司需求：根据业务需要定时发送邮件，并且邮件需要有excel附件，附件内容为查询到的数据。
开发思路：由于服务器环境本身空间有限，而且不确定环境中读写权限，生成excel文件比较浪费资源，出现异常时可能导致删除临时文件失败。因此生成excel文件时不生成实际文件，直接获取到excel写入流，并且将流直接写入到邮件附件中。
查询了很多网上资料以及java邮件API，最终得以实现，分享下供大家参考，互相学习！
转载请标明出处：/yixin605691235/article/details/82429156
详细代码：/download/yixin605691235/10648074
核心代码如下：
配置邮箱服务器端口及发送人接收人
mail.smtp.host=smtp.mail.smtp.port=465mail.send.address=xxx@xxx.xxmail.send.password=xxxxxxmail.accept.address=qqqqqq@mail.accept.address.other=qqqqqq@,qqqqqq@
生成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 .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 mons.beanutils.PropertyUtils;import mons.lang3.StringUtils;import jxl.Workbook;import mon.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;// 因为的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;// 因为的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 blocke.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的抽象方法PasswordAuthenticationstatic 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)@ResponseBodypublic 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;}}
