前些日子突然碰到一个问题，说是客户的导入文件模版想支持部分导入内容的下拉选，于是我就找了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;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public 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:$B2
String 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();
效果图如下：
