EasyExcel下拉框操作（数据联动下拉框）

前言

在一次项目过程中，需要根据模板实现excel带数据下载模版和上传功能，模版包含动态下拉框，数据联动下拉框等内容，在此记录分享

jdk版本:1.8
easyexcel版本:3.0.5

直接上代码

自定义处理器

@Slf4j
public class SelectedCellWriteHandler implements CellWriteHandler {

    private final String KEY = "key";
    private final String VAL = "value";
    private static String  key = "";

    /**
     * 数据map
     */
    private static Map<String, List<TestSelectData>> dataMap;

    static {
        //实际场景中可以使用SpringContext工具获取spring管理的bean调用service或repository中的数据查询方法
        List<TestSelectData> list = new ArrayList();
        TestSelectData d1 = new TestSelectData();
        d1.setKey("A");
        d1.setValue("1");

        TestSelectData d2 = new TestSelectData();
        d2.setKey("A");
        d2.setValue("2");

        TestSelectData d3 = new TestSelectData();
        d3.setKey("B");
        d3.setValue("3");

        TestSelectData d4 = new TestSelectData();
        d4.setKey("B");
        d4.setValue("4");

        list.add(d1);
        list.add(d2);
        list.add(d3);
        list.add(d4);
        dataMap = list.stream().collect(Collectors.groupingBy(TestSelectData::getKey));
    }


    /**
     * called after the cell is disposed
     * @param writeSheetHolder
     * @param writeTableHolder
     * @param cellDataList 单元格数据
     * @param cell 单元格
     * @param head 标题
     * @param relativeRowIndex
     * @param isHead 是否是标题列
     */
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                 List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

        if (!isHead) {
            //key列
            if (KEY.equals(head.getFieldName())){
                key = cell.getStringCellValue();
            }

            //value列
            if (VAL.equals(head.getFieldName())){
                //设置value下拉框
                setSelectDataList(writeSheetHolder,key,cell.getRowIndex(),cell.getColumnIndex());
            }
        }
    }

    /**
     * 设置下拉框数据
     * @param writeSheetHolder
     * @param key
     * @param rowIndex 行号
     * @param columnIndex 列号
     */
    private void setSelectDataList(WriteSheetHolder writeSheetHolder, String key, int rowIndex, int columnIndex) {
        Sheet sheet = writeSheetHolder.getSheet();
        DataValidationHelper helper = sheet.getDataValidationHelper();

        // 设置下拉列表的行： 首行，末行，首列，末列
        CellRangeAddressList rangeList = new CellRangeAddressList(rowIndex, rowIndex, columnIndex, columnIndex);
        // 设置下拉列表的值
        DataValidationConstraint constraint = helper.createExplicitListConstraint(getSourceByKey(key));
        // 设置约束
        DataValidation validation = helper.createValidation(constraint, rangeList);
        // 阻止输入非下拉选项的值
        validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
        validation.setShowErrorBox(true);
        validation.setSuppressDropDownArrow(true);
        validation.createErrorBox("提示", "请输入下拉选项中的内容");
        sheet.addValidationData(validation);
    }

    /**
     * 根据key关联出下拉框数据
     * @param key
     * @return
     */
    private String[] getSourceByKey(String key) {
        List<TestSelectData> values = dataMap.get(key);
        List<String> selectList = values.stream().map(TestSelectData::getValue).collect(Collectors.toList());
        String[] selectArray = selectList.toArray(new String[selectList.size()]);
        return selectArray;
    }

}
@Data
class TestSelectData {
    private String key;
    private String value;
}

导出工具类

public class TestExcelUtils<T> {

    public static final String CONTENT_TYPE_STREAM = "application/octet-stream";

    public static final String CONTENT_DISPOSITION_ATTACHMENT = "attachment;filename=";

    public static <T> void writeKolWithSheet(String fileName,String sheet,List<T> data,Class<T> obj, HttpServletResponse response){
        try{
            //设置响应头类型
            response.setContentType(CONTENT_TYPE_STREAM);
            //设置编码
            response.setCharacterEncoding(CharEncoding.UTF_8);

            fileName = URLEncoder.encode(fileName + ".xlsx","UTF-8");

            //设置响应头
            response.setHeader(HttpHeaders.CONTENT_DISPOSITION, CONTENT_DISPOSITION_ATTACHMENT + fileName);
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
            WriteSheet writeSheet = writeKolSelectedSheet(obj,0,sheet);
            excelWriter.write(data,writeSheet);
            excelWriter.finish();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    /**
     * 创建即将导出的sheet页（注册自定义的处理器）
     * @param head 导出的表头信息和配置
     * @param sheetNo sheet索引
     * @param sheetName sheet名称
     * @param <T> 泛型
     * @return sheet页
     */
    public static <T> WriteSheet writeKolSelectedSheet(Class<T> head, Integer sheetNo, String sheetName) {
        return EasyExcel.writerSheet(sheetNo, sheetName)
                .head(head)
                .registerWriteHandler(new SelectedCellWriteHandler())
                .build();
    }

导出实体类

@Data
public class TestExport {
    @ExcelProperty("key")
    private String key;
    @ExcelProperty("value")
    private String value;
}

导出controller

@RestController
@RequestMapping("/test")
public class TestController {

    @Resource
    private TestService testService;

    /**
     * 下载
     * @return
     */
    @GetMapping("/download")
    public ResponseVo download(HttpServletResponse response){
        return testService.downloadKolList(response);
    }
}

导出service

@Slf4j
@Service
public class TestService {
    /**
     * 下载
     * @param response
     * @return
     */
    public ResponseVo downloadKolList(HttpServletResponse response) {
        List<TestExport> data = buildExportKolList();
        String fileName = "数据清单";
        String sheet = "数据清单";
        TestExcelUtils.writeKolWithSheet(fileName,sheet,data,TestExport.class,response);
        return ResponseVoUtil.success();
    }

    private List<TestExport> buildExportKolList() {
        List<TestExport> list = new ArrayList<>();
        TestExport export = new TestExport();
        export.setKey("A");
        export.setValue("1");


        TestExport export1 = new TestExport();
        export1.setKey("B");
        export1.setValue("2");

        list.add(export);
        list.add(export1);
        return list;
    }

SpringContext工具类（本示例中未使用）

@Component
public class SpringContextUtil implements ApplicationContextAware {
    private static ApplicationContext applicationContext;

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

    /**
     * 获取ApplicationContext
     * @return
     */
    public static ApplicationContext getApplicationContext() {
        return applicationContext;
    }

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

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

导出效果