使用EasyExcel分页导出与导入大量数据

在我们使用easyexcel导出大量数据时，如果数据量过大会造成内存溢出，写了一个简单的demo提供参考。
提供了一下方法：

1. 导入单个sheet数据
2. 导入多个sheet数据（并提供了两个不同的实体类导入）
3. 简单导出数据（适合少量数据）
4. 分页导出数据
5. 分页导出多sheet数据（Excel2003版最大行数是65536行，Excel2007开始的版本最大行数是1048576行）
6. 分页导出多sheet数据并发送邮件（由于数据量过大后台运行时间过长，采用这个）

1.项目截图

2.项目使用的pom文件

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.4.1</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.easyexcel</groupId>
    <artifactId>easyexcel</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>easyexcel</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>

        <!--配合插件lombok 使用-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.2</version>
            <scope>provided</scope>
        </dependency>

        <!--swagger依赖-->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.9.2</version>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.9.2</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.16</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.27</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.1.0</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-extension</artifactId>
            <version>3.1.0</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.1.0</version>
        </dependency>

        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-typehandlers-jsr310</artifactId>
            <version>1.0.2</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.71</version>
        </dependency>

        <!-- 分页插件  -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.3</version>
        </dependency>

        <!--发送邮件依赖jar包-->
        <!-- https://mvnrepository.com/artifact/javax.mail/javax.mail-api -->
        <dependency>
            <groupId>javax.mail</groupId>
            <artifactId>javax.mail-api</artifactId>
            <version>1.6.1</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.sun.mail/javax.mail -->
        <dependency>
            <groupId>com.sun.mail</groupId>
            <artifactId>javax.mail</artifactId>
            <version>1.6.1</version>
        </dependency>
        <!-- END: Java mail -->

    </dependencies>


    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
        </resources>
    </build>
    
</project>

3.配置文件

server:
  port: 8080
spring:
  datasource:
    druid:
      url: jdbc:mysql://localhost:3306/easyexcel?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
      username: root
      password: root
mybatis‐plus:
  typeAliasesPackage: com.easyexcel.dao
  mapper-locations: classpath*:com.easyexcel.mapper/*.xml

4.config配置类

4.1 MybatisPlusConfig

package com.easyexcel.config;

import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.PerformanceInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * <P>
 * Mybatis-Plus 配置
 * </p>
 *
 * @author
 * @since 2020-12-23
 */
@Configuration
@MapperScan("com.easyexcel.mapper")
public class MybatisPlusConfig {

	/**
	 * 分页插件，自动识别数据库类型
	 */
	@Bean
	public PaginationInterceptor paginationInterceptor() {
		return new PaginationInterceptor();
	}

	/**
	 * 启用性能分析插件
	 */
	@Bean
	public PerformanceInterceptor performanceInterceptor(){
		return new PerformanceInterceptor();
	}
}

4.2 SwaggerConfiguration

package com.easyexcel.config;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.PathSelectors;
import springfox.documentation.builders.RequestHandlerSelectors;
import springfox.documentation.service.ApiInfo;
import springfox.documentation.service.Contact;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spring.web.plugins.Docket;
import springfox.documentation.swagger2.annotations.EnableSwagger2;

@Configuration
@ConditionalOnProperty(prefix = "swagger",value = {"enable"},havingValue = "true")
@EnableSwagger2
public class SwaggerConfiguration {

	@Bean
	public Docket buildDocket() {
		return new Docket(DocumentationType.SWAGGER_2)
				.apiInfo(buildApiInfo())
				.select()
				// 要扫描的API(Controller)基础包
				.apis(RequestHandlerSelectors.basePackage("com.easyexcel.controller"))
				.paths(PathSelectors.any())
				.build();
	}

	/**
	 * @param
	 * @return springfox.documentation.service.ApiInfo
	 * @Title: 构建API基本信息
	 * @methodName: buildApiInfo
	 */
	private ApiInfo buildApiInfo() {
		Contact contact = new Contact("开发者","","");
		return new ApiInfoBuilder()
				.title("API文档")
				.description("")
				.contact(contact)
				.version("1.0.0").build();
	}

}

5.工具类

5.1 DeleteFileUtil

package com.easyexcel.util;

import java.io.File;


