EasyExel解决下拉选超过50个的问题

    @GetMapping("/download")
    @ApiOperation(value = "下载导入模板")
    public void download(@RequestParam(required = true) Integer projectId, HttpServletResponse response) throws IOException {
        //导出模板名称
        String fileName = "导入模板";
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        //这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileNameEncode = URLEncoder.encode(fileName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileNameEncode + ExcelTypeEnum.XLS.getValue());
        //设置下拉框内容
        Map<Integer, List<String>> selectMap = new HashMap<>();
        //模拟下拉选内容
        List<String> list1 = new ArrayList<>();
        list1.add("硬件传输");
        list1.add("人工录入");
        List<String> list2 = new ArrayList<>();
        list2.add("r/min");
        ...
        list2.add("m³");
        selectMap.put(1, list1);
        selectMap.put(2, list2);
        //表头
        List<EnergyType> list = new ArrayList<>();
        EasyExcelFactory.write(response.getOutputStream())
                .registerWriteHandler(new SelectDataSheetWriteHandler(selectMap))//设置字典
                .registerWriteHandler(new SimpleColumnWidthStyleStrategy(17))//设置列宽度
                .registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 28, (short) 18))//设置行高度
                .head(EnergyType.class)//此处对应的是实体类
                .excelType(ExcelTypeEnum.XLS)//设置导出格式为xls后缀
                .sheet("sheet1")
                .doWrite(list);
    }

package com.energy.util;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.util.List;
import java.util.Map;

/**
 * @author: CUI
 * @date: 2022-05-27 9:11
 */
public class SelectDataSheetWriteHandler implements SheetWriteHandler {
    private Map<Integer, List<String>> selectMap;

    private 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'};

    public SelectDataSheetWriteHandler(Map<Integer, List<String>> selectMap) {
        this.selectMap = selectMap;
    }

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

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        if (selectMap == null || selectMap.size() == 0) {
            return;
        }

        // 需要设置下拉框的sheet页
        Sheet curSheet = writeSheetHolder.getSheet();
        DataValidationHelper helper = curSheet.getDataValidationHelper();
        String dictSheetName = "sheet2";
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        // 数据字典的sheet页
        Sheet dictSheet = workbook.createSheet(dictSheetName);
        // 隐藏数据字典的sheet页
        workbook.setSheetHidden(workbook.getSheetIndex(dictSheet), true);
        for (Map.Entry<Integer, List<String>> entry : selectMap.entrySet()) {
            // 设置下拉单元格的首行、末行、首列、末列
            CellRangeAddressList rangeAddressList = new CellRangeAddressList(1, 65533, entry.getKey(), entry.getKey());
            int rowLen = entry.getValue().size();
            // 设置字典sheet页的值 每一列一个字典项
            for (int i = 0; i < rowLen; i++) {
                Row row = dictSheet.getRow(i);
                if (row == null) {
                    row = dictSheet.createRow(i);
                }
                row.createCell(entry.getKey()).setCellValue(entry.getValue().get(i));
            }
            String excelColumn = getExcelColumn(entry.getKey());
            // 下拉框数据来源 eg:字典sheet!$B1:$B2
            String refers = dictSheetName + "!$" + excelColumn + "$1:$" + excelColumn + "$" + rowLen;
            // 创建可被其他单元格引用的名称
            Name name = workbook.createName();
            // 设置名称的名字
            name.setNameName("dict" + entry.getKey());
            // 设置公式
            name.setRefersToFormula(refers);
            // 设置引用约束
            DataValidationConstraint constraint = helper.createFormulaListConstraint("dict" + entry.getKey());
            // 设置约束
            DataValidation validation = helper.createValidation(constraint, rangeAddressList);
            if (validation instanceof HSSFDataValidation) {
                validation.setSuppressDropDownArrow(false);
            } else {
                validation.setSuppressDropDownArrow(true);
                validation.setShowErrorBox(true);
            }
            // 阻止输入非下拉框的值
            validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
            validation.createErrorBox("提示", "此值与单元格定义格式不一致！");
            // 添加下拉框约束
            writeSheetHolder.getSheet().addValidationData(validation);
        }
    }

    /**
     * 将数字列转化成为字母列
     * @param num
     * @author: CUI
     * @date: 2022-05-27 9:12
     * @return: java.lang.String
     */
    private String getExcelColumn(int num) {
        String column = "";
        int len = alphabet.length - 1;
        int first = num / len;
        int second = num % len;
        if (num <= len) {
            column = alphabet[num] + "";
        } else {
            column = alphabet[first - 1] + "";
            if (second == 0) {
                column = column + alphabet[len] + "";
            } else {
                column = column + alphabet[second - 1] + "";
            }
        }
        return column;
    }
}

package com.energy.model.entity;


import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.validation.constraints.NotBlank;
import java.util.Date;

/**
 * (EnergyType)表实体类
 *
 * @author CUI
 * @since 2022-05-25 20:31:09
 */
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@TableName("energy_type")
public class EnergyType extends Model<EnergyType> {
    //唯一标识
    @ExcelIgnore
    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;
    //类型名称
    @ApiModelProperty(value = "类型名称")
    @ExcelProperty(value = "类型名称" , index = 0)
    @NotBlank(message = "类型名称不能为空")
    private String name;
    //数据来源
    @ApiModelProperty(value = "数据来源")
    @ExcelProperty(value = "数据来源" , index = 1)
    @NotBlank(message = "数据来源不能为空")
    private String source;
    //计量单位
    @ApiModelProperty(value = "计量单位")
    @ExcelProperty(value = "计量单位" , index = 2)
    @NotBlank(message = "计量单位不能为空")
    private String measure;
    //计费方式
    @ExcelIgnore
    @ApiModelProperty(value = "计费方式")
    private String billType;
    //描述
    @ExcelProperty(value = "描述" , index = 3)
    @ApiModelProperty(value = "描述")
    private String description;
    //创建人ID
    @ExcelIgnore
    @ApiModelProperty(value = "创建人ID")
    private Integer inUserSysNo;
    //修改人ID
    @ExcelIgnore
    @ApiModelProperty(value = "修改人ID")
    private Integer editUserSysNo;
    //是否生效
    @ExcelIgnore
    @ApiModelProperty(value = "是否生效")
    private Integer commonStatus;
    //创建人用户名
    @ExcelIgnore
    @ApiModelProperty(value = "创建人用户名")
    private String creator;
    //创建时间
    @ExcelIgnore
    @ApiModelProperty(value = "创建时间")
    private Date createTime;
    //修改时间
    @ExcelIgnore
    @ApiModelProperty(value = "修改时间")
    private Date updateTime;
    //所属项目ID
    @ExcelIgnore
    @ApiModelProperty(value = "所属项目ID")
    private Integer projectId;
}