当前位置: 首页 > news >正文

【实战】EasyExcel实现百万级数据导入导出

文章目录

    • 前言
    • 技术积累
    • 实战演示
      • 实现思路
      • 模拟代码
      • 测试结果

在这里插入图片描述

前言

最近接到一个百万级excel数据导入导出的需求,大概就是我们在进行公众号API群发的时候,需要支持500w以上的openid进行群发,并且可以提供发送openid数据的导出功能。可能有的同学会说,这么大的数据量发送为啥不用标签发送呢。哈哈,标签发送需要提前打标签微信限制50个一批,我们开10个线程也是需要3个小时左右才能打完,这样肯定不能满足客户需求。如果用openid群发就不一样了,微信支持10000个每批,基本上我开5个线程同时发送差不多几分钟搞定。所以,问题就来到了百万级excel数据的导入与导出啦。

技术积累

EasyExcel是什么
EasyExcel是一个基于Java的、快速、简洁、解决大文件内存溢出的Excel处理工具。他能让你在不用考虑性能、内存的等因素的情况下,快速完成Excel的读、写等功能。

作为一个资深的搬砖人,秉承着能够用CV大法,绝不自己造轮子的原则,我肯定选择这个阿里开源的excel读写工具来开发功能。

使用案例
对于excel的读、写、填充都有简单的案例,有兴趣的同学可以自己去看,这里不再重复叙述。
https://easyexcel.opensource.alibaba.com/docs/current/quickstart/read

实战演示

相信有很多的同学都使用过Easyexcel这个开源中间件,基本上很多的管理系统项目做到导出导入功能都会使用这个中间件。按照目前使用的情况来看,这个中间件还是比较稳定的,而且这个开源社区的活跃度也是比较高,基本上很难遇到不能解决的问题。

对于简单的excel导入导出我们直接安装Easyexcel提供的Demo就能够完美搞定,但是遇到比较大的数据量的时候就需要我们特殊处理下业务逻辑了。比如今天的重点百万级数据的导入导出。

实现思路

1、由于Easyexcel读功能是对excel一行一行进行读的,这是为了保证不过多占用我们内存。如果我们系统需要对数据进行入库的话则需要对数据进行缓存,比如1w每批次入库。虽然会损失一定的内存,但是写库时间大大降低了呀;
2、如果传入的excel有多个sheet,可以考虑开启多个线程进行读excel。比如每个sheet一个线程,但是线程需要做好管理,如使用线程池等等。但是,一般大数据量都不使用excel来保存,而是使用csv来储存数据,因为这个格式简单、体积小、易于使用、可被多种软件打开和编辑。当然,Easyexel也是可以读取csv文件的,但因为要兼容csv文件就不采用多sheet的方式,因为csv没有sheet。

EasyExcel.read(filePath, Object.class, new PageReadListener<Object>(dataList ->{//TODO 数据处理,默认读取0号sheet
}, 10000)).sheet().doRead();

3、excel导出目前Easyexcel最新版本是不支持多线程写数据的,只能单线程进行写excel。为了保证写excel效率,我采用20w数据一批一次写入excel。
4、由于excel数据行数超过100w打开时间特别长,所以我们在导出的时候对数据进行切割,每个sheet最多只保存100w数据,其他数据写入下个sheet。
5、为了保证我们每批次可以写入20w数据到excel,那么,我必须保证能够用最短的时间从数据库抓取20w数据。这里我们可以采用多线程每个线程去拉5w条,开启4个线程足够,然后用countdownlatch进行多线程处理。当然,如果内存足够可以一次从数据库拉取20w数据,其实也不大最多也才几兆而已。

