Excel导出表格时，下拉框数据长度超过255出现的问题及解决办法

1、直接添加下拉框，数据量过多会有问题

/**
     * 创建下拉列表选项(单元格下拉框数据小于255字节时使用)
     *
     * @param sheet    所在Sheet页面
     * @param values   下拉框的选项值
     * @param firstRow 起始行（从0开始）
     * @param lastRow  终止行（从0开始）
     * @param firstCol 起始列（从0开始）
     * @param lastCol  终止列（从0开始）
     */
    public void createDropDownList(Sheet sheet, String[] values, int firstRow, int lastRow, int firstCol, int lastCol) {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        DataValidationConstraint constraint = helper.createExplicitListConstraint(values);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        if (dataValidation instanceof HSSFDataValidation ) {
            dataValidation.setSuppressDropDownArrow(false);
        } else {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        }
        sheet.addValidationData(dataValidation);
    }

2、使用隐藏sheet的方式实现

/**
     * 隐藏Sheet方式创建下拉框(单元格下拉框数据大于255字节时使用)
     *
     * @param sheet  需要添加下拉框的Sheet
     * @param firstRow 起始行
     * @param firstCol 其实列
     * @param endRow   终止行
     * @param endCol   终止列
     * @param dataArray  下拉框数组
     * @param wbCreat    所在excel的WorkBook，用于创建隐藏Sheet
     * @param hidddenSheetName    隐藏Sheet的名称
     * @return
     */
    public void createDropDownListWithHiddenSheet(Sheet sheet, int firstRow,
                                                  int firstCol, int endRow,
                                                  int endCol, String[] dataArray,
                                                  Workbook wbCreat,
                                                  String hidddenSheetName) {

        Sheet hidden = wbCreat.createSheet(hidddenSheetName);
        Cell cell = null;
        for (int i = 0, length = dataArray.length; i < length; i++) {
            String name = dataArray[i];
            Row row = hidden.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(name);
        }
        Name namedCell = wbCreat.createName();
        namedCell.setNameName(hidddenSheetName);
        namedCell.setRefersToFormula(hidddenSheetName + "!$A$1:$A$" + dataArray.length);
        //sheet设置为隐藏
        wbCreat.setSheetHidden(wbCreat.getSheetIndex(hidden), true);
        //加载数据,将名称为hidden的
        DataValidationConstraint constraint = null;
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol,
                endCol);
        // 创建 DataValidation
        DataValidation validation = null;
        if (sheet instanceof XSSFSheet || sheet instanceof SXSSFSheet) {
            DataValidationHelper dvHelper = sheet.getDataValidationHelper();
            constraint = dvHelper.createFormulaListConstraint(hidddenSheetName);
            validation = dvHelper.createValidation(constraint, addressList);
        } else {
            constraint = DVConstraint.createFormulaListConstraint(hidddenSheetName);
            validation = new HSSFDataValidation(addressList, constraint);
        }
        if (validation instanceof HSSFDataValidation ) {
            validation .setSuppressDropDownArrow(false);
        } else {
            validation .setSuppressDropDownArrow(true);
            validation .setShowErrorBox(true);
        }
        sheet.addValidationData(validation);
    }

3、多选下拉框

由于Excel要做成多选下拉框，是需要用给Excel写宏的方式实现，但是使用POI没办法给Excel写宏，所以我们使用读取一个有Excel的宏的模板，然后往这个Excel里面写宏的方式实现

首先我们需要一个有宏的模板

• 打开excel->文件->选项->自定义功能区->右侧的开发工具点击√
• 开发工具->Visual Basic->双击需要添加宏的sheet->在右侧粘贴下面的代码

Sub Worksheet_Change(ByVal Target As Range)
'让数据有效性选择 可以多选,不可重复
Dim rngDV As Range
Dim oldVal As String

Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
'do nothing

Else
Application.EnableEvents = False
newVal = Target.Value
If InStr(Cells(3, Target.Column), "多选") Then '本列的第三行的单元格是否包含对选    也可以直接用数字，数字是你想要多选的列是多少，多个用or连接。Target.Column = 7 Or 9
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If oldVal = "" Then
Else
If newVal = "" Then
Else '去除重复的字段
       If InStr(1, oldVal, newVal) <> 0 Then
          If InStr(1, oldVal, newVal) + Len(newVal) - 1 = Len(oldVal) Then '最后一个选项重复
            Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 1)
          Else
            Target.Value = Replace(oldVal, newVal & ",", "") '不是最后一个选项重复的时候处理逗号
          End If
        Else '不是重复选项就视同增加选项
Target.Value = oldVal _
& "," & newVal '可以是任意符号隔开
End If
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub

4、参考：

链接地址：https://gitee.com/lemur/easypoi/issues/I4Y4TE

分为两个版本
若导出xls格式的excel时，下拉框列表字符串长度超过255时，会报出异常。这个属于poi源码中的异常，不好修改。
若导出xlsx格式的excel时，下拉框列表字符串长度超过255时，会自动取消下拉框。
在实际应用中遇到了绕不开的问题，必须解决，所以参考了创建新的隐藏sheet页，再将sheet页中数据引用至下拉框中方法。
需要修改两个地方，具体如下：
cn\afterturn\easypoi\excel\export\ExcelExportService.java 中 createSheetForMap方法。
cn\afterturn\easypoi\excel\export\ExcelExportService.java 中 createAddressList方法