public class DeleteFileUtil {
    /**
     * 删除文件，可以是文件或文件夹
     *
     * @param fileName 要删除的文件名
     * @return 删除成功返回true，否则返回false
     */
    public static boolean delete(String fileName) {
        File file = new File(fileName);
        if (!file.exists()) {
            return false;
        } else {
            if (file.isFile()) {
                return deleteFile(fileName);
            } else {
                return deleteDirectory(fileName);
            }
        }
    }

    /**
     * 删除单个文件
     *
     * @param fileName 要删除的文件的文件名
     * @return 单个文件删除成功返回true，否则返回false
     */
    public static boolean deleteFile(String fileName) {
        File file = new File(fileName);
        // 如果文件路径所对应的文件存在，并且是一个文件，则直接删除
        if (file.exists() && file.isFile()) {
            if (file.delete()) {
                return true;
            } else {
                return false;
            }
        } else {
            return false;
        }
    }

    /**
     * 删除目录及目录下的文件
     *
     * @param dir 要删除的目录的文件路径
     * @return 目录删除成功返回true，否则返回false
     */
    public static boolean deleteDirectory(String dir) {
        // 如果dir不以文件分隔符结尾，自动添加文件分隔符
        if (!dir.endsWith(File.separator)) {
            dir = dir + File.separator;
        }
        File dirFile = new File(dir);
        // 如果dir对应的文件不存在，或者不是一个目录，则退出
        if ((!dirFile.exists()) || (!dirFile.isDirectory())) {
            return false;
        }
        boolean flag = true;
        // 删除文件夹中的所有文件包括子目录
        File[] files = dirFile.listFiles();
        for (int i = 0; i < files.length; i++) {
            // 删除子文件
            if (files[i].isFile()) {
                flag = DeleteFileUtil.deleteFile(files[i].getAbsolutePath());
                if (!flag) {
                    break;
                }
            }
            // 删除子目录
            else if (files[i].isDirectory()) {
                flag = DeleteFileUtil.deleteDirectory(files[i]
                        .getAbsolutePath());
                if (!flag) {
                    break;
                }
            }
        }
        if (!flag) {
            return false;
        }
        // 删除当前目录
        if (dirFile.delete()) {
            return true;
        } else {
            return false;
        }
    }
}

5.2 EasyExcelUtil

package com.easyexcel.util;


import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.util.CollectionUtils;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;


/**
 * @Copyright (C), 2018-2020
 * @FileName: EasyExcelUtil
 * @Author: 杜景涛
 * @Date: 2020/12/23 14:58
 * @Description: easyexcel工具类
 */
public class EasyExcelUtil {

    /**
     * 导出数据到excel
     *
     * @param response
     * @param fileName  文件名
     * @param sheetName 表格sheet名
     * @param list      导出数据
     * @param clazz     实体类
     */
    public static void export(HttpServletResponse response, String fileName, String sheetName, List<?> list, Class clazz) {
        if (CollectionUtils.isEmpty(list)) {
            return;
        }
        try (ServletOutputStream out = response.getOutputStream()) {
            response.setCharacterEncoding("utf8");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
            response.setHeader("Cache-Control", "no-store");
            response.addHeader("Cache-Control", "max-age=0");
            EasyExcel.write(out, clazz).sheet(sheetName).doWrite(list);
            out.flush();
        } catch (IOException e) {
            throw new RuntimeException();
        }
    }
}

5.3 EmailUtil

package com.easyexcel.util;

import java.util.Date;
import java.util.List;
import java.util.Properties;

import javax.activation.DataHandler;
import javax.activation.DataSource;
import javax.activation.FileDataSource;
import javax.mail.Authenticator;
import javax.mail.BodyPart;
import javax.mail.Multipart;
import javax.mail.PasswordAuthentication;
import javax.mail.Session;
import javax.mail.Transport;
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;

/**
 * 发送邮件
 */
public class EmailUtil {
    private static final String SMTP_HOST = "smtp.163.com";
    private static final String USER_NAME = "dujingtao0228@163.com";
    //邮箱授权码
    private static final String PASSWORD = "*******";


