/**
 * Excel相關(guān)處理
 *
 * @author 魯子狄
 */
@NoArgsConstructor(access = AccessLevel.PRIVATE)
public class ExcelUtil {
	/**
     * 導(dǎo)出 Excel 文件
     *
     * @param list      導(dǎo)出數(shù)據(jù)集合
     * @param sheetName 工作表的名稱(chēng)
     * @param heads     表頭
     * @param response  響應(yīng)體
     */
    public static <T> void exportExcel(List<T> list, Class<T> clazz, String sheetName, List<List<String>> heads, HttpServletResponse response) {
        try {
            // 將對(duì)象列表轉(zhuǎn)換為自定義的數(shù)據(jù)列表
            List<List<Object>> data = ExcelUtil.convertObjectsToExcelRows(list, heads);
            // 重置響應(yīng)體，設(shè)置響應(yīng)頭
            ExcelUtil.resetResponse(sheetName, response);
            // 獲取響應(yīng)輸出流
            ServletOutputStream os = response.getOutputStream();
            // 導(dǎo)出 Excel 文件
            ExcelUtil.exportExcel(data, clazz, heads, sheetName, false, os, null);
        } catch (IOException e) {
            throw new RuntimeException("導(dǎo)出 Excel 異常");
        }
    }
    /**
     * 重置響應(yīng)體
     */
    private static void resetResponse(String sheetName, HttpServletResponse response) throws UnsupportedEncodingException {
        String filename = ExcelUtil.encodingFilename(sheetName);
        FileUtils.setAttachmentResponseHeader(response, filename);
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
    }
    /**
     * 導(dǎo)出 Excel 文件
     *
     * @param data      導(dǎo)出數(shù)據(jù)集合
     * @param heads     表頭
     * @param sheetName 工作表的名稱(chēng)
     * @param merge     是否合并單元格
     * @param os        輸出流
     * @param options   級(jí)聯(lián)下拉選內(nèi)容
     */
    public static <T> void exportExcel(List<List<Object>> data, Class<T> clazz, List<List<String>> heads, String sheetName, boolean merge,
                                       OutputStream os, List<DropDownOptions> options) {
        ExcelWriterSheetBuilder builder = EasyExcel.write(os, clazz)
            .autoCloseStream(false)
            // 自動(dòng)適配列寬
            .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
            // 大數(shù)值自動(dòng)轉(zhuǎn)換，防止失真
            .registerConverter(new ExcelBigNumberConvert())
            .sheet(sheetName)
            .head(heads);
        if (merge) {
            // 注冊(cè)合并單元格處理器
            builder.registerWriteHandler(new CellMergeStrategy(data, true));
        }
        // 添加下拉框操作
        builder.registerWriteHandler(new ExcelDownHandler(options, heads));
        // 寫(xiě)入數(shù)據(jù)
        builder.doWrite(data);
    }
    /**
     * convertObjectsToExcelRows 將對(duì)象列表轉(zhuǎn)換為 Excel 行數(shù)據(jù)
     *
     * @param list  對(duì)象集合
     * @param heads 表頭
     * @return {@link List<List<Object>>}  Excel 行數(shù)據(jù)
     */
    private static <T> List<List<Object>> convertObjectsToExcelRows(List<T> list, List<List<String>> heads) {
        return list.stream()
            // 將對(duì)象轉(zhuǎn)換為 Map
            .map(ExcelUtil::convertObjectToMap)
            .map(map -> {
                List<Object> row = new ArrayList<>();
                for (List<String> header : heads) {
                    // 假設(shè)表頭只有一個(gè)元素
                    String key = header.get(0);
                    // 根據(jù)表頭獲取對(duì)應(yīng)的值
                    row.add(map.get(key));
                }
                return row;
            })
            .toList();
    }
    /**
     * convertObjectToMap   將對(duì)象轉(zhuǎn)換為 Map，鍵為 @ExcelProperty 注解中的列標(biāo)題，值為字段值。
     *
     * @param vo 對(duì)象
     * @return {@link Map<String,Object>} 包含列標(biāo)題和字段值的 Map
     */
    private static Map<String, Object> convertObjectToMap(Object vo) {
        // 獲取對(duì)象的所有字段
        return Arrays.stream(vo.getClass().getDeclaredFields())
            // 過(guò)濾帶有 @ExcelProperty 注解的字段
            .filter(field -> field.isAnnotationPresent(ExcelProperty.class))
            .map(field -> {
                try {
                    // 設(shè)置字段可訪問(wèn)
                    field.setAccessible(true);
                    // 獲取字段名和值
                    String fieldName = field.getDeclaredAnnotation(ExcelProperty.class).value()[0];
                    Object fieldValue = field.get(vo);
                    // 檢查是否有 @ExcelDictFormat 注解
                    ExcelDictFormat dictFormat = field.getDeclaredAnnotation(ExcelDictFormat.class);
                    if (dictFormat != null && fieldValue != null) {
                        fieldValue = convertExcelDictFormat(fieldValue, dictFormat);
                    }
                    return new AbstractMap.SimpleEntry<>(fieldName, fieldValue);
                } catch (IllegalAccessException e) {
                    throw new IllegalStateException("獲取字段值失敗: " + field.getName(), e);
                }
            })
            // 過(guò)濾掉值為 null 的條目
            .filter(entry -> entry.getValue() != null)
            .collect(Collectors.toMap(
                // 鍵為字段名
                AbstractMap.SimpleEntry::getKey,
                // 值為字段值
                AbstractMap.SimpleEntry::getValue,
                // 如果鍵重復(fù)，保留第一個(gè)值
                (existing, replacement) -> existing,
                // 使用 HashMap 存儲(chǔ)結(jié)果
                HashMap::new
            ));
    }
    /**
     * convertToExcelData   根據(jù) @ExcelDictFormat 注解轉(zhuǎn)換數(shù)據(jù)
     *
     * @param value       字段值
     * @param dictFormat  注解信息
     * @return 轉(zhuǎn)換后的值
     */
    private static String convertExcelDictFormat(Object value, ExcelDictFormat dictFormat) {
        String type = dictFormat.dictType();
        String label;
        if (StringUtils.isBlank(type)) {
            label = convertByExp(value.toString(), dictFormat.readConverterExp(), dictFormat.separator());
        } else {
            label =  SpringUtils.getBean(DictService.class).getDictLabel(type, value.toString(), dictFormat.separator());
        }
        return label;
    }
}
    /**
     * 解析導(dǎo)出值 0=男,1=女,2=未知
     *
     * @param propertyValue 參數(shù)值
     * @param converterExp  翻譯注解
     * @param separator     分隔符
     * @return 解析后值
     */
    public static String convertByExp(String propertyValue, String converterExp, String separator) {
        StringBuilder propertyString = new StringBuilder();
        String[] convertSource = converterExp.split(StringUtils.SEPARATOR);
        for (String item : convertSource) {
            String[] itemArray = item.split("=");
            if (StringUtils.containsAny(propertyValue, separator)) {
                for (String value : propertyValue.split(separator)) {
                    if (itemArray[0].equals(value)) {
                        propertyString.append(itemArray[1] + separator);
                        break;
                    }
                }
            } else {
                if (itemArray[0].equals(propertyValue)) {
                    return itemArray[1];
                }
            }
        }
        return StringUtils.stripEnd(propertyString.toString(), separator);
    }

