easyexcel下拉框超过50个

一，字典下拉框选项太多

easyexcel最多支持50 解决方案：单独设置隐藏sheet页，将字典放进去

二，代码

1，写一个util，继承 CellWriteHandler

public class PredictHandlerUtil implements CellWriteHandler {
    List<String> data;
    public PredictHandlerUtil(List<String> data) {
        this.data = data;
    }
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        WriteSheet sheet = writeSheetHolder.getWriteSheet();
        if (sheet.getSheetNo() > 0) {
            Workbook workbook = writeSheetHolder.getParentWriteWorkbookHolder().getWorkbook();
            workbook.setSheetHidden(sheet.getSheetNo(), true);
            return;
        }
        if (isHead) {
            setSelectData(writeSheetHolder, head, cell.getRowIndex(), cell.getColumnIndex());
        }
    }
    private void setSelectData(WriteSheetHolder writeSheetHolder, Head head, int rowIndex, int columnIndex) {
        if (!StringUtils.equals(head.getFieldName(), "city") && !StringUtils.equals(head.getHeadNameList().get(GlobalConstant.ZERO_INTEGER), "所在城市")) {
            return;
        }
        Sheet sheet = writeSheetHolder.getSheet();
        DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();

        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(rowIndex, 5000, columnIndex, columnIndex);
        DataValidationConstraint constraint = dataValidationHelper.createFormulaListConstraint("city!$A$1:$A$" + data.size());
        DataValidation validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
        validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
        validation.setShowErrorBox(true);
        validation.setSuppressDropDownArrow(true);
        validation.createErrorBox("提示", "请输入下拉选项中的内容");
        sheet.addValidationData(validation);
    }
    

2,写一个util

public static void writeExcel(HttpServletResponse response, String filename, List<String> citys, Class<ShopExcelVo> shopExcelVoClass, Class<CityDTO> cityDTOClass) throws Exception {
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8") + ".xlsx");

        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
        WriteSheet sheet = EasyExcel.writerSheet(0, PredictConsant.HISTORY_REVENUE_FILE_TEMPLATE)
                .registerWriteHandler(new PredictHandlerUtil(citys, new ArrayList<>()))
                .head(shopExcelVoClass)
                .build();
        WriteSheet citySheet = EasyExcel.writerSheet(1, "city")
                .registerWriteHandler(new PredictHandlerUtil(citys, new ArrayList<>()))
                .head(cityDTOClass)
                .needHead(false)
                .build();
        List<CityDTO> cityData = citys.stream().map(CityDTO::new).collect(Collectors.toList());
        excelWriter.write(new ArrayList<>(), sheet)
                .write(cityData, citySheet)
                .finish();
    }

三，效果