EasyExcel自定义动态下拉框（附加业务对象转换功能）

全文直接复制粘贴即可，测试无误

一、注解类

1、ExcelSelected.java

设置下拉框

@Documented
@Target({ElementType.FIELD})//用此注解用在属性上。
@Retention(RetentionPolicy.RUNTIME)//注解不仅被保存到class文件中，jvm加载class文件之后，仍然存在；
public @interface ExcelSelected {
    /**
     * 固定下拉内容
     */
    String[] source() default {};


    /**
     * 方式二：提供动态下拉选项的类
     */
    Class<? extends WhExcelDynamicSelect>[] sourceClass() default {};

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

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

2、TextColumn.java

设置单元格为文本格式

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface TextColumn {
    int index(); // 列索引
}

二、Controller类

分为导入数据与导出模板

@Tag(name = "xxxx管理")
@RestController
@RequestMapping("/api/v1/project/people")
@RequiredArgsConstructor
@Slf4j
public class WhProjectPeopleController {

    private final WhProjectPeopleService whProjectPeopleService;

    @GetMapping("/exportDownload")
    @Operation(summary = "导出excel模板")
    public void downloadExcel(HttpServletResponse response) throws IOException {
        //获取输入流，原始模板位置
        String name = "人员信息模板";
        //假如以中文名下载的话，设置下载文件名称
        String filename = "人员信息模板.xlsx";
        //转码，免得文件名中文乱码
        filename = URLEncoder.encode(filename, "UTF-8");
        //设置文件下载头
        response.addHeader("Content-Disposition", "attachment;filename=" + filename);
        response.setContentType("multipart/form-data");
        ExcelUtils.writeExcel(response, filename, name, WhProjectPeopleVO.class, null);
    }

    @PostMapping("/importExcel")
    @Operation(summary = "导入人员数据")
    public Result importExcel(MultipartFile file, HttpServletRequest request) throws IOException {
        if (file.isEmpty()) {
            throw new SzException("excel文件内容为空！");
        }
        List<WhProjectPeopleVO> vos = ExcelUtils.read(file, WhProjectPeopleVO.class);
        if (CollectionUtils.isEmpty(vos)){
            throw new SzException("导入数据失败！");
        }
        Map<String,Integer> result = whProjectPeopleService.importExcel(vos);
        return Result.success(result);
    }

}

三、Service方法

向excel导入数据

    @Override
    @Transactional
    public Map<String, Integer> importExcel(List<WhProjectPeopleVO> vos) {
        Map<String, Integer> params = new HashMap<>();
        if (CollectionUtils.isEmpty(vos)) {
            throw new SzException("数据导入失败！");
        }
        log.info("导入数据：{}" + JSON.toJSONString(vos));
        int count = 0;
        List<WhProjectPeople> peoples = whProjectPeopleConverter.voListToEntity(vos);
        for (WhProjectPeople people : peoples) {
            WhProjectPeopleForm whProjectPeopleForm = whProjectPeopleConverter.entityToForm(people);
            saveProjectPeople(whProjectPeopleForm);
            count++;
            params.put("insert", count);
        }
        return params;
    }
}

四、工具类

1、ExcelUtils.java

public class ExcelUtils {

    /**
     * 将列表以 Excel 响应给前端
     *
     * @param response  响应
     * @param filename  文件名
     * @param sheetName Excel sheet 名
     * @param head      Excel head 头
     * @param data      数据列表
     * @param <T>       泛型，保证 head 和 data 类型的一致性
     * @throws IOException 写入失败的情况
     */
    public static <T> void writeExcel(HttpServletResponse response, String filename, String sheetName,
                                      Class<T> head, List<T> data) throws IOException {

        CellStyleStrategy cellStyleStrategy =
                new CellStyleStrategy(new WriteCellStyle());
        Map<Integer, WhExcelSelectedResolve> selectedMap = resolveSelectedAnnotation(head);

        // 输出 Excel
        EasyExcel.write(response.getOutputStream(), head)
                // 不要自动关闭，交给 Servlet 自己处理
                .autoCloseStream(false)
                //设置表头和填充内容的样式
                .registerWriteHandler(cellStyleStrategy)
                //设置填充内容单元格格式为文本格式
                .registerWriteHandler(new CustomSheetWriteHandler())
                .registerWriteHandler(new SelectedSheetWriteHandler(selectedMap))
                .sheet(sheetName).doWrite(data);

        // 设置 header 和 contentType。写在最后的原因是，避免报错时，响应 contentType 已经被修改了
        response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
    }

