大数据量导入导出解决方案-EasyExcel
站长
· 阅读数 2
在上篇文章中，针对项目中大数据量的导入和导出使用了 easypoi 解决。该篇文章中，将使用easyExcel来解决大数量的导入和导出。项目示例框架跟上篇文章一样。本篇文章就只列出一些不同的地方
一、项目使用框架
二、项目依赖
<!-- easyExcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.3.4</version>
</dependency>
三、添加数据库相关文件
数据库表使用的还是跟上篇文章一样的表结构。脚本见仓库中 db 文件夹。创建数据库及数据库表。然后生成相关 entity、mapper等文件。 如果是使用 IDEA 的小伙伴，这里推荐一个生成插件。MyBatisX 
四、导入
1、下载easyExcel导入模板
使用注解方式，下载easyExcel模板。
1.1、创建excel实体类
@Data
@ColumnWidth(25)
public class EasyExcelExcel {
/** 学生名称 */
@ExcelProperty("学生名称")
@ColumnWidth(30)
private String name;
/** 学号 */
@ExcelProperty("学号")
@ColumnWidth(20)
private String studentNum;
/** 学生性别 */
@ExcelProperty(value = "学生性别", converter = EasyExcelSexConvert.class)
@ColumnWidth(20)
private String sex;
/** 学生年龄 */
@ExcelProperty("学生年龄")
@ColumnWidth(20)
private Integer age;
/** 出生日期 */
@ExcelProperty("出生日期")
@DateTimeFormat("yyyy-MM-dd")
@ColumnWidth(30)
private Date birthday;
}
注解说明：
-
@ExcelProperty：声明列名。其中 converter 属性可以自定义转换规则。这里将性别做了枚举转换。该枚举转化器作用：
- 在导入数据时，性别列填写男或女时，获取到的值是对应的枚举值0或1。
- 在导出数据时，性别列填写0或1，自动转化成男或女导出输出文件。
-
@ColumnWidth：设置列宽。也可以直接作用在类上。统一每一列的宽度
-
@DateTimeFormat：时间格式化
1.2、创建自定义转化器（性别枚举转化）
新建性别枚举：
@Getter
public enum SexEnum {
MAN("0", "男"),
WOMAN("1", "女"),
UNKNOWN("2", "未知"),
;
/**
* 根据name解析成对应枚举value
*/
public static String resolveToValue(String name) {
for (SexEnum s : values()) {
if (s.name.equals(name)) {
return s.value;
}
}
return UNKNOWN.value;
}
/**
* 根据value解析成对应枚举name
*/
public static String resolveToName(String value) {
for (SexEnum s : values()) {
if (s.value.equals(value)) {
return s.name;
}
}
return UNKNOWN.name;
}
private final String value;
private final String name;
SexEnum(String value, String name) {
this.value = value;
this.name = name;
}
}
新建转化器
public class EasyExcelSexConvert implements Converter<String> {
@Override
public Class<?> supportJavaTypeKey() {
return null;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return null;
}
/**
* 将 Excel 读取到的值转化为 Java 类型
* @author LP to 2024/3/30
*/
@Override
public String convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
return SexEnum.resolveToValue(Convert.toStr(cellData));
}
/**
* 将 Java 类型转为 Excel 中填写的值
* @author LP to 2024/3/30
*/
@Override
public WriteCellData<?> convertToExcelData(String value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
return new WriteCellData<>(SexEnum.resolveToName(value));
}
}
1.3、创建控制器
@RestController
@RequestMapping("/easyExcel")
@Slf4j
@AllArgsConstructor
public class EasyExcelController {
private final EasyExcelService easyExcelService;
@RequestMapping("/download")
public void easypoiDownload(HttpServletResponse response) {
try {
// 如果这里需要后台指定文件名，则可以把文件名定义在请求头里。注意：这里的文件名如果是中文，需要对名称encode
EasyExcel.write(response.getOutputStream(), EasyExcelExcel.class).sheet().doWrite(Lists.newArrayList());
} catch (Exception e) {
throw new BizException("下载easyExcel导入模板失败");
}
}
}
1.3、前端代码
注：这里axios配置、跨域问题见上篇文章。这里不做赘述 添加api请求配置文件
import axios from '@/axios'
// 下载模板
export const easyExcelDownloadApi = () => {
return axios({
url: '/easyExcel/download',
method: 'post',
responseType: 'blob'
})
}
添加导出事件。这里导出文件的实现方式是，获取后端接口的文件流。然后将文件流转为Blob对象，并使用URL.createObjectURL()方法创建一个URL地址。然后出发click()点击事件触发下载
<template>
<el-card shadow="never" class="border-0">
<div class="flex mb-4">
<el-button type="primary" @click="easyExcelDownload">
<el-icon class="mr-1">
<Download/>
</el-icon>
下载模板
</el-button>
</el-card>
</template>
<script setup>
import {easypoiDownloadApi} from "@/api/easyExcel.js";
// 下载模板
const easyExcelDownload = () => {
easyExcelDownloadApi()
.then(resp => {
const downloadElement = document.createElement('a')
const href = window.URL.createObjectURL(new Blob([resp], {type: 'application/vnd.ms-excel'}))
downloadElement.href = href
downloadElement.download = '学生信息.xls'
document.body.appendChild(downloadElement)
downloadElement.click() // 点击下载
document.body.removeChild(downloadElement) // 下载完成移除元素
window.URL.revokeObjectURL(href) // 释放掉blob对象
})
}
</script>
2、导入easyExcel
导入文件使用上篇文章的测试文件
2.1、创建excel实体类
@Data
@EqualsAndHashCode(callSuper = true)
public class EasyExcelImportExcel extends EasyExcelExcel {
@Excel(name = "处理信息", width = 35, orderNum = "99")
private String errorMsg;
}
2.2、创建控制器
@RequestMapping("/import")
public ResultData<?> easyExcelImport(MultipartFile file) {
if (file.isEmpty()) {
throw new BizException("导入文件为空");
}
easyExcelService.easyExcelImport(file);
return ResultData.success();
}
2.3、实现类
@Service
@Slf4j
@AllArgsConstructor
public class EasyExcelServiceImpl implements EasyExcelService {
private final ApplicationContext applicationContext;
@Override
public void easyExcelImport(MultipartFile file) {
try {
long startTime = System.currentTimeMillis();
// 读取总条数:这里会消耗一定时间。提供读取总条数方法，示例实际没有用到。这里也可以大概测出easyExcel读取文件的速度
EasyExcelTotalNumImportHandler<EasyExcelImportExcel> totalNumListener = new EasyExcelTotalNumImportHandler<>();
EasyExcel.read(file.getInputStream(), EasyExcelImportExcel.class, totalNumListener).sheet().doRead();
log.info("总条数:{},解析文件总耗时/ms:{}", totalNumListener.getTotalRow(), (System.currentTimeMillis() - startTime));
startTime = System.currentTimeMillis();
// 实际处理数据
EasyExcelStudentImportHandler listener = applicationContext.getBean(EasyExcelStudentImportHandler.class);
EasyExcel.read(file.getInputStream(), EasyExcelImportExcel.class, listener)
.sheet().doRead();
log.info("解析文件 + 处理数据总耗时/ms:{}", System.currentTimeMillis() - startTime);
} catch (Exception e) {
throw new BizException("导入异常");
}
}
}
EasyExcelTotalNumImportHandler类：
public class EasyExcelTotalNumImportHandler<T> implements ReadListener<T> {
private int rowCount = 0;
@Override
public void invoke(T t, AnalysisContext analysisContext) {
rowCount++;
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
public int getTotalRow() {
return rowCount;
}
}
这里主要是读取文件总条数。也可以大概反应出easyExcel读取文件的速度 EasyExcelStudentImportHandler 类：
@Slf4j
@Service
@Scope("prototype")
public class EasyExcelStudentImportHandler implements ReadListener<EasyExcelImportExcel> {
/** 成功数据集合 */
private final CopyOnWriteArrayList<EasyExcelImportExcel> successList = new CopyOnWriteArrayList<>();
/** 失败数据集合 */
private final CopyOnWriteArrayList<EasyExcelImportExcel> failList = new CopyOnWriteArrayList<>();
/** 批处理条数 */
private final static int BATCH_COUNT = 20000;
@Resource
private ThreadPoolExecutor easyExcelStudentImportThreadPool;
@Resource
private StudentMapper studentMapper;
/**
* 读取表格内容，每一条数据解析都会来调用
* @author LP to 2024/4/7
*/
@Override
public void invoke(EasyExcelImportExcel importExcel, AnalysisContext analysisContext) {
// 参数校验
if (StringUtils.isBlank(importExcel.getName())) {
importExcel.setErrorMsg("学生名称不能为空");
failList.add(importExcel);
return;
}
successList.add(importExcel);
if (successList.size() >= BATCH_COUNT) {
saveDate();
}
}
/**
* 在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。
* @author LP to 2024/4/7
*/
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
// 导入异常，实际业务可以做一些异常处理。比如记录失败数据等
}
/**
* 所有数据解析完成了调用
* @author LP to 2024/4/7
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
saveDate();
}
/**
* 保存信息
* @author Liupeng to 2024/4/2
*/
private void saveDate() {
// 拆分list，每个list 2000 条数据
List<List<EasyExcelImportExcel>> lists = ListUtil.split(successList, 1000);
final CountDownLatch countDownLatch = new CountDownLatch(lists.size());
for (List<EasyExcelImportExcel> list : lists) {
easyExcelStudentImportThreadPool.execute(() -> {
try {
studentMapper.insertBatch(list.stream().map(o -> {
Student student = new Student();
student.setNo(IdUtil.getSnowflakeNextId());
student.setName(o.getName());
student.setStudentNum(o.getStudentNum());
student.setAge(o.getAge());
student.setSex(o.getSex());
student.setBirthday(o.getBirthday());
return student;
}).collect(Collectors.toList()));
} catch (Exception e) {
log.error("启动线程失败,e:{}", e.getMessage(), e);
} finally {
countDownLatch.countDown();
}
});
}
// 等待所有线程执行完
try {
countDownLatch.await();
} catch (Exception e) {
log.error("等待所有线程执行完异常,e:{}", e.getMessage(), e);
}
// 提前将不再使用的集合清空，释放资源
successList.clear();
failList.clear();
lists.clear();
}
}
EasyExcelStudentImportHandler 的逻辑与上篇文章中EasypoiStudentListener类的类似。这里主要想说明一下easyExcel和 easyPoi 两者之间实现类的区别： easyExcel 实现 ReadListener 接口。主要可以实现四个常用方法：
- invokeHead() ：读取标题，里面实现在读完标题后会回调。本篇文章中未使用到
- invoke()：读取表格内容，每一条数据解析都会来调用
- onException()：转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。
- doAfterAllAnalysed()：所有数据解析完成了调用
easypoi实现IReadHandler接口。只提供了两个实现方法：
- handler()：处理解析对象
- doAfterAll()：处理完成之后的业务
easypoi没有提供异常时的处理方法。需要注意自行处理异常
2.4、创建前端代码
<template>
<el-button type="primary" @click="dialogVisible = true">
<el-icon class="mr-1">
<Upload/>
</el-icon>
导入
</el-button>
<el-dialog
title="导入"
v-model="dialogVisible"
:open="openDialog"
width="30%">
<el-upload
class="pt-5"
ref="formRef"
action="#"
accept=".xlsx,.xls"
:auto-upload="false"
:file-list="fileList"
:http-request="handleFileSuccess"
:on-change="handlerFileChange"
:limit="1"
>
<el-button :icon="Upload" type="primary"
>选择文件
</el-button>
<template #tip>
<div class="el-upload__tip">
<span>仅允许导入xls、xlsx格式文件。</span>
</div>
</template>
</el-upload>
<div class="mt-10">
<el-button @click="dialogVisible = false">取 消</el-button>
<el-button type="primary" @click="onSubmit" :loading="importButtonLoading">确 定</el-button>
</div>
</el-dialog>
</template>
<script setup>
import { easyExcelImportFileApi } from "@/api/easyExcel.js";
const dialogVisible = ref(false);
const importButtonLoading = ref(false);
const formRef = ref();
const fileList = ref();
const openDialog = () => {
importButtonLoading.value = false;
formRef.value.clearFiles();
}
const handlerFileChange = (file, fileListVal) => {
fileList.value = fileListVal;
};
/** 文件上传成功处理 */
const handleFileSuccess = async data => {
const formData = new FormData();
formData.append("file", data.file);
await easyExcelImportFileApi(formData).then(resp => {
if (resp.result) {
ElMessage.success(resp.msg)
dialogVisible.value = false
} else {
ElMessage.error(resp.msg)
}
importButtonLoading.value = false
}).catch(err => {
ElMessage.error(err.msg)
});
};
const onSubmit = () => {
if (!fileList.value || fileList.value.length === 0) {
ElMessage({
message: "请选择文件",
type: "error"
});
return;
}
importButtonLoading.value = true
formRef.value.submit();
};
</script>
添加api请求配置文件
import axios from '@/axios'
export const easyExcelImportFileApi = (data) => {
return axios({
url: '/easyExcel/import',
method: 'post',
data,
header: {
headers: {
"Content-Type": "multipart/form-data"
}
}
});
};
至此使用easyExcel导入大批量数据已经完成。经过测试，导入100w条数据，只解析文件，用时/ms： 
解析文件 + 插入数据库，用时/ms 
五、导出
5.1、创建excel实体类
@Data
public class EasyExcelExcel {
/** 学生名称 */
@ExcelProperty("学生名称")
@ColumnWidth(30)
private String name;
/** 学号 */
@ExcelProperty("学号")
@ColumnWidth(20)
private String studentNum;
/** 学生性别 */
@ExcelProperty(value = "学生性别", converter = EasyExcelSexConvert.class)
@ColumnWidth(20)
private String sex;
/** 学生年龄 */
@ExcelProperty("学生年龄")
@ColumnWidth(20)
private Integer age;
/** 出生日期 */
@ExcelProperty("出生日期")
@DateTimeFormat("yyyy-MM-dd")
@ColumnWidth(30)
private Date birthday;
}
5.2、创建控制器
@PostMapping("/export")
public void easyExcelExport(HttpServletResponse response) {
try {
long startTime = System.currentTimeMillis();
response.setContentType("text/csv");
Long total = studentMapper.selectCount(null);
if (total == 0) {
log.info("查询无数据");
return;
}
ExcelWriter build = EasyExcel.write(response.getOutputStream(), EasyExcelExcel.class).build();
WriteSheet writeSheet = EasyExcel.writerSheet("sheet").build();
long page = 1;
long pageSize = 100_000;
do {
Page<Student> studentPage = studentMapper.selectPage(new Page<>(page, pageSize), null);
build.write(studentPage.getRecords().stream().peek(e -> {
EasyExcelExcel excelExcel = new EasyExcelExcel();
excelExcel.setName(e.getName());
excelExcel.setStudentNum(e.getStudentNum());
excelExcel.setAge(e.getAge());
excelExcel.setSex(e.getSex());
excelExcel.setBirthday(e.getBirthday());
}).collect(Collectors.toList()), writeSheet);
page += 1;
} while ((page - 1) * pageSize < total);
build.finish();
log.info("导出耗时/ms:" + (System.currentTimeMillis() - startTime));
} catch (Exception e) {
log.error("easyExcel导出失败,e:{}", e.getMessage(), e);
}
}
使用分页的方式导出数据，这样避免内存溢出。 每次查询 10w 条数据，导出 100w 条数据平均耗时在1分多钟左右。 
5.3、前端代码
<template>
<el-button type="primary" @click="easyExcelExport">
<el-icon class="mr-1">
<Download/>
</el-icon>
导出
</el-button>
</template>
<script setup>
const easyExcelExport = () => {
easyExcelExportApi()
.then(resp => {
const downloadElement = document.createElement('a')
const href = window.URL.createObjectURL(new Blob([resp]))
downloadElement.href = href
downloadElement.download = 'easyExcel导出.csv'
document.body.appendChild(downloadElement)
downloadElement.click() // 点击下载
document.body.removeChild(downloadElement) // 下载完成移除元素
window.URL.revokeObjectURL(href) // 释放掉blob对象
})
}
</script>
api请求配置文件
import axios from '@/axios'
export const easyExcelExportApi = () => {
return axios({
url: '/easyExcel/export',
method: 'post',
responseType: 'blob'
})
}
以上就使用 easyExcel 实现了大数据量的导入和导出。如果有什么疑问，欢迎在评论区下留言交流。 至此，博主将提供了两个解决大数量的导出和导出方法：easypoi和easyExcel。两者之间的效率相差不大，各位可以随便选用哪个。 这里再说一句题外话。如果你们公司对于导入和导出的需求量很大，可以考虑把导入和导出分别做成任务的形式。让导入和导出任务在后台执行。这样对于操作人员来说，在导入的同时，可以去做其他的事情。不需要等待导入或导出完成。因为当数据量大的时候。还是需要一定时间来处理的。