EasyExcel 使用自定义注解实现自定义下拉框选项
1.版本介绍
-
easyexcel：3.3.2
-
SpringBoot：2.7.1
2.具体实现
- 定义自定义注解：ExcelSelected
@Documented
@Target({ElementType.FIELD}) //用此注解用在属性上
@Retention(RetentionPolicy.RUNTIME)//注解不仅被保存到class文件中，jvm加载class文件之后，仍然存在；
public @interface ExcelSelected {
/**
* 固定下拉内容
*/
String[] source() default {};
/**
* 动态下拉内容
*/
Class<? extends ExcelDynamicSelect>[] sourceClass() default {};
/**
* 设置下拉框的起始行，默认为第二行
*/
int firstRow() default 1;
/**
* 设置下拉框的结束行，默认为最后一行
*/
int lastRow() default 0x10000;
}
- 定义动态获取下拉框数据接口：ExcelDynamicSelect
public interface ExcelDynamicSelect {
/**
* 获取动态生成的下拉框可选数据
* @return 动态生成的下拉框可选数据
*/
String[] getSource();
}
- 自定义注解解析
@Data
@Slf4j
public class ExcelSelectedResolve {
/**
* 下拉内容
*/
private String[] source;
/**
* 设置下拉框的起始行，默认为第二行
*/
private int firstRow;
/**
* 设置下拉框的结束行，默认为最后一行
*/
private int lastRow;
public String[] resolveSelectedSource(ExcelSelected excelSelected) {
if (excelSelected == null) {
return null;
}
// 获取固定下拉框的内容
String[] source = excelSelected.source();
if (source.length > 0) {
return source;
}
// 获取动态下拉框的内容
Class<? extends ExcelDynamicSelect>[] classes = excelSelected.sourceClass();
if (classes.length > 0) {
try {
ExcelDynamicSelect excelDynamicSelect = classes[0].newInstance();
String[] dynamicSelectSource = excelDynamicSelect.getSource();
if (dynamicSelectSource != null && dynamicSelectSource.length > 0) {
return dynamicSelectSource;
}
} catch (InstantiationException | IllegalAccessException e) {
log.error("解析动态下拉框数据异常", e);
}
}
return null;
}
}
4.继承SheetWriteHandler，实现自定义的处理器
@Data
@AllArgsConstructor
public class SelectedSheetWriteHandler implements SheetWriteHandler {
private final Map<Integer, ExcelSelectedResolve> selectedMap;
/**
* Called before create the sheet
*/
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
/**
* 说明：当下拉选项内容过多时，导出会存在问题，解决办法是新增一个”hidden“sheet页，将下拉框内容写入，然后再具体单元格引用此处的内容
* 当下拉选项内容不多时，可使用下面的方式
* // 设置下拉列表的值
* DataValidationConstraint constraint = helper.createExplicitListConstraint(v.getSource());
* // 设置约束
* DataValidation validation = helper.createValidation(constraint, rangeList);
*/
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
// 这里可以对cell进行任何操作
Sheet sheet = writeSheetHolder.getSheet();
//设置下拉框
DataValidationHelper helper = sheet.getDataValidationHelper();
//1.创建一个隐藏的sheet 名称为 hidden
String hiddenName = "hidden";
Workbook workbook = writeWorkbookHolder.getWorkbook();
Sheet hiddenSheet = workbook.createSheet(hiddenName);
workbook.setSheetHidden(workbook.getSheetIndex(hiddenName),true);
selectedMap.forEach((k, v) -> {
// 设置下拉列表的行： 首行，末行，首列，末列
CellRangeAddressList rangeList = new CellRangeAddressList(v.getFirstRow(), v.getLastRow(), k, k);
//获取excel列名
String excelLine = getExcelLine(k);
//2.循环赋值
String[] values = v.getSource();
generateSelectValue(hiddenSheet,k,values);
//4.hidden!$H:$1:$H$50 sheet为hidden的 H1列开始H50行数据获取下拉数组
String refers = hiddenName + "!$" + excelLine +
"$1:$" + excelLine + "$" + (values.length);
//5 将刚才设置的sheet引用到你的下拉列表中
DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
DataValidation validation = helper.createValidation(constraint, rangeList);
// 设置下拉列表的值
//DataValidationConstraint constraint = helper.createExplicitListConstraint(v.getSource());
// 设置约束
//DataValidation validation = helper.createValidation(constraint, rangeList);
// 阻止输入非下拉选项的值
validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
validation.setShowErrorBox(true);
validation.setSuppressDropDownArrow(true);
validation.createErrorBox("提示", "请输入下拉选项中的内容");
sheet.addValidationData(validation);
});
}
/**
* @param num 列数
* @return java.lang.String
* @Description 返回excel列标A-Z-AA-ZZ
* @Author chou
* @Date 2020/9/8
*/
public static String getExcelLine(int num) {
String line = "";
int first = num / 26;
int second = num % 26;
if (first > 0) {
line = (char) ('A' + first - 1) + "";
}
line += (char) ('A' + second) + "";
return line;
}
private void generateSelectValue(Sheet sheet,int col,String[] values) {
for (int i = 0, length = values.length; i < length; i++) {
Row row = sheet.getRow(i);
if (row == null) {
row = sheet.createRow(i);
}
row.createCell(col).setCellValue(values[i]);
}
}
-
新增EasyExcelUtil工具类
@Slf4j public class EasyExcelUtil { /** * 创建即将导出的sheet页（sheet页中含有带下拉框的列） * * @param head 导出的表头信息和配置 * @param sheetNo sheet索引 * @param sheetName sheet名称 * @param <T> 泛型 * @return sheet页 */ public static <T> WriteSheet writeSelectedSheet(Class<T> head, Integer sheetNo, String sheetName) { // 解析表头类中的下拉注解 Map<Integer, ExcelSelectedResolve> selectedMap = resolveSelectedAnnotation(head); return EasyExcel.writerSheet(sheetNo, sheetName) .head(head) .registerWriteHandler(new SelectedSheetWriteHandler(selectedMap)) .build(); } /** * 解析表头类中的下拉注解 * * @param head 表头类 * @param <T> 泛型 * @return Map<下拉框列索引, 下拉框内容> map */ private static <T> Map<Integer, ExcelSelectedResolve> resolveSelectedAnnotation(Class<T> head) { Map<Integer, ExcelSelectedResolve> selectedMap = new HashMap<>(); // getDeclaredFields(): 返回全部声明的属性；getFields(): 返回public类型的属性 Field[] fields = head.getDeclaredFields(); // 忽略静态常量 如 serialVersionUID List<Field> fieldList = Arrays.stream(fields).filter(e -> !Modifier.isStatic(e.getModifiers())).collect(Collectors.toList()); for (int i = 0; i < fieldList.size(); i++) { Field field = fieldList.get(i); // 解析注解信息 ExcelSelected selected = field.getAnnotation(ExcelSelected.class); ExcelProperty property = field.getAnnotation(ExcelProperty.class); if (selected != null) { ExcelSelectedResolve excelSelectedResolve = new ExcelSelectedResolve(); String[] source = excelSelectedResolve.resolveSelectedSource(selected); if (source != null && source.length > 0) { excelSelectedResolve.setSource(source); excelSelectedResolve.setFirstRow(selected.firstRow()); excelSelectedResolve.setLastRow(selected.lastRow()); if (property != null && property.index() >= 0) { selectedMap.put(property.index(), excelSelectedResolve); } else { selectedMap.put(i, excelSelectedResolve); } } } } return selectedMap; } } -
具体使用到实体类
@Data @ColumnWidth(14) @HeadRowHeight(20) @ContentRowHeight(18) public class RoadExcel implements Serializable { private static final long serialVersionUID = 6255824451047479409L; @ExcelProperty("路线编号") private String code; @ExcelProperty("路线全称") private String fullName; @ExcelProperty("路线简称") private String briefName; @ExcelProperty("起点桩号") private String startStake; @ExcelProperty("终点桩号") private String endStake; @ExcelProperty("起点名称") private String startPosName; @ExcelProperty("终点名称") private String endPosName; @ExcelSelected(sourceClass = TechnicalGradeConverter.class) private String technicalGrade; @ExcelSelected(sourceClass = AdministrativeGradeConverter.class) private String administrativeGrade; @ExcelSelected(sourceClass = RoadClassificationConverter.class) private String roadClassification; @ExcelSelected(sourceClass = RoadDirectConverter.class) private String direct; @ExcelProperty("上行方向") private String upDirect; @ExcelProperty("下行方向") private String downDirect; } -
具体实现ExcelDynamicSelect接口的类 如 TechnicalGradeConverter
public class TechnicalGradeConverter implements ExcelDynamicSelect { /** * 获取动态生成的下拉框可选数据 * * @return 动态生成的下拉框可选数据 */ @Override public String[] getSource() { List<DictBiz> dictBizList = DictBizCache.getList(DICT_CODE); return dictBizList.stream().sorted(Comparator.comparingInt(DictBiz::getSort)).map(DictBiz::getDictValue).toArray(String[]::new); } }
3.导出结果