    /**
     * 将列表以 Excel 响应给前端
     *
     * @param response  响应
     * @param filename  文件名
     * @param sheetName Excel sheet 名
     * @param head      Excel head 头
     * @param data      数据列表
     * @param <T>       泛型，保证 head 和 data 类型的一致性
     * @throws IOException 写入失败的情况
     */
    public static <T> void write(HttpServletResponse response, String filename, String sheetName,
                                 Class<T> head, List<T> data) throws IOException {

        CellStyleStrategy cellStyleStrategy =
                new CellStyleStrategy(new WriteCellStyle());

        // 输出 Excel
        EasyExcel.write(response.getOutputStream(), head)
                .autoCloseStream(false) // 不要自动关闭，交给 Servlet 自己处理
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // 基于 column 长度，自动适配。最大 255 宽度
                .registerWriteHandler(cellStyleStrategy)
                .sheet(sheetName).doWrite(data);

        // 设置 header 和 contentType。写在最后的原因是，避免报错时，响应 contentType 已经被修改了
        response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
    }



    public static <T> List<T> read(MultipartFile file, Class<T> head) throws IOException {
        return EasyExcel.read(file.getInputStream(), head, null)
                // 不要自动关闭，交给 Servlet 自己处理
                .autoCloseStream(false)
                .doReadAllSync();
    }

    /**
     * 解析表头类中的下拉注解
     * @param head 表头类
     * @param <T> 泛型
     * @return Map<下拉框列索引, 下拉框内容> map
     */
    private static <T> Map<Integer, WhExcelSelectedResolve> resolveSelectedAnnotation(Class<T> head) {
        Map<Integer, WhExcelSelectedResolve> selectedMap = new HashMap<>();

        // getDeclaredFields(): 返回全部声明的属性；getFields(): 返回public类型的属性
        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) {
                WhExcelSelectedResolve excelSelectedResolve = new WhExcelSelectedResolve();
                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;
    }


}

2、SpringContextUtil.java

@Component
public class SpringContextUtil implements ApplicationContextAware {

    /**
     * 获取ApplicationContext
     */
    @Getter
    private static ApplicationContext applicationContext;

    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        SpringContextUtil.applicationContext = applicationContext;
    }

    /**
     * 通过class获取Bean
     */
    public static <T> T getBean(Class<T> clazz) {
        return applicationContext.getBean(clazz);
    }

    /**
     * 通过name以及class获取Bean
     */
    public static <T> T getBean(String name, Class<T> clazz) {
        return applicationContext.getBean(name, clazz);
    }
}

五、转换器

1、WhProjectExcelConverter.java

项目id与项目名称互相转化

public class WhProjectExcelConverter implements Converter<String> {

    private final WhProjectService whProjectService = SpringContextUtil.getBean(WhProjectService.class);
    private final List<WhProject> list = whProjectService.list();

    @Override
    public Class<?> supportJavaTypeKey() {
        //实体类对象属性类型
        return String.class;
    }


    /**
     * 将单元格中数据转化为java对象属性
     * @param context
     * @return
     * @throws Exception
     */
    @Override
    public String convertToJavaData(ReadConverterContext<?> context) throws Exception {
        //创建集合用于存储项目id和名称对应关系
        Map<String,String> mapToJavaData = new HashMap<>();
        //遍历项目列表将项目id和名称放入map
        for (WhProject sysDeptEntity : list) {
            mapToJavaData.put(sysDeptEntity.getProjectName(),sysDeptEntity.getId());
        }
        //从cellData中读取数据 转换为实体类中的对象数值
        return mapToJavaData.get(context.getReadCellData().getStringValue());
    }

    /**
     * 将java对象转化为excel单元格数据
     * @param context
     * @return
     * @throws Exception
     */
    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<String> context) throws Exception {
        Map<String,String> mapToExcelData = new HashMap<>();
        for (WhProject sysDeptEntity : list) {
            mapToExcelData.put(sysDeptEntity.getId(),sysDeptEntity.getProjectName());
        }
        //将java属性转换为excel对应属性类型
        return new WriteCellData<>(mapToExcelData.get(context.getValue()));
    }
}

2、WhWorkTypeExcelConverter.java

int类型type值与String类型type名称相互转化

public class WhWorkTypeExcelConverter implements Converter<Integer> {
    private final WhDictItemService whDictItemService = SpringContextUtil.getBean(WhDictItemService.class);
    private final List<WhDictItem> list = whDictItemService.listByCode(WORK_TYPE);

    @Override
    public Class<?> supportJavaTypeKey() {
        //实体类对象属性类型
        return Integer.class;
    }


    /**
     * 将单元格中数据转化为java对象属性
     * @param context
     * @return
     * @throws Exception
     */
    @Override
    public Integer convertToJavaData(ReadConverterContext<?> context) throws Exception {
        //创建集合用于存储部门id和名称对应关系
        Map<String,String> mapToJavaData = new HashMap<>();
        //遍历数据字典将属性名称和属性值放入map
        for (WhDictItem sysDeptEntity : list) {
            mapToJavaData.put(sysDeptEntity.getItemName(),sysDeptEntity.getItemValue());
        }
        //从cellData中读取数据 转换为实体类中的对象数值
        return Integer.valueOf(mapToJavaData.get(context.getReadCellData().getStringValue()));
    }

