参考了这位大佬的代码：https://blog.csdn.net/qq_42747210/article/details/113063645

 主要原因是：excel本身每个单元格限制了255个字符，下拉框总字符超过255就无法正常打开

 

 

因为我有多列需要下拉框，所有用Map来装数据，key为需要下拉框的列，value为该列下拉框的值；我只是设置了序号为1到50的行需要下拉框；

import java.util.Map;

import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;

public class CustomSheetWriteHandler implements SheetWriteHandler {

    private Map<Integer, String[]> map;

    private int index;

    public CustomSheetWriteHandler(Map<Integer, String[]> map) {
        this.map = map;
        this.index = 0;
    }

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        // TODO Auto-generated method stub

    }

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

        DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();

        // k 为存在下拉数据集的单元格下表 v为下拉数据集
        map.forEach((k, v) -> {
            // 创建sheet，突破下拉框255的限制
            // 获取一个workbook
            Workbook workbook = writeWorkbookHolder.getWorkbook();
            // 定义sheet的名称
            String sheetName = "sheet" + k;
            // 1.创建一个隐藏的sheet 名称为 proviceSheet
            Sheet proviceSheet = workbook.createSheet(sheetName);
            // 从第二个工作簿开始隐藏
            this.index++;
            // 设置隐藏
            workbook.setSheetHidden(this.index, true);
            // 2.循环赋值（为了防止下拉框的行数与隐藏域的行数相对应，将隐藏域加到结束行之后）
            for (int i = 0, length = v.length; i < length; i++) {
                // i:表示你开始的行数 0表示你开始的列数
                proviceSheet.createRow(i).createCell(0).setCellValue(v[i]);
            }
            Name category1Name = workbook.createName();
            category1Name.setNameName(sheetName);
            // 4 $A$1:$A$N代表 以A列1行开始获取N行下拉数据
            category1Name.setRefersToFormula(sheetName + "!$A$1:$A$" + (v.length));
            // 5 将刚才设置的sheet引用到你的下拉列表中,1表示从行的序号1开始（开始行，通常行的序号为0的行是表头），50表示行的序号50（结束行），表示从行的序号1到50，k表示开始列序号和结束列序号
            CellRangeAddressList addressList = new CellRangeAddressList(1, 50, k, k);
            DataValidationConstraint constraint8 = helper.createFormulaListConstraint(sheetName);
            DataValidation dataValidation3 = helper.createValidation(constraint8, addressList);
　　　　　　　// 阻止输入非下拉选项的值
           dataValidation3.setErrorStyle(DataValidation.ErrorStyle.STOP);
           dataValidation3.setShowErrorBox(true);
           dataValidation3.setSuppressDropDownArrow(true);
           dataValidation3.createErrorBox("提示", "此值与单元格定义格式不一致");
           // validation.createPromptBox("填写说明：","填写内容只能为下拉数据集中的单位，其他单位将会导致无法入仓");
           writeSheetHolder.getSheet().addValidationData(dataValidation3);

        });
    }

}

 

调用：

EasyExcel.write(response.getOutputStream()).head(outDto.getHeadList())
                .registerWriteHandler(new CustomSheetWriteHandler(map)).registerWriteHandler(new Custemhandler())
                .sheet("导出模板").doWrite(outDto.getDataList());