excel下拉框超过255后office打开报部分内容有问题

导出Excel数据用的是EasyExcel插件，为了解决excel下拉框超过255无法正常显示问题，网上找了一大堆解决方案，都类似，wps都可以正常显示，但是office打开报部分内容有问题提示，踩了一波坑。

CellSelect 下拉框实体

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class CellSelect {
    private int col;
    private String[] values;
}

SelectSheetWriteHandler 下拉框写入处理类

public class SelectSheetWriteHandler implements SheetWriteHandler {

    private List<CellSelect> cellSelectList;

    /**
     * 设置阈值，避免生成的导入模板下拉值获取不到
     */
    private static final Integer LIMIT_NUMBER = 255;

    public SelectSheetWriteHandler(List<CellSelect> cellSelectList) {
        this.cellSelectList = cellSelectList;
    }


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

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        if (cellSelectList == null) {
            return;
        }
        // 区间设置 第一列第一行和第二行的数据。由于第一行是头，所以第一、二行的数据实际上是第二三行
        for (int i = 0; i < cellSelectList.size(); i++) {
            CellSelect cellSelect = cellSelectList.get(i);
            CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 10000, cellSelect.getCol(), cellSelect.getCol());
            DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();

            if (cellSelect.getValues() != null && cellSelect.getValues().length > 0) {
                String[] values = cellSelect.getValues();
                // 如果下拉值总数大于255，则使用一个新sheet存储，避免生成的导入模板下拉值获取不到
                if (getTotalLen(values)>=LIMIT_NUMBER){
                    //定义sheet的名称
                    addDropDownList(writeWorkbookHolder.getWorkbook(),writeSheetHolder.getSheet(),cellSelect.getValues(),cellRangeAddressList,cellSelect.getCol());
                }else{
                    DataValidationConstraint constraint = helper.createExplicitListConstraint(cellSelect.getValues());
                    DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
                    // 阻止输入非下拉选项的值
                    dataValidation.setSuppressDropDownArrow(true);
                    dataValidation.setShowErrorBox(true);
                    writeSheetHolder.getSheet().addValidationData(dataValidation);

                }

            }

        }

    }


    /**
     * 单元格添加下拉菜单(不限制菜单可选项个数)<br/>
     * [注意：此方法会添加隐藏的sheet，可调用getDataSheetInDropMenuBook方法获取用户输入数据的未隐藏的sheet]<br/>
     * [待添加下拉菜单的单元格 -> 以下简称：目标单元格]
     * @param @param workbook
     * @param @param tarSheet 目标单元格所在的sheet
     * @param @param menuItems 下拉菜单可选项数组
     * @param @param cellRangeAddressList
     * @param @param column 待添加下拉菜单的单元格所在的列(从0开始)
     */
    public  void addDropDownList(Workbook workbook, Sheet tarSheet, String[] menuItems, CellRangeAddressList cellRangeAddressList, int column)
    {

        //必须以字母开头，最长为31位
        String hiddenSheetName = "hiddenSheet" + (int) ((Math.random() * 9 + 1) * 100000);
        Sheet hiddenSheet = workbook.createSheet(hiddenSheetName);//用于存储 下拉菜单数据
        Row row = null;
        Cell cell = null;
        //隐藏sheet中添加菜单数据
        for (int i = 0; i < menuItems.length; i++)
        {
            row = hiddenSheet.createRow(i);
            cell = row.createCell(column);
            cell.setCellValue(menuItems[i]);
        }
        Name category1Name = workbook.createName();
        category1Name.setNameName(hiddenSheetName);
        String excelLine = getExcelLine(column);
        // =hidden!$H:$1:$H$50  sheet为hidden的 H1列开始H50行数据获取下拉数组
        String refers = hiddenSheetName + "!$" + excelLine + "$1:$" + excelLine + "$" + menuItems.length;
        category1Name.setRefersToFormula(refers);

        // 将刚才设置的sheet引用到你的下拉列表中
        DataValidationHelper helper = tarSheet.getDataValidationHelper();
        DataValidationConstraint constraint = helper.createFormulaListConstraint(hiddenSheetName);
        DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
        // 阻止输入非下拉选项的值
        dataValidation.setSuppressDropDownArrow(true);
        dataValidation.setShowErrorBox(true);
        tarSheet.addValidationData(dataValidation);
        // 设置存储下拉列值得sheet为隐藏
        int hiddenIndex = workbook.getSheetIndex(hiddenSheetName);
        if (!workbook.isSheetHidden(hiddenIndex)) {
            workbook.setSheetHidden(hiddenIndex, true);
        }
    }


    private Integer getTotalLen(String[] strs){
        if(strs == null || strs.length == 0){
            return 0;
        }
        int len = 0;
        for(String str : strs){
            len += str.length();
        }
        return len;
    }
    private String getExcelLine(int num) {
        String line = "";
        int first = num / 26;
        int second = num % 26;
        if (first > 0) {
            line = (char) ('A' + first - 1) + "";
        }
        line += (char) ('A' + second) + "";
        return line;
    }
}

EasyExcel调用示例

   private void writeSheet(ExcelWriter excelWriter, int num, DeviceSheetInfo deviceSheetInfo, List<CellSelect> cellSelectList) {
        // 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样。这里注意DemoData.class 可以每次都变，我这里为了方便 所以用的同一个class 实际上可以一直变
        WriteSheet writeSheet = EasyExcel.writerSheet(deviceSheetInfo.getName())
                .registerWriteHandler(new SelectSheetWriteHandler(cellSelectList)).head(deviceSheetInfo.getClazz()).build();
        // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
        excelWriter.write(Collections.emptyList(), writeSheet);
    }