Alibaba EasyExcel 导入导出全家桶

一、阿里巴巴EasyExcel的上风

        首先说下EasyExcel相对 Apache poi的上风：
        EasyExcel也是阿里研发在poi基础上做了封装，改进产物。它替开发者做了注解列表分析，表格添补等一系列代码编写工作，并将此抽象成通用和可扩展的框架。相对poi，在数据量比较大的时候，它有着更精良的性能表现。导出的时候，easyexcel使用优化的反射技术，制止poi频仍的去创建cell和row对象；导入的时候，它的分析器AnalysisEventListener，可设置批量阈值 BATCH_COUNT，达到阈值就往数据库插入数据，然后清空分析器内部缓存，相同的表格，easyexcel导入所占用的内存要比poi节流90%，制止了大数据量导入的时候，造成的内存占用井喷（这使得stop the world的时间可能会被集中，而体系可能会出现短暂的停摆。），而GC不能均衡变动垃圾回收。同时也制止堆积数据后，sql的巨量数据的批量插入，导致超出mybatis批量插入语句能蒙受的最大长度限制。

二、EasyExcel核心util类

1. @Slf4j
2. public class EasyExcels {
4.     public static final String EXT_NAME_XLSX = "xlsx";
5.     public static final String EXT_NAME_XLS = "xls";
8.     /**
9.      *
10.      * @param response
11.      * @param data
12.      * @param filename
13.      * @param sheetName
14.      * @param selectMap  自定义下拉列，但是既然数据都导出了，下拉用处何在？这个需求比较少
15.      * @param <T>
16.      * @throws IOException
17.      */
18.     public static <T> void write(HttpServletResponse response, List<T> data, String filename, String sheetName,
19.                                  List<KeyValue<ExcelColumn, List<String>>> selectMap) throws IOException {
20.         setResponse(response, filename);
21.         if (StringUtils.isBlank(sheetName)) {
22.             sheetName = filename;
23.         }
24.         // 输出 Excel
25.         try {
26.             EasyExcel.write(response.getOutputStream(), data != null && !data.isEmpty() ? data.get(0).getClass() : null)
27.                     .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // 基于 column 长度，自动适配。最大 255 宽度
28.                     .registerWriteHandler(new CustomCellWriteWeightConfig()) // Excel 列宽自适应
29.                     .registerWriteHandler(EasyExcelStyle.horizontalCellStyleStrategy) //内容样式
30.                     .registerWriteHandler(new SelectWriteHandler(selectMap)) // 基于固定 sheet 实现下拉框
31.                     .sheet(sheetName).doWrite(data);
32.         } catch (Exception e) {
33.             e.printStackTrace();
34.         } finally {
35.             response.getOutputStream().close();
36.         }
37.     }
39.     // 简单导入读取，不做解析，不做校验
40.     public static <T> List<T> read(MultipartFile file, Class<T> head) throws IOException {
41.         return EasyExcel.read(file.getInputStream(), head, null)
42.                 .autoCloseStream(false)  // 不要自动关闭，交给 Servlet 自己处理
43.                 .doReadAllSync();
44.     }
46.     // 需要配合监听器解析数据
47.     public static <T> void read(MultipartFile file, Class<T> head, ReadListener<T> listener) throws IOException {
48.         EasyExcel.read(file.getInputStream(), head, listener)
49.                 .sheet()
50.                 .doRead();
52.     }
54.     // 不带下拉列的导出，用的比较多
55.     public static <T> void export(HttpServletResponse response, List<T> data, String filename, String sheetName) throws IOException {
56.         setResponse(response, filename);
57.         if (StringUtils.isBlank(sheetName)) {
58.             sheetName = filename;
59.         }
60.         EasyExcel.write(response.getOutputStream(), data != null && !data.isEmpty() ? data.get(0).getClass() : null)
61.                 .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
62.                 .registerWriteHandler(new CustomCellWriteWeightConfig())
63.                 .registerWriteHandler(EasyExcelStyle.horizontalCellStyleStrategy)
64.                 .sheet(sheetName).doWrite(data);
65.     }
68.    // 用于合并单元格列的导出
69.     public static <T> void export(HttpServletResponse response, List<T> data, String filename, String sheetName, RowWriteHandler handler) throws IOException {
70.         setResponse(response, filename);
71.         EasyExcel.write(response.getOutputStream(), data != null && !data.isEmpty() ? data.get(0).getClass() : null)
72.                 .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
73.                 .registerWriteHandler(new CustomCellWriteWeightConfig())
74.                 .registerWriteHandler(EasyExcelStyle.horizontalCellStyleStrategy)
75.                 .registerWriteHandler(handler)
76.                 .sheet(sheetName).doWrite(data);
77.     }
80.    // 用于导出表头模板，填充导入数据用的excel模板，因为是模板，所以肯定会有下拉列的需求
81.     public static <T> void export(HttpServletResponse response, Class<T> clazz, String filename) throws IOException {
82.         setResponse(response, filename);
83.         Map<Integer, ExcelSelectedResolve> selectedMap = resolveSelectedAnnotation(clazz);
84.         EasyExcel.write(response.getOutputStream(), clazz)
85.                 .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
86. //                .registerWriteHandler(new CustomCellWriteHeightConfig())
87.                 .registerWriteHandler(new CustomCellWriteWeightConfig())
88.                 .registerWriteHandler(EasyExcelStyle.horizontalCellStyleStrategy)
89.                 .registerWriteHandler(new SelectSheetWriteHandler(selectedMap))
90.                 .sheet(filename).doWrite(Collections.emptyList());
91.     }
93.     /**
94.      * 解析表头类中的下拉注解
95.      * @param head 表头类
96.      * @param <T> 泛型
97.      * @return Map<下拉框列索引, 下拉框内容> map
98.      */
99.     private static <T> Map<Integer, ExcelSelectedResolve> resolveSelectedAnnotation(Class<T> head) {
100.         Map<Integer, ExcelSelectedResolve> selectedMap = new HashMap<>();
102.         // getDeclaredFields(): 返回全部声明的属性；getFields(): 返回public类型的属性
103.         Field[] fields = head.getDeclaredFields();
104.         for (int i = 0; i < fields.length; i++){
105.             Field field = fields[i];
106.             // 解析注解信息
107.             ExcelSelected selected = field.getAnnotation(ExcelSelected.class);
108.             ExcelProperty property = field.getAnnotation(ExcelProperty.class);
109.             if (selected != null) {
110.                 ExcelSelectedResolve excelSelectedResolve = new ExcelSelectedResolve();
111.                 String[] source = excelSelectedResolve.resolveSelectedSource(selected);
112.                 if (source != null && source.length > 0){
113.                     excelSelectedResolve.setSource(source);
114.                     excelSelectedResolve.setFirstRow(selected.firstRow());
115.                     excelSelectedResolve.setLastRow(selected.lastRow());
116.                     if (property != null && property.index() >= 0){
117.                         selectedMap.put(property.index(), excelSelectedResolve);
118.                     } else {
119.                         selectedMap.put(i, excelSelectedResolve);
120.                     }
121.                 }
122.             }
123.         }
125.         return selectedMap;
126.     }
128.     public static void setResponse(HttpServletResponse response, String filename) throws IOException {
129.         setResponse(response, filename, EXT_NAME_XLSX);
130.     }
132.     public static void setResponse(HttpServletResponse response, String filename, String extName) throws IOException {
133.         String exportFilename = URLEncoder.encode(filename, StandardCharsets.UTF_8.name());
134.         response.setContentType("application/vnd.ms-excel");
135.         response.setCharacterEncoding("utf-8");
136.         response.setHeader("Access-Control-Expose-Headers", "token,Content-Type,Content-disposition");
137.         response.setHeader("Access-Control-Allow-Headers", "Origin, X-Requested-With, Content-Type, Accept, Connection, User-Agent, Cookie, token,Content-Type,Content-disposition");
138.         response.setHeader("Content-disposition", exportFilename + "." + extName);
139.     }
141. }

