poi导出Excel输入列表下拉提示

一般情况下，输入列表的提示使用字符串数组，一般情况使用如下方法

    private static void createComboList(Sheet sheet, List<ExcelCode> allowedValues, int firstRow, int endRow, int col) {
        if(ObjectUtils.isNotEmpty(allowedValues) ) {
            List<String> listOfTitles = allowedValues.stream().map(excelColumnDto -> excelColumnDto.getText()).collect(Collectors.toList());
            String[] listOfValues = listOfTitles.toArray(new String[listOfTitles.size()]);
            CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, col, col);

            DataValidationHelper helper = sheet.getDataValidationHelper();
            DataValidationConstraint constraint = helper.createExplicitListConstraint(listOfValues);
            DataValidation dataValidation = helper.createValidation(constraint, addressList);

            if (dataValidation instanceof XSSFDataValidation) {
                // 数据校验
                dataValidation.setSuppressDropDownArrow(true);
                dataValidation.setShowErrorBox(true);
            } else {
                dataValidation.setSuppressDropDownArrow(false);
            }
            sheet.addValidationData(dataValidation);

        }
    }

但是打开保存文件有时候会提示验证加载失败的错误，而输入提示就是一个validation。

在poi保存文件时，其实把数组合并为一个字符串，用一个字符串保存在xml中，再次打开Excel，Excel加载时，估计字符串的最大长度不超过255，如果整个数组合并字符串后长度超过255左右的字符，就整个sheet都无法加载下拉列表输入提示了。

 

1、检查创建下拉列表的接口，发现另外一个方法；

public interface DataValidationHelper {
    DataValidationConstraint createFormulaListConstraint(String var1);

    DataValidationConstraint createExplicitListConstraint(String[] var1);

    DataValidationConstraint createNumericConstraint(int var1, int var2, String var3, String var4);

    DataValidationConstraint createTextLengthConstraint(int var1, String var2, String var3);

    DataValidationConstraint createDecimalConstraint(int var1, String var2, String var3);

    DataValidationConstraint createIntegerConstraint(int var1, String var2, String var3);

    DataValidationConstraint createDateConstraint(int var1, String var2, String var3, String var4);

    DataValidationConstraint createTimeConstraint(int var1, String var2, String var3);

    DataValidationConstraint createCustomConstraint(String var1);

    DataValidation createValidation(DataValidationConstraint var1, CellRangeAddressList var2);
}

2、在Excel中，查看单元格输入提示的设置，其数据来源可以是数组（也就是上面的方法），也可以是源自其他单元格的数据；

所以上面两点有关联，但还是要求助baidu，搜索createFormulaListConstraint

找到如下解决办法

private static void createLargeCountList(Workbook workbook, Sheet sheet, String colName, List<ExcelCode> allowedValues, int firstRow, int endRow, int col) {
        if(ObjectUtils.isEmpty(allowedValues) ) {
            return;
        }
  
        String name = colName.replaceAll("\\*","");
        String sheetName = name + "_Sheet";
        String nameName = name + "_hidden";
        Sheet hideSheet = workbook.createSheet(sheetName);
        for (int i = 0; i < allowedValues.size(); i++) {
            hideSheet.createRow(i).createCell(0).setCellValue(allowedValues.get(i).getText());
        }
        Name category1Name = workbook.createName();
        category1Name.setNameName(nameName);
 
        category1Name.setRefersToFormula(sheetName + "!" + "$A$1:$A$" + allowedValues.size());

        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = helper.createFormulaListConstraint(nameName);

        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, col, col);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
  
        if (dataValidation instanceof XSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
            dataValidation.setSuppressDropDownArrow(false);
        }

        sheet.addValidationData(dataValidation);

        workbook.setSheetHidden(workbook.getSheetIndex(hideSheet), true);
    }