EasyExcel 百万级别数据高效率导入/导出(上)

简介: EasyExcel 百万级别数据高效率导入/导出

前言

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

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

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

POI 框架特性对比

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

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

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

大数据量解决思路

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

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

导入

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

导出

  1. 不要一次性将数据量全量查询出来,如果几百万数据一下查出来,内存是吃不下的,所以应该对数据进行分批查出,比如:20W一次、10W一次
  2. 将数据查询出来以后,进行 Excel 写入时,也应该做批次的写入操作,一行一行的写入会造成大量 IO 请求,性能随之就会下降
  3. 几百万数据同时放在一个 sheet 里面,不然打开一个 sheet 都会停顿很长时间,操作时也会明显变慢,例如:一个 sheet 存储一百万数据
  4. 导出到 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);
    }
}

目录
相关文章
|
6月前
|
SQL 缓存 easyexcel
面试官问10W 行级别数据的 Excel 导入如何10秒处理
面试官问10W 行级别数据的 Excel 导入如何10秒处理
267 0
|
easyexcel Java Maven
百万数据excel导出功能怎么做
百万数据excel导出功能怎么做
395 0
|
存储 easyexcel Java
阿里easyexcel解析百万级大数据量的Excel表格,看这一篇文章就够了
阿里easyexcel解析百万级大数据量的Excel表格,看这一篇文章就够了
阿里easyexcel解析百万级大数据量的Excel表格,看这一篇文章就够了
|
消息中间件 JavaScript 小程序
SpringBoot 实现 MySQL 百万级数据量导出并避免 OOM 的解决方案
SpringBoot 实现 MySQL 百万级数据量导出并避免 OOM 的解决方案
|
Java 关系型数据库 MySQL
SpringBoot 实现 MySQL 百万级数据量导出并避免 OOM 的解决方案!
SpringBoot 实现 MySQL 百万级数据量导出并避免 OOM 的解决方案!
581 0
|
6月前
|
前端开发 easyexcel 关系型数据库
EasyExcel处理Mysql百万数据的导入导出案例,秒级效率,拿来即用!
【5月更文挑战第11天】EasyExcel处理Mysql百万数据的导入导出案例,秒级效率,拿来即用!
268 1
|
缓存 Java easyexcel
如何高效的导出 百万级别的数据量 到 Excel?
如何高效的导出 百万级别的数据量 到 Excel?
229 0
|
SQL 存储 Java
Java实现excel表数据的批量存储(结合easyexcel插件)
Java实现excel表数据的批量存储(结合easyexcel插件)
|
XML 缓存 API
百万级 Excel导入数据库 效率太低? 基于 SAX 的事件模型 导入,将会解决 效率问题
百万级 Excel导入数据库 效率太低? 基于 SAX 的事件模型 导入,将会解决 效率问题
96 0
|
XML 存储 Java
SpringBoot结合POI实现百万级数据报表操作
SpringBoot结合POI实现百万级数据报表操作
143 0