/**
 * 大數(shù)值轉(zhuǎn)換
 * Excel 數(shù)值長(zhǎng)度位15位 大于15位的數(shù)值轉(zhuǎn)換位字符串
 */
@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;
    }
}

**
 * 列值重復(fù)合并策略
 */
@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;
        // 行合并開(kāi)始下標(biāo)
        this.rowIndex = hasTitle ? 1 : 0;
        this.cellList = handle(list, hasTitle);
    }
    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
        //單元格寫(xiě)入了,遍歷合并區(qū)域,如果該Cell在區(qū)域內(nèi),但非首行,則清空
        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) {
        //當(dāng)前表格寫(xiě)完后，統(tǒng)一寫(xiě)入
        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)) {
                        // 空值跳過(guò)不合并
                        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)) {
            //比對(duì)當(dāng)前l(fā)ist(i)和list(i - 1)的各個(gè)屬性值一一比對(duì) 如果全為真 則為真
            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)) {
                    //依賴(lài)字段如有任一不等值,則標(biāo)記為不可合并
                    isMerge = false;
                }
            }
        }
        return isMerge;
    }
    @Data
    @AllArgsConstructor
    static class RepeatCell {
        private Object value;
        private int current;
    }
}

/**
 * <h1>Excel表格下拉選操作</h1>
 * 考慮到下拉選過(guò)多可能導(dǎo)致Excel打開(kāi)緩慢的問(wèn)題，只校驗(yàn)前1000行
 * <p>
 * 即只有前1000行的數(shù)據(jù)可以用下拉框，超出的自行通過(guò)限制數(shù)據(jù)量的形式，第二次輸出
 */
