EasyExcel导入、导出(百万数据量,生产环境已测)
需求:测试EasyExcel的导入导出使用、性能,测试数据量十个字段100万条数据;
前言
这个是实战优化,数据层用的是es+mongdb,不过其他框架的同学可以直接使用,只需要稍微改一下就行,需要改动的地方我已经通过注释标明
一、导出实现
1、pom.xml文件
主要依赖是:
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.10</version>
</dependency>
2、yml文件(导入、导出共用代码)
spring:
devtools:
restart:
enabled: true #设置开启热部署
additional-paths: src/main/java #重启目录
exclude: WEB-INF/**
freemarker:
cache: false #页面不加载缓存,修改即时生效
servlet:
multipart: #这里必须要加这配置,不然一百万条数据文件太大会拒绝导入
max-request-size: 100MB
max-file-size: 100MB
3、共用的代码文件
创建一个类对象类名与字段名根据自身业务需要定义
,用来接受接收需要导出的字段,通过@ExcelProperty(value = "列名", index = 排列顺序)
注解来反射接收需要写出的值
/**
* @className: UserAddressBookExportExcel
* @description: 用户通讯录导出类
* @author: 路过人间的姜先生
* @date: 2022/7/25
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class UserAddressBookExportExcel {
@ExcelProperty(value = "用户姓名", index = 0)
private String userName;
@ExcelProperty(value = "用户手机号", index = 1)
private String userPhoneNumber;
@ExcelProperty(value = "好友备注", index = 2)
private String friendRemarks;
@ExcelProperty(value = "好友手机号", index = 3)
private String friendPhoneNumber;
/** 好友是否注册 0=>没有注册 1=>有注册 */
@ExcelProperty(value = "好友注册状态", index = 4)
private String isRegister;
/** 是否是好友 0=>不是好友 1=>是好友 */
@ExcelProperty(value = "双方关系", index = 5)
private String isFriend;
/** 邀请通讯录好友的时间 */
@ExcelProperty(value = "邀请时间", index = 6)
private String sendnInvitationTime;
}
4、excel导出
/**
* 我们公司的框架用的是es+mongdb做的数据层,
* 分页也许和使用mybatis的同学们不同,不过本质上的逻辑都是一样,
* 核心都是循环分页查询,然后拼接写入到excel中
*
* @description: 导出用户Excel
* @return: void
* @author: 路过人间的姜先生
* @date: 2022/5/19
*/
@ApiOperation(value = "导出用户通讯录", httpMethod = "GET")
@GetMapping(value = "/userAddressBook/exportExcel")
@AvoidRepeatableCommit
public void exportExcel(UserAddressBookQueryRequest queryRequest) {
// 分页查询,每次查询量1000
int pageNum = 1000;
ExcelWriter excelWriter = null;
try {
// 下方使用了用户自己选择文件保存路径的方式,所以需要配请求参数,如果使用固定路径可忽略此代码
String filename = URLEncoder.encode(System.currentTimeMillis() + ".xlsx", "UTF-8");
response.setCharacterEncoding("UTF-8");
// 设定输出文件头
response.setHeader("Content-disposition", "attachment; filename=" + filename);
// 定义输出类型
response.setContentType("application/x-xls");
// 这里采用用户自己选择文件保存路径的方式
OutputStream out = response.getOutputStream();
// 这里其实就是把上面的方法分开写,写入同一个sheet
excelWriter = EasyExcelFactory.write(out,UserAddressBookExportExcel.class).build();
WriteSheet writeSheet = EasyExcelFactory.writerSheet("用户通讯录").build();
// 重点是这里的循环调用---es的分页查询,先查询出需要导出的总数
//(用mybatis的同学这个方法的实现也是一样的,只需要定义一个条件查询所有的方法即可)
Integer count = userAddressBookService.count(queryRequest);
int num = (count / pageNum) + 1;
for (int i = 0; i < num; i++) {
// Elasticsearch分页查询,from+size默认分页方式;
Pageable pageable = new PageRequest(i, pageNum);
// 从数据库中查询到我要的通讯录实体,然后组装为我定义的UserAddressBookExportExcel的集合模型
List<UserAddressBookExportExcel> userAddressBookExportExcels =
userAddressBookService.exportExcel(queryRequest, pageable);
if (userAddressBookExportExcels.size() > 0) {
// 导出
excelWriter.write(userAddressBookExportExcels, writeSheet);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (excelWriter != null) {
excelWriter.finish();
}
}
}
5、涉及到的 Service层代码
/**
* 通讯录导出
* 该方法的入参可以根据你们自己框架的设计来定义,本博主由于用的是es,所以是这么写的,
* 还是那句话,代码不同,核心实现逻辑一致
* @param: queryRequest
* @param: pageable
* @return: void
* @author: 路过人间的姜先生
* @date: 2022/7/25
*/
@Override
public List<UserAddressBookExportExcel> exportExcel(
UserAddressBookQueryRequest queryRequest, Pageable pageable) {
//调用条件分页查询的方法,返回一个模型集合(此处用你们项目中自己的分页查询方法)
List<UserAddressBookDTO> dtoList = findAll(queryRequest, pageable).getContent();
// 下面的操作就是定义一个UserAddressBookExportExcel集合对象,遍历数据库中返回的集合对它赋值
List<UserAddressBookExportExcel> userAddressBookDTOS = new ArrayList<>();
dtoList.stream()
.forEach(
(userBoot -> {
UserAddressBookExportExcel build =
UserAddressBookExportExcel.builder()
.userName(
Objects.nonNull(userBoot.getUserDTO())
? userBoot.getUserDTO().getNickName()
: "用户已注销")
.userPhoneNumber(
Objects.nonNull(userBoot.getUserDTO())
? userBoot.getUserDTO().getPhoneNumber()
: "用户已注销")
.friendPhoneNumber(userBoot.getFriendPhoneNumber())
.friendRemarks(userBoot.getFriendRemarks())
.sendnInvitationTime(
Objects.nonNull(userBoot.getSendnInvitationTime())
? userBoot.getSendnInvitationTime().toString()
: LocalDate.now().toString())
.isRegister(
(Objects.nonNull(userBoot.getIsFriend())
&& Objects.equals(userBoot.getIsRegister(), 1))
? "该好友已注册"
: "未注册")
.isFriend(
(Objects.nonNull(userBoot.getIsFriend())
&& Objects.equals(userBoot.getIsFriend(), 1))
? "好友"
: "非好友")
.build();
userAddressBookDTOS.add(build);
}));
return userAddressBookDTOS;
}
二、导如实现
1、EasyExcel的工具类(最主要的文件,如果使用的其它框架,可以忽略上面的代码,注释会尽量写的详细一些)
package com.bug.util.excel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.CellExtra;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.fastjson.JSON;
import com.bug.entity.TestExcel;
import com.bug.service.excel.TestExcelService;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* 小bug
*/
/**
* 通讯录导出
* excel导入的工具类
* 是不是很奇怪这里是实现的ReadListener接口而不是继承的AnalysisEventListener类
* 因为源码里AnalysisEventListener类也是继承的ReadListener接口,何必多此一举呢
* @author: 路过人间的姜先生
* @date: 2022/7/25
*/
public class ExcelUpload implements ReadListener<UserAddressBookExportExcel> {
private UserAddressBookService userAddressBookService;
/**
* 注意这里是不能交给spring管理的,就是不能使用@Resource,或者@Autowired注入
* 可以使用构造方法的方式获取你想要的对象
* @param testExcelService testExcelService
*/
public ExcelUpload(UserAddressBookService userAddressBookService){
this.userAddressBookService = userAddressBookService;
}
/**
* 无参构造方法,不能省略
*/
public ExcelUpload(){}
public static final Logger log = LoggerFactory.getLogger(ExcelUpload.class);
private static final int count = 10000;//设置读取的条数,每次达到指定条数时就保存入数据库
private List<UserAddressBookExportExcel> exportExcelTrue = new ArrayList<>();//校验正确的数据集合,数量达到设定值后插入数据库,然后再清空一直循环
private List<UserAddressBookExportExcel> exportExcelFalse = new ArrayList<>();//校验失败、保存数据库失败的数据集合,可以插入到一个失败数据表,或者显示在前端提醒用户哪些数据导入失败
/**
* 很明显,onException这个就是用来处理异常的,当其它侦听器出现异常时会触发此方法
* @param e Exception
* @param analysisContext analysisContext
* Exception 默认是直接抛出异常的,要注意处理异常
*/
@Override
public void onException(Exception e, AnalysisContext analysisContext) {
log.info("兄嘚,你的代码出现异常了!");
e.printStackTrace();
}
/**
* 获取excel的第一行head数据
* @param map 数据map
* @param analysisContext analysisContext
*/
@Override
public void invokeHead(Map<Integer, CellData> map, AnalysisContext analysisContext) {
log.info("第一列:{} 第二列:{} 第三列:{}",map.get(0).getStringValue(),map.get(1).getStringValue(),map.get(2).getStringValue());
}
/**
* 读取正文数据,一次只读取一行
* @param testExcel 实体类对象
* @param analysisContext analysisContext
*/
@Override
public void invoke(UserAddressBookExportExcel exportExcel, AnalysisContext analysisContext) {
log.info("读取到一条数据:{}", JSON.toJSONString(exportExcel));
//因为是测试,这里只做一些简单的为空判断,正式的可以根据业务需求自己写校验条件
if(testExcel == null){//对象为空直接跳出
return;
}else if(StringUtils.isBlank(exportExcel.getUserName())){//判断名字是否为空
exportExcelFalse.add(exportExcel);//放入错误集合列表
return;
}else if(StringUtils.isBlank(testExcel.getUserPhoneNumber())){//判断电话是否为空
exportExcelFalse.add(testExcel);//放入错误集合列表
return;
}
exportExcelTrue.add(exportExcel);//校验通过的方法正确集合列表
if(count <= exportExcelTrue.size()){//集合数据大于设定的数量时,提交数据库保存
userAddressBookService.saveBatch(exportExcelTrue);//批量存入数据库
exportExcelTrue = new ArrayList<>();//清空正确列表数据,再次循环
}
}
/**
* 额外单元格返回的数据,这个方法还没详细了解过,一直没用到过
* @param cellExtra cellExtra对象
* @param analysisContext analysisContext
*/
@Override
public void extra(CellExtra cellExtra, AnalysisContext analysisContext) {
log.info("extra:{}",JSON.toJSONString(cellExtra));
}
/**
* 当读取完所有数据后就会执行次方法
* @param analysisContext analysisContext
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
log.info("兄嘚,所有数据读取完了哦!");
//读取完excel后再次判断是否还要未存入数据库的数据
if(exportExcelTrue.size() > 0){
userAddressBookService.saveBatch(exportExcelTrue);//不为空,则存入数据库
}
//这里也可以处理错误列表,保存入错误列表数据库,或者显示到前端给用户查看
}
/**
* 这个方法最坑,默认返回的是false,一定要记得改成true,为false时会只返回一条数据,
* 意思是只会执行invokeHead方法
* @param analysisContext analysisContext
* @return 是否读取下一行
*/
@Override
public boolean hasNext(AnalysisContext analysisContext) {
return true;
}
}
2、excel导入
/**
* 2、EasyExcel excel导入(百万级数据测试)
* @return
*/
@PostMapping("/submitExcel")
public void submitExcel(MultipartFile file){
log.info("开始导入excel。。。");
//创建reader
ExcelReader excelReader = null;
try {
excelReader = EasyExcel.read(file.getInputStream(), UserAddressBookExportExcel.class, new ExcelUpload(UserAddressBookService)).build();
// 构建sheet,可以指定是第几个sheet
ReadSheet readSheet = EasyExcel.readSheet(0).build();
// 读取sheet
excelReader.read(readSheet);
}catch (Exception e){
e.printStackTrace();
}finally {
if (excelReader != null) {
//这里不能省略
excelReader.finish();
}
}
}
三、总结
EasyExcel本身的读写速度是非常快的,再加上先生我用的是es,速度翻倍,如上:导入100万条数据7个字段,只需要3-4分钟即可完成,for循环导出100万条只需要2分钟。
真正影响速度的其实是:你的批量插入方法和你的分页查询的速度,打个比方,就最上面的分页查询导出,循环对100万条分页查询,
一次只查500条,整个的导出需要15分钟左右,查询就用了12-13分钟左右。最后看看效果吧!