EastExcel话题讨论 - 编程导航 | 2026最新EastExcel相关编程话题

EastExcel

4 参与

快来分享你的内容吧～

写文章

写面经

向专家提问，帮你解惑

点击登录，快来和大家讨论吧～

表情

图片

话题

打卡

综合

交流

文章

问答

吃遍全国汉堡

2025-01-17·
后端
EasyExcel 百万数据导出实战篇
大家好，我是雪荷。今天给大家分享一个较硬核的干货——利用多线程技术实现百万数据的导出，学的好的话甚至可以写在简历上，话不说和我开始实战吧。
查看全文
lenyan：
现在升级迁移到了FastExcel ：https://idev.cn/fastexcel/zh-CN用与处理excel的大杀器
EastExcel
Java
MySQL
11
3
14
分享
吃遍全国汉堡

2024-07-26·
后端
EasyExcel 初使用—— Java 实现多种写入 Excel 功能
前言大家好，我是汉堡。最近实习接触到了导出数据的功能，在实现此功能之前我们要先了解如何往 Excel 中写入数据。相信学完这个，再去做导出数据功能也不会太难，等大家学完这个我再教大家前后端如何实现导出 Excel。 EasyExcel 官方网址：EasyExcel官方文档 - 基于Java的Excel处理工具 | Easy Excel 前置准备引入依赖先创建一个 Spring Boot 工程
查看全文
Java
EastExcel
12
0
0
分享
吃遍全国汉堡

2024-07-22·
后端
EasyExcel 初使用—— Java 实现读取 Excel 功能
前言在我们项目的开发中啊，前端有时候会传送 Excel 文件给后端（Java）去解析，那我们作为后端该如何实现对 Excel 文件的解析和数据读取呢？说到这我就不得不推荐 EasyExcel 了！ EasyExcel 介绍引用下官方对于 EasyExcel 介绍：EasyExcel是一个基于Java的、快速、简洁、解决大文件内存溢出的Excel处理工具。他能让你在不用考虑性能、内存的等因素的情
查看全文
EastExcel
Java
10
0
0
分享
玄德

2023-11-11·
全栈开发
EasyExcel导出文件，并解决格式报错
记录下项目里数据导出为excel的碎片，并用简单粗暴的方式规避了报错，希望有大佬能完善一下另一种优雅的解决办法引入pom文件 com.alibaba easyexcel 3.2.1
查看全文
程序员小白条：
知识碎片的目的是通过简单分享，帮大家快速学习重点编程小知识。知识碎片汇总：https://yuyuanweb.feishu.cn/wiki/AqfawFUT0iD69kkiRKoci6Nqnqc欢迎参与贡献，获取奖励：https://docs.qq.com/form/page/DQkdHTGFLQmJjV3VU#/fill
知识碎片
技术
EastExcel
17
1
0
分享

热门话题

EasyExcel 百万数据导出实战篇

