邮件发送自定义Excel
需求：
给定Excel模板，将数据填充到模板，并将生成的Excel文件发送邮件。
1
解决方案：
采用EasyExcel填充模板文件。
采用spring提供的发送邮件接口JavaMailSender实现邮件功能。
1
2
2
# 1 邮件功能
# 1.1 添加邮件相关依赖包
<!-- 邮件 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-mail</artifactId>
</dependency>
1
2
3
4
5
2
3
4
5
# 1.2 邮件配置
spring:
mail:
host: smtphz.qiye.163.com
username: xxxxxx
password: xxxxxx
port: 465
protocol: smtps
default-encoding: UTF-8
properties:
mail:
smtp:
ssl:
enable: true
starttls:
enable: true
required: true
connectiontimeout: 5000
timeout: 3000
writetimeout: 5000
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 1.3 发送邮件工具类
package com.xxx.impl.util;
import cn.hutool.core.util.ObjectUtil;
import com.xxx.vo.order.EmailAttachment;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.core.io.InputStreamSource;
import org.springframework.mail.javamail.JavaMailSender;
import org.springframework.mail.javamail.MimeMessageHelper;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import javax.annotation.Resource;
import javax.mail.MessagingException;
import javax.mail.internet.MimeMessage;
import java.io.File;
import java.util.List;
@Service
@Slf4j
public class EmailUtil {
@Resource
private JavaMailSender javaMailSender;
@Value("${spring.mail.username}")
private String from;
/**
* 发送邮件
*
* @param title 邮件标题
* @param msg 消息内容
* @param appendix 附件
* @param receivers 接收人
*/
public void sendEmail(String title, String msg, File appendix, String... receivers) {
try {
//没有附件 或 没有收件人，不发送邮件
if (ObjectUtil.isEmpty(appendix) || ObjectUtil.isEmpty(receivers)) {
return;
}
MimeMessage mimeMessage = javaMailSender.createMimeMessage();
MimeMessageHelper messageHelper = new MimeMessageHelper(mimeMessage, true);
messageHelper.setFrom(from);
messageHelper.setTo(receivers);
messageHelper.setSubject(title);
messageHelper.setText(msg);
messageHelper.addAttachment(appendix.getName(), appendix);
javaMailSender.send(mimeMessage);
} catch (MessagingException e) {
e.printStackTrace();
}
}
/**
* 发送邮件
*
* @param title 邮件标题
* @param msg 消息内容
* @param appendix 附件
* @param receivers 接收人
*/
public void sendEmailByIns(String title, String msg, String fileName, InputStreamSource appendix, String... receivers) {
try {
MimeMessage mimeMessage = javaMailSender.createMimeMessage();
MimeMessageHelper messageHelper = new MimeMessageHelper(mimeMessage, true);
messageHelper.setFrom(from);
messageHelper.setTo(receivers);
messageHelper.setSubject(title);
messageHelper.setText(msg, true);
messageHelper.addAttachment(fileName, appendix);
javaMailSender.send(mimeMessage);
} catch (MessagingException e) {
e.printStackTrace();
}
}
/**
* 发送邮件
*
* @param title 邮件标题
* @param msg 消息内容
* @param attachmentList 附件
* @param receivers 接收人
*/
public void sendEmailByIns(String title, String msg, List<EmailAttachment> attachmentList, String... receivers) {
try {
MimeMessage mimeMessage = javaMailSender.createMimeMessage();
MimeMessageHelper messageHelper = new MimeMessageHelper(mimeMessage, true);
messageHelper.setFrom(from);
messageHelper.setTo(receivers);
messageHelper.setSubject(title);
messageHelper.setText(msg, true);
if (!CollectionUtils.isEmpty(attachmentList)) {
for (EmailAttachment attachment : attachmentList) {
messageHelper.addAttachment(attachment.getFileName(), attachment.getAppendix());
}
}
javaMailSender.send(mimeMessage);
} catch (MessagingException e) {
e.printStackTrace();
}
}
}
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
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
package com.xxx.vo.order;
import lombok.Data;
import org.springframework.core.io.InputStreamSource;
@Data
public class EmailAttachment {
private String fileName;
private InputStreamSource appendix;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
# 2 Excel功能
提示
EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。
github地址：https://github.com/alibaba/easyexcel (opens new window)
使用说明：https://easyexcel.opensource.alibaba.com (opens new window)
# 2.1 添加EasyExcel相关依赖包
<!-- Excel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
1
2
3
4
5
6
2
3
4
5
6
# 2.2 获取Excel模板并填充数据
模板文件存放在项目resources目录下
例：DN-A模板内容
public class Test {
public DataResult sendMail(TestMailInfoDto testMailInfoDto) {
log.info("sendMail testMailInfoDto = {}", JSON.toJSONString(testMailInfoDto));
try {
TestDnVO testDnVO = basicsTestMapper.queryTestDn(testMailInfoDto.getBillcode());
List<TestDnDetailVO> testDnDetailVOList = basicsTestMapper.queryTestDnDetail(testMailInfoDto.getBillcode());
List<TestImeiVO> testImeiVOList = basicsTestMapper.queryTestImeiList(testMailInfoDto.getBillcode());
List<EmailAttachment> attachmentList = new ArrayList<>();
EmailAttachment dnAttachment = getDnAttachment(testDnVO, testDnDetailVOList, getDNTemplate(testMailInfoDto));
if (dnAttachment != null) {
attachmentList.add(dnAttachment);
}
EmailAttachment imeiAttachment = getImeiAttachment(testImeiVOList, getImeiTemplate(testMailInfoDto));
if (imeiAttachment != null) {
attachmentList.add(imeiAttachment);
}
emailUtil.sendEmailByIns(testMailInfoDto.getTitle(), testMailInfoDto.getMsg(), attachmentList, testMailInfoDto.getReceivers());
} catch (Exception e) {
log.error("sendMail Exception", e);
return error(ResultCodeEnum.SYSTEM_BUSY.getCode(), e.getMessage());
}
return success();
}
private EmailAttachment getDNTemplate(TestMailInfoDto testMailInfoDto) {
EmailAttachment attachment = null;
ClassPathResource classPathResource = null;
switch (testMailInfoDto.getDmTemplete()) {
case "DN-A":
classPathResource = new ClassPathResource("excel" + File.separator + "DN-A.xlsx");
break;
case "DN-B":
classPathResource = new ClassPathResource("excel" + File.separator + "DN-B.xlsx");
break;
case "DN-C":
classPathResource = new ClassPathResource("excel" + File.separator + "DN-C.xlsx");
break;
}
if (classPathResource != null) {
attachment = new EmailAttachment();
attachment.setFileName(testMailInfoDto.getDmTemplete() + ".xlsx");
attachment.setAppendix(classPathResource);
}
return attachment;
}
private EmailAttachment getDnAttachment(TestDnVO testDnVO, List<TestDnDetailVO> testDnDetailVOList, EmailAttachment emailAttachment) throws Exception {
if (emailAttachment == null) {
return null;
}
ByteArrayOutputStream out = new ByteArrayOutputStream();
ExcelWriter excelWriter = null;
try {
excelWriter = EasyExcel.write(out).withTemplate(emailAttachment.getAppendix().getInputStream()).build();
WriteSheet writeSheet = EasyExcel.writerSheet().build();
//dn主单
//由于变量属性有一个个设置，直接通过反射的反射一次性设置
Map<String, Object> map = new HashMap<>();
Class clazz = testDnVO.getClass();
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
field.setAccessible(true);
map.put(field.getName(), field.get(testDnVO));
}
//dn明细
// 这里注意 入参用了forceNewRow 代表在写入list的时候不管list下面有没有空行 都会创建一行，然后下面的数据往后移动。默认 是false，会直接使用下一行，如果没有则创建。
// forceNewRow 如果设置了true,有个缺点 就是他会把所有的数据都放到内存了，所以慎用
// 简单的说 如果你的模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存 会很耗内存
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
excelWriter.fill(testDnDetailVOList, fillConfig, writeSheet);
//dn明细汇总
int totalPcs = 0;
int totalCtns = 0;
int totalPlts = 0;
BigDecimal totalGw = BigDecimal.ZERO;
BigDecimal totalCbm = BigDecimal.ZERO;
for (TestDnDetailVO detailVO : testDnDetailVOList) {
totalPcs += (detailVO.getPcs() == null ? 0 : detailVO.getPcs());
totalCtns += (detailVO.getCtns() == null ? 0 : detailVO.getCtns());
totalPlts += (detailVO.getPlts() == null ? 0 : detailVO.getPlts());
totalGw = totalGw.add(detailVO.getGw() == null ? BigDecimal.ZERO : detailVO.getGw());
totalCbm = totalCbm.add(detailVO.getGw() == null ? BigDecimal.ZERO : detailVO.getCbm());
}
map.put("totalPcs", totalPcs);
map.put("totalCtns", totalCtns);
map.put("totalPlts", totalPlts);
map.put("totalGw", totalGw.doubleValue());
map.put("totalCbm", totalCbm.doubleValue());
excelWriter.fill(map, writeSheet);
} finally {
if (excelWriter != null) {
excelWriter.finish();
}
}
emailAttachment.setAppendix(new ByteArrayResource(out.toByteArray()));
return emailAttachment;
}
}
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
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
# 3 发送Excel附件的邮件