复制代码

三、导入分析监听器

那要使用easyexcel，首先要解决分析器抽象类的实现：
        当时第一次使用easyexcel的时候，对这个工具框架不熟悉，项目时间被催的紧，没时间去做设计，当时修改每个类的字段注解index属性，每个字段单独写校验语句，简直苦不堪言。我只想说，磨刀不误砍柴工，不注重设计的公司，只会被延误更多的时间。

1. /**
2. * @Title: ExcelImportReadListener
3. * @Description: 其他人如果觉得invoke()方法不满足其需求，可以自己实现一下
4. * @Author: wenrong
5. * @Date: 2024/4/25 17:08
6. * @Version:1.0
7. */
8. @Data
9. public abstract class ExcelImportReadListener<T extends ValidateBaseBo> extends AnalysisEventListener<T> {
11.     private static final Logger log = LoggerFactory.getLogger("excelReadListener");
13.     public static int BATCH_COUNT = 1000;
15.     private AtomicLong successNum = new AtomicLong();
17.     private final Class<T> clazz;
19.     private Validator<T> validator;
21.     private List<T> successData = new ArrayList<>();
23.     private List<T> failureData = new ArrayList<>();
26.     public ExcelImportReadListener(Class<T> clazz) {
27.         this.clazz = clazz;
28.     }
30.     @Override
31.     public void invoke(T data, AnalysisContext context) {
32.         log.info("解析到一条数据:{}", JSONObject.toJSON(data));
33.         StringBuilder errMsg = new StringBuilder();
34.         try {
35.             //根据excel数据实体中的javax.validation + 正则表达式来校验excel数据
36.             errMsg.append(EasyExcelValidateHandler.validateEntity(data));
37.             // 额外自定义校验，以及设置数据属性的逻辑
38.             if (validator != null) {
39.                 errMsg.append(validator.validate(data));
40.             }
41.         } catch (NoSuchFieldException e) {
42.             log.error(e.getMessage());
43.         }
44.         if (StringUtils.isNotEmpty(errMsg.toString())) {
45.             data.setErrMsg(errMsg.toString());
46.             failureData.add(data);
47.         } else {
48.             successData.add(data);
49.             successNum.incrementAndGet();
50.         }
52.         if (BATCH_COUNT != 0 && successData.size() >= BATCH_COUNT) {
53.             try {
54.                 saveData();
55.             } catch (Exception e) {
56.                 log.error(e.getMessage(), e);
57.             }
58.             successData.clear();
59.         }
61.     }
63.     @Override
64.     public final void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
65.         // 验证表头
66.         if (headMap.isEmpty()) {
67.             throw new ServiceException("无表头");
68.         }
69.         if (clazz != null) {
70.             try {
71.                 Map<Integer, String> indexNameMap = getIndexNameMap(clazz);
72.                 for (Integer index : indexNameMap.keySet()) {
73.                     log.info("表头字段：{}", headMap.get(index));
74.                     if (StringUtils.isEmpty(headMap.get(index))) {
75.                         throw new ServiceException("未设置index");
76.                     }
77.                     // 对比excel表头和解析数据的java实体类的，看是否匹配
78.                     if (!headMap.get(index).equals(indexNameMap.get(index))) {
79.                         throw new ServiceException("导入模板错误");
80.                     }
81.                 }
82.             } catch (NoSuchFieldException e) {
83.                 log.error(e.getMessage(), e);
84.             }
85.         }
87.     }
89.     @Override
90.     public final void doAfterAllAnalysed(AnalysisContext context) {
91.         log.info("所有数据解析完成！共校验成功{}条数据，校验失败{}条数据", successNum.get(), failureData.size());
92.         try {
93.             saveData();
94.         } catch (Exception e) {
95.             log.error(e.getMessage(), e);
96.         }
97.     }
99.     /**
100.      * 将该类做成抽象类，在各service中实现saveDate方法，
101.      * 不侵入业务，同时不会让解析占用内存
102.      */
103.     public void saveData() throws Exception {
104.         log.info("开始往数据库插入数据");
105.     }
107.     private Map<Integer, String> getIndexNameMap(Class<T> clazz) throws NoSuchFieldException {
108.         Map<Integer, String> excelPropertyMap = new HashMap<>();
109.         Field field;
110.         Field[] fields = clazz.getDeclaredFields();
111.         int sequence = 0;
112.         for (Field item : fields) {
113.             field = clazz.getDeclaredField(item.getName());
114.             field.setAccessible(true);
115.             ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
116.             if (excelProperty != null) {
117.                 // 避免每个列都要写index,插入或删除一个字段，所有的index都需要修改。默认为java实体类中字段的顺序。
118.                 int index = excelProperty.index() == -1 ? sequence : excelProperty.index();
119.                 String[] values = excelProperty.value();
120.                 StringBuilder value = new StringBuilder();
121.                 for (String v : values) {
122.                     value.append(v);
123.                 }
124.                 excelPropertyMap.put(index, value.toString());
125.                 sequence++;
126.             }
127.         }
128.         return excelPropertyMap;
129.     }
131. }