//需要导出的总数量
int total = count(*)
//每次读20w数据
int readNum = 20 * 10000;
//每个sheet总数据
int sheetDataNum = 100 * 10000;
//需要写入sheetNum
int sheetNum = total % sheetDataNum == 0 ? total / sheetDataNum : (total / sheetDataNum)+1;
//计算每个线程查询数据库次数
int queryNum = sheetDataNum / readNum;
//最后一个线程查询数据库次数
int lastQueryNum = total % sheetDataNum == 0 ? queryNum: (total % sheetDataNum % readNum == 0 ? (total % sheetDataNum / readNum) : (total % sheetDataNum / readNum + 1));
//导出逻辑
for (int i = 0; i < sheetNum; i++) {final int finalI = i;new Runnable() {@Overridepublic void run() {//查询数据数据for (int j = 0; j < ((finalI < sheetNum -1) ? queryNum : lastQueryNum); j++) {//查询数据库int page = j+1+finalI * sheetDataNum;int pageSize = readNum;//TODO 调用数据库查询//TODO 写excel}}};
}

模拟代码

数据库创建一个公众号用户表

-- 创建一个缓存openid的数据库
drop table if exists mp_user;
create table mp_user(id bigint   not null auto_increment comment 'ID',openid varchar(64) not null comment 'openid',deleted bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',primary key (`id`) using btree
) engine = innodb default charset=utf8mb4 comment '公众号粉丝';

引入maven依赖

<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.3.3</version>
</dependency>
<dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.3.0</version>
</dependency>
<dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.33</version>
</dependency>

application配置数据库

spring:datasource:url: jdbc:mysql://localhost:3306/cce-demo?serverTimezone=GMT%2B8&autoReconnect=false&useUnicode=true&characterEncoding=UTF-8&characterSetResults=UTF-8&zeroDateTimeBehavior=convertToNull&useSSL=trueusername: rootpassword: 12345678driver-class-name: com.mysql.cj.jdbc.Driver# MyBatis Plus 的配置项
mybatis-plus:configuration:map-underscore-to-camel-case: true # 虽然默认为 true ,但是还是显示去指定下。log-impl: org.apache.ibatis.logging.stdout.StdOutImpl #开启sql日志global-config:db-config:id-type: NONElogic-delete-value: 1 # 逻辑已删除值(默认为 1)logic-not-delete-value: 0 # 逻辑未删除值(默认为 0)banner: false # 关闭控制台的 Banner 打印type-aliases-package: com.example.ccedemo.entitymapper-locations: classpath:/mapper/*.xml    

MpUser实体和excel类

/*** MpUser* @author senfel* @version 1.0* @date 2024/7/1 16:17*/
@TableName("mp_user")
@KeySequence("mp_user_seq")
@Data
@ToString(callSuper = true)
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class MpUser implements Serializable {@TableIdprivate Long id;/*** openid*/private String openid;private Boolean deleted;}
/*** openId Excel 导入 VO* @author senfel*/
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = false)
public class OpenIdImportExcelVo {/*** 用户OpenId*/@ExcelProperty(index = 0)private String openid;
}MpUserMapper
/*** MpUserMapper* @author senfel* @version 1.0* @date 2024/7/1 16:23*/
@Mapper
public interface MpUserMapper extends BaseMapper<MpUser> {/*** insertBatch* @param list* @author senfel* @date 2024/7/1 17:16* @return int*/int insertBatch(List<OpenIdImportExcelVo> list);/*** selectDataByPage* @param offset* @param size* @author senfel* @date 2024/7/1 17:16* @return java.util.List<java.lang.String>*/List<OpenIdImportExcelVo> selectDataByPage(int offset, int size);
}

MpUserMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.ccedemo.mapper.MpUserMapper"><!--批量新增openid--><insert id="insertBatch" parameterType="com.example.ccedemo.excel.OpenIdImportExcelVo">insert into mp_user (openid) values<foreach collection="list" item="item" separator=",">(#{item.openid})</foreach></insert><!--查询分页数据--><select id="selectDataByPage" parameterType="map" resultType="com.example.ccedemo.excel.OpenIdImportExcelVo">select openid from mp_userlimit #{offset},#{size}</select></mapper>

提供测试类

/*** EasyExcelTest* @author senfel* @version 1.0* @date 2024/7/1 16:03*/
@SpringBootTest
public class EasyExcelTest {@Resourceprivate MpUserMapper mpUserMapper;/*** readExcel* @author senfel* @date 2024/7/1 17:17* @return void*/@Testpublic void readExcel(){//800w+的csv文件,每批次读取10000条long startTime = System.currentTimeMillis();System.err.println("readExcel开始执行时间:"+startTime);String filePath = "D:\\blank\\工作簿1.csv";EasyExcel.read(filePath, OpenIdImportExcelVo.class, new PageReadListener<OpenIdImportExcelVo>(dataList ->{if(!CollectionUtils.isEmpty(dataList)){//数据存储mpUserMapper.insertBatch(dataList);}}, 10000)).sheet().doRead();System.err.println("readExcel结束执行时间:"+(System.currentTimeMillis()-startTime));}/*** exportExcel* @author senfel* @date 2024/7/1 17:19* @return void*/@Testpublic void exportExcel(){long startTime = System.currentTimeMillis();System.err.println("exportExcel:"+startTime);String excelName = "测试导出openid";String exportPath = "D:\\blank\\";boolean isRun = true;int size = 20 * 10000;int page = 0;int sheetDataSize = 0;int sheetNo = 0;FileOutputStream outputStream = null;try {outputStream = new FileOutputStream(exportPath + excelName + ".xlsx");ExcelWriter excelWriter = EasyExcel.write(outputStream).build();do {page++;List<OpenIdImportExcelVo> openList = mpUserMapper.selectDataByPage((page - 1) * size, size);if(CollectionUtils.isEmpty(openList)){isRun = false;break;}sheetDataSize += openList.size();if(sheetDataSize > 1000000){sheetNo++;sheetDataSize = openList.size();}//写入文件流WriteSheet writeSheet = EasyExcel.writerSheet(sheetNo, "openid"+sheetNo).head(OpenIdImportExcelVo.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();excelWriter.write(openList, writeSheet);}while (isRun);excelWriter.finish();outputStream.flush();}catch (IOException e){e.printStackTrace();}finally {if (outputStream != null) {try {outputStream.close();} catch (IOException e) {e.printStackTrace();}}System.err.println("exportExcel结束执行时间:"+(System.currentTimeMillis()-startTime));}}
}

测试结果

导入结果
在这里插入图片描述

83s导入存入数据库 838w数据,如果改为原生的JDBC操作入库会更快。

导出结果
在这里插入图片描述

77s导出写入excel 838w数据,写excel不建议多线程。如果受到内存限制查询条数低于20w可以考虑多线程执行,但是写excel必须单线程。

如果需要导出到响应头HttpServletResponse

public void exportExcel2(HttpServletResponse response){long startTime = System.currentTimeMillis();String excelName = "测试导出openid";boolean isRun = true;int size = 20 * 10000;int page = 0;int sheetDataSize = 0;int sheetNo = 0;OutputStream outputStream = null;try {outputStream = response.getOutputStream();ExcelWriter excelWriter = EasyExcel.write(outputStream).build();do {page++;List<OpenIdImportExcelVo> openList = mpUserMapper.selectDataByPage((page - 1) * size, size);if(CollectionUtils.isEmpty(openList)){isRun = false;break;}sheetDataSize += openList.size();if(sheetDataSize > 1000000){sheetNo++;sheetDataSize = openList.size();}//写入文件流WriteSheet writeSheet = EasyExcel.writerSheet(sheetNo, "openid"+sheetNo).head(OpenIdImportExcelVo.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();excelWriter.write(openList, writeSheet);}while (isRun);// 下载EXCELresponse.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");String fileName = URLEncoder.encode(excelName, "UTF-8").replaceAll("\\+", "%20");response.setHeader("Content-disposition", "attachment;filename*=" + fileName + ".xlsx");excelWriter.finish();outputStream.flush();}catch (IOException e){e.printStackTrace();throw new RuntimeException("exportExcel异常,具体信息为:"+e.getMessage());}finally {if (outputStream != null) {try {outputStream.close();} catch (IOException e) {e.printStackTrace();}}System.err.println("exportExcel结束执行时间:"+(System.currentTimeMillis()-startTime));}
}

相关文章:

【实战】EasyExcel实现百万级数据导入导出

文章目录 前言技术积累实战演示实现思路模拟代码测试结果 前言 最近接到一个百万级excel数据导入导出的需求&#xff0c;大概就是我们在进行公众号API群发的时候&#xff0c;需要支持500w以上的openid进行群发&#xff0c;并且可以提供发送openid数据的导出功能。可能有的同学…...

Graalvm配置文件与Feature和Substitute机制介绍

GraalVM介绍 GraalVM提前将Java应用程序编译成独立与机器码二进制文件&#xff08;可执行文件、动态库文件&#xff09;,如windows系统中的exe文件和dll文件。与在Java虚拟机&#xff08;JVM&#xff09;上运行的应用程序相比&#xff0c;这些二进制文件更小&#xff0c;启动速…...

Appium adb 获取appActivity

方法一&#xff08;最简单有效的方法&#xff09; 通过cmd命令&#xff0c;前提是先打开手机中你要获取包名的APP adb devices -l 获取连接设备详细信息 adb shell dumpsys activity | grep mFocusedActivity 有时获取到的不是真实的Activity 方法二 adb shell monkey -p …...

调整分区失败致盘无法访问:深度解析与数据恢复全攻略

调整分区失败盘打不开的困境 在计算机的日常维护与管理中&#xff0c;调整磁盘分区是常见的操作之一&#xff0c;旨在优化存储空间布局、提升系统性能或满足特定应用需求。然而&#xff0c;当这一操作未能如预期般顺利进行&#xff0c;反而导致分区调整失败&#xff0c;进而使…...

试用笔记之-汇通计算机等级考试软件一级Windows

首先下载汇通计算机等级考试软件一级Windows http://www.htsoft.com.cn/download/htwork.rar...

Java的NIO体系

目录 NIO1、操作系统级别下的IO模型有哪些&#xff1f;2、Java语言下的IO模型有哪些&#xff1f;3、Java的NIO应用场景&#xff1f;相比于IO的优势在哪&#xff1f;4、Java的IO、NIO、AIO 操作文件读写5、NIO的核心类 :Buffer&#xff08;缓冲区&#xff09;、Channel&#xff…...

自下而上的选股与自上而下的选股

一起学习了《战胜华尔街》&#xff0c;不知道大家有没有这么一种感受&#xff1a;林奇的选股方法是典型的自下而上的选股方法。虽然这一点没有单独拎出来讨论过&#xff0c;但在《从低迷中寻找卓越》《如何通过财务指标筛选股票&#xff1f;》《边逛街边选股&#xff1f;》《好…...

Tech Talk:智能电视eMMC存储的五问五答

智能电视作为搭载操作系统的综合影音载体&#xff0c;以稳步扩大的市场规模走入越来越多的家庭&#xff0c;成为人们生活娱乐的重要组成部分。存储部件是智能电视不可或缺的组成部分&#xff0c;用于保存操作系统、应用程序、多媒体文件和用户数据等信息。智能电视使用eMMC作为…...

scikit-learn教程

scikit-learn&#xff08;通常简称为sklearn&#xff09;是Python中最受欢迎的机器学习库之一&#xff0c;它提供了各种监督和非监督学习算法的实现。下面是一个基本的教程&#xff0c;涵盖如何使用sklearn进行数据预处理、模型训练和评估。 1. 安装和导入包 首先确保安装了…...

CentOS 7 搭建rsyslog日志服务器

CentOS 7 搭建rsyslog日志服务器 前言一、IP地址及主机名称规划1.修改主机名 二、配置rsyslog日志服务器1.安装rsyslog服务2.编辑/etc/rsyslog.conf 文件3.启动并启用rsyslog服务4.验证端口是否侦听 三、在rsyslog日志服务器上配置firewalld防火墙四、配置rsyslog日志客户端1.编…...

使用Spring Boot Actuator监控应用健康状态

使用Spring Boot Actuator监控应用健康状态 大家好&#xff0c;我是免费搭建查券返利机器人省钱赚佣金就用微赚淘客系统3.0的小编&#xff0c;也是冬天不穿秋裤&#xff0c;天冷也要风度的程序猿&#xff01;今天我们将探讨如何利用Spring Boot Actuator来监控和管理应用程序的…...

leetcode刷题:vector刷题

​ ​ &#x1f525;个人主页&#xff1a;guoguoqiang. &#x1f525;专栏&#xff1a;leetcode刷题 1.只出现一次的数字 这道题很简单&#xff0c;我们只需要遍历一次数组即可通过异或运算实现。(一个数与自身异或结果为0&#xff0c;任何数与0异或还是它本身) class Solut…...

CGI面试题及参考答案

什么是CGI?它在Web服务器与应用程序之间扮演什么角色? CGI(Common Gateway Interface) 是一种标准协议,它定义了Web服务器与运行在服务器上的外部程序(通常是脚本或应用程序)之间的通信方式。简单来说,CGI充当了一个桥梁,使得Web服务器能够将用户的请求传递给后端程序…...

论文调研_物联网漏洞检测综述

A Review of IoT Firmware Vulnerabilities and Auditing Techniques 研究背景&#xff1a;物联网设备在工业、消费类等各个领域得到了广泛应用&#xff0c;实现了更高的自动化和生产率。然而&#xff0c;这些连网设备的高度依赖也带来了一系列网络安全威胁&#xff0c;特别是…...

Java学习【IO流:深入理解与应用(上)】

Java学习【IO流&#xff1a;深入理解与应用&#xff08;上&#xff09;】 &#x1f343;1.IO流体系结构&#x1f343;2.FileOutputStream&#x1f341;2.1FileOutputStream写数据的三种方式&#x1f341;2.2换行和续写 &#x1f343;3.FileInputStream&#x1f341;3.1每次读取…...

干货系列:SpringBoot3第三方接口调用10种方式

环境&#xff1a;SpringBoot.3.3.0 1、简介 在项目中调用第三方接口是日常开发中非常常见的。调用方式的选择通常遵循公司既定的技术栈和架构规范&#xff0c;以确保项目的一致性和可维护性。无论是RESTful API调用、Feign声明式HTTP客户端、Apache HttpClient等调用方式&…...

KVM性能优化之CPU优化

1、查看kvm虚拟机vCPU的QEMU线程 ps -eLo ruser,pid,ppid,lwp,psr,args |awk /^qemu/{print $1,$2,$3,$4,$5,$6,$8} 注:vcpu是不同的线程&#xff0c;而不同的线程是跑在不同的cpu上&#xff0c;一般情况&#xff0c;虚拟机在运行时自身会点用3个cpus&#xff0c;为保证生产环…...

lua中判断2个表是否相等

当我们获取 table 长度的时候无论是使用 # 还是 table.getn 其都会在索引中断的地方停止计数&#xff0c;而导致无法正确取得 table 的长度&#xff0c;而且还会出现奇怪的现象。例如&#xff1a;t里面有3个元素&#xff0c;但是因为最后一个下表是5和4&#xff0c;却表现出不一…...

uni-app 自定义支付密码键盘

1.新建组件 payKeyboard .vue <template><view class"page-total" v-show"isShow"><view class"key-list"><view class"list" v-for"(item,index) in keyList" :class"{special:item.keyCode190…...

抖音微短剧小程序源码搭建:实现巨量广告数据高效回传

在数字化营销日益盛行的今天&#xff0c;抖音微短剧小程序已成为品牌与观众互动的新渠道。这些短小精悍的剧目不仅能迅速抓住用户的注意力&#xff0c;还能有效提升品牌的知名度和用户黏性。然而&#xff0c;想要充分利用这一营销工具&#xff0c;关键在于如何高效地追踪广告数…...

手游刚开服就被攻击怎么办?如何防御DDoS?

开服初期是手游最脆弱的阶段&#xff0c;极易成为DDoS攻击的目标。一旦遭遇攻击&#xff0c;可能导致服务器瘫痪、玩家流失&#xff0c;甚至造成巨大经济损失。本文为开发者提供一套简洁有效的应急与防御方案&#xff0c;帮助快速应对并构建长期防护体系。 一、遭遇攻击的紧急应…...

React hook之useRef

React useRef 详解 useRef 是 React 提供的一个 Hook&#xff0c;用于在函数组件中创建可变的引用对象。它在 React 开发中有多种重要用途&#xff0c;下面我将全面详细地介绍它的特性和用法。 基本概念 1. 创建 ref const refContainer useRef(initialValue);initialValu…...

安宝特方案丨XRSOP人员作业标准化管理平台:AR智慧点检验收套件

在选煤厂、化工厂、钢铁厂等过程生产型企业&#xff0c;其生产设备的运行效率和非计划停机对工业制造效益有较大影响。 随着企业自动化和智能化建设的推进&#xff0c;需提前预防假检、错检、漏检&#xff0c;推动智慧生产运维系统数据的流动和现场赋能应用。同时&#xff0c;…...

使用分级同态加密防御梯度泄漏

抽象 联邦学习 &#xff08;FL&#xff09; 支持跨分布式客户端进行协作模型训练&#xff0c;而无需共享原始数据&#xff0c;这使其成为在互联和自动驾驶汽车 &#xff08;CAV&#xff09; 等领域保护隐私的机器学习的一种很有前途的方法。然而&#xff0c;最近的研究表明&…...

屋顶变身“发电站” ,中天合创屋面分布式光伏发电项目顺利并网!

5月28日&#xff0c;中天合创屋面分布式光伏发电项目顺利并网发电&#xff0c;该项目位于内蒙古自治区鄂尔多斯市乌审旗&#xff0c;项目利用中天合创聚乙烯、聚丙烯仓库屋面作为场地建设光伏电站&#xff0c;总装机容量为9.96MWp。 项目投运后&#xff0c;每年可节约标煤3670…...

论文浅尝 | 基于判别指令微调生成式大语言模型的知识图谱补全方法(ISWC2024)

笔记整理&#xff1a;刘治强&#xff0c;浙江大学硕士生&#xff0c;研究方向为知识图谱表示学习&#xff0c;大语言模型 论文链接&#xff1a;http://arxiv.org/abs/2407.16127 发表会议&#xff1a;ISWC 2024 1. 动机 传统的知识图谱补全&#xff08;KGC&#xff09;模型通过…...

【Oracle】分区表

个人主页&#xff1a;Guiat 归属专栏&#xff1a;Oracle 文章目录 1. 分区表基础概述1.1 分区表的概念与优势1.2 分区类型概览1.3 分区表的工作原理 2. 范围分区 (RANGE Partitioning)2.1 基础范围分区2.1.1 按日期范围分区2.1.2 按数值范围分区 2.2 间隔分区 (INTERVAL Partit…...

项目部署到Linux上时遇到的错误(Redis,MySQL,无法正确连接,地址占用问题)

Redis无法正确连接 在运行jar包时出现了这样的错误 查询得知问题核心在于Redis连接失败&#xff0c;具体原因是客户端发送了密码认证请求&#xff0c;但Redis服务器未设置密码 1.为Redis设置密码&#xff08;匹配客户端配置&#xff09; 步骤&#xff1a; 1&#xff09;.修…...

Rust 开发环境搭建

环境搭建 1、开发工具RustRover 或者vs code 2、Cygwin64 安装 https://cygwin.com/install.html 在工具终端执行&#xff1a; rustup toolchain install stable-x86_64-pc-windows-gnu rustup default stable-x86_64-pc-windows-gnu ​ 2、Hello World fn main() { println…...

Python常用模块:time、os、shutil与flask初探

一、Flask初探 & PyCharm终端配置 目的: 快速搭建小型Web服务器以提供数据。 工具: 第三方Web框架 Flask (需 pip install flask 安装)。 安装 Flask: 建议: 使用 PyCharm 内置的 Terminal (模拟命令行) 进行安装,避免频繁切换。 PyCharm Terminal 配置建议: 打开 Py…...