Excel导入导出百万级数据

article/2025/9/8 14:07:35

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 会导致各种问题，请直接用浏览器或者用postmanresponse.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();// 每次查询sizeint 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 会导致各种问题，请直接用浏览器或者用postmanresponse.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;}//这个每一条数据解析都会来调用@Overridepublic void invoke(ActResultLogModel actResultLogModel, AnalysisContext analysisContext) {actResultLogModelList.add(actResultLogModel);// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOMif (actResultLogModelList.size() >= BATCH_COUNT) {saveData();// 存储完成清理 listactResultLogModelList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);}}//所有数据解析完成了 都会来调用@Overridepublic 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官方文档


http://chatgpt.dhexx.cn/article/HEDsrvFw.shtml

相关文章

Excel数据的导入

Excel数据的导入

一、实现功能 将创建好的Excel文件内容导入到渲染好的表格中&#xff1b; 初始状态如下&#xff1a; 图一&#xff1a;点击导入的初始状态 功能介绍&#xff1a;如图点击导入按钮弹出导入学生的模态窗体内容如图一&#xff0c;1.将想要的模板提前准备好点击下载按钮经过控制…
阅读更多...
Excel导入和导出

Excel导入和导出

一、添加需要用到的依赖 <!-- 实体类工具 --> <dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional> </dependency><!-- excel工具 --> <dependency&…
阅读更多...
Java实现Excel导入和导出

Java实现Excel导入和导出

1. 功能测试 1.1 测试准备 在做测试前&#xff0c;我们需要將【2. 环境准备】中的四个文件拷贝在工程里&#xff08;如&#xff1a;我这里均放在了com.zyq.util.excel 包下&#xff09;。 1.2 数据导入 1.2.1 导入解析为JSON 比如&#xff0c;我们有下面一个表格&#xff1…
阅读更多...
前端 - excel导入 / 导出功能

前端 - excel导入 / 导出功能

1. 导入功能 1.1 前端主导(工作大量在前端) 上传excel文件&#xff0c;把excel文件的内容读出来&#xff0c;还原成最基本的行列结构&#xff0c;按后端的接口要求回传过去。 前端读excel文件&#xff0c;调接口 1.2 后端主导(工作大量在后端) 前端上传excel文件 1.3 实现 …
阅读更多...
excel导入功能

excel导入功能

------这里只是测试类------实际使用的看下面 需要用到ExcelUtils工具类 ExcelUtils的主要作用是把Excel转化成 List<List<Object>>类型的数据&#xff0c;方便遍历 package tech.niua.common.excelimport;import java.io.IOException; import java.io.InputStream…
阅读更多...
Java实现Excel导入导出操作详解

Java实现Excel导入导出操作详解

本文转载自 :Java实现Excel导入和导出&#xff0c;看这一篇就够了(珍藏版)_zyqok的博客-CSDN博客_excel导入 java前言最近抽了两天时间&#xff0c;把Java实现表格的相关操作进行了封装&#xff0c;本次封装是基于POI的二次开发&#xff0c;最终使用只需要调用一个工具类中的方…
阅读更多...
EasyExcel实现excel导入

EasyExcel实现excel导入

文章目录 前言一、使用步骤1.添加依赖&#xff1a;2.创建和实体类对应的用于导入导出的模板类&#xff0c;尽量不要直接使用实体类。每个字段需添加ExcelProperty注解&#xff0c;作为导入导出的识别的依据。注意value值是跟excel里的列名保持一致&#xff0c;不是跟数据库里的…
阅读更多...
Excel表格的导入导出——EasyExcel

Excel表格的导入导出——EasyExcel

参考视频 csdn参考地址 一、导入依赖 <dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.0.5</version> </dependency>二、实体类 方式一&#xff1a;Excel Property&#xff08;&…
阅读更多...
实现Excel的导入、导出

实现Excel的导入、导出

实现Excel的导入、导出 关于excel的操作在工作中经常会遇到&#xff0c;如果只是一次性使用的话&#xff0c;最简单的方式就是通过数据库的可视化工具&#xff08;如Navicat&#xff09;查询结果集之后直接一键生成excel了&#xff0c;当然这只能解燃眉之急&#xff0c;并不是…
阅读更多...
EasyExcel复杂excel导入

EasyExcel复杂excel导入

