poi设置下拉列表报错?

Question

前端请求文件模板,代码生成excel模板。其中有一列数据要做成下拉的模式。
功能也实现了。
但是如果下拉列表的下拉项过多。下载出的文件模板，下拉项点击无法展示出来。20多个下拉选项是可以正确下拉的，40多个就不行了。这个如果解决？？

            //下拉选项
            String[] departList = list.subList(0,35).stream().map(v->{
                return v.getDepartName();
            }).distinct().toArray(String[]::new);
            //设置下拉的选项
            XSSFDataValidationHelper helper = new XSSFDataValidationHelper(sheet);
            DataValidationConstraint constraint = helper.createExplicitListConstraint(departList);
            //设置影响的行和列
            CellRangeAddressList addressList = new CellRangeAddressList(1, 300, 2, 2);
            //设置校验
            DataValidation validation = helper.createValidation(constraint, addressList);
            validation.setSuppressDropDownArrow(true);
            validation.setShowErrorBox(true);
            validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
            validation.createErrorBox("提示","部门不存在!");
            sheet.addValidationData(validation);

Answer 1

XSSFSheet refSheet = workbook.createSheet("refSheet");
for (int i = 0; i < departList.length; i++) {
    XSSFRow row = refSheet.createRow(i);
    XSSFCell cell = row.createCell(0);
    cell.setCellValue(departList[i]);
}

String formula = "refSheet!$A$1:$A$" + departList.length;
DataValidationConstraint constraint = helper.createFormulaListConstraint(formula);
// ...其余设置相同