复制代码

3.1、分析成功的数据直接落库，错误数据导出

        ValidateBaseBo：用在导入的时候，将校验的错误保留下来，然后再把有问题的数据过滤出来，再导出，或者表现在前端的导入效果里，操纵者可以按照错误信息把表格里的数据修改好后，再次导入，而且只将导入失败的数据导出，不用去原表中大片的数据中去找有错误信息的数据，目标是方便操纵者快速定位表格里的问题数据。

1. /**
2. * @Title: ValidateBaseBo
3. * @Description:
4. * @Author: wenrong
5. * @Date: 2024/10/17 上午11:02
6. * @Version:1.0
7. */
8. @Data
9. public abstract class ValidateBaseBo {
11.     @ExcelProperty(value = "错误信息")
12.     @TableField(exist = false)
13.     @ApiModelProperty(hidden = true)
14.     private String errMsg;
15. }

复制代码

3.2、分析过程中校验数据精确性 

        除了javax.validation，基础的注解校验之外，假如还必要额外的校验，就自界说校验器作增补。

1. /**
2. * @Title: ValidData
3. * @Description: javax.validation 以外校验函数
4. * @Author: wenrong
5. * @Date: 2024/4/26 19:51
6. * @Version:1.0
7. */
8. public interface Validator<T> {
10.     /**
11.      * 这里的实现方法，最后返回的如果为null，一定要返回""，否则会被转化为"null"
12.      *
13.      * @param T t
14.      * @return ""
15.      */
16.     String validate(T t);
17. }

复制代码

3.3、导入/导出 Convertor 

        excel导入数据对应的实体类：要注意表格中的汉字和现实存入到数据库中数值的转换：Convertor

