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

 EasyExcel导出带下拉框，并解决导出之后打开总是显示发现不可读取内容_EasyExcel

 

因为我有多列需要下拉框，所有用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 map;    private int index;    public CustomSheetWriteHandler(Map 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引用到你的下拉列表中
            CellRangeAddressList addressList = new CellRangeAddressList(1, 50, k, k);
            DataValidationConstraint constraint8 = helper.createFormulaListConstraint(sheetName);
            DataValidation dataValidation3 = helper.createValidation(constraint8, addressList);
            writeSheetHolder.getSheet().addValidationData(dataValidation3);

        });
    }

}

 

调用：

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