开源轮子 - EasyExcel02(深入实践)

EasyExcel02 - 深入实践

   本文整理自掘金大佬 - 竹子爱熊猫
https://juejin.cn/post/7405158771017220131
  

一：通用监听器

利用EasyExcel分析excel文件时，针对差别的业务场景，通常必要界说差别的监听器，如：

• 现在必要导入商品数据，就必要界说一个ProductListener；
  
• 现在必要导入员工数据，就必要界说一个StaffListener；
  
• ……
  

很显然，这一步会造成大量性子类似的class被界说出来，以是，能否封装一个通用监听器，以此来镌汰这步工作量与重复类呢
1：通用监听器

不管是什么业务场景下的Excel导入，都会颠末“分析文件、提取数据、举行业务处理处罚”这三步
除开第三步外，前两步逻辑是类似的，既然逻辑类似，那么天然可以抽象成通用监听器

1. package com.example.bootrocketmq.study.wheel.easyexcel;
3. import com.alibaba.excel.context.AnalysisContext;
4. import com.alibaba.excel.event.AnalysisEventListener;
6. import java.lang.reflect.Field;
7. import java.util.ArrayList;
8. import java.util.List;
9. import java.util.Map;
11. /**
12. * @author cui haida
13. * 2024/12/22
14. */
15. public class CommonListener<T> extends AnalysisEventListener<T> {
17.     //创建list集合封装最终的数据
18.     private final List<T> data;
20.     // 字段列表
21.     private final Field[] fields;
22.     // 类
23.     private final Class<T> clazz;
24.     // 是否对excel的有效性进行校验，如果设置为true, 将会检验excel模板的头和数据模型类字段是否匹配
25.     private boolean validateSwitch = true;
27.     public CommonListener(Class<T> clazz) {
28.         fields = clazz.getDeclaredFields();
29.         this.clazz = clazz;
30.         this.data = new ArrayList<T>();
31.     }
33.     /*
34.      * 每解析到一行数据都会触发
35.      * */
36.     @Override
37.     public void invoke(T row, AnalysisContext analysisContext) {
38.         data.add(row);
39.     }
41.     /*
42.      * 读取到excel头信息时触发，会将表头数据转为Map集合
43.      * */
44.     @Override
45.     public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
46.         // 校验读到的excel表头是否与数据模型类匹配
47.         if (validateSwitch) {
48.             // 如果需要进行正确性校验，将会进行工具方法进行excel模板校验 -> header和数据模型类字段关系校验
49.             ExcelUtil.validateExcelTemplate(headMap, clazz, fields);
50.         }
51.     }
53.     /*
54.      * 所有数据解析完之后触发
55.      * */
56.     @Override
57.     public void doAfterAllAnalysed(AnalysisContext analysisContext) {}
59.     /*
60.      * 关闭excel表头验证
61.      * */
62.     public void offValidate() {
63.         this.validateSwitch = false;
64.     }
66.     /*
67.      * 返回解析到的所有数据
68.      * */
69.     public List<T> getData() {
70.         return data;
71.     }
72. }

复制代码

上述界说了一个通用监听器，实在跟EasyExcel里提供的SyncReadListener非常类似，只不外我们这里做了两点优化

• 利用泛型来取代Object，使其变得更加的机动，获取数据的时间无需举行逼迫转换
  
• 增长模板校验机制，查抄excel的头信息和数据模子字段的匹配关系
  

为什么要有第二个呢？
全部excel导入的业务场景，都是先下载模板，再根据模板指引填写数据，末了才上传填写好的excel文件，这是导入场景的业务流程
如果你不对传入的excel文件举行模板校验，这时就算任意传个excel文件，EasyExcel也照旧不会报错，而是分析出多行全部字段为空的数据，从而触发你背面的业务逻辑引发未知Bug

1. package com.example.bootrocketmq.study.wheel.easyexcel;
3. import com.alibaba.excel.annotation.ExcelIgnore;
4. import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
5. import com.alibaba.excel.annotation.ExcelProperty;
6. import com.alibaba.excel.exception.ExcelAnalysisException;
7. import lombok.extern.slf4j.Slf4j;
9. import java.lang.reflect.Field;
10. import java.util.Collection;
11. import java.util.Map;
13. /**
14. * @author cui haida
15. * 2024/12/22
16. */
17. @Slf4j
18. public class ExcelUtil {
19.     /*
20.      * 校验excel文件的表头，与数据模型类的映射关系是否匹配
21.      * */
22.     public static void validateExcelTemplate(Map<Integer, String> headMap, Class<?> clazz, Field[] fields) {
23.         // 拿到头部名称
24.         Collection<String> headNames = headMap.values();
26.         // 类上是否存在忽略excel字段的注解
27.         boolean classIgnore = clazz.isAnnotationPresent(ExcelIgnoreUnannotated.class);
28.         int count = 0;
29.         for (Field field : fields) {
30.             // 如果字段上存在忽略注解，则跳过当前字段
31.             if (field.isAnnotationPresent(ExcelIgnore.class)) {
32.                 continue;
33.             }
35.             ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
36.             if (null == excelProperty) {
37.                 // 如果类上也存在忽略注解，则跳过所有未使用ExcelProperty注解的字段
38.                 if (classIgnore) {
39.                     continue;
40.                 }
41.                 // 如果检测到既未忽略、又未映射excel列的字段，则抛出异常提示模板不正确
42.                 throw new ExcelAnalysisException("请检查导入的excel文件是否按模板填写!");
43.             }
45.             // 校验数据模型类上绑定的名称，是否与excel列名匹配
46.             String[] value = excelProperty.value();
47.             String name = value[0];
48.             if (name != null && !name.isEmpty() && !headNames.contains(name)) {
49.                 throw new ExcelAnalysisException("请检查导入的excel文件是否按模板填写!");
50.             }
51.             // 更新有效字段的数量
52.             count++;
53.         }
54.         // 最后校验数据模型类的有效字段数量，与读到的excel列数量是否匹配
55.         if (headMap.size() != count) {
56.             throw new ExcelAnalysisException("请检查导入的excel文件是否按模板填写!");
57.         }
58.     }
59. }