1. /**
2. * @author wenrong
3. * @date 2024-11-25 17:38:26
4. */
5. @Data
6. @Builder
7. @NoArgsConstructor
8. @AllArgsConstructor
9. @TableName("····")
10. public class YourClass extends ValidateBaseBo implements Serializable {
12.     private static final long serialVersionUID = 1L;
13.     public static Map<String, DyzProvinceSchool> provinceMap = new HashMap<>();
14.     public static Map<String, DyzProvinceSchool> schoolMap = new HashMap<>();
15.     @ExcelIgnore
16.     @TableId(type = IdType.AUTO)
17.     private Long id;
19.     @ApiModelProperty(value = "省份ID")
20.     @ExcelProperty(value = "省份", converter = ProvinceConvertor.class)
21.     @NotNull(message = "不能为空")
22.     private Integer provinceId;
24.     @ApiModelProperty(value = "学校ID")
25.     @ExcelProperty(value = "学校", converter = SchoolConvertor.class)
26.     @NotNull(message = "不能为空")
27.     private Integer schoolId;
29.     @ApiModelProperty(value = "节目代码")
30.     @ExcelProperty(value = "节目代码")
31.     @NotNull(message = "不能为空")
32.     private String worksNumber;
34.     @ApiModelProperty(value = "节目/项目名称")
35.     @ExcelProperty(value = "节目/项目名称")
36.     @NotNull(message = "不能为空")
37.     private String ``````;
41.     public String validate(Map<Integer, DyzProvinceSchool> provinceMap, Map<Integer, DyzProvinceSchool> schoolMap) {
42.         StringBuilder sb = new StringBuilder(this.getErrMsg() == null ? "" : this.getErrMsg());
43.         if (provinceMap.get(this.provinceId) == null) {
44.             sb.append("省份不存在: ").append(provinceId);
45.         }
47.         if (schoolMap.get(this.schoolId) == null) {
48.             sb.append("学校不存在: ").append(schoolId);
49.         }
51.         return sb.toString();
52.     }
54.     public static class GroupTypeConvertor implements Converter<Integer> {
56.         //导入的时候，将表格的汉字转换成java对应数据库的字段
57.         @Override
58.         public Integer convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty,
59.                                          GlobalConfiguration globalConfiguration) {
60.             switch (cellData.getStringValue()) {
61.                 default:
62.                     return 2;
63.                 case "小学组":
64.                     return 0;
65.                 case "中学组":
66.                     return 1;
67.             }
68.         }
69.         
70.         // 导出的时候，将数据库中存储的值，转换为用户能看懂的汉字
71.         @Override
72.         public WriteCellData<?> convertToExcelData(Integer value,
73.                                                    ExcelContentProperty excelContentProperty,
74.                                                    GlobalConfiguration globalConfiguration) {
75.             switch (value) {
76.                 default:
77.                     return new WriteCellData<>("其他组");
78.                 case 0:
79.                     return new WriteCellData<>("小学组");
80.                 case 1:
81.                     return new WriteCellData<>("中学组");
82.             }
83.         }
85.     }
87.     public static class PresentConvertor implements Converter<Integer> {
88.         @Override
89.         public Integer convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty,
90.                                          GlobalConfiguration globalConfiguration) {
91.             if (cellData.getStringValue().equals("否")) {
92.                 return 0;
93.             } else {
94.                 return 1;
95.             }
96.         }
97.         
98.         @Override
99.         public WriteCellData<?> convertToExcelData(Integer value,
100.                                                    ExcelContentProperty excelContentProperty,
101.                                                    GlobalConfiguration globalConfiguration) {
102.             switch (value) {
103.                 default:
104.                     return new WriteCellData<>("-");
105.                 case 0:
106.                     return new WriteCellData<>("否");
107.                 case 1:
108.                     return new WriteCellData<>("是");
109.             }
110.         }
111.     }
113.     public static class ProvinceConvertor implements Converter<Integer> {
114.         @Override
115.         public Integer convertToJavaData(ReadCellData<?> cellData,
116.                                          ExcelContentProperty contentProperty,
117.                                          GlobalConfiguration globalConfiguration) throws Exception {
118.             if (provinceMap.isEmpty()) {
119.                 throw new Exception("省份配置数据为空");
120.             }
121.             return StringUtils.isBlank(cellData.getStringValue()) ? null : provinceMap.get(cellData.getStringValue()).getId();
122.         }
123.     }
125.     public static class SchoolConvertor implements Converter<Integer> {
126.         @Override
127.         public Integer convertToJavaData(ReadCellData<?> cellData,
128.                                          ExcelContentProperty contentProperty,
129.                                          GlobalConfiguration globalConfiguration) throws Exception {
130.             if (schoolMap.isEmpty()) {
131.                 throw new Exception("学校配置数据为空");
132.             }
133.             return StringUtils.isBlank(cellData.getStringValue()) ? null : schoolMap.get(cellData.getStringValue()).getId();
134.         }
135.         
136.         // 导出转换省略掉
137.     }
139. }

复制代码

3.4、实现导入分析监听器    

        上面的分析监听器是个抽象类，是一种模板模式的设计头脑应用，我们根据不同的业务，自己扩展invoke方法和saveData方法，但其实saveData也可以做成模板方法，只是必要依靠内部绑定一个数据层dao接口，Mapper，对于有的人来说，会耦合dao层，但我以为假如dao层取一个接口，那么也没什么耦合的问题。节流不必要的重复代码，还是值得的。
那么上述的谁人模板抽象分析监听器可以改为：

