[移动开发]EasyPOI的Excel模板导出并且用邮件以附件方式发送Excel

一.EasyPOI依赖

<!-- easypoi的支持 -->
<dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-base</artifactId>
        <version>4.4.0</version>
    </dependency>
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-web</artifactId>
        <version>4.4.0</version>
    </dependency>
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-annotation</artifactId>
        <version>4.4.0</version>
    </dependency>
<!--发送邮件依赖-->
        <dependency>
            <groupId>javax.mail</groupId>
            <artifactId>javax.mail-api</artifactId>
            <version>1.6.1</version>
        </dependency>
        <dependency>
            <groupId>com.sun.mail</groupId>
            <artifactId>javax.mail</artifactId>
            <version>1.6.1</version>
        </dependency>

二.Excel模板
三.代码部分

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import com.cookie.entity.*;
import com.cookie.utils.EmailUtils;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.*;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

public class demo {
    public static void main(String[] args) throws IOException {
	    List<ExportData> list = new ArrayList<>();
        ExportData req = new ExportData();
        req.setOutAccount("123455");
        req.setOutAccountName("xxxx银行");
        req.setReceiptAccountWhereitis("xxx");
        req.setCurrency("人民币");
        req.setTradingHours("2022-12-22");
        req.setValueDate("2022-12-22");
        req.setTradeType("test");
        req.setAmount(new BigDecimal(1));
        req.setLoan("1");
        req.setBalance(new BigDecimal(2));
        req.setDealFlow("11112223333");
        req.setReceiptAccountName("Kata");
        req.setReceiptAccount("66666");
        req.setRecentDealFlow("000000");
        list.add(req);
        TemplateExportParams params = new TemplateExportParams("template/模板.xls");
        SimpleDateFormat simpleFormat = new SimpleDateFormat("yyyy-MM-dd");
        //累计借总笔数
        long total = list.stream().filter(exportData -> "1".equals(exportData.getLoan())).count();
        //累计借金额
        BigDecimal lendTotal = list.stream().filter(exportData -> "1".equals(exportData.getLoan())).map(ExportData::getAmount).reduce(BigDecimal::add).orElse(BigDecimal.ZERO);
        //累计贷金额
        BigDecimal loanTotal = list.stream().filter(exportData -> "2".equals(exportData.getLoan())).map(ExportData::getAmount).reduce(BigDecimal::add).orElse(BigDecimal.ZERO);
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("outAccount", list.get(0).getOutAccount());
        map.put("outAccountName", list.get(0).getOutAccountName());
        map.put("createDate", simpleFormat.format(new Date()));
        map.put("loanAmount", loanTotal);
        map.put("totalLend", total);
        map.put("balance", list.get(list.size() - 1).getBalance());
        map.put("lendAmount", lendTotal);
        map.put("startTime", list.get(0).getValueDate());
        map.put("endTime", list.get(0).getValueDate());
        List<Map<String, String>> listMap = new ArrayList<Map<String, String>>();
        for (ExportData exportData : list) {
            Map<String, String> lm = new HashMap<String, String>();
            lm.put("outAccount", exportData.getOutAccount());
            lm.put("outAccountName", exportData.getOutAccountName());
            lm.put("currency", "人民币");
            lm.put("tradingHours", exportData.getTradingHours());
            lm.put("valueDate", exportData.getValueDate());
            lm.put("tradeType", exportData.getTradeType());
            lm.put("amount", String.valueOf(exportData.getAmount()));
            lm.put("loan", "1".equals(exportData.getLoan()) ? "借" : "贷");
            lm.put("balance", String.valueOf(exportData.getBalance()));
            lm.put("dealFlow", exportData.getDealFlow());
            lm.put("receiptAccountName", exportData.getReceiptAccountName());
            lm.put("receiptAccount", exportData.getReceiptAccount());
            lm.put("receiptAccountWhereitis", exportData.getReceiptAccountWhereitis());
            listMap.add(lm);
        }
        map.put("maplist", listMap);
        Workbook workbook = ExcelExportUtil.exportExcel(params, map);
        ByteArrayOutputStream fos = new ByteArrayOutputStream();
        workbook.write(fos);
        ByteArrayInputStream inputStream = new ByteArrayInputStream(fos.toByteArray());
        EmailUtils.sendEmail("收件人邮箱","邮件主题","邮件内容",inputStream);
        fos.close();
    }
}

发邮件工具类1，以腾讯企业邮箱为例(需要邮箱密码)

import com.sun.mail.util.MailSSLSocketFactory;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
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.InputStream;
import java.security.GeneralSecurityException;
import java.util.Date;
import java.util.Properties;

/**
 * @author admin
 */
@Component
@Slf4j
public class EmailUtils {

    //腾讯企业邮箱,也可以换成别家的
    //协议
    private static final String PROTOCOL = "smtp";
    //地址
    private static final String HOST = "smtp.exmail.qq.com";
    //端口
    private static final String PORT = "465";
    //发送人邮箱
    private static final String ACCOUNT = "发送人邮箱";
    //发送人邮箱密码
    private static final String PASS = "发送人邮箱密码";
    //发件人别名，不需要设为空串或null
    private static final String PERSONAL = "发件人别名";

