From 4a9aafe8530e945bd52d8093944c4bcb1a89c28f Mon Sep 17 00:00:00 2001
From: 疯狂的狮子Li <15040126243@163.com>
Date: 星期日, 25 六月 2023 11:13:05 +0800
Subject: [PATCH] add 增加 excel 导出下拉框功能
---
ruoyi-modules/ruoyi-demo/src/main/java/org/dromara/demo/controller/TestExcelController.java | 38 +
ruoyi-modules/ruoyi-demo/src/main/java/org/dromara/demo/service/impl/ExportExcelServiceImpl.java | 222 +++++++++++
ruoyi-modules/ruoyi-system/src/main/java/org/dromara/system/service/impl/SysDictTypeServiceImpl.java | 6
ruoyi-common/ruoyi-common-excel/src/main/java/org/dromara/common/excel/utils/ExcelUtil.java | 83 +++
ruoyi-common/ruoyi-common-excel/src/main/java/org/dromara/common/excel/core/ExcelDownHandler.java | 370 +++++++++++++++++++
ruoyi-modules/ruoyi-demo/src/main/java/org/dromara/demo/service/IExportExcelService.java | 18
ruoyi-modules/ruoyi-demo/src/main/java/org/dromara/demo/domain/vo/ExportDemoVo.java | 118 ++++++
ruoyi-common/ruoyi-common-excel/src/main/java/org/dromara/common/excel/core/DropDownOptions.java | 149 +++++++
ruoyi-modules/ruoyi-demo/src/main/java/org/dromara/demo/listener/ExportDemoListener.java | 68 +++
ruoyi-common/ruoyi-common-core/src/main/java/org/dromara/common/core/service/DictService.java | 10
10 files changed, 1,063 insertions(+), 19 deletions(-)
diff --git a/ruoyi-common/ruoyi-common-core/src/main/java/org/dromara/common/core/service/DictService.java b/ruoyi-common/ruoyi-common-core/src/main/java/org/dromara/common/core/service/DictService.java
index 04d0f9b..b78a7f2 100644
--- a/ruoyi-common/ruoyi-common-core/src/main/java/org/dromara/common/core/service/DictService.java
+++ b/ruoyi-common/ruoyi-common-core/src/main/java/org/dromara/common/core/service/DictService.java
@@ -1,5 +1,7 @@
package org.dromara.common.core.service;
+import java.util.Map;
+
/**
* 閫氱敤 瀛楀吀鏈嶅姟
*
@@ -54,4 +56,12 @@
*/
String getDictValue(String dictType, String dictLabel, String separator);
+ /**
+ * 鑾峰彇瀛楀吀涓嬫墍鏈夌殑瀛楀吀鍊间笌鏍囩
+ *
+ * @param dictType 瀛楀吀绫诲瀷
+ * @return dictValue涓簁ey锛宒ictLabel涓哄�肩粍鎴愮殑Map
+ */
+ Map<String, String> getAllDictByDictType(String dictType);
+
}
diff --git a/ruoyi-common/ruoyi-common-excel/src/main/java/org/dromara/common/excel/core/DropDownOptions.java b/ruoyi-common/ruoyi-common-excel/src/main/java/org/dromara/common/excel/core/DropDownOptions.java
new file mode 100644
index 0000000..8b53a0c
--- /dev/null
+++ b/ruoyi-common/ruoyi-common-excel/src/main/java/org/dromara/common/excel/core/DropDownOptions.java
@@ -0,0 +1,149 @@
+package org.dromara.common.excel.core;
+
+import cn.hutool.core.util.StrUtil;
+import lombok.AllArgsConstructor;
+import lombok.Data;
+import lombok.NoArgsConstructor;
+import org.dromara.common.core.exception.ServiceException;
+
+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/ruoyi-common/ruoyi-common-excel/src/main/java/org/dromara/common/excel/core/ExcelDownHandler.java b/ruoyi-common/ruoyi-common-excel/src/main/java/org/dromara/common/excel/core/ExcelDownHandler.java
new file mode 100644
index 0000000..52bfebc
--- /dev/null
+++ b/ruoyi-common/ruoyi-common-excel/src/main/java/org/dromara/common/excel/core/ExcelDownHandler.java
@@ -0,0 +1,370 @@
+package org.dromara.common.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.annotation.ExcelProperty;
+import com.alibaba.excel.write.handler.SheetWriteHandler;
+import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
+import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
+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 org.dromara.common.core.exception.ServiceException;
+import org.dromara.common.core.service.DictService;
+import org.dromara.common.core.utils.SpringUtils;
+import org.dromara.common.core.utils.StreamUtils;
+import org.dromara.common.excel.annotation.ExcelDictFormat;
+import org.dromara.common.excel.annotation.ExcelEnumFormat;
+
+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();
+ Field[] fields = writeWorkbookHolder.getClazz().getDeclaredFields();
+ Workbook workbook = writeWorkbookHolder.getWorkbook();
+ int length = fields.length;
+ for (int i = 0; i < length; i++) {
+ // 寰幆瀹炰綋涓殑姣忎釜灞炴��
+ // 鍙�夌殑涓嬫媺鍊�
+ List<String> options = new ArrayList<>();
+ if (fields[i].isAnnotationPresent(ExcelDictFormat.class)) {
+ // 濡傛灉鎸囧畾浜咢ExcelDictFormat锛屽垯浣跨敤瀛楀吀鐨勯�昏緫
+ ExcelDictFormat format = fields[i].getDeclaredAnnotation(ExcelDictFormat.class);
+ String dictType = format.dictType();
+ String converterExp = format.readConverterExp();
+ if (StrUtil.isNotBlank(dictType)) {
+ // 濡傛灉浼犻�掍簡瀛楀吀鍚嶏紝鍒欎緷鎹瓧鍏稿缓绔嬩笅鎷�
+ Collection<String> values = Optional.ofNullable(dictService.getAllDictByDictType(dictType))
+ .orElseThrow(() -> new ServiceException(String.format("瀛楀吀 %s 涓嶅瓨鍦�", dictType)))
+ .values();
+ options = new ArrayList<>(values);
+ } else if (StrUtil.isNotBlank(converterExp)) {
+ // 濡傛灉鎸囧畾浜嗙‘鍒囩殑鍊硷紝鍒欑洿鎺ヨВ鏋愮‘鍒囩殑鍊�
+ options = StrUtil.split(converterExp, format.separator(), true, true);
+ }
+ } else if (fields[i].isAnnotationPresent(ExcelEnumFormat.class)) {
+ // 鍚﹀垯濡傛灉鎸囧畾浜咢ExcelEnumFormat锛屽垯浣跨敤鏋氫妇鐨勯�昏緫
+ ExcelEnumFormat format = fields[i].getDeclaredAnnotation(ExcelEnumFormat.class);
+ List<Object> values = EnumUtil.getFieldValues(format.enumClass(), format.textField());
+ options = StreamUtils.toList(values, String::valueOf);
+ }
+ if (ObjectUtil.isNotEmpty(options)) {
+ // 浠呭綋涓嬫媺鍙�夐」涓嶄负绌烘椂鎵ц
+ // 鑾峰彇鍒椾笅鏍囷紝榛樿涓哄綋鍓嶅惊鐜鏁�
+ int index = i;
+ if (fields[i].isAnnotationPresent(ExcelProperty.class)) {
+ // 濡傛灉鎸囧畾浜嗗垪涓嬫爣锛屼互鎸囧畾鐨勪负涓�
+ index = fields[i].getDeclaredAnnotation(ExcelProperty.class).index();
+ }
+ if (options.size() > 20) {
+ // 杩欓噷闄愬埗濡傛灉鍙�夐」澶т簬20锛屽垯浣跨敤棰濆琛ㄥ舰寮�
+ dropDownWithSheet(helper, workbook, sheet, index, options);
+ } else {
+ // 鍚﹀垯浣跨敤鍥哄畾鍊煎舰寮�
+ dropDownWithSimple(helper, sheet, index, options);
+ }
+ }
+ }
+ 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/ruoyi-common/ruoyi-common-excel/src/main/java/org/dromara/common/excel/utils/ExcelUtil.java b/ruoyi-common/ruoyi-common-excel/src/main/java/org/dromara/common/excel/utils/ExcelUtil.java
index 3605cf2..00a78ea 100644
--- a/ruoyi-common/ruoyi-common-excel/src/main/java/org/dromara/common/excel/utils/ExcelUtil.java
+++ b/ruoyi-common/ruoyi-common-excel/src/main/java/org/dromara/common/excel/utils/ExcelUtil.java
@@ -10,21 +10,19 @@
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
-import org.dromara.common.core.utils.StringUtils;
-import org.dromara.common.core.utils.file.FileUtils;
-import org.dromara.common.excel.convert.ExcelBigNumberConvert;
-import org.dromara.common.excel.core.CellMergeStrategy;
-import org.dromara.common.excel.core.DefaultExcelListener;
-import org.dromara.common.excel.core.ExcelListener;
-import org.dromara.common.excel.core.ExcelResult;
import jakarta.servlet.ServletOutputStream;
import jakarta.servlet.http.HttpServletResponse;
import lombok.AccessLevel;
import lombok.NoArgsConstructor;
+import org.dromara.common.core.utils.StringUtils;
+import org.dromara.common.core.utils.file.FileUtils;
+import org.dromara.common.excel.convert.ExcelBigNumberConvert;
+import org.dromara.common.excel.core.*;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
+import java.io.UnsupportedEncodingException;
import java.util.Collection;
import java.util.List;
import java.util.Map;
@@ -87,7 +85,26 @@
try {
resetResponse(sheetName, response);
ServletOutputStream os = response.getOutputStream();
- exportExcel(list, sheetName, clazz, false, os);
+ exportExcel(list, sheetName, clazz, false, os, null);
+ } catch (IOException e) {
+ throw new RuntimeException("瀵煎嚭Excel寮傚父");
+ }
+ }
+
+ /**
+ * 瀵煎嚭excel
+ *
+ * @param list 瀵煎嚭鏁版嵁闆嗗悎
+ * @param sheetName 宸ヤ綔琛ㄧ殑鍚嶇О
+ * @param clazz 瀹炰綋绫�
+ * @param response 鍝嶅簲浣�
+ * @param options 绾ц仈涓嬫媺閫�
+ */
+ public static <T> void exportExcel(List<T> list, String sheetName, Class<T> clazz, HttpServletResponse response, List<DropDownOptions> options) {
+ try {
+ resetResponse(sheetName, response);
+ ServletOutputStream os = response.getOutputStream();
+ exportExcel(list, sheetName, clazz, false, os, options);
} catch (IOException e) {
throw new RuntimeException("瀵煎嚭Excel寮傚父");
}
@@ -106,7 +123,27 @@
try {
resetResponse(sheetName, response);
ServletOutputStream os = response.getOutputStream();
- exportExcel(list, sheetName, clazz, merge, os);
+ exportExcel(list, sheetName, clazz, merge, os, null);
+ } catch (IOException e) {
+ throw new RuntimeException("瀵煎嚭Excel寮傚父");
+ }
+ }
+
+ /**
+ * 瀵煎嚭excel
+ *
+ * @param list 瀵煎嚭鏁版嵁闆嗗悎
+ * @param sheetName 宸ヤ綔琛ㄧ殑鍚嶇О
+ * @param clazz 瀹炰綋绫�
+ * @param merge 鏄惁鍚堝苟鍗曞厓鏍�
+ * @param response 鍝嶅簲浣�
+ * @param options 绾ц仈涓嬫媺閫�
+ */
+ public static <T> void exportExcel(List<T> list, String sheetName, Class<T> clazz, boolean merge, HttpServletResponse response, List<DropDownOptions> options) {
+ try {
+ resetResponse(sheetName, response);
+ ServletOutputStream os = response.getOutputStream();
+ exportExcel(list, sheetName, clazz, merge, os, options);
} catch (IOException e) {
throw new RuntimeException("瀵煎嚭Excel寮傚父");
}
@@ -121,7 +158,20 @@
* @param os 杈撳嚭娴�
*/
public static <T> void exportExcel(List<T> list, String sheetName, Class<T> clazz, OutputStream os) {
- exportExcel(list, sheetName, clazz, false, os);
+ exportExcel(list, sheetName, clazz, false, os, null);
+ }
+
+ /**
+ * 瀵煎嚭excel
+ *
+ * @param list 瀵煎嚭鏁版嵁闆嗗悎
+ * @param sheetName 宸ヤ綔琛ㄧ殑鍚嶇О
+ * @param clazz 瀹炰綋绫�
+ * @param os 杈撳嚭娴�
+ * @param options 绾ц仈涓嬫媺閫夊唴瀹�
+ */
+ public static <T> void exportExcel(List<T> list, String sheetName, Class<T> clazz, OutputStream os, List<DropDownOptions> options) {
+ exportExcel(list, sheetName, clazz, false, os, options);
}
/**
@@ -133,7 +183,8 @@
* @param merge 鏄惁鍚堝苟鍗曞厓鏍�
* @param os 杈撳嚭娴�
*/
- public static <T> void exportExcel(List<T> list, String sheetName, Class<T> clazz, boolean merge, OutputStream os) {
+ public static <T> void exportExcel(List<T> list, String sheetName, Class<T> clazz, boolean merge,
+ OutputStream os, List<DropDownOptions> options) {
ExcelWriterSheetBuilder builder = EasyExcel.write(os, clazz)
.autoCloseStream(false)
// 鑷姩閫傞厤
@@ -144,6 +195,10 @@
if (merge) {
// 鍚堝苟澶勭悊鍣�
builder.registerWriteHandler(new CellMergeStrategy(list, true));
+ }
+ if (CollUtil.isNotEmpty(options)) {
+ // 娣诲姞涓嬫媺妗嗘搷浣�
+ builder.registerWriteHandler(new ExcelDownHandler(options));
}
builder.doWrite(list);
}
@@ -253,7 +308,7 @@
/**
* 閲嶇疆鍝嶅簲浣�
*/
- private static void resetResponse(String sheetName, HttpServletResponse response) {
+ 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");
@@ -275,7 +330,7 @@
if (StringUtils.containsAny(propertyValue, separator)) {
for (String value : propertyValue.split(separator)) {
if (itemArray[0].equals(value)) {
- propertyString.append(itemArray[1]).append(separator);
+ propertyString.append(itemArray[1] + separator);
break;
}
}
@@ -304,7 +359,7 @@
if (StringUtils.containsAny(propertyValue, separator)) {
for (String value : propertyValue.split(separator)) {
if (itemArray[1].equals(value)) {
- propertyString.append(itemArray[0]).append(separator);
+ propertyString.append(itemArray[0] + separator);
break;
}
}
diff --git a/ruoyi-modules/ruoyi-demo/src/main/java/org/dromara/demo/controller/TestExcelController.java b/ruoyi-modules/ruoyi-demo/src/main/java/org/dromara/demo/controller/TestExcelController.java
index d83455d..418f740 100644
--- a/ruoyi-modules/ruoyi-demo/src/main/java/org/dromara/demo/controller/TestExcelController.java
+++ b/ruoyi-modules/ruoyi-demo/src/main/java/org/dromara/demo/controller/TestExcelController.java
@@ -1,14 +1,19 @@
package org.dromara.demo.controller;
import cn.hutool.core.collection.CollUtil;
-import org.dromara.common.excel.utils.ExcelUtil;
+import jakarta.servlet.http.HttpServletResponse;
import lombok.AllArgsConstructor;
import lombok.Data;
-import org.springframework.web.bind.annotation.GetMapping;
-import org.springframework.web.bind.annotation.RequestMapping;
-import org.springframework.web.bind.annotation.RestController;
+import lombok.RequiredArgsConstructor;
+import org.dromara.common.excel.core.ExcelResult;
+import org.dromara.common.excel.utils.ExcelUtil;
+import org.dromara.demo.domain.vo.ExportDemoVo;
+import org.dromara.demo.listener.ExportDemoListener;
+import org.dromara.demo.service.IExportExcelService;
+import org.springframework.http.MediaType;
+import org.springframework.web.bind.annotation.*;
+import org.springframework.web.multipart.MultipartFile;
-import jakarta.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
@@ -19,9 +24,12 @@
*
* @author Lion Li
*/
+@RequiredArgsConstructor
@RestController
@RequestMapping("/demo/excel")
public class TestExcelController {
+
+ private final IExportExcelService exportExcelService;
/**
* 鍗曞垪琛ㄥ鏁版嵁
@@ -76,6 +84,26 @@
ExcelUtil.exportTemplateMultiList(multiListMap, "澶氬垪琛�.xlsx", "excel/澶氬垪琛�.xlsx", response);
}
+ /**
+ * 瀵煎嚭涓嬫媺妗�
+ *
+ * @param response /
+ */
+ @GetMapping("/exportWithOptions")
+ public void exportWithOptions(HttpServletResponse response) {
+ exportExcelService.exportWithOptions(response);
+ }
+
+ /**
+ * 瀵煎叆琛ㄦ牸
+ */
+ @PostMapping(value = "/importWithOptions", consumes = MediaType.MULTIPART_FORM_DATA_VALUE)
+ public List<ExportDemoVo> importWithOptions(@RequestPart("file") MultipartFile file) throws Exception {
+ // 澶勭悊瑙ｆ瀽缁撴灉
+ ExcelResult<ExportDemoVo> excelResult = ExcelUtil.importExcel(file.getInputStream(), ExportDemoVo.class, new ExportDemoListener());
+ return excelResult.getList();
+ }
+
@Data
@AllArgsConstructor
static class TestObj1 {
diff --git a/ruoyi-modules/ruoyi-demo/src/main/java/org/dromara/demo/domain/vo/ExportDemoVo.java b/ruoyi-modules/ruoyi-demo/src/main/java/org/dromara/demo/domain/vo/ExportDemoVo.java
new file mode 100644
index 0000000..95fa0d1
--- /dev/null
+++ b/ruoyi-modules/ruoyi-demo/src/main/java/org/dromara/demo/domain/vo/ExportDemoVo.java
@@ -0,0 +1,118 @@
+package org.dromara.demo.domain.vo;
+
+import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
+import com.alibaba.excel.annotation.ExcelProperty;
+import jakarta.validation.constraints.NotEmpty;
+import jakarta.validation.constraints.NotNull;
+import lombok.AllArgsConstructor;
+import lombok.Data;
+import lombok.NoArgsConstructor;
+import org.dromara.common.core.enums.UserStatus;
+import org.dromara.common.core.validate.AddGroup;
+import org.dromara.common.core.validate.EditGroup;
+import org.dromara.common.excel.annotation.ExcelDictFormat;
+import org.dromara.common.excel.annotation.ExcelEnumFormat;
+import org.dromara.common.excel.convert.ExcelDictConvert;
+import org.dromara.common.excel.convert.ExcelEnumConvert;
+
+/**
+ * 甯︽湁涓嬫媺閫夌殑Excel瀵煎嚭
+ *
+ * @author Emil.Zhang
+ */
+@Data
+@ExcelIgnoreUnannotated
+@AllArgsConstructor
+@NoArgsConstructor
+public class ExportDemoVo {
+
+ private static final long serialVersionUID = 1L;
+
+ /**
+ * 鐢ㄦ埛鏄电О
+ */
+ @ExcelProperty(value = "鐢ㄦ埛鍚�", index = 0)
+ @NotEmpty(message = "鐢ㄦ埛鍚嶄笉鑳戒负绌�", groups = AddGroup.class)
+ private String nickName;
+
+ /**
+ * 鐢ㄦ埛绫诲瀷
+ * </p>
+ * 浣跨敤ExcelEnumFormat娉ㄨВ闇�瑕佽繘琛屼笅鎷夐�夌殑閮ㄥ垎
+ */
+ @ExcelProperty(value = "鐢ㄦ埛绫诲瀷", index = 1, converter = ExcelEnumConvert.class)
+ @ExcelEnumFormat(enumClass = UserStatus.class, textField = "info")
+ @NotEmpty(message = "鐢ㄦ埛绫诲瀷涓嶈兘涓虹┖", groups = AddGroup.class)
+ private String userStatus;
+
+ /**
+ * 鎬у埆
+ * <p>
+ * 浣跨敤ExcelDictFormat娉ㄨВ闇�瑕佽繘琛屼笅鎷夐�夌殑閮ㄥ垎
+ */
+ @ExcelProperty(value = "鎬у埆", index = 2, converter = ExcelDictConvert.class)
+ @ExcelDictFormat(dictType = "sys_user_sex")
+ @NotEmpty(message = "鎬у埆涓嶈兘涓虹┖", groups = AddGroup.class)
+ private String gender;
+
+ /**
+ * 鎵嬫満鍙�
+ */
+ @ExcelProperty(value = "鎵嬫満鍙�", index = 3)
+ @NotEmpty(message = "鎵嬫満鍙蜂笉鑳戒负绌�", groups = AddGroup.class)
+ private String phoneNumber;
+
+ /**
+ * Email
+ */
+ @ExcelProperty(value = "Email", index = 4)
+ @NotEmpty(message = "Email涓嶈兘涓虹┖", groups = AddGroup.class)
+ private String email;
+
+ /**
+ * 鐪�
+ * <p>
+ * 绾ц仈涓嬫媺锛屼粎鍒ゆ柇鏄惁閫変簡
+ */
+ @ExcelProperty(value = "鐪�", index = 5)
+ @NotNull(message = "鐪佷笉鑳戒负绌�", groups = AddGroup.class)
+ private String province;
+
+ /**
+ * 鏁版嵁搴撲腑鐨勭渷ID
+ * </p>
+ * 澶勭悊瀹屾瘯鍚庡啀鍒ゆ柇鏄惁甯傛纭殑鍊�
+ */
+ @NotNull(message = "璇峰嬁鎵嬪姩杈撳叆", groups = EditGroup.class)
+ private Integer provinceId;
+
+ /**
+ * 甯�
+ * <p>
+ * 绾ц仈涓嬫媺
+ */
+ @ExcelProperty(value = "甯�", index = 6)
+ @NotNull(message = "甯備笉鑳戒负绌�", groups = AddGroup.class)
+ private String city;
+
+ /**
+ * 鏁版嵁搴撲腑鐨勫競ID
+ */
+ @NotNull(message = "璇峰嬁鎵嬪姩杈撳叆", groups = EditGroup.class)
+ private Integer cityId;
+
+ /**
+ * 鍘�
+ * <p>
+ * 绾ц仈涓嬫媺
+ */
+ @ExcelProperty(value = "鍘�", index = 7)
+ @NotNull(message = "鍘夸笉鑳戒负绌�", groups = AddGroup.class)
+ private String area;
+
+ /**
+ * 鏁版嵁搴撲腑鐨勫幙ID
+ */
+ @NotNull(message = "璇峰嬁鎵嬪姩杈撳叆", groups = EditGroup.class)
+ private Integer areaId;
+}
diff --git a/ruoyi-modules/ruoyi-demo/src/main/java/org/dromara/demo/listener/ExportDemoListener.java b/ruoyi-modules/ruoyi-demo/src/main/java/org/dromara/demo/listener/ExportDemoListener.java
new file mode 100644
index 0000000..7bd4e1e
--- /dev/null
+++ b/ruoyi-modules/ruoyi-demo/src/main/java/org/dromara/demo/listener/ExportDemoListener.java
@@ -0,0 +1,68 @@
+package org.dromara.demo.listener;
+
+import cn.hutool.core.util.NumberUtil;
+import com.alibaba.excel.context.AnalysisContext;
+import org.dromara.common.core.utils.ValidatorUtils;
+import org.dromara.common.core.validate.AddGroup;
+import org.dromara.common.core.validate.EditGroup;
+import org.dromara.common.excel.core.DefaultExcelListener;
+import org.dromara.common.excel.core.DropDownOptions;
+import org.dromara.demo.domain.vo.ExportDemoVo;
+
+import java.util.List;
+
+/**
+ * Excel甯︿笅鎷夋鐨勮В鏋愬鐞嗗櫒
+ *
+ * @author Emil.Zhang
+ */
+public class ExportDemoListener extends DefaultExcelListener<ExportDemoVo> {
+
+ public ExportDemoListener() {
+ // 鏄剧ず浣跨敤鏋勯�犲嚱鏁帮紝鍚﹀垯灏嗗鑷寸┖鎸囬拡
+ super(true);
+ }
+
+ @Override
+ public void invoke(ExportDemoVo data, AnalysisContext context) {
+ // 鍏堟牎楠屽繀濉�
+ ValidatorUtils.validate(data, AddGroup.class);
+
+ // 澶勭悊绾ц仈涓嬫媺鐨勯儴鍒�
+ String province = data.getProvince();
+ String city = data.getCity();
+ String area = data.getArea();
+ // 鏈鐢ㄦ埛閫夋嫨鐨勭渷
+ List<String> thisRowSelectedProvinceOption = DropDownOptions.analyzeOptionValue(province);
+ if (thisRowSelectedProvinceOption.size() == 2) {
+ String provinceIdStr = thisRowSelectedProvinceOption.get(1);
+ if (NumberUtil.isNumber(provinceIdStr)) {
+ // 涓ユ牸瑕佹眰鏁版嵁鐨勮瘽鍙互鍦ㄨ繖閲屽仛涓庢暟鎹簱鐩稿叧鐨勫垽鏂�
+ // 渚嬪鍒ゆ柇鐪佷俊鎭槸鍚﹀湪鏁版嵁搴撲腑瀛樺湪绛夛紝寤鸿缁撳悎RedisCache鍋氱紦瀛�10s锛屽噺灏戞暟鎹簱璋冪敤
+ data.setProvinceId(Integer.parseInt(provinceIdStr));
+ }
+ }
+ // 鏈鐢ㄦ埛閫夋嫨鐨勫競
+ List<String> thisRowSelectedCityOption = DropDownOptions.analyzeOptionValue(city);
+ if (thisRowSelectedCityOption.size() == 2) {
+ String cityIdStr = thisRowSelectedCityOption.get(1);
+ if (NumberUtil.isNumber(cityIdStr)) {
+ data.setCityId(Integer.parseInt(cityIdStr));
+ }
+ }
+ // 鏈鐢ㄦ埛閫夋嫨鐨勫幙
+ List<String> thisRowSelectedAreaOption = DropDownOptions.analyzeOptionValue(area);
+ if (thisRowSelectedAreaOption.size() == 2) {
+ String areaIdStr = thisRowSelectedAreaOption.get(1);
+ if (NumberUtil.isNumber(areaIdStr)) {
+ data.setAreaId(Integer.parseInt(areaIdStr));
+ }
+ }
+
+ // 澶勭悊瀹屾瘯浠ュ悗鍒ゆ柇鏄惁绗﹀悎瑙勫垯
+ ValidatorUtils.validate(data, EditGroup.class);
+
+ // 娣诲姞鍒板鐞嗙粨鏋滀腑
+ getExcelResult().getList().add(data);
+ }
+}
diff --git a/ruoyi-modules/ruoyi-demo/src/main/java/org/dromara/demo/service/IExportExcelService.java b/ruoyi-modules/ruoyi-demo/src/main/java/org/dromara/demo/service/IExportExcelService.java
new file mode 100644
index 0000000..4dfa5ef
--- /dev/null
+++ b/ruoyi-modules/ruoyi-demo/src/main/java/org/dromara/demo/service/IExportExcelService.java
@@ -0,0 +1,18 @@
+package org.dromara.demo.service;
+
+import jakarta.servlet.http.HttpServletResponse;
+
+/**
+ * 瀵煎嚭涓嬫媺妗咵xcel绀轰緥
+ *
+ * @author Emil.Zhang
+ */
+public interface IExportExcelService {
+
+ /**
+ * 瀵煎嚭涓嬫媺妗�
+ *
+ * @param response /
+ */
+ void exportWithOptions(HttpServletResponse response);
+}
diff --git a/ruoyi-modules/ruoyi-demo/src/main/java/org/dromara/demo/service/impl/ExportExcelServiceImpl.java b/ruoyi-modules/ruoyi-demo/src/main/java/org/dromara/demo/service/impl/ExportExcelServiceImpl.java
new file mode 100644
index 0000000..0240e02
--- /dev/null
+++ b/ruoyi-modules/ruoyi-demo/src/main/java/org/dromara/demo/service/impl/ExportExcelServiceImpl.java
@@ -0,0 +1,222 @@
+package org.dromara.demo.service.impl;
+
+import cn.hutool.core.util.StrUtil;
+import jakarta.servlet.http.HttpServletResponse;
+import lombok.Data;
+import lombok.RequiredArgsConstructor;
+import org.dromara.common.core.enums.UserStatus;
+import org.dromara.common.core.utils.StreamUtils;
+import org.dromara.common.excel.core.DropDownOptions;
+import org.dromara.common.excel.utils.ExcelUtil;
+import org.dromara.demo.domain.vo.ExportDemoVo;
+import org.dromara.demo.service.IExportExcelService;
+import org.springframework.stereotype.Service;
+
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Map;
+import java.util.stream.Collectors;
+
+/**
+ * 瀵煎嚭涓嬫媺妗咵xcel绀轰緥
+ *
+ * @author Emil.Zhang
+ */
+@Service
+@RequiredArgsConstructor
+public class ExportExcelServiceImpl implements IExportExcelService {
+
+ @Override
+ public void exportWithOptions(HttpServletResponse response) {
+ // 鍒涘缓琛ㄦ牸鏁版嵁锛屼笟鍔′腑涓�鑸�氳繃鏁版嵁搴撴煡璇�
+ List<ExportDemoVo> excelDataList = new ArrayList<>();
+ for (int i = 0; i < 3; i++) {
+ // 妯℃嫙鏁版嵁搴撲腑鐨勪竴鏉℃暟鎹�
+ ExportDemoVo everyRowData = new ExportDemoVo();
+ everyRowData.setNickName("鐢ㄦ埛-" + i);
+ everyRowData.setUserStatus(UserStatus.OK.getCode());
+ everyRowData.setGender("1");
+ everyRowData.setPhoneNumber(String.format("175%08d", i));
+ everyRowData.setEmail(String.format("175%08d", i) + "@163.com");
+ everyRowData.setProvinceId(i);
+ everyRowData.setCityId(i);
+ everyRowData.setAreaId(i);
+ excelDataList.add(everyRowData);
+ }
+
+ // 閫氳繃@ExcelIgnoreUnannotated閰嶅悎@ExcelProperty鍚堢悊鏄剧ず闇�瑕佺殑鍒�
+ // 骞堕�氳繃@DropDown娉ㄨВ鎸囧畾涓嬫媺鍊硷紝鎴栬�呴�氳繃鍒涘缓ExcelOptions鏉ユ寚瀹氫笅鎷夋
+ // 浣跨敤ExcelOptions鏃跺缓璁寚瀹氬垪index锛岄槻姝㈠嚭鐜颁笅鎷夊垪瑙ｆ瀽涓嶅榻�
+
+ // 棣栧厛浠庢暟鎹簱涓煡璇笅鎷夋鍐呯殑鍙�夐」
+ // 杩欓噷妯℃嫙鏌ヨ缁撴灉
+ List<DemoCityData> provinceList = getProvinceList(),
+ cityList = getCityList(provinceList),
+ areaList = getAreaList(cityList);
+ int provinceIndex = 5, cityIndex = 6, areaIndex = 7;
+
+ DropDownOptions provinceToCity = DropDownOptions.buildLinkedOptions(
+ provinceList,
+ provinceIndex,
+ cityList,
+ cityIndex,
+ DemoCityData::getId,
+ DemoCityData::getPid,
+ everyOptions -> DropDownOptions.createOptionValue(
+ everyOptions.getName(),
+ everyOptions.getId()
+ )
+ );
+
+ DropDownOptions cityToArea = DropDownOptions.buildLinkedOptions(
+ cityList,
+ cityIndex,
+ areaList,
+ areaIndex,
+ DemoCityData::getId,
+ DemoCityData::getPid,
+ everyOptions -> DropDownOptions.createOptionValue(
+ everyOptions.getName(),
+ everyOptions.getId()
+ )
+ );
+
+ // 鎶婃墍鏈夌殑涓嬫媺妗嗗瓨鍌�
+ List<DropDownOptions> options = new ArrayList<>();
+ options.add(provinceToCity);
+ options.add(cityToArea);
+
+ // 鍒版涓烘鎵�鏈夌殑涓嬫媺妗嗗彲閫夐」宸插叏閮ㄩ厤缃畬姣�
+
+ // 鎺ヤ笅鏉ラ渶瑕佸皢Excel涓殑灞曠ず鏁版嵁杞崲涓哄搴旂殑涓嬫媺閫�
+ List<ExportDemoVo> outList = StreamUtils.toList(excelDataList, everyRowData -> {
+ // 鍙渶瑕佸鐞嗘病鏈変娇鐢ˊExcelDictFormat娉ㄨВ鐨勪笅鎷夋
+ // 涓�鑸潵璇达紝鍙互鐩存帴鍦ㄦ暟鎹簱鏌ヨ鍗虫煡璇㈠嚭鐪佸競鍘夸俊鎭紝杩欓噷閫氳繃妯℃嫙鎿嶄綔璧嬪��
+ everyRowData.setProvince(buildOptions(provinceList, everyRowData.getProvinceId()));
+ everyRowData.setCity(buildOptions(cityList, everyRowData.getCityId()));
+ everyRowData.setArea(buildOptions(areaList, everyRowData.getAreaId()));
+ return everyRowData;
+ });
+
+ ExcelUtil.exportExcel(outList, "涓嬫媺妗嗙ず渚�", ExportDemoVo.class, response, options);
+ }
+
+ private String buildOptions(List<DemoCityData> cityDataList, Integer id) {
+ Map<Integer, List<DemoCityData>> groupByIdMap =
+ cityDataList.stream().collect(Collectors.groupingBy(DemoCityData::getId));
+ if (groupByIdMap.containsKey(id)) {
+ DemoCityData demoCityData = groupByIdMap.get(id).get(0);
+ return DropDownOptions.createOptionValue(demoCityData.getName(), demoCityData.getId());
+ } else {
+ return StrUtil.EMPTY;
+ }
+ }
+
+ /**
+ * 妯℃嫙鏌ヨ鏁版嵁搴撴搷浣�
+ *
+ * @return /
+ */
+ private List<DemoCityData> getProvinceList() {
+ List<DemoCityData> provinceList = new ArrayList<>();
+
+ // 瀹為檯涓氬姟涓竴鑸噰鐢ㄦ暟鎹簱璇诲彇鐨勫舰寮忥紝杩欓噷鐩存帴鎷兼帴鍒涘缓
+ provinceList.add(new DemoCityData(0, null, "瀹夊窘鐪�"));
+ provinceList.add(new DemoCityData(1, null, "姹熻嫃鐪�"));
+
+ return provinceList;
+ }
+
+ /**
+ * 妯℃嫙鏌ユ壘鏁版嵁搴撴搷浣滐紝闇�瑕佽繛甯︽煡璇㈠嚭鐪佺殑鏁版嵁
+ *
+ * @param provinceList 妯℃嫙鐨勭埗鐪佹暟鎹�
+ * @return /
+ */
+ private List<DemoCityData> getCityList(List<DemoCityData> provinceList) {
+ List<DemoCityData> cityList = new ArrayList<>();
+
+ // 瀹為檯涓氬姟涓竴鑸噰鐢ㄦ暟鎹簱璇诲彇鐨勫舰寮忥紝杩欓噷鐩存帴鎷兼帴鍒涘缓
+ cityList.add(new DemoCityData(0, 0, "鍚堣偉甯�"));
+ cityList.add(new DemoCityData(1, 0, "鑺滄箹甯�"));
+ cityList.add(new DemoCityData(2, 1, "鍗椾含甯�"));
+ cityList.add(new DemoCityData(3, 1, "鏃犻敗甯�"));
+ cityList.add(new DemoCityData(4, 1, "寰愬窞甯�"));
+
+ selectParentData(provinceList, cityList);
+
+ return cityList;
+ }
+
+ /**
+ * 妯℃嫙鏌ユ壘鏁版嵁搴撴搷浣滐紝闇�瑕佽繛甯︽煡璇㈠嚭甯傜殑鏁版嵁
+ *
+ * @param cityList 妯℃嫙鐨勭埗甯傛暟鎹�
+ * @return /
+ */
+ private List<DemoCityData> getAreaList(List<DemoCityData> cityList) {
+ List<DemoCityData> areaList = new ArrayList<>();
+
+ // 瀹為檯涓氬姟涓竴鑸噰鐢ㄦ暟鎹簱璇诲彇鐨勫舰寮忥紝杩欓噷鐩存帴鎷兼帴鍒涘缓
+ areaList.add(new DemoCityData(0, 0, "鐟舵捣鍖�"));
+ areaList.add(new DemoCityData(1, 0, "搴愭睙鍖�"));
+ areaList.add(new DemoCityData(2, 1, "鍗楀畞鍘�"));
+ areaList.add(new DemoCityData(3, 1, "闀滄箹鍖�"));
+ areaList.add(new DemoCityData(4, 2, "鐜勬鍖�"));
+ areaList.add(new DemoCityData(5, 2, "绉︽樊鍖�"));
+ areaList.add(new DemoCityData(6, 3, "瀹滃叴甯�"));
+ areaList.add(new DemoCityData(7, 3, "鏂板惔鍖�"));
+ areaList.add(new DemoCityData(8, 4, "榧撴ゼ鍖�"));
+ areaList.add(new DemoCityData(9, 4, "涓板幙"));
+
+ selectParentData(cityList, areaList);
+
+ return areaList;
+ }
+
+ /**
+ * 妯℃嫙鏁版嵁搴撶殑鏌ヨ鐖舵暟鎹搷浣�
+ *
+ * @param parentList /
+ * @param sonList /
+ */
+ private void selectParentData(List<DemoCityData> parentList, List<DemoCityData> sonList) {
+ Map<Integer, List<DemoCityData>> parentGroupByIdMap =
+ parentList.stream().collect(Collectors.groupingBy(DemoCityData::getId));
+
+ sonList.forEach(everySon -> {
+ if (parentGroupByIdMap.containsKey(everySon.getPid())) {
+ everySon.setPData(parentGroupByIdMap.get(everySon.getPid()).get(0));
+ }
+ });
+ }
+
+ /**
+ * 妯℃嫙鐨勬暟鎹簱鐪佸競鍘�
+ */
+ @Data
+ private static class DemoCityData {
+ /**
+ * 鏁版嵁搴搃d瀛楁
+ */
+ private Integer id;
+ /**
+ * 鏁版嵁搴損id瀛楁
+ */
+ private Integer pid;
+ /**
+ * 鏁版嵁搴搉ame瀛楁
+ */
+ private String name;
+ /**
+ * MyBatisPlus杩炲甫鏌ヨ鐖舵暟鎹�
+ */
+ private DemoCityData pData;
+
+ public DemoCityData(Integer id, Integer pid, String name) {
+ this.id = id;
+ this.pid = pid;
+ this.name = name;
+ }
+ }
+}
diff --git a/ruoyi-modules/ruoyi-system/src/main/java/org/dromara/system/service/impl/SysDictTypeServiceImpl.java b/ruoyi-modules/ruoyi-system/src/main/java/org/dromara/system/service/impl/SysDictTypeServiceImpl.java
index 60431ec..971b0b2 100644
--- a/ruoyi-modules/ruoyi-system/src/main/java/org/dromara/system/service/impl/SysDictTypeServiceImpl.java
+++ b/ruoyi-modules/ruoyi-system/src/main/java/org/dromara/system/service/impl/SysDictTypeServiceImpl.java
@@ -265,4 +265,10 @@
}
}
+ @Override
+ public Map<String, String> getAllDictByDictType(String dictType) {
+ List<SysDictDataVo> list = selectDictDataByType(dictType);
+ return StreamUtils.toMap(list, SysDictDataVo::getDictValue, SysDictDataVo::getDictLabel);
+ }
+
}
--
Gitblit v1.9.3