EasyExcel导出动态下拉选模版

注意⚠️:下拉选的选项数量超过255个后则会通过创建额外sheet保存选项，再引用到下拉选

涉及到的内容:

1.@ExcelSelected注解

@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelSelected {

    /**
     * 固定下拉内容
     */
    String[] source() default {};

    /**
     * 动态下拉内容
     */
    Class<? extends ExcelDynamicSelect>[] sourceClass() default {};

    /**
     * 设置下拉框的起始行，默认为第二行
     */
    int firstRow() default 1;

    /**
     * 设置下拉框的结束行，默认为最后一行
     */
    int lastRow() default 0x10000;

}

2.ExcelSelectedResolve解析器

@Data
@Slf4j
public class ExcelSelectedResolve {


    /**
     * 下拉内容
     */
    private String[] source;

    /**
     * 设置下拉框的起始行，默认为第二行
     */
    private int firstRow;

    /**
     * 设置下拉框的结束行，默认为最后一行
     */
    private int lastRow;

    /**
     * 下拉列名
     */
    private String columnName;

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

}

3.SelectedSheetWriteHandler

@Data
public class SelectedSheetWriteHandler implements SheetWriteHandler {

    /**
     * 构建下拉选的map
     */
    private final Map<Integer, ExcelSelectedResolve> selectedMap;

    private final int columnSelectMaxLength = 255;

    @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();
            selectedMap.forEach((k, v) -> {
                String[] source = v.getSource();
                String selectColumnName = v.getColumnName();
                // 下拉选总字符超过255,需要通过创建sheet关联方式构建下拉选
                if (StringUtils.join(source).length() > columnSelectMaxLength) {
                    // 获取一个workbook
                    Workbook workbook = writeWorkbookHolder.getWorkbook();
                    // 定义sheet的名称
                    String sheetName = selectColumnName + k;
                    // 创建一个隐藏的sheet
                    Sheet tmpSheet = workbook.createSheet(sheetName);
                    // 循环赋值（为了防止下拉框的行数与隐藏域的行数相对应，将隐藏域加到结束行之后）
                    for (int i = 0, length = source.length; i < length; i++) {
                        // i:表示你开始的行数 0表示你开始的列数
                        tmpSheet.createRow(i).createCell(0).setCellValue(source[i]);
                    }
                    Name category1Name = workbook.createName();
                    category1Name.setNameName(sheetName);
                    // $A$1:$A$N代表 以A列1行开始获取N行下拉数据
                    category1Name.setRefersToFormula(sheetName + "!$A$1:$A$" + (source.length));
                    // 将刚才设置的sheet引用到你的下拉列表中, (首行，末行，首列，末列)
                    CellRangeAddressList addressList = new CellRangeAddressList(v.getFirstRow(), v.getLastRow(), k, k);
                    DataValidationConstraint constraint = helper.createFormulaListConstraint(sheetName);
                    DataValidation validation = helper.createValidation(constraint, addressList);
                    // 阻止输入非下拉选项的值
                    validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
                    validation.setShowErrorBox(true);
                    validation.setSuppressDropDownArrow(true);
                    validation.createErrorBox("提示", "请输入下拉选项中的内容");
                    sheet.addValidationData(validation);
                } else {
                    // 设置下拉列表的行： 首行，末行，首列，末列
                    CellRangeAddressList rangeList = new CellRangeAddressList(v.getFirstRow(), v.getLastRow(), k, k);
                    // 设置下拉列表的值
                    DataValidationConstraint constraint = helper.createExplicitListConstraint(source);
                    // 设置约束
                    DataValidation validation = helper.createValidation(constraint, rangeList);
                    // 阻止输入非下拉选项的值
                    validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
                    validation.setShowErrorBox(true);
                    validation.setSuppressDropDownArrow(true);
                    validation.createErrorBox("提示", "请输入下拉选项中的内容");
                    sheet.addValidationData(validation);
                }
            });
    }
}

4.ExcelDynamicSelect

public interface ExcelDynamicSelect {

    /**
     * 获取动态生成的下拉框可选数据
     * @return 动态生成的下拉框可选数据
     */
    String[] getSource();

}

5.解析方法:EasyExcelUtil.resolveSelectedAnnotation

/**
     * 解析表头类中的下拉注解
     * @param head 表头类
     * @param <T> 泛型
     * @return Map<下拉框列索引, 下拉框内容> map
     */
    public static <T> Map<Integer, ExcelSelectedResolve> resolveSelectedAnnotation(Class<T> head) {
        Map<Integer, ExcelSelectedResolve> selectedMap = Maps.newHashMap();
        Field[] fields = head.getDeclaredFields();
        for (int i = 0; i < fields.length; i++){
            Field field = fields[i];
            // 解析注解信息
            ExcelSelected selected = field.getAnnotation(ExcelSelected.class);
            ExcelProperty property = field.getAnnotation(ExcelProperty.class);
            if (selected != null) {
                ExcelSelectedResolve excelSelectedResolve = new ExcelSelectedResolve();
                excelSelectedResolve.setColumnName(StringUtils.join(property.value(), "-"));
                // 处理下拉框内容
                String[] source = excelSelectedResolve.resolveSelectedSource(selected);
                if (source != null && source.length > 0){
                    excelSelectedResolve.setSource(source);
                    excelSelectedResolve.setFirstRow(selected.firstRow());
                    excelSelectedResolve.setLastRow(selected.lastRow());
                    if (property.index() >= 0){
                        selectedMap.put(property.index(), excelSelectedResolve);
                    } else {
                        selectedMap.put(i, excelSelectedResolve);
                    }
                }
            }
        }
        return selectedMap;
    }

使用说明:

下拉选的选项数量超过255个后则会通过创建额外sheet保存选项，再引用到下拉选

1.固定值的下拉选,使用

@ExcelSelected(source = {"男","女", "未知"})
@ExcelProperty("性别")
private String gender;

2.动态下拉选,需自定义实现ExcelDynamicSelect

public class CompanySelector implements ExcelDynamicSelect {

    @Override
    public String[] getSource() {
        // todo 此处可通过获取dao实例来查询数据库,构建动态数据
        return new String[] {"百度", "腾讯", "阿里"};
    }
}

@ExcelSelected(sourceClass = CompanySelector.class)
@ExcelProperty("公司")
private String warehouseName;

3.调用EasyExcelUtil解析方法

public static void main(String[] args) {
    File file = new File("/Users/user/Downloads/test.xlsx");
    try {
        FileOutputStream fileOutputStream = new FileOutputStream(file);
        Map<Integer, ExcelSelectedResolve> excelCellSelectMap = EasyExcelUtil.resolveSelectedAnnotation(TestExcel.class);
        EasyExcel.write(fileOutputStream, TestExcel.class)
                .sheet("sheet1")
                .registerWriteHandler(new SelectedSheetWriteHandler(excelCellSelectMap))
                .doWrite(Lists.newArrayList());
    } catch (FileNotFoundException e) {
        throw new RuntimeException(e);
    }
}