复制代码

先来看三个入参，分别是当前读到的表头聚集，以及数据模子类的class对象和字段数组，这三个参数会用来校验模板的准确性。

• 注意点一：ExcelProperty注解提供了列名、权重、索引三种列匹配模式，可现在只是基于列名实现了校验逻辑，以是界说的数据模子类上，不支持利用index、order来指定字段与excel列的匹配关系（也可以自行改造上面的第四步校验逻辑）
  
• 注意点二：出于校验机制的存在，以是这个通用监听器无法正常读取多行头的excel文件，如果你想要正常分析多行头文件，则可以在创建监听器之后，手动调用offValidate()方法来关闭模板校验机制，这时就能制止校验机制干扰多行头文件的读取
  
• 注意点三：如果你利用的EasyExcel版本较低，分析excel数据时不会主动忽略已利用过的空行，即填写过内容又删除的数据行，在分析时仍旧会被辨认成一条数据，这种环境必要在监听器的invoke()方法中主动过滤
  

2：分批处理处罚监听器

前面封装的通用监听器，现实上只能满足数据量不大的业务场景，当数据量到达几万行、数十万、百万行时，如果再利用这个通用监听器就会存在OOM风险
由于分析到的全部数据，都会暂存到内部的data聚集里，直至整个文件全部数据行读取竣事
对于大文件而言，这种模式会给内存造成较大的负担，一旦同时导入的excel文件数量过多，内存资源耗尽就会引发内存溢出标题，怎么办？可以选择分批处理处罚读取到的数据

1. package com.example.bootrocketmq.study.wheel.easyexcel;
3. import com.alibaba.excel.context.AnalysisContext;
4. import com.alibaba.excel.event.AnalysisEventListener;
6. import java.lang.reflect.Field;
7. import java.util.ArrayList;
8. import java.util.List;
9. import java.util.Map;
10. import java.util.function.Consumer;
12. /**
13. * @author cui haida
14. * 2024/12/22
15. */
16. public class BatchHandleListener<T> extends AnalysisEventListener<T> {
18.     // 每次处理的行数(每次处理1000行 - 批次大小)
19.     private static int BATCH_NUMBER = 1000;
20.     // 临时存储读取到的excel数据
21.     private List<T> data;
22.     // 行数和批次编号
23.     private int rows, batchNo;
24.     // 是否要进行excel模板校验，如果需要校验，将validateSwitch设置为true
25.     private boolean validateSwitch = true;
27.     /*
28.      * 每批数据的业务逻辑处理器
29.      * 说明：如果业务方法会返回结果，可以将换成Function接口，同时类上新增一个类型参数
30.      **/
31.     private final Consumer<List<T>> businessHandler;
33.     /*
34.      * 用于校验excel模板正确性的字段
35.      **/
36.     private final Field[] fields;
37.     private final Class<T> clazz;
39.     /**
40.      * @author cui haida
41.      * 方法编写日期: 2024/12/22
42.      * desc -> 构造方法1 - 使用的批次大小为默认的1000条
43.      */
44.     public BatchHandleListener(Class<T> clazz, Consumer<List<T>> handle) {
45.         // 通过构造器为字段赋值，用于校验excel文件与模板十分匹配
46.         this(clazz, handle, BATCH_NUMBER);
47.     }
49.     /**
50.      * @author cui haida
51.      * 方法编写日期: 2024/12/22
52.      * desc ->  构造方法2 - 传入具体使用的批次大小
53.      */
54.     public BatchHandleListener(Class<T> clazz, Consumer<List<T>> handle, int batchNumber) {
55.         // 通过构造器为字段赋值，用于校验excel文件与模板十分匹配
56.         this.clazz = clazz;
57.         this.fields = clazz.getDeclaredFields();
58.         // 初始化临时存储数据的集合，及外部传入的业务方法
59.         this.businessHandler = handle;
60.         BATCH_NUMBER = batchNumber;
61.         this.data = new ArrayList<>(BATCH_NUMBER);
62.     }
64.     /*
65.      * 读取到excel头信息时触发，会将表头数据转为Map集合（用于校验导入的excel文件与模板是否匹配）
66.      *   注意点1：当前校验逻辑不适用于多行头模板（如果是多行头的文件，请关闭表头验证）；
67.      *   注意点2：使用当前监听器的导入场景，模型类不允许出现既未忽略、又未使用ExcelProperty注解的字段；
68.      **/
69.     @Override
70.     public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
71.         if (validateSwitch) {
72.             ExcelUtil.validateExcelTemplate(headMap, clazz, fields);
73.         }
74.     }
76.     /*
77.      * 每成功解析一条excel数据行时触发
78.      **/
79.     @Override
80.     public void invoke(T row, AnalysisContext analysisContext) {
81.         data.add(row);
82.         // 判断当前已解析的数据是否达到本批上限，是则执行对应的业务处理
83.         if (data.size() >= BATCH_NUMBER) {
84.             // 更新读取到的总行数、批次数
85.             rows += data.size();
86.             batchNo++;
88.             // 触发业务逻辑处理
89.             this.businessHandler.accept(data);
90.             // 处理完本批数据后，使用新List替换旧List，旧List失去引用后会很快被GC
91.             data = new ArrayList<>(BATCH_NUMBER);
92.         }
93.     }
95.     /*
96.      * 所有数据解析完之后触发
97.      **/
98.     @Override
99.     public void doAfterAllAnalysed(AnalysisContext analysisContext) {
100.         // 因为最后一批可能达不到指定的上限，所以解析完之后要做一次收尾处理
101.         if (!data.isEmpty()) {
102.             this.businessHandler.accept(data);
103.             // 更新读取到的总行数、批次数，以及清理集合辅助GC
104.             rows += data.size();
105.             batchNo++;
106.             data.clear();
107.         }
108.     }
110.     /*
111.      * 关闭excel表头验证
112.      **/
113.     public void offValidate() {
114.         this.validateSwitch = false;
115.     }
117.     public int getRows() {
118.         return rows;
119.     }
121.     public int getBatchNo() {
122.         return batchNo;
123.     }
124. }

复制代码

这个批量处理处罚监听器比上一个通用监听器多了些逻辑

