Java使用easyExcel生成excel文件直接写入邮件附件并发送_easyexcel生成分批成附件发送邮箱

1.引入pom依赖

<!-- 邮箱 -->
  <dependency>
     <groupId>org.apache.commons</groupId>
     <artifactId>commons-email</artifactId>
     <version>1.4</version>
    </dependency>
  <!-- easyexcel-->
  <dependency>
     <groupId>cn.afterturn</groupId>
     <artifactId>easypoi-spring-boot-starter</artifactId>
     <version>4.0.0</version>
  </dependency>
  <dependency>
     <groupId>org.apache.poi</groupId>
     <artifactId>poi</artifactId>
     <version>4.1.2</version>
   </dependency>
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

1.创建邮箱是实体类`

import lombok.Data;
import java.util.List;

/**
 * 创建邮箱实体类
 *
 * @author ZL
 */
@Data
public class CreateExcelSendEmailVo<T> {
    /**
     * 生成excel的数据
     */
    private List<T>dataList;

    /**
     * excel的表头
     */
    private List<String> tableHeadList;

    /**
     * 邮件的标题
     */
    private String emailTitle;

    /**
     * 邮件内容
     */
    private String emailContent;

    /**
     * 邮件收件人邮箱，支持多个收件人邮箱
     */
    private List<String> acceptAddressList;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

2.工具类（这里是两个工具类，别看错了）

import com.alibaba.excel.metadata.BaseRowModel;
import org.springframework.stereotype.Component;

/**
 * @author ZL
 */
@Component
public class ExcelFactory<T extends BaseRowModel> {
 
    public ExportExcelUtil<T> createExportExcel() {
        return new ExportExcelUtil<>();
    }
}



import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.metadata.Table;
import com.alibaba.excel.support.ExcelTypeEnum;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * Excel创建工具类
 *
 * @author ZL
 */
@Component
@Slf4j
public class ExportExcelUtil<T extends BaseRowModel> {
    public ExportExcelUtil() {
    }

    /**
     * 创建excel表
     *
     * @param out
     * @param data
     * @param tableHeadList
     * @throws IOException
     */
    public void createExcel(ByteArrayOutputStream out, List<T> data, List<String> tableHeadList) throws IOException {
        try {
            List<List<String>> head = getExcelHead(tableHeadList);

            ExcelWriter writer = new ExcelWriter(null, out, ExcelTypeEnum.XLSX, true);
            Table table = new Table(0);
            table.setHead(head);
            Sheet sheet1 = new Sheet(1, 0);
            sheet1.setAutoWidth(true);
            sheet1.setSheetName("sheet1");
            writer.write(data, sheet1, table);
            writer.finish();
            out.flush();
        } finally {
            if (out != null) {
                out.close();
            }
        }
    }

    /**
     * 创建excel表头
     *
     * @param tableHeadList
     * @return
     */
    private List<List<String>> getExcelHead(List<String> tableHeadList) {
        List<List<String>> head = new ArrayList<List<String>>();
        for (String s : tableHeadList) {
            List<String> column = new ArrayList<String>();
            column.add(s);
            head.add(column);
        }
        return head;
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85

3.创建邮箱业务类

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import javax.activation.DataHandler;
import javax.activation.DataSource;
import javax.mail.*;
import javax.mail.internet.*;
import javax.mail.util.ByteArrayDataSource;
import java.io.ByteArrayInputStream;
import java.util.List;
import java.util.Properties;

/**
 * 创建邮箱业务类
 *
 * @author ZL
 */
@Component
@Slf4j
public class EmailService {
    @Value("${email.userName:null}")
    private String userName;
    @Value("${email.passWord:null}")
    private String passWord;


    /**
     * 消息发送方法
     * @param acceptAddressList
     * @param title
     * @param text
     * @param affixName
     * @param inputstream
     */
    public void sendMsgFileDs(List<String> acceptAddressList, String title, String text, String affixName, ByteArrayInputStream inputstream) {
        Session session = assembleSession();
        Message msg = new MimeMessage(session);
        try {
            msg.setFrom(new InternetAddress(userName));
            msg.setSubject(title);

            Address[] addressArr = acceptAddressList(acceptAddressList);
            msg.setRecipients(Message.RecipientType.TO, addressArr);
            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("创建消息失败,{}",e);
        }
    }

    /**
     * 创建邮件消息接收者
     *
     * @param acceptAddressList
     * @return
     */
    public Address[] acceptAddressList(List<String> acceptAddressList) {
        Address[] tos = new InternetAddress[acceptAddressList.size()];
        try {
            for (int i = 0; i < acceptAddressList.size(); i++) {
                tos[i] = new InternetAddress(acceptAddressList.get(i));
            }
        } catch (AddressException e) {
            log.error("创建邮件消息接收者失败,{}",e);
        }
        return tos;
    }

    /**
     * 以QQ邮箱为例，邮箱配置类
     *
     * @return
     */
    public Session assembleSession() {

        String host = "host";
        String mailStoreType = "smtp";
        String popPort = "587";

        final Properties props = new Properties();

        props.put("mail.smtp.auth", "true");
        props.put("mail.smtp.host", host);
        props.put("mail.store.protocol", mailStoreType);
        props.put("mail.smtp.port", popPort);
        //开启SSL
        props.put("mail.smtp.starttls.enable", "true");
        props.put("mail.smtp.socketFactory.port", popPort);
        props.put("mail.smtp.socketFactory.fallback", "false");

        Session session = Session.getDefaultInstance(props, new MyAuthenricator(userName, passWord));
        return session;
    }

    /**
     * 输出的excel文件配置
     *
     * @param content
     * @param inputstream
     * @param affixName
     * @return
     */
    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) {
            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);
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147

4.邮件发送工具类

import com.alibaba.excel.metadata.BaseRowModel;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.util.List;
import java.util.Random;

/**
 * 邮件发送工具类
 *
 * @author ZL
 */
@Component
@Slf4j
public class EmailUtil<T extends BaseRowModel> {
    @Autowired
    private ExcelFactory excelFactory;

    @Autowired
    private EmailService emailService;


    /**
     * @Author ZL
     * @Describetion 发送邮件的入口方法
     */
    public void sendEmail(CreateExcelSendEmailVo vo) {
        try {
            log.info("开始发送邮件: {}", vo);
            generateExcelSendEmail(vo.getDataList(), vo.getTableHeadList(), vo.getAcceptAddressList(), vo.getEmailTitle(), vo.getEmailContent());
            log.info("发送邮件成功");
        } catch (Exception e) {
            log.info("发送邮件失败", e);
        }

    }

    /**
     * @Author ZL
     * @Describetion 生成excel并发送邮件
     */
    public void generateExcelSendEmail(List<T> dataList, List<String> tableHeadList, List<String> acceptAddressList, String emailTitle, String content) {
        try {
            ByteArrayOutputStream out = new ByteArrayOutputStream();
            String fileName = "email_" + new Random().nextInt(10000) + System.currentTimeMillis() + ".xlsx";
            //生成excel
            excelFactory.createExportExcel().createExcel(out, dataList, tableHeadList);
            // 发送邮件
            emailService.sendMsgFileDs(acceptAddressList, emailTitle, content, fileName, new ByteArrayInputStream(out.toByteArray()));
        } catch (Exception e) {
            log.error("发送邮件时出错：{}", e);
        }
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

4.测试（以上代码可直接使用）

/**
     * 测试
     */
    @Test
    public void test() {
        CreateExcelSendEmailVo vo = new CreateExcelSendEmailVo<>();
        // 1.构建导出数据内容，这里我是查询自己的用户表
        List<Usre> usreList = userService.getUserList;
        vo.setDataList(usreList);
        // 2.设置表头
        List<String> headList = new ArrayList<>();
        headList.add("姓名");
        headList.add("年龄");
        vo.setTableHeadList(headList);
        // 3.设置email的title
        vo.setEmailTitle("测试");
        //4.设置email的内容
        vo.setEmailContent("学生表");
        // 5.设置收件人
        List<String> acceptAddressList = new ArrayList<>();
        acceptAddressList.add("xxx@qq.com");
        vo.setAcceptAddressList(acceptAddressList);
        // 6.发送邮件
        emailUtil.sendEmail(vo);
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25