EasyPoi 导出xlsx下拉列表过长问题解决

问题描述：

通过EasyPoi导出Excel带下拉框字段时，下拉框内值超过255时，会报错

String literals in formulas can't be bigger than 255 characters ASCII

解决方案：

额外创建sheet页去存储下拉框内数据，然后从这个sheet页中读取下拉框数据存到下拉列表中，最后需将额外创建的sheet隐藏。

public static void exportExcelLargeReplace(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response,Integer col, String selectSheetName,String[] replaceList){
        exportXlsLargeReplace(list, pojoClass, fileName, response, new ExportParams(title, sheetName),col,selectSheetName,replaceList);
    }

/**
     * 用于导出下拉列表过大的xlsx
     * @param response
     * @throws IOException
     */
    public static void exportXlsLargeReplace(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams,Integer col, String selectSheetName,String[] replaceList) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        selectList(selectSheetName, 1, workbook, col, col, replaceList);
        downLoadExcel(fileName, response,workbook);
    }

/**
     * 用于处理下拉列表过大
     *
     * @param sheetName
     * @param index
     * @param workbook
     * @param firstCol
     * @param lastCol
     * @param strings
     */
    public static void selectList(String sheetName, Integer index, Workbook workbook, int firstCol, int lastCol, String[] strings) {

        // 创建隐藏的sheet用于存放下拉列表数据
        Sheet hidden = workbook.createSheet(sheetName);

        // 创建单元格对象
        Cell cell = null;

        // 遍历数据数组，将数据放入隐藏的sheet中
        for (int i = 0; i < strings.length; i++) {
            String name = strings[i];
            Row row = hidden.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(name);
        }

        // 创建名称，用于引用下拉列表数据
        Name namedCell = workbook.createName();
        namedCell.setNameName(sheetName);
        // 设置引用的公式
        namedCell.setRefersToFormula(sheetName + "!$A$1:$A$" + strings.length);

        // 创建下拉列表约束
        DataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) hidden);
        DataValidationConstraint constraint = dvHelper.createFormulaListConstraint(sheetName);
        CellRangeAddressList addressList = new CellRangeAddressList(0, 65535, firstCol, lastCol);
        DataValidation validation = dvHelper.createValidation(constraint, addressList);

        // 将下拉列表约束应用到工作簿中的相应单元格区域
        workbook.setSheetHidden(index, true);
        Sheet sheet = workbook.getSheetAt(0);
        sheet.addValidationData(validation);
    }