## 前言大家好，我是汉堡🍔。今天给大家分享一个较硬核的干货——利用多线程技术实现百万数据的导出，学的好的话甚至可以写在简历上，话不说开始实战。 ## EasyExcel 如何实现数据写入 Excel 在我以前的文章介绍了如何利用 EasyExcel 实现多种方式 Excel 的写入，不了解的小伙伴可以👀这篇文章📓：https://blog.csdn.net/xyendjsj/article/details/140708831?spm=1001.2014.3001.5501 ## 前置准备创建一个数据库表，模拟真实需要导出的场景。 ```sql create database if not exists lingxibi; create table lingxibi.user ( id bigint auto_increment comment 'id' primary key, userAccount varchar(256) not null comment '账号', userPassword varchar(512) not null comment '密码', userName varchar(256) null comment '用户昵称', userAvatar varchar(1024) default 'https://th.bing.com/th/id/R.54a295a86f04aaf12f1285d4e00fd6be?rik=QAdEADu3LNh9Hg&pid=ImgRaw&r=0' null comment '用户头像', userRole varchar(256) default 'user' not null comment '用户角色：user/admin/ban', score int default 0 not null comment '积分', generatingCount int default 0 not null comment '正在生成的图表数量', createTime datetime default CURRENT_TIMESTAMP not null comment '创建时间', updateTime datetime default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间', isDelete tinyint default 0 not null comment '是否删除' ) comment '用户' collate = utf8mb4_unicode_ci; create index idx_userAccount on lingxibi.user (userAccount); ``` 定义一个存储过程，往 user 表插入 100w 数据。说句题外话，插入大量数据建议用 Navicat ，别用 IDEA 自带的数据库工具，容易卡死。 ```sql use lingxibi; DROP PROCEDURE IF EXISTS insert_users; DELIMITER $$ CREATE PROCEDURE insert_users() BEGIN -- 变量声明 DECLARE i INT DEFAULT 1; -- 异常处理 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN -- 出现异常时回滚事务 ROLLBACK; SELECT 'Error occurred, transaction rolled back.' AS message; END; -- 开启事务 START TRANSACTION; -- 循环插入数据 WHILE i <= 1000000 DO -- 插入数据 INSERT INTO `lingxibi`.`user` (userAccount, userPassword, userName, userAvatar, userRole, score, generatingCount, createTime, updateTime, isDelete) VALUES ('hanbao', 'b0dd3697a192885d7c055db46155b26a', '汉堡', NULL, 'user', FLOOR(RAND() * 1000), 0, NOW(), NOW(), 0); SET i = i + 1; END WHILE; -- 提交事务 COMMIT; SELECT 'Data inserted successfully.' AS message; END$$ DELIMITER ; CALL insert_users(); ``` 调用结果如下： <img src="https://pic.code-nav.cn/post_picture/1608639807578177538/wviF3dev5qgelkEv.webp" alt="image-20250117192401160" width="100%" /> 随后创建一个 Spring Boot 工程，记得勾选 lombok 依赖，spring web 想要就要吧，其他就不需要了。引入 EasyExcel 和 mysql 驱动、mybatis-plus 依赖。 ```xml  <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.1.7</version> </dependency> <dependency> <groupId>com.mysql</groupId> <artifactId>mysql-connector-j</artifactId> </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.5.2</version>  </dependency> ``` 用 MyBatisX 插件生成 Mapper 和 Service 层代码，将生成代码移入指定的包，在 resources 目录新建一个 `user.xlsx` 文件，具体结构如下： <img src="https://pic.code-nav.cn/post_picture/1608639807578177538/FvqgYhYk32F9D1c7.webp" alt="image-20250117191537294" width="100%" /> ## 技术难点我们思考一下海量数据数据导出的技术难点是啥，我觉得主要有以下几点： 1. OOM：一次性导出数据会导致 OOM，不然也不会有这篇文章了 2. 海量数据的查询：如何按照顺序依次导出数据，有些数据要求导出数据不能变 3. 耗时问题：如果你足够有钱能升级内存，可能不会导致 OOM，但是导出时间肯定很长，导致服务崩溃。可能是垃圾回收耗时，可能是数据查询耗时。 ## 如何解决 1. 针对第一个和第三个问题，可以使用多线程分块写入数据，减少线程持有数据的时间，因为 GC 回收垃圾是在线程执行完毕后进行的 2. 按照多线程分批的数据量和批次进行分页查询，但这样会导致深分页问题使得查询性能下降。因此我们可以按照主键 id 进行范围查询，比如 0 < id < 10000，10000 < id < 20000 以此类推，前提是主键 id 是顺序递增的。如果不行的话可以添加一个冗余字段，用代码维护字段的递增，或者如果表中没有用到递增列就设为递增列并设置二级索引，但这样插入性能低，而且数据量大时占用空间可不小呢。 ## 代码实现话不多说直接看代码吧，我创建了一个固定线程池，线程数为 5，然后每批数据量为 10 w，总共分 10 批。下面代码我就不解释了，相信看到这里的帅老们都能看懂。 ```java @Component public class ExcelUtil { @Resource private UserService userService; private ExecutorService executorService = Executors.newFixedThreadPool(5); public void exportData() { StopWatch stopWatch = new StopWatch(); stopWatch.start(); // 获取 JVM 当前内存状态 Runtime runtime = Runtime.getRuntime(); long beforeUsedMemory = runtime.totalMemory() - runtime.freeMemory(); // 获取数据总量和每页大小 int pageSize = 100000; long totalSize = userService.count(); // 计算总页数 int totalPage = (int) ((totalSize + pageSize - 1) / pageSize); List<CompletableFuture<Void>> futureList = new ArrayList<>(); final long[] minId = {0}; for (int i = 0; i < totalPage; i++) { long currentMinId = minId[0]; CompletableFuture<Void> future = CompletableFuture.runAsync(() -> { List<User> users = userService.queryByIdRange(currentMinId, pageSize); if (!users.isEmpty()) { minId[0] = users.get(users.size() - 1).getId(); } writeDataToExcel(users); }, executorService); futureList.add(future); } CompletableFuture.allOf(futureList.toArray(new CompletableFuture[0])).join(); stopWatch.stop(); long afterUsedMemory = runtime.totalMemory() - runtime.freeMemory(); long memoryUsed = afterUsedMemory - beforeUsedMemory; System.out.println("导出数据耗时：" + stopWatch.getTotalTimeMillis() + "ms"); System.out.println("导出数据内存使用：" + memoryUsed / (1024 * 1024) + " MB"); } public void writeDataToExcel(List<User> users) { String fileName = "/Users/hejiajun/Documents/study/projects-demo/excel-demo/src/main/resources/user.xlsx"; EasyExcel.write(fileName).sheet().doWrite(users); } } ``` 创建一个 Test 类，看看耗时时间和用了多少内存。 ```java @SpringBootTest public class ExcelTest { @Resource private ExcelUtil excelUtil; @Test public void test() { excelUtil.exportData(); } } ``` UserSerive.queryByIdRange 实现： ```java public List<User> queryByIdRange(long minId, int limit) { return this.baseMapper.selectList( new LambdaQueryWrapper<User>() .gt(User::getId, minId) // 查询 ID 大于 minId 的记录 .orderByAsc(User::getId) // 按 ID 升序排序 .last("LIMIT " + limit ) // 限制查询条数 ); } ``` 结果如下，我们看到花了 14s 左右，内存为 611 MB <img src="https://pic.code-nav.cn/post_picture/1608639807578177538/GeebAJVajT0kp1xZ.webp" alt="image-20250117203113409" width="100%" /> 那么尝试将 `pageSize` 参数改为 25w 再导出试试。没想到吧，时间还长了，内存也变大了。 <img src="https://pic.code-nav.cn/post_picture/1608639807578177538/pKciaE7U9yPeMhRl.webp" alt="image-20250117203341733" width="100%" /> 那我改下线程数试试呢？我改为 9 试试，`pageSize`依旧是 10w。好吗时间和内存都变大了。 <img src="https://pic.code-nav.cn/post_picture/1608639807578177538/G5gFt6J31IH0YvNk.webp" alt="image-20250117204321557" width="100%" /> 我再把`pageSize`改为 25w，线程数还为 9。时间几乎差不多，但是内存变多了。 <img src="https://pic.code-nav.cn/post_picture/1608639807578177538/rYweA62wu187UIGC.webp" alt="image-20250117204852952" width="100%" /> 综上所述，你问我该如何调整每批的数据量还线程数我也不知道，唯有实践出真知。下面给一些线程池参数的建议： - CPU 密集型（数学计算）：CPU 核心数 + 1 - IO 密集型（文件读取，数据库读取）：CPU 核心数 * 2 或更多些值得注意的是我用的是固定线程池，即任务数量变多都线程数并不会变多和减少，适用于任务数量相对稳定，不会出现突发的大量任务的场景。 ## 常见线程池的创建方式 1）FixedThreadPool：创建一个固定数量的线程池线程池中的线程数是固定的，空闲的线程会被复用。如果所有线程都在忙，则新任务会放入队列中等待。适合负载稳定的场景，任务数量确定且不需要动态调整线程数。 2）CachedThreadPool：一个可以根据需要创建新线程的线程池线程池的线程数量没有上限，空闲线程会在 60 秒后被回收，如果有新任务且没有可用线程，会创建新线程。适合短期大量并发任务的场景，任务执行时间短且线程数需求变化较大。 3）SignleThreadExecutor：创建一个只有单线程的线程池只有一个线程处理任务，任务会按照提交顺序执行。适用于按顺序执行的场景，或不需要并发处理任务的场景 4）ScheduledThreadPool：支持定时任务和周期性任务的线程池以定时或固定频次执行任务，如定时任务调度器。使用周期性执行任务的场景，如定时任务调度器。 5）WorkStealingPool：基于任务窃取算法的线程池线程池中的每个线程维护一个双端队列(deque)，线程可以从自己的队列中取任务执行。如果线程的任务队列为空，它可以从其他线程的队列中“窃取“任务来执行，达到负载均衡的效果。适合大量小任务并行执行，特别是递归算法或大任务分解成小任务的场景。 ## 注意 EasyExcel的 write 方法是线程不安全的，因此在多线程环境下需要进行加锁，以防出现多个线程写入文件导致损坏文件，但是不知道我没加锁也没损坏文件，有知道的大佬可以指导一下。另外有任何讲的不对的地方欢迎各位指正。 EasyExcel.write 方法加锁实例： ```java @Component public class ExcelUtil { @Resource private UserService userService; private ExecutorService executorService = Executors.newFixedThreadPool(5); private final ReentrantLock lock = new ReentrantLock(); public void exportData() { StopWatch stopWatch = new StopWatch(); stopWatch.start(); // 获取 JVM 当前内存状态 Runtime runtime = Runtime.getRuntime(); long beforeUsedMemory = runtime.totalMemory() - runtime.freeMemory(); // 获取数据总量和每页大小 int pageSize = 100000; long totalSize = userService.count(); // 计算总页数 int totalPage = (int) ((totalSize + pageSize - 1) / pageSize); List<CompletableFuture<Void>> futureList = new ArrayList<>(); final long[] minId = {0}; for (int i = 0; i < totalPage; i++) { long currentMinId = minId[0]; CompletableFuture<Void> future = CompletableFuture.runAsync(() -> { List<User> users = userService.queryByIdRange(currentMinId, pageSize); if (!users.isEmpty()) { minId[0] = users.get(users.size() - 1).getId(); } writeDataToExcel(users); }, executorService); futureList.add(future); } CompletableFuture.allOf(futureList.toArray(new CompletableFuture[0])).join(); stopWatch.stop(); long afterUsedMemory = runtime.totalMemory() - runtime.freeMemory(); long memoryUsed = afterUsedMemory - beforeUsedMemory; System.out.println("导出数据耗时：" + stopWatch.getTotalTimeMillis() + "ms"); System.out.println("导出数据内存使用：" + memoryUsed / (1024 * 1024) + " MB"); } public void writeDataToExcel(List<User> users) { String fileName = "/Users/hejiajun/Documents/study/projects-demo/excel-demo/src/main/resources/user.xlsx"; lock.lock(); try { EasyExcel.write(fileName).sheet().doWrite(users); } catch (Exception e) { e.printStackTrace(); } finally { lock.unlock(); } } } ``` ## 开源项目 ### 厚米匹配网址：http://hm.hejiajun.icu/ 前端仓库：https://github.com/dnwwdwd/homieMatching-fronted 后端仓库：https://github.com/dnwwdwd/homieMatching ### 灵犀 BI 网址：http://bi.hejiajun.icu/ 前端仓库：https://github.com/dnwwdwd/Lingxi-BI-fronted 后端仓库：https://github.com/dnwwdwd/Lingxi-BI

EasyExcel 初使用—— Java 实现多种写入 Excel 功能

