add 增加 excel 导出下拉框功能

疯狂的狮子Li

2023-06-25 4a9aafe8530e945bd52d8093944c4bcb1a89c28f

add 增加 excel 导出下拉框功能

 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为key，dictLabel为值组成的Map
     */
    Map<String, String> getAllDictByDictType(String dictType);

}

 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>
 * æ³¨æ„ï¼šä¸ºç¡®ä¿ä¸‹æ‹‰æ¡†è§£æžæ­£ç¡®ï¼Œä¼ å€¼åŠ¡å¿…使用createOptionValue()做为值的拼接
 *
 * @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<>();
    /**
     * äºŒçº§ä¸‹æ‹‰æ‰€åŒ…含的数据Map
     * <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<>();
        // çˆ¶çº§ä¾æ®è‡ªå·±çš„ID分组
        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);
                // æå–名称和ID作为Key
                String key = howToBuildEveryOption.apply(parentObj);
                // Key对应的Value
                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;
    }
}

 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>
 * è€ƒè™‘到下拉选过多可能导致Excel打开缓慢的问题，只校验前1000行
 * <p>
 * å³åªæœ‰å‰1000行的数据可以用下拉框，超出的自行通过限制数据量的形式，第二次输出
 *
 * @author Emil.Zhang
 */
@Slf4j
public class ExcelDownHandler implements SheetWriteHandler {

    /**
     * Excel表格中的列名英文
     * ä»…为了解析列英文，禁止修改
     */
    private static final String EXCEL_COLUMN_NAME = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
    /**
     * å•é€‰æ•°æ®Sheet名
     */
    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选项
     * å¦‚果有且设置了value值，则将其直接置为下拉可选项
     * <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 åˆ—index
     * @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);
            // æ•°æ®éªŒè¯ä¸ºåºåˆ—模式，引用到每一个主表中的二级选项位置
            // åˆ›å»ºå­é¡¹çš„名称管理器，只是为了使得Excel可以识别到数据
            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>
     * ç”±äºŽä¸‹æ‹‰æ¡†å¯é€‰å€¼æ•°é‡è¿‡å¤šï¼Œä¸ºæå‡Excel打开效率，使用额外表格形式做下拉
     *
     * @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>依据列index获取列名英文</h2>
     * ä¾æ®åˆ—index转换为Excel中的列名英文
     * <p>例如第1列，index为0，解析出来为A列</p>
     * ç¬¬27列，index为26，解析为AA列
     * <p>第28列，index为27，解析为AB列</p>
     *
     * @param columnIndex åˆ—index
     * @return åˆ—index所在得英文名
     */
    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;
    }
}

 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;
                    }
                }

 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 {

 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;
}

 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);
    }
}

 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;

/**
 * å¯¼å‡ºä¸‹æ‹‰æ¡†Excel示例
 *
 * @author Emil.Zhang
 */
public interface IExportExcelService {

    /**
     * å¯¼å‡ºä¸‹æ‹‰æ¡†
     *
     * @param response /
     */
    void exportWithOptions(HttpServletResponse response);
}

 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;

/**
 * å¯¼å‡ºä¸‹æ‹‰æ¡†Excel示例
 *
 * @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 {
        /**
         * æ•°æ®åº“id字段
         */
        private Integer id;
        /**
         * æ•°æ®åº“pid字段
         */
        private Integer pid;
        /**
         * æ•°æ®åº“name字段
         */
        private String name;
        /**
         * MyBatisPlus连带查询父数据
         */
        private DemoCityData pData;

        public DemoCityData(Integer id, Integer pid, String name) {
            this.id = id;
            this.pid = pid;
            this.name = name;
        }
    }
}

 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);
    }

}