EasyExcel 百万级别数据高效率导入/导出（上）-阿里云开发者社区

前言

在日常的开发中，用的比较多的方式就是 Apache 下的 POI 框架了，但在目前数据量大的时代下，这种方式显得已经不适合了

编写该篇文章主要是公司中实际上用到了这种场景，将它作为文章分享出来，如果这种方式的导入/导出能帮助到大家也挺开心的，或者大家也可以发表自己在这方面的看法，提出来，做为更好的一种方式去呈现

节点分以下几步走，先是介绍原始 Apache POI 以及由阿里巴巴开源的一框 POI 框架做对比，再是提出解决这方面问题上的思路，然后进行案例演示，最后记录一下在使用中遇到的错误点.

POI 框架特性对比

poi 依赖的基础接口：WorkBook，有几种实现子类需要进行区分，如下：

HSSFWorkbook：Excel 2003(包含) 之前版本使用的子类对象，处理的文件格式都是 .xls 的，其是 poi 中最常用的方式，处理的行数在 6W+，一般处理的数据不超过这个大小就不会出现内存溢出的，这个量内存也是足够支撑的.
XSSFWorkbook：Excel 2003-2007 使用的子类对象，目前还是有大量公司使用的这个，文件格式为 .xlsx，出现这个格式就是为了突破 HSSFWorkBook 6W 数据的局限，是为了针对Excel2007版本的 1048576行，16384 列，最多可以导出 104w 条数据，虽然在数据上增加了，但是内存的瓶颈也就来了，OOM 离之不远了.
SXSSFWorkbook：该实现类是 POI3.8 之后的版本才有的,它可以操作 Excel2007 以后的所有版本 Excel,扩展名是 .xlsx，这种方式提供了一种低内存占用机制，存储百万数据丝毫不是问题，一般不会出现内存溢出（它使用硬盘来换内存，也就是说当内存数据到达一定时会采用硬盘来进行存储，内存里存储的只会是最新的数据），缺点：因为它使用到了硬盘，当数据到达硬盘以后，也就无法完成数据的克隆或者公式计算，sheet.clone() 已经无法被支持了

在使用过程中，如果不涉及到 Excel 公式和样式并且数据量较大的情况下，推荐使用 SXSSFWorkbook；数据量不超过 6W~7W 也涉及到了公式的计算，推荐使用 XSSFWorkbook

大数据量解决思路

使用传统的 poi 导入导出方式，当数据量过大时，明显会出现 OOM 异常，因此推荐大家使用阿里巴巴开源的 easyExcel 框架作为导入导出的媒介

GitHub - alibaba/easyexcel: 快速、简单避免OOM的处理Excel工具

导入

导入数据也分批次的导入，导入也需要集合来存储 Excel 行列的值，当这个批次被导入完成后，集合进行清空，防止重复处理且减少内存的占用
在下面的案例中，会通过 MyBatis、JDBC 做批量的数据库插入操作，很明显你就会 JDBC 的效率比 MyBatis 高，因为 MyBatis 底层也是通过 JDBC 对数据库进行操作的，MyBatis 在处理结果集的时候都是一条一条数据进行循环遍历处理的，所以在这个基础上效率就下降了.

导出

不要一次性将数据量全量查询出来，如果几百万数据一下查出来，内存是吃不下的，所以应该对数据进行分批查出，比如：20W一次、10W一次
将数据查询出来以后，进行 Excel 写入时，也应该做批次的写入操作，一行一行的写入会造成大量 IO 请求，性能随之就会下降
几百万数据同时放在一个 sheet 里面，不然打开一个 sheet 都会停顿很长时间，操作时也会明显变慢，例如：一个 sheet 存储一百万数据
导出到 excel 时，肯定需要依赖集合进行数据的临时存储，在每个批次的数据处理完以后，记得对集合进行清空，以便让 GC 知道这个可以提前进行回收了，内存也不会一直被占用.

案例演示

准备工作

前期准备好 SQL 脚本和存储函数用于批量生成假数据

