EasyExcel导出设置下拉框和隐藏列

        TimeInterval timer = DateUtil.timer();
        String fileName = "import_clue_template_" + TimeUtil.getDateTimeString() + ".xlsx";
        // 默认中文
        String language = servletResponse.getHeader("Accept-Language");
        String lang = StringUtils.isEmpty(language) ? "zh" : language.substring(0, 2);
        ExcelUtils.excelWriteResponse(fileName, servletResponse);
        EasyExcel.write(servletResponse.getOutputStream(), ClueImportExcelVO.class)
                .registerWriteHandler(new ExcelTitleHandler(this.getDropDownMap(), new int[]{10, 11}))
                .head(ExcelUtils.chooseLang(ClueImportExcelVO.class, lang))
                .sheet(fileName)
                .doWrite(ListUtil.toList());
        log.info("下载导入模板,花费{}毫秒", timer.intervalMs());

@Slf4j
public class ExcelTitleHandler implements SheetWriteHandler {

    /**
     * 下拉框值
     */
    private final Map<Integer, String[]> dropDownMap;

    private final int[] hideIndex;

    private final char[] alphabet = new char[]{'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L',
            'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'};

    /**
     * dropDownMap key 列索引从 0 开始 value 下拉框的值
     * hideIndex 需要隐藏的列索引从 0 开始
     **/
    public ExcelTitleHandler(Map<Integer, String[]> dropDownMap, int[] hideIndex) {
        this.dropDownMap = dropDownMap;
        this.hideIndex = hideIndex;
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        // 需要设置的sheet页
        Sheet sheet = writeSheetHolder.getSheet();

        for (int index : hideIndex) {
            sheet.setColumnHidden(index, true);
        }

        if (dropDownMap == null || dropDownMap.isEmpty()) {
            return;
        }

        //设置下拉框
        DataValidationHelper helper = sheet.getDataValidationHelper();

        for (Map.Entry<Integer, String[]> entry : dropDownMap.entrySet()) {
            //定义sheet的名称
            String hiddenName = "hidden" + entry.getKey();
            //1.创建一个隐藏的sheet 名称为 hidden
            Workbook workbook = writeWorkbookHolder.getWorkbook();
            Sheet hidden = workbook.createSheet(hiddenName);
            Name category1Name = workbook.createName();
            category1Name.setNameName(hiddenName);
            //下拉框的起始行（第2行，索引从0开始）,结束行（Excel最大行1048575）,起始列,结束列  
            CellRangeAddressList addressList = new CellRangeAddressList(1, 1048575, entry.getKey(), entry.getKey());
            //获取excel列名
            String excelLine = getExcelColumn(entry.getKey());
            //2.循环赋值
            String[] values = entry.getValue();
            for (int i = 0, length = values.length; i < length; i++) {
                // 3:表示你开始的行数  3表示 你开始的列数
                Row row = hidden.getRow(i);
                if (row == null) {
                    row = hidden.createRow(i);
                }
                row.createCell(entry.getKey()).setCellValue(values[i]);
            }
            //4.  =hidden!$H:$1:$H$50  sheet为hidden的 H1列开始H50行数据获取下拉数组
            String refers = "=" + hiddenName + "!$" + excelLine + "$1:$" + excelLine + "$" + (values.length);
            //5 将刚才设置的sheet引用到你的下拉列表中
            DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
            DataValidation dataValidation = helper.createValidation(constraint, addressList);
            // 阻止输入非下拉选项的值
            dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
            dataValidation.setShowErrorBox(true);
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.createErrorBox("title", "formal error");
            writeSheetHolder.getSheet().addValidationData(dataValidation);
            //设置列为隐藏
            int hiddenIndex = workbook.getSheetIndex(hiddenName);
            if (!workbook.isSheetHidden(hiddenIndex)) {
                workbook.setSheetHidden(hiddenIndex, true);
            }
        }
    }

    /**
     * 将数字列转化成为字母列
     */
    private String getExcelColumn(int num) {
        String nullStr = "";
        String column;
        int len = alphabet.length - 1;
        int first = num / len;
        int second = num % len;
        if (num <= len) {
            column = alphabet[num] + nullStr;
        } else {
            column = alphabet[first - 1] + nullStr;

            column = second == 0 ? column + alphabet[len] + nullStr : column + alphabet[second - 1] + nullStr;
        }
        return column;
    }

}