Excel导入导出百万级数据

Excel百万级数据导入导出方案

本文使用EasyExcel工作，导出格式XLSX

1.生成测试数据

这里用到的是MYSQL 5.7.31 创建表语句

CREATE TABLE `ACT_RESULT_LOG` (
  `onlineseqid` int(11) NOT NULL AUTO_INCREMENT,
  `businessid` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `becifno` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ivisresult` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `createdby` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `createddate` datetime DEFAULT CURRENT_TIMESTAMP,
  `updateby` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `updateddate` datetime DEFAULT CURRENT_TIMESTAMP,
  `risklevel` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`onlineseqid`)
) ENGINE=InnoDB AUTO_INCREMENT=1310694 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

这里插入测试数据

INSERT INTO `wjb`.`ACT_RESULT_LOG` (`onlineseqid`, `businessid`, `becifno`, `ivisresult`, `createdby`, `createddate`, `updateby`, `updateddate`, `risklevel`) VALUES (18179, 'test-01', '测试excel百万级数据导入导出', 'YES', '=======', '2022-10-11 10:18:11', 'wujubin', '2022-10-11 10:18:11', '1');

-- 重复执行这条sql 一直执行数量大于100w 这里要注意了如果使用单个sheet 数据不能大于104w 这是excel的限制
INSERT INTO ACT_RESULT_LOG(`businessid`, `becifno`, `ivisresult`, `createdby`, `createddate`, `updateby`, `updateddate`, `risklevel`)
select `businessid`, `becifno`, `ivisresult`, `createdby`, `createddate`, `updateby`, `updateddate`, `risklevel` from ACT_RESULT_LOG

2.导出数据

依赖导入

<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>easyexcel</artifactId>
  <version>3.1.1</version>
</dependency>

导出注意点

• 一般需求导出excel数据量都比较少，不会引起OOM，因为数据都是放到内存中，如果达到百万级别很容易出现OOM，这里可以使用分页查询来处理。
• 如果数据量超过104w，就要写到多个sheet中，本文演示单个sheet导入导出百万数据

不分页查询导出

@GetMapping("/download")
    public void download(HttpServletResponse response) throws IOException {
        log.info("start====>{}",System.currentTimeMillis());
        long l = System.currentTimeMillis();
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), ActResultLogModel.class).sheet("模板").doWrite(actResultLogService.getList());
        log.info("end====>{}",System.currentTimeMillis()-l);
    }

结果耗时：40s. 但是很容易导致OOM，这里我设置的jvm内存比较大【不推荐】

分页查询导出

 @GetMapping("/download2")
  public void download2(HttpServletResponse response) throws IOException {
    log.info("start====>{}",System.currentTimeMillis());
    long l = System.currentTimeMillis();
    OutputStream outputStream = null;
    try {
      outputStream = response.getOutputStream();
      ExcelWriter excelWriter = EasyExcel.write(outputStream, ActResultLogModel.class).build();
      // 这里注意 如果同一个sheet只要创建一次
      WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
      // 导出总数量
      int dataCount = actResultLogService.getDataCount();
      // 每次查询size
      int count = 200000;
      // 计算查询次数/页数
      int index = dataCount>count?dataCount/count+1:1;
      // 根据数据库分页的总的页数来
      for (int i = 0; i < index; i++) {
        List<ActResultLogModel> listByPage = actResultLogService.getListByPage(i*count,  count);
        log.info("===>i:{},size:{}",(i+1),listByPage.size());
        excelWriter.write(listByPage, writeSheet);
      }
    }catch (Exception e){
      log.error("====>{}",e.getMessage());
    }
    // 这里注意 使用swagger 会导致各种问题，请直接用浏览器或者用postman
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setCharacterEncoding("utf-8");
    // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
    String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
    response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    log.info("end====>{}",System.currentTimeMillis()-l);
  }

耗时：37s. 不会导致OOM,且速度还要快些

导出的数据

3.导入数据

处理思路：导入文件后解析时需要分批操作【也是防止大量数据进入内存，导致OOM】

这里演示解析数据后，分批入库。入库也可换做做业务

这里需要了解一个点，easyExcel在解析excel的时候提供了一个监听器，只要实现它，读每行内容之后都会执行invoke方法

@Slf4j
public class UploadDataListener implements ReadListener<ActResultLogModel> {
    /**
     * 每隔BATCH_COUNT条存储数据库，实际使用中可以根据MYSQL服务器配置来配置，调试最优执行SQL大小，
     * 然后清理list，方便内存回收
     */
    private static final int BATCH_COUNT = 50000;
    private List<ActResultLogModel> actResultLogModelList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

  	/**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private final ActResultLogService actResultLogService;

 		//如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
    public UploadDataListener(ActResultLogService actResultLogService){
        this.actResultLogService = actResultLogService;
    }
  
		//这个每一条数据解析都会来调用
    @Override
    public void invoke(ActResultLogModel actResultLogModel, AnalysisContext analysisContext) {
        actResultLogModelList.add(actResultLogModel);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (actResultLogModelList.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            actResultLogModelList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

  	//所有数据解析完成了 都会来调用
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();
        log.info("===>解析完成！！");
    }
  
		//存储数据库
    public void saveData(){
        int i = actResultLogService.saveList(actResultLogModelList);
        log.info("====>{}",i);
    }
}

其中在配置分批次入库时，BATCH_COUNT参数设置需要根据MYSQL服务器配置来，过大可能会报You can change this value on the server by setting the ‘max_allowed_packet’ variable。设置MYSQL中配置文件my.cnf的max_allowed_packet参数就行，大于你报错提示的size就行。

其他具体代码

@PostMapping("/upload")
@ResponseBody
public String upload(MultipartFile file) throws IOException {
  log.info("start====>{}",System.currentTimeMillis());
  long l = System.currentTimeMillis();
  EasyExcel.read(file.getInputStream(), ActResultLogModel.class, 
                 new UploadDataListener(actResultLogService)).sheet().doRead();	// 这里初始化监听器就可以了
  log.info("end====>{}",System.currentTimeMillis()-l);
  return "success";
}

// 写入数据
public int saveList(List<ActResultLogModel> actResultLogModelList){
  // 最好判断 可能会报
  if (CollectionUtils.isEmpty(actResultLogModelList)) return -1;
  int i = actResultLogMapper.insertData(actResultLogModelList);
  log.info("数据入库数量：====>{}",i);
  return i;
}

// 分批入库
  @Insert({"<script>" +
    "<foreach collection=\"actResultLogModelList\" item=\"item\" separator=\";\">" +
    "INSERT INTO `wjb`.`ACT_RESULT_LOG2` (`businessid`, `becifno`, `ivisresult`, `createdby`, `createddate`, `updateby`, `updateddate`, `risklevel`) " +
    " VALUES (#{item.businessid}, #{item.becifno}, #{item.ivisresult}, #{item.createdby}, #{item.createddate}, #{item.updateby}, #{item.updateddate}, #{item.risklevel})" +
    "</foreach>" +
    "</script>"})
  int insertData(@Param("actResultLogModelList") List<ActResultLogModel> actResultLogModelList);

主要注意：OOM，根据服务器JVM大小来处理对于数据量

其他更多玩法：EasyExcel官方文档