public void createSheetForMap(Workbook workbook, ExportParams entity,
                                  List<ExcelExportEntity> entityList, Collection<?> dataSet) {
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug("Excel version is {}",
                    entity.getType().equals(ExcelType.HSSF) ? "03" : "07");
        }
        if (workbook == null || entity == null || entityList == null || dataSet == null) {
            throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
        }
        super.type = entity.getType();
        if (type.equals(ExcelType.XSSF)) {
            MAX_NUM = 1000000;
        }
        if (entity.getMaxNum() > 0) {
            MAX_NUM = entity.getMaxNum();
        }
        Sheet sheet = null;
        try {
            sheet = workbook.createSheet(entity.getSheetName());
        } catch (Exception e) {
            // 重复遍历,出现了重名现象,创建非指定的名称Sheet
            sheet = workbook.createSheet();
        }
        if (dataSet.getClass().getClass().getName().contains("Unmodifiable")) {
            List dataTemp = new ArrayList<>();
            dataTemp.addAll(dataSet);
            dataSet = dataTemp;
        }

        //-------------修改这里，以下为新增代码--------------
        //---在生成EXCEL数据验证功能时，将下拉框中数据长度----
        //---大于255的数据，放入一个隐藏sheet页中，在引用-----
        //---至之前的位置上。-------------------------------
        dictHandler = entity.getDictHandler();
        Map<Integer,List<String>> tmpMap = new HashMap<>();
        for (int i = 0; i < entityList.size(); i++) {
            if (entityList.get(i).isAddressList()) {
                ExcelExportEntity eee = entityList.get(i);
                String[] addressLists = getAddressListValues(eee);
                if (StrUtil.totalLength(addressLists) > 255){
                    tmpMap.put(i,Arrays.asList(addressLists));
                }
            }
        }
        // 创建隐藏的sheet页，将长下拉框的值，依次存入其中。
        String[] colIndex = {"A","B","C","D","E","F","G","H","I","G","K","L"};
        Sheet hideSheet = workbook.createSheet("hiddenSheet");
        if (tmpMap.size() > 0){
            int col = 0;
            for (Map.Entry<Integer,List<String>> entry : tmpMap.entrySet()){
                // 从第一列开始，每个第一行存放 key值
                for (int i = 0; i < entry.getValue().size(); i++) {
                    hideSheet.createRow(i).createCell(col).setCellValue(entry.getValue().get(i));
                }
                // 创建名称，可被其他单元格引用
                Name refName = workbook.createName();
                refName.setNameName("hidden");
                // 设置名称引用的公式
                refName.setRefersToFormula("hiddenSheet!" + "$"+colIndex[col]+"$1:$"+colIndex[col]+"$"+entry.getValue().size());
                // 获取上文名称内数据
                DataValidationHelper helper = sheet.getDataValidationHelper();
                DataValidationConstraint constraint = helper.createFormulaListConstraint("hidden");
                // 设置下拉框位置
                CellRangeAddressList addressList = new CellRangeAddressList(1, 10000, entry.getKey(), entry.getKey());
                DataValidation dataValidation = helper.createValidation(constraint, addressList);
                sheet.addValidationData(dataValidation);
                col++;
            }
        }
        //-----------------结束------------------------

        insertDataToSheet(workbook, entity, entityList, dataSet, sheet);
        if (entity.isReadonly()) {
            sheet.protectSheet(UUID.randomUUID().toString());
        }
        sheet.setForceFormulaRecalculation(true);
        if (isAutoSize(entity, entityList)) {
            int len0 = 0, len1 = 0;
            if (sheet.getRow(0) !=null){
                len0 = sheet.getRow(0).getLastCellNum();
            }
            if (sheet.getRow(1) !=null){
                len1 = sheet.getRow(1).getLastCellNum();
            }
            int len = Math.max(len0,len1);
            for (int i = 0; i < len; i++) {
                sheet.autoSizeColumn(i, true);
            }
        }
    }

private int createAddressList(Sheet sheet, int index, List<ExcelExportEntity> excelParams, int cellIndex) {
        for (int i = 0; i < excelParams.size(); i++) {
            if (excelParams.get(i).isAddressList()) {
                ExcelExportEntity entity = excelParams.get(i);
                CellRangeAddressList regions = new CellRangeAddressList(index,
                        this.type.equals(ExcelType.XSSF) ? 1000000 : 65535, cellIndex, cellIndex);

                //---------------修改位置-----------------------------
                //---若数据验证下拉框总长度大于255，则不进行下面的操作----
                String[] addressList = getAddressListValues(entity);
                if (StrUtil.totalLength(addressList) < 255){
                    //-----------------------结束-------------------------------------------------
                    DataValidation dataValidation = sheet.getDataValidationHelper().createValidation(sheet.getDataValidationHelper().createExplicitListConstraint(getAddressListValues(entity)), regions);
                    // 处理Excel兼容性问题
                    if (dataValidation instanceof XSSFDataValidation) {

                        dataValidation.setSuppressDropDownArrow(true);
                        dataValidation.setShowErrorBox(false);
                    } else {
                        dataValidation.setSuppressDropDownArrow(false);
                    }
                    sheet.addValidationData(dataValidation);
                }

            }
            if (excelParams.get(i).getList() != null) {
                cellIndex = createAddressList(sheet, index, excelParams.get(i).getList(), cellIndex);
            } else {
                cellIndex++;
            }
        }
        return cellIndex;
    }