前言
在日常的开发中，用的比较多的方式就是 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); } }