@Slf4j
public class ExcelDownHandler implements SheetWriteHandler {
    /**
     * Excel表格中的列名英文
     * 僅為了解析列英文，禁止修改
     */
    private static final String EXCEL_COLUMN_NAME = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
    /**
     * 單選數(shù)據(jù)Sheet名
     */
    private static final String OPTIONS_SHEET_NAME = "options";
    /**
     * 聯(lián)動(dòng)選擇數(shù)據(jù)Sheet名的頭
     */
    private static final String LINKED_OPTIONS_SHEET_NAME = "linkedOptions";
    /**
     * 下拉可選項(xiàng)
     */
    private final List<DropDownOptions> dropDownOptions;
    /**
     * 表頭
     */
    private final List<List<String>> heads;
    /**
     * 當(dāng)前單選進(jìn)度
     */
    private int currentOptionsColumnIndex;
    /**
     * 當(dāng)前聯(lián)動(dòng)選擇進(jìn)度
     */
    private int currentLinkedOptionsSheetIndex;
    private final DictService dictService;
    public ExcelDownHandler(List<DropDownOptions> options, List<List<String>> heads) {
        dropDownOptions = options;
        this.heads = heads;
        currentOptionsColumnIndex = 0;
        currentLinkedOptionsSheetIndex = 0;
        dictService = SpringUtils.getBean(DictService.class);
    }
    /**
     * <h2>開(kāi)始創(chuàng)建下拉數(shù)據(jù)</h2>
     * 1.通過(guò)解析傳入的@ExcelProperty同級(jí)是否標(biāo)注有@DropDown選項(xiàng)
     * 如果有且設(shè)置了value值，則將其直接置為下拉可選項(xiàng)
     * <p>
     * 2.或者在調(diào)用ExcelUtil時(shí)指定了可選項(xiàng)，將依據(jù)傳入的可選項(xiàng)做下拉
     * <p>
     * 3.二者并存，注意調(diào)用方式
     */
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        // 開(kāi)始設(shè)置下拉框 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();
            // 循環(huán)實(shí)體中的每個(gè)屬性
            // 可選的下拉值
            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)) {
                    // 如果傳遞了字典名，則依據(jù)字典建立下拉
                    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)) {
                // 如過(guò)自定義表頭不為空
                if (CollUtil.isNotEmpty(heads)) {
                    // 獲取對(duì)應(yīng)的下拉表頭的下標(biāo)
                    index = IntStream.range(0, heads.size())
                        .filter(i -> wrapper.getHeads()[0].equals(heads.get(i).get(0)))
                        .findFirst().orElse(0);
                }
                // 僅當(dāng)下拉可選項(xiàng)不為空時(shí)執(zhí)行
                if (options.size() > 20) {
                    // 這里限制如果可選項(xiàng)大于20，則使用額外表形式
                    dropDownWithSheet(helper, workbook, sheet, index, options);
                } else {
                    // 否則使用固定值形式
                    dropDownWithSimple(helper, sheet, index, options);
                }
            }
        }
        if (CollUtil.isEmpty(dropDownOptions)) {
            return;
        }
        dropDownOptions.forEach(everyOptions -> {
            // 如果傳遞了下拉框選擇器參數(shù)
            if (!everyOptions.getNextOptions().isEmpty()) {
                // 當(dāng)二級(jí)選項(xiàng)不為空時(shí)，使用額外關(guān)聯(lián)表的形式
                dropDownLinkedOptions(helper, workbook, sheet, everyOptions);
            } else if (everyOptions.getOptions().size() > 10) {
                // 當(dāng)一級(jí)選項(xiàng)參數(shù)個(gè)數(shù)大于10，使用額外表的形式
                dropDownWithSheet(helper, workbook, sheet, everyOptions.getIndex(), everyOptions.getOptions());
            } else if (!everyOptions.getOptions().isEmpty()) {
                // 當(dāng)一級(jí)選項(xiàng)個(gè)數(shù)不為空，使用默認(rèn)形式
                dropDownWithSimple(helper, sheet, everyOptions.getIndex(), everyOptions.getOptions());
            }
        });
    }
    /**
     * <h2>簡(jiǎn)單下拉框</h2>
     * 直接將可選項(xiàng)拼接為指定列的數(shù)據(jù)校驗(yàn)值
     *
     * @param celIndex 列index
     * @param value    下拉選可選值
     */
    private void dropDownWithSimple(DataValidationHelper helper, Sheet sheet, Integer celIndex, List<String> value) {
        if (ObjectUtil.isEmpty(value)) {
            return;
        }
        markOptionsToSheet(helper, sheet, celIndex, helper.createExplicitListConstraint(ArrayUtil.toArray(value, String.class)));
    }
    /**
     * <h2>額外表格形式的級(jí)聯(lián)下拉框</h2>
     *
     * @param options 額外表格形式存儲(chǔ)的下拉可選項(xiàng)
     */
    private void dropDownLinkedOptions(DataValidationHelper helper, Workbook workbook, Sheet sheet, DropDownOptions options) {
        String linkedOptionsSheetName = String.format("%s_%d", ExcelDownHandler.LINKED_OPTIONS_SHEET_NAME, currentLinkedOptionsSheetIndex);
        // 創(chuàng)建聯(lián)動(dòng)下拉數(shù)據(jù)表
        Sheet linkedOptionsDataSheet = workbook.createSheet(WorkbookUtil.createSafeSheetName(linkedOptionsSheetName));
        // 將下拉表隱藏
        workbook.setSheetHidden(workbook.getSheetIndex(linkedOptionsDataSheet), true);
        // 完善橫向的一級(jí)選項(xiàng)數(shù)據(jù)表
        List<String> firstOptions = options.getOptions();
        Map<String, List<String>> secoundOptionsMap = options.getNextOptions();
        // 創(chuàng)建名稱(chēng)管理器
        Name name = workbook.createName();
        // 設(shè)置名稱(chēng)管理器的別名
        name.setNameName(linkedOptionsSheetName);
        // 以橫向第一行創(chuàng)建一級(jí)下拉拼接引用位置
        String firstOptionsFunction = String.format("%s!$%s$1:$%s$1",
            linkedOptionsSheetName,
            ExcelDownHandler.getExcelColumnName(0),
            ExcelDownHandler.getExcelColumnName(firstOptions.size())
        );
        // 設(shè)置名稱(chēng)管理器的引用位置
        name.setRefersToFormula(firstOptionsFunction);
        // 設(shè)置數(shù)據(jù)校驗(yàn)為序列模式，引用的是名稱(chēng)管理器中的別名
        markOptionsToSheet(helper, sheet, options.getIndex(), helper.createFormulaListConstraint(linkedOptionsSheetName));
        for (int columIndex = 0; columIndex < firstOptions.size(); columIndex++) {
            // 先提取主表中一級(jí)下拉的列名
            String firstOptionsColumnName = ExcelDownHandler.getExcelColumnName(columIndex);
            // 一次循環(huán)是每一個(gè)一級(jí)選項(xiàng)
            int finalI = columIndex;
            // 本次循環(huán)的一級(jí)選項(xiàng)值
            String thisFirstOptionsValue = firstOptions.get(columIndex);
            // 創(chuàng)建第一行的數(shù)據(jù)
            Optional.ofNullable(linkedOptionsDataSheet.getRow(0))
                // 如果不存在則創(chuàng)建第一行
                .orElseGet(() -> linkedOptionsDataSheet.createRow(finalI))
                // 第一行當(dāng)前列
                .createCell(columIndex)
                // 設(shè)置值為當(dāng)前一級(jí)選項(xiàng)值
                .setCellValue(thisFirstOptionsValue);
            // 第二行開(kāi)始，設(shè)置第二級(jí)別選項(xiàng)參數(shù)
            List<String> secondOptions = secoundOptionsMap.get(thisFirstOptionsValue);
            if (CollUtil.isEmpty(secondOptions)) {
                // 必須保證至少有一個(gè)關(guān)聯(lián)選項(xiàng)，否則將導(dǎo)致Excel解析錯(cuò)誤
                secondOptions = Collections.singletonList("暫無(wú)_0");
            }
            // 以該一級(jí)選項(xiàng)值創(chuàng)建子名稱(chēng)管理器
            Name sonName = workbook.createName();
            // 設(shè)置名稱(chēng)管理器的別名
            sonName.setNameName(thisFirstOptionsValue);
            // 以第二行該列數(shù)據(jù)拼接引用位置
            String sonFunction = String.format("%s!$%s$2:$%s$%d",
                linkedOptionsSheetName,
                firstOptionsColumnName,
                firstOptionsColumnName,
                secondOptions.size() + 1
            );
            // 設(shè)置名稱(chēng)管理器的引用位置
            sonName.setRefersToFormula(sonFunction);
            // 數(shù)據(jù)驗(yàn)證為序列模式，引用到每一個(gè)主表中的二級(jí)選項(xiàng)位置
            // 創(chuàng)建子項(xiàng)的名稱(chēng)管理器，只是為了使得Excel可以識(shí)別到數(shù)據(jù)
            String mainSheetFirstOptionsColumnName = ExcelDownHandler.getExcelColumnName(options.getIndex());
            for (int i = 0; i < 100; i++) {
                // 以一級(jí)選項(xiàng)對(duì)應(yīng)的主體所在位置創(chuàng)建二級(jí)下拉
                String secondOptionsFunction = String.format("=INDIRECT(%s%d)", mainSheetFirstOptionsColumnName, i + 1);
                // 二級(jí)只能主表每一行的每一列添加二級(jí)校驗(yàn)
                markLinkedOptionsToSheet(helper, sheet, i, options.getNextIndex(), helper.createFormulaListConstraint(secondOptionsFunction));
            }
            for (int rowIndex = 0; rowIndex < secondOptions.size(); rowIndex++) {
                // 從第二行開(kāi)始填充二級(jí)選項(xiàng)
                int finalRowIndex = rowIndex + 1;
                int finalColumIndex = columIndex;
                Row row = Optional.ofNullable(linkedOptionsDataSheet.getRow(finalRowIndex))
                    // 沒(méi)有則創(chuàng)建
                    .orElseGet(() -> linkedOptionsDataSheet.createRow(finalRowIndex));
                Optional
                    // 在本級(jí)一級(jí)選項(xiàng)所在的列
                    .ofNullable(row.getCell(finalColumIndex))
                    // 不存在則創(chuàng)建
                    .orElseGet(() -> row.createCell(finalColumIndex))
                    // 設(shè)置二級(jí)選項(xiàng)值
                    .setCellValue(secondOptions.get(rowIndex));
            }
        }
        currentLinkedOptionsSheetIndex++;
    }
    /**
     * <h2>額外表格形式的普通下拉框</h2>
     * 由于下拉框可選值數(shù)量過(guò)多，為提升Excel打開(kāi)效率，使用額外表格形式做下拉
     *
     * @param celIndex 下拉選
     * @param value    下拉選可選值
     */
    private void dropDownWithSheet(DataValidationHelper helper, Workbook workbook, Sheet sheet, Integer celIndex, List<String> value) {
        // 創(chuàng)建下拉數(shù)據(jù)表
        Sheet simpleDataSheet = Optional.ofNullable(workbook.getSheet(WorkbookUtil.createSafeSheetName(ExcelDownHandler.OPTIONS_SHEET_NAME)))
            .orElseGet(() -> workbook.createSheet(WorkbookUtil.createSafeSheetName(ExcelDownHandler.OPTIONS_SHEET_NAME)));
        // 將下拉表隱藏
        workbook.setSheetHidden(workbook.getSheetIndex(simpleDataSheet), true);
        // 完善縱向的一級(jí)選項(xiàng)數(shù)據(jù)表
        for (int i = 0; i < value.size(); i++) {
            int finalI = i;
            // 獲取每一選項(xiàng)行，如果沒(méi)有則創(chuàng)建
            Row row = Optional.ofNullable(simpleDataSheet.getRow(i))
                .orElseGet(() -> simpleDataSheet.createRow(finalI));
            // 獲取本級(jí)選項(xiàng)對(duì)應(yīng)的選項(xiàng)列，如果沒(méi)有則創(chuàng)建
            Cell cell = Optional.ofNullable(row.getCell(currentOptionsColumnIndex))
                .orElseGet(() -> row.createCell(currentOptionsColumnIndex));
            // 設(shè)置值
            cell.setCellValue(value.get(i));
        }
        // 創(chuàng)建名稱(chēng)管理器
        Name name = workbook.createName();
        // 設(shè)置名稱(chēng)管理器的別名
        String nameName = String.format("%s_%d", ExcelDownHandler.OPTIONS_SHEET_NAME, celIndex);
        name.setNameName(nameName);
        // 以縱向第一列創(chuàng)建一級(jí)下拉拼接引用位置
        String function = String.format("%s!$%s$1:$%s$%d",
            ExcelDownHandler.OPTIONS_SHEET_NAME,
            ExcelDownHandler.getExcelColumnName(currentOptionsColumnIndex),
            ExcelDownHandler.getExcelColumnName(currentOptionsColumnIndex),
            value.size());
        // 設(shè)置名稱(chēng)管理器的引用位置
        name.setRefersToFormula(function);
        // 設(shè)置數(shù)據(jù)校驗(yàn)為序列模式，引用的是名稱(chēng)管理器中的別名
        markOptionsToSheet(helper, sheet, celIndex, helper.createFormulaListConstraint(nameName));
        currentOptionsColumnIndex++;
    }
    /**
     * 掛載下拉的列，僅限一級(jí)選項(xiàng)
     */
    private void markOptionsToSheet(DataValidationHelper helper, Sheet sheet, Integer celIndex,
                                    DataValidationConstraint constraint) {
        // 設(shè)置數(shù)據(jù)有效性加載在哪個(gè)單元格上,四個(gè)參數(shù)分別是：起始行、終止行、起始列、終止列
        CellRangeAddressList addressList = new CellRangeAddressList(1, 1000, celIndex, celIndex);
        markDataValidationToSheet(helper, sheet, constraint, addressList);
    }
    /**
     * 掛載下拉的列，僅限二級(jí)選項(xiàng)
     */
    private void markLinkedOptionsToSheet(DataValidationHelper helper, Sheet sheet, Integer rowIndex,
                                          Integer celIndex, DataValidationConstraint constraint) {
        // 設(shè)置數(shù)據(jù)有效性加載在哪個(gè)單元格上,四個(gè)參數(shù)分別是：起始行、終止行、起始列、終止列
        CellRangeAddressList addressList = new CellRangeAddressList(rowIndex, rowIndex, celIndex, celIndex);
        markDataValidationToSheet(helper, sheet, constraint, addressList);
    }
    /**
     * 應(yīng)用數(shù)據(jù)校驗(yàn)
     */
    private void markDataValidationToSheet(DataValidationHelper helper, Sheet sheet,
                                           DataValidationConstraint constraint, CellRangeAddressList addressList) {
        // 數(shù)據(jù)有效性對(duì)象
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        // 處理Excel兼容性問(wèn)題
        if (dataValidation instanceof XSSFDataValidation) {
            //數(shù)據(jù)校驗(yàn)
            dataValidation.setSuppressDropDownArrow(true);
            //錯(cuò)誤提示
            dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
            dataValidation.createErrorBox("提示", "此值與單元格定義數(shù)據(jù)不一致");
            dataValidation.setShowErrorBox(true);
            //選定提示
            dataValidation.createPromptBox("填寫(xiě)說(shuō)明：", "填寫(xiě)內(nèi)容只能為下拉中數(shù)據(jù)，其他數(shù)據(jù)將導(dǎo)致導(dǎo)入失敗");
            dataValidation.setShowPromptBox(true);
            sheet.addValidationData(dataValidation);
        } else {
            dataValidation.setSuppressDropDownArrow(false);
        }
        sheet.addValidationData(dataValidation);
    }
    /**
     * <h2>依據(jù)列index獲取列名英文</h2>
     * 依據(jù)列index轉(zhuǎn)換為Excel中的列名英文
     * <p>例如第1列，index為0，解析出來(lái)為A列</p>
     * 第27列，index為26，解析為AA列
     * <p>第28列，index為27，解析為AB列</p>
     *
     * @param columnIndex 列index
     * @return 列index所在得英文名
     */
    private static String getExcelColumnName(int columnIndex) {
        // 26一循環(huán)的次數(shù)
        int columnCircleCount = columnIndex / 26;
        // 26一循環(huán)內(nèi)的位置
        int thisCircleColumnIndex = columnIndex % 26;
        // 26一循環(huán)的次數(shù)大于0，則視為欄名至少兩位
        String columnPrefix = columnCircleCount == 0
            ? StrUtil.EMPTY
            : StrUtil.subWithLength(ExcelDownHandler.EXCEL_COLUMN_NAME, columnCircleCount - 1, 1);
        // 從26一循環(huán)內(nèi)取對(duì)應(yīng)的欄位名
        String columnNext = StrUtil.subWithLength(ExcelDownHandler.EXCEL_COLUMN_NAME, thisCircleColumnIndex, 1);
        // 將二者拼接即為最終的欄位名
        return columnPrefix + columnNext;
    }
}

