在数据处理和报表生成中,将超大规模数据导出到Excel文件是一项常见且挑战性的任务。
传统的Excel处理库如Apache POI在处理大数据量时容易遇到内存溢出的问题。
为了解决这个问题,我们可以采用阿里巴巴开源的EasyExcel库,它以其内存占用低、处理速度快的特点,非常适合处理大规模数据的导出。
本文将详细介绍如何使用Java结合EasyExcel库,通过分页从数据库读取数据,并将数据分批导出到Excel文件中。
实现效果
JVM监控
设置堆内存大小 -Xms384m -Xmx384m
EasyExcel简介
EasyExcel是阿里巴巴开源的一个基于Java的简单、省内存的读写Excel的库。
Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存,poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题,但POI还是有一些缺陷,比如07版Excel解压缩以及解压后存储都是在内存中完成的,内存消耗依然很大。
easyexcel重写了poi对07版Excel的解析,一个3M的excel用POI sax解析依然需要100M左右内存,改用easyexcel可以降低到几M,并且再大的excel也不会出现内存溢出;03版依赖POI的sax模式,在上层做了模型转换的封装,让使用者更加简单方便
EasyExcel特别适用于处理大数据量的Excel文件,能够轻松应对百万级数据的导入导出需求。
POM
代码采用Java代码编写,jdk版本要求21。
首先,你需要在你的Java项目中引入EasyExcel的依赖,实际使用过程中,将version替换成最新版本。以下是一个Maven依赖示例:
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>4.0.2</version>
</dependency>
动态头
动态头适用性更广,且不需要创建POJO。
private List<List<String>> head() {
return List.of(List.of("双精度浮点数"),
List.of("整型"),
List.of("日期"));
}
模拟数据
动态获取批量数据,这为模拟数据库数据,真实场景下一般从数据库分批获取数据。
private List<List<Object>> data(int pageSize) {
List<List<Object>> result = new ArrayList<>(pageSize);
for (int i = 0; i < pageSize; i++) {
result.add(List.of(Random.from(RandomGenerator.getDefault()).nextDouble(),
Random.from(RandomGenerator.getDefault()).nextInt(),
LocalDateTime.now()));
}
return result;
}
循环写excel
由于数据量超大,我们需要分页读取并将数据写入excel。
接下来,我们使用EasyExcel将分页查询到的数据分批写入Excel文件的多个sheet。这里的关键是控制每次写入的数据量,避免内存溢出。
示例代码通过模拟数据写入,真实场景可以采用mybatis从数据库分页查询数据写入excel。
public void export() {
int pageNum = 0;
int pageSize = 200000;
long total = 5000000;
try (ExcelWriter excelWriter = EasyExcel.write("./export.xlsx").build()) {
do {
WriteSheet writeSheet = EasyExcel.writerSheet(pageNum, "雇员页" + (pageNum + 1)).build();
writeSheet.setHead(head());
// Page<Object> page = PageHelper.startPage(pageNum + 1, pageSize).setOrderBy("id desc");
// total = page.getTotal();
List<List<Object>> data = data(pageSize);
excelWriter.write(data, writeSheet);
pageNum++;
} while ((pageNum + 1L) * pageSize < total);
}
}
扩展
动态头和动态数据结合,可将excel数据导出的功能进行高度抽象,将导出功能建成一个微服务,本例称为:ExportMicroservice。数据导出以异步任务的形式执行,用户提交一个导出请求,微服务A提交任务到ExportMicroservice,将要导出的数据通过消息中间件推送到ExportMicroservice,用户只需等待任务导出成功的消息。
该微服务可定义一种数据协议。一种可供参考的数据交换协议如下:
创建任务的http请求的数据协议如下:
{
"filename": "测试表格",
"head": [
{
"type": "double",
"display": "双精度浮点数"
},
{
"type": "int",
"display": "整型"
},
{
"type": "LocalDateTime",
"display": "日期"
},
{
"type": "URL",
"display": "日期"
}
],
"total": 100000,
"sheetName": "测试"
}
该json定义了表头,文件名,数据量等,可根据实际情况扩展。
而需导出的数据,可通过消息中间件Kafka推送给该微服务,可单条推送,也可一次性推送多条数据,Kafka的数据协议如下:
[
{
"double": -8043262919442300000,
"int": 1301170199,
"LocalDateTime": "2024/8/14 18:01:40",
"URL": "https://yanglei.ltd/archives/gEZtR6Qw"
},
{
"double": -8043262919442300000,
"int": 1301170199,
"LocalDateTime": "2024/8/14 18:01:40",
"URL": "https://yanglei.ltd/archives/gEZtR6Qw"
},
{
"double": -8043262919442300000,
"int": 1301170199,
"LocalDateTime": "2024/8/14 18:01:40",
"URL": "https://yanglei.ltd/archives/gEZtR6Qw"
}
]
定义该数据格式,主要是方便服务使用者组装数据,ExportMicroservice在收到该数据后,需要清洗为List<List
以上仅提供一种Excel导出服务的设计思路,具体实现方式可结合现实场景进行改造。
比如有些场景下不仅有结构化数据导出,还有非结构化数据导出,如图片和个人信息同时导出,这时可以通过type进行识别,若type为URL时进行图片导出,并将图片链接写入Excel的单元格,其他type时仅进行字符串导出,当然,还可以结合EasyExcel的拦截器对Excel的单元格进行一些特殊设置,如对图片链接设置蓝色并加下划线,可使导出效果更佳。