<html> <head></head> <body> <div class="content ql-editor"> <p><strong style="color: rgb(51, 51, 51);">前言</strong></p> <p>大家好，我是汉堡。最近实习接触到了导出数据的功能，在实现此功能之前我们要先了解如何往 Excel 中写入数据。相信学完这个，再去做导出数据功能也不会太难，等大家学完这个我再教大家前后端如何实现导出 Excel。</p> <p><br></p> <p>EasyExcel 官方网址：<a href="https://easyexcel.opensource.alibaba.com/" target="_blank" style="color: rgb(65, 131, 196);">EasyExcel官方文档 - 基于Java的Excel处理工具 | Easy Excel</a></p> <p><br></p> <h3 class="ql-line-height-1_6"><strong style="color: rgb(51, 51, 51);">前置准备</strong></h3> <h4 class="ql-line-height-1_6"><strong style="color: rgb(51, 51, 51);">引入依赖</strong></h4> <p>先创建一个 Spring Boot 工程，随后加入 EasyExcel 和 Lombok 依赖。</p> <div class="ql-code-block-container"> <div class="ql-code-block"> <span class="ql-token hljs-tag"><dependency></span> </div> <div class="ql-code-block"> <span class="ql-token hljs-tag"><groupId></span>org.projectlombok<span class="ql-token hljs-tag"></groupId></span> </div> <div class="ql-code-block"> <span class="ql-token hljs-tag"><artifactId></span>lombok<span class="ql-token hljs-tag"></artifactId></span> </div> <div class="ql-code-block"> <span class="ql-token hljs-tag"><optional></span>true<span class="ql-token hljs-tag"></optional></span> </div> <div class="ql-code-block"> <span class="ql-token hljs-tag"></dependency></span> </div> <div class="ql-code-block"> <span class="ql-token hljs-tag"><dependency></span> </div> <div class="ql-code-block"> <span class="ql-token hljs-tag"><groupId></span>com.alibaba<span class="ql-token hljs-tag"></groupId></span> </div> <div class="ql-code-block"> <span class="ql-token hljs-tag"><artifactId></span>easyexcel<span class="ql-token hljs-tag"></artifactId></span> </div> <div class="ql-code-block"> <span class="ql-token hljs-tag"><version></span>3.1.1<span class="ql-token hljs-tag"></version></span> </div> <div class="ql-code-block"> <span class="ql-token hljs-tag"></dependency></span> </div> </div> <p><br></p> <h4 class="ql-line-height-1_6"><strong style="color: rgb(51, 51, 51);">设置一个写对象，即写入 Excel 的对象</strong></h4> <p><br></p> <div class="ql-code-block-container"> <div class="ql-code-block"><span class="ql-token hljs-meta">@Data</span> </div> <div class="ql-code-block"><span class="ql-token hljs-meta">@AllArgsConstructor</span> </div> <div class="ql-code-block"><span class="ql-token hljs-keyword">public</span> <span class="ql-token hljs-keyword">class</span> <span class="ql-token hljs-title">SaleData</span> <span class="ql-token hljs-keyword">implements</span> <span class="ql-token hljs-title">Serializable</span> { </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ExcelProperty("订单号")</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> Long id; </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ExcelProperty("品种")</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> String name; </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ExcelProperty("价格")</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> BigDecimal price; </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ExcelProperty("数量")</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> Integer totalNum; </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ExcelProperty("用户ID")</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> Long userId; </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ExcelProperty("交易时间")</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> String datetime; </div> <div class="ql-code-block"> } </div> </div> <p><br></p> <h3 class="ql-line-height-1_6"><strong style="color: rgb(51, 51, 51);">写入 Excel</strong></h3> <h4 class="ql-line-height-1_6"><strong style="color: rgb(51, 51, 51);">最简单的写入 Excel 方式</strong></h4> <p><br></p> <p class="ql-line-height-1_6">看代码也能看出来，首先有一个写入 Excel 的对象 List，其次指定 Excel 表格的位置和表单名称，最后进行调用 write 方法将 list 写入 Excel 即可。</p> <div class="ql-code-block-container"> <div class="ql-code-block"> <span class="ql-token hljs-meta">@Test</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">public</span> <span class="ql-token hljs-keyword">void</span> <span class="ql-token hljs-title">simpleWriteDataToExcel()</span> { </div> <div class="ql-code-block"> List<SaleData> list = <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">ArrayList</span><>(); </div> <div class="ql-code-block"> list.add(<span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">SaleData</span>(<span class="ql-token hljs-number">1L</span>, <span class="ql-token hljs-string">"苹果"</span>, <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">BigDecimal</span>(<span class="ql-token hljs-string">"10.00"</span>), <span class="ql-token hljs-number">10</span>, <span class="ql-token hljs-number">1L</span>,<span class="ql-token hljs-string">"2024-01-01 13:00:00"</span>)); </div> <div class="ql-code-block"> list.add(<span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">SaleData</span>(<span class="ql-token hljs-number">2L</span>, <span class="ql-token hljs-string">"梨子"</span>, <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">BigDecimal</span>(<span class="ql-token hljs-string">"12.00"</span>), <span class="ql-token hljs-number">10</span>, <span class="ql-token hljs-number">1L</span>,<span class="ql-token hljs-string">"2025-01-01 13:00:00"</span>)); </div> <div class="ql-code-block"> list.add(<span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">SaleData</span>(<span class="ql-token hljs-number">3L</span>, <span class="ql-token hljs-string">"西瓜"</span>, <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">BigDecimal</span>(<span class="ql-token hljs-string">"5.00"</span>), <span class="ql-token hljs-number">10</span>, <span class="ql-token hljs-number">1L</span>,<span class="ql-token hljs-string">"2024-03-01 13:00:00"</span>)); </div> <div class="ql-code-block"> list.add(<span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">SaleData</span>(<span class="ql-token hljs-number">4L</span>, <span class="ql-token hljs-string">"香蕉"</span>, <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">BigDecimal</span>(<span class="ql-token hljs-string">"7.00"</span>), <span class="ql-token hljs-number">10</span>, <span class="ql-token hljs-number">1L</span>,<span class="ql-token hljs-string">"2024-02-01 13:00:00"</span>)); </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-type">String</span> <span class="ql-token hljs-variable">fileName</span> <span class="ql-token hljs-operator">=</span> <span class="ql-token hljs-string">"D:\\projects\\plant-sys-main\\src\\main\\resources\\销售数据1.xlsx"</span>; </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">//写入excel</span> </div> <div class="ql-code-block"> EasyExcel.write(fileName, SaleData.class) </div> <div class="ql-code-block"> .sheet(<span class="ql-token hljs-string">"Sheet1"</span>) </div> <div class="ql-code-block"> .doWrite(list); </div> <div class="ql-code-block"> } </div> </div> <p>写入效果：</p> <p><img src="https://pic.code-nav.cn/planet_post_image/1608639807578177538/hqpeshvd.jpeg"></p> <p>如果 EasyExcel.write() 方法不带上 SaleData.class，就表示未指定表头，那这样写入 Excel 时是直接写入的，但没有表头。</p> <p>没指明表头的写入效果：</p> <p><img src="https://pic.code-nav.cn/planet_post_image/1608639807578177538/k5zo42ag.jpeg"></p> <p><br></p> <h4 class="ql-line-height-1_6"><strong style="color: rgb(51, 51, 51);">根据参数写入指定列</strong></h4> <p><br></p> <p class="ql-line-height-1_6">与上一种方法类似，无非多了两个 Set，一个需要写入的 Set，另一个不需要写入的 Set。</p> <div class="ql-code-block-container"> <div class="ql-code-block"> <span class="ql-token hljs-meta">@Test</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">public</span> <span class="ql-token hljs-keyword">void</span> <span class="ql-token hljs-title">excludeOrIncludeWrite()</span> { </div> <div class="ql-code-block"> List<SaleData> list = <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">ArrayList</span><>(); </div> <div class="ql-code-block"> list.add(<span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">SaleData</span>(<span class="ql-token hljs-number">1L</span>, <span class="ql-token hljs-string">"苹果"</span>, <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">BigDecimal</span>(<span class="ql-token hljs-string">"10.00"</span>), <span class="ql-token hljs-number">10</span>, <span class="ql-token hljs-number">1L</span>,<span class="ql-token hljs-string">"2024-01-01 13:00:00"</span>)); </div> <div class="ql-code-block"> list.add(<span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">SaleData</span>(<span class="ql-token hljs-number">2L</span>, <span class="ql-token hljs-string">"梨子"</span>, <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">BigDecimal</span>(<span class="ql-token hljs-string">"12.00"</span>), <span class="ql-token hljs-number">10</span>, <span class="ql-token hljs-number">1L</span>,<span class="ql-token hljs-string">"2025-01-01 13:00:00"</span>)); </div> <div class="ql-code-block"> list.add(<span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">SaleData</span>(<span class="ql-token hljs-number">3L</span>, <span class="ql-token hljs-string">"西瓜"</span>, <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">BigDecimal</span>(<span class="ql-token hljs-string">"5.00"</span>), <span class="ql-token hljs-number">10</span>, <span class="ql-token hljs-number">1L</span>,<span class="ql-token hljs-string">"2024-03-01 13:00:00"</span>)); </div> <div class="ql-code-block"> list.add(<span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">SaleData</span>(<span class="ql-token hljs-number">4L</span>, <span class="ql-token hljs-string">"香蕉"</span>, <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">BigDecimal</span>(<span class="ql-token hljs-string">"7.00"</span>), <span class="ql-token hljs-number">10</span>, <span class="ql-token hljs-number">1L</span>,<span class="ql-token hljs-string">"2024-02-01 13:00:00"</span>)); </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-type">String</span> <span class="ql-token hljs-variable">fileName</span> <span class="ql-token hljs-operator">=</span> <span class="ql-token hljs-string">"D:\\projects\\plant-sys-main\\src\\main\\resources\\销售数据2.xlsx"</span>; </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">// 无需写入的 Set 集合</span> </div> <div class="ql-code-block"> Set<String> excludSet = <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">HashSet</span><>(); </div> <div class="ql-code-block"> excludSet.add(<span class="ql-token hljs-string">"id"</span>); </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">// 需要写入的 Set 集合</span> </div> <div class="ql-code-block"> Set<String> includeSet = <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">HashSet</span><>(); </div> <div class="ql-code-block"> includeSet.add(<span class="ql-token hljs-string">"userId"</span>); </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">// 指定两个 Set，并写入 Excel</span> </div> <div class="ql-code-block"> EasyExcel.write(fileName, SaleDetailVO.class).includeColumnFieldNames(includeSet) </div> <div class="ql-code-block"> .excludeColumnFiledNames(excludSet).sheet(<span class="ql-token hljs-string">"Sheet1"</span>).doWrite(list); </div> <div class="ql-code-block"> } </div> </div> <p>在此段代码中，我指定了 id 不需要写入 Excel，userId 需要写入 Excel。最终可以看出，只有 userId 被写入 Excel 中。</p> <p><img src="https://pic.code-nav.cn/planet_post_image/1608639807578177538/mtfcuwsc.jpeg"></p> <p><br></p> <h4 class="ql-line-height-1_6"><strong style="color: rgb(51, 51, 51);">写入指定的列</strong></h4> <p><br></p> <p>可以利用 ExcelProperty 注解的 index 属性，指定某些字段写到 Excel 的某一列。</p> <div class="ql-code-block-container"> <div class="ql-code-block"><span class="ql-token hljs-meta">@Data</span> </div> <div class="ql-code-block"><span class="ql-token hljs-meta">@AllArgsConstructor</span> </div> <div class="ql-code-block"><span class="ql-token hljs-keyword">public</span> <span class="ql-token hljs-keyword">class</span> <span class="ql-token hljs-title">SaleData</span> <span class="ql-token hljs-keyword">implements</span> <span class="ql-token hljs-title">Serializable</span> { </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ExcelProperty(value = "订单号", index = 1)</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> Long id; </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ExcelProperty(value = "品种", index = 2)</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> String name; </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ExcelProperty(value = "价格", index = 3)</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> BigDecimal price; </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ExcelProperty(value = "数量", index = 4)</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> Integer totalNum; </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ExcelProperty(value = "交易对象", index = 5)</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> Long userId; </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ExcelProperty(value = "交易时间", index = 6)</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> String datetime; </div> <div class="ql-code-block"> } </div> </div> <p>代码很简单我就不多解释了，相较于最简单的写入 Excel 方式，唯一不同的就是加了写入对象的 index 属性。</p> <div class="ql-code-block-container"> <div class="ql-code-block"> <span class="ql-token hljs-meta">@Test</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">public</span> <span class="ql-token hljs-keyword">void</span> <span class="ql-token hljs-title">writeSpecificColumn()</span> { </div> <div class="ql-code-block"> List<SaleData> list = <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">ArrayList</span><>(); </div> <div class="ql-code-block"> list.add(<span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">SaleData</span>(<span class="ql-token hljs-number">1L</span>, <span class="ql-token hljs-string">"苹果"</span>, <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">BigDecimal</span>(<span class="ql-token hljs-string">"10.00"</span>), <span class="ql-token hljs-number">10</span>, <span class="ql-token hljs-number">1L</span>,<span class="ql-token hljs-string">"2024-01-01 13:00:00"</span>)); </div> <div class="ql-code-block"> list.add(<span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">SaleData</span>(<span class="ql-token hljs-number">2L</span>, <span class="ql-token hljs-string">"梨子"</span>, <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">BigDecimal</span>(<span class="ql-token hljs-string">"12.00"</span>), <span class="ql-token hljs-number">10</span>, <span class="ql-token hljs-number">1L</span>,<span class="ql-token hljs-string">"2025-01-01 13:00:00"</span>)); </div> <div class="ql-code-block"> list.add(<span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">SaleData</span>(<span class="ql-token hljs-number">3L</span>, <span class="ql-token hljs-string">"西瓜"</span>, <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">BigDecimal</span>(<span class="ql-token hljs-string">"5.00"</span>), <span class="ql-token hljs-number">10</span>, <span class="ql-token hljs-number">1L</span>,<span class="ql-token hljs-string">"2024-03-01 13:00:00"</span>)); </div> <div class="ql-code-block"> list.add(<span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">SaleData</span>(<span class="ql-token hljs-number">4L</span>, <span class="ql-token hljs-string">"香蕉"</span>, <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">BigDecimal</span>(<span class="ql-token hljs-string">"7.00"</span>), <span class="ql-token hljs-number">10</span>, <span class="ql-token hljs-number">1L</span>,<span class="ql-token hljs-string">"2024-02-01 13:00:00"</span>)); </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-type">String</span> <span class="ql-token hljs-variable">fileName</span> <span class="ql-token hljs-operator">=</span> <span class="ql-token hljs-string">"D:\\projects\\plant-sys-main\\src\\main\\resources\\销售数据3.xlsx"</span>; </div> <div class="ql-code-block"> EasyExcel.write(fileName, SaleDetailVO.class).sheet(<span class="ql-token hljs-string">"Sheet1"</span>).doWrite(list); </div> <div class="ql-code-block"> } </div> </div> <p>可以看出来，第一列被没有被写入，因为我的 index 是从 1 开始的，表格的第一列 index（索引）为 0。最终效果如下：</p> <p><img src="https://pic.code-nav.cn/planet_post_image/1608639807578177538/8896zyko.jpeg"></p> <p><br></p> <h4 class="ql-line-height-1_6"><strong style="color: rgb(51, 51, 51);">复杂的头写入</strong></h4> <p><br></p> <p>这个复杂头写入不太好解释，所以大家直接看代码和写入效果就懂了。</p> <div class="ql-code-block-container"> <div class="ql-code-block"><span class="ql-token hljs-meta">@Data</span> </div> <div class="ql-code-block"><span class="ql-token hljs-meta">@AllArgsConstructor</span> </div> <div class="ql-code-block"><span class="ql-token hljs-keyword">public</span> <span class="ql-token hljs-keyword">class</span> <span class="ql-token hljs-title">SaleData</span> <span class="ql-token hljs-keyword">implements</span> <span class="ql-token hljs-title">Serializable</span> { </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ExcelProperty({"主标题", "订单号"})</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> Long id; </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ExcelProperty({"主标题", "品种"})</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> String name; </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ExcelProperty({"主标题", "价格"})</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> BigDecimal price; </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ExcelProperty({"主标题", "数量"})</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> Integer totalNum; </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ExcelProperty({"主标题", "交易对象"})</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> Long userId; </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ExcelProperty({"主标题", "交易时间"})</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> String datetime; </div> <div class="ql-code-block"> } </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@Test</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">public</span> <span class="ql-token hljs-keyword">void</span> <span class="ql-token hljs-title">complexHeadWrite()</span> { </div> <div class="ql-code-block"> QueryWrapper<Sale> queryWrapper = <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">QueryWrapper</span><>(); </div> <div class="ql-code-block"> queryWrapper.orderBy(<span class="ql-token hljs-literal">true</span>, <span class="ql-token hljs-literal">false</span>, <span class="ql-token hljs-string">"createTime"</span>); </div> <div class="ql-code-block"> List<Sale> saleList = saleService.list(queryWrapper); </div> <div class="ql-code-block"> List<SaleDetailVO> saleDetailVOList = saleList.stream().map(sale -> { </div> <div class="ql-code-block"> <span class="ql-token hljs-type">SaleDetailVO</span> <span class="ql-token hljs-variable">saleDetailVO</span> <span class="ql-token hljs-operator">=</span> <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">SaleDetailVO</span>(); </div> <div class="ql-code-block"> saleDetailVO.setId(sale.getId()); </div> <div class="ql-code-block"> <span class="ql-token hljs-type">Date</span> <span class="ql-token hljs-variable">createTime</span> <span class="ql-token hljs-operator">=</span> sale.getCreateTime(); </div> <div class="ql-code-block"> <span class="ql-token hljs-type">SimpleDateFormat</span> <span class="ql-token hljs-variable">dateFormat</span> <span class="ql-token hljs-operator">=</span> <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">SimpleDateFormat</span>(<span class="ql-token hljs-string">"yyyy-MM-dd HH:mm:ss"</span>); </div> <div class="ql-code-block"> <span class="ql-token hljs-type">String</span> <span class="ql-token hljs-variable">datetime</span> <span class="ql-token hljs-operator">=</span> dateFormat.format(createTime); </div> <div class="ql-code-block"> saleDetailVO.setDatetime(datetime); </div> <div class="ql-code-block"> saleDetailVO.setTotalNum(sale.getTotalNum()); </div> <div class="ql-code-block"> saleDetailVO.setUserId(sale.getUserId()); </div> <div class="ql-code-block"> <span class="ql-token hljs-type">Plant</span> <span class="ql-token hljs-variable">plant</span> <span class="ql-token hljs-operator">=</span> plantService.getById(sale.getPlantId()); </div> <div class="ql-code-block"> saleDetailVO.setPrice(plant.getPrice()); </div> <div class="ql-code-block"> saleDetailVO.setName(plant.getName()); </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">return</span> saleDetailVO; </div> <div class="ql-code-block"> }) </div> <div class="ql-code-block"> .collect(Collectors.toList()); </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-type">String</span> <span class="ql-token hljs-variable">fileName</span> <span class="ql-token hljs-operator">=</span> <span class="ql-token hljs-string">"D:\\projects\\plant-sys-main\\src\\main\\resources\\销售数据4.xlsx"</span>; </div> <div class="ql-code-block"> EasyExcel.write(fileName, SaleData.class).sheet(<span class="ql-token hljs-string">"Sheet1"</span>).doWrite(saleDetailVOList); </div> <div class="ql-code-block"> } </div> </div> <p>运行效果：</p> <p><img src="https://pic.code-nav.cn/planet_post_image/1608639807578177538/oafu6f31.jpeg"></p> <p><br></p> <h4 class="ql-line-height-1_6"><strong style="color: rgb(51, 51, 51);">重复多次写入</strong></h4> <p><br></p> <p class="ql-line-height-1_6">前几种写入方式都只能在数据量不大的情况下才可使用，这个数量是指在 5000 一下，超过这个量级我们就得换种方式了，在此我就介绍一下官方演示的重复多次写入 Excel 方式。</p> <p>老实说看代码并不复杂，也就是加了个 for 循环罢了，对于重复多次写入，我们不仅可以写入不同的表单（Sheet）还能写入不同的对象，甚至写入不同的数据（即写入不同的 list）。</p> <p>利用这个方式我们可以在循环中调整不同的分页参数，然后分页查询数据库并把查到的数据写入 Excel 中，是不是很巧妙啊，要是一次性写入的话内存会溢出的。</p> <p>可能有人会问怎么写入不同的表单（Sheet）和对象，我这里提供个思路，你可以创建一个表单或者对象集合，可以是 List 也可以是 Map，然后根据循环的 i 去获取对应的元素嘛。</p> <div class="ql-code-block-container"> <div class="ql-code-block"> <span class="ql-token hljs-meta">@Test</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">public</span> <span class="ql-token hljs-keyword">void</span> <span class="ql-token hljs-title">repeatedWrite()</span> { </div> <div class="ql-code-block"> List<SaleData> list = <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">ArrayList</span><>(); </div> <div class="ql-code-block"> list.add(<span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">SaleData</span>(<span class="ql-token hljs-number">1L</span>, <span class="ql-token hljs-string">"苹果"</span>, <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">BigDecimal</span>(<span class="ql-token hljs-string">"10.00"</span>), <span class="ql-token hljs-number">10</span>, <span class="ql-token hljs-number">1L</span>,<span class="ql-token hljs-string">"2024-01-01 13:00:00"</span>)); </div> <div class="ql-code-block"> list.add(<span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">SaleData</span>(<span class="ql-token hljs-number">2L</span>, <span class="ql-token hljs-string">"梨子"</span>, <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">BigDecimal</span>(<span class="ql-token hljs-string">"12.00"</span>), <span class="ql-token hljs-number">10</span>, <span class="ql-token hljs-number">1L</span>,<span class="ql-token hljs-string">"2025-01-01 13:00:00"</span>)); </div> <div class="ql-code-block"> list.add(<span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">SaleData</span>(<span class="ql-token hljs-number">3L</span>, <span class="ql-token hljs-string">"西瓜"</span>, <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">BigDecimal</span>(<span class="ql-token hljs-string">"5.00"</span>), <span class="ql-token hljs-number">10</span>, <span class="ql-token hljs-number">1L</span>,<span class="ql-token hljs-string">"2024-03-01 13:00:00"</span>)); </div> <div class="ql-code-block"> list.add(<span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">SaleData</span>(<span class="ql-token hljs-number">4L</span>, <span class="ql-token hljs-string">"香蕉"</span>, <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">BigDecimal</span>(<span class="ql-token hljs-string">"7.00"</span>), <span class="ql-token hljs-number">10</span>, <span class="ql-token hljs-number">1L</span>,<span class="ql-token hljs-string">"2024-02-01 13:00:00"</span>)); </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-type">String</span> <span class="ql-token hljs-variable">fileName</span> <span class="ql-token hljs-operator">=</span> <span class="ql-token hljs-string">"D:\\projects\\plant-sys-main\\src\\main\\resources\\销售数据5.xlsx"</span>; </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">// 写入不同对象，不同 sheet</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">try</span> (<span class="ql-token hljs-type">ExcelWriter</span> <span class="ql-token hljs-variable">excelWriter2</span> <span class="ql-token hljs-operator">=</span> EasyExcel.write(fileName, SaleData.class).build()) { </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">for</span> (<span class="ql-token hljs-type">int</span> <span class="ql-token hljs-variable">i</span> <span class="ql-token hljs-operator">=</span> <span class="ql-token hljs-number">0</span>; i < <span class="ql-token hljs-number">5</span>; i++) { </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">// 这里每次都要创建writeSheet，这里注意必须指定writerSheet，而且sheetNo必须不一样。这里注意DemoData.class 可以每次都变，我这里为了方便所以用的同一个class</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-type">WriteSheet</span> <span class="ql-token hljs-variable">writeSheet</span> <span class="ql-token hljs-operator">=</span> EasyExcel.writerSheet(i, <span class="ql-token hljs-string">"模板"</span> + i).head(SaleData.class).build(); </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">// 分页去数据库查询数据，这里可以去数据库查询每一页的数据</span> </div> <div class="ql-code-block"> excelWriter2.write(list, writeSheet); </div> <div class="ql-code-block"> } </div> <div class="ql-code-block"> } </div> <div class="ql-code-block"> } </div> </div> <p><br></p> <p>写入效果如下，我们可以看到共有 5 个表单，因为我们循环写入了 5 个不同的表单，并且每个表单数据都是一样的。</p> <p><img src="https://pic.code-nav.cn/planet_post_image/1608639807578177538/2wg80xly.jpeg"></p> <p><br></p> <p><br></p> <h3 class="ql-line-height-1_6"><strong style="color: rgb(51, 51, 51);">Java 读取 Excel</strong></h3> <p><br></p> <p>想要了解 Java 如何实现读取 Excel 的小伙伴可以看看这篇文章哈——https://articles.zsxq.com/id_k751dhh0utwv.html</p> <p><br></p> <p>如果你觉得还不错的话就给我点个赞吧。</p> <h3 class="ql-line-height-1_6"><br></h3> </div> </body> </html>

