SpringBoot+EasyExcel轻松实现300万数据快速导出

 

38141c2cd6f982463ecbf017b956b879.jpeg


前景

在项目开发中往往需要使用到数据的导入和导出,导入就是从Excel中导入到DB中,而导出就是从DB中查询数据然后使用POI写到Excel上。

写本文的背景是因为在工作中遇到了大数据的导入和导出,问题既然来了逃跑不如干掉它!!!

只要这一次解决了,后期遇到同样的问题就好解决了。

废话不多说,开始撸起来!!!

1 传统POI的的版本优缺点比较

其实想到数据的导入导出,理所当然的会想到apache的poi技术,以及Excel的版本问题。

既然要做导入导出,那么我们就先来大致看一下传统poi技术的版本以及优缺点对比吧!

首先我们知道POI中我们最熟悉的莫过于WorkBook这样一个接口,我们的POI版本也在更新的同时对这个几口的实现类做了更新:

  • HSSFWorkbook :

这个实现类是我们早期使用最多的对象,它可以操作Excel2003以前(包含2003)的所有Excel版本。在2003以前Excel的版本后缀还是.xls

  • XSSFWorkbook :

这个实现类现在在很多公司都可以发现还在使用,它是操作的Excel2003--Excel2007之间的版本,Excel的扩展名是.xlsx

  • SXSSFWorkbook :

这个实现类是POI3.8之后的版本才有的,它可以操作Excel2007以后的所有版本Excel,扩展名是.xlsx

大致知道了我们在导入导出操作的时候会用到这样三个实现类以及他们可以操作的Excel版本和后缀之后,我们就要从优缺点分析他们了

HSSFWorkbook

它是POI版本中最常用的方式,不过:

  • 它的缺点是 最多只能导出 65535行,也就是导出的数据函数超过这个数据就会报错;

  • 它的优点是 不会报内存溢出。(因为数据量还不到7w所以内存一般都够用,首先你得明确知道这种方式是将数据先读取到内存中,然后再操作)

XSSFWorkbook

  • 优点:这种形式的出现是为了突破HSSFWorkbook的65535行局限,是为了针对Excel2007版本的1048576行,16384列,最多可以导出104w条数据;

  • 缺点:伴随的问题来了,虽然导出数据行数增加了好多倍,但是随之而来的内存溢出问题也成了噩梦。因为你所创建的book,Sheet,row,cell等在写入到Excel之前,都是存放在内存中的(这还没有算Excel的一些样式格式等等),可想而知,内存不溢出就有点不科学了!!!

SXSSFWorkbook

从POI 3.8版本开始,提供了一种基于XSSF的低内存占用的SXSSF方式:

优点:

  • 这种方式不会一般不会出现内存溢出(它使用了硬盘来换取内存空间,

  • 也就是当内存中数据达到一定程度这些数据会被持久化到硬盘中存储起来,而内存中存的都是最新的数据),

  • 并且支持大型Excel文件的创建(存储百万条数据绰绰有余)。

缺点:

  • 既然一部分数据持久化到了硬盘中,且不能被查看和访问那么就会导致,

  • 在同一时间点我们只能访问一定数量的数据,也就是内存中存储的数据;

  • sheet.clone()方法将不再支持,还是因为持久化的原因;

  • 不再支持对公式的求值,还是因为持久化的原因,在硬盘中的数据没法读取到内存中进行计算;

  • 在使用模板方式下载数据的时候,不能改动表头,还是因为持久化的问题,写到了硬盘里就不能改变了;

2 使用方式哪种看情况

经过了解也知道了这三种Workbook的优点和缺点,那么具体使用哪种方式还是需要看情况的:

我一般会根据这样几种情况做分析选择:

1、当我们经常导入导出的数据不超过7w的情况下,可以使用 HSSFWorkbook 或者 XSSFWorkbook都行;

2、当数据量查过7w并且导出的Excel中不牵扯对Excel的样式,公式,格式等操作的情况下,推荐使用SXSSFWorkbook;

3、当数据量查过7w,并且我们需要操做Excel中的表头,样式,公式等,这时候我们可以使用 XSSFWorkbook 配合进行分批查询,分批写入Excel的方式来做;

3 百万数据导入导出(正菜)

铺垫也做了不少,那么现在开始讲讲我在工作中遇到的超百万数据的导入导出解决方案:

想要解决问题我们首先要明白自己遇到的问题是什么?

1、 我遇到的数据量超级大,使用传统的POI方式来完成导入导出很明显会内存溢出,并且效率会非常低;

2、 数据量大直接使用select * from tableName肯定不行,一下子查出来300w条数据肯定会很慢;

3、 300w 数据导出到Excel时肯定不能都写在一个Sheet中,这样效率会非常低;估计打开都得几分钟;

4、 300w数据导出到Excel中肯定不能一行一行的导出到Excel中。频繁IO操作绝对不行;

5、 导入时300万数据存储到DB如果循环一条条插入也肯定不行;

6、导入时300w数据如果使用Mybatis的批量插入肯定不行,因为Mybatis的批量插入其实就是SQL的循环;一样很慢。

解决思路:

针对1 :

其实问题所在就是内存溢出,我们只要使用对上面介绍的POI方式即可,主要问题就是原生的POI解决起来相当麻烦。

经过查阅资料翻看到阿里的一款POI封装工具EasyExcel,上面问题等到解决;

针对2:

