Easyexcel生成excel直接发送邮件

依赖：

         <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-mail</artifactId>
            <version>2.2.10.RELEASE</version>
        </dependency>


         <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>2.2.7</version>
          </dependency>

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;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;

@Service
@Slf4j
public class MailService {
    // 发送人的邮箱,QQ邮箱可以自己发给自己
    @Value("${mail.username:123@qq.com}")
    private String account;
    // 发送人的邮箱smtp授权码
    @Value("${mail.password:ftuvgewercgjg}")
    private String pwd;
    // 发送人的邮箱smtp授权码
    @Value("${mail.host:smtp.qq.com}")
    private String host;

    /**
     * 发送邮件
     *
     * @param to    邮件收件人地址
     * @param title 邮件标题
     * @param text  内容
     * @param text  附件标题
     * @param
     */
  
    public void sendMsgFileDs(String to, String title, String text, String affixName, ByteArrayInputStream inputstream) {
        Session session = assembleSession();
        Message msg = new MimeMessage(session);
        try {
            msg.setFrom(new InternetAddress(account));
            msg.setSubject(title);
            msg.setRecipients(RecipientType.TO, acceptAddressList(to));
            //参数为正文内容和附件流
            MimeBodyPart contentPart = (MimeBodyPart) createContent(text, inputstream, affixName);
            MimeMultipart mime = new MimeMultipart("mixed");
            mime.addBodyPart(contentPart);
            msg.setContent(mime);
            Transport.send(msg);
        } catch (Exception e) {
            log.error("sendMsgFileDs异常", e);
        }
    }

    public Address[] acceptAddressList(String acceptAddress) {
        // 创建邮件的接收者地址，并设置到邮件消息中
        Address[] tos = null;
        try {
            tos = new InternetAddress[1];
            tos[0] = new InternetAddress(acceptAddress);
        } catch (AddressException e) {
            log.error("acceptAddressList异常", e);
        }
        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", "465");
        //邮件服务器
        props.setProperty("mail.smtp.host", host);
        //开启安全协议
        MailSSLSocketFactory sf = null;
        try {
            sf = new MailSSLSocketFactory();
            sf.setTrustAllHosts(true);
        } catch (GeneralSecurityException e) {
            log.error("GeneralSecurityException异常", e);
        }
        props.put("mail.smtp.ssl.socketFactory", sf);
        props.put("mail.smtp.ssl.enable", "true");
        session = Session.getDefaultInstance(props, new MyAuthenricator(account, pwd));
        return session;
    }

    static Part createContent(String content, ByteArrayInputStream inputstream, String affixName) {
        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(affixName));
            contentMultipart.addBodyPart(excelBodyPart);
            contentPart.setContent(contentMultipart);
        } catch (Exception e) {
            log.error("createContent异常", e);
        }
        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);
        }
    }

}

测试：

import cn.hutool.core.date.DateUtil;
import com.alibaba.excel.EasyExcel;
import order.meal.admin.job.MailService;
import order.meal.admin.model.vo.req.OrderReqVO;
import order.meal.admin.model.vo.resp.OrderExcelRespVO;
import order.meal.admin.service.OrderService;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.Date;
import java.util.List;


public class EmailTest extends BaseTest {

    @Autowired
    private MailService mailService;
    @Autowired
    private OrderService orderService;

    // 邮件标题
    @Value("${email.title:订餐数据}")
    private String title;

    /* *
     * description: 结合EasyExcel；但一次只能发送一个sheet;
     *
     */
    @Test
    public void sendMailWithExcel() throws IOException {
        String fileName = new StringBuilder(DateUtil.formatDate(new Date())).append("_").append(title).toString();
        String affixName = new StringBuilder(DateUtil.formatDate(new Date())).append(".xlsx").toString();
        ByteArrayOutputStream outputStream = null;
        ByteArrayInputStream inputStream = null;
        try {
            OrderReqVO orderReqVO = new OrderReqVO();
            orderReqVO.setPageNo(0);
            orderReqVO.setState(3L);
            List<OrderExcelRespVO> list = orderService.exportList(orderReqVO);
            outputStream = new ByteArrayOutputStream(1000);
            EasyExcel.write(outputStream, OrderExcelRespVO.class).
                    sheet("订单列表").doWrite(list);
            inputStream = new ByteArrayInputStream(outputStream.toByteArray());
            //注意：附件需要带后缀，例如上面例子，附件名称可以写 测试.xlsx
            mailService.sendMsgFileDs("1635597903@qq.com",
                    fileName,
                    fileName,
                    affixName,
                    inputStream);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (inputStream != null) {
                inputStream.close();
            }
            if (outputStream != null) {
                outputStream.close();
            }
        }
    }

    @Test
    public void sendMailWithExcels() throws IOException {
        HSSFWorkbook wb = null;
        ByteArrayOutputStream outputStream = null;
        ByteArrayInputStream inputStream = null;
        try {
            String[] headers = {"列名1", "列名2", "列名3"};
            // 声明一个工作薄
            wb = new HSSFWorkbook();
            // 生成一个表格
            HSSFSheet orders = wb.createSheet();
            HSSFSheet users = wb.createSheet();
            wb.setSheetName(0, "订单数据");
            wb.setSheetName(1, "用户数据");
            extracted(headers, orders);
            extracted(headers, users);

            outputStream = new ByteArrayOutputStream(1000);
            wb.write(outputStream);
            inputStream = new ByteArrayInputStream(outputStream.toByteArray());
            //注意：附件需要带后缀，例如上面例子，附件名称可以写 测试.xlsx
            mailService.sendMsgFileDs("1635597903@qq.com",
                    "attachmentMail subject",
                    "I have an attachment",
                    "测试.xls",
                    inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (wb != null) {
                wb.close();
            }
            if (outputStream != null) {
                outputStream.close();
            }
            if (inputStream != null) {
                inputStream.close();
            }
        }
    }

    private void extracted(String[] headers, HSSFSheet sheet) {
        HSSFRow row = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(headers[i]);
        }
        int rowIndex = 1;
        for (int j = 0; j < 3; j++) {
            row = sheet.createRow(rowIndex);
            rowIndex++;
            HSSFCell cell1 = row.createCell(0);
            cell1.setCellValue(j);
            cell1 = row.createCell(1);
            cell1.setCellValue(j + 1);
            cell1 = row.createCell(2);
            cell1.setCellValue(j + 2);
        }
        for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }
    }
}


/**  指定当前生效的配置文件( active profile)，如果是 appplication-dev.yml 则 dev   **/
@ActiveProfiles("dev")
@SpringBootTest(classes = AdminStarter.class, webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
public class BaseTest {

 
    @Test
    public void test(){
    }
}

@Setter
@Getter
public class OrderExcelRespVO implements Serializable {

    /* *
     * 订单号
     */
    @ExcelProperty(value = "订单号")
    private long orderNo;

    @ExcelProperty(value = "手机号")
    private String phone;

    @ExcelProperty(value = "用户名称")
    private String userName;

    /* *
     * 商家名称
     */
    @ExcelProperty(value = "商家名称")
    private String merchantName;

    /* *
     * 商品列表
     */
    @ExcelProperty(value = "商品")
    String materialList;

    /* *
     *  1:预定 2:取餐 3:取消 4:爽约
     */
    @ExcelProperty(value = "订单状态")
    private String state;

    /* *
     * 订餐时间
     */
    @ExcelProperty(value = "订餐时间")
    private String createTime;
}

 

转自：

java 发邮件带excel附件，以流的形式发送附件，不生成excel文件

  最简单的 springboot 发送邮件,使用thymeleaf模板