/**
 * 字典格式化
 */
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelDictFormat {
    /**
     * 如果是字典類(lèi)型，請(qǐng)?jiān)O(shè)置字典的type值 (如: sys_user_sex)
     */
    String dictType() default "";
    /**
     * 讀取內(nèi)容轉(zhuǎn)表達(dá)式 (如: 0=男,1=女,2=未知)
     */
    String readConverterExp() default "";
    /**
     * 分隔符，讀取字符串組內(nèi)容
     */
    String separator() default StringUtils.SEPARATOR;
}

/**
 * 字典格式化轉(zhuǎn)換處理
 */
@Slf4j
public class ExcelDictConvert 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) {
        ExcelDictFormat anno = getAnnotation(contentProperty.getField());
        String type = anno.dictType();
        String label = cellData.getStringValue();
        String value;
        if (StringUtils.isBlank(type)) {
            value = ExcelUtil.reverseByExp(label, anno.readConverterExp(), anno.separator());
        } else {
            value = SpringUtils.getBean(DictService.class).getDictValue(type, label, anno.separator());
        }
        return Convert.convert(contentProperty.getField().getType(), value);
    }
    @Override
    public WriteCellData<String> convertToExcelData(Object object, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
        if (ObjectUtil.isNull(object)) {
            return new WriteCellData<>("");
        }
        ExcelDictFormat anno = getAnnotation(contentProperty.getField());
        String type = anno.dictType();
        String value = Convert.toStr(object);
        String label;
        if (StringUtils.isBlank(type)) {
            label = ExcelUtil.convertByExp(value, anno.readConverterExp(), anno.separator());
        } else {
            label = SpringUtils.getBean(DictService.class).getDictLabel(type, value, anno.separator());
        }
        return new WriteCellData<>(label);
    }
    private ExcelDictFormat getAnnotation(Field field) {
        return AnnotationUtil.getAnnotation(field, ExcelDictFormat.class);
    }
}