    /**
     * 发送邮件
     *
     * @param title
     * @param content
     * @param receiveList
     * @throws Exception
     */
    public static void sendEmail(String title, String content, List<String> receiveList, String fileBytes) {


        Session session = EmailUtil.getSession();
        MimeMessage message = new MimeMessage(session);
        InternetAddress[] toArray = new InternetAddress[receiveList.size()];
        try {
            //接收列表
            for (int i = 0; i < toArray.length; i++) {
                toArray[i] = new InternetAddress(receiveList.get(i));
            }
            message.setSubject(title);
            message.setSentDate(new Date());
            message.setFrom(new InternetAddress(USER_NAME));
            message.addRecipients(MimeMessage.RecipientType.TO, toArray);
            // 创建消息部分
            BodyPart messageBodyPart = new MimeBodyPart();
            // 消息
            messageBodyPart.setContent(content, "text/html;charset=utf-8");
            // 创建多重消息
            Multipart multipart = new MimeMultipart();
            // 设置文本消息部分
            multipart.addBodyPart(messageBodyPart);
            // 附件部分
            messageBodyPart = new MimeBodyPart();
            // 设置要发送附件的文件路径
            DataSource source = new FileDataSource(fileBytes);
            messageBodyPart.setDataHandler(new DataHandler(source));
            // 处理附件名称中文（附带文件路径）乱码问题
            messageBodyPart.setFileName(MimeUtility.encodeText(title + ".zip"));
            multipart.addBodyPart(messageBodyPart);
            // 发送完整消息
            message.setContent(multipart);
            // 发送消息
            Transport.send(message);
        } catch (Exception e) {
        }
    }

    public static Session getSession() {
        Properties props = new Properties();
        props.setProperty("mail.transport.protocol", "smtp");
        props.setProperty("mail.smtp.host", SMTP_HOST);
        props.setProperty("mail.smtp.port", "25");
        props.setProperty("mail.smtp.auth", "true");
        Session session = Session.getInstance(props,
                new Authenticator() {
                    @Override
                    protected PasswordAuthentication getPasswordAuthentication() {
                        return new PasswordAuthentication(USER_NAME, PASSWORD);
                    }
                });

        return session;
    }

}

6.实体类

6.1 User

package com.easyexcel.dao;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.*;

/**
 * @Copyright (C), 2018-2020
 * @FileName: User
 * @Author: 杜景涛
 * @Date: 2020/12/23 14:47
 * @Description: 用户实体类
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName("user")
public class User {
    /**
     * 用户id'
     */

    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    @TableId(value = "id",type = IdType.AUTO)
    private Integer id;

    /**
     * 姓名
     */
    @ExcelProperty("姓名")
    private String name;

    /**
     * 性别
     */
    @ExcelProperty("性别")
    private String sex;

    /**
     * 年龄
     */
    @ExcelProperty("年龄")
    private Integer age;

    /**
     * 手机号
     */
    @ExcelProperty("手机号")
    private String phone;

    /**
     * 住址
     */
    @ExcelProperty("住址")
    private String address;

}

6.2 Achievement

package com.easyexcel.dao;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @Copyright (C), 2018-2020
 * @FileName: Achievement
 * @Author: 杜景涛
 * @Date: 2020/12/24 14:23
 * @Description: 成绩实体类
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName("achievement")
public class Achievement {
    /**
     * id'
     */

    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;
    /**
     * 用户id
     */
    @ExcelProperty("用户id")
    @TableId(value = "uid")
    private Integer uid;
    /**
     * 语文
     */
    @ExcelProperty("语文")
    private Double chinese;
    /**
     * 数学
     */
    @ExcelProperty("数学")
    private Double mathematics;
    /**
     * 英语
     */
    @ExcelProperty("英语")
    private Double english;
    /**
     * 总和
     */
    @ExcelProperty("总和")
    private Double sum;

}

7. service接口

7.1 UserService

package com.easyexcel.service;

import com.easyexcel.dao.User;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

/**
 * @Copyright (C), 2018-2020
 * @FileName: UserService
 * @Author: 杜景涛
 * @Date: 2020/12/23 14:57
 * @Description:
 */
public interface UserService {
    /**
     * 批量插入
     *
     * @param users
     */
    void insertBatch(List<User> users);

    /**
     * 导出数据
     *
     * @param response
     */
    void simpleWrite(HttpServletResponse response);

    /**
     * 分页导出数据
     *
     * @param response
     */
    void pageWrite(HttpServletResponse response);

    /**
     * 分页多sheet导出数据
     *
     * @param response
     */
    void pageWriteSheets(HttpServletResponse response);