不能一次性查询出全部数据,我们可以分批进行查询,只不过时多查询几次的问题,况且市面上分页插件很多。此问题好解决。

针对3:

可以将300w条数据写到不同的Sheet中,每一个Sheet写一百万即可。

针对4:

不能一行一行的写入到Excel上,我们可以将分批查询的数据分批写入到Excel中。

针对5:

导入到DB时我们可以将Excel中读取的数据存储到集合中,到了一定数量,直接批量插入到DB中。

针对6:

不能使用Mybatis的批量插入,我们可以使用JDBC的批量插入,配合事务来完成批量插入到DB。即 Excel读取分批+JDBC分批插入+事务。

3.1 EasyExcel 简介

附上GitHub地址:https://github.com/alibaba/easyexcel

GitHub地址上教程和说明很详细,并且附带有读和写的demo代码,这里对它的介绍我就不再详细说了。

至于EasyExcel底层怎么实现的这个还有待研究。

3.2 300w数据导出

EasyExcel完成300w数据的导出。技术难点已经知道了,接下来就是针对这一难点提供自己的解决思路即可。

300

### SpringBootEasyExcel 实现固定表头与动态表头的数据导出 #### 一、背景介绍 在实际开发场景中,数据导出的需求通常较为复杂,不仅涉及简单的字段映射,还可能需要支持 **固定表头** 和 **动态表头** 的组合。通过 `SpringBoot` 集成 `EasyExcel` 可以高效完成此类需求。 --- #### 二、实现方案详解 ##### 1. 固定表头的定义 固定表头是指在 Excel 文件中始终存在的列名或分组信息。可以通过 `@ExcelProperty` 注解指定固定的表头内容及其索引位置[^1]。 以下是基于案例中的实体类扩展: ```java @Data @NoArgsConstructor @AllArgsConstructor @Builder public class User implements Serializable { @ExcelProperty(value = {"用户基本信息", "用户名"}, index = 0) private String userName; @ExcelProperty(value = {"用户基本信息", "年龄"}, index = 1) private Integer age; @ExcelProperty(value = {"用户基本信息", "地址"}, index = 2) private String address; @ExcelProperty(value = {"用户基本信息", "生日"}, index = 3) private Date birthday; } ``` 上述代码展示了如何利用嵌套数组形式设置多级固定表头结构[^1]。 --- ##### 2. 动态表头的支持 对于动态表头,可以采用自定义写入逻辑的方式,在不修改实体类的情况下灵活调整表头内容。具体步骤如下: ###### (1)创建动态表头配置对象 定义一个用于存储动态表头信息的对象,例如: ```java import com.alibaba.excel.annotation.ExcelProperty; public class DynamicHeaderConfig { private List<String[]> headers; // 存储动态表头的二维数组 public DynamicHeaderConfig(List<String[]> headers) { this.headers = headers; } public List<String[]> getHeaders() { return headers; } } ``` ###### (2)编写自定义监听器 通过继承 `com.alibaba.excel.write.metadata.holder.WriteSheetHolder` 或者直接操作 `WriteTable` 来控制动态表头的行为。 示例代码片段: ```java import com.alibaba.excel.EasyExcel; import com.alibaba.excel.write.builder.ExcelWriterBuilder; import com.alibaba.excel.write.metadata.WriteSheet; import java.util.ArrayList; import java.util.List; public class ExportService { public void exportWithDynamicHeader(String filePath, List<User> data, List<String[]> dynamicHeaders) { // 创建 writer 对象 ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(filePath); WriteSheet writeSheet = EasyExcel.writerSheet("用户信息").build(); // 添加动态表头 for (String[] headerRow : dynamicHeaders) { excelWriterBuilder.head(headerRow); } // 执行写入 excelWriterBuilder.sheet(writeSheet.getSheetNo(), writeSheet.getSheetName()) .doWrite(data); } } ``` 在此过程中,`dynamicHeaders` 参数允许开发者传入任意数量的动态表头行,从而满足不同业务场景下的需求。 --- ##### 3. 组合固定表头与动态表头 当需要同时支持固定表头和动态表头时,可以在程序运行期间先加载默认的固定表头,再叠加动态部分。最终效果取决于两者的顺序安排以及冲突解决策略。 以下是一个完整的调用实例: ```java public static void main(String[] args) { // 准备测试数据 ArrayList<User> users = new ArrayList<>(); for (int i = 0; i < 10; i++) { User user = new User(); user.setAddress("西安" + i); user.setUserName("张三" + i); user.setBirthday(new Date()); user.setAge(10 + i); users.add(user); } // 定义动态表头 List<String[]> dynamicHeaders = new ArrayList<>(); dynamicHeaders.add(new String[]{"额外信息"}); dynamicHeaders.add(new String[]{"备注"}); // 调用服务方法执行导出 ExportService service = new ExportService(); service.exportWithDynamicHeader("D:\\用户_带动态表头.xlsx", users, dynamicHeaders); } ``` 此代码实现了将固定表头(来自实体类注解)与动态表头相结合的功能[^1]。 --- #### 三、注意事项 - 如果存在重复的表头名称,则需手动调整其显示层次或者添加唯一标识符。 - 使用日期类型字段时应确保格式化工具链正常工作,推荐依赖 `SimpleDateFormat` 明确设定输出样式。 - 大规模数据量下建议启用流式写法优化性能表现。 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

码农耕地巫师

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值