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

importjava.util.Map;

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

importcom.alibaba.excel.write.handler.SheetWriteHandler;
importcom.alibaba.excel.write.metadata.holder.WriteSheetHolder;
importcom.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;

public class CustomSheetWriteHandler implementsSheetWriteHandler {

    private Map<Integer, String[]>map;

    private intindex;

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

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

    @Override
    public voidafterSheetCreate(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(newCustemhandler())
                .sheet("导出模板").doWrite(outDto.getDataList());