• 起首多了一个批次的概念，当分析的数据量到达给定量级时，就会触发businessHandler业务处理处罚器，而businessHandler则是在监听器初始化时传入的Consumer对象。
  
• 当实行业务逻辑竣过后，会重新new一个新的聚集吸收数据，而旧聚集被断开引用关系后，会在短时间内被GC，这里不利用clear()方法的缘故起因，是由于clear()内部会去断开与每个元素的引用，这种方式会影响整个文件的读取性能。其次，当分析到的行数还未到达给定阈值时，会继续分析背面的数据，直到抵达阈值后重复前面的流程。
  
• 这个分批处理处罚监听器，还重写了doAfterAllAnalysed()方法，这个方法会在整个文件分析完成后触发，内里实现的逻辑是为了做好收尾工作，由于末了一批大概达不到指定的上限，以是分析完之后还要视环境做一次处理处罚。不外这里用了clear()方法，毕竟这是末了一次收尾工作，后续也不必要新聚集来吸收数据了，直接整理现有聚集最符合。
  

该监听器还提供了两个API：

• getRows()：获取当前监听器分析的总行数；
  
• getBatchNo()：获取当前正在处理处罚的批次数（批次号）。
  

这两个方法返回的值，会随着excel文件不绝分析而不绝变革，当文件彻底分析完成后，调用这两个方法可以获取到总批次数以及数据总行数。
关于这个监听器，会在百万级大文件分析的实战中才会用到，先暂时相识即可。
二：封装excel导出工具类

上面listener是导入的时间用到的，那么如果是导出的时间呢？

1. package com.example.bootrocketmq.study.wheel.easyexcel;
3. import com.alibaba.excel.EasyExcelFactory;
4. import com.alibaba.excel.support.ExcelTypeEnum;
5. import com.alibaba.excel.write.builder.ExcelWriterBuilder;
6. import com.alibaba.excel.write.metadata.style.WriteCellStyle;
7. import com.alibaba.excel.write.metadata.style.WriteFont;
8. import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
9. import org.apache.poi.ss.usermodel.HorizontalAlignment;
10. import org.apache.poi.ss.usermodel.IndexedColors;
12. import javax.servlet.http.HttpServletResponse;
13. import java.io.IOException;
14. import java.net.URLEncoder;
15. import java.util.List;
17. /**
18. * @author cui haida
19. * 2024/12/22
20. * 导出工具类
21. */
22. public class ExcelExportUtil {
23.     /*
24.      * 三种excel文件类型分别对应的响应头格式
25.      **/
26.     private static final String XLS_CONTENT_TYPE = "application/vnd.ms-excel";
27.     private static final String XLSX_CONTENT_TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
28.     private static final String CSV_CONTENT_TYPE = "text/csv";
30.     /*
31.      * 导出excel的通用方法
32.      * clazz -> 导出excel所需的数据模型类
33.      * execlData -> 需要导出的数据列表
34.      * filenName -> 当前导出的文件名称（不带文件后缀）
35.      * execlType -> 导出的文件类型(xlsx, xls, csv)
36.      * response -> 网络相应对象
37.      **/
38.     public static void exportExcel(Class<?> clazz, List<?> excelData, String fileName, ExcelTypeEnum excelType, HttpServletResponse response) throws IOException {
39.         HorizontalCellStyleStrategy styleStrategy = setCellStyle();
40.         // 对文件名进行UTF-8编码、拼接文件后缀名
41.         fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20") + excelType.getValue();
42.         switch (excelType) {
43.             case XLS:
44.                 response.setContentType(XLS_CONTENT_TYPE);
45.                 break;
46.             case XLSX:
47.                 response.setContentType(XLSX_CONTENT_TYPE);
48.                 break;
49.             case CSV:
50.                 response.setContentType(CSV_CONTENT_TYPE);
51.                 break;
52.             default:
53.         }
54.         response.setCharacterEncoding("utf-8");
55.         response.setHeader("Content-Disposition", "attachment;filename*=utf-8''" + fileName);
57.         ExcelWriterBuilder writeWork = EasyExcelFactory.write(response.getOutputStream(), clazz);
58.         writeWork.registerWriteHandler(styleStrategy).excelType(excelType).sheet().doWrite(excelData);
59.     }
61.     /*
62.      * 设置单元格风格
63.      **/
64.     public static HorizontalCellStyleStrategy setCellStyle(){
65.         // 设置表头的样式（背景颜色、字体、居中显示）
66.         WriteCellStyle headStyle = new WriteCellStyle();
67.         //设置表头的背景颜色
68.         headStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
69.         WriteFont headFont = new WriteFont();
70.         headFont.setFontHeightInPoints((short)12);
71.         headFont.setBold(true);
72.         headStyle.setWriteFont(headFont);
73.         headStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
75.         // 设置Excel内容策略(水平居中)
76.         WriteCellStyle cellStyle = new WriteCellStyle();
77.         cellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
78.         // 创建并返回样式策略对象
79.         return new HorizontalCellStyleStrategy(headStyle, cellStyle);
80.     }
81. }

复制代码

接着基于response对象，设置了相应的数据范例、编码格式、文件名称等，末了就基于相应对象的输出流写出了天生的excel文件。
固然，通常大文件导出并不会实时返回流给调用方，而是返回OSS地点给前端去下载

1. /**
2. * @author cui haida
3. * 方法编写日期: 2024/12/22
4. * desc ->  上传文件到oss
5. * @param clazz -> 导出excel所需的数据模型类
6. * @param excelData -> 需要导出的数据列表
7. * @param excelType -> excel的类型
8. * @param fileName -> excel文件名称
9. */
10. public static String exportExcelToOSS(Class<?> clazz, List<?> excelData, String fileName, ExcelTypeEnum excelType) throws IOException {
11.     HorizontalCellStyleStrategy styleStrategy = setCellStyle();
12.     fileName = fileName + excelType.getValue();
13.     File excelFile = File.createTempFile(fileName, excelType.getValue());
14.     EasyExcelFactory.write(excelFile, clazz).registerWriteHandler(styleStrategy).sheet().doWrite(excelData);
15.     String url = uploadFileToOss(excelFile);
17.     if (excelFile.exists()) {
18.         boolean flag = excelFile.delete();
19.         log.info("删除临时文件是否成功：{}", flag);
20.     }
21.     return url;
22. }
24. /*
25. * 模拟将上传OSS文件的代码（实际使用请替换为真实上传）
26. * @param file 文件对象
27. **/
28. public static String uploadFileToOss(File file) {
29.     String resultUrl = "";
30.     // 省略上传至OSS的代码……
31.     return resultUrl;
32. }