    /**
     * 发送excel到邮箱
     *
     * @param response
     */
    void writeSendMail(HttpServletResponse response) throws IOException;
}

7.2 AchievementService

package com.easyexcel.service;

import com.easyexcel.dao.Achievement;

import java.util.List;

/**
 * @Copyright (C), 2018-2020
 * @FileName: AchievementService
 * @Author: 杜景涛
 * @Date: 2020/12/24 14:28
 * @Description:
 */
public interface AchievementService {
    /**
     * 批量插入
     *
     * @param achievements
     */
    void insertBatch(List<Achievement> achievements);
}

8. xml

8.1 UserMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.easyexcel.mapper.UserMapper">

    <insert id="insertBatch" parameterType="java.util.List">
        INSERT INTO user
        (name,sex,age,phone,address)
        VALUES
        <foreach collection="list" close="" index="index" item="item" open="" separator=",">
            (#{item.name},#{item.sex},#{item.age},#{item.phone},#{item.address})
        </foreach>
    </insert>
    <select id="findPageUser" resultType="com.easyexcel.dao.User">
        select * from user
    </select>
</mapper>

8.2 AchievementMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.easyexcel.mapper.AchievementMapper">

    <insert id="insertBatch" parameterType="java.util.List">
        INSERT INTO achievement
        (uid,chinese,mathematics,english,sum)
        VALUES
        <foreach collection="list" close="" index="index" item="item" open="" separator=",">
            (#{item.uid},#{item.chinese},#{item.mathematics},#{item.english},#{item.sum})
        </foreach>
    </insert>
</mapper>

9. service实现类

9.1 UserServiceImpl

package com.easyexcel.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.easyexcel.dao.User;
import com.easyexcel.mapper.UserMapper;
import com.easyexcel.service.UserService;
import com.easyexcel.util.DeleteFileUtil;
import com.easyexcel.util.EasyExcelUtil;
import com.easyexcel.util.EmailUtil;
import com.github.pagehelper.PageHelper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

/**
 * @Copyright (C), 2018-2020
 * @FileName: UserServiceImpl
 * @Author: 杜景涛
 * @Date: 2020/12/23 14:57
 * @Description:
 */
@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserMapper userMapper;

    /**
     * 获得程序当前路径
     */
    private String basePath = System.getProperty("user.dir");

    @Override
    public void insertBatch(List<User> users) {
        userMapper.insertBatch(users);
    }

    @Override
    public void simpleWrite(HttpServletResponse response) {
        EasyExcelUtil.export(response, "test01", "sheet1", userMapper.findAll(), User.class);
    }

    @Override
    public void pageWrite(HttpServletResponse response) {
        ExcelWriter excelWriter = null;
        try (ServletOutputStream out = response.getOutputStream()) {
            //设置字符集为utf-8
            response.setCharacterEncoding("utf8");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            //通知浏览器服务器发送的数据格式
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("Test02" + ".xlsx", "UTF-8"));
            //发送一个报头，告诉浏览器当前页面不进行缓存，每次访问的时间必须从服务器上读取最新的数据
            response.setHeader("Cache-Control", "no-store");
            response.addHeader("Cache-Control", "max-age=0");
            // 这里 需要指定写用哪个class去写
            excelWriter = EasyExcel.write(out, User.class).build();
            // 这里注意 如果同一个sheet只要创建一次
            WriteSheet writeSheet = EasyExcel.writerSheet("sheet").build();
            Integer number = userMapper.number();
            int page = (int) Math.ceil((double) number / (double) 10);
            // 去调用写入,根据数据库分页的总的页数来
            for (int i = 1; i <= page; i++) {
                //先定义一个空集合每次循环使他变成null减少内存的占用
                List<User> pageUser = null;
                PageHelper.startPage(i, 10);
                pageUser = userMapper.findPageUser();
                excelWriter.write(pageUser, writeSheet);
                pageUser.clear();
            }
            // 千万别忘记finish 会帮忙关闭流
            excelWriter.finish();
            out.flush();
        } catch (IOException e) {
            throw new RuntimeException();
        }
    }

    @Override
    public void pageWriteSheets(HttpServletResponse response) {
        ExcelWriter excelWriter = null;
        try (ServletOutputStream out = response.getOutputStream()) {
            response.setCharacterEncoding("utf8");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("Test03" + ".xlsx", "UTF-8"));
            response.setHeader("Cache-Control", "no-store");
            response.addHeader("Cache-Control", "max-age=0");
            // 这里 需要指定写用哪个class去写
            excelWriter = EasyExcel.write(out, User.class).build();
            // 这里注意 如果同一个sheet只要创建一次
            WriteSheet writeSheet = EasyExcel.writerSheet("sheet").build();
            Integer number = userMapper.number();
            Integer count = 0;
            int page = (int) Math.ceil((double) number / (double) 10);
            // 去调用写入,根据数据库分页的总的页数来
            for (int i = 1; i <= page; i++) {
                if (count == 2) {
                    writeSheet = EasyExcel.writerSheet("sheet2").build();
                    count = 0;
                }
                //先定义一个空集合每次循环使他变成null减少内存的占用
                List<User> pageUser = null;
                PageHelper.startPage(i, 10);
                pageUser = userMapper.findPageUser();
                excelWriter.write(pageUser, writeSheet);
                pageUser.clear();
                count++;
            }
            // 千万别忘记finish 会帮忙关闭流
            excelWriter.finish();
            out.flush();
        } catch (IOException e) {
            throw new RuntimeException();
        }
    }

    @Override
    public void writeSendMail(HttpServletResponse response) throws IOException {
        ExcelWriter excelWriter = null;
        UUID uuid = UUID.randomUUID();
        ZipOutputStream zip = new ZipOutputStream(new FileOutputStream(basePath + "\\src\\main\\resources\\file\\" + uuid + ".zip"));
        ZipEntry entry = new ZipEntry("明细查询.xlsx");
        zip.putNextEntry(entry);
        try {
            // 这里需要指定写用哪个class去写
            excelWriter = EasyExcel.write(zip, User.class).build();
            // 这里注意 如果同一个sheet只要创建一次
            WriteSheet writeSheet = EasyExcel.writerSheet("sheet").build();
            Integer number = userMapper.number();
            int count = 0;
            int page = (int) Math.ceil((double) number / (double) 10);
            // 去调用写入,根据数据库分页的总的页数来
            for (int i = 1; i <= page; i++) {
                if (count == 2) {
                    writeSheet = EasyExcel.writerSheet("sheet2").build();
                    count = 0;
                }
                //先定义一个空集合每次循环使他变成null减少内存的占用
                List<User> pageUser = null;
                PageHelper.startPage(i, 10);
                pageUser = userMapper.findPageUser();
                excelWriter.write(pageUser, writeSheet);
                pageUser.clear();
                count++;
            }
        } finally {
            // 千万别忘记finish 会帮忙关闭流
            if (excelWriter != null) {
                excelWriter.finish();
                zip.flush();
                zip.close();
            }
        }
        List<String> receiveList = new ArrayList<>();
        receiveList.add("2209273361@qq.com");
        EmailUtil.sendEmail("明细查询", "查询数据已发送,请注意查收！", receiveList, basePath + "\\src\\main\\resources\\file\\" + uuid + ".zip");
        DeleteFileUtil.delete(basePath + "\\src\\main\\resources\\file\\" + uuid + ".zip");
    }


}