EasyExcel复杂excel导入 easyexcel官方都是一些简单的导入到处示例&#xff0c;复杂的excel文档导入&#xff0c;还得自己去慢慢琢磨、百度、思考、总结、学习、观察。 代码地址在文档的最后&#xff0c;如果你也遇到这种需求&#xff0c;不妨动动你的小拇指&#xff0c;点个…
阅读更多...
导出Excel的方式

导出Excel的方式

*数据是表格的形式&#xff0c;进常用到Excel *在程序中经常可以看到有导出Excel文档&#xff0c;Excel导入数据的情况&#xff0c;现在我就说一下我学到的导出Excel *导出Excel有两种方法&#xff0c;第一种是自己设置表头的&#xff0c;第二种是填充的&#xff0c;现在我说的…
阅读更多...
Excel文件导入导出操作

Excel文件导入导出操作

> 注意&#xff01;注意&#xff01;&#xff01;注意&#xff01;&#xff01;&#xff01; 文末有惊喜彩蛋&#xff0c;请注意查收&#xff01;日常开发工作中对于文件的相关操作大家多少都会涉及&#xff1a;上传解析、数据导出等。此篇内容主要分享一下工作中常用的Exce…
阅读更多...
Java实现Excel导入导出

Java实现Excel导入导出

一、导入 前言&#xff1a;导入必须用post请求 具体原因在2中叙述 1、Excel导入 总结一下目标&#xff0c;就是要将excel中的数据行、逐一提取&#xff0c;最后得到一个list&#xff0c;这个list的每个元素就是excel的每个数据行的实例&#xff0c;之后的操作就是常规的jav…
阅读更多...
Java实现Excel导入

Java实现Excel导入

实现前准备&#xff08;导入所需要的依赖&#xff09; <dependency><groupId>cn.hutool</groupId><artifactId>hutool-all</artifactId><version>5.7.22</version></dependency><dependency><groupId>org.apache.…
阅读更多...
导入Excel文件的方法

导入Excel文件的方法

我们在做一些项目的时候通常会遇到有导入文件的这种需求&#xff0c;下面我给大家分享一下导入Excel文件的方法。 首先我们得准备一个模板&#xff0c;下图就是一个简单的EX导入模板&#xff0c; 下图就是一个导入模板&#xff0c; 我们先把导入模板写好&#xff0c; 还有一个…
阅读更多...
两种方式导入excel

两种方式导入excel

第一种 easyExcel pom文件导入 com.alibaba easyexcel 2.2.3 然后 /** * 导入用户excel * param * return */ PostMapping(“manage/imporAcc”) Message<?> imporAcc(RequestParam(“accountFile”) MultipartFile file,RequestParam(“role”)String role,Request…
阅读更多...
Java实现Excel导入和导出，看这一篇就够了(珍藏版)

Java实现Excel导入和导出，看这一篇就够了(珍藏版)

目录 目录 前言 1. 功能测试 1.1 测试准备 1.2 数据导入 1.2.1 导入解析为JSON 1.2.2 导入解析为对象&#xff08;基础&#xff09; 1.2.3 导入解析为对象&#xff08;字段自动映射&#xff09; 1.2.4 导入解析为对象&#xff08;获取行号&#xff09; 1.2.5 导入解析…
阅读更多...
常见机器学习面试题

常见机器学习面试题

参考&#xff1a;http://kubicode.me/2015/08/16/Machine%20Learning/Common-Interview/?fromsinglemessage# http://blog.csdn.NET/heyongluoyao8/article/details/49429629 http://lib.csdn.Net/article/machinelearning/33798 http://www.cnblogs.com/zuochongyan/p/540705…
阅读更多...
机器学习面试题60~100

机器学习面试题60~100

61.说说梯度下降法 LeftNotEasy&#xff0c;本题解析来源&#xff1a;http://www.cnblogs.com/LeftNotEasy/archive/2010/12/05/mathmatic_in_machine_learning_1_regression_and_gradient_descent.html 下面是一个典型的机器学习的过程&#xff0c;首先给出一个输入数据&#…
阅读更多...
深度学习机器学习面试题汇——模型优化，轻量化，模型压缩

深度学习机器学习面试题汇——模型优化，轻量化，模型压缩

深度学习机器学习面试题汇——模型优化&#xff0c;轻量化&#xff0c;模型压缩 提示&#xff1a;互联网大厂可能考的面试题 若CNN网络很庞大&#xff0c;在手机上运行效率不高&#xff0c;对应模型压缩方法有了解吗 介绍一下模型压缩常用的方法&#xff1f;为什么用知识蒸馏&…
阅读更多...
推荐文章

Copyright @ 2022~2023