EasyExcel 初使用—— Java 实现读取 Excel 功能

<html> <head></head> <body> <div class="content ql-editor"> <h3><strong style="color: rgb(51, 51, 51);">前言</strong></h3> <p><br></p> <p>在我们项目的开发中啊，前端有时候会传送 Excel 文件给后端（Java）去解析，那我们作为后端该如何实现对 Excel 文件的解析和数据读取呢？说到这我就不得不推荐 EasyExcel 了！</p> <p><br></p> <h3 class="ql-line-height-1_6"><strong style="color: rgb(51, 51, 51);">EasyExcel 介绍</strong></h3> <p><br></p> <p>引用下官方对于 EasyExcel 介绍：EasyExcel是一个基于Java的、快速、简洁、解决大文件内存溢出的Excel处理工具。他能让你在不用考虑性能、内存的等因素的情况下，快速完成Excel的读、写等功能。</p> <p>官方网址：<a href="https://easyexcel.opensource.alibaba.com/" target="_blank" style="color: rgb(65, 131, 196);">EasyExcel官方文档 - 基于Java的Excel处理工具 | Easy Excel</a></p> <p><br></p> <h3 class="ql-line-height-1_6"><strong style="color: rgb(51, 51, 51);">快速上手 EasyExcel</strong></h3> <p><br></p> <h4 class="ql-line-height-1_6"><strong style="color: rgb(51, 51, 51);">前置工作</strong></h4> <p><br></p> <p>先创建一个 Spring Boot 工程，并在 pom.xml 文件添加 EasyExcel 和 Lombok 依赖。</p> <div class="ql-code-block-container"> <div class="ql-code-block"> <span class="ql-token hljs-tag"><dependency></span> </div> <div class="ql-code-block"> <span class="ql-token hljs-tag"><groupId></span>org.projectlombok<span class="ql-token hljs-tag"></groupId></span> </div> <div class="ql-code-block"> <span class="ql-token hljs-tag"><artifactId></span>lombok<span class="ql-token hljs-tag"></artifactId></span> </div> <div class="ql-code-block"> <span class="ql-token hljs-tag"><optional></span>true<span class="ql-token hljs-tag"></optional></span> </div> <div class="ql-code-block"> <span class="ql-token hljs-tag"></dependency></span> </div> <div class="ql-code-block"> <span class="ql-token hljs-tag"><dependency></span> </div> <div class="ql-code-block"> <span class="ql-token hljs-tag"><groupId></span>com.alibaba<span class="ql-token hljs-tag"></groupId></span> </div> <div class="ql-code-block"> <span class="ql-token hljs-tag"><artifactId></span>easyexcel<span class="ql-token hljs-tag"></artifactId></span> </div> <div class="ql-code-block"> <span class="ql-token hljs-tag"><version></span>3.1.1<span class="ql-token hljs-tag"></version></span> </div> <div class="ql-code-block"> <span class="ql-token hljs-tag"></dependency></span> </div> </div> <p><br></p> <h4 class="ql-line-height-1_6"><strong style="color: rgb(51, 51, 51);">知道表头</strong></h4> <p><br></p> <p>如果我们知道 Excel 数据的表头，即每列数据的类型包括有多少列时就可以用此方法读取 Excel 文件数据。</p> <p>我们以下图数据为例，对改 Excel 中的数据进行获取和处理。</p> <p><img src="https://pic.code-nav.cn/planet_post_image/1608639807578177538/wi8t7qfn.jpeg"></p> <p><br></p> <h5 class="ql-line-height-1_6"><strong style="color: rgb(51, 51, 51);">方法一：</strong></h5> <p><br></p> <p>首先我们创建一个名为 ExcelData 的 Java 对象，共有两个属性，分别是 date（日期列数据）和 useNum（用户列数据），每个属性对应 Excel 每列某一行中的数据。那么很显而易见，每一行的数据就是一个 ExcelData 对象，所有行的数据合起来就是一个泛型为 ExcelData 的 ExcelData 的集合。</p> <div class="ql-code-block-container"> <div class="ql-code-block"><span class="ql-token hljs-meta">@Data</span> </div> <div class="ql-code-block"><span class="ql-token hljs-keyword">public class ExcelData implements Serializable {</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">/**</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> * 对应表格的日期列</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> */</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> String date; </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">/**</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> * 对应表格的用户数列</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> */</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> Integer userNum; </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> } </div> </div> <p>随后编写一个测试类，并在其中编写测试方法。</p> <p>EasyExcel 的 read 方法有很多中构造方法，其中 Class head 就是表头类型，传入它还要传入 ReadListener 监听器，以便在去读取每行数据时做些自定义操作。我们直接传入它的实现类实例，因为 PageReadListener 支持逐页读取数据，通过读取指定行数的数据保证占用更少的内存。</p> <p><img src="https://pic.code-nav.cn/planet_post_image/1608639807578177538/zttn39zp.jpeg"></p> <p>话不多说直接上代码：</p> <div class="ql-code-block-container"> <div class="ql-code-block"> <span class="ql-token hljs-comment">/**</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> * 知道表头，并形成映射关系</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> */</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@Test</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">public</span> <span class="ql-token hljs-keyword">void</span> <span class="ql-token hljs-title">doImportsForMapping()</span> <span class="ql-token hljs-keyword">throws</span> FileNotFoundException{ </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">// 读取 resource 目录下的 Excel 文件（网站数据.xlsx）</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-type">File</span> <span class="ql-token hljs-variable">file</span> <span class="ql-token hljs-operator">=</span> ResourceUtils.getFile(<span class="ql-token hljs-string">"classpath:网站数据.xlsx"</span>); </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">// 创建一个 list 存储每行的数据，即 ExcelData 对象</span> </div> <div class="ql-code-block"> List<ExcelData> list = <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">ArrayList</span><>(); </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">// 直接使用 EasyExcel 的 read 方法，同时定义表头的类型，以便将列中数据映射为 ExcelData 对象</span> </div> <div class="ql-code-block"> EasyExcel.read(file, ExcelData.class, <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">PageReadListener</span><ExcelData>(dataList -> { </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">// 并且每行数据，并将其 add 至 list 中</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">for</span> (ExcelData excelData : dataList) { </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">if</span> (excelData != <span class="ql-token hljs-literal">null</span>) { </div> <div class="ql-code-block"> list.add(excelData); </div> <div class="ql-code-block"> } </div> <div class="ql-code-block"> } </div> <div class="ql-code-block"> })).excelType(ExcelTypeEnum.XLSX).sheet().doRead(); <span class="ql-token hljs-comment">// 指定 Excel 的文件后缀，开始分析读取</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">for</span> (ExcelData excelData : list) { </div> <div class="ql-code-block"> System.out.println(excelData.getDate() + <span class="ql-token hljs-string">","</span> + excelData.getUserNum()); </div> <div class="ql-code-block"> } </div> <div class="ql-code-block"> } </div> </div> <p>执行结果：</p> <p><img src="https://pic.code-nav.cn/planet_post_image/1608639807578177538/293m28iq.jpeg"></p> <p><br></p> <h5 class="ql-line-height-1_6"><strong style="color: rgb(51, 51, 51);">方法二：</strong></h5> <p><br></p> <p class="ql-line-height-1_6">方法一是直接一次性读取 Excel 中的数据，缺少要读取的数据行数和一些自定义操作，所以我们在这里对上面的代码增强一下。</p> <p>在此方法中我们通过匿名内部类的方式实现 ReadListenser 接口，无需额外写一个类去实现 ReadListener了。我们设置了一个临时存储的列表（大小为 2），当每次读取的数据（执行 invoke 方法）添加到临时存储表中。当其长度超过 2 时进行全部删除，在删除前我们可以将临时存储的列表存到数据库中，或进行一些其他的自定义操作。</p> <p>doAfterAllAnalysed 方法是分析并获取所有的数据后会执行的一个方法，我们可以在其中打上日志，表示 Excel 所有数据已存入数据库中。</p> <div class="ql-code-block-container"> <div class="ql-code-block"> <span class="ql-token hljs-comment">/**</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> * 知道表头，并形成映射关系</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> * @throws FileNotFoundException</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> */</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@Test</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">public</span> <span class="ql-token hljs-keyword">void</span> <span class="ql-token hljs-title">doImportsForMappingByInnerClass()</span> <span class="ql-token hljs-keyword">throws</span> FileNotFoundException{ </div> <div class="ql-code-block"> <span class="ql-token hljs-type">File</span> <span class="ql-token hljs-variable">file</span> <span class="ql-token hljs-operator">=</span> ResourceUtils.getFile(<span class="ql-token hljs-string">"classpath:网站数据.xlsx"</span>); </div> <div class="ql-code-block"> EasyExcel.read(file, ExcelData.class, <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">ReadListener</span><ExcelData>() { </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">// 单次缓存的数据量</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">public</span> <span class="ql-token hljs-keyword">static</span> <span class="ql-token hljs-keyword">final</span> <span class="ql-token hljs-type">int</span> <span class="ql-token hljs-variable">BATCH_COUNT</span> <span class="ql-token hljs-operator">=</span> <span class="ql-token hljs-number">2</span>; </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">// 临时存储的列表</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> List<ExcelData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT); </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@Override</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">public</span> <span class="ql-token hljs-keyword">void</span> <span class="ql-token hljs-title">invoke(ExcelData excelData, AnalysisContext analysisContext)</span> { </div> <div class="ql-code-block"> cachedDataList.add(excelData); </div> <div class="ql-code-block"> getData(excelData); </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">if</span> (cachedDataList.size() >= BATCH_COUNT) { </div> <div class="ql-code-block"> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT); </div> <div class="ql-code-block"> } </div> <div class="ql-code-block"> } </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@Override</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">public</span> <span class="ql-token hljs-keyword">void</span> <span class="ql-token hljs-title">doAfterAllAnalysed(AnalysisContext analysisContext)</span> { </div> <div class="ql-code-block"> System.out.println(<span class="ql-token hljs-string">"存储数据库成功"</span>); </div> <div class="ql-code-block"> } </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> <span class="ql-token hljs-keyword">void</span> <span class="ql-token hljs-title">getData(ExcelData excelData)</span> { </div> <div class="ql-code-block"> System.out.println(excelData.getDate() + <span class="ql-token hljs-string">","</span> + excelData.getUserNum()); </div> <div class="ql-code-block"> } </div> <div class="ql-code-block"> }).excelType(ExcelTypeEnum.XLSX).sheet().doRead(); </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> } </div> </div> <p>执行结果：</p> <p><img src="https://pic.code-nav.cn/planet_post_image/1608639807578177538/hdrv6000.jpeg"></p> <p><br></p> <h4 class="ql-line-height-1_6"><strong style="color: rgb(51, 51, 51);">不知道表头</strong></h4> <p><br></p> <p>上面的两种方法都是我们知道表头，包括列的类型和列数量的情况下对 Excel 文件进行数据获取的。那我们不知道表头信息，又该如何操作呢？</p> <p>我们依赖利用 EasyExcel 的 read 方法，和前面的步骤大差不差，只不过这次参数少了，如果你还要获取表头即表格的第一行数据，还可通过 headRowNumber 方法指定首行编号为 0。</p> <p>此时返回的是一个 List<Map<Integer, String>> 集合，其中 Map 的键对应表格的列编号（从 0 开始），值就是对应某一行某一列的值，List 的索引代表某一行的数据。调用 Map 对象的 values() 方法即可直接获取某一行数据的集合，List<Map<Integer, String>> 就是所有行数据的集合。我们这说可能不太直观，我把它打印出来给你们看就很简单明了了。</p> <div class="ql-code-block-container"> <div class="ql-code-block"> [{0=日期, 1=用户数}, {0=1号, 1=10}, {0=2号, 1=20}, {0=3号, 1=30}, {0=4号, 1=70}, {0=5号, 1=20}, {0=6号, 1=29}, {0=7号, 1=24}, {0=8号, 1=31}, {0=9号, 1=40}, {0=10号, 1=38}, {0=11号, 1=43}] </div> </div> <p>代码如下：</p> <div class="ql-code-block-container"> <div class="ql-code-block"> <span class="ql-token hljs-meta">@Test</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">public</span> <span class="ql-token hljs-keyword">void</span> <span class="ql-token hljs-title">doImport()</span> <span class="ql-token hljs-keyword">throws</span> FileNotFoundException { </div> <div class="ql-code-block"> List<Map<Integer, String>> list = <span class="ql-token hljs-literal">null</span>; </div> <div class="ql-code-block"> <span class="ql-token hljs-type">File</span> <span class="ql-token hljs-variable">file</span> <span class="ql-token hljs-operator">=</span> ResourceUtils.getFile(<span class="ql-token hljs-string">"classpath:网站数据.xlsx"</span>); </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">try</span> { </div> <div class="ql-code-block"> list = EasyExcel.read(file) </div> <div class="ql-code-block"> .excelType(ExcelTypeEnum.XLSX) </div> <div class="ql-code-block"> .sheet() </div> <div class="ql-code-block"> .headRowNumber(<span class="ql-token hljs-number">0</span>) </div> <div class="ql-code-block"> .doReadSync(); </div> <div class="ql-code-block"> } <span class="ql-token hljs-keyword">catch</span> (Exception e) { </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">throw</span> <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">RuntimeException</span>(<span class="ql-token hljs-string">"读取 Excel 文件失败"</span>); </div> <div class="ql-code-block"> } </div> <div class="ql-code-block"> <span class="ql-token hljs-type">StringBuilder</span> <span class="ql-token hljs-variable">stringBuilder</span> <span class="ql-token hljs-operator">=</span> <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">StringBuilder</span>(); </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">for</span> (<span class="ql-token hljs-type">int</span> i=<span class="ql-token hljs-number">0</span>;i<list.size();i++) { </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">// 转为 LinkedHashMap 主要是为了保证读取的数据和表格顺序一致</span> </div> <div class="ql-code-block"> LinkedHashMap<Integer, String> linkedHashMap = (LinkedHashMap) list.get(i); </div> <div class="ql-code-block"> List<String> dataList = linkedHashMap.values().stream() </div> <div class="ql-code-block"> .filter(ObjectUtils::isNotEmpty).collect(Collectors.toList()); </div> <div class="ql-code-block"> stringBuilder.append(StringUtils.join(dataList, <span class="ql-token hljs-string">","</span>)).append(<span class="ql-token hljs-string">"\n"</span>); </div> <div class="ql-code-block"> } </div> <div class="ql-code-block"> System.out.println(stringBuilder.toString()); </div> <div class="ql-code-block"> } </div> </div> <p>执行结果：</p> <p><img src="https://pic.code-nav.cn/planet_post_image/1608639807578177538/e3z65l3w.jpeg"></p> <p><br></p> <h3 class="ql-line-height-1_6"><strong style="color: rgb(51, 51, 51);">总结</strong></h3> <p><br></p> <p>如果知道表头并且数据量较小，就用方法一，如果数据量较大或者想添加一些自定操作就用方法二。如果不知道表头并且想要读取表头的信息就用方法三。</p> </div> </body> </html>

