通过EasyExcel+线程池实现百万级数据从Excel导入到数据库
通过EasyExcel+线程池实现百万级数据从Excel导入到数据库
·
EasyExcel的优缺点
优点：
- 高效性：EasyExcel采用零反射、零注解的方式读写Excel文件，这使得它在处理大型Excel文件时具有出色的性能。此外，它采用了高效的解析算法，能够快速读取和解析文件内容。
- 低内存占用：与传统的Excel文件读取方式相比，EasyExcel显著降低了内存占用。它采用基于事件驱动的模型，通过回调函数来处理每一行数据，而不是一次性将整个文件读入内存。这种流式的处理方式极大地节省了内存资源，使得处理大文件时更加稳定可靠。
- 支持多种文件格式：EasyExcel支持多种Excel文件格式，包括.xls、.xlsx、.xlsm等，这使得它在处理不同版本的Excel文件时具有更大的灵活性。
- 易用性：EasyExcel提供了简单易用的API，使得开发者能够轻松地实现Excel文件的读写操作。它支持读写多种数据类型，如基本类型、集合、自定义对象等，满足了开发者在处理复杂数据时的需求。
- 错误处理与稳定性：在处理大文件时，数据的一致性和完整性至关重要。EasyExcel提供了良好的错误处理机制，能够在读取过程中有效地识别和处理异常情况，确保数据的准确性和稳定性。
缺点：
特殊格式和功能的限制：EasyExcel可能无法完全支持Excel文件中的所有特殊格式和功能。例如，它可能无法正确解析某些复杂的公式、图表或特殊的数据验证规则。这可能导致在读取某些特定格式的Excel文件时出现问题。
对于大文件的写入性能：虽然EasyExcel在读取大文件时表现优秀，但在写入大文件时可能会遇到性能瓶颈。尤其是在处理大量数据并需要频繁写入到Excel文件时，可能会导致写入速度变慢，甚至可能出现超时或内存问题。
并发处理能力有限：EasyExcel在处理高并发请求时可能存在一定的问题。当多个线程或进程同时尝试读取或写入同一个Excel文件时，可能会出现数据冲突或不一致的情况。尽管可以通过一些同步机制来避免这些问题，但这可能会增加系统的复杂性和开销。
依赖环境和版本兼容性：EasyExcel的性能和稳定性可能受到Java环境、操作系统版本或其他依赖库的影响。如果环境配置不当或存在版本不兼容的问题，可能会导致读取大文件时出现异常或错误。
错误处理和日志记录不足：在某些情况下，EasyExcel可能无法提供足够的错误处理和日志记录功能。当遇到复杂的数据结构或格式问题时，可能难以快速定位和解决问题。这可能会增加开发和维护的难度。
具体实现
Maven依赖
<!--EasyExcel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
<!-- 数据库连接和线程池 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.24</version>
</dependency>
import com.alibaba.excel.EasyExcel;
import com.demo.importExcel.entity.User;
import com.demo.importExcel.listener.DataReadListener;
import com.demo.importExcel.mapper.ImportExcelMapper;
import com.demo.importExcel.service.IDataBaseService;
import com.demo.importExcel.service.IImportExcelService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;
@Service
public class ImportExcelServiceImpl implements IImportExcelService {
@Autowired
private IDataBaseService dataBaseService;
/**
* 导入Excel数据实现
*/
@Override
public void importExcel() {
long startTime = System.currentTimeMillis();
//Excel路径
String path = "D://exportExcel.xlsx";
//读取sheet的数量
int numberSheet = 20;
//创建一个固定大小的线程池，大小和sheet数量一样
ExecutorService executor = Executors.newFixedThreadPool(numberSheet);
//遍历所有sheet
for (int i = 0; i < numberSheet; i++) {
//lambda表达式中的变量必须是final的
int sheetNumber = i;
//向线程池提交任务
executor.submit(()->{
//使用EasyExcel获取相对于sheet数据
EasyExcel.read(path, User.class,new DataReadListener(dataBaseService))
.sheet(sheetNumber)//sheet数
.doRead();//开始读取数据
});
}
//线程池关闭
executor.shutdown();
//等待所以任务完成读取操作
try {
executor.awaitTermination(Long.MAX_VALUE, TimeUnit.NANOSECONDS);
} catch (InterruptedException e) {
throw new RuntimeException(e);
}
long endTime = System.currentTimeMillis();
System.out.println("导入时长：" + String.valueOf(endTime-startTime));
}
}
实现并发读取多个sheet代码：
import com.alibaba.excel.EasyExcel;
import com.demo.importExcel.entity.User;
import com.demo.importExcel.listener.DataReadListener;
import com.demo.importExcel.mapper.ImportExcelMapper;
import com.demo.importExcel.service.IDataBaseService;
import com.demo.importExcel.service.IImportExcelService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;
@Service
public class ImportExcelServiceImpl implements IImportExcelService {
@Autowired
private IDataBaseService dataBaseService;
/**
* 导入Excel数据实现
*/
@Override
public void importExcel() {
long startTime = System.currentTimeMillis();
//Excel路径
String path = "D://exportExcel.xlsx";
//读取sheet的数量
int numberSheet = 20;
//创建一个固定大小的线程池，大小和sheet数量一样
ExecutorService executor = Executors.newFixedThreadPool(numberSheet);
//遍历所有sheet
for (int i = 0; i < numberSheet; i++) {
//lambda表达式中的变量必须是final的
int sheetNumber = i;
//向线程池提交任务
executor.submit(()->{
//使用EasyExcel获取相对于sheet数据
EasyExcel.read(path, User.class,new DataReadListener(dataBaseService))
.sheet(sheetNumber)//sheet数
.doRead();//开始读取数据
});
}
//线程池关闭
executor.shutdown();
//等待所以任务完成读取操作
try {
executor.awaitTermination(Long.MAX_VALUE, TimeUnit.NANOSECONDS);
} catch (InterruptedException e) {
throw new RuntimeException(e);
}
long endTime = System.currentTimeMillis();
System.out.println("导入时长：" + String.valueOf(endTime-startTime));
}
}
先定义sheet的数量和固定大小的线程池数量，每个sheet页做为单独的任务交给线程池处理。定义了DataReadListener，这个类是ReadListener的实现类。当EasyExcel每读取一行数据都会调用invoke方法，在invoke()中可以做我们自己的逻辑处理
以下是DataReadListener：
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.demo.importExcel.entity.User;
import com.demo.importExcel.mapper.ImportExcelMapper;
import com.demo.importExcel.service.IDataBaseService;
import com.demo.importExcel.service.IImportExcelService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.context.annotation.Import;
import java.util.ArrayList;
import java.util.List;
//自定义监听器，处理读取到的Excel数据
@Slf4j
public class DataReadListener implements ReadListener<User> {
private IDataBaseService dataBaseService;
/**
* 每次批量插入数据的数量
*/
private static final int batchSize = 1000;
/**
* 用于暂存数据的集合，直到数量等于batchSize时就会进行插入操作并清空集合
*/
private List<User> batchList = new ArrayList();
/**
* 注入mapper
* @param mapper
*/
public DataReadListener(IDataBaseService dataBaseService) {
this.dataBaseService = dataBaseService;
}
//EasyExcel每读取一行数据就会执行一次
@Override
public void invoke(User user, AnalysisContext analysisContext) {
log.info("读取到的行数据：{}",user);
if(validateData(user)){
batchList.add(user);
}else {
//没有通过校验的数据，打印日志
log.error("id为[{}]的数据没有通过校验",user.getId());
}
//如果集合数量大于设置的批量数量，那么就插入数据并清空集合
if(batchList.size() >= batchSize){
dataBaseService.batchInsert(batchList);
batchList.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
log.info("Excel读取完成！");
//如果还有数据就一起插入到数据库中
if(!batchList.isEmpty()){
dataBaseService.batchInsert(batchList);
}
}
/**
* 数据校验
* @param user
* @return
*/
private boolean validateData(User user){
int userCount = dataBaseService.findUserById(user.getId());
//判断是否存在数据库中
if(userCount == 0){
return true;
}
//处理其他逻辑校验........
return false;
}
}
通过自定义的DataReadListener，我们就可以在读取Excel的时候做处理。
每读取到一行数据会先做数据校验，如果校验通过后就会放到缓存集合中，List数量积累到1000时就会通过Mybatis的批量操作进行数据插入。doAfterAllAnalysed方法会在读取Excel完成后进行调用
MyBatis批量操作：
@Mapper
public interface ImportExcelMapper {
/**
* 批量导入数据
* @param dataList
*/
void batchInsertData(List<User> dataList);
/**
* 查询用户
* @return
*/
List<User> findAllUser();
/**
* 根据id查询用户
* @param id
* @return
*/
int findUserById(String id);
}
ImportExcel.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.demo.importExcel.mapper.ImportExcelMapper">
<insert id="batchInsertData" parameterType="list">
INSERT INTO t_user (id, name,create_time)
VALUES
<foreach collection="list" item="user" separator=",">
(#{user.id}, #{user.name},#{user.createTime})
</foreach>
</insert>
<select id="findAllUser" resultType="com.demo.importExcel.entity.User">
select * from t_user
</select>
<select id="findUserById" resultType="int">
select count(id) from t_user where id=#{id}
</select>
</mapper>
在CSDN上，一键三连是对作者辛勤创作的最好鼓励！喜欢我的文章，就请点赞、收藏、转发吧！你们的支持是我持续分享知识的动力，感谢大家的陪伴与认可！💖🔝🔄
腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。
更多推荐
74
0- 0
已为社区贡献1条内容
所有评论(0)