9.2 AchievementServiceImpl

package com.easyexcel.service.impl;

import com.easyexcel.dao.Achievement;
import com.easyexcel.mapper.AchievementMapper;
import com.easyexcel.service.AchievementService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @Copyright (C), 2018-2020
 * @FileName: AchievementServiceImpl
 * @Author: 杜景涛
 * @Date: 2020/12/24 14:29
 * @Description:
 */
@Service
public class AchievementServiceImpl implements AchievementService {
    @Autowired
    private AchievementMapper achievementMapper;

    @Override
    public void insertBatch(List<Achievement> achievements) {
        achievementMapper.insertBatch(achievements);
    }
}

10.拦截器

10.1 UserDataListener

package com.easyexcel.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.easyexcel.dao.User;
import com.easyexcel.service.UserService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.ArrayList;
import java.util.List;

/**
 * 模板的读取类
 */
public class UserDataListener extends AnalysisEventListener<User> {
    private static final Logger LOGGER = LoggerFactory.getLogger(UserDataListener.class);
    /**
     * 每隔3000条存储数据库,然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 3000;
    List<User> list = new ArrayList<>();

    private UserService userService;


    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param userService
     */
    public UserDataListener(UserService userService) {
        this.userService = userService;
    }

    @Override
    public void invoke(User data, AnalysisContext context) {
        LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
        list.add(data);
        if (list.size() >= BATCH_COUNT) {
            saveData();
            list.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
        LOGGER.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        userService.insertBatch(list);
        LOGGER.info("存储数据库成功！");
    }
}

10.2 AchievementDataListener

package com.easyexcel.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.easyexcel.dao.Achievement;
import com.easyexcel.service.AchievementService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.ArrayList;
import java.util.List;

/**
 * 模板的读取类
 */
public class AchievementDataListener extends AnalysisEventListener<Achievement> {
    private static final Logger LOGGER = LoggerFactory.getLogger(AchievementDataListener.class);