EasyExcel导出文件，并解决格式报错

<html> <head></head> <body> <div class="content ql-editor"> <p>记录下项目里数据导出为excel的碎片，并用简单粗暴的方式规避了报错，希望有大佬能完善一下另一种优雅的解决办法</p> <p><br></p> <h3><strong style="color: rgb(52, 73, 94);">引入pom文件</strong></h3> <p><br></p> <div class="ql-code-block-container"> <div class="ql-code-block"><span class="ql-token hljs-comment"></span> </div> <div class="ql-code-block"><span class="ql-token hljs-tag"><dependency></span> </div> <div class="ql-code-block"> <span class="ql-token hljs-tag"><groupId></span>com.alibaba<span class="ql-token hljs-tag"></groupId></span> </div> <div class="ql-code-block"> <span class="ql-token hljs-tag"><artifactId></span>easyexcel<span class="ql-token hljs-tag"></artifactId></span> </div> <div class="ql-code-block"> <span class="ql-token hljs-tag"><version></span>3.2.1<span class="ql-token hljs-tag"></version></span> </div> <div class="ql-code-block"><span class="ql-token hljs-tag"></dependency></span> </div> </div> <p><br></p> <h3><strong style="color: rgb(52, 73, 94);">定义导出对象</strong></h3> <p><br></p> <div class="ql-code-block-container"> <div class="ql-code-block"><span class="ql-token hljs-comment">/**</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> * Excel订单信息导出</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> *</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> * @author 玄德</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> */</span> </div> <div class="ql-code-block"><span class="ql-token hljs-meta">@Data</span> </div> <div class="ql-code-block"><span class="ql-token hljs-keyword">public</span> <span class="ql-token hljs-keyword">class</span> <span class="ql-token hljs-title">IndentExcelVO</span> { </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">/**</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> * 订单编号</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> */</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ColumnWidth(20)</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@NumberFormat("#")</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ExcelProperty(value = "订单编号", index = 0)</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> String id; </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">/**</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> * 用户姓名</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> */</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ExcelProperty(value = "用户姓名", index = 1)</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> String realName; </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">/**</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> * 手机号</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> */</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ColumnWidth(15)</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ExcelProperty(value = "手机号", index = 2)</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> String information; </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">/**</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> * 接亲日期</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> */</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ColumnWidth(15)</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ExcelProperty(value = "接亲日期", index = 3)</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> String date_time; </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">/**</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> * 开始时间</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> */</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ColumnWidth(15)</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ExcelProperty(value = "开始时间", index = 4)</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> String start_time; </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">/**</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> * 结束时间</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> */</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ColumnWidth(15)</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ExcelProperty(value = "结束时间", index = 5)</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> String end_time; </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">/**</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> * 接亲地址</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> */</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ColumnWidth(20)</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ExcelProperty(value = "接亲地址", index = 6)</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> String address; </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">/**</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> * 订单总金额</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> */</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@NumberFormat("#")</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ExcelProperty(value = "订单总金额", index = 7)</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> Double amount; </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">/**</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> * 订单状态</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> */</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ExcelProperty(value = "订单状态", index = 8)</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> String indent_state; </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">/**</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> * 支付状态</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> */</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ExcelProperty(value = "支付状态", index = 9)</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> String payment_state; </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">/**</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> * 订单创建时间</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> */</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ColumnWidth(15)</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-meta">@ExcelProperty(value = "订单创建时间", index = 10)</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">private</span> String createTime; </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> } </div> </div> <p><br></p> <h3><strong style="color: rgb(52, 73, 94);">定义工具类</strong></h3> <p><br></p> <div class="ql-code-block-container"> <div class="ql-code-block"><span class="ql-token hljs-comment">/**</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> * Excel工具类</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> *</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> * @author 玄德</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> */</span> </div> <div class="ql-code-block"><span class="ql-token hljs-keyword">public</span> <span class="ql-token hljs-keyword">class</span> <span class="ql-token hljs-title">ExcelUtils</span> { </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">/**</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> * 获取路径</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> *</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> * @return 当前路径</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> */</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">public</span> <span class="ql-token hljs-keyword">static</span> String <span class="ql-token hljs-title">getPath()</span> { </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">return</span> ExcelUtils.class.getResource(<span class="ql-token hljs-string">"/"</span>).getPath(); </div> <div class="ql-code-block"> } </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">/**</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> * 创建新文件</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> *</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> * @param pathName 文件名</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> * @return 文件</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> */</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">public</span> <span class="ql-token hljs-keyword">static</span> File <span class="ql-token hljs-title">createNewFile(String pathName)</span> { </div> <div class="ql-code-block"> <span class="ql-token hljs-type">File</span> <span class="ql-token hljs-variable">file</span> <span class="ql-token hljs-operator">=</span> <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">File</span>(getPath() + pathName); </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">if</span> (file.exists()) { </div> <div class="ql-code-block"> file.delete(); </div> <div class="ql-code-block"> } <span class="ql-token hljs-keyword">else</span> { </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">if</span> (!file.getParentFile().exists()) { </div> <div class="ql-code-block"> file.getParentFile().mkdirs(); </div> <div class="ql-code-block"> } </div> <div class="ql-code-block"> } </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">return</span> file; </div> <div class="ql-code-block"> } </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">/**</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> * 设置响应结果</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> *</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> * @param response 响应结果对象</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> * @param rawFileName 文件名</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> */</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">public</span> <span class="ql-token hljs-keyword">static</span> <span class="ql-token hljs-keyword">void</span> <span class="ql-token hljs-title">setExcelResponseProp(HttpServletResponse response, String rawFileName)</span> <span class="ql-token hljs-keyword">throws</span> IOException { </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">//设置内容类型</span> </div> <div class="ql-code-block"> response.setContentType(<span class="ql-token hljs-string">"application/vnd.vnd.ms-excel"</span>); </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">//设置编码格式</span> </div> <div class="ql-code-block"> response.setCharacterEncoding(<span class="ql-token hljs-string">"utf-8"</span>); </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">//设置导出文件名称（避免乱码）</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-type">String</span> <span class="ql-token hljs-variable">fileName</span> <span class="ql-token hljs-operator">=</span> URLEncoder.encode(rawFileName.concat(<span class="ql-token hljs-string">".xlsx"</span>), <span class="ql-token hljs-string">"UTF-8"</span>); </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">// 设置响应头</span> </div> <div class="ql-code-block"> response.setHeader(<span class="ql-token hljs-string">"Content-disposition"</span>, <span class="ql-token hljs-string">"attachment;filename*=utf-8''"</span> + fileName); </div> <div class="ql-code-block"> } </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">/**</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> * Date转String</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> *</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> * @param date 日期</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> * @return 字符串</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> */</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">public</span> <span class="ql-token hljs-keyword">static</span> String <span class="ql-token hljs-title">dateToString(Date date)</span> { </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">if</span> (date == <span class="ql-token hljs-literal">null</span>) { </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">throw</span> <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">BusinessException</span>(ErrorCode.NOT_FOUND_ERROR); </div> <div class="ql-code-block"> } </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">// Date转换为String</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-type">SimpleDateFormat</span> <span class="ql-token hljs-variable">sdf</span> <span class="ql-token hljs-operator">=</span> <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">SimpleDateFormat</span>(<span class="ql-token hljs-string">"yyyy-MM-dd"</span>); </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">return</span> sdf.format(date); </div> <div class="ql-code-block"> } </div> <div class="ql-code-block"> </div> <div class="ql-code-block"> } </div> </div> <p><br></p> <h3><strong style="color: rgb(52, 73, 94);">编写接口</strong></h3> <p><br></p> <div class="ql-code-block-container"> <div class="ql-code-block"><span class="ql-token hljs-comment">/**</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> * 订单信息导出</span> </div> <div class="ql-code-block"><span class="ql-token hljs-comment"> */</span> </div> <div class="ql-code-block"><span class="ql-token hljs-meta">@GetMapping("/download")</span> </div> <div class="ql-code-block"><span class="ql-token hljs-meta">@AuthCheck(mustRole = ADMIN_ROLE)</span> </div> <div class="ql-code-block"><span class="ql-token hljs-keyword">public</span> <span class="ql-token hljs-keyword">void</span> <span class="ql-token hljs-title">download(HttpServletResponse response)</span> <span class="ql-token hljs-keyword">throws</span> IOException { </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">// 获取数据，根据自身业务修改</span> </div> <div class="ql-code-block"> List<Indent> data = indentService.list(); </div> <div class="ql-code-block"> List<IndentExcelVO> indentList = data.stream().map(item -> { </div> <div class="ql-code-block"> <span class="ql-token hljs-type">IndentExcelVO</span> <span class="ql-token hljs-variable">indentExcelVO</span> <span class="ql-token hljs-operator">=</span> <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">IndentExcelVO</span>(); </div> <div class="ql-code-block"> BeanUtils.copyProperties(item, indentExcelVO); </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">// Date转字符串</span> </div> <div class="ql-code-block"> indentExcelVO.setDate_time(ExcelUtils.dateToString(item.getDate_time())); </div> <div class="ql-code-block"> indentExcelVO.setCreateTime(ExcelUtils.dateToString(item.getCreateTime())); </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">return</span> indentExcelVO; </div> <div class="ql-code-block"> }).collect(Collectors.toList()); </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">// 设置导出名称</span> </div> <div class="ql-code-block"> ExcelUtils.setExcelResponseProp(response, <span class="ql-token hljs-string">"订单信息"</span>); </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">// 获取输出流名称</span> </div> <div class="ql-code-block"> <span class="ql-token hljs-type">OutputStream</span> <span class="ql-token hljs-variable">outputStream</span> <span class="ql-token hljs-operator">=</span> response.getOutputStream(); </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">// 这里需要指定写用哪个class去写，然后写到第一个sheet，名字为模板然后文件流会自动关闭</span> </div> <div class="ql-code-block"> EasyExcel.write(outputStream, IndentExcelVO.class) <span class="ql-token hljs-comment">// 对应实体类</span> </div> <div class="ql-code-block"> .sheet(<span class="ql-token hljs-string">"订单数据"</span>) <span class="ql-token hljs-comment">// sheet页名称</span> </div> <div class="ql-code-block"> .doWrite(indentList); <span class="ql-token hljs-comment">// 导出的数据集合</span> </div> <div class="ql-code-block"> } </div> </div> <p><br></p> <h3><strong style="color: rgb(52, 73, 94);">遇到的问题</strong></h3> <blockquote> <div class="blockquote-item"> 关于Date字段导出问题 </div> </blockquote> <p>启动服务，调用接口，成功导出 Excel文件，但是文件没有数据，且报错：Can not find ‘Converter‘ support class Date.</p> <p>报错原因：导出 Excel 表格时候，默认不支持 DateTime 日期格式，所以需要指定 DateTime 类型的字段的日期格式。</p> <p>解决方式1：将Date转换为String</p> <div class="ql-code-block-container"> <div class="ql-code-block"> List<IndentExcelVO> indentList = data.stream().map(item -> { </div> <div class="ql-code-block"> <span class="ql-token hljs-type">IndentExcelVO</span> <span class="ql-token hljs-variable">indentExcelVO</span> <span class="ql-token hljs-operator">=</span> <span class="ql-token hljs-keyword">new</span> <span class="ql-token hljs-title">IndentExcelVO</span>(); </div> <div class="ql-code-block"> BeanUtils.copyProperties(item, indentExcelVO); </div> <div class="ql-code-block"> <span class="ql-token hljs-comment">// Date转字符串</span> </div> <div class="ql-code-block"> indentExcelVO.setDate_time(ExcelUtils.dateToString(item.getDate_time())); </div> <div class="ql-code-block"> indentExcelVO.setCreateTime(ExcelUtils.dateToString(item.getCreateTime())); </div> <div class="ql-code-block"> <span class="ql-token hljs-keyword">return</span> indentExcelVO; </div> <div class="ql-code-block"> }).collect(Collectors.toList()); </div> </div> <p>解决方式2：定义转换器</p> <p><strong>求个大佬给个解决方案，我试了好多都不好使</strong></p> <p><br></p> <p><br></p> <p>#技术# #EasyExcel# #知识碎片#</p> </div> </body> </html>