aliEasyexcel导出单元格实现下拉框,下拉框数据字典查询,超过50不显示字典数据问题解决方案

前段时间写过，自定义表格下拉框单元,下拉框数据硬编码，可以满足数据少的情况。有些数据不固定，则不方便。所以今天和大家分享下，从字典中获取。
上一篇文章地址

一. 自定义下拉框注解

/**
 * 下拉框注解
 */
@Documented
@Target({ElementType.FIELD})//用此注解用在属性上。
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelSelector {
    /**
     * 固定数据
     */
    String[] fixedSelector() default {};

    /**
     * 动态字典key
     */
    String dictKeyValue() default "";

    /**
     * 服务类
     */
    Class<? extends ExcelSelectorService>[] serviceClass() default {};

}

二，下拉框需要写入的表格

/**
 * 
 * @Title: ExcelSelectorDataWriteHandler
 * @Description: TODO
 * @Date: 2023/6/12 17:51
 */
@Data
public class ExcelSelectorDataWriteHandler implements SheetWriteHandler {

    private final Map<Integer, ExcelSelectorResolve> selectedMap;

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        DataValidationHelper helper = sheet.getDataValidationHelper();
        if (CollUtil.isEmpty(selectedMap)) {
            return;
        }
        selectedMap.forEach((k, v) -> {
            // 下拉 首行 末行 首列 末列
            CellRangeAddressList list = new CellRangeAddressList(v.getStartRow(), v.getEndRow(), k, k);
            // 下拉值
            DataValidationConstraint constraint = helper.createExplicitListConstraint(v.getSelectorData());
            DataValidation validation = helper.createValidation(constraint, list);
            validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
            validation.setShowErrorBox(true);
            validation.setSuppressDropDownArrow(true);
            validation.createErrorBox("提示", "请输入下拉选项中的内容");
            sheet.addValidationData(validation);
        });
// 字典数据超过50 不显示问题 超过50个数据 使用下面这个
//		       String hiddenName = "hidden";
//        //1.创建一个隐藏的sheet 名称为 hidden
//        Workbook workbook = writeWorkbookHolder.getWorkbook();
//        Sheet hidden = workbook.createSheet(hiddenName);
//        Name category1Name = workbook.createName();
//        category1Name.setNameName(hiddenName);
//        for(Map.Entry<Integer,ExcelSelectorResolve> entry:selectedMap.entrySet()){
//            // 下拉 首行 末行 首列 末列
//            Integer key = entry.getKey();
//            ExcelSelectorResolve entryValue = entry.getValue();
//            CellRangeAddressList list = new CellRangeAddressList(entryValue.getStartRow(), entryValue.getEndRow(), key, key);
//            //获取excel列名
//            String excelLine = getExcelLine(entry.getKey());
//            String[] selectorData = entryValue.getSelectorData();
//            for (int i = 0, length = selectorData.length; i < length; i++) {
//                // 3:表示你开始的行数
//                Row row = hidden.getRow(i);
//                if (row == null) {
//                    row = hidden.createRow(i);
//                }
//                row.createCell(entry.getKey()).setCellValue(selectorData[i]);
//            }
//            String refers = "="+hiddenName + "!$"+excelLine+
//                    "$1:$"+excelLine +"$"+ (selectorData.length);
//            //5 将刚才设置的sheet引用到你的下拉列表中
//            DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
//            DataValidation dataValidation = helper.createValidation(constraint, list);
//            writeSheetHolder.getSheet().addValidationData(dataValidation);
//
//        }
//        //设置列为隐藏
//        int hiddenIndex = workbook.getSheetIndex("hidden");
//        if (!workbook.isSheetHidden(hiddenIndex)) {
//            workbook.setSheetHidden(hiddenIndex, true);
//        }

    }

/**
     * @Description 返回excel列标A-Z-AA-ZZ
     * @param num 列数
     */
    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;
    }

}

三 . 下拉框处理类，获取下拉框处理类

/**
 * @Author: lkz
 * @Title: ExcelSelectorResolve
 * @Description: 下拉选处理类, 方法resolveExcelSelector获取下拉数据
 * @Date: 2023/6/12 17:50
 */
@Data
@Slf4j
public class ExcelSelectorResolve {



    /**
     * 下拉选起始行排除表头 从第一行开始
     */
    private int startRow=1 ;

    /**
     * 下拉选结束行 这里设置最后一行
     */
    private int endRow =0x10000;

    /**
     * 下拉数据集
     */
    private String[] selectorData;