    /**
     * 每隔3000条存储数据库，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 3000;
    List<Achievement> list = new ArrayList<>();


    private AchievementService achievementService;


    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param achievementService
     */
    public AchievementDataListener(AchievementService achievementService) {
        this.achievementService = achievementService;
    }

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data
     * @param context
     */
    @Override
    public void invoke(Achievement data, AnalysisContext context) {
        LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        LOGGER.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        achievementService.insertBatch(list);
        LOGGER.info("存储数据库成功！");
    }
}

11.Controller服务层

11.1UserController

package com.easyexcel.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.easyexcel.dao.Achievement;
import com.easyexcel.dao.User;
import com.easyexcel.service.AchievementService;
import com.easyexcel.service.UserService;
import com.easyexcel.listener.UserDataListener;
import com.easyexcel.listener.AchievementDataListener;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;

/**
 * @Copyright (C), 2018-2020
 * @FileName: UserController
 * @Author: 杜景涛
 * @Date: 2020/12/23 14:58
 * @Description:
 */
@RestController
@RequestMapping("/v1")
@Api(tags = {"数据导出导入"})
public class UserController {

    @Resource
    private UserService userService;
    @Resource
    private AchievementService achievementService;

    @ApiOperation(value = "导入单个sheet数据")
    @PostMapping("/importSheet")
    public void importSheet(@RequestParam("multipartFile") MultipartFile multipartFile) {
        try {
            InputStream inputStream = multipartFile.getInputStream();
            //异步读取所有sheet数据,可在sheet方法参数中指定sheet索引,sheet名称
            EasyExcel.read(inputStream, User.class, new UserDataListener(userService)).sheet().doRead();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @ApiOperation(value = "导入多个sheet数据")
    @PostMapping("/importSheets")
    public void importSheets(@RequestParam("multipartFile") MultipartFile multipartFile) {
        ExcelReader excelReader = null;
        try {
            InputStream inputStream = multipartFile.getInputStream();
            excelReader = EasyExcel.read(inputStream).build();
            ReadSheet readSheet1 =
                    //构建ExcelReader对象
                    EasyExcel.readSheet(0).head(User.class).registerReadListener(new UserDataListener(userService)).build();
            ReadSheet readSheet2 =
                    //构建ExcelReader对象
                    EasyExcel.readSheet(1).head(Achievement.class).registerReadListener(new AchievementDataListener(achievementService)).build();
            //这里注意 一定要把sheet1 sheet2 一起传进去
            excelReader.read(readSheet1, readSheet2);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (excelReader != null) {
                // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
                excelReader.finish();
            }
        }
    }

    @ApiOperation(value = "导出数据", produces = "application/octet-stream")
    @PostMapping("/simpleWrite")
    public void simpleWrite(HttpServletResponse response) {
        userService.simpleWrite(response);
    }

    @ApiOperation(value = "分页导出数据", produces = "application/octet-stream")
    @PostMapping("/pageWrite")
    public void pageWrite(HttpServletResponse response) {
        userService.pageWrite(response);
    }

    @ApiOperation(value = "分页导出多sheet数据", produces = "application/octet-stream")
    @PostMapping("/pageWriteSheets")
    public void pageWriteSheets(HttpServletResponse response) {
        userService.pageWriteSheets(response);
    }

    @ApiOperation(value = "分页导出多sheet数据发送邮件")
    @PostMapping("/writeSendMail")
    public void writeSendMail(HttpServletResponse response) throws IOException {
        userService.writeSendMail(response);
    }
}

项目地址

https://gitee.com/du_jing_tao/easyexcel.git