复制代码

三：项目实践

1： excel导入并举行批量落库

为了更加的贴合业务环境，创建对应的数据库表举行测试

1. CREATE TABLE `panda` (
2.   `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
3.   `name` varchar(80) DEFAULT NULL COMMENT '名称',
4.   `nickname` varchar(80) DEFAULT NULL COMMENT '外号',
5.   `unique_code` varchar(20) DEFAULT NULL COMMENT '唯一编码',
6.   `sex` tinyint(1) DEFAULT '2' COMMENT '性别，0：男，1：女，2：未知',
7.   `height` decimal(6,2) DEFAULT NULL COMMENT '身高',
8.   `birthday` date DEFAULT NULL COMMENT '出生日期',
9.   `pic` varchar(255) DEFAULT NULL COMMENT '头像地址',
10.   `level` varchar(50) DEFAULT '0' COMMENT '等级',
11.   `motto` varchar(255) DEFAULT NULL COMMENT '座右铭',
12.   `address` varchar(255) DEFAULT NULL COMMENT '所在地址',
13.   `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
14.   `is_deleted` tinyint(1) DEFAULT '0' COMMENT '删除标识，0：正常，1：删除',
15.   PRIMARY KEY (`id`) USING BTREE
16. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='熊猫表';

复制代码

这是一张拥有十三个字段的熊猫表，下面初始化一些数据：

1. insert into panda(name, nickname, unique_code, sex, height, birthday, pic, level, motto, address, create_time) values
2. ("竹子", "小竹", "P888888", 0, '179.99', '2017-07-07', NULL, "高级", "十年磨一剑，五年磨半边！", "太阳省银河市地球村888号", now()),
3. ("花花", "阿花", "P666666", 1, '155.00', '2016-06-07', NULL, "顶级", "我爱睡觉爱我！", "太阳省银河市地球村666号", now()),
4. ("甜甜", "肥肥", "P999999", 0, '163.43', '2020-02-02', NULL, "特级", "今天的事能拖就拖，明天的事明天再说！", "太阳省银河市地球村999号", now()),
5. ("子竹", "小子", "P555555", 1, '188.88', '2021-08-08', NULL, "初级", "你小子！", "太阳省银河市地球村555号", now());

复制代码

前面封装了通用监听器，下面就来试试结果，先来写一个最根本的excel导入接口，对应的导入模板如下：

起首来界说一个与excel模板匹配的数据模子类：

1. package org.example.myeasyexcel.model.excel;
3. import com.alibaba.excel.annotation.ExcelProperty;
4. import com.alibaba.excel.annotation.format.DateTimeFormat;
5. import lombok.Data;
7. import java.io.Serializable;
8. import java.math.BigDecimal;
9. import java.util.Date;
11. /**
12. * @author cui haida
13. * 2024/12/23
14. * 熊猫导出类
15. */
16. @Data
17. public class PandaReadModel implements Serializable {
18.     private static final long serialVersionUID = 1L;
20.     @ExcelProperty("名称")
21.     private String name;
23.     @ExcelProperty("外号")
24.     private String nickname;
26.     @ExcelProperty("唯一编码")
27.     private String uniqueCode;
29.     @ExcelProperty("性别")
30.     private String sex;
32.     @ExcelProperty("身高")
33.     private BigDecimal height;
35.     @ExcelProperty("出生日期")
36.     @DateTimeFormat("yyyy-MM-dd")
37.     private Date birthday;
39.     @ExcelProperty("等级")
40.     private String level;
42.     @ExcelProperty("座右铭")
43.     private String motto;
45.     @ExcelProperty("所在地址")
46.     private String address;
47. }

复制代码

这是一个尺度的Java类，没有任何特别的地方，下面再来编写service层的实现逻辑：

1. @Override
2. @Transactional(rollbackFor = Exception.class)
3. public void importExcelV1(MultipartFile file) {
4.     // 创建通用监听器来解析excel文件
5.     CommonListener<PandaReadModel> listener = new CommonListener<>(PandaReadModel.class);
6.     try {
7.         // 从excel模板中读取数据
8.         EasyExcelFactory.read(file.getInputStream(), PandaReadModel.class, listener).sheet().doRead();
9.     } catch (IOException e) {
10.         log.error("导入熊猫数据出错：{}: {}", e, e.getMessage());
11.         throw new BusinessException(ResponseCode.ANALYSIS_EXCEL_ERROR, "网络繁忙，请稍后重试！");
12.     }
14.     // 对读取到的数据进行批量保存
15.     List<PandaReadModel> excelData = listener.getData();
16.     if (excelData.isEmpty()) {
17.         throw new BusinessException(ResponseCode.ANALYSIS_EXCEL_ERROR, "请检查您上传的excel文件是否为空！");
18.     }
19.     batchSaveExcelData(excelData);
20. }
22. @Override
23. @Transactional(rollbackFor = Exception.class)
24. public void batchSaveExcelData(List<PandaReadModel> excelData) {
25.     List<Panda> pandas = excelData.stream().map(model -> {
26.         Panda panda = new Panda();
27.         BeanUtils.copyProperties(model, panda, "sex");
28.         panda.setSex(Sex.codeOfValue(model.getSex()));
29.         panda.setCreateTime(new Date());
30.         return panda;
31.     }).collect(Collectors.toList());
32.     saveBatch(pandas);
33. }

复制代码

接着界说controller层

1. @RestController
2. @RequestMapping("/panda")
3. public class PandaController {
4.   @Resource
5.   private PandaService pandaService;
7.   @PostMapping("/import/v1")
8.   public ServerResponse<Void> importExcelV1(MultipartFile file) {
9.     if (null == file) {
10.       throw new BusinessException(ResponseCode.FILE_IS_NULL);
11.     }
12.     pandaService.importExcelV1(file);
13.     return ServerResponse.success();
14.   }
15. }

复制代码

调用外部接口就可以发现已经乐成的读取了数据并

2：根据检索条件导出excel

导出也非常的容易，先界说导出模子类：

1. package org.example.myeasyexcel.model.vo;
3. import com.alibaba.excel.annotation.ExcelIgnore;
4. import com.alibaba.excel.annotation.ExcelProperty;
5. import com.alibaba.excel.annotation.format.DateTimeFormat;
6. import com.alibaba.excel.annotation.write.style.ColumnWidth;
7. import lombok.Data;
8. import org.example.myeasyexcel.converter.SexConverter;
10. import java.io.Serializable;
11. import java.math.BigDecimal;
12. import java.util.Date;
14. /**
15. * @author cui haida
16. * 2024/12/22
17. */
18. @Data
19. @ColumnWidth(10)
20. public class PandaExportVO implements Serializable {
21.     private static final long serialVersionUID = 1L;
23.     @ExcelIgnore
24.     private Long id;
26.     @ExcelProperty(value = "熊猫昵称", index = 0)
27.     private String nickname;
29.     @ExcelProperty(value = "性别", index = 1, converter = SexConverter.class)
30.     private Integer sex;
32.     @ExcelProperty(value = "唯一编码", index = 2)
33.     private String uniqueCode;
35.     @ExcelProperty(value = "身高", index = 3)
36.     private BigDecimal height;
38.     @ExcelProperty(value = "出生日期", index = 4)
39.     @DateTimeFormat("yyyy-MM-dd")
40.     @ColumnWidth(15)
41.     private Date birthday;
43.     @ExcelProperty(value = "创建时间", index = 5)
44.     @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
45.     @ColumnWidth(20)
46.     private Date createTime;
47. }

复制代码

固然，如果导入、导出的字段类似，你可以利用同一个类，不外个人发起是最好分开
下面再来界说一个查询条件参数类：

1. package org.example.myeasyexcel.model.dto;
3. import lombok.Data;
5. import java.io.Serializable;
7. /**
8. * @author cui haida
9. * 2024/12/23
10. */
11. @Data
12. public class PandaQueryDTO implements Serializable {
13.     private static final long serialVersionUID = 1L;
15.     /*
16.      * 搜索关键字（兼容名称、外号、编码三个条件）
17.      */
18.     private String keyword;
20.     /*
21.      * 生日开始时间（yyyy-MM-dd格式）
22.      */
23.     private String startTime;
25.     /*
26.      * 生日结束时间（yyyy-MM-dd格式）
27.      */
28.     private String endTime;
29. }

复制代码

这个DTO类中，统共有三个字段，支持名称、外号、编码的暗昧搜刮，以及基于生日按范围查询熊猫数据，接着来看service层：

1. /**
2. * 根据条件导出熊猫信息到Excel
3. *
4. * @param queryDTO 包含查询条件的PandaQueryDTO对象，用于筛选要导出的熊猫信息
5. * @param response HttpServletResponse对象，用于将Excel文件作为HTTP响应返回
6. *
7. * 本方法首先根据查询条件从数据库中筛选出相应的熊猫信息，然后利用ExcelUtil工具类
8. * 将这些信息导出为Excel文件，并通过HTTP响应返回给客户端如果导出过程中发生IO异常，
9. * 则记录错误日志并抛出业务异常，提示导出失败
10. */
11. @Override
12. public void exportExcelByCondition(PandaQueryDTO queryDTO, HttpServletResponse response) {
13.     // 通过查询条件选择出对应的熊猫
14.     List<PandaExportVO> pandas = baseMapper.selectPandas(queryDTO);
15.     // 生成唯一文件名，防止重复
16.     String fileName = "熊猫基本信息集合-" + System.currentTimeMillis();
17.     try {
18.         // 通过工具方法进行导入
19.         ExcelUtil.exportExcel(PandaExportVO.class, pandas, fileName, ExcelTypeEnum.XLSX, response);
20.     } catch (IOException e) {
21.         // 记录异常日志
22.         log.error("熊猫数据导出失败，{}：{}", e, e.getMessage());
23.         // 抛出业务异常，提示用户导出失败
24.         throw new BusinessException("熊猫基本信息导出失败，请稍后再试！");
25.     }
26. }

复制代码

对应的工具方法在上面已经分析，这里在形貌一遍

1. package org.example.myeasyexcel.util;
3. import com.alibaba.excel.EasyExcelFactory;
4. import com.alibaba.excel.ExcelWriter;
5. import com.alibaba.excel.annotation.ExcelIgnore;
6. import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
7. import com.alibaba.excel.annotation.ExcelProperty;
8. import com.alibaba.excel.exception.ExcelAnalysisException;
9. import com.alibaba.excel.support.ExcelTypeEnum;
10. import com.alibaba.excel.write.builder.ExcelWriterBuilder;
11. import com.alibaba.excel.write.metadata.style.WriteCellStyle;
12. import com.alibaba.excel.write.metadata.style.WriteFont;
13. import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
14. import lombok.extern.slf4j.Slf4j;
15. import org.apache.poi.ss.usermodel.HorizontalAlignment;
16. import org.apache.poi.ss.usermodel.IndexedColors;
17. import org.example.myeasyexcel.enums.ExcelTemplate;
19. import javax.servlet.http.HttpServletResponse;
20. import java.io.File;
21. import java.io.IOException;
22. import java.io.UnsupportedEncodingException;
23. import java.lang.reflect.Field;
24. import java.net.URLEncoder;
25. import java.util.*;
27. /**
28. * @author cui haida
29. * 2024/12/23
30. */
31. @Slf4j
32. public class ExcelUtil {
33.     /*
34.      * 三种excel文件类型分别对应的响应头格式
35.      * */
36.     private static final String XLS_CONTENT_TYPE = "application/vnd.ms-excel";
37.     private static final String XLSX_CONTENT_TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
38.     private static final String CSV_CONTENT_TYPE = "text/csv";
40.     /*
41.      * 导出excel的通用方法
42.      *   clazz：传入excel导出的VO类
43.      *   data：传入需要导出的数据列表
44.      *   fileName：当前导出的excel文件名称（不带文件后缀）
45.      *   excelType：导出的文件类型
46.      *   response：网络响应对象
47.      * */
48.     public static void exportExcel(Class<?> clazz, List<?> excelData, String fileName, ExcelTypeEnum excelType, HttpServletResponse response) throws IOException {
49.         HorizontalCellStyleStrategy styleStrategy = setCellStyle();
50.         setResponse(fileName, excelType, response);
51.         ExcelWriterBuilder writeWork = EasyExcelFactory.write(response.getOutputStream(), clazz);
52.         writeWork.registerWriteHandler(styleStrategy).excelType(excelType).sheet().doWrite(excelData);
53.     }
55.     /*
56.      * 初始化模板填充导出场景的写对象
57.      * */
58.     public static ExcelWriter initExportFillWriter(String fileName, ExcelTypeEnum excelType, ExcelTemplate template, HttpServletResponse response) throws IOException {
59.         setResponse(fileName, excelType, response);
60.         return EasyExcelFactory.write(response.getOutputStream())
61.                 .excelType(excelType)
62.                 .withTemplate(template.getPath()).build();
63.     }
65.     /*
66.      * 设置通用的响应头信息
67.      * */
68.     public static void setResponse(String fileName, ExcelTypeEnum excelType, HttpServletResponse response) {
69.         // 对文件名进行UTF-8编码、拼接文件后缀名
70.         try {
71.             fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20") + excelType.getValue();
72.         } catch (UnsupportedEncodingException e) {
73.             throw new RuntimeException(e);
74.         }
75.         switch (excelType) {
76.             case XLS:
77.                 response.setContentType(XLS_CONTENT_TYPE);
78.                 break;
79.             case XLSX:
80.                 response.setContentType(XLSX_CONTENT_TYPE);
81.                 break;
82.             case CSV:
83.                 response.setContentType(CSV_CONTENT_TYPE);
84.                 break;
85.         }
86.         response.setCharacterEncoding("utf-8");
87.         response.setHeader("Content-Disposition", "attachment;filename*=utf-8''" + fileName);
88.     }
90.     /*
91.      * 设置单元格风格
92.      * */
93.     public static HorizontalCellStyleStrategy setCellStyle(){
94.         // 设置表头的样式（背景颜色、字体、居中显示）
95.         WriteCellStyle headStyle = new WriteCellStyle();
96.         //设置表头的背景颜色
97.         headStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
98.         WriteFont headFont = new WriteFont();
99.         headFont.setFontHeightInPoints((short)12);
100.         headFont.setBold(true);
101.         headStyle.setWriteFont(headFont);
102.         headStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
104.         // 设置Excel内容策略(水平居中)
105.         WriteCellStyle cellStyle = new WriteCellStyle();
106.         cellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
107.         return new HorizontalCellStyleStrategy(headStyle, cellStyle);
108.     }
110.     /*
111.      * 上传错误的Excel文件到OSS
112.      * */
113.     public static String exportExcelToOSS(Class<?> clazz, List<?> excelData, String fileName, ExcelTypeEnum excelType) throws IOException {
114.         HorizontalCellStyleStrategy styleStrategy = ExcelUtil.setCellStyle();
115.         fileName = fileName + excelType.getValue();
116.         File excelFile = File.createTempFile(fileName, excelType.getValue());
117.         EasyExcelFactory.write(excelFile, clazz).registerWriteHandler(styleStrategy).sheet().doWrite(excelData);
118.         String url = uploadFileToOss(excelFile);
120.         if (excelFile.exists()) {
121.             boolean flag = excelFile.delete();
122.             log.info("删除临时文件是否成功：{}", flag);
123.         }
124.         return url;
125.     }
127.     /*
128.      * 模拟将上传OSS文件的代码（实际使用请替换为真实上传）
129.      * */
130.     public static String uploadFileToOss(File file) {
131.         String resultUrl = "https://juejin.cn/user/862486453028888/posts";
132.         // 省略上传至OSS的代码……
133.         return resultUrl;
134.     }
136.     /*
137.      * 校验excel文件的表头，与数据模型类的映射关系是否匹配
138.      * */
139.     public static void validateExcelTemplate(Map<Integer, String> headMap, Class<?> clazz, Field[] fields) {
140.         Collection<String> headNames = headMap.values();
142.         // 类上是否存在忽略excel字段的注解
143.         boolean classIgnore = clazz.isAnnotationPresent(ExcelIgnoreUnannotated.class);
144.         int count = 0;
145.         // 如果EasyExcel框架版本较低，请把这行代码放开，并将有效的字段列表返回，用于空值校验
146.         // List<Field> validFields = new ArrayList<>(fields.length);
147.         for (Field field : fields) {
148.             // 忽略序列化ID字段
149.             if ("serialVersionUID".equals(field.getName())) {
150.                 continue;
151.             }
152.             // 如果字段上存在忽略注解，则跳过当前字段
153.             if (field.isAnnotationPresent(ExcelIgnore.class)) {
154.                 continue;
155.             }
157.             ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
158.             if (null == excelProperty) {
159.                 // 如果类上也存在忽略注解，则跳过所有未使用ExcelProperty注解的字段
160.                 if (classIgnore) {
161.                     continue;
162.                 }
163.                 // 如果检测到既未忽略、又未映射excel列的字段，则抛出异常提示模板不正确
164.                 throw new ExcelAnalysisException("请检查导入的excel文件是否按模板填写!");
165.             }
167.             // 校验数据模型类上绑定的名称，是否与excel列名匹配
168.             String[] value = excelProperty.value();
169.             String name = value[0];
170.             if (name != null && 0 != name.length() && !headNames.contains(name)) {
171.                 throw new ExcelAnalysisException("请检查导入的excel文件是否按模板填写!");
172.             }
173.             // 更新有效字段的数量
174.             count++;
175.             // validFields.add(field);
176.         }
177.         // 最后校验数据模型类的有效字段数量，与读到的excel列数量是否匹配
178.         if (headMap.size() != count) {
179.             throw new ExcelAnalysisException("请检查导入的excel文件是否按模板填写!");
180.         }
181.     }
183.     /**
184.      * 判断整行单元格数据是否均为空（依赖于validateExcelTemplate()方法返回的有效字段列表）
185.      * 说明：该方法适用于低版本的EasyExcel读取数据时校验，因为低版本的不会自动忽略空行
186.      */
187.     public static <T> boolean rowIsNull(T data, List<Field> validFields) {
188.         if (data instanceof String) {
189.             return "".equals(data);
190.         }
191.         try {
192.             List<Boolean> fieldNulls = new ArrayList<>(validFields.size());
193.             for (Field field : validFields) {
194.                 field.setAccessible(true);
195.                 Object value = field.get(data);
196.                 if (Objects.isNull(value)) {
197.                     fieldNulls.add(Boolean.TRUE);
198.                 } else {
199.                     fieldNulls.add(Boolean.FALSE);
200.                 }
201.             }
202.             return fieldNulls.stream().allMatch(Boolean.TRUE::equals);
203.         } catch (Exception e) {
204.             log.error("读取数据行[{}]解析失败: {}", data, e.getMessage());
205.         }
206.         return true;
207.     }
208. }

复制代码

末了编写controller层，就是简朴的对service层举行调用

1. @PostMapping("/export/v1")
2. public void exportExcelV1(@RequestBody PandaQueryDTO queryDTO, HttpServletResponse response) {
3.     pandaService.exportExcelByCondition(queryDTO, response);
4. }

复制代码

3：校验写入的excel的数据

在有些场景中，我们必要对导入的excel数据举行校验洗濯后才气利用
如果上传的excel文件存在校验堕落的信息，则需像上图一样指明错误缘故起因，末了回传给调用方下载检察，这该怎么实现呢？如下：

1. @Override
2. @Transactional(rollbackFor = Exception.class)
3. public BaseImportExcelVO<String> importExcelV2(MultipartFile file) {
4.     CommonListener<PandaReadModel> listener = new CommonListener<>(PandaReadModel.class);
5.     try {
6.         EasyExcelFactory.read(file.getInputStream(), PandaReadModel.class, listener).sheet().doRead();
7.     } catch (IOException e) {
8.         log.error("导入熊猫数据出错：{}: {}", e, e.getMessage());
9.         throw new BusinessException(ResponseCode.ANALYSIS_EXCEL_ERROR, "网络繁忙，请稍后重试！");
10.     }
12.     List<PandaReadModel> excelData = listener.getData();
13.     if (excelData.isEmpty()) {
14.         throw new BusinessException(ResponseCode.ANALYSIS_EXCEL_ERROR, "请检查您上传的excel文件是否为空！");
15.     }
17.     // 校验excel数据，如果校验未通过直接阻断执行，将错误信息返回给调用方
18.     BaseImportExcelVO<String> result = validateExcelData(excelData);
19.     if (result.getErrorFlag()) {
20.         return result;
21.     }
23.     // 对校验通过的数据进行批量落库
24.     batchSaveExcelData(excelData);
25.     return result;
26. }
28. /*
29. * 校验导入的excel数据
30. * */
31. private BaseImportExcelVO<String> validateExcelData(List<PandaReadModel> excelData) {
32.     BaseImportExcelVO<String> result = new BaseImportExcelVO<>();
33.     boolean errorFlag = false;
34.     List<PandaReadErrorModel> validatePandas = new ArrayList<>();
35.     String birthdayErrorMsg = "生日不能为空;", uniCodeErrorMsg = "唯一编码重复;";
37.     // 根据唯一编码查询库内的熊猫数
38.     List<String> uniCodes = excelData.stream().map(PandaReadModel::getUniqueCode).collect(Collectors.toList());
39.     List<CountVO<String, Integer>> counts = baseMapper.selectCountByUniCodes(uniCodes);
40.     Map<String, Integer> countMap = counts.stream().collect(Collectors.toMap(CountVO::getKey, CountVO::getValue));
42.     // 循环对excel所有数据行进行校验
43.     for (PandaReadModel excelRow : excelData) {
44.         String errorMsg = "";
45.         PandaReadErrorModel errorModel = new PandaReadErrorModel();
46.         BeanUtils.copyProperties(excelRow, errorModel);
47.         // 如果库里对应的唯一编码能查到熊猫，说明UniqueCode重复
48.         if (countMap.containsKey(excelRow.getUniqueCode())) {
49.             errorMsg += uniCodeErrorMsg;
50.             errorFlag = true;
51.         }
52.         // 如果导入的生日字段为空，说明对应的excel行没填写出生日期
53.         if (null == excelRow.getBirthday()) {
54.             errorMsg += birthdayErrorMsg;
55.             errorFlag = true;
56.         }
57.         errorModel.setErrorMsg(errorMsg);
58.         validatePandas.add(errorModel);
59.     }
61.     // 如果存在校验未通过的记录，则导出校验出错的数据为excel文件
62.     if (errorFlag) {
63.         String url, fileName = "熊猫信息导入-校验出错文件-" + System.currentTimeMillis();
64.         try {
65.             url = ExcelUtil.exportExcelToOSS(PandaReadErrorModel.class, validatePandas, fileName, ExcelTypeEnum.XLSX);
66. //                ExcelUtil.exportExcel(PandaReadErrorModel.class, validatePandas, fileName, ExcelTypeEnum.XLSX, response);
67.         } catch (IOException e) {
68.             log.error("生成熊猫导入校验出错文件失败：{}: {}", e, e.getMessage());
69.             throw new BusinessException(ResponseCode.ANALYSIS_EXCEL_ERROR, "网络繁忙，请稍后重试！");
70.         }
71.         result.setResult(url);
72.         result.setCheckMsg("文件校验未通过！");
73.     }
74.     result.setErrorFlag(errorFlag);
75.     return result;
76. }

复制代码

4：部分字段差别的导出场景

来看这个场景，正如上图所示，现在有两个导出需求，可仅仅只有第一列字段差别罢了，这时我们必要界说两个接口吗？答案是不必要，面临这种大多数字段类似、部分字段差别的场景，可以基于多态的特性来实现，起首界说一个父类来声明公共字段：

1. @Data
2. public class PandaStatisticsExportVO implements Serializable {
3.     private static final long serialVersionUID = 1L;
5.     @ExcelProperty(index = 1, value = "熊数")
6.     private Integer counting;
8.     @ExcelProperty(index = 2, value = "身高>=170cm熊数")
9.     private Integer heightGte170cm;
10. }

复制代码

接着分别为两个差别的场景界说子类，即：

1. /**
2. * 熊猫统计导出VO类（根据年龄分组）
3. */
4. @Data
5. @EqualsAndHashCode(callSuper = true)
6. public class SexStatisticsExportVO extends PandaStatisticsExportVO implements Serializable {
7.     private static final long serialVersionUID = 1L;
9.     @ExcelProperty(index = 0, value = "性别分组", converter = SexConverter.class)
10.     private Integer sex;
11. }
13. /**
14. * 熊猫统计导出VO类（根据等级分组）
15. */
16. @Data
17. @EqualsAndHashCode(callSuper = true)
18. public class LevelStatisticsExportVO extends PandaStatisticsExportVO implements Serializable {
19.   private static final long serialVersionUID = 1L;
21.   @ExcelProperty(index = 0, value = "等级分组")
22.   private String level;
23. }

复制代码

这是面向对象语言的尺度写法，值得一提的是：这里公道的利用了ExcelProperty注解的index属性，由于两个表格中，存在差别的字段就是第一列，以是两个子类的差别字段的列索引写成0即可。
下面来看详细的service实现：

1. @Override
2. public void exportStatisticsData(PandaStatisticsDTO statisticsDTO, HttpServletResponse response) {
3.     List<PandaStatisticsBO> pandaStatisticsBOs = baseMapper.selectPandaStatistics(statisticsDTO);
5.     List<PandaStatisticsExportVO> exportData;
6.     Class<?> clazz;
7.     // 如果是按性别分组统计，则使用性别的数据模型类
8.     if (0 == statisticsDTO.getStatisticsType()) {
9.         clazz = SexStatisticsExportVO.class;
10.         exportData = pandaStatisticsBOs.stream().map(bo -> {
11.             SexStatisticsExportVO sexVO = new SexStatisticsExportVO();
12.             BeanUtils.copyProperties(bo, sexVO);
13.             return sexVO;
14.         }).collect(Collectors.toList());
15.     }
16.     // 如果是按等级分组统计，则使用等级的数据模型类
17.     else if (1 == statisticsDTO.getStatisticsType()) {
18.         clazz = LevelStatisticsExportVO.class;
19.         exportData = pandaStatisticsBOs.stream().map(bo -> {
20.             LevelStatisticsExportVO levelVO = new LevelStatisticsExportVO();
21.             BeanUtils.copyProperties(bo, levelVO);
22.             return levelVO;
23.         }).collect(Collectors.toList());
24.     } else {
25.         throw new BusinessException("暂不支持这种统计方式哦~");
26.     }
28.     // 导出对应的excel数据
29.     String fileName = "熊猫统计数据-" + System.currentTimeMillis();
30.     try {
31.         ExcelUtil.exportExcel(clazz, exportData, fileName, ExcelTypeEnum.XLSX, response);
32.     } catch (IOException e) {
33.         log.error("熊猫统计数据导出失败，{}：{}", e, e.getMessage());
34.         throw new BusinessException("统计数据导出失败，请稍后再试！");
35.     }
36. }

复制代码

代码仍旧非常简朴，起首会根据参数里传入的导出范例，来选择要利用的数据模子类：

• 0：代表按性别统计数据，利用SexStatisticsExportVO模子类；
  
• 1：代表按品级统计数据，利用LevelStatisticsExportVO模子类。
  

5：多行头的excel导出

有些业务场景下，我们必要导出像上图如许的归并表头文件，即一个大列下面有多个子列，整个文件由多个大列+N多个子列构成，这种需求该怎么导出呢？
实在非常简朴，只靠ExcelProperty注解就能实现，如下：

1. @Data
2. public class MultiLineHeadExportVO implements Serializable {
3.     private static final long serialVersionUID = 1L;
5.     @ExcelProperty(value = {"基本信息", "昵称"})
6.     private String nickname;
8.     @ExcelProperty(value = {"基本信息", "编码"})
9.     private String uniqueCode;
11.     @ExcelProperty(value = {"基本信息","性别"}, converter = SexConverter.class)
12.     private Integer sex;
14.     @ExcelProperty(value = {"基本信息","身高"})
15.     private BigDecimal height;
17.     @ExcelProperty(value = {"基本信息","出生日期"})
18.     @DateTimeFormat("yyyy-MM-dd")
19.     @ColumnWidth(15)
20.     private Date birthday;
22.     @ExcelProperty(value = {"其他信息","等级"})
23.     private String level;
25.     @ExcelProperty(value = {"其他信息","座右铭"})
26.     @ColumnWidth(30)
27.     private String motto;
29.     @ExcelProperty(value = {"其他信息", "创建时间"})
30.     @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
31.     @ColumnWidth(20)
32.     private Date createTime;
33. }

复制代码

正如代码所示，如果你想要将某几列归并成一个大列，只需在注解的value属性中，以数组情势通报类似的大列名即可。
而EasyExcel发现列名是数组情势时，就会实行主动归并类似列表的字段，而导出代码无需做任何改造：

1. @Override
2. public void exportMultiLineHeadExcel(HttpServletResponse response) {
3.     List<MultiLineHeadExportVO> pandas = baseMapper.selectAllPandas();
4.     String fileName = "多行表头熊猫数据-" + System.currentTimeMillis();
5.     try {
6.         ExcelUtil.exportExcel(MultiLineHeadExportVO.class, pandas, fileName, ExcelTypeEnum.XLSX, response);
7.     } catch (IOException e) {
8.         log.error("多行表头熊猫数据，{}：{}", e, e.getMessage());
9.         throw new BusinessException("数据导出失败，请稍后再试！");
10.     }
11. }

复制代码