    /**
     * 解决Excel注解的下拉选数据获取
     * @param excelSelector Excel下拉选
     */
    public String[] resolveExcelSelector(ExcelSelector excelSelector) {
        if (excelSelector == null) {
            return null;
        }

        String[] fixedSelector = excelSelector.fixedSelector();
        if (ArrayUtil.isNotEmpty(fixedSelector)) {
            return fixedSelector;
        }
        String[] selectorData = null;
        Class<? extends ExcelSelectorService>[] serviceClass = excelSelector.serviceClass();
        if (ArrayUtil.isNotEmpty(serviceClass)) {
            try {
                ExcelSelectorService excelSelectorService = serviceClass[0].newInstance();
                if (StrUtil.isBlank(excelSelector.dictKeyValue())) {
                    selectorData = excelSelectorService.getSelectorData();
                } else {
                    selectorData = excelSelectorService.getSelectorData(excelSelector.dictKeyValue());
                }
            } catch (InstantiationException | IllegalAccessException e) {
                log.error(e.getMessage(), e);
            }
        }
        return selectorData;
    }



}

四. 下拉接口

/**
 * @Author: lkz
 * @Title: ExcelSelectorService
 * @Description: 退出原因字典下拉
 * @Date: 2023/6/12 17:47
 */

public interface  ExcelSelectorService {

    /**
     * 获取下拉数据
     *
     * @return java.lang.String[]
     */
    String[] getSelectorData();

    /**
     * 根据字典key获取下拉数据
     *
     * @param dictKeyValue 字典key
     * @return java.lang.String[]
     */
    String[] getSelectorData(String dictKeyValue);

}

五 下拉实现

/**
 * @Author: lkz
 * @Title: ExcelSelectorServiceImpl
 * @Description: TODO
 * @Date: 2023/6/12 18:04
 */
@Slf4j
@Service
public class ExcelSelectorServiceImpl implements ExcelSelectorService{
    @Override
    public String[] getSelectorData() {
        return new String[0];
    }

    @Override
    public String[] getSelectorData(String dictKeyValue) {
        SysDictApi dictApi = SpringUtil.getBean(SysDictApi.class);
        List<SysDictDTO> dictDTOS = dictApi.findByDictCode(dictKeyValue, null);
        if (CollUtil.isEmpty(dictDTOS)) {
            return null;
        }
        return dictDTOS.stream().map(SysDictDTO::getDictName).toArray(String[]::new);
    }
}

六 获取下拉注解

public class EasyExcelUtil {
	 /**
     * 获取下拉的map
     * @param clazz 类class
     */
    public static Map<Integer, ExcelSelectorResolve> getSelectedMap(Class<?> clazz) {
        Map<Integer, ExcelSelectorResolve> selectedMap = new ConcurrentHashMap<>();
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            if (!field.isAnnotationPresent(ExcelSelector.class) || !field.isAnnotationPresent(ExcelProperty.class)) {
                continue;
            }
            ExcelSelector excelSelector = field.getAnnotation(ExcelSelector.class);
            ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
            if(excelSelector!=null){
                ExcelSelectorResolve resolve = new ExcelSelectorResolve();
                String[] data = resolve.resolveExcelSelector(excelSelector);
                if (ArrayUtil.isNotEmpty(data)) {
                    resolve.setSelectorData(data);
                    selectedMap.put(excelProperty.index(), resolve);
                }
            }

        }
        return selectedMap;
    }

}

七 实体对象

public class ExportVO {


    @ExcelProperty(value = "退出原因",index = 0) // 索引一定要加上 不然会不起作用
    @ExcelSelector(serviceClass = ExcelSelectorServiceImpl.class, dictKeyValue = "ctms_sub_exit")
    @ColumnWidth(15)
    private String exitReason;

  
}

八.导出

 @Log("受试者导出")
    @PreNotAuthorize
    @GetMapping("exportSubject")
    public void exportSubject(HttpServletResponse response){
        List<CtmsProjectSubjectExportVO> list = ctmsSubjectService.selectExportList();
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 防止中文乱码
            String fileName = URLEncoder.encode("数据导出", "UTF-8")
                    .replaceAll("\\+","%20");
            response.setHeader("Content-disposition", "attachment;filename*=UTF-8''" + fileName + ExcelTypeEnum.XLSX.getValue());
            //响应的输入流
            ServletOutputStream outputStream = response.getOutputStream();
            // workbook
            ExcelWriterBuilder writeWorkBook = EasyExcel.write(outputStream, CtmsProjectSubjectExportVO.class).inMemory(Boolean.TRUE)
                    .head(ExportVO.class)
                    .useDefaultStyle(false)
             		// 下拉框注册器
                    .registerWriteHandler(new ExcelSelectorDataWriteHandler(EasyExcelUtil.getSelectedMap(ExportVO.class)));
                
            // sheet
            writeWorkBook.sheet().sheetName("列表").sheetNo(0).doWrite(list);
            outputStream.flush();
            outputStream.close();

        }catch (IOException e){
            throw ServiceExceptionUtil.exception(new ErrorCode(9999,"导出失败"));
        }catch (IllegalArgumentException e){
            throw ServiceExceptionUtil.exception(new ErrorCode(9999,e.getMessage()));
        }

    }