    /**
     * 将java对象转化为excel单元格数据
     * @param context
     * @return
     * @throws Exception
     */
    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<Integer> context) throws Exception {
        Map<String,String> mapToExcelData = new HashMap<>();
        for (WhDictItem sysDeptEntity : list) {
            mapToExcelData.put(sysDeptEntity.getItemValue(),sysDeptEntity.getItemName());
        }
        //将java属性转换为excel对应属性类型
        return new WriteCellData<>(mapToExcelData.get(context.getValue()));
    }
}

六、Handler类

1、SelectedSheetWriteHandler.java

设置下拉列表相关

@Data
public class SelectedSheetWriteHandler implements SheetWriteHandler {

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

    private final int columnSelectMaxLength = 255;


    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        // 这里可以对cell进行任何操作
        Sheet sheet = writeSheetHolder.getSheet();
        DataValidationHelper helper = sheet.getDataValidationHelper();
        selectedMap.forEach((k, v) -> {
            // 设置下拉列表的行： 首行，末行，首列，末列
            CellRangeAddressList rangeList = new CellRangeAddressList(v.getFirstRow(), v.getLastRow(), k, k);
            // 设置下拉列表的值
            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);
        });
    }
}

2、CustomSheetWriteHandler.java

设置文本格式、表格式

public class CustomSheetWriteHandler implements SheetWriteHandler {

    // 设置100列column
    private static final Integer COLUMN = 100;

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        // 获取带有 @TextColumn 注解的列索引
        Map<Integer, String> textColumns = getTextColumns();

        SXSSFSheet sxssfSheet = (SXSSFSheet) writeSheetHolder.getSheet();
        for (int i = 0; i < COLUMN; i++) {
            if (textColumns.containsKey(i)) {
                // 设置为文本格式
                CellStyle cellStyle = writeWorkbookHolder.getCachedWorkbook().createCellStyle();
                // 49为文本格式
                cellStyle.setDataFormat((short) 49);
                // i为列，一整列设置为文本格式
                sxssfSheet.setDefaultColumnStyle(i, cellStyle);
            }
        }
    }

    private Map<Integer, String> getTextColumns() {
        Map<Integer, String> textColumns = new HashMap<>();

        Class<?> dataClass = WhProjectPeopleVO.class;
        Field[] fields = dataClass.getDeclaredFields();
        for (Field field : fields) {
            if (field.isAnnotationPresent(TextColumn.class)) {
                int columnIndex = field.getAnnotation(TextColumn.class).index();
                textColumns.put(columnIndex, field.getName());
            }
        }
        return textColumns;
    }
}

七、SelectedResolve类

@Data
@Slf4j
public class WhExcelSelectedResolve {

    /**
     * 下拉内容
     */
    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 WhExcelDynamicSelect>[] classes = excelSelected.sourceClass();
        if (classes.length > 0) {
            try {
                WhExcelDynamicSelect 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;
    }
}

八、实体类

@Data
@Schema(description = "项目随行人员表")
@HeadRowHeight(30)
@ContentRowHeight(18)
public class WhProjectPeopleVO {

    @ExcelIgnore
    @Schema(description = "id")
    private String id;

    @ExcelIgnore
    @Schema(description = "人员类型 0随行人员1负责人 默认0")
    private Integer type;

    @ExcelProperty(value = "姓名", index = 0)
    @Schema(description = "姓名")
    private String name;


    @ColumnWidth(20)
    @TextColumn(index = 1)
    @ExcelProperty(value = "身份证号", index = 1)
    @Schema(description = "身份证号")
    private String identityId;

    @ColumnWidth(20)
    @ExcelProperty(value = "手机号", index = 2)
    @Schema(description = "手机号")
    private String mobile;


    @ColumnWidth(20)
    @ExcelSelected(sourceClass = WhProjectSelect.class)
    @ExcelProperty(value = "所属项目", index = 3, converter = WhProjectExcelConverter.class)
    @Schema(description = "项目id")
    private String projectId;

    @ExcelIgnore
    @Schema(description = "微信openId")
    private String openId;

    @ExcelIgnore
    @Schema(description = "微信unionId")
    private String unionId;

    @ExcelIgnore
    @Schema(description = "开始时间")
    private Date startTime;

    @ExcelIgnore
    @Schema(description = "结束时间")
    private Date endTime;

    @ExcelIgnore
    @Schema(description = "项目名称")
    private String projectName;

    @ExcelIgnore
    @Schema(description = "最近入场时间")
    private Date lastArrivalTime;

    @ExcelSelected(sourceClass = WhWorkTypeSelect.class)
    @ExcelProperty(value = "工种", index = 4, converter = WhWorkTypeExcelConverter.class)
    @Schema(description = "工种")
    private Integer workType;
}

测试结果