EasyExcel处理Mysql百万数据的导入导出案例，秒级效率，拿来即用！-阿里云开发者社区

一、写在开头

今天终于更新新专栏 《EfficientFarm》 的第二篇博文啦，本文主要来记录一下对于EasyExcel的高效应用，包括对MySQL数据库百万级数据量的导入与导出操作，以及性能的优化（争取做到秒级性能！）。

二、如何做技术选型

其实在市面上我们有很多常用的excel操作依赖库，除了EasyExcel之外，还有EasyPOI、JXL、JXLS等等，他们各有千秋，依赖重点不同，我们在做技术选型的时候，要根据自己的需求去做针对性选择，下面我们列举了这几种常见技术的特点对比

技术方案	优点	缺点
EasyExcel	简单易用，API设计友好；高效处理大量数据；支持自定义样式和格式化器等功能	不支持老版本 Excel 文件 (如 xls 格式)
POI	Apache开源项目，稳定性高，EasyPOI基于它开发的，特点类似，进行了功能增强，这里不单独列举；支持多种格式（XLS、XLSX等）；可以读写复杂表格（如带有合并单元格或图表的表格）	API使用较为繁琐；对于大数据量可能会存在性能问题
Jxls	具备良好的模板引擎机制，支持通过模板文件生成 Excel 表格；提供了可视化设计器来快速创建报告模板	性能相对其他两个方案稍弱一些；模板与代码耦合度较高。

而本文中主要针对的是大数据量的导入与导出，因此，我们果断的选择了EasyExcel技术进行实现。

三、应用场景模拟

假设我们在开发中接到了一个需求要求我们做一个功能：

1、导出商城中所有的用户信息，由于用户规模达到了百万级，导出等待时间不可太长
2、允许通过规定的excel模板进行百万级用户信息的初始化（系统迁移时会发生）。

拿到这个需求后，经过技术选型EasyExcel后，我们在心里有个大概的构想了，大概可以分三个内容：“模板下载”、“上传数据”、“下载数据”。

想好这些后，我们就可以开整了！✊✊✊

四、数据准备

在数据准备阶段，我们应该做如下几点：

1. 在数据库中创建一个用户信息表User；

-- 如果存在表先删除
drop table if exists `user`;
--建表语句
CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '员工姓名',
  `phone_num` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '联系方式',
  `address` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '住址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

2. 准备一个用户信息导入的初始化模板；

3. 模拟创造百万数据量在User表中；

这一点其实有2种方案，第一种就是在创造好的模板文件xlsx中，手工造出100万的数据，xlsx单个sheet页最大可创建104万行数据，刚刚好满足，如果用xls单个sheet还不可以，这种肯定相对麻烦，并且100万的数据有几十M，打开就已经很慢了；

另外一种方案，可以通过存储过程向MySQL中加入100w条数据，不过性能也不好，毕竟数据量太大，自己斟酌吧，sql贴出来（性能不好的电脑，不建议这么干，容易把软件跑崩）：

DELIMITER //
drop procedure IF EXISTS InsertTestData;
CREATE PROCEDURE InsertTestData()
BEGIN
    DECLARE counter INT DEFAULT 1;

    WHILE counter < 1000000 DO
            INSERT INTO user (id, name, phone_num, address) VALUES
        (counter, CONCAT('name_', counter), CONCAT('phone_', counter), CONCAT('add_',counter)) ; 
            SET counter = counter + 1;
        END WHILE;
END //
DELIMITER;

-- 调用存储过程插入数据
CALL InsertTestData();

五、SpringBoot中配置EasyExcel

5.1 pom.xml中引入依赖

本次代码中一共用到了如下这些依赖，很多小伙伴本地若已经引入了，可以忽略！

<!--lombok依赖-->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>
<!--MyBatis Plus依赖-->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.4.0</version>
</dependency>
<!--easyexcel-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.3.4</version>
</dependency>
<!-- hutool -->
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.8.25</version>
</dependency>

5.2 创建实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
@ColumnWidth(25)
public class User {
   
    /**
     * 主键
     *
     * @mbg.generated
     */
    @ExcelProperty("id")
    private Integer id;

    /**
     * 员工姓名
     *
     * @mbg.generated
     */
    @ExcelProperty("姓名")
    private String name;

