前些日子突然碰到一個問題，說是客戶的導入文件模版想支持部分導入內容的下拉選，于是我就找了easyexcel官網尋找解決方案，并沒有找到合適的方案，沒辦法只能自己動手并分享出來，針對Java生成Excel下拉菜單時因選項過多導致的問題，也通過將下拉數據存入隱藏Sheet再引用的方式解決。

直接上代碼：

package com.tlit.platform.excel.annotations;import java.lang.annotation.*;/*** @author : Doug Liang* @date : 2025/1/22 14:21* @version: 1.0* @description: 支持easyExcel的下拉選的自定義注解*/@Documented
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelSelected {/*** 固定下拉內容*/String[] source() default {};}

靜態的下拉選支持直接注解配置

package com.tlit.platform.excel.util;import com.tlit.platform.excel.annotations.ExcelSelected;import java.util.Optional;/*** @author : Doug Liang* @date : 2025/1/22 14:24* @version: 1.0* @description: 解析下拉選注解工具類*/
public class ResoveSelectedUtil {public static String[] resove(ExcelSelected selected){if(!Optional.ofNullable(selected).isPresent()){return null;}// 獲取固定下拉信息String[] source = selected.source();if(null != source && source.length > 0){return source;}return null;}
}

動態下拉選代碼

package com.tlit.platform.excel.handler;import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.tlit.platform.excel.annotations.ExcelSelected;
import com.tlit.platform.excel.util.ResoveSelectedUtil;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.Map;/*** @author : Doug Liang* @date : 2025/1/22 14:33* @version: 1.0* @description: 自定義攔截器處理*/
public class SelectedWriteHandler implements SheetWriteHandler {private final Map<Integer, String[]> map;// 調整為20大小 private static final Integer LIMIT = 20;private final char[] alphabet = new char[]{'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L','M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'};public SelectedWriteHandler(Class<?> clazz) {Field[] fields = clazz.getDeclaredFields();Map<Integer, String[]> map = new HashMap<>();for (int i = 0; i < fields.length; i++) {Field field = fields[i];// 解析注解信息ExcelSelected excelSelected = field.getAnnotation(ExcelSelected.class);if (null != excelSelected) {String[] sources = ResoveSelectedUtil.resove(excelSelected);if (null != sources && sources.length > 0) {map.put(i, sources);}}}this.map = map;}/*** 如果想動態傳入對應的下拉選，則只需要將key和對應的下拉選列表傳入即可** @param map Map<key , value>*            value -> {"","",""}*/public SelectedWriteHandler(Map<Integer, String[]> map) {this.map = map;}@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {// 這里可以對cell進行任何操作Sheet sheet = writeSheetHolder.getSheet();DataValidationHelper helper = sheet.getDataValidationHelper();String dictSheetName = "字典sheet" ;Workbook workbook = writeWorkbookHolder.getWorkbook();Sheet dictSheet = workbook.createSheet(dictSheetName);workbook.setSheetHidden(1, true);// k 為存在下拉數據集的單元格下表 v為下拉數據集map.forEach((k, v) -> {// 設置下拉單元格的首行 末行 首列 末列CellRangeAddressList rangeList = new CellRangeAddressList(1, 65536, k, k);// 下拉列表約束數據DataValidationConstraint constraint;if (v.length < LIMIT) {constraint = helper.createExplicitListConstraint(v);} else {// 獲取動態數據的大小int rowLen = v.length;// 設置字典sheet頁的值 每一列一個字典項for (int i = 0; i < rowLen; i++) {Row row = dictSheet.getRow(i);if (row == null) {row = dictSheet.createRow(i);}row.createCell(k).setCellValue(v[i]);}String excelColumn = getExcelColumn(k);// 下拉框數據來源 eg:字典sheet!$B1:$B2String refers = dictSheetName + "!$" + excelColumn + "$1:$" + excelColumn + "$" + rowLen;// 創建可被其他單元格引用的名稱Name name = workbook.createName();// 設置名稱的名字name.setNameName("dict" + k);// 設置公式name.setRefersToFormula(refers);// 設置引用約束constraint = helper.createFormulaListConstraint("dict" + k);}// 設置約束DataValidation validation = helper.createValidation(constraint, rangeList);if (validation instanceof HSSFDataValidation) {validation.setSuppressDropDownArrow(false);} else {validation.setSuppressDropDownArrow(true);validation.setShowErrorBox(true);}// 阻止輸入非下拉選項的值validation.setErrorStyle(DataValidation.ErrorStyle.STOP);validation.createErrorBox("提示", "此值與單元格定義格式不一致");sheet.addValidationData(validation);});}/*** 將數字列轉化成為字母列** @param num 數字* @return 字母列*/private String getExcelColumn(int num) {String column;int len = alphabet.length - 1;int first = num / len;int second = num % len;if (num <= len) {column = alphabet[num] + "" ;} else {column = alphabet[first - 1] + "" ;if (second == 0) {column = column + alphabet[len] ;} else {column = column + alphabet[second - 1] ;}}return column;}}

實際引用

EasyExcel.writerSheet(sheetName).head(head).registerWriteHandler(new SelectedWriteHandler(head))// 自適應寬度.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();

效果圖如下：