From a0d5a27690869a2a80251976b119c0b0b8fb58c7 Mon Sep 17 00:00:00 2001
From: fuliqi <fuliqi@qq.com>
Date: 星期五, 29 十一月 2024 04:50:42 +0800
Subject: [PATCH] 移植excelUtils
---
common/src/main/java/com/ycl/common/utils/excel/core/DefaultExcelResult.java | 73 +
common/src/main/java/com/ycl/common/utils/excel/utils/ValidatorUtils.java | 36
common/src/main/java/com/ycl/common/utils/excel/core/CellMergeStrategy.java | 157 ++++
common/src/main/java/com/ycl/common/utils/excel/core/ExcelDownHandler.java | 373 +++++++++
common/src/main/java/com/ycl/common/utils/excel/core/ExcelResult.java | 26
common/src/main/java/com/ycl/common/utils/excel/service/DictService.java | 67 +
common/src/main/java/com/ycl/common/utils/excel/utils/JsonUtils.java | 169 ++++
common/src/main/java/com/ycl/common/utils/excel/convert/ExcelEnumConvert.java | 87 ++
common/src/main/java/com/ycl/common/utils/excel/core/DropDownOptions.java | 149 +++
common/src/main/java/com/ycl/common/utils/excel/core/ExcelListener.java | 14
common/src/main/java/com/ycl/common/utils/excel/convert/ExcelBigNumberConvert.java | 52 +
common/src/main/java/com/ycl/common/utils/excel/annotation/CellMerge.java | 29
common/src/main/java/com/ycl/common/utils/excel/annotation/ExcelDictFormat.java | 32
business/src/main/java/com/ycl/controller/ProjectInfoController.java | 16
common/src/main/java/com/ycl/common/utils/excel/utils/ReflectUtils.java | 55 +
common/src/main/java/com/ycl/common/utils/excel/utils/StringUtils.java | 323 ++++++++
common/pom.xml | 9
common/src/main/java/com/ycl/common/utils/excel/annotation/ExcelEnumFormat.java | 30
system/pom.xml | 6
common/src/main/java/com/ycl/common/utils/excel/core/DefaultExcelListener.java | 104 ++
common/src/main/java/com/ycl/common/utils/excel/OutputExcelUtils.java | 217 +++++
common/src/main/java/com/ycl/common/utils/excel/utils/StreamUtils.java | 282 +++++++
22 files changed, 2,300 insertions(+), 6 deletions(-)
diff --git a/business/src/main/java/com/ycl/controller/ProjectInfoController.java b/business/src/main/java/com/ycl/controller/ProjectInfoController.java
index f2e314c..f66bcc0 100644
--- a/business/src/main/java/com/ycl/controller/ProjectInfoController.java
+++ b/business/src/main/java/com/ycl/controller/ProjectInfoController.java
@@ -4,6 +4,8 @@
import com.ycl.common.group.Add;
import com.ycl.common.group.Update;
import com.ycl.common.utils.ProjectCodeGenerator;
+import com.ycl.common.utils.excel.OutputExcelUtils;
+import com.ycl.domain.excel.ProjectExcelTemplate;
import com.ycl.domain.form.DocumentInfoForm;
import com.ycl.domain.form.ProjectInfoForm;
import com.ycl.domain.query.ProjectInfoQuery;
@@ -14,7 +16,9 @@
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;
+import javax.servlet.http.HttpServletResponse;
import javax.validation.constraints.NotEmpty;
+import java.io.IOException;
import java.util.List;
/**
@@ -104,4 +108,16 @@
public Result getManagerFlag(@PathVariable("recordId") Integer recordId) {
return projectInfoService.getManagerFlag(recordId);
}
+
+ /**
+ * 瀵煎嚭妯℃澘
+ * @param response
+ * @return
+ */
+ @PostMapping("/export/template")
+ public void exportTemplate(HttpServletResponse response,
+ @RequestBody List<String> fieldList
+ ) throws IOException {
+ OutputExcelUtils.export(response, "瀵煎叆妯℃澘", "椤圭洰淇℃伅", null, ProjectExcelTemplate.class ,fieldList);
+ }
}
diff --git a/common/pom.xml b/common/pom.xml
index 99d8e16..7ee7272 100644
--- a/common/pom.xml
+++ b/common/pom.xml
@@ -128,7 +128,16 @@
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
</dependency>
+ <!-- easy excel -->
+ <dependency>
+ <groupId>com.alibaba</groupId>
+ <artifactId>easyexcel</artifactId>
+ </dependency>
+ <dependency>
+ <groupId>cn.hutool</groupId>
+ <artifactId>hutool-crypto</artifactId>
+ </dependency>
</dependencies>
</project>
diff --git a/common/src/main/java/com/ycl/common/utils/excel/OutputExcelUtils.java b/common/src/main/java/com/ycl/common/utils/excel/OutputExcelUtils.java
new file mode 100644
index 0000000..68d0bac
--- /dev/null
+++ b/common/src/main/java/com/ycl/common/utils/excel/OutputExcelUtils.java
@@ -0,0 +1,217 @@
+package com.ycl.common.utils.excel;
+
+import cn.hutool.core.collection.CollUtil;
+import cn.hutool.core.util.IdUtil;
+import cn.hutool.core.util.ObjectUtil;
+import cn.hutool.core.util.ZipUtil;
+import com.alibaba.excel.EasyExcel;
+import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
+import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
+import com.ycl.common.utils.excel.convert.ExcelBigNumberConvert;
+import com.ycl.common.utils.excel.core.CellMergeStrategy;
+import com.ycl.common.utils.excel.core.DropDownOptions;
+import com.ycl.common.utils.excel.core.ExcelDownHandler;
+import com.ycl.common.utils.file.FileUtils;
+import org.apache.commons.codec.Charsets;
+
+import javax.servlet.ServletOutputStream;
+import javax.servlet.http.HttpServletResponse;
+import javax.validation.constraints.NotNull;
+import java.io.*;
+import java.lang.reflect.Field;
+import java.net.URLEncoder;
+import java.nio.file.Files;
+import java.util.HashSet;
+import java.util.List;
+import java.util.Set;
+
+/**
+ * @Author: ljx
+ * @CreateTime: 2024-10-18 10:13
+ */
+
+public class OutputExcelUtils {
+
+ /**
+ * 蹇界暐閮ㄥ垎瀵煎嚭瀛楁鏂规硶
+ * @param response
+ * @param fileName 鏂囦欢鍚嶇О
+ * @param sheetName sheet鍚嶇О
+ * @param dataList 闇�瑕佸鍑虹殑鏁版嵁
+ * @param clazz 绫�
+ * @param
+ * @param <T>
+ * @throws IOException
+ */
+ public static <T> void export(HttpServletResponse response, String fileName, String sheetName, List<T> dataList, Class<T> clazz, List<String> fieldNames) throws IOException {
+ response.setContentType("application/zip");
+ response.setCharacterEncoding(Charsets.UTF_8.name());
+ fileName = URLEncoder.encode(fileName, Charsets.UTF_8);
+ response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".zip");
+ Set<Integer> selectedIndexes = getSelectFields(fieldNames, clazz);
+
+ //涓嬭浇妯℃澘
+ downloadTemplate(response, sheetName, dataList, clazz, selectedIndexes);
+
+
+ // 涓存椂鍚戣祫婧愭枃浠跺す鍐� 鍚嶄负template鐨勬枃浠跺す
+// ClassPathResource classPathResource = new ClassPathResource("/template/test.xls");
+// File file = classPathResource.getFile();
+// try(FileOutputStream fileOutputStream = new FileOutputStream(file,false)) {
+// EasyExcel.write(fileOutputStream, clazz).sheet(sheetName).doWrite(dataList);
+// }
+//
+// ClassPathResource classPathResource1 = new ClassPathResource("/template");
+// File zip = ZipUtil.zip(classPathResource1.getFile());
+// byte[] bytes;
+// try (FileInputStream fileInputStream = new FileInputStream(zip)) {
+// bytes = fileInputStream.readAllBytes();
+// }
+// response.getOutputStream().write(bytes);
+
+// if (selectedIndexes.size() > 0) {
+// EasyExcel.write(response.getOutputStream(), clazz).excludeColumnIndexes(selectedIndexes).sheet(sheetName).doWrite(dataList);
+// } else {
+// EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(dataList);
+// }
+ }
+
+ private synchronized static <T> void downloadTemplate(HttpServletResponse response, String sheetName, List<T> dataList, Class<T> clazz, Set<Integer> columnIndex) throws IOException {
+
+ File tempDir = null;
+ try {
+ // 鍒涘缓涓存椂鐩綍
+ tempDir = Files.createTempDirectory("temp").toFile();
+
+ File templateDir = new File(tempDir, "template");
+ if (!templateDir.exists()) {
+ templateDir.mkdirs();
+ }
+
+ // 鍒涘缓 Excel 鏂囦欢
+ File excelFile = new File(templateDir, "excel.xlsx");
+ if (!excelFile.exists()) {
+ excelFile.createNewFile();
+ }
+
+ // 鍐欏叆 Excel 妯℃澘鏁版嵁
+ try (FileOutputStream fileOutputStream = new FileOutputStream(excelFile, false)) {
+ EasyExcel.write(fileOutputStream, clazz).includeColumnIndexes(columnIndex).sheet(sheetName).doWrite(dataList);
+ }
+
+ // 鍒涘缓闄勪欢鐩綍
+ File attachmentDir = new File(templateDir, "attachment");
+ if (!attachmentDir.exists()) {
+ attachmentDir.mkdirs();
+ }
+
+ // 鎵撳寘 ZIP 鏂囦欢
+ File zipFile = ZipUtil.zip(templateDir);
+ byte[] zipBytes = Files.readAllBytes(zipFile.toPath());
+
+ // 灏� ZIP 鏂囦欢鍐欏叆鍝嶅簲
+ try(ServletOutputStream outputStream = response.getOutputStream()) {
+ outputStream.write(zipBytes);
+ }
+ } finally {
+ deleteDirectoryOrFile(tempDir);
+ }
+ }
+
+ private static void deleteDirectoryOrFile(File file) {
+ if (ObjectUtil.isNull(file)) {
+ return;
+ }
+
+ if (file.isDirectory()) {
+ File[] files = file.listFiles();
+ if (files != null) {
+ for (File f : files) {
+ deleteDirectoryOrFile(f);
+ }
+ }
+ }
+ file.delete();
+
+ }
+
+ /**
+ * 瀵煎嚭妯℃澘
+ * @param response
+ * @param fileName
+ * @param sheetName
+ * @param dataList
+ * @param clazz
+ * @param fieldNames
+ */
+ public static <T> void exportTemplate(HttpServletResponse response,String fileName, String sheetName, List<T> dataList, Class<T> clazz, List<String> fieldNames) throws IOException {
+ Set<Integer> selectedIndexes = getSelectFields(fieldNames, clazz);
+
+ resetResponse(fileName, response);
+
+ exportExcel(dataList, sheetName, clazz, false, response.getOutputStream(), null, selectedIndexes);
+ }
+
+ public static <T> @NotNull Set<Integer> getSelectFields(List<String> fieldNames, Class<T> clazz) {
+ Set<Integer> selectedIndexes = new HashSet<>();
+ if (CollUtil.isNotEmpty(fieldNames)) {
+ // 鍙嶅皠鑾峰彇瀛楁灞炴��
+ Field[] declaredFields = clazz.getDeclaredFields();
+// // 鍖归厤闇�瑕佸鍏ョ殑瀛楁
+ for (int i = 0; i < declaredFields.length; i++) {
+ if (fieldNames.contains(declaredFields[i].getName())) {
+ // 鑾峰彇闇�瑕佸鍏ョ殑瀛楁涓嬫爣
+ selectedIndexes.add(i);
+ }
+ }
+ }
+ return selectedIndexes;
+
+ }
+
+ /**
+ * 瀵煎嚭excel
+ *
+ * @param list 瀵煎嚭鏁版嵁闆嗗悎
+ * @param sheetName 宸ヤ綔琛ㄧ殑鍚嶇О
+ * @param clazz 瀹炰綋绫�
+ * @param merge 鏄惁鍚堝苟鍗曞厓鏍�
+ * @param os 杈撳嚭娴�
+ */
+ public static <T> void exportExcel(List<T> list, String sheetName, Class<T> clazz, boolean merge,
+ OutputStream os, List<DropDownOptions> options, Set<Integer> selectedIndexes) {
+ ExcelWriterSheetBuilder builder = EasyExcel.write(os, clazz)
+ .autoCloseStream(false)
+ .includeColumnIndexes(selectedIndexes)
+ // 鑷姩閫傞厤
+ .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
+ // 澶ф暟鍊艰嚜鍔ㄨ浆鎹� 闃叉澶辩湡
+ .registerConverter(new ExcelBigNumberConvert())
+ .sheet(sheetName);
+ if (merge) {
+ // 鍚堝苟澶勭悊鍣�
+ builder.registerWriteHandler(new CellMergeStrategy(list, true));
+ }
+ // 娣诲姞涓嬫媺妗嗘搷浣�
+ builder.registerWriteHandler(new ExcelDownHandler(options));
+ builder.doWrite(list);
+ }
+
+ /**
+ * 閲嶇疆鍝嶅簲浣�
+ */
+ private static void resetResponse(String sheetName, HttpServletResponse response) throws UnsupportedEncodingException {
+ String filename = encodingFilename(sheetName);
+ FileUtils.setAttachmentResponseHeader(response, filename);
+ response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
+ }
+
+ /**
+ * 缂栫爜鏂囦欢鍚�
+ */
+ public static String encodingFilename(String filename) {
+ return IdUtil.fastSimpleUUID() + "_" + filename + ".xlsx";
+ }
+
+
+}
diff --git a/common/src/main/java/com/ycl/common/utils/excel/annotation/CellMerge.java b/common/src/main/java/com/ycl/common/utils/excel/annotation/CellMerge.java
new file mode 100644
index 0000000..1b78f92
--- /dev/null
+++ b/common/src/main/java/com/ycl/common/utils/excel/annotation/CellMerge.java
@@ -0,0 +1,29 @@
+package com.ycl.common.utils.excel.annotation;
+
+import com.ycl.common.utils.excel.core.CellMergeStrategy;
+
+import java.lang.annotation.*;
+
+/**
+ * excel 鍒楀崟鍏冩牸鍚堝苟(鍚堝苟鍒楃浉鍚岄」)
+ * <p>
+ * 闇�鎼厤 {@link CellMergeStrategy} 绛栫暐浣跨敤
+ *
+ * @author Lion Li
+ */
+@Target(ElementType.FIELD)
+@Retention(RetentionPolicy.RUNTIME)
+@Inherited
+public @interface CellMerge {
+
+ /**
+ * col index
+ */
+ int index() default -1;
+
+ /**
+ * 鍚堝苟闇�瑕佷緷璧栫殑鍏朵粬瀛楁鍚嶇О
+ */
+ String[] mergeBy() default {};
+
+}
diff --git a/common/src/main/java/com/ycl/common/utils/excel/annotation/ExcelDictFormat.java b/common/src/main/java/com/ycl/common/utils/excel/annotation/ExcelDictFormat.java
new file mode 100644
index 0000000..7205c59
--- /dev/null
+++ b/common/src/main/java/com/ycl/common/utils/excel/annotation/ExcelDictFormat.java
@@ -0,0 +1,32 @@
+package com.ycl.common.utils.excel.annotation;
+
+
+import com.ycl.common.utils.excel.utils.StringUtils;
+
+import java.lang.annotation.*;
+
+/**
+ * 瀛楀吀鏍煎紡鍖�
+ *
+ * @author Lion Li
+ */
+@Target({ElementType.FIELD})
+@Retention(RetentionPolicy.RUNTIME)
+@Inherited
+public @interface ExcelDictFormat {
+ /**
+ * 濡傛灉鏄瓧鍏哥被鍨嬶紝璇疯缃瓧鍏哥殑type鍊� (濡�: sys_user_sex)
+ */
+ String dictType() default "";
+
+ /**
+ * 璇诲彇鍐呭杞〃杈惧紡 (濡�: 0=鐢�,1=濂�,2=鏈煡)
+ */
+ String readConverterExp() default "";
+
+ /**
+ * 鍒嗛殧绗︼紝璇诲彇瀛楃涓茬粍鍐呭
+ */
+ String separator() default StringUtils.SEPARATOR;
+
+}
diff --git a/common/src/main/java/com/ycl/common/utils/excel/annotation/ExcelEnumFormat.java b/common/src/main/java/com/ycl/common/utils/excel/annotation/ExcelEnumFormat.java
new file mode 100644
index 0000000..3682609
--- /dev/null
+++ b/common/src/main/java/com/ycl/common/utils/excel/annotation/ExcelEnumFormat.java
@@ -0,0 +1,30 @@
+package com.ycl.common.utils.excel.annotation;
+
+import java.lang.annotation.*;
+
+/**
+ * 鏋氫妇鏍煎紡鍖�
+ *
+ * @author Liang
+ */
+@Target({ElementType.FIELD})
+@Retention(RetentionPolicy.RUNTIME)
+@Inherited
+public @interface ExcelEnumFormat {
+
+ /**
+ * 瀛楀吀鏋氫妇绫诲瀷
+ */
+ Class<? extends Enum<?>> enumClass();
+
+ /**
+ * 瀛楀吀鏋氫妇绫讳腑瀵瑰簲鐨刢ode灞炴�у悕绉帮紝榛樿涓篶ode
+ */
+ String codeField() default "code";
+
+ /**
+ * 瀛楀吀鏋氫妇绫讳腑瀵瑰簲鐨則ext灞炴�у悕绉帮紝榛樿涓簍ext
+ */
+ String textField() default "text";
+
+}
diff --git a/common/src/main/java/com/ycl/common/utils/excel/convert/ExcelBigNumberConvert.java b/common/src/main/java/com/ycl/common/utils/excel/convert/ExcelBigNumberConvert.java
new file mode 100644
index 0000000..50735ea
--- /dev/null
+++ b/common/src/main/java/com/ycl/common/utils/excel/convert/ExcelBigNumberConvert.java
@@ -0,0 +1,52 @@
+package com.ycl.common.utils.excel.convert;
+
+import cn.hutool.core.convert.Convert;
+import cn.hutool.core.util.ObjectUtil;
+import com.alibaba.excel.converters.Converter;
+import com.alibaba.excel.enums.CellDataTypeEnum;
+import com.alibaba.excel.metadata.GlobalConfiguration;
+import com.alibaba.excel.metadata.data.ReadCellData;
+import com.alibaba.excel.metadata.data.WriteCellData;
+import com.alibaba.excel.metadata.property.ExcelContentProperty;
+import lombok.extern.slf4j.Slf4j;
+
+import java.math.BigDecimal;
+
+/**
+ * 澶ф暟鍊艰浆鎹�
+ * Excel 鏁板�奸暱搴︿綅15浣� 澶т簬15浣嶇殑鏁板�艰浆鎹綅瀛楃涓�
+ *
+ * @author Lion Li
+ */
+@Slf4j
+public class ExcelBigNumberConvert implements Converter<Long> {
+
+ @Override
+ public Class<Long> supportJavaTypeKey() {
+ return Long.class;
+ }
+
+ @Override
+ public CellDataTypeEnum supportExcelTypeKey() {
+ return CellDataTypeEnum.STRING;
+ }
+
+ @Override
+ public Long convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
+ return Convert.toLong(cellData.getData());
+ }
+
+ @Override
+ public WriteCellData<Object> convertToExcelData(Long object, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
+ if (ObjectUtil.isNotNull(object)) {
+ String str = Convert.toStr(object);
+ if (str.length() > 15) {
+ return new WriteCellData<>(str);
+ }
+ }
+ WriteCellData<Object> cellData = new WriteCellData<>(new BigDecimal(object));
+ cellData.setType(CellDataTypeEnum.NUMBER);
+ return cellData;
+ }
+
+}
diff --git a/common/src/main/java/com/ycl/common/utils/excel/convert/ExcelEnumConvert.java b/common/src/main/java/com/ycl/common/utils/excel/convert/ExcelEnumConvert.java
new file mode 100644
index 0000000..b2479fb
--- /dev/null
+++ b/common/src/main/java/com/ycl/common/utils/excel/convert/ExcelEnumConvert.java
@@ -0,0 +1,87 @@
+package com.ycl.common.utils.excel.convert;
+
+import cn.hutool.core.annotation.AnnotationUtil;
+import cn.hutool.core.convert.Convert;
+import cn.hutool.core.util.ObjectUtil;
+import com.alibaba.excel.converters.Converter;
+import com.alibaba.excel.enums.CellDataTypeEnum;
+import com.alibaba.excel.metadata.GlobalConfiguration;
+import com.alibaba.excel.metadata.data.ReadCellData;
+import com.alibaba.excel.metadata.data.WriteCellData;
+import com.alibaba.excel.metadata.property.ExcelContentProperty;
+import com.ycl.common.utils.excel.annotation.ExcelEnumFormat;
+import com.ycl.common.utils.reflect.ReflectUtils;
+import lombok.extern.slf4j.Slf4j;
+
+import java.lang.reflect.Field;
+import java.util.HashMap;
+import java.util.Map;
+
+/**
+ * 鏋氫妇鏍煎紡鍖栬浆鎹㈠鐞�
+ *
+ * @author Liang
+ */
+@Slf4j
+public class ExcelEnumConvert implements Converter<Object> {
+
+ @Override
+ public Class<Object> supportJavaTypeKey() {
+ return Object.class;
+ }
+
+ @Override
+ public CellDataTypeEnum supportExcelTypeKey() {
+ return null;
+ }
+
+ @Override
+ public Object convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
+ cellData.checkEmpty();
+ // Excel涓～鍏ョ殑鏄灇涓句腑鎸囧畾鐨勬弿杩�
+ Object textValue = switch (cellData.getType()) {
+ case STRING, DIRECT_STRING, RICH_TEXT_STRING -> cellData.getStringValue();
+ case NUMBER -> cellData.getNumberValue();
+ case BOOLEAN -> cellData.getBooleanValue();
+ default -> throw new IllegalArgumentException("鍗曞厓鏍肩被鍨嬪紓甯�!");
+ };
+ // 濡傛灉鏄┖鍊�
+ if (ObjectUtil.isNull(textValue)) {
+ return null;
+ }
+ Map<Object, String> enumCodeToTextMap = beforeConvert(contentProperty);
+ // 浠嶫ava杈撳嚭鑷矱xcel鏄痗ode杞瑃ext
+ // 鍥犳浠嶦xcel杞琂ava搴旇灏唗ext涓巆ode瀵硅皟
+ Map<Object, Object> enumTextToCodeMap = new HashMap<>();
+ enumCodeToTextMap.forEach((key, value) -> enumTextToCodeMap.put(value, key));
+ // 搴旇浠巘ext -> code涓煡鎵�
+ Object codeValue = enumTextToCodeMap.get(textValue);
+ return Convert.convert(contentProperty.getField().getType(), codeValue);
+ }
+
+ @Override
+ public WriteCellData<String> convertToExcelData(Object object, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
+ if (ObjectUtil.isNull(object)) {
+ return new WriteCellData<>("");
+ }
+ Map<Object, String> enumValueMap = beforeConvert(contentProperty);
+ String value = Convert.toStr(enumValueMap.get(object), "");
+ return new WriteCellData<>(value);
+ }
+
+ private Map<Object, String> beforeConvert(ExcelContentProperty contentProperty) {
+ ExcelEnumFormat anno = getAnnotation(contentProperty.getField());
+ Map<Object, String> enumValueMap = new HashMap<>();
+ Enum<?>[] enumConstants = anno.enumClass().getEnumConstants();
+ for (Enum<?> enumConstant : enumConstants) {
+ Object codeValue = ReflectUtils.invokeGetter(enumConstant, anno.codeField());
+ String textValue = ReflectUtils.invokeGetter(enumConstant, anno.textField());
+ enumValueMap.put(codeValue, textValue);
+ }
+ return enumValueMap;
+ }
+
+ private ExcelEnumFormat getAnnotation(Field field) {
+ return AnnotationUtil.getAnnotation(field, ExcelEnumFormat.class);
+ }
+}
diff --git a/common/src/main/java/com/ycl/common/utils/excel/core/CellMergeStrategy.java b/common/src/main/java/com/ycl/common/utils/excel/core/CellMergeStrategy.java
new file mode 100644
index 0000000..eb38e47
--- /dev/null
+++ b/common/src/main/java/com/ycl/common/utils/excel/core/CellMergeStrategy.java
@@ -0,0 +1,157 @@
+package com.ycl.common.utils.excel.core;
+
+import cn.hutool.core.collection.CollUtil;
+import cn.hutool.core.util.ReflectUtil;
+import cn.hutool.core.util.StrUtil;
+import com.alibaba.excel.annotation.ExcelProperty;
+import com.alibaba.excel.metadata.Head;
+import com.alibaba.excel.write.handler.WorkbookWriteHandler;
+import com.alibaba.excel.write.handler.context.WorkbookWriteHandlerContext;
+import com.alibaba.excel.write.merge.AbstractMergeStrategy;
+import com.ycl.common.utils.excel.annotation.CellMerge;
+import com.ycl.common.utils.excel.utils.ReflectUtils;
+import lombok.AllArgsConstructor;
+import lombok.Data;
+import lombok.SneakyThrows;
+import lombok.extern.slf4j.Slf4j;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.util.CellRangeAddress;
+
+import java.lang.reflect.Field;
+import java.util.*;
+
+/**
+ * 鍒楀�奸噸澶嶅悎骞剁瓥鐣�
+ *
+ * @author Lion Li
+ */
+@Slf4j
+public class CellMergeStrategy extends AbstractMergeStrategy implements WorkbookWriteHandler {
+
+ private final List<CellRangeAddress> cellList;
+ private final boolean hasTitle;
+ private int rowIndex;
+
+ public CellMergeStrategy(List<?> list, boolean hasTitle) {
+ this.hasTitle = hasTitle;
+ // 琛屽悎骞跺紑濮嬩笅鏍�
+ this.rowIndex = hasTitle ? 1 : 0;
+ this.cellList = handle(list, hasTitle);
+ }
+
+ @Override
+ protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
+ //鍗曞厓鏍煎啓鍏ヤ簡,閬嶅巻鍚堝苟鍖哄煙,濡傛灉璇ell鍦ㄥ尯鍩熷唴,浣嗛潪棣栬,鍒欐竻绌�
+ final int rowIndex = cell.getRowIndex();
+ if (CollUtil.isNotEmpty(cellList)){
+ for (CellRangeAddress cellAddresses : cellList) {
+ final int firstRow = cellAddresses.getFirstRow();
+ if (cellAddresses.isInRange(cell) && rowIndex != firstRow){
+ cell.setBlank();
+ }
+ }
+ }
+ }
+
+ @Override
+ public void afterWorkbookDispose(final WorkbookWriteHandlerContext context) {
+ //褰撳墠琛ㄦ牸鍐欏畬鍚庯紝缁熶竴鍐欏叆
+ if (CollUtil.isNotEmpty(cellList)){
+ for (CellRangeAddress item : cellList) {
+ context.getWriteContext().writeSheetHolder().getSheet().addMergedRegion(item);
+ }
+ }
+ }
+
+ @SneakyThrows
+ private List<CellRangeAddress> handle(List<?> list, boolean hasTitle) {
+ List<CellRangeAddress> cellList = new ArrayList<>();
+ if (CollUtil.isEmpty(list)) {
+ return cellList;
+ }
+ Field[] fields = ReflectUtils.getFields(list.get(0).getClass(), field -> !"serialVersionUID".equals(field.getName()));
+
+ // 鏈夋敞瑙ｇ殑瀛楁
+ List<Field> mergeFields = new ArrayList<>();
+ List<Integer> mergeFieldsIndex = new ArrayList<>();
+ for (int i = 0; i < fields.length; i++) {
+ Field field = fields[i];
+ if (field.isAnnotationPresent(CellMerge.class)) {
+ CellMerge cm = field.getAnnotation(CellMerge.class);
+ mergeFields.add(field);
+ mergeFieldsIndex.add(cm.index() == -1 ? i : cm.index());
+ if (hasTitle) {
+ ExcelProperty property = field.getAnnotation(ExcelProperty.class);
+ rowIndex = Math.max(rowIndex, property.value().length);
+ }
+ }
+ }
+
+ Map<Field, RepeatCell> map = new HashMap<>();
+ // 鐢熸垚涓や袱鍚堝苟鍗曞厓鏍�
+ for (int i = 0; i < list.size(); i++) {
+ for (int j = 0; j < mergeFields.size(); j++) {
+ Field field = mergeFields.get(j);
+ Object val = ReflectUtils.invokeGetter(list.get(i), field.getName());
+
+ int colNum = mergeFieldsIndex.get(j);
+ if (!map.containsKey(field)) {
+ map.put(field, new RepeatCell(val, i));
+ } else {
+ RepeatCell repeatCell = map.get(field);
+ Object cellValue = repeatCell.getValue();
+ if (cellValue == null || "".equals(cellValue)) {
+ // 绌哄�艰烦杩囦笉鍚堝苟
+ continue;
+ }
+
+ if (!cellValue.equals(val)) {
+ if ((i - repeatCell.getCurrent() > 1)) {
+ cellList.add(new CellRangeAddress(repeatCell.getCurrent() + rowIndex, i + rowIndex - 1, colNum, colNum));
+ }
+ map.put(field, new RepeatCell(val, i));
+ } else if (i == list.size() - 1) {
+ if (i > repeatCell.getCurrent() && isMerge(list, i, field)) {
+ cellList.add(new CellRangeAddress(repeatCell.getCurrent() + rowIndex, i + rowIndex, colNum, colNum));
+ }
+ } else if (!isMerge(list, i, field)) {
+ if ((i - repeatCell.getCurrent() > 1)) {
+ cellList.add(new CellRangeAddress(repeatCell.getCurrent() + rowIndex, i + rowIndex - 1, colNum, colNum));
+ }
+ map.put(field, new RepeatCell(val, i));
+ }
+ }
+ }
+ }
+ return cellList;
+ }
+
+ private boolean isMerge(List<?> list, int i, Field field) {
+ boolean isMerge = true;
+ CellMerge cm = field.getAnnotation(CellMerge.class);
+ final String[] mergeBy = cm.mergeBy();
+ if (StrUtil.isAllNotBlank(mergeBy)) {
+ //姣斿褰撳墠list(i)鍜宭ist(i - 1)鐨勫悇涓睘鎬у�间竴涓�姣斿 濡傛灉鍏ㄤ负鐪� 鍒欎负鐪�
+ for (String fieldName : mergeBy) {
+ final Object valCurrent = ReflectUtil.getFieldValue(list.get(i), fieldName);
+ final Object valPre = ReflectUtil.getFieldValue(list.get(i - 1), fieldName);
+ if (!Objects.equals(valPre, valCurrent)) {
+ //渚濊禆瀛楁濡傛湁浠讳竴涓嶇瓑鍊�,鍒欐爣璁颁负涓嶅彲鍚堝苟
+ isMerge = false;
+ }
+ }
+ }
+ return isMerge;
+ }
+
+ @Data
+ @AllArgsConstructor
+ static class RepeatCell {
+
+ private Object value;
+
+ private int current;
+
+ }
+}
diff --git a/common/src/main/java/com/ycl/common/utils/excel/core/DefaultExcelListener.java b/common/src/main/java/com/ycl/common/utils/excel/core/DefaultExcelListener.java
new file mode 100644
index 0000000..04390be
--- /dev/null
+++ b/common/src/main/java/com/ycl/common/utils/excel/core/DefaultExcelListener.java
@@ -0,0 +1,104 @@
+package com.ycl.common.utils.excel.core;
+
+import cn.hutool.core.util.StrUtil;
+import com.alibaba.excel.context.AnalysisContext;
+import com.alibaba.excel.event.AnalysisEventListener;
+import com.alibaba.excel.exception.ExcelAnalysisException;
+import com.alibaba.excel.exception.ExcelDataConvertException;
+import com.ycl.common.utils.excel.utils.JsonUtils;
+import com.ycl.common.utils.excel.utils.StreamUtils;
+import com.ycl.common.utils.excel.utils.ValidatorUtils;
+import lombok.NoArgsConstructor;
+import lombok.extern.slf4j.Slf4j;
+
+import javax.validation.ConstraintViolation;
+import javax.validation.ConstraintViolationException;
+import java.util.Map;
+import java.util.Set;
+
+/**
+ * Excel 瀵煎叆鐩戝惉
+ *
+ * @author Yjoioooo
+ * @author Lion Li
+ */
+@Slf4j
+@NoArgsConstructor
+public class DefaultExcelListener<T> extends AnalysisEventListener<T> implements ExcelListener<T> {
+
+ /**
+ * 鏄惁Validator妫�楠岋紝榛樿涓烘槸
+ */
+ private Boolean isValidate = Boolean.TRUE;
+
+ /**
+ * excel 琛ㄥご鏁版嵁
+ */
+ private Map<Integer, String> headMap;
+
+ /**
+ * 瀵煎叆鍥炴墽
+ */
+ private ExcelResult<T> excelResult;
+
+ public DefaultExcelListener(boolean isValidate) {
+ this.excelResult = new DefaultExcelResult<>();
+ this.isValidate = isValidate;
+ }
+
+ /**
+ * 澶勭悊寮傚父
+ *
+ * @param exception ExcelDataConvertException
+ * @param context Excel 涓婁笅鏂�
+ */
+ @Override
+ public void onException(Exception exception, AnalysisContext context) throws Exception {
+ String errMsg = null;
+ if (exception instanceof ExcelDataConvertException excelDataConvertException) {
+ // 濡傛灉鏄煇涓�涓崟鍏冩牸鐨勮浆鎹㈠紓甯� 鑳借幏鍙栧埌鍏蜂綋琛屽彿
+ Integer rowIndex = excelDataConvertException.getRowIndex();
+ Integer columnIndex = excelDataConvertException.getColumnIndex();
+ errMsg = StrUtil.format("绗瑊}琛�-绗瑊}鍒�-琛ㄥご{}: 瑙ｆ瀽寮傚父<br/>",
+ rowIndex + 1, columnIndex + 1, headMap.get(columnIndex));
+ if (log.isDebugEnabled()) {
+ log.error(errMsg);
+ }
+ }
+ if (exception instanceof ConstraintViolationException constraintViolationException) {
+ Set<ConstraintViolation<?>> constraintViolations = constraintViolationException.getConstraintViolations();
+ String constraintViolationsMsg = StreamUtils.join(constraintViolations, ConstraintViolation::getMessage, ", ");
+ errMsg = StrUtil.format("绗瑊}琛屾暟鎹牎楠屽紓甯�: {}", context.readRowHolder().getRowIndex() + 1, constraintViolationsMsg);
+ if (log.isDebugEnabled()) {
+ log.error(errMsg);
+ }
+ }
+ excelResult.getErrorList().add(errMsg);
+ throw new ExcelAnalysisException(errMsg);
+ }
+
+ @Override
+ public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
+ this.headMap = headMap;
+ log.debug("瑙ｆ瀽鍒颁竴鏉¤〃澶存暟鎹�: {}", JsonUtils.toJsonString(headMap));
+ }
+
+ @Override
+ public void invoke(T data, AnalysisContext context) {
+ if (isValidate) {
+ ValidatorUtils.validate(data);
+ }
+ excelResult.getList().add(data);
+ }
+
+ @Override
+ public void doAfterAllAnalysed(AnalysisContext context) {
+ log.debug("鎵�鏈夋暟鎹В鏋愬畬鎴愶紒");
+ }
+
+ @Override
+ public ExcelResult<T> getExcelResult() {
+ return excelResult;
+ }
+
+}
diff --git a/common/src/main/java/com/ycl/common/utils/excel/core/DefaultExcelResult.java b/common/src/main/java/com/ycl/common/utils/excel/core/DefaultExcelResult.java
new file mode 100644
index 0000000..d61b3ab
--- /dev/null
+++ b/common/src/main/java/com/ycl/common/utils/excel/core/DefaultExcelResult.java
@@ -0,0 +1,73 @@
+package com.ycl.common.utils.excel.core;
+
+import cn.hutool.core.util.StrUtil;
+import lombok.Setter;
+
+import java.util.ArrayList;
+import java.util.List;
+
+/**
+ * 榛樿excel杩斿洖瀵硅薄
+ *
+ * @author Yjoioooo
+ * @author Lion Li
+ */
+public class DefaultExcelResult<T> implements ExcelResult<T> {
+
+ /**
+ * 鏁版嵁瀵硅薄list
+ */
+ @Setter
+ private List<T> list;
+
+ /**
+ * 閿欒淇℃伅鍒楄〃
+ */
+ @Setter
+ private List<String> errorList;
+
+ public DefaultExcelResult() {
+ this.list = new ArrayList<>();
+ this.errorList = new ArrayList<>();
+ }
+
+ public DefaultExcelResult(List<T> list, List<String> errorList) {
+ this.list = list;
+ this.errorList = errorList;
+ }
+
+ public DefaultExcelResult(ExcelResult<T> excelResult) {
+ this.list = excelResult.getList();
+ this.errorList = excelResult.getErrorList();
+ }
+
+ @Override
+ public List<T> getList() {
+ return list;
+ }
+
+ @Override
+ public List<String> getErrorList() {
+ return errorList;
+ }
+
+ /**
+ * 鑾峰彇瀵煎叆鍥炴墽
+ *
+ * @return 瀵煎叆鍥炴墽
+ */
+ @Override
+ public String getAnalysis() {
+ int successCount = list.size();
+ int errorCount = errorList.size();
+ if (successCount == 0) {
+ return "璇诲彇澶辫触锛屾湭瑙ｆ瀽鍒版暟鎹�";
+ } else {
+ if (errorCount == 0) {
+ return StrUtil.format("鎭枩鎮紝鍏ㄩ儴璇诲彇鎴愬姛锛佸叡{}鏉�", successCount);
+ } else {
+ return "";
+ }
+ }
+ }
+}
diff --git a/common/src/main/java/com/ycl/common/utils/excel/core/DropDownOptions.java b/common/src/main/java/com/ycl/common/utils/excel/core/DropDownOptions.java
new file mode 100644
index 0000000..aa0d9f7
--- /dev/null
+++ b/common/src/main/java/com/ycl/common/utils/excel/core/DropDownOptions.java
@@ -0,0 +1,149 @@
+package com.ycl.common.utils.excel.core;
+
+import cn.hutool.core.util.StrUtil;
+import com.ycl.common.exception.ServiceException;
+import lombok.AllArgsConstructor;
+import lombok.Data;
+import lombok.NoArgsConstructor;
+
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+import java.util.function.Function;
+import java.util.stream.Collectors;
+
+/**
+ * <h1>Excel涓嬫媺鍙�夐」</h1>
+ * 娉ㄦ剰锛氫负纭繚涓嬫媺妗嗚В鏋愭纭紝浼犲�煎姟蹇呬娇鐢╟reateOptionValue()鍋氫负鍊肩殑鎷兼帴
+ *
+ * @author Emil.Zhang
+ */
+@Data
+@AllArgsConstructor
+@NoArgsConstructor
+@SuppressWarnings("unused")
+public class DropDownOptions {
+ /**
+ * 涓�绾т笅鎷夋墍鍦ㄥ垪index锛屼粠0寮�濮嬬畻
+ */
+ private int index = 0;
+ /**
+ * 浜岀骇涓嬫媺鎵�鍦ㄧ殑index锛屼粠0寮�濮嬬畻锛屼笉鑳戒笌涓�绾х浉鍚�
+ */
+ private int nextIndex = 0;
+ /**
+ * 涓�绾т笅鎷夋墍鍖呭惈鐨勬暟鎹�
+ */
+ private List<String> options = new ArrayList<>();
+ /**
+ * 浜岀骇涓嬫媺鎵�鍖呭惈鐨勬暟鎹甅ap
+ * <p>浠ユ瘡涓�涓竴绾ч�夐」鍊间负Key锛屾瘡涓竴绾ч�夐」瀵瑰簲鐨勪簩绾ф暟鎹负Value</p>
+ */
+ private Map<String, List<String>> nextOptions = new HashMap<>();
+ /**
+ * 鍒嗛殧绗�
+ */
+ private static final String DELIMITER = "_";
+
+ /**
+ * 鍒涘缓鍙湁涓�绾х殑涓嬫媺閫�
+ */
+ public DropDownOptions(int index, List<String> options) {
+ this.index = index;
+ this.options = options;
+ }
+
+ /**
+ * <h2>鍒涘缓姣忎釜閫夐」鍙�夊��</h2>
+ * <p>娉ㄦ剰锛氫笉鑳戒互鏁板瓧锛岀壒娈婄鍙峰紑澶达紝閫夐」涓笉鍙互鍖呭惈浠讳綍杩愮畻绗﹀彿</p>
+ *
+ * @param vars 鍙�夊�煎唴鍖呭惈鐨勫弬鏁�
+ * @return 鍚堣鐨勫彲閫夊��
+ */
+ public static String createOptionValue(Object... vars) {
+ StringBuilder stringBuffer = new StringBuilder();
+ String regex = "^[\\S\\d\\u4e00-\\u9fa5]+$";
+ for (int i = 0; i < vars.length; i++) {
+ String var = StrUtil.trimToEmpty(String.valueOf(vars[i]));
+ if (!var.matches(regex)) {
+ throw new ServiceException("閫夐」鏁版嵁涓嶇鍚堣鍒欙紝浠呭厑璁镐娇鐢ㄤ腑鑻辨枃瀛楃浠ュ強鏁板瓧");
+ }
+ stringBuffer.append(var);
+ if (i < vars.length - 1) {
+ // 鐩磋嚦鏈�鍚庝竴涓墠锛岄兘浠浣滀负鍒囧壊绾�
+ stringBuffer.append(DELIMITER);
+ }
+ }
+ if (stringBuffer.toString().matches("^\\d_*$")) {
+ throw new ServiceException("绂佹浠ユ暟瀛楀紑澶�");
+ }
+ return stringBuffer.toString();
+ }
+
+ /**
+ * 灏嗗鐞嗗悗鍚堢悊鐨勫彲閫夊�艰В鏋愪负鍘熷鐨勫弬鏁�
+ *
+ * @param option 缁忚繃澶勭悊鍚庣殑鍚堢悊鐨勫彲閫夐」
+ * @return 鍘熷鐨勫弬鏁�
+ */
+ public static List<String> analyzeOptionValue(String option) {
+ return StrUtil.split(option, DELIMITER, true, true);
+ }
+
+ /**
+ * 鍒涘缓绾ц仈涓嬫媺閫夐」
+ *
+ * @param parentList 鐖跺疄浣撳彲閫夐」鍘熷鏁版嵁
+ * @param parentIndex 鐖朵笅鎷夐�変綅缃�
+ * @param sonList 瀛愬疄浣撳彲閫夐」鍘熷鏁版嵁
+ * @param sonIndex 瀛愪笅鎷夐�変綅缃�
+ * @param parentHowToGetIdFunction 鐖剁被濡備綍鑾峰彇鍞竴鏍囪瘑
+ * @param sonHowToGetParentIdFunction 瀛愮被濡備綍鑾峰彇鐖剁被鐨勫敮涓�鏍囪瘑
+ * @param howToBuildEveryOption 濡備綍鐢熸垚涓嬫媺閫夊唴瀹�
+ * @return 绾ц仈涓嬫媺閫夐」
+ */
+ public static <T> DropDownOptions buildLinkedOptions(List<T> parentList,
+ int parentIndex,
+ List<T> sonList,
+ int sonIndex,
+ Function<T, Number> parentHowToGetIdFunction,
+ Function<T, Number> sonHowToGetParentIdFunction,
+ Function<T, String> howToBuildEveryOption) {
+ DropDownOptions parentLinkSonOptions = new DropDownOptions();
+ // 鍏堝垱寤虹埗绫荤殑涓嬫媺
+ parentLinkSonOptions.setIndex(parentIndex);
+ parentLinkSonOptions.setOptions(
+ parentList.stream()
+ .map(howToBuildEveryOption)
+ .collect(Collectors.toList())
+ );
+ // 鎻愬彇鐖�-瀛愮骇鑱斾笅鎷�
+ Map<String, List<String>> sonOptions = new HashMap<>();
+ // 鐖剁骇渚濇嵁鑷繁鐨処D鍒嗙粍
+ Map<Number, List<T>> parentGroupByIdMap =
+ parentList.stream().collect(Collectors.groupingBy(parentHowToGetIdFunction));
+ // 閬嶅巻姣忎釜瀛愰泦锛屾彁鍙栧埌Map涓�
+ sonList.forEach(everySon -> {
+ if (parentGroupByIdMap.containsKey(sonHowToGetParentIdFunction.apply(everySon))) {
+ // 鎵惧埌瀵瑰簲鐨勪笂绾�
+ T parentObj = parentGroupByIdMap.get(sonHowToGetParentIdFunction.apply(everySon)).get(0);
+ // 鎻愬彇鍚嶇О鍜孖D浣滀负Key
+ String key = howToBuildEveryOption.apply(parentObj);
+ // Key瀵瑰簲鐨刅alue
+ List<String> thisParentSonOptionList;
+ if (sonOptions.containsKey(key)) {
+ thisParentSonOptionList = sonOptions.get(key);
+ } else {
+ thisParentSonOptionList = new ArrayList<>();
+ sonOptions.put(key, thisParentSonOptionList);
+ }
+ // 寰�Value涓坊鍔犲綋鍓嶅瓙闆嗛�夐」
+ thisParentSonOptionList.add(howToBuildEveryOption.apply(everySon));
+ }
+ });
+ parentLinkSonOptions.setNextIndex(sonIndex);
+ parentLinkSonOptions.setNextOptions(sonOptions);
+ return parentLinkSonOptions;
+ }
+}
diff --git a/common/src/main/java/com/ycl/common/utils/excel/core/ExcelDownHandler.java b/common/src/main/java/com/ycl/common/utils/excel/core/ExcelDownHandler.java
new file mode 100644
index 0000000..47ad684
--- /dev/null
+++ b/common/src/main/java/com/ycl/common/utils/excel/core/ExcelDownHandler.java
@@ -0,0 +1,373 @@
+package com.ycl.common.utils.excel.core;
+
+import cn.hutool.core.collection.CollUtil;
+import cn.hutool.core.util.ArrayUtil;
+import cn.hutool.core.util.EnumUtil;
+import cn.hutool.core.util.ObjectUtil;
+import cn.hutool.core.util.StrUtil;
+import com.alibaba.excel.metadata.FieldCache;
+import com.alibaba.excel.metadata.FieldWrapper;
+import com.alibaba.excel.util.ClassUtils;
+import com.alibaba.excel.write.handler.SheetWriteHandler;
+import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
+import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
+import com.ycl.common.exception.ServiceException;
+import com.ycl.common.utils.excel.annotation.ExcelDictFormat;
+import com.ycl.common.utils.excel.annotation.ExcelEnumFormat;
+import com.ycl.common.utils.excel.service.DictService;
+import com.ycl.common.utils.excel.utils.StreamUtils;
+import com.ycl.common.utils.excel.utils.StringUtils;
+import com.ycl.common.utils.spring.SpringUtils;
+import lombok.extern.slf4j.Slf4j;
+import org.apache.poi.ss.usermodel.*;
+import org.apache.poi.ss.util.CellRangeAddressList;
+import org.apache.poi.ss.util.WorkbookUtil;
+import org.apache.poi.xssf.usermodel.XSSFDataValidation;
+
+import java.lang.reflect.Field;
+import java.util.*;
+
+/**
+ * <h1>Excel琛ㄦ牸涓嬫媺閫夋搷浣�</h1>
+ * 鑰冭檻鍒颁笅鎷夐�夎繃澶氬彲鑳藉鑷碋xcel鎵撳紑缂撴參鐨勯棶棰橈紝鍙牎楠屽墠1000琛�
+ * <p>
+ * 鍗冲彧鏈夊墠1000琛岀殑鏁版嵁鍙互鐢ㄤ笅鎷夋锛岃秴鍑虹殑鑷閫氳繃闄愬埗鏁版嵁閲忕殑褰㈠紡锛岀浜屾杈撳嚭
+ *
+ * @author Emil.Zhang
+ */
+@Slf4j
+public class ExcelDownHandler implements SheetWriteHandler {
+
+ /**
+ * Excel琛ㄦ牸涓殑鍒楀悕鑻辨枃
+ * 浠呬负浜嗚В鏋愬垪鑻辨枃锛岀姝慨鏀�
+ */
+ private static final String EXCEL_COLUMN_NAME = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
+ /**
+ * 鍗曢�夋暟鎹甋heet鍚�
+ */
+ private static final String OPTIONS_SHEET_NAME = "options";
+ /**
+ * 鑱斿姩閫夋嫨鏁版嵁Sheet鍚嶇殑澶�
+ */
+ private static final String LINKED_OPTIONS_SHEET_NAME = "linkedOptions";
+ /**
+ * 涓嬫媺鍙�夐」
+ */
+ private final List<DropDownOptions> dropDownOptions;
+ /**
+ * 褰撳墠鍗曢�夎繘搴�
+ */
+ private int currentOptionsColumnIndex;
+ /**
+ * 褰撳墠鑱斿姩閫夋嫨杩涘害
+ */
+ private int currentLinkedOptionsSheetIndex;
+ private final DictService dictService;
+
+ public ExcelDownHandler(List<DropDownOptions> options) {
+ this.dropDownOptions = options;
+ this.currentOptionsColumnIndex = 0;
+ this.currentLinkedOptionsSheetIndex = 0;
+ this.dictService = SpringUtils.getBean(DictService.class);
+ }
+
+ /**
+ * <h2>寮�濮嬪垱寤轰笅鎷夋暟鎹�</h2>
+ * 1.閫氳繃瑙ｆ瀽浼犲叆鐨凘ExcelProperty鍚岀骇鏄惁鏍囨敞鏈堾DropDown閫夐」
+ * 濡傛灉鏈変笖璁剧疆浜唙alue鍊硷紝鍒欏皢鍏剁洿鎺ョ疆涓轰笅鎷夊彲閫夐」
+ * <p>
+ * 2.鎴栬�呭湪璋冪敤ExcelUtil鏃舵寚瀹氫簡鍙�夐」锛屽皢渚濇嵁浼犲叆鐨勫彲閫夐」鍋氫笅鎷�
+ * <p>
+ * 3.浜岃�呭苟瀛橈紝娉ㄦ剰璋冪敤鏂瑰紡
+ */
+ @Override
+ public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
+ Sheet sheet = writeSheetHolder.getSheet();
+ // 寮�濮嬭缃笅鎷夋 HSSFWorkbook
+ DataValidationHelper helper = sheet.getDataValidationHelper();
+ Workbook workbook = writeWorkbookHolder.getWorkbook();
+ FieldCache fieldCache = ClassUtils.declaredFields(writeWorkbookHolder.getClazz(), writeWorkbookHolder);
+ for (Map.Entry<Integer, FieldWrapper> entry : fieldCache.getSortedFieldMap().entrySet()) {
+ Integer index = entry.getKey();
+ FieldWrapper wrapper = entry.getValue();
+ Field field = wrapper.getField();
+ // 寰幆瀹炰綋涓殑姣忎釜灞炴��
+ // 鍙�夌殑涓嬫媺鍊�
+ List<String> options = new ArrayList<>();
+ if (field.isAnnotationPresent(ExcelDictFormat.class)) {
+ // 濡傛灉鎸囧畾浜咢ExcelDictFormat锛屽垯浣跨敤瀛楀吀鐨勯�昏緫
+ ExcelDictFormat format = field.getDeclaredAnnotation(ExcelDictFormat.class);
+ String dictType = format.dictType();
+ String converterExp = format.readConverterExp();
+ if (StringUtils.isNotBlank(dictType)) {
+ // 濡傛灉浼犻�掍簡瀛楀吀鍚嶏紝鍒欎緷鎹瓧鍏稿缓绔嬩笅鎷�
+ Collection<String> values = Optional.ofNullable(dictService.getAllDictByDictType(dictType))
+ .orElseThrow(() -> new ServiceException(String.format("瀛楀吀 %s 涓嶅瓨鍦�", dictType)))
+ .values();
+ options = new ArrayList<>(values);
+ } else if (StringUtils.isNotBlank(converterExp)) {
+ // 濡傛灉鎸囧畾浜嗙‘鍒囩殑鍊硷紝鍒欑洿鎺ヨВ鏋愮‘鍒囩殑鍊�
+ List<String> strList = StringUtils.splitList(converterExp, format.separator());
+ options = StreamUtils.toList(strList, s -> StringUtils.split(s, "=")[1]);
+ }
+ } else if (field.isAnnotationPresent(ExcelEnumFormat.class)) {
+ // 鍚﹀垯濡傛灉鎸囧畾浜咢ExcelEnumFormat锛屽垯浣跨敤鏋氫妇鐨勯�昏緫
+ ExcelEnumFormat format = field.getDeclaredAnnotation(ExcelEnumFormat.class);
+ List<Object> values = EnumUtil.getFieldValues(format.enumClass(), format.textField());
+ options = StreamUtils.toList(values, String::valueOf);
+ }
+ if (ObjectUtil.isNotEmpty(options)) {
+ // 浠呭綋涓嬫媺鍙�夐」涓嶄负绌烘椂鎵ц
+ if (options.size() > 20) {
+ // 杩欓噷闄愬埗濡傛灉鍙�夐」澶т簬20锛屽垯浣跨敤棰濆琛ㄥ舰寮�
+ dropDownWithSheet(helper, workbook, sheet, index, options);
+ } else {
+ // 鍚﹀垯浣跨敤鍥哄畾鍊煎舰寮�
+ dropDownWithSimple(helper, sheet, index, options);
+ }
+ }
+ }
+ if (CollUtil.isEmpty(dropDownOptions)) {
+ return;
+ }
+ dropDownOptions.forEach(everyOptions -> {
+ // 濡傛灉浼犻�掍簡涓嬫媺妗嗛�夋嫨鍣ㄥ弬鏁�
+ if (!everyOptions.getNextOptions().isEmpty()) {
+ // 褰撲簩绾ч�夐」涓嶄负绌烘椂锛屼娇鐢ㄩ澶栧叧鑱旇〃鐨勫舰寮�
+ dropDownLinkedOptions(helper, workbook, sheet, everyOptions);
+ } else if (everyOptions.getOptions().size() > 10) {
+ // 褰撲竴绾ч�夐」鍙傛暟涓暟澶т簬10锛屼娇鐢ㄩ澶栬〃鐨勫舰寮�
+ dropDownWithSheet(helper, workbook, sheet, everyOptions.getIndex(), everyOptions.getOptions());
+ } else if (everyOptions.getOptions().size() != 0) {
+ // 褰撲竴绾ч�夐」涓暟涓嶄负绌猴紝浣跨敤榛樿褰㈠紡
+ dropDownWithSimple(helper, sheet, everyOptions.getIndex(), everyOptions.getOptions());
+ }
+ });
+ }
+
+ /**
+ * <h2>绠�鍗曚笅鎷夋</h2>
+ * 鐩存帴灏嗗彲閫夐」鎷兼帴涓烘寚瀹氬垪鐨勬暟鎹牎楠屽��
+ *
+ * @param celIndex 鍒梚ndex
+ * @param value 涓嬫媺閫夊彲閫夊��
+ */
+ private void dropDownWithSimple(DataValidationHelper helper, Sheet sheet, Integer celIndex, List<String> value) {
+ if (ObjectUtil.isEmpty(value)) {
+ return;
+ }
+ this.markOptionsToSheet(helper, sheet, celIndex, helper.createExplicitListConstraint(ArrayUtil.toArray(value, String.class)));
+ }
+
+ /**
+ * <h2>棰濆琛ㄦ牸褰㈠紡鐨勭骇鑱斾笅鎷夋</h2>
+ *
+ * @param options 棰濆琛ㄦ牸褰㈠紡瀛樺偍鐨勪笅鎷夊彲閫夐」
+ */
+ private void dropDownLinkedOptions(DataValidationHelper helper, Workbook workbook, Sheet sheet, DropDownOptions options) {
+ String linkedOptionsSheetName = String.format("%s_%d", LINKED_OPTIONS_SHEET_NAME, currentLinkedOptionsSheetIndex);
+ // 鍒涘缓鑱斿姩涓嬫媺鏁版嵁琛�
+ Sheet linkedOptionsDataSheet = workbook.createSheet(WorkbookUtil.createSafeSheetName(linkedOptionsSheetName));
+ // 灏嗕笅鎷夎〃闅愯棌
+ workbook.setSheetHidden(workbook.getSheetIndex(linkedOptionsDataSheet), true);
+ // 瀹屽杽妯悜鐨勪竴绾ч�夐」鏁版嵁琛�
+ List<String> firstOptions = options.getOptions();
+ Map<String, List<String>> secoundOptionsMap = options.getNextOptions();
+
+ // 鍒涘缓鍚嶇О绠＄悊鍣�
+ Name name = workbook.createName();
+ // 璁剧疆鍚嶇О绠＄悊鍣ㄧ殑鍒悕
+ name.setNameName(linkedOptionsSheetName);
+ // 浠ユí鍚戠涓�琛屽垱寤轰竴绾т笅鎷夋嫾鎺ュ紩鐢ㄤ綅缃�
+ String firstOptionsFunction = String.format("%s!$%s$1:$%s$1",
+ linkedOptionsSheetName,
+ getExcelColumnName(0),
+ getExcelColumnName(firstOptions.size())
+ );
+ // 璁剧疆鍚嶇О绠＄悊鍣ㄧ殑寮曠敤浣嶇疆
+ name.setRefersToFormula(firstOptionsFunction);
+ // 璁剧疆鏁版嵁鏍￠獙涓哄簭鍒楁ā寮忥紝寮曠敤鐨勬槸鍚嶇О绠＄悊鍣ㄤ腑鐨勫埆鍚�
+ this.markOptionsToSheet(helper, sheet, options.getIndex(), helper.createFormulaListConstraint(linkedOptionsSheetName));
+
+ for (int columIndex = 0; columIndex < firstOptions.size(); columIndex++) {
+ // 鍏堟彁鍙栦富琛ㄤ腑涓�绾т笅鎷夌殑鍒楀悕
+ String firstOptionsColumnName = getExcelColumnName(columIndex);
+ // 涓�娆″惊鐜槸姣忎竴涓竴绾ч�夐」
+ int finalI = columIndex;
+ // 鏈寰幆鐨勪竴绾ч�夐」鍊�
+ String thisFirstOptionsValue = firstOptions.get(columIndex);
+ // 鍒涘缓绗竴琛岀殑鏁版嵁
+ Optional.ofNullable(linkedOptionsDataSheet.getRow(0))
+ // 濡傛灉涓嶅瓨鍦ㄥ垯鍒涘缓绗竴琛�
+ .orElseGet(() -> linkedOptionsDataSheet.createRow(finalI))
+ // 绗竴琛屽綋鍓嶅垪
+ .createCell(columIndex)
+ // 璁剧疆鍊间负褰撳墠涓�绾ч�夐」鍊�
+ .setCellValue(thisFirstOptionsValue);
+
+ // 绗簩琛屽紑濮嬶紝璁剧疆绗簩绾у埆閫夐」鍙傛暟
+ List<String> secondOptions = secoundOptionsMap.get(thisFirstOptionsValue);
+ if (CollUtil.isEmpty(secondOptions)) {
+ // 蹇呴』淇濊瘉鑷冲皯鏈変竴涓叧鑱旈�夐」锛屽惁鍒欏皢瀵艰嚧Excel瑙ｆ瀽閿欒
+ secondOptions = Collections.singletonList("鏆傛棤_0");
+ }
+
+ // 浠ヨ涓�绾ч�夐」鍊煎垱寤哄瓙鍚嶇О绠＄悊鍣�
+ Name sonName = workbook.createName();
+ // 璁剧疆鍚嶇О绠＄悊鍣ㄧ殑鍒悕
+ sonName.setNameName(thisFirstOptionsValue);
+ // 浠ョ浜岃璇ュ垪鏁版嵁鎷兼帴寮曠敤浣嶇疆
+ String sonFunction = String.format("%s!$%s$2:$%s$%d",
+ linkedOptionsSheetName,
+ firstOptionsColumnName,
+ firstOptionsColumnName,
+ secondOptions.size() + 1
+ );
+ // 璁剧疆鍚嶇О绠＄悊鍣ㄧ殑寮曠敤浣嶇疆
+ sonName.setRefersToFormula(sonFunction);
+ // 鏁版嵁楠岃瘉涓哄簭鍒楁ā寮忥紝寮曠敤鍒版瘡涓�涓富琛ㄤ腑鐨勪簩绾ч�夐」浣嶇疆
+ // 鍒涘缓瀛愰」鐨勫悕绉扮鐞嗗櫒锛屽彧鏄负浜嗕娇寰桬xcel鍙互璇嗗埆鍒版暟鎹�
+ String mainSheetFirstOptionsColumnName = getExcelColumnName(options.getIndex());
+ for (int i = 0; i < 100; i++) {
+ // 浠ヤ竴绾ч�夐」瀵瑰簲鐨勪富浣撴墍鍦ㄤ綅缃垱寤轰簩绾т笅鎷�
+ String secondOptionsFunction = String.format("=INDIRECT(%s%d)", mainSheetFirstOptionsColumnName, i + 1);
+ // 浜岀骇鍙兘涓昏〃姣忎竴琛岀殑姣忎竴鍒楁坊鍔犱簩绾ф牎楠�
+ markLinkedOptionsToSheet(helper, sheet, i, options.getNextIndex(), helper.createFormulaListConstraint(secondOptionsFunction));
+ }
+
+ for (int rowIndex = 0; rowIndex < secondOptions.size(); rowIndex++) {
+ // 浠庣浜岃寮�濮嬪～鍏呬簩绾ч�夐」
+ int finalRowIndex = rowIndex + 1;
+ int finalColumIndex = columIndex;
+
+ Row row = Optional.ofNullable(linkedOptionsDataSheet.getRow(finalRowIndex))
+ // 娌℃湁鍒欏垱寤�
+ .orElseGet(() -> linkedOptionsDataSheet.createRow(finalRowIndex));
+ Optional
+ // 鍦ㄦ湰绾т竴绾ч�夐」鎵�鍦ㄧ殑鍒�
+ .ofNullable(row.getCell(finalColumIndex))
+ // 涓嶅瓨鍦ㄥ垯鍒涘缓
+ .orElseGet(() -> row.createCell(finalColumIndex))
+ // 璁剧疆浜岀骇閫夐」鍊�
+ .setCellValue(secondOptions.get(rowIndex));
+ }
+ }
+
+ currentLinkedOptionsSheetIndex++;
+ }
+
+ /**
+ * <h2>棰濆琛ㄦ牸褰㈠紡鐨勬櫘閫氫笅鎷夋</h2>
+ * 鐢变簬涓嬫媺妗嗗彲閫夊�兼暟閲忚繃澶氾紝涓烘彁鍗嘐xcel鎵撳紑鏁堢巼锛屼娇鐢ㄩ澶栬〃鏍煎舰寮忓仛涓嬫媺
+ *
+ * @param celIndex 涓嬫媺閫�
+ * @param value 涓嬫媺閫夊彲閫夊��
+ */
+ private void dropDownWithSheet(DataValidationHelper helper, Workbook workbook, Sheet sheet, Integer celIndex, List<String> value) {
+ // 鍒涘缓涓嬫媺鏁版嵁琛�
+ Sheet simpleDataSheet = Optional.ofNullable(workbook.getSheet(WorkbookUtil.createSafeSheetName(OPTIONS_SHEET_NAME)))
+ .orElseGet(() -> workbook.createSheet(WorkbookUtil.createSafeSheetName(OPTIONS_SHEET_NAME)));
+ // 灏嗕笅鎷夎〃闅愯棌
+ workbook.setSheetHidden(workbook.getSheetIndex(simpleDataSheet), true);
+ // 瀹屽杽绾靛悜鐨勪竴绾ч�夐」鏁版嵁琛�
+ for (int i = 0; i < value.size(); i++) {
+ int finalI = i;
+ // 鑾峰彇姣忎竴閫夐」琛岋紝濡傛灉娌℃湁鍒欏垱寤�
+ Row row = Optional.ofNullable(simpleDataSheet.getRow(i))
+ .orElseGet(() -> simpleDataSheet.createRow(finalI));
+ // 鑾峰彇鏈骇閫夐」瀵瑰簲鐨勯�夐」鍒楋紝濡傛灉娌℃湁鍒欏垱寤�
+ Cell cell = Optional.ofNullable(row.getCell(currentOptionsColumnIndex))
+ .orElseGet(() -> row.createCell(currentOptionsColumnIndex));
+ // 璁剧疆鍊�
+ cell.setCellValue(value.get(i));
+ }
+
+ // 鍒涘缓鍚嶇О绠＄悊鍣�
+ Name name = workbook.createName();
+ // 璁剧疆鍚嶇О绠＄悊鍣ㄧ殑鍒悕
+ String nameName = String.format("%s_%d", OPTIONS_SHEET_NAME, celIndex);
+ name.setNameName(nameName);
+ // 浠ョ旱鍚戠涓�鍒楀垱寤轰竴绾т笅鎷夋嫾鎺ュ紩鐢ㄤ綅缃�
+ String function = String.format("%s!$%s$1:$%s$%d",
+ OPTIONS_SHEET_NAME,
+ getExcelColumnName(currentOptionsColumnIndex),
+ getExcelColumnName(currentOptionsColumnIndex),
+ value.size());
+ // 璁剧疆鍚嶇О绠＄悊鍣ㄧ殑寮曠敤浣嶇疆
+ name.setRefersToFormula(function);
+ // 璁剧疆鏁版嵁鏍￠獙涓哄簭鍒楁ā寮忥紝寮曠敤鐨勬槸鍚嶇О绠＄悊鍣ㄤ腑鐨勫埆鍚�
+ this.markOptionsToSheet(helper, sheet, celIndex, helper.createFormulaListConstraint(nameName));
+ currentOptionsColumnIndex++;
+ }
+
+ /**
+ * 鎸傝浇涓嬫媺鐨勫垪锛屼粎闄愪竴绾ч�夐」
+ */
+ private void markOptionsToSheet(DataValidationHelper helper, Sheet sheet, Integer celIndex,
+ DataValidationConstraint constraint) {
+ // 璁剧疆鏁版嵁鏈夋晥鎬у姞杞藉湪鍝釜鍗曞厓鏍间笂,鍥涗釜鍙傛暟鍒嗗埆鏄細璧峰琛屻�佺粓姝㈣銆佽捣濮嬪垪銆佺粓姝㈠垪
+ CellRangeAddressList addressList = new CellRangeAddressList(1, 1000, celIndex, celIndex);
+ markDataValidationToSheet(helper, sheet, constraint, addressList);
+ }
+
+ /**
+ * 鎸傝浇涓嬫媺鐨勫垪锛屼粎闄愪簩绾ч�夐」
+ */
+ private void markLinkedOptionsToSheet(DataValidationHelper helper, Sheet sheet, Integer rowIndex,
+ Integer celIndex, DataValidationConstraint constraint) {
+ // 璁剧疆鏁版嵁鏈夋晥鎬у姞杞藉湪鍝釜鍗曞厓鏍间笂,鍥涗釜鍙傛暟鍒嗗埆鏄細璧峰琛屻�佺粓姝㈣銆佽捣濮嬪垪銆佺粓姝㈠垪
+ CellRangeAddressList addressList = new CellRangeAddressList(rowIndex, rowIndex, celIndex, celIndex);
+ markDataValidationToSheet(helper, sheet, constraint, addressList);
+ }
+
+ /**
+ * 搴旂敤鏁版嵁鏍￠獙
+ */
+ private void markDataValidationToSheet(DataValidationHelper helper, Sheet sheet,
+ DataValidationConstraint constraint, CellRangeAddressList addressList) {
+ // 鏁版嵁鏈夋晥鎬у璞�
+ DataValidation dataValidation = helper.createValidation(constraint, addressList);
+ // 澶勭悊Excel鍏煎鎬ч棶棰�
+ if (dataValidation instanceof XSSFDataValidation) {
+ //鏁版嵁鏍￠獙
+ dataValidation.setSuppressDropDownArrow(true);
+ //閿欒鎻愮ず
+ dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
+ dataValidation.createErrorBox("鎻愮ず", "姝ゅ�间笌鍗曞厓鏍煎畾涔夋暟鎹笉涓�鑷�");
+ dataValidation.setShowErrorBox(true);
+ //閫夊畾鎻愮ず
+ dataValidation.createPromptBox("濉啓璇存槑锛�", "濉啓鍐呭鍙兘涓轰笅鎷変腑鏁版嵁锛屽叾浠栨暟鎹皢瀵艰嚧瀵煎叆澶辫触");
+ dataValidation.setShowPromptBox(true);
+ sheet.addValidationData(dataValidation);
+ } else {
+ dataValidation.setSuppressDropDownArrow(false);
+ }
+ sheet.addValidationData(dataValidation);
+ }
+
+ /**
+ * <h2>渚濇嵁鍒梚ndex鑾峰彇鍒楀悕鑻辨枃</h2>
+ * 渚濇嵁鍒梚ndex杞崲涓篍xcel涓殑鍒楀悕鑻辨枃
+ * <p>渚嬪绗�1鍒楋紝index涓�0锛岃В鏋愬嚭鏉ヤ负A鍒�</p>
+ * 绗�27鍒楋紝index涓�26锛岃В鏋愪负AA鍒�
+ * <p>绗�28鍒楋紝index涓�27锛岃В鏋愪负AB鍒�</p>
+ *
+ * @param columnIndex 鍒梚ndex
+ * @return 鍒梚ndex鎵�鍦ㄥ緱鑻辨枃鍚�
+ */
+ private String getExcelColumnName(int columnIndex) {
+ // 26涓�寰幆鐨勬鏁�
+ int columnCircleCount = columnIndex / 26;
+ // 26涓�寰幆鍐呯殑浣嶇疆
+ int thisCircleColumnIndex = columnIndex % 26;
+ // 26涓�寰幆鐨勬鏁板ぇ浜�0锛屽垯瑙嗕负鏍忓悕鑷冲皯涓や綅
+ String columnPrefix = columnCircleCount == 0
+ ? StrUtil.EMPTY
+ : StrUtil.subWithLength(EXCEL_COLUMN_NAME, columnCircleCount - 1, 1);
+ // 浠�26涓�寰幆鍐呭彇瀵瑰簲鐨勬爮浣嶅悕
+ String columnNext = StrUtil.subWithLength(EXCEL_COLUMN_NAME, thisCircleColumnIndex, 1);
+ // 灏嗕簩鑰呮嫾鎺ュ嵆涓烘渶缁堢殑鏍忎綅鍚�
+ return columnPrefix + columnNext;
+ }
+}
diff --git a/common/src/main/java/com/ycl/common/utils/excel/core/ExcelListener.java b/common/src/main/java/com/ycl/common/utils/excel/core/ExcelListener.java
new file mode 100644
index 0000000..76ba16c
--- /dev/null
+++ b/common/src/main/java/com/ycl/common/utils/excel/core/ExcelListener.java
@@ -0,0 +1,14 @@
+package com.ycl.common.utils.excel.core;
+
+import com.alibaba.excel.read.listener.ReadListener;
+
+/**
+ * Excel 瀵煎叆鐩戝惉
+ *
+ * @author Lion Li
+ */
+public interface ExcelListener<T> extends ReadListener<T> {
+
+ ExcelResult<T> getExcelResult();
+
+}
diff --git a/common/src/main/java/com/ycl/common/utils/excel/core/ExcelResult.java b/common/src/main/java/com/ycl/common/utils/excel/core/ExcelResult.java
new file mode 100644
index 0000000..36d9c8e
--- /dev/null
+++ b/common/src/main/java/com/ycl/common/utils/excel/core/ExcelResult.java
@@ -0,0 +1,26 @@
+package com.ycl.common.utils.excel.core;
+
+import java.util.List;
+
+/**
+ * excel杩斿洖瀵硅薄
+ *
+ * @author Lion Li
+ */
+public interface ExcelResult<T> {
+
+ /**
+ * 瀵硅薄鍒楄〃
+ */
+ List<T> getList();
+
+ /**
+ * 閿欒鍒楄〃
+ */
+ List<String> getErrorList();
+
+ /**
+ * 瀵煎叆鍥炴墽
+ */
+ String getAnalysis();
+}
diff --git a/common/src/main/java/com/ycl/common/utils/excel/service/DictService.java b/common/src/main/java/com/ycl/common/utils/excel/service/DictService.java
new file mode 100644
index 0000000..801189e
--- /dev/null
+++ b/common/src/main/java/com/ycl/common/utils/excel/service/DictService.java
@@ -0,0 +1,67 @@
+package com.ycl.common.utils.excel.service;
+
+import java.util.Map;
+
+/**
+ * 閫氱敤 瀛楀吀鏈嶅姟
+ *
+ * @author Lion Li
+ */
+public interface DictService {
+
+ /**
+ * 鍒嗛殧绗�
+ */
+ String SEPARATOR = ",";
+
+ /**
+ * 鏍规嵁瀛楀吀绫诲瀷鍜屽瓧鍏稿�艰幏鍙栧瓧鍏告爣绛�
+ *
+ * @param dictType 瀛楀吀绫诲瀷
+ * @param dictValue 瀛楀吀鍊�
+ * @return 瀛楀吀鏍囩
+ */
+ default String getDictLabel(String dictType, String dictValue) {
+ return getDictLabel(dictType, dictValue, SEPARATOR);
+ }
+
+ /**
+ * 鏍规嵁瀛楀吀绫诲瀷鍜屽瓧鍏告爣绛捐幏鍙栧瓧鍏稿��
+ *
+ * @param dictType 瀛楀吀绫诲瀷
+ * @param dictLabel 瀛楀吀鏍囩
+ * @return 瀛楀吀鍊�
+ */
+ default String getDictValue(String dictType, String dictLabel) {
+ return getDictValue(dictType, dictLabel, SEPARATOR);
+ }
+
+ /**
+ * 鏍规嵁瀛楀吀绫诲瀷鍜屽瓧鍏稿�艰幏鍙栧瓧鍏告爣绛�
+ *
+ * @param dictType 瀛楀吀绫诲瀷
+ * @param dictValue 瀛楀吀鍊�
+ * @param separator 鍒嗛殧绗�
+ * @return 瀛楀吀鏍囩
+ */
+ String getDictLabel(String dictType, String dictValue, String separator);
+
+ /**
+ * 鏍规嵁瀛楀吀绫诲瀷鍜屽瓧鍏告爣绛捐幏鍙栧瓧鍏稿��
+ *
+ * @param dictType 瀛楀吀绫诲瀷
+ * @param dictLabel 瀛楀吀鏍囩
+ * @param separator 鍒嗛殧绗�
+ * @return 瀛楀吀鍊�
+ */
+ String getDictValue(String dictType, String dictLabel, String separator);
+
+ /**
+ * 鑾峰彇瀛楀吀涓嬫墍鏈夌殑瀛楀吀鍊间笌鏍囩
+ *
+ * @param dictType 瀛楀吀绫诲瀷
+ * @return dictValue涓簁ey锛宒ictLabel涓哄�肩粍鎴愮殑Map
+ */
+ Map<String, String> getAllDictByDictType(String dictType);
+
+}
diff --git a/common/src/main/java/com/ycl/common/utils/excel/utils/JsonUtils.java b/common/src/main/java/com/ycl/common/utils/excel/utils/JsonUtils.java
new file mode 100644
index 0000000..3303c55
--- /dev/null
+++ b/common/src/main/java/com/ycl/common/utils/excel/utils/JsonUtils.java
@@ -0,0 +1,169 @@
+package com.ycl.common.utils.excel.utils;
+
+import cn.hutool.core.lang.Dict;
+import cn.hutool.core.util.ArrayUtil;
+import cn.hutool.core.util.ObjectUtil;
+import com.fasterxml.jackson.core.JsonProcessingException;
+import com.fasterxml.jackson.core.type.TypeReference;
+import com.fasterxml.jackson.databind.ObjectMapper;
+import com.fasterxml.jackson.databind.exc.MismatchedInputException;
+import com.ycl.common.utils.spring.SpringUtils;
+import lombok.AccessLevel;
+import lombok.NoArgsConstructor;
+
+import java.io.IOException;
+import java.util.ArrayList;
+import java.util.List;
+
+/**
+ * JSON 宸ュ叿绫�
+ *
+ * @author 鑺嬮亾婧愮爜
+ */
+@NoArgsConstructor(access = AccessLevel.PRIVATE)
+public class JsonUtils {
+
+ private static final ObjectMapper OBJECT_MAPPER = SpringUtils.getBean(ObjectMapper.class);
+
+ public static ObjectMapper getObjectMapper() {
+ return OBJECT_MAPPER;
+ }
+
+ /**
+ * 灏嗗璞¤浆鎹负JSON鏍煎紡鐨勫瓧绗︿覆
+ *
+ * @param object 瑕佽浆鎹㈢殑瀵硅薄
+ * @return JSON鏍煎紡鐨勫瓧绗︿覆锛屽鏋滃璞′负null锛屽垯杩斿洖null
+ * @throws RuntimeException 濡傛灉杞崲杩囩▼涓彂鐢烰SON澶勭悊寮傚父锛屽垯鎶涘嚭杩愯鏃跺紓甯�
+ */
+ public static String toJsonString(Object object) {
+ if (ObjectUtil.isNull(object)) {
+ return null;
+ }
+ try {
+ return OBJECT_MAPPER.writeValueAsString(object);
+ } catch (JsonProcessingException e) {
+ throw new RuntimeException(e);
+ }
+ }
+
+ /**
+ * 灏咼SON鏍煎紡鐨勫瓧绗︿覆杞崲涓烘寚瀹氱被鍨嬬殑瀵硅薄
+ *
+ * @param text JSON鏍煎紡鐨勫瓧绗︿覆
+ * @param clazz 瑕佽浆鎹㈢殑鐩爣瀵硅薄绫诲瀷
+ * @param <T> 鐩爣瀵硅薄鐨勬硾鍨嬬被鍨�
+ * @return 杞崲鍚庣殑瀵硅薄锛屽鏋滃瓧绗︿覆涓虹┖鍒欒繑鍥瀗ull
+ * @throws RuntimeException 濡傛灉杞崲杩囩▼涓彂鐢烮O寮傚父锛屽垯鎶涘嚭杩愯鏃跺紓甯�
+ */
+ public static <T> T parseObject(String text, Class<T> clazz) {
+ if (StringUtils.isEmpty(text)) {
+ return null;
+ }
+ try {
+ return OBJECT_MAPPER.readValue(text, clazz);
+ } catch (IOException e) {
+ throw new RuntimeException(e);
+ }
+ }
+
+ /**
+ * 灏嗗瓧鑺傛暟缁勮浆鎹负鎸囧畾绫诲瀷鐨勫璞�
+ *
+ * @param bytes 瀛楄妭鏁扮粍
+ * @param clazz 瑕佽浆鎹㈢殑鐩爣瀵硅薄绫诲瀷
+ * @param <T> 鐩爣瀵硅薄鐨勬硾鍨嬬被鍨�
+ * @return 杞崲鍚庣殑瀵硅薄锛屽鏋滃瓧鑺傛暟缁勪负绌哄垯杩斿洖null
+ * @throws RuntimeException 濡傛灉杞崲杩囩▼涓彂鐢烮O寮傚父锛屽垯鎶涘嚭杩愯鏃跺紓甯�
+ */
+ public static <T> T parseObject(byte[] bytes, Class<T> clazz) {
+ if (ArrayUtil.isEmpty(bytes)) {
+ return null;
+ }
+ try {
+ return OBJECT_MAPPER.readValue(bytes, clazz);
+ } catch (IOException e) {
+ throw new RuntimeException(e);
+ }
+ }
+
+ /**
+ * 灏咼SON鏍煎紡鐨勫瓧绗︿覆杞崲涓烘寚瀹氱被鍨嬬殑瀵硅薄锛屾敮鎸佸鏉傜被鍨�
+ *
+ * @param text JSON鏍煎紡鐨勫瓧绗︿覆
+ * @param typeReference 鎸囧畾绫诲瀷鐨凾ypeReference瀵硅薄
+ * @param <T> 鐩爣瀵硅薄鐨勬硾鍨嬬被鍨�
+ * @return 杞崲鍚庣殑瀵硅薄锛屽鏋滃瓧绗︿覆涓虹┖鍒欒繑鍥瀗ull
+ * @throws RuntimeException 濡傛灉杞崲杩囩▼涓彂鐢烮O寮傚父锛屽垯鎶涘嚭杩愯鏃跺紓甯�
+ */
+ public static <T> T parseObject(String text, TypeReference<T> typeReference) {
+ if (StringUtils.isBlank(text)) {
+ return null;
+ }
+ try {
+ return OBJECT_MAPPER.readValue(text, typeReference);
+ } catch (IOException e) {
+ throw new RuntimeException(e);
+ }
+ }
+
+ /**
+ * 灏咼SON鏍煎紡鐨勫瓧绗︿覆杞崲涓篋ict瀵硅薄
+ *
+ * @param text JSON鏍煎紡鐨勫瓧绗︿覆
+ * @return 杞崲鍚庣殑Dict瀵硅薄锛屽鏋滃瓧绗︿覆涓虹┖鎴栬�呬笉鏄疛SON鏍煎紡鍒欒繑鍥瀗ull
+ * @throws RuntimeException 濡傛灉杞崲杩囩▼涓彂鐢烮O寮傚父锛屽垯鎶涘嚭杩愯鏃跺紓甯�
+ */
+ public static Dict parseMap(String text) {
+ if (StringUtils.isBlank(text)) {
+ return null;
+ }
+ try {
+ return OBJECT_MAPPER.readValue(text, OBJECT_MAPPER.getTypeFactory().constructType(Dict.class));
+ } catch (MismatchedInputException e) {
+ // 绫诲瀷涓嶅尮閰嶈鏄庝笉鏄痡son
+ return null;
+ } catch (IOException e) {
+ throw new RuntimeException(e);
+ }
+ }
+
+ /**
+ * 灏咼SON鏍煎紡鐨勫瓧绗︿覆杞崲涓篋ict瀵硅薄鐨勫垪琛�
+ *
+ * @param text JSON鏍煎紡鐨勫瓧绗︿覆
+ * @return 杞崲鍚庣殑Dict瀵硅薄鐨勫垪琛紝濡傛灉瀛楃涓蹭负绌哄垯杩斿洖null
+ * @throws RuntimeException 濡傛灉杞崲杩囩▼涓彂鐢烮O寮傚父锛屽垯鎶涘嚭杩愯鏃跺紓甯�
+ */
+ public static List<Dict> parseArrayMap(String text) {
+ if (StringUtils.isBlank(text)) {
+ return null;
+ }
+ try {
+ return OBJECT_MAPPER.readValue(text, OBJECT_MAPPER.getTypeFactory().constructCollectionType(List.class, Dict.class));
+ } catch (IOException e) {
+ throw new RuntimeException(e);
+ }
+ }
+
+ /**
+ * 灏咼SON鏍煎紡鐨勫瓧绗︿覆杞崲涓烘寚瀹氱被鍨嬪璞＄殑鍒楄〃
+ *
+ * @param text JSON鏍煎紡鐨勫瓧绗︿覆
+ * @param clazz 瑕佽浆鎹㈢殑鐩爣瀵硅薄绫诲瀷
+ * @param <T> 鐩爣瀵硅薄鐨勬硾鍨嬬被鍨�
+ * @return 杞崲鍚庣殑瀵硅薄鐨勫垪琛紝濡傛灉瀛楃涓蹭负绌哄垯杩斿洖绌哄垪琛�
+ * @throws RuntimeException 濡傛灉杞崲杩囩▼涓彂鐢烮O寮傚父锛屽垯鎶涘嚭杩愯鏃跺紓甯�
+ */
+ public static <T> List<T> parseArray(String text, Class<T> clazz) {
+ if (StringUtils.isEmpty(text)) {
+ return new ArrayList<>();
+ }
+ try {
+ return OBJECT_MAPPER.readValue(text, OBJECT_MAPPER.getTypeFactory().constructCollectionType(List.class, clazz));
+ } catch (IOException e) {
+ throw new RuntimeException(e);
+ }
+ }
+
+}
diff --git a/common/src/main/java/com/ycl/common/utils/excel/utils/ReflectUtils.java b/common/src/main/java/com/ycl/common/utils/excel/utils/ReflectUtils.java
new file mode 100644
index 0000000..c6bcfa9
--- /dev/null
+++ b/common/src/main/java/com/ycl/common/utils/excel/utils/ReflectUtils.java
@@ -0,0 +1,55 @@
+package com.ycl.common.utils.excel.utils;
+
+import cn.hutool.core.util.ReflectUtil;
+import lombok.AccessLevel;
+import lombok.NoArgsConstructor;
+
+import java.lang.reflect.Method;
+
+/**
+ * 鍙嶅皠宸ュ叿绫�. 鎻愪緵璋冪敤getter/setter鏂规硶, 璁块棶绉佹湁鍙橀噺, 璋冪敤绉佹湁鏂规硶, 鑾峰彇娉涘瀷绫诲瀷Class, 琚獳OP杩囩殑鐪熷疄绫荤瓑宸ュ叿鍑芥暟.
+ *
+ * @author Lion Li
+ */
+@SuppressWarnings("rawtypes")
+@NoArgsConstructor(access = AccessLevel.PRIVATE)
+public class ReflectUtils extends ReflectUtil {
+
+ private static final String SETTER_PREFIX = "set";
+
+ private static final String GETTER_PREFIX = "get";
+
+ /**
+ * 璋冪敤Getter鏂规硶.
+ * 鏀寔澶氱骇锛屽锛氬璞″悕.瀵硅薄鍚�.鏂规硶
+ */
+ @SuppressWarnings("unchecked")
+ public static <E> E invokeGetter(Object obj, String propertyName) {
+ Object object = obj;
+ for (String name : StringUtils.split(propertyName, ".")) {
+ String getterMethodName = GETTER_PREFIX + StringUtils.capitalize(name);
+ object = invoke(object, getterMethodName);
+ }
+ return (E) object;
+ }
+
+ /**
+ * 璋冪敤Setter鏂规硶, 浠呭尮閰嶆柟娉曞悕銆�
+ * 鏀寔澶氱骇锛屽锛氬璞″悕.瀵硅薄鍚�.鏂规硶
+ */
+ public static <E> void invokeSetter(Object obj, String propertyName, E value) {
+ Object object = obj;
+ String[] names = StringUtils.split(propertyName, ".");
+ for (int i = 0; i < names.length; i++) {
+ if (i < names.length - 1) {
+ String getterMethodName = GETTER_PREFIX + StringUtils.capitalize(names[i]);
+ object = invoke(object, getterMethodName);
+ } else {
+ String setterMethodName = SETTER_PREFIX + StringUtils.capitalize(names[i]);
+ Method method = getMethodByName(object.getClass(), setterMethodName);
+ invoke(object, method, value);
+ }
+ }
+ }
+
+}
diff --git a/common/src/main/java/com/ycl/common/utils/excel/utils/StreamUtils.java b/common/src/main/java/com/ycl/common/utils/excel/utils/StreamUtils.java
new file mode 100644
index 0000000..968774f
--- /dev/null
+++ b/common/src/main/java/com/ycl/common/utils/excel/utils/StreamUtils.java
@@ -0,0 +1,282 @@
+package com.ycl.common.utils.excel.utils;
+
+import cn.hutool.core.collection.CollUtil;
+import cn.hutool.core.map.MapUtil;
+import lombok.AccessLevel;
+import lombok.NoArgsConstructor;
+
+import java.util.*;
+import java.util.function.BiFunction;
+import java.util.function.Function;
+import java.util.function.Predicate;
+import java.util.stream.Collectors;
+
+/**
+ * stream 娴佸伐鍏风被
+ *
+ * @author Lion Li
+ */
+@NoArgsConstructor(access = AccessLevel.PRIVATE)
+public class StreamUtils {
+
+ /**
+ * 灏哻ollection杩囨护
+ *
+ * @param collection 闇�瑕佽浆鍖栫殑闆嗗悎
+ * @param function 杩囨护鏂规硶
+ * @return 杩囨护鍚庣殑list
+ */
+ public static <E> List<E> filter(Collection<E> collection, Predicate<E> function) {
+ if (CollUtil.isEmpty(collection)) {
+ return CollUtil.newArrayList();
+ }
+ // 娉ㄦ剰姝ゅ涓嶈浣跨敤 .toList() 鏂拌娉� 鍥犱负杩斿洖鐨勬槸涓嶅彲鍙楲ist 浼氬鑷村簭鍒楀寲闂
+ return collection.stream().filter(function).collect(Collectors.toList());
+ }
+
+ /**
+ * 鎵惧埌娴佷腑婊¤冻鏉′欢鐨勭涓�涓厓绱�
+ *
+ * @param collection 闇�瑕佹煡璇㈢殑闆嗗悎
+ * @param function 杩囨护鏂规硶
+ * @return 鎵惧埌绗﹀悎鏉′欢鐨勭涓�涓厓绱狅紝娌℃湁鍒欒繑鍥瀗ull
+ */
+ public static <E> E findFirst(Collection<E> collection, Predicate<E> function) {
+ if (CollUtil.isEmpty(collection)) {
+ return null;
+ }
+ return collection.stream().filter(function).findFirst().orElse(null);
+ }
+
+ /**
+ * 鎵惧埌娴佷腑浠绘剰涓�涓弧瓒虫潯浠剁殑鍏冪礌
+ *
+ * @param collection 闇�瑕佹煡璇㈢殑闆嗗悎
+ * @param function 杩囨护鏂规硶
+ * @return 鎵惧埌绗﹀悎鏉′欢鐨勪换鎰忎竴涓厓绱狅紝娌℃湁鍒欒繑鍥瀗ull
+ */
+ public static <E> Optional<E> findAny(Collection<E> collection, Predicate<E> function) {
+ if (CollUtil.isEmpty(collection)) {
+ return Optional.empty();
+ }
+ return collection.stream().filter(function).findAny();
+ }
+
+ /**
+ * 灏哻ollection鎷兼帴
+ *
+ * @param collection 闇�瑕佽浆鍖栫殑闆嗗悎
+ * @param function 鎷兼帴鏂规硶
+ * @return 鎷兼帴鍚庣殑list
+ */
+ public static <E> String join(Collection<E> collection, Function<E, String> function) {
+ return join(collection, function, StringUtils.SEPARATOR);
+ }
+
+ /**
+ * 灏哻ollection鎷兼帴
+ *
+ * @param collection 闇�瑕佽浆鍖栫殑闆嗗悎
+ * @param function 鎷兼帴鏂规硶
+ * @param delimiter 鎷兼帴绗�
+ * @return 鎷兼帴鍚庣殑list
+ */
+ public static <E> String join(Collection<E> collection, Function<E, String> function, CharSequence delimiter) {
+ if (CollUtil.isEmpty(collection)) {
+ return StringUtils.EMPTY;
+ }
+ return collection.stream().map(function).filter(Objects::nonNull).collect(Collectors.joining(delimiter));
+ }
+
+ /**
+ * 灏哻ollection鎺掑簭
+ *
+ * @param collection 闇�瑕佽浆鍖栫殑闆嗗悎
+ * @param comparing 鎺掑簭鏂规硶
+ * @return 鎺掑簭鍚庣殑list
+ */
+ public static <E> List<E> sorted(Collection<E> collection, Comparator<E> comparing) {
+ if (CollUtil.isEmpty(collection)) {
+ return CollUtil.newArrayList();
+ }
+ // 娉ㄦ剰姝ゅ涓嶈浣跨敤 .toList() 鏂拌娉� 鍥犱负杩斿洖鐨勬槸涓嶅彲鍙楲ist 浼氬鑷村簭鍒楀寲闂
+ return collection.stream().filter(Objects::nonNull).sorted(comparing).collect(Collectors.toList());
+ }
+
+ /**
+ * 灏哻ollection杞寲涓虹被鍨嬩笉鍙樼殑map<br>
+ * <B>{@code Collection<V> ----> Map<K,V>}</B>
+ *
+ * @param collection 闇�瑕佽浆鍖栫殑闆嗗悎
+ * @param key V绫诲瀷杞寲涓篕绫诲瀷鐨刲ambda鏂规硶
+ * @param <V> collection涓殑娉涘瀷
+ * @param <K> map涓殑key绫诲瀷
+ * @return 杞寲鍚庣殑map
+ */
+ public static <V, K> Map<K, V> toIdentityMap(Collection<V> collection, Function<V, K> key) {
+ if (CollUtil.isEmpty(collection)) {
+ return MapUtil.newHashMap();
+ }
+ return collection.stream().filter(Objects::nonNull).collect(Collectors.toMap(key, Function.identity(), (l, r) -> l));
+ }
+
+ /**
+ * 灏咰ollection杞寲涓簃ap(value绫诲瀷涓巆ollection鐨勬硾鍨嬩笉鍚�)<br>
+ * <B>{@code Collection<E> -----> Map<K,V> }</B>
+ *
+ * @param collection 闇�瑕佽浆鍖栫殑闆嗗悎
+ * @param key E绫诲瀷杞寲涓篕绫诲瀷鐨刲ambda鏂规硶
+ * @param value E绫诲瀷杞寲涓篤绫诲瀷鐨刲ambda鏂规硶
+ * @param <E> collection涓殑娉涘瀷
+ * @param <K> map涓殑key绫诲瀷
+ * @param <V> map涓殑value绫诲瀷
+ * @return 杞寲鍚庣殑map
+ */
+ public static <E, K, V> Map<K, V> toMap(Collection<E> collection, Function<E, K> key, Function<E, V> value) {
+ if (CollUtil.isEmpty(collection)) {
+ return MapUtil.newHashMap();
+ }
+ return collection.stream().filter(Objects::nonNull).collect(Collectors.toMap(key, value, (l, r) -> l));
+ }
+
+ /**
+ * 灏哻ollection鎸夌収瑙勫垯(姣斿鏈夌浉鍚岀殑鐝骇id)鍒嗙被鎴恗ap<br>
+ * <B>{@code Collection<E> -------> Map<K,List<E>> } </B>
+ *
+ * @param collection 闇�瑕佸垎绫荤殑闆嗗悎
+ * @param key 鍒嗙被鐨勮鍒�
+ * @param <E> collection涓殑娉涘瀷
+ * @param <K> map涓殑key绫诲瀷
+ * @return 鍒嗙被鍚庣殑map
+ */
+ public static <E, K> Map<K, List<E>> groupByKey(Collection<E> collection, Function<E, K> key) {
+ if (CollUtil.isEmpty(collection)) {
+ return MapUtil.newHashMap();
+ }
+ return collection
+ .stream().filter(Objects::nonNull)
+ .collect(Collectors.groupingBy(key, LinkedHashMap::new, Collectors.toList()));
+ }
+
+ /**
+ * 灏哻ollection鎸夌収涓や釜瑙勫垯(姣斿鏈夌浉鍚岀殑骞寸骇id,鐝骇id)鍒嗙被鎴愬弻灞俶ap<br>
+ * <B>{@code Collection<E> ---> Map<T,Map<U,List<E>>> } </B>
+ *
+ * @param collection 闇�瑕佸垎绫荤殑闆嗗悎
+ * @param key1 绗竴涓垎绫荤殑瑙勫垯
+ * @param key2 绗簩涓垎绫荤殑瑙勫垯
+ * @param <E> 闆嗗悎鍏冪礌绫诲瀷
+ * @param <K> 绗竴涓猰ap涓殑key绫诲瀷
+ * @param <U> 绗簩涓猰ap涓殑key绫诲瀷
+ * @return 鍒嗙被鍚庣殑map
+ */
+ public static <E, K, U> Map<K, Map<U, List<E>>> groupBy2Key(Collection<E> collection, Function<E, K> key1, Function<E, U> key2) {
+ if (CollUtil.isEmpty(collection)) {
+ return MapUtil.newHashMap();
+ }
+ return collection
+ .stream().filter(Objects::nonNull)
+ .collect(Collectors.groupingBy(key1, LinkedHashMap::new, Collectors.groupingBy(key2, LinkedHashMap::new, Collectors.toList())));
+ }
+
+ /**
+ * 灏哻ollection鎸夌収涓や釜瑙勫垯(姣斿鏈夌浉鍚岀殑骞寸骇id,鐝骇id)鍒嗙被鎴愬弻灞俶ap<br>
+ * <B>{@code Collection<E> ---> Map<T,Map<U,E>> } </B>
+ *
+ * @param collection 闇�瑕佸垎绫荤殑闆嗗悎
+ * @param key1 绗竴涓垎绫荤殑瑙勫垯
+ * @param key2 绗簩涓垎绫荤殑瑙勫垯
+ * @param <T> 绗竴涓猰ap涓殑key绫诲瀷
+ * @param <U> 绗簩涓猰ap涓殑key绫诲瀷
+ * @param <E> collection涓殑娉涘瀷
+ * @return 鍒嗙被鍚庣殑map
+ */
+ public static <E, T, U> Map<T, Map<U, E>> group2Map(Collection<E> collection, Function<E, T> key1, Function<E, U> key2) {
+ if (CollUtil.isEmpty(collection) || key1 == null || key2 == null) {
+ return MapUtil.newHashMap();
+ }
+ return collection
+ .stream().filter(Objects::nonNull)
+ .collect(Collectors.groupingBy(key1, LinkedHashMap::new, Collectors.toMap(key2, Function.identity(), (l, r) -> l)));
+ }
+
+ /**
+ * 灏哻ollection杞寲涓篖ist闆嗗悎锛屼絾鏄袱鑰呯殑娉涘瀷涓嶅悓<br>
+ * <B>{@code Collection<E> ------> List<T> } </B>
+ *
+ * @param collection 闇�瑕佽浆鍖栫殑闆嗗悎
+ * @param function collection涓殑娉涘瀷杞寲涓簂ist娉涘瀷鐨刲ambda琛ㄨ揪寮�
+ * @param <E> collection涓殑娉涘瀷
+ * @param <T> List涓殑娉涘瀷
+ * @return 杞寲鍚庣殑list
+ */
+ public static <E, T> List<T> toList(Collection<E> collection, Function<E, T> function) {
+ if (CollUtil.isEmpty(collection)) {
+ return CollUtil.newArrayList();
+ }
+ return collection
+ .stream()
+ .map(function)
+ .filter(Objects::nonNull)
+ // 娉ㄦ剰姝ゅ涓嶈浣跨敤 .toList() 鏂拌娉� 鍥犱负杩斿洖鐨勬槸涓嶅彲鍙楲ist 浼氬鑷村簭鍒楀寲闂
+ .collect(Collectors.toList());
+ }
+
+ /**
+ * 灏哻ollection杞寲涓篠et闆嗗悎锛屼絾鏄袱鑰呯殑娉涘瀷涓嶅悓<br>
+ * <B>{@code Collection<E> ------> Set<T> } </B>
+ *
+ * @param collection 闇�瑕佽浆鍖栫殑闆嗗悎
+ * @param function collection涓殑娉涘瀷杞寲涓簊et娉涘瀷鐨刲ambda琛ㄨ揪寮�
+ * @param <E> collection涓殑娉涘瀷
+ * @param <T> Set涓殑娉涘瀷
+ * @return 杞寲鍚庣殑Set
+ */
+ public static <E, T> Set<T> toSet(Collection<E> collection, Function<E, T> function) {
+ if (CollUtil.isEmpty(collection) || function == null) {
+ return CollUtil.newHashSet();
+ }
+ return collection
+ .stream()
+ .map(function)
+ .filter(Objects::nonNull)
+ .collect(Collectors.toSet());
+ }
+
+
+ /**
+ * 鍚堝苟涓や釜鐩稿悓key绫诲瀷鐨刴ap
+ *
+ * @param map1 绗竴涓渶瑕佸悎骞剁殑 map
+ * @param map2 绗簩涓渶瑕佸悎骞剁殑 map
+ * @param merge 鍚堝苟鐨刲ambda锛屽皢key value1 value2鍚堝苟鎴愭渶缁堢殑绫诲瀷,娉ㄦ剰value鍙兘涓虹┖鐨勬儏鍐�
+ * @param <K> map涓殑key绫诲瀷
+ * @param <X> 绗竴涓� map鐨剉alue绫诲瀷
+ * @param <Y> 绗簩涓� map鐨剉alue绫诲瀷
+ * @param <V> 鏈�缁坢ap鐨剉alue绫诲瀷
+ * @return 鍚堝苟鍚庣殑map
+ */
+ public static <K, X, Y, V> Map<K, V> merge(Map<K, X> map1, Map<K, Y> map2, BiFunction<X, Y, V> merge) {
+ if (MapUtil.isEmpty(map1) && MapUtil.isEmpty(map2)) {
+ return MapUtil.newHashMap();
+ } else if (MapUtil.isEmpty(map1)) {
+ map1 = MapUtil.newHashMap();
+ } else if (MapUtil.isEmpty(map2)) {
+ map2 = MapUtil.newHashMap();
+ }
+ Set<K> key = new HashSet<>();
+ key.addAll(map1.keySet());
+ key.addAll(map2.keySet());
+ Map<K, V> map = new HashMap<>();
+ for (K t : key) {
+ X x = map1.get(t);
+ Y y = map2.get(t);
+ V z = merge.apply(x, y);
+ if (z != null) {
+ map.put(t, z);
+ }
+ }
+ return map;
+ }
+
+}
diff --git a/common/src/main/java/com/ycl/common/utils/excel/utils/StringUtils.java b/common/src/main/java/com/ycl/common/utils/excel/utils/StringUtils.java
new file mode 100644
index 0000000..8cf75f8
--- /dev/null
+++ b/common/src/main/java/com/ycl/common/utils/excel/utils/StringUtils.java
@@ -0,0 +1,323 @@
+package com.ycl.common.utils.excel.utils;
+
+import cn.hutool.core.collection.CollUtil;
+import cn.hutool.core.convert.Convert;
+import cn.hutool.core.lang.Validator;
+import cn.hutool.core.util.StrUtil;
+import lombok.AccessLevel;
+import lombok.NoArgsConstructor;
+import org.springframework.util.AntPathMatcher;
+
+import java.util.*;
+import java.util.function.Function;
+import java.util.stream.Collectors;
+
+/**
+ * 瀛楃涓插伐鍏风被
+ *
+ * @author Lion Li
+ */
+@NoArgsConstructor(access = AccessLevel.PRIVATE)
+public class StringUtils extends org.apache.commons.lang3.StringUtils {
+
+ public static final String SEPARATOR = ",";
+
+ public static final String SLASH = "/";
+
+ /**
+ * 鑾峰彇鍙傛暟涓嶄负绌哄��
+ *
+ * @param str defaultValue 瑕佸垽鏂殑value
+ * @return value 杩斿洖鍊�
+ */
+ public static String blankToDefault(String str, String defaultValue) {
+ return StrUtil.blankToDefault(str, defaultValue);
+ }
+
+ /**
+ * * 鍒ゆ柇涓�涓瓧绗︿覆鏄惁涓虹┖涓�
+ *
+ * @param str String
+ * @return true锛氫负绌� false锛氶潪绌�
+ */
+ public static boolean isEmpty(String str) {
+ return StrUtil.isEmpty(str);
+ }
+
+ /**
+ * * 鍒ゆ柇涓�涓瓧绗︿覆鏄惁涓洪潪绌轰覆
+ *
+ * @param str String
+ * @return true锛氶潪绌轰覆 false锛氱┖涓�
+ */
+ public static boolean isNotEmpty(String str) {
+ return !isEmpty(str);
+ }
+
+ /**
+ * 鍘荤┖鏍�
+ */
+ public static String trim(String str) {
+ return StrUtil.trim(str);
+ }
+
+ /**
+ * 鎴彇瀛楃涓�
+ *
+ * @param str 瀛楃涓�
+ * @param start 寮�濮�
+ * @return 缁撴灉
+ */
+ public static String substring(final String str, int start) {
+ return substring(str, start, str.length());
+ }
+
+ /**
+ * 鎴彇瀛楃涓�
+ *
+ * @param str 瀛楃涓�
+ * @param start 寮�濮�
+ * @param end 缁撴潫
+ * @return 缁撴灉
+ */
+ public static String substring(final String str, int start, int end) {
+ return StrUtil.sub(str, start, end);
+ }
+
+ /**
+ * 鏍煎紡鍖栨枃鏈�, {} 琛ㄧず鍗犱綅绗�<br>
+ * 姝ゆ柟娉曞彧鏄畝鍗曞皢鍗犱綅绗� {} 鎸夌収椤哄簭鏇挎崲涓哄弬鏁�<br>
+ * 濡傛灉鎯宠緭鍑� {} 浣跨敤 \\杞箟 { 鍗冲彲锛屽鏋滄兂杈撳嚭 {} 涔嬪墠鐨� \ 浣跨敤鍙岃浆涔夌 \\\\ 鍗冲彲<br>
+ * 渚嬶細<br>
+ * 閫氬父浣跨敤锛歠ormat("this is {} for {}", "a", "b") -> this is a for b<br>
+ * 杞箟{}锛� format("this is \\{} for {}", "a", "b") -> this is {} for a<br>
+ * 杞箟\锛� format("this is \\\\{} for {}", "a", "b") -> this is \a for b<br>
+ *
+ * @param template 鏂囨湰妯℃澘锛岃鏇挎崲鐨勯儴鍒嗙敤 {} 琛ㄧず
+ * @param params 鍙傛暟鍊�
+ * @return 鏍煎紡鍖栧悗鐨勬枃鏈�
+ */
+ public static String format(String template, Object... params) {
+ return StrUtil.format(template, params);
+ }
+
+ /**
+ * 鏄惁涓篽ttp(s)://寮�澶�
+ *
+ * @param link 閾炬帴
+ * @return 缁撴灉
+ */
+ public static boolean ishttp(String link) {
+ return Validator.isUrl(link);
+ }
+
+ /**
+ * 瀛楃涓茶浆set
+ *
+ * @param str 瀛楃涓�
+ * @param sep 鍒嗛殧绗�
+ * @return set闆嗗悎
+ */
+ public static Set<String> str2Set(String str, String sep) {
+ return new HashSet<>(str2List(str, sep, true, false));
+ }
+
+ /**
+ * 瀛楃涓茶浆list
+ *
+ * @param str 瀛楃涓�
+ * @param sep 鍒嗛殧绗�
+ * @param filterBlank 杩囨护绾┖鐧�
+ * @param trim 鍘绘帀棣栧熬绌虹櫧
+ * @return list闆嗗悎
+ */
+ public static List<String> str2List(String str, String sep, boolean filterBlank, boolean trim) {
+ List<String> list = new ArrayList<>();
+ if (isEmpty(str)) {
+ return list;
+ }
+
+ // 杩囨护绌虹櫧瀛楃涓�
+ if (filterBlank && isBlank(str)) {
+ return list;
+ }
+ String[] split = str.split(sep);
+ for (String string : split) {
+ if (filterBlank && isBlank(string)) {
+ continue;
+ }
+ if (trim) {
+ string = trim(string);
+ }
+ list.add(string);
+ }
+
+ return list;
+ }
+
+ /**
+ * 鏌ユ壘鎸囧畾瀛楃涓叉槸鍚﹀寘鍚寚瀹氬瓧绗︿覆鍒楄〃涓殑浠绘剰涓�涓瓧绗︿覆鍚屾椂涓插拷鐣ュぇ灏忓啓
+ *
+ * @param cs 鎸囧畾瀛楃涓�
+ * @param searchCharSequences 闇�瑕佹鏌ョ殑瀛楃涓叉暟缁�
+ * @return 鏄惁鍖呭惈浠绘剰涓�涓瓧绗︿覆
+ */
+ public static boolean containsAnyIgnoreCase(CharSequence cs, CharSequence... searchCharSequences) {
+ return StrUtil.containsAnyIgnoreCase(cs, searchCharSequences);
+ }
+
+ /**
+ * 椹煎嘲杞笅鍒掔嚎鍛藉悕
+ */
+ public static String toUnderScoreCase(String str) {
+ return StrUtil.toUnderlineCase(str);
+ }
+
+ /**
+ * 鏄惁鍖呭惈瀛楃涓�
+ *
+ * @param str 楠岃瘉瀛楃涓�
+ * @param strs 瀛楃涓茬粍
+ * @return 鍖呭惈杩斿洖true
+ */
+ public static boolean inStringIgnoreCase(String str, String... strs) {
+ return StrUtil.equalsAnyIgnoreCase(str, strs);
+ }
+
+ /**
+ * 灏嗕笅鍒掔嚎澶у啓鏂瑰紡鍛藉悕鐨勫瓧绗︿覆杞崲涓洪┘宄板紡銆傚鏋滆浆鎹㈠墠鐨勪笅鍒掔嚎澶у啓鏂瑰紡鍛藉悕鐨勫瓧绗︿覆涓虹┖锛屽垯杩斿洖绌哄瓧绗︿覆銆� 渚嬪锛欻ELLO_WORLD->HelloWorld
+ *
+ * @param name 杞崲鍓嶇殑涓嬪垝绾垮ぇ鍐欐柟寮忓懡鍚嶇殑瀛楃涓�
+ * @return 杞崲鍚庣殑椹煎嘲寮忓懡鍚嶇殑瀛楃涓�
+ */
+ public static String convertToCamelCase(String name) {
+ return StrUtil.upperFirst(StrUtil.toCamelCase(name));
+ }
+
+ /**
+ * 椹煎嘲寮忓懡鍚嶆硶 渚嬪锛歶ser_name->userName
+ */
+ public static String toCamelCase(String s) {
+ return StrUtil.toCamelCase(s);
+ }
+
+ /**
+ * 鏌ユ壘鎸囧畾瀛楃涓叉槸鍚﹀尮閰嶆寚瀹氬瓧绗︿覆鍒楄〃涓殑浠绘剰涓�涓瓧绗︿覆
+ *
+ * @param str 鎸囧畾瀛楃涓�
+ * @param strs 闇�瑕佹鏌ョ殑瀛楃涓叉暟缁�
+ * @return 鏄惁鍖归厤
+ */
+ public static boolean matches(String str, List<String> strs) {
+ if (isEmpty(str) || CollUtil.isEmpty(strs)) {
+ return false;
+ }
+ for (String pattern : strs) {
+ if (isMatch(pattern, str)) {
+ return true;
+ }
+ }
+ return false;
+ }
+
+ /**
+ * 鍒ゆ柇url鏄惁涓庤鍒欓厤缃�:
+ * ? 琛ㄧず鍗曚釜瀛楃;
+ * * 琛ㄧず涓�灞傝矾寰勫唴鐨勪换鎰忓瓧绗︿覆锛屼笉鍙法灞傜骇;
+ * ** 琛ㄧず浠绘剰灞傝矾寰�;
+ *
+ * @param pattern 鍖归厤瑙勫垯
+ * @param url 闇�瑕佸尮閰嶇殑url
+ */
+ public static boolean isMatch(String pattern, String url) {
+ AntPathMatcher matcher = new AntPathMatcher();
+ return matcher.match(pattern, url);
+ }
+
+ /**
+ * 鏁板瓧宸﹁竟琛ラ綈0锛屼娇涔嬭揪鍒版寚瀹氶暱搴︺�傛敞鎰忥紝濡傛灉鏁板瓧杞崲涓哄瓧绗︿覆鍚庯紝闀垮害澶т簬size锛屽垯鍙繚鐣� 鏈�鍚巗ize涓瓧绗︺��
+ *
+ * @param num 鏁板瓧瀵硅薄
+ * @param size 瀛楃涓叉寚瀹氶暱搴�
+ * @return 杩斿洖鏁板瓧鐨勫瓧绗︿覆鏍煎紡锛岃瀛楃涓蹭负鎸囧畾闀垮害銆�
+ */
+ public static String padl(final Number num, final int size) {
+ return padl(num.toString(), size, '0');
+ }
+
+ /**
+ * 瀛楃涓插乏琛ラ綈銆傚鏋滃師濮嬪瓧绗︿覆s闀垮害澶т簬size锛屽垯鍙繚鐣欐渶鍚巗ize涓瓧绗︺��
+ *
+ * @param s 鍘熷瀛楃涓�
+ * @param size 瀛楃涓叉寚瀹氶暱搴�
+ * @param c 鐢ㄤ簬琛ラ綈鐨勫瓧绗�
+ * @return 杩斿洖鎸囧畾闀垮害鐨勫瓧绗︿覆锛岀敱鍘熷瓧绗︿覆宸﹁ˉ榻愭垨鎴彇寰楀埌銆�
+ */
+ public static String padl(final String s, final int size, final char c) {
+ final StringBuilder sb = new StringBuilder(size);
+ if (s != null) {
+ final int len = s.length();
+ if (s.length() <= size) {
+ sb.append(String.valueOf(c).repeat(size - len));
+ sb.append(s);
+ } else {
+ return s.substring(len - size, len);
+ }
+ } else {
+ sb.append(String.valueOf(c).repeat(Math.max(0, size)));
+ }
+ return sb.toString();
+ }
+
+ /**
+ * 鍒囧垎瀛楃涓�(鍒嗛殧绗﹂粯璁ら�楀彿)
+ *
+ * @param str 琚垏鍒嗙殑瀛楃涓�
+ * @return 鍒嗗壊鍚庣殑鏁版嵁鍒楄〃
+ */
+ public static List<String> splitList(String str) {
+ return splitTo(str, Convert::toStr);
+ }
+
+ /**
+ * 鍒囧垎瀛楃涓�
+ *
+ * @param str 琚垏鍒嗙殑瀛楃涓�
+ * @param separator 鍒嗛殧绗�
+ * @return 鍒嗗壊鍚庣殑鏁版嵁鍒楄〃
+ */
+ public static List<String> splitList(String str, String separator) {
+ return splitTo(str, separator, Convert::toStr);
+ }
+
+ /**
+ * 鍒囧垎瀛楃涓茶嚜瀹氫箟杞崲(鍒嗛殧绗﹂粯璁ら�楀彿)
+ *
+ * @param str 琚垏鍒嗙殑瀛楃涓�
+ * @param mapper 鑷畾涔夎浆鎹�
+ * @return 鍒嗗壊鍚庣殑鏁版嵁鍒楄〃
+ */
+ public static <T> List<T> splitTo(String str, Function<? super Object, T> mapper) {
+ return splitTo(str, SEPARATOR, mapper);
+ }
+
+ /**
+ * 鍒囧垎瀛楃涓茶嚜瀹氫箟杞崲
+ *
+ * @param str 琚垏鍒嗙殑瀛楃涓�
+ * @param separator 鍒嗛殧绗�
+ * @param mapper 鑷畾涔夎浆鎹�
+ * @return 鍒嗗壊鍚庣殑鏁版嵁鍒楄〃
+ */
+ public static <T> List<T> splitTo(String str, String separator, Function<? super Object, T> mapper) {
+ if (isBlank(str)) {
+ return new ArrayList<>(0);
+ }
+ return StrUtil.split(str, separator)
+ .stream()
+ .filter(Objects::nonNull)
+ .map(mapper)
+ .collect(Collectors.toList());
+ }
+
+}
diff --git a/common/src/main/java/com/ycl/common/utils/excel/utils/ValidatorUtils.java b/common/src/main/java/com/ycl/common/utils/excel/utils/ValidatorUtils.java
new file mode 100644
index 0000000..9ac6f62
--- /dev/null
+++ b/common/src/main/java/com/ycl/common/utils/excel/utils/ValidatorUtils.java
@@ -0,0 +1,36 @@
+package com.ycl.common.utils.excel.utils;
+
+import com.ycl.common.utils.spring.SpringUtils;
+import lombok.AccessLevel;
+import lombok.NoArgsConstructor;
+
+import javax.validation.ConstraintViolation;
+import javax.validation.ConstraintViolationException;
+import javax.validation.Validator;
+import java.util.Set;
+
+/**
+ * Validator 鏍￠獙妗嗘灦宸ュ叿
+ *
+ * @author Lion Li
+ */
+@NoArgsConstructor(access = AccessLevel.PRIVATE)
+public class ValidatorUtils {
+
+ private static final Validator VALID = SpringUtils.getBean(Validator.class);
+
+ /**
+ * 瀵圭粰瀹氬璞¤繘琛屽弬鏁版牎楠岋紝骞舵牴鎹寚瀹氱殑鏍￠獙缁勮繘琛屾牎楠�
+ *
+ * @param object 瑕佽繘琛屾牎楠岀殑瀵硅薄
+ * @param groups 鏍￠獙缁�
+ * @throws ConstraintViolationException 濡傛灉鏍￠獙涓嶉�氳繃锛屽垯鎶涘嚭鍙傛暟鏍￠獙寮傚父
+ */
+ public static <T> void validate(T object, Class<?>... groups) {
+ Set<ConstraintViolation<T>> validate = VALID.validate(object, groups);
+ if (!validate.isEmpty()) {
+ throw new ConstraintViolationException("鍙傛暟鏍￠獙寮傚父", validate);
+ }
+ }
+
+}
diff --git a/system/pom.xml b/system/pom.xml
index 18ddae0..1482ed7 100644
--- a/system/pom.xml
+++ b/system/pom.xml
@@ -22,12 +22,6 @@
<artifactId>knife4j-spring-boot-starter</artifactId>
</dependency>
- <!-- easy excel -->
- <dependency>
- <groupId>com.alibaba</groupId>
- <artifactId>easyexcel</artifactId>
- </dependency>
-
<!-- 楠岃瘉鐮� -->
<dependency>
--
Gitblit v1.8.0