    /**
     * 联系方式
     *
     * @mbg.generated
     */
    @ExcelProperty("联系方式")
    private String phoneNum;

    /**
     * 住址
     *
     * @mbg.generated
     */
    @ExcelProperty("联系地址")
    private String address;

}

【注解说明】

@ExcelProperty：声明列名。
@ColumnWidth：设置列宽。也可以直接作用在类上。统一每一列的宽度

5.3 创建数据关系映射

UserMapper 文件

//*注：这里面继承了mybatis-plus的BaseMapper接口，供后面进行分页查询使用。*
public interface UserMapper extends BaseMapper<User> {
    int deleteByPrimaryKey(Integer id);

    int insertAll(User record);

    void insertSelective(@Param("list") List<User> list);

    User selectByPrimaryKey(Integer id);

    int updateByPrimaryKeySelective(User record);

    int updateByPrimaryKey(User record);
    Integer countNum();
}

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="org.javaboy.vhr.mapper.UserMapper">
  <resultMap id="BaseResultMap" type="org.javaboy.vhr.pojo.User">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="phone_num" jdbcType="VARCHAR" property="phoneNum" />
    <result column="address" jdbcType="VARCHAR" property="address" />
  </resultMap>
  <sql id="Base_Column_List">
    id, name, phone_num, address
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from user
    where id = #{id,jdbcType=INTEGER}
  </select>
  <select id="countNum" resultType="java.lang.Integer">
    select count(*) from user
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from user
    where id = #{id,jdbcType=INTEGER}
  </delete>
  <insert id="insertAll" keyColumn="id" keyProperty="id" parameterType="org.javaboy.vhr.pojo.User" useGeneratedKeys="true">
    insert into user (name, phone_num, address
      )
    values (#{name,jdbcType=VARCHAR}, #{phoneNum,jdbcType=VARCHAR}, #{address,jdbcType=VARCHAR}
      )
  </insert>
  <insert id="insertSelective" parameterType="org.javaboy.vhr.pojo.User">
    insert into user
    (id,name, phone_num, address
      )
    values
    <foreach collection="list" item="item" separator=",">
        (#{item.id},#{item.name},#{item.phoneNum},#{item.address})
    </foreach>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="org.javaboy.vhr.pojo.User">
    update user
    <set>
      <if test="name != null">
        name = #{name,jdbcType=VARCHAR},
      </if>
      <if test="phoneNum != null">
        phone_num = #{phoneNum,jdbcType=VARCHAR},
      </if>
      <if test="address != null">
        address = #{address,jdbcType=VARCHAR},
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="org.javaboy.vhr.pojo.User">
    update user
    set name = #{name,jdbcType=VARCHAR},
      phone_num = #{phoneNum,jdbcType=VARCHAR},
      address = #{address,jdbcType=VARCHAR}
    where id = #{id,jdbcType=INTEGER}
  </update>
</mapper>

六、前端设计

前端页面采用Vue框架实现，咱们就按照上文中构想的那三点来设计就行，可以简单点实现，如果想要更加炫酷的前端样式，比如导入的文件格式校验，数据量提示等等，可以自行网上学习哈。

<template>
    <el-card>
        <div>
            <!--导入数据-->
            <el-upload
                    :show-file-list="false"
                    :before-upload="beforeUpload"
                    :on-success="onSuccess"
                    :on-error="onError"
                    :disabled="importDataDisabled"
                    style="display: inline-flex;margin-right: 8px"
                    action="/employee/excel/import">
                <!--导入数据-->
                <el-button :disabled="importDataDisabled" type="success" :icon="importDataBtnIcon">
                    {
   {
   importDataBtnText}}
                </el-button>
            </el-upload>
            <el-button type="success" @click="exportEasyExcel" icon="el-icon-download">
                导出数据
            </el-button>
            <el-button type="success" @click="exportExcelTemplate" icon="el-icon-download">
                导出模板
            </el-button>
        </div>
    </el-card>

</template>

<script>
    import {
   Message} from 'element-ui';
    export default {
   
        name: "Export",
        data() {
   
            return {
   
                importDataBtnText: '导入数据',
                importDataBtnIcon: 'el-icon-upload2',
                importDataDisabled: false,
            }
        },
        methods: {
   
            onError(res) {
   
                this.importDataBtnText = '导入数据';
                this.importDataBtnIcon = 'el-icon-upload2';
                this.importDataDisabled = false;
                console.log(res);
            },
            onSuccess(res) {
   
                this.importDataBtnText = '导入数据';
                this.importDataBtnIcon = 'el-icon-upload2';
                this.importDataDisabled = false;
                console.log(res.msg);
                if (res.msg == '文件导入成功'){
   
                    Message.success("文件导入完成")
                }
               // this.initEmps();

            },
            beforeUpload() {
   
                this.importDataBtnText = '正在导入';
                this.importDataBtnIcon = 'el-icon-loading';
                this.importDataDisabled = true;
            },
            exportEasyExcel() {
   
                window.open('/employee/excel/easyexcelexport', '_parent');
            },
            exportExcelTemplate(){
   
                window.open('/employee/excel/exporttemplate', '_parent');
            }
        }
    }

</script>

<style scoped>

</style>

效果如下：

七、导入导出实现

7.1 模板下载

1️⃣ 将准备好的用户信息模板.xlsx文件放入resource对应路径下。

2️⃣ 构建一个控制器类，用以接收导出模板、导入数据、导出数据的请求。

@RestController
@RequestMapping("/employee/excel")
@AllArgsConstructor
@Slf4j
public class EasyExcellController {
   
/**
     * 下载用户信息模板
     * @param response
     */
    @RequestMapping("/exporttemplate")
    public void downloadTemplate(HttpServletResponse response){
   
        try {
   
            //设置文件名
            InputStream inputStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("template/用户信息模板.xlsx");
            //设置头文件,注意文件名若为中文，使用encode进行处理
            response.setHeader("Content-disposition", "attachment;fileName=" +  java.net.URLEncoder.encode("用户信息模板.xlsx", "UTF-8"));
            //设置文件传输类型与编码
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            OutputStream outputStream = response.getOutputStream();
            byte[] bytes = new byte[2048];
            int len;
            while((len = inputStream.read(bytes)) != -1){
   
                outputStream.write(bytes,0,len);
            }
            outputStream.flush();
            outputStream.close();
            inputStream.close();
        } catch (Exception e) {
   
            e.printStackTrace();
        }
    }

}

这部分代码中需要注意的是，如果你的模板是中文名字，需要加上java.net.URLEncoder.encode("用户信息模板.xlsx", "UTF-8")解决乱码问题。

7.2 导入数据

1️⃣ 在EasyExcellController类中增加导入数据的请求处理方法；

    @Autowired
    EasyExcelServiceImpl easyExcel;

 /**
 * 导入百万excel文件
  * @param file
  * @return
  */
 @RequestMapping("/import")
 public RespBean easyExcelImport(MultipartFile file){
   
     if(file.isEmpty()){
   
         return RespBean.error("文件不可为空");
     }
     easyExcel.easyExcelImport(file);
     return RespBean.ok("文件导入成功");
 }

代码中的RespBean是自己定义的一个响应工具类。

public class RespBean {
   
    private Integer status;
    private String msg;
    private Object obj;

    public static RespBean build() {
   
        return new RespBean();
    }

    public static RespBean ok(String msg) {
   
        return new RespBean(200, msg, null);
    }

    public static RespBean ok(String msg, Object obj) {
   
        return new RespBean(200, msg, obj);
    }

    public static RespBean error(String msg) {
   
        return new RespBean(500, msg, null);
    }

    public static RespBean error(String msg, Object obj) {
   
        return new RespBean(500, msg, obj);
    }

    private RespBean() {
   
    }

    private RespBean(Integer status, String msg, Object obj) {
   
        this.status = status;
        this.msg = msg;
        this.obj = obj;
    }

    public Integer getStatus() {
   
        return status;
    }

    public RespBean setStatus(Integer status) {
   
        this.status = status;
        return this;
    }

    public String getMsg() {
   
        return msg;
    }

    public RespBean setMsg(String msg) {
   
        this.msg = msg;
        return this;
    }

    public Object getObj() {
   
        return obj;
    }

    public RespBean setObj(Object obj) {
   
        this.obj = obj;
        return this;
    }
}

2️⃣ 在控制器中引入的easyExcel.easyExcelImport(file)方法中进行导入逻辑的实现。

@Service
@Slf4j
@AllArgsConstructor
public class EasyExcelServiceImpl implements EasyExcelService {
   

    private final ApplicationContext applicationContext;
    /**
     * excle文件导入实现
     * @param file
     */
    @Override
    public void easyExcelImport(MultipartFile file) {
   
        try {
   
            long beginTime = System.currentTimeMillis();
            //加载文件读取监听器
            EasyExcelImportHandler listener = applicationContext.getBean(EasyExcelImportHandler.class);
            //easyexcel的read方法进行数据读取
            EasyExcel.read(file.getInputStream(), User.class,listener).sheet().doRead();
            log.info("读取文件耗时：{}秒",(System.currentTimeMillis() - beginTime)/1000);
        } catch (IOException e) {
   
            log.error("导入异常", e.getMessage(), e);
        }
    }
}

这部分代码的核心是文件读取监听器：EasyExcelImportHandler。

3️⃣ 构建文件读取监听器

@Slf4j
@Service
public class EasyExcelImportHandler implements ReadListener<User> {
   
    /*成功数据*/
    private final CopyOnWriteArrayList<User> successList = new CopyOnWriteArrayList<>();
    /*单次处理条数*/
    private final static int BATCH_COUNT = 20000;
    @Resource
    private ThreadPoolExecutor threadPoolExecutor;
    @Resource
    private UserMapper userMapper;



    @Override
    public void invoke(User user, AnalysisContext analysisContext) {
   
        if(StringUtils.isNotBlank(user.getName())){
   
            successList.add(user);
            return;
        }
        if(successList.size() >= BATCH_COUNT){
   
            log.info("读取数据：{}", successList.size());
            saveData();
        }

    }

    /**
     * 采用多线程读取数据
     */
    private void saveData() {
   
        List<List<User>> lists = ListUtil.split(successList, 20000);
        CountDownLatch countDownLatch = new CountDownLatch(lists.size());
        for (List<User> list : lists) {
   
            threadPoolExecutor.execute(()->{
   
                try {
   
                    userMapper.insertSelective(list.stream().map(o -> {
   
                        User user = new User();
                        user.setName(o.getName());
                        user.setId(o.getId());
                        user.setPhoneNum(o.getPhoneNum());
                        user.setAddress(o.getAddress());
                        return user;
                    }).collect(Collectors.toList()));
                } catch (Exception e) {
   
                    log.error("启动线程失败,e:{}", e.getMessage(), e);
                } finally {
   
                    //执行完一个线程减1,直到执行完
                    countDownLatch.countDown();
                }
            });
        }
        // 等待所有线程执行完
        try {
   
            countDownLatch.await();
        } catch (Exception e) {
   
            log.error("等待所有线程执行完异常,e:{}", e.getMessage(), e);
        }
        // 提前将不再使用的集合清空，释放资源
        successList.clear();
        lists.clear();
    }

    /**
     * 所有数据读取完成之后调用
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
   
        //读取剩余数据
        if(CollectionUtils.isNotEmpty(successList)){
   
            log.info("读取数据：{}条",successList.size());
            saveData();
        }
    }
}

在这部分代码中我们需要注意两个问题，第一个是多线程，第二个是EasyExcel提供的ReadListener监听器。

第一个，由于我们在代码里采用了多线程导入，因此我们需要配置一个合理的线程池，以提高导入效率。

@Configuration
public class EasyExcelThreadPoolExecutor {
   

    @Bean(name = "threadPoolExecutor")
    public ThreadPoolExecutor easyExcelStudentImportThreadPool() {
   
        // 系统可用处理器的虚拟机数量
        int processors = Runtime.getRuntime().availableProcessors();
        return new ThreadPoolExecutor(processors + 1,
                processors * 2 + 1,
                10 * 60,
                TimeUnit.SECONDS,
                new LinkedBlockingQueue<>(1000000));
    }
}

第二个，对于ReadListener，我们需要搞清楚它提供的方法的作用。

invoke()：读取表格内容，每一条数据解析都会来调用；
doAfterAllAnalysed()：所有数据解析完成了调用；
invokeHead() ：读取标题，里面实现在读完标题后会回调，本篇文章中未使用到；
onException()：转换异常获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则继续读取下一行，本篇文章中未使用到。

4️⃣ 导入100万数据量耗时测试

在做导入测试前，由于100万数据量的excel文件很大，所以我们要在application.yml文件中进行最大可上传文件的配置：

spring:
 servlet:
    multipart:
      max-file-size: 128MB        # 设置单个文件最大大小为10MB
      max-request-size: 128MB    # 设置多个文件大小为100MB

对100万数据进行多次导入测试，所损耗时间大概在500秒左右，8分多钟，这对于我们来说肯定无法接受，所以我们在后面针对这种导入进行彻底优化！

7.3 导出数据

1️⃣ 在EasyExcellController类中增加导出数据的请求处理方法；

/**
     * 导出百万excel文件
     * @param response
     */
    @RequestMapping("/easyexcelexport")
    public void easyExcelExport(HttpServletResponse response){
   
        try {
   
            //设置内容类型
            response.setContentType("text/csv");
            //设置响应编码
            response.setCharacterEncoding("utf-8");
            //设置文件名的编码格式,防止文件名乱码
            String fileName = URLEncoder.encode("用户信息", "UTF-8");
            //固定写法,设置响应头
            response.setHeader("Content-disposition", "attachment;filename="+ fileName + ".xlsx");
            Integer total = userMapper.countNum();
            if (total == 0) {
   
                log.info("查询无数据");
                return;
            }
            //指定用哪个class进行写出
            ExcelWriter build = EasyExcel.write(response.getOutputStream(), User.class).build();
            //设置一个sheet页存储所有导出数据
            WriteSheet writeSheet = EasyExcel.writerSheet("sheet").build();
            long pageSize = 10000;
            long pages = total / pageSize;
            long startTime = System.currentTimeMillis();
            //数据量只有一页时直接写出
            if(pages < 1){
   
                List<User> users = userMapper.selectList(null);
                build.write(users, writeSheet);
            }
            //大数据量时，进行分页查询写入
            for (int i = 0; i <= pages; i++) {
   
                Page<User> page = new Page<>();
                page.setCurrent(i + 1);
                page.setSize(pageSize);
                Page<User> userPage = userMapper.selectPage(page, null);
                build.write(userPage.getRecords(), writeSheet);
            }
            build.finish();
            log.info("导出耗时/ms:"+(System.currentTimeMillis()-startTime)+",导出数据总条数："+total);
        } catch (Exception e) {
   
            log.error("easyExcel导出失败，e:{}",e.getMessage(),e);
        }
    }

由于数据量比较大，我们在这里采用分页查询，写入到一个sheet中，如果导出到xls格式的文件中，需要写入到多个sheet中，这种可能会慢一点。

且在Mybatis-Plus中使用分页的话，需要增加一个分页插件的配置

@Configuration
public class MybatisPlusPageConfig {
   
    /**
     * 新版分页插件配置
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
   
        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
        mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        return mybatisPlusInterceptor;
    }
}

2️⃣ 百万数据量导出测试

经过多次测试发现，100万数据量平均导出耗时在40秒左右，在可以接受的范围内！

八、总结

以上就是SpringBoot项目下，通过阿里开源的EasyExcel技术进行百万级数据的导入与导出，不过针对百万数据量的导入，时间在分钟级别，这很明显不够优秀，但考虑到本文的篇幅已经很长了，我们在下一篇文章针对导入进行性能优化，敬请期待！

九、结尾彩蛋

如果本篇博客对您有一定的帮助，大家记得留言+点赞+收藏呀。原创不易，转载请联系Build哥！

EasyExcel处理Mysql百万数据的导入导出案例，秒级效率，拿来即用！

一、写在开头

二、如何做技术选型

三、应用场景模拟

四、数据准备

五、SpringBoot中配置EasyExcel

5.1 pom.xml中引入依赖

5.2 创建实体类

5.3 创建数据关系映射

六、前端设计

七、导入导出实现

7.1 模板下载

7.2 导入数据

7.3 导出数据

八、总结

九、结尾彩蛋

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

EasyExcel处理Mysql百万数据的导入导出案例，秒级效率，拿来即用！

一、写在开头

二、如何做技术选型

三、应用场景模拟

四、数据准备

五、SpringBoot中配置EasyExcel

5.1 pom.xml中引入依赖

5.2 创建实体类

5.3 创建数据关系映射

六、前端设计

七、导入导出实现

7.1 模板下载

7.2 导入数据

7.3 导出数据

八、总结

九、结尾彩蛋

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像