    // 权限认证
    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);
        }
    }

        /**
         * 发送邮件工具方法
         *
         * @param recipients 收件人
         * @param subject    主题
         * @param content    内容
         * @param is    附件
         * @return true/false 发送成功
         */
        public static boolean sendEmail(String recipients, String subject, String content, InputStream is) {
            Properties prop = new Properties();
            //协议
            prop.setProperty("mail.transport.protocol", PROTOCOL);
            //服务器
            prop.setProperty("mail.smtp.host", HOST);
            //端口
            prop.setProperty("mail.smtp.port", PORT);
            //使用smtp身份验证
            prop.setProperty("mail.smtp.auth", "true");
            //使用SSL，企业邮箱必需！
            //开启安全协议
            MailSSLSocketFactory mailSSLSocketFactory = null;
            try {
                mailSSLSocketFactory = new MailSSLSocketFactory();
                mailSSLSocketFactory.setTrustAllHosts(true);
            } catch (GeneralSecurityException e1) {
                e1.printStackTrace();
            }
            prop.put("mail.smtp.ssl.enable", "true");
            prop.put("mail.smtp.ssl.socketFactory", mailSSLSocketFactory);

            Session session = Session.getDefaultInstance(prop, new MyAuthenricator(ACCOUNT, PASS));

            MimeMessage mimeMessage = new MimeMessage(session);
            try {
                //发件人
                if (StringUtils.isNotBlank(PERSONAL)){
                    //可以设置发件人的别名
                    mimeMessage.setFrom(new InternetAddress(ACCOUNT, PERSONAL));
                }
                else {
                    //如果不需要就省略
                    mimeMessage.setFrom(new InternetAddress(ACCOUNT));
                }
                //收件人
                mimeMessage.addRecipient(Message.RecipientType.TO, new InternetAddress(recipients));
                //主题
                mimeMessage.setSubject(subject);
                //时间
                mimeMessage.setSentDate(new Date());
                //容器类，可以包含多个MimeBodyPart对象
                Multipart mp = new MimeMultipart();

                //MimeBodyPart可以包装文本，图片，附件
                MimeBodyPart body = new MimeBodyPart();
                //HTML正文
                body.setText(content);
                mp.addBodyPart(body);

                //添加图片&附件
                MimeBodyPart bodyPart = new MimeBodyPart();
                DataSource dataSource = new ByteArrayDataSource(is,"application/msexcel");
                bodyPart.setDataHandler(new DataHandler(dataSource));
                //这里很重要，通过下面的Base64编码的转换可以保证你的中文附件标题名在发送时不会变成乱码
                bodyPart.setFileName(MimeUtility.encodeText("交易明细.xls"));
                mp.addBodyPart(bodyPart);

                //设置邮件内容
                mimeMessage.setContent(mp);
                //仅仅发送文本
                //mimeMessage.setText(content);
                mimeMessage.saveChanges();
                Transport.send(mimeMessage);
                log.info("Send email successfully....");
                // 发送成功
                return true;
            } catch (Exception e) {
                e.printStackTrace();
            }
            return false;
        }
    }

发邮件工具类2(需要邮箱授权码)

import lombok.extern.slf4j.Slf4j;
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.InputStream;
import java.util.Properties;

@Component
@Slf4j
public class SendEmailUtils {
    /**
     * 发送邮件
     * @param is  表格输出流
     * @param fromEmail  发件人邮箱 （例：12345678@qq.com）
     * @param toEmail  收件人邮箱（例：12345678@qq.com）
     * @param authCode  发件人邮箱授权码
     */
    public static void sendFileEmail(String toEmail, InputStream is,String fromEmail,String authCode) {
        Transport transport = null;
        // 指定发送邮件的主机为 localhost
        String host = "smtp.qq.com";
        // 获取系统属性
        Properties properties = System.getProperties();
        // 设置邮件服务器
        properties.setProperty("mail.smtp.host", host);
        properties.put("mail.smtp.auth", "true");
        // 获取默认session对象
        Session session = Session.getDefaultInstance(properties, new Authenticator() {
            @Override
            public PasswordAuthentication getPasswordAuthentication() {
                return new PasswordAuthentication(fromEmail, authCode); //发件人邮件、授权码
            }
        });
        try {
            //创建默认的 MimeMessage 对象。
            MimeMessage message = new MimeMessage(session);
            //Set From: 头部头字段
            message.setFrom(new InternetAddress(fromEmail));
            // Set To: 头部头字段
            message.addRecipient(Message.RecipientType.TO,
                    new InternetAddress(toEmail));
            //Set Subject: 头字段
            message.setSubject("邮件主题");
            //向multipart对象中添加邮件的各个部分内容，包括文本内容和附件
            Multipart multipart = new MimeMultipart();
            //设置邮件的文本内容
            BodyPart contentPart = new MimeBodyPart();
            contentPart.setText("邮件的文本内容");
            multipart.addBodyPart(contentPart);
            //添加附件
            BodyPart messageBodyPart = new MimeBodyPart();
            DataSource source = new ByteArrayDataSource(is, "application/msexcel");
            //添加附件的内容
            messageBodyPart.setDataHandler(new DataHandler(source));
            //添加附件的标题
            //这里很重要，通过下面的Base64编码的转换可以保证你的中文附件标题名在发送时不会变成乱码
            messageBodyPart.setFileName(MimeUtility.encodeText("交易明细.xls"));
            multipart.addBodyPart(messageBodyPart);
            //将multipart对象放到message中
            message.setContent(multipart);
            //保存邮件
            message.saveChanges();
            //发送消息
            Transport.send(message, message.getAllRecipients());
            log.info("Send email successfully....");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

结果：

与50位技术专家面对面

20年技术见证，附赠技术全景图