/**
     * convertObjectsToExcelRows 將對(duì)象列表轉(zhuǎn)換為 Excel 行數(shù)據(jù)
     *
     * @param list  對(duì)象集合
     * @param heads 表頭
     * @return {@link List<List<Object>>}  Excel 行數(shù)據(jù)
     */
    private static <T> List<List<Object>> convertObjectsToExcelRows(List<T> list, List<List<String>> heads) {
        return list.stream()
            // 將對(duì)象轉(zhuǎn)換為 Map
            .map(ExcelUtil::convertObjectToMap)
            .map(map -> {
                List<Object> row = new ArrayList<>();
                for (List<String> header : heads) {
                    String key = header.get(1);
                    // 根據(jù)表頭獲取對(duì)應(yīng)的值
                    row.add(map.get(key));
                }
                return row;
            })
            .toList();
    }

/**
     * convertToExcelData 數(shù)據(jù)轉(zhuǎn)換
     *
     * @param value 值
     * @return {@link java.lang.Object}
     */
    private static Object convertToExcelData(Object value) {
        if (ObjectUtil.isEmpty(value)) {
            return value;
        }
        if (value instanceof LocalTime localTime) {
            return localTime.format(DateTimeFormatter.ofPattern("HH:mm:ss"));
        } else if (value instanceof LocalDateTime localDateTime) {
            return localDateTime.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
        } else if (value instanceof LocalDate localDate) {
            return localDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
        } else {
            return value;
        }
    }