java - 使用 Apache POI 生成 excel 下拉列表时的限制

Question

我正在尝试生成一个带有一些验证的 excel 文件，我已经阅读了 poi dev guides实现它。在实现过程中，我遇到了一个异常(公式中的字符串文字不能大于 255 个 ASCII 字符)。 POI 将所有下拉选项连接成“0”分隔字符串并检查其长度并给我异常。 :(
我正在使用最新版本的 POI 3.8 beta 5。
我的代码是:

try {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("new sheet");
    HSSFRow row = sheet.createRow((short) 0);
    //CellRangeAddressList from org.apache.poi.ss.util package
    CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
    DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(getCountries());
    DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
    dataValidation.setSuppressDropDownArrow(false);
    sheet.addValidationData(dataValidation);
    FileOutputStream fileOut = new FileOutputStream("c:\\test.xls");
    wb.write(fileOut);
    fileOut.close();
    } catch (IOException e) {
       e.printStackTrace();
  }

之后，我尝试使用 XSSFWorkBook 使用此代码:

XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("new sheet");
DataValidationHelper validationHelper = new XSSFDataValidationHelper(sheet);
DataValidationConstraint constraint = validationHelper.createExplicitListConstraint(getCountries());
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
DataValidation dataValidation = validationHelper.createValidation(constraint, addressList);
dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
dataValidation.setSuppressDropDownArrow(true);
sheet.addValidationData(dataValidation);
FileOutputStream fileOut = new FileOutputStream("c:\\test.xlsx");

不幸的是，这样的结果没有成功，它是一个单元格中逗号分隔的长字符串:

但是在 excel 中手动，我可以用这个长国家列表创建下拉单元格。
有什么方法可以生成长字符串的下拉列表，或者 API 不支持？

Answer 1

我明白了，Excel本身不允许输入超过255个字符的验证范围字符串，这不是POI限制。现在我正在使用 Named Ranges and Named Cells它对我来说工作正常。所以我不得不将我的验证范围标记放在另一个工作表中(隐藏)并且我从我的真实工作表中引用了所需的单元格范围。这是我的工作代码:

HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet realSheet = workbook.createSheet("Sheet xls");
HSSFSheet hidden = workbook.createSheet("hidden");
for (int i = 0, length= countryName.length; i < length; i++) {
   String name = countryName[i];
   HSSFRow row = hidden.createRow(i);
   HSSFCell cell = row.createCell(0);
   cell.setCellValue(name);
 }
 Name namedCell = workbook.createName();
 namedCell.setNameName("hidden");
 namedCell.setRefersToFormula("hidden!$A$1:$A$" + countryName.length);
 DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden");
 CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
 HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
 workbook.setSheetHidden(1, true);
 realSheet.addValidationData(validation);
 FileOutputStream stream = new FileOutputStream("c:\\range.xls");
 workbook.write(stream);
 stream.close();