1. /**
2. * @Title: ExcelImportReadListener
3. * @Description: 其他人如果觉得invoke()方法不满足其需求，可以自己实现一下
4. * @Author: wenrong
5. * @Date: 2024/4/25 17:08
6. * @Version:1.0
7. */
8. @Data
9. public abstract class ExcelImportReadListener<T extends ValidateBaseBo, S extends IService<T>> extends AnalysisEventListener<T> {
11.     private static final Logger log = LoggerFactory.getLogger("excelReadListener");
13.     public static int BATCH_COUNT = 1000;
15.     private AtomicLong successNum = new AtomicLong();
17.     private final Class<T> clazz;
19.     private S service;
21.     private Validator<T> validator;
23.     private List<T> successData = new ArrayList<>();
25.     private List<T> failureData = new ArrayList<>();
28.     public ExcelImportReadListener(Class<T> clazz, S service) {
29.         this.clazz = clazz;
30.         this.service = service;
31.     }
33.     @Override
34.     public void invoke(T data, AnalysisContext context) {
35.         log.info("解析到一条数据:{}", JSONObject.toJSON(data));
36.         StringBuilder errMsg = new StringBuilder();
37.         try {
38.             //根据excel数据实体中的javax.validation + 正则表达式来校验excel数据
39.             errMsg.append(EasyExcelValidateHandler.validateEntity(data));
40.             // 额外自定义校验，以及设置数据属性的逻辑
41.             if (validator != null) {
42.                 errMsg.append(validator.validate(data));
43.             }
44.         } catch (NoSuchFieldException e) {
45.             log.error(e.getMessage());
46.         }
47.         if (StringUtils.isNotEmpty(errMsg.toString())) {
48.             data.setErrMsg(errMsg.toString());
49.             failureData.add(data);
50.         } else {
51.             successData.add(data);
52.             successNum.incrementAndGet();
53.         }
55.         if (BATCH_COUNT != 0 && successData.size() >= BATCH_COUNT) {
56.             try {
57.                 saveData();
58.             } catch (Exception e) {
59.                 log.error(e.getMessage(), e);
60.             }
61.             successData.clear();
62.         }
64.     }
66.     @Override
67.     public final void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
68.         // 验证表头
69.         if (headMap.isEmpty()) {
70.             throw new ServiceException("无表头");
71.         }
72.         if (clazz != null) {
73.             try {
74.                 Map<Integer, String> indexNameMap = getIndexNameMap(clazz);
75.                 for (Integer index : indexNameMap.keySet()) {
76.                     log.info("表头字段：{}", headMap.get(index));
77.                     if (StringUtils.isEmpty(headMap.get(index))) {
78.                         throw new ServiceException("未设置index");
79.                     }
80.                     if (!headMap.get(index).equals(indexNameMap.get(index))) {
81.                         throw new ServiceException("导入模板错误");
82.                     }
83.                 }
84.             } catch (NoSuchFieldException e) {
85.                 log.error(e.getMessage(), e);
86.             }
87.         }
89.     }
91.     @Override
92.     public final void doAfterAllAnalysed(AnalysisContext context) {
93.         log.info("所有数据解析完成！共校验成功{}条数据，校验失败{}条数据", successNum.get(), failureData.size());
94.         try {
95.             saveData();
96.         } catch (Exception e) {
97.             log.error(e.getMessage(), e);
98.         }
99.     }
101.     /**
102.      * 将该类做成抽象类，在各service中实现saveDate方法，
103.      * 不侵入业务，同时不会让解析占用内存
104.      */
105.     public void saveData() throws Exception {
106.         log.info("开始往数据库插入数据");
107.         List<T> successData = this.getSuccessData();
108.         List<T> failureData = this.getFailureData();
109.         boolean saved = service.saveBatch(successData);
110.         if (!saved) {
111.             successData.forEach(work -> work.setErrMsg("保存失败"));
112.             failureData.addAll(successData);
113.         } else {
114.             this.setSuccessData(successData);
115.         }
116.     }
118.     private Map<Integer, String> getIndexNameMap(Class<T> clazz) throws NoSuchFieldException {
119.         Map<Integer, String> excelPropertyMap = new HashMap<>();
120.         Field field;
121.         Field[] fields = clazz.getDeclaredFields();
122.         int sequence = 0;
123.         for (Field item : fields) {
124.             field = clazz.getDeclaredField(item.getName());
125.             field.setAccessible(true);
126.             ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
127.             if (excelProperty != null) {
128.                 int index = excelProperty.index() == -1 ? sequence : excelProperty.index();
129.                 String[] values = excelProperty.value();
130.                 StringBuilder value = new StringBuilder();
131.                 for (String v : values) {
132.                     value.append(v);
133.                 }
134.                 excelPropertyMap.put(index, value.toString());
135.                 sequence++;
136.             }
137.         }
138.         return excelPropertyMap;
139.     }
141. }

复制代码

业务代码中实现模板分析监听器的代码示例：