# 表结构脚本
CREATE TABLE `act_result_log` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `onlineseqid` varchar(32) DEFAULT NULL,
  `businessid` varchar(32) DEFAULT NULL,
  `becifno` varchar(32) DEFAULT NULL,
  `ivisresult` varchar(32) DEFAULT NULL,
  `createdby` varchar(32) DEFAULT NULL,
  `createddate` date DEFAULT NULL,
  `updateby` varchar(32) DEFAULT NULL,
  `updateddate` date DEFAULT NULL,
  `risklevel` varchar(32) DEFAULT NULL,
  `is_deleted` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `account_id_IDX` (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1000002 DEFAULT CHARSET=utf8mb4;
# 存储函数，生成五百万数据，等待时间会比较长
CREATE PROCEDURE demo.batch_generate ( )
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 5000000 DO
INSERT INTO `demo`.`act_result_log` (`onlineseqid`, `businessid`, `becifno`, `ivisresult`, `createdby`,`createddate`, `updateby`, `updateddate`,`risklevel`)
VALUES(CONCAT('0' + i), CONCAT('1'+ i), concat('2'+i), concat('3'+i),'1',now(),'1',now(),'1');
SET i = i + 1;
END WHILE;
END

EasyExcel maven 版本依赖

<easyexcel.version>3.0.5</easyexcel.version>
<!--EasyExcel相关依赖-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>${easyexcel.version}</version>
</dependency>

若在你项目还用到了低版本的 EasyExcel，需要对旧版本的 poi 依赖进行排除

<easyexcel.high.version>3.0.5</easyexcel.high.version>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>${easyexcel.high.version}</version>
    <exclusions>
        <exclusion>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
        </exclusion>
        <exclusion>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
        </exclusion>
        <exclusion>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
        </exclusion>
    </exclusions>
</dependency>

示例代码

基础依赖的实体类、mapper、service、控制器、公共类

1、数据库实体

@Data
@Accessors(chain = true)
@TableName("act_result_log")
public class ActResultLogDO implements Serializable {
    private static final long serialVersionUID = 1L;
    @TableId(value = "id", type = IdType.AUTO)
    private Long id;
    @TableField("onlineseqid")
    private String onlineseqid;
    @TableField("businessid")
    private String businessid;
    @TableField("becifno")
    private String becifno;
    @TableField("ivisresult")
    private String ivisresult;
    @TableField("createdby")
    private String createdby;
    @TableField("createddate")
    private LocalDate createddate;
    @TableField("updateby")
    private String updateby;
    @TableField("updateddate")
    private LocalDate updateddate;
    @TableField("risklevel")
    private String risklevel;
}

2、实体 Vo

@Data
public class ActResultLogVO implements Serializable {
    private static final long serialVersionUID = 1L;
    @ExcelProperty(value = "onlineseqid",index = 0)
    private String onlineseqid;
    @ExcelProperty(value = "businessid",index = 1)
    private String businessid;
    @ExcelProperty(value = "becifno",index = 2)
    private String becifno;
    @ExcelProperty(value = "ivisresult",index = 3)
    private String ivisresult;
    @ExcelProperty(value = "createdby",index = 4)
    private String createdby;
    @ExcelProperty(value = "createddate",index = 5)
    private LocalDate createddate;
    @ExcelProperty(value = "updateby",index = 6)
    private String updateby;
    @ExcelProperty(value = "updateddate",index = 7)
    private LocalDate updateddate;
    @ExcelProperty(value = "risklevel",index = 8)
    private String risklevel;
}

3、控制器

@RequestMapping("/excel")
@RestController
public class ExcelController {
    @Resource
    private EasyExcelUtil easyExcelUtil;
    @GetMapping("/export")
    public void exportExcel(HttpServletResponse response) {
        easyExcelUtil.dataExport300w(response);
    }
}

4、常量类

public class ExcelConstants {
    public static final Integer PER_SHEET_ROW_COUNT = 100*10000;
    public static final Integer PER_WRITE_ROW_COUNT = 20*10000;
    public static final Integer GENERAL_ONCE_SAVE_TO_DB_ROWS_JDBC = 10*10000;
    public static final Integer GENERAL_ONCE_SAVE_TO_DB_ROWS_MYBATIS = 5*10000;
}

导入

导入分两种方式进行，第一种是通过 MyBatis 编写 SQL 语句运行，不使用 MP 是因为它底层的批量新增是一条一条写入的，并不是实际意义上的批次；第二种是通过原生 JDBC 进行批量操作，使用手动提交事务的方式.

1、Service 接口

public interface IActResultLogService extends IService<ActResultLogDO> {
    /**
     * 通过分页参数查询一百w数据
     * @return
     */
    List<ActResultLogDO> findByPage100w(Integer pageNum,Integer pageSize);
    /**
     * 从 Excel 导入数据，批次为 10w，通过 JDBC
     * @param dataList
     * @return
     */
    Map<String, Object> import2DBFromExcel10wByJDBC(List<Map<Integer, String>> dataList);
    /**
     * 从 Excel 导入数据，批次为 10W，通过 MyBatis
     * @param actResultLogList
     */
    void import2DBFromExcel10wByMybatis(List<ActResultLogDO> actResultLogList);
}

2、Service 接口实现类

@Slf4j
@Service
public class ActResultLogServiceImpl extends ServiceImpl<ActResultLogMapper, ActResultLogDO> implements IActResultLogService {
    @Override
    public List<ActResultLogDO> findByPage100w(Integer pageNum, Integer pageSize) {
        PageInfo<ActResultLogDO> pageInfo = PageHelper.startPage(pageNum, pageSize).doSelectPageInfo(() -> {
            QueryWrapper<ActResultLogDO> wrapper = new QueryWrapper<>();
            // TODO 此处可以进行添加条件过滤
            baseMapper.selectList(wrapper);
        });
        return pageInfo.getList();
    }
    // Service中具体业务逻辑
    /**
     * 测试用Excel导入超过10w条数据,经过测试发现,使用 Mybatis 批量插入速度非常慢,所以这里可以使用 数据分批+JDBC分批插入+事务来继续插入速度会非常快
     */
    @Override
    public Map<String, Object> import2DBFromExcel10wByJDBC(List<Map<Integer, String>> dataList) {
        Map<String, Object> result = new HashMap<>();
        // 结果集中数据为0时,结束方法.进行下一次调用
        if (dataList.size() == 0) {
            result.put("empty", "0000");
            return result;
        }
        // JDBC分批插入+事务操作完成对10w数据的插入
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            long startTime = System.currentTimeMillis();
            log.info("{} 条,开始导入到数据库时间:{}", dataList.size(), startTime + "ms");
            conn = JDBCDruidUtils.getConnection();
            // 控制事务:默认不提交
            conn.setAutoCommit(false);
            String sql = "insert into ACT_RESULT_LOG (onlineseqid,businessid,becifno,ivisresult,createdby,createddate,updateby,updateddate,risklevel) values";
            sql += "(?,?,?,?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            // 循环结果集:这里循环不支持"烂布袋"表达式
            for (int i = 0; i < dataList.size(); i++) {
                Map<Integer, String> item = dataList.get(i);
                ps.setString(1, item.get(0));
                ps.setString(2, item.get(1));
                ps.setString(3, item.get(2));
                ps.setString(4, item.get(3));
                ps.setString(5, item.get(4));
                ps.setTimestamp(6, new Timestamp(System.currentTimeMillis()));
                ps.setString(7, item.get(6));
                ps.setTimestamp(8, new Timestamp(System.currentTimeMillis()));
                ps.setString(9, item.get(8));
                // 将一组参数添加到此 PreparedStatement 对象的批处理命令中。
                ps.addBatch();
            }
            // 执行批处理
            ps.executeBatch();
            // 手动提交事务
            conn.commit();
            long endTime = System.currentTimeMillis();
            log.info("{} 条,结束导入到数据库时间:{}", dataList.size(), endTime + "ms");
            log.info("{} 条,导入用时:{}", dataList.size(), (endTime - startTime) + "ms");
            result.put("success", "1111");
        } catch (Exception e) {
            result.put("exception", "0000");
            e.printStackTrace();
        } finally {
            // 关连接
            JDBCDruidUtils.close(conn, ps);
        }
        return result;
    }
    // 采用 mapper 编写 SQL 语句进行测试，效率明显比原生 JDBC 要低
    @Override
    public void import2DBFromExcel10wByMybatis(List<ActResultLogDO> actResultLogList) {
        baseMapper.importToDb(actResultLogList);
    }
}

EasyExcel 百万级别数据高效率导入/导出（上）

前言

POI 框架特性对比

大数据量解决思路

导入

导出

案例演示

准备工作

示例代码

导入

热门文章

最新文章

相关电子书

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

EasyExcel 百万级别数据高效率导入/导出（上）

前言

POI 框架特性对比

大数据量解决思路

导入

导出

案例演示

准备工作

示例代码

导入

热门文章

最新文章

相关电子书