1.     @Override
2.     @Transactional
3.     public ExcelImportReadListener<BasicWorks> importExcel(MultipartFile file) throws IOException {
5.         List<DyzProvinceSchool> schoolList = dyzProvinceSchoolService.getSchoolList();
6.         List<DyzProvinceSchool> provinceList = dyzProvinceSchoolService.getProvinceList();
7.         Map<String, DyzProvinceSchool> schoolMap = schoolList.stream().collect(Collectors.toMap(DyzProvinceSchool::getSchoolName, s -> s));
8.         Map<Integer, DyzProvinceSchool> schoolMap1 = schoolList.stream().collect(Collectors.toMap(DyzProvinceSchool::getId, s -> s));
9.         Map<String, DyzProvinceSchool> provinceMap = provinceList.stream().collect(Collectors.toMap(DyzProvinceSchool::getProvinceName, s -> s));
10.         Map<Integer, DyzProvinceSchool> provinceMap1 = provinceList.stream().collect(Collectors.toMap(DyzProvinceSchool::getId, s -> s));
11.         BasicWorks.schoolMap = schoolMap;
12.         BasicWorks.provinceMap = provinceMap;
14.         // 匿名内部类扩展模板监听器
15.         ExcelImportReadListener<BasicWorks> readListener = new ExcelImportReadListener<BasicWorks>(BasicWorks.class) {
16.             @Override
17.             public void invoke(BasicWorks data, AnalysisContext context) {
18.                 Set<String> allDataExistInExcel = new HashSet<>();
19.                 Set<String> allDataExistInDataSource = list().stream().map(BasicWorks::getWorksNumber).collect(Collectors.toSet());
20.                 List<BasicWorks> failureData = this.getFailureData();
22.                 StringBuilder errMsg = new StringBuilder(data.getErrMsg() == null ? "" : data.getErrMsg());
23.                 if (StringUtils.isBlank(data.getWorksNumber())) {
24.                     errMsg.append("节目代码不能为空，");
25.                     data.setErrMsg(errMsg.toString());
26.                     failureData.add(data);
27.                 } else {
28.                     if (allDataExistInExcel.contains(data.getWorksNumber())) {
29.                         errMsg.append("Excel表格中存在重复的数据，")
30.                                 .append("节目代码：").append(data.getWorksNumber());
31.                         data.setErrMsg(errMsg.toString());
32.                         failureData.add(data);
33.                         allDataExistInExcel.add(data.getWorksNumber());
35.                     }
36.                     if (allDataExistInDataSource.contains(data.getWorksNumber())) {
37.                         errMsg.append("数据库中存在重复的数据，")
38.                                 .append("节目代码：").append(data.getWorksNumber());
39.                         data.setErrMsg(errMsg.toString());
40.                         failureData.add(data);
41.                         allDataExistInExcel.add(data.getWorksNumber());
42.                     }
43.                 }
44.                 allDataExistInExcel.add(data.getWorksNumber());
45.                 super.invoke(data, context);
46.             }
47.         
48.         // 设置javax.validation以外校验器，将会在invoke方法里执行校验
49.         readListener.setValidator(work -> work.validate(provinceMap1, schoolMap1));
50.         // 导入 Excel
51.         EasyExcels.read(file, BasicWorks.class, readListener);
52.         return readListener;
53.     }

复制代码

另外还有必要将表格中图片导入后主动上传到文件服务，然后将url生存在数据库的需求：

1. public ExcelImportReadListener<BasicHotel> importExcel(MultipartFile file) throws IOException {
2.     //获取图片，联合Apache 的ExcelUtil，ExcelPicUtil工具类，获取图片数据对象PictureData
3.     ExcelReader reader = ExcelUtil.getReader(file.getInputStream());
4.     Map<String, PictureData> picMap = ExcelPicUtil.getPicMap(reader.getWorkbook(), 0);
6.     ExcelImportReadListener<BasicHotel> readListener = new ExcelImportReadListener<BasicHotel>(BasicHotel.class) {
7.         @Override
8.         public void invoke(BasicHotel data, AnalysisContext context) {
9.             Set<String> allDataExistInDataSource = list().stream().map(BasicHotel::getHotelName).collect(Collectors.toSet());
10.             Set<String> allDataExistInExcel = new HashSet<>();
11.             List<BasicHotel> failureData = this.getFailureData();
13.             StringBuilder errMsg = new StringBuilder(data.getErrMsg() == null ? "" : data.getErrMsg());
14.             if (StringUtils.isEmpty(data.getErrMsg())) {
15.                 errMsg.append("酒店名称不能为空，");
16.                 data.setErrMsg(errMsg.toString());
17.                 failureData.add(data);
18.             } else {
19.                 if (allDataExistInExcel.contains(data.getHotelName())) {
20.                     errMsg.append("Excel表格中存在重复的数据，")
21.                             .append("酒店名称：").append(data.getHotelName());
22.                     data.setErrMsg(errMsg.toString());
23.                     failureData.add(data);
24.                     allDataExistInExcel.add(data.getHotelName());
25.                 }
26.                 if (allDataExistInDataSource.contains(data.getHotelName())) {
27.                     errMsg.append("数据库中存在重复的数据，")
28.                             .append("酒店名称：").append(data.getHotelName());
29.                     data.setErrMsg(errMsg.toString());
30.                     failureData.add(data);
31.                     allDataExistInExcel.add(data.getHotelName());
32.                 }
33.             }
34.             allDataExistInExcel.add(data.getHotelName());
35.             String err = "";
36.             int rowIndex = context.readRowHolder().getRowIndex() + 1;
37.             PictureData pictureData = picMap.get(rowIndex + "_0");
38.             if (pictureData == null) {
39.                 err = String.format(data.getErrMsg() + "第%s行，%s", rowIndex, "酒店照片为空");
40.             }
42.             try {
43.                 // 上传图片
44.                 String fileUrl = ossFileController.ftpUploadFile(pictureData.getData(), "", data.getHotelName());
45.                 data.setPicture(fileUrl);
46.             } catch (IOException ex) {
47.                 err = String.format(data.getErrMsg() + "第%s行，%s", rowIndex, "酒店照片为空上传失败");
48.             }
50.             data.setErrMsg(err);
51.             super.invoke(data, context);
52.         }
53.     };
54.     // 导入 Excel
55.     EasyExcels.read(file, BasicHotel.class, readListener);
56.     return readListener;
57. }

复制代码

        这是导入部分，导出部分，五花八门的需求就比较多了。

四、导出


4.1、导出 数据导入模板

模板一样平常会有下拉选项列的需求，下拉列一样平常用注解枚举几个就行了：

2. import java.lang.annotation.*;
4. /**
5. * 标注导出的列为下拉框类型，并为下拉框设置内容
6. */
7. @Documented
8. @Retention(RetentionPolicy.RUNTIME)
9. @Target(ElementType.FIELD)
10. public @interface ExcelSelected {
11.     /**
12.      * 固定下拉内容
13.      */
14.     String[] source() default {};
16.     /**
17.      * 设置下拉框的起始行，默认为第二行
18.      */
19.     int firstRow() default 1;
21.     /**
22.      * 设置下拉框的结束行，默认为最后一行
23.      */
24.     int lastRow() default 0x10000;
25. }

复制代码

下拉注解分析器：

1. @Data
2. @Slf4j
3. public class ExcelSelectedResolve {
4.     /**
5.      * 下拉内容
6.      */
7.     private String[] source;
9.     /**
10.      * 设置下拉框的起始行，默认为第二行
11.      */
12.     private int firstRow;
14.     /**
15.      * 设置下拉框的结束行，默认为最后一行
16.      */
17.     private int lastRow;
19.     public String[] resolveSelectedSource(ExcelSelected excelSelected) {
20.         if (excelSelected == null) {
21.             return null;
22.         }
24.         // 获取固定下拉框的内容
25.         String[] source = excelSelected.source();
26.         if (source.length > 0) {
27.             return source;
28.         }
30. //        // 获取动态下拉框的内容
31. //        Class<? extends ExcelDynamicSelect>[] classes = excelSelected.sourceClass();
32. //        if (classes.length > 0) {
33. //            try {
34. //                ExcelDynamicSelect excelDynamicSelect = classes[0].newInstance();
35. //                String[] dynamicSelectSource = excelDynamicSelect.getSource();
36. //                if (dynamicSelectSource != null && dynamicSelectSource.length > 0) {
37. //                    return dynamicSelectSource;
38. //                }
39. //            } catch (InstantiationException | IllegalAccessException e) {
40. //                log.error("解析动态下拉框数据异常", e);
41. //            }
42. //        }
43.         return null;
44.     }
46. }

复制代码

下拉handler:

1. public class SelectSheetWriteHandler implements SheetWriteHandler {
3.     private final Map<Integer, ExcelSelectedResolve> selectedMap;
5.     public SelectSheetWriteHandler(Map<Integer, ExcelSelectedResolve> selectedMap) {
6.         this.selectedMap = selectedMap;
7.     }
9.     /**
10.      * Called before create the sheet
11.      */
12.     @Override
13.     public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
15.     }
17.     /**
18.      * Called after the sheet is created
19.      */
20.     @Override
21.     public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
22.         // 这里可以对cell进行任何操作
23.         Sheet sheet = writeSheetHolder.getSheet();
24.         DataValidationHelper helper = sheet.getDataValidationHelper();
25.         selectedMap.forEach((k, v) -> {
26.             // 设置下拉列表的行： 首行，末行，首列，末列
27.             CellRangeAddressList rangeList = new CellRangeAddressList(v.getFirstRow(), v.getLastRow(), k, k);
28.             // 设置下拉列表的值
29.             DataValidationConstraint constraint = helper.createExplicitListConstraint(v.getSource());
30.             // 设置约束
31.             DataValidation validation = helper.createValidation(constraint, rangeList);
32.             // 阻止输入非下拉选项的值
33.             validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
34.             validation.setShowErrorBox(true);
35.             validation.setSuppressDropDownArrow(true);
36.             validation.createErrorBox("提示", "请输入下拉选项中的内容");
37.             sheet.addValidationData(validation);
38.         });
39.     }
41. }

复制代码

        其实假如是动态的下拉列表，不能固定枚举的话，直接从配置数据表中拉出业务配置列表，将列表作为传参，使用util类EasyExcels第一个方法就好。

1.     @ExcelProperty(value = "组别")
2.     @ExcelSelected(source = {"小学组", "初中组"})
3.     @ApiModelProperty(value = "组别:0-小学组，1-初中组，2-其他组")
4.     @NotNull(message = "不能为空")
5.     private String groupType;
9.     @ExcelProperty(value = "是否出席")
10.     @ExcelSelected(source = {"是", "否"})
11.     @ApiModelProperty(value = "是否出席：0-否，1是")
12.     @NotNull(message = "不能为空")
13.     private String present;

复制代码

4.2、图片导出convertor：

1. public class UrlPictureConverter implements Converter<String> {
2.     public static int urlConnectTimeout = 2000;
3.     public static int urlReadTimeout = 6000;
5.     @Override
6.     public Class<?> supportJavaTypeKey() {
7.         return String.class;
8.     }
10.     @Override
11.     public WriteCellData<?> convertToExcelData(String url, ExcelContentProperty contentProperty,
12.                                                GlobalConfiguration globalConfiguration) throws IOException {
13.         InputStream inputStream = null;
14.         try {
15.             URL value = new URL(url);
16.             if (ObjectUtils.isEmpty(value)) {
17.                 return new WriteCellData<>("");
18.             }
19.             URLConnection urlConnection = value.openConnection();
20.             urlConnection.setConnectTimeout(urlConnectTimeout);
21.             urlConnection.setReadTimeout(urlReadTimeout);
22.             inputStream = urlConnection.getInputStream();
23.             byte[] bytes = IoUtils.toByteArray(inputStream);
24.             return new WriteCellData<>(bytes);
25.         } catch (Exception e) {
26.             log.info("图片获取异常", e);
27.             return new WriteCellData<>("图片获取异常");
28.         } finally {
29.             if (inputStream != null) {
30.                 inputStream.close();
31.             }
32.         }
33.     }
34. }

复制代码

4.3、有归并单元格导出：

1. @Data
2. @Builder
3. @AllArgsConstructor
4. @NoArgsConstructor
5. @ExcelIgnoreUnannotated
6. @Slf4j
7. public class WorkJudgesStatisticsVo implements Serializable {
8.     private static final long serialVersionUID = 1L;
10.     @ExcelProperty(value = "序号", index = 0)
11.     private String sequence;
15.     public static class MergeStrategy implements RowWriteHandler {
17.         private int totalRowNum;
19.         public MergeStrategy(int totalRowNum) {
20.             this.totalRowNum = totalRowNum;
21.         }
23.         public static MergeStrategy build(int totalRowNum) {
24.             return new MergeStrategy(totalRowNum);
25.         }
27.         @Override
28.         public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
29.             if (isHead) {
30.                 // 处理表头
31.                 return;
32.             }
33.             log.info("当前行号:{}", row.getRowNum());
34.             log.info("总行数:{}", totalRowNum);
35.             // 合并
36.             if (row.getRowNum() != totalRowNum + 1) {
37.                 return;
38.             }
39.             writeSheetHolder.getSheet().addMergedRegion(new CellRangeAddress(writeSheetHolder.getLastRowIndex(), writeSheetHolder.getLastRowIndex(), 5, 6));
40.         }
41.     }
44. }

复制代码

4.4、行转列，并使用模板的方式导出：

1. @Override
2. public void selectWorksJudgesResultReview(HttpServletResponse response) throws IOException {
3.     List<Map<String, Object>> views = scoreReviewWorksJudgesMapper.selectWorksJudgesResultReview();
4.     List<DyzScoreReviewWorksJudges> reviewWorksJudges = scoreReviewWorksJudgesMapper.selectList();
5.     List<DyzScoreWorksFiles> scoreWorksFiles = worksFilesMapper.selectList();
6.     HashMap<Long, List<DyzScoreWorksFiles>> fileMap = new HashMap<>();
7.     scoreWorksFiles.forEach(f -> {
8.         List<DyzScoreWorksFiles> files = fileMap.computeIfAbsent(f.getWorksId(), k -> new ArrayList<>());
9.         files.add(f);
10.     });
12.     HashMap<Long, List<DyzScoreReviewWorksJudges>> scoreMap = new HashMap<>();
13.     reviewWorksJudges.forEach(judge -> {
14.         List<DyzScoreReviewWorksJudges> judges = scoreMap.computeIfAbsent(judge.getWorksId(), k -> new ArrayList<>());
15.         judges.add(judge);
16.     });
17.     AtomicInteger sequence = new AtomicInteger(0);
18.     views.forEach(map -> {
19.         map.put("sequence", String.valueOf(sequence.incrementAndGet()));
20.         Long workId = Long.valueOf(map.get("workId").toString());
21.         List<DyzScoreReviewWorksJudges> judges = scoreMap.get(workId);
22.         for (int i = 0; i < 15; i++) {
23.             map.put("score" + (i + 1), "");
24.             map.put("correctness" + (i + 1), "");
25.         }
26.         map.put("avgScore", "");
27.         map.put("avgScore1", "");
29.         if (judges != null && judges.size() > 0) {
30.             AtomicInteger serialNo = new AtomicInteger(0);
31.             AtomicInteger serialNo1 = new AtomicInteger(0);
32.             judges.forEach(j -> {
33.                 map.put("score" + serialNo.incrementAndGet(), j.getScore());
34.                 map.put("correctness" + serialNo1.incrementAndGet(), j.getRemark());
35.             });
36.             judges.sort(Comparator.comparing(DyzScoreReviewWorksJudges::getScore));
37.             BigDecimal sum = judges.stream().map(DyzScoreReviewWorksJudges::getScore).reduce(BigDecimal.ZERO, BigDecimal::add);
38.             BigDecimal avg = sum.divide(BigDecimal.valueOf(judges.size()), 2, BigDecimal.ROUND_HALF_UP);
39.             map.put("avgScore", avg);
40.             if (judges.size() > 3) {
41.                 judges.remove(0);
42.                 judges.remove(judges.size() - 1);
43.                 BigDecimal sum1 = judges.stream().map(DyzScoreReviewWorksJudges::getScore).reduce(BigDecimal.ZERO, BigDecimal::add);
44.                 BigDecimal avg1 = sum1.divide(BigDecimal.valueOf(judges.size()), 2, BigDecimal.ROUND_HALF_UP);
45.                 map.put("avgScore1", avg1);
46.             }
47.         }
49.         for (int i = 0; i < 4; i++) {
50.             map.put("fileName" + (i + 1), "");
51.         }
52.         List<DyzScoreWorksFiles> files = fileMap.get(workId);
53.         if (files != null && files.size() > 0) {
54.             AtomicInteger serialNo = new AtomicInteger(0);
55.             files.forEach(f -> map.put("fileName" + serialNo.incrementAndGet(), f.getUrl()));
56.         }
58.     });
60.     ConcurrentHashSet<String> columns = views.stream().flatMap(map -> map.keySet().stream()).collect(Collectors.toCollection(ConcurrentHashSet::new));
61.     List<String> scoreColumns = columns.stream().filter(c -> c.contains("score") || c.contains("avgScore")).collect(Collectors.toList());
62.     List<String> correctnessColumns = columns.stream().filter(c -> c.contains("correctness")).collect(Collectors.toList());
64.     //输入流
65.     InputStream inputStream = null;
66.     ServletOutputStream outputStream = null;
67.     ExcelWriter excelWriter = null;
68.     try {
69.         org.springframework.core.io.Resource templateFile = resourceLoader.getResource("classpath:templates\\XXXX报表.xlsx");
70.         inputStream = templateFile.getInputStream();
71.         // 获取文件名并转码
72.         response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
73.         response.setCharacterEncoding("utf-8");
74.         outputStream = response.getOutputStream();
75.         // 创建填充配置
76.         FillConfig fillConfig = FillConfig.builder().forceNewRow(true).build();
77.         // 创建写对象
78.         excelWriter = EasyExcel.write(outputStream)
79.                 .withTemplate(inputStream).build();
80.         // 创建Sheet对象
81.         WriteSheet sheet = EasyExcel.writerSheet(0, "报名数量统计").build();
82.         excelWriter.fill(views, fillConfig, sheet);
83.         excelWriter.fill(new FillWrapper("scoreColumns", scoreColumns), sheet);
84.         excelWriter.fill(new FillWrapper("correctnessColumns", correctnessColumns), sheet);
85.     } catch (Exception e) {
86.         log.error("导出失败={}", e.getMessage());
87.     } finally {
88.         if (excelWriter != null) {
89.             excelWriter.finish();
90.         }
91.         //关闭流
92.         if (outputStream != null) {
93.             try {
94.                 outputStream.close();
95.             } catch (IOException e) {
96.                 log.error("关闭输出流失败", e);
97.             }
98.         }
99.         if (inputStream != null) {
100.             try {
101.                 inputStream.close();
102.             } catch (IOException e) {
103.                 log.error("关闭输入流失败", e);
104.             }
105.         }
107.     }
108. }

复制代码

模板内里的取值占位符写法

        还有一些表格宽度，高度自顺应策略，美化风格的代码就不贴了，必要的话到我的资源中去下载。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。