使用Apache POI生成Excel下拉列表时的限制

java apache-poi xssf

2022-09-03 09:20:21

我正在尝试生成一个带有一些验证的excel文件，我已经阅读了poi开发指南来实现它。在实现过程中，我得到了一个异常（）。POI将所有下拉选项连接到“0”去利值字符串中，并检查其长度并给我例外。:(
我使用的是最新版本的 POI 3.8 beta 5。
我的代码是：String literals in formulas can't be bigger than 255 characters ASCII

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");

不幸的是，这样的结果是逗号在一个单元格中减去长字符串，没有成功：

enter image description here

但是在Excel中手动，我可以创建带有此长国家/地区列表的下拉单元格。
有没有办法生成带有长字符串的下拉列表，或者API不支持？

答案 1

我明白了，Excel本身不允许输入超过255个字符的验证范围字符串，这不是POI的限制。现在我正在使用命名区域和命名单元格，它对我来说工作正常。因此，我必须将我的验证范围令牌放在另一个工作表中（隐藏），并从我的真实工作表中引用所需的单元格范围。这是我的工作代码：

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();

答案 2

Õzbek 提供的强大解决方案只需稍作修改即可完美地与 NPOI（在 .NET 上使用 C#）配合使用。

这是我的代码，为方便 C# 编码人员而提供。它采用工作表和位置元素作为输入，并且当它们排列在列中时，还可以处理多个下拉列表。

public static void CreateDropDownListForExcel(this ISheet sheet, IList<string> dropDownValues, int startRow, int lastRow, int column) {
    if (sheet == null) {
        return;
    }

    //Create a hidden sheet on the workbook (using the column as an id) with the dropdown values
    IWorkbook workbook = sheet.Workbook;
    string dropDownName = sheet.SheetName + "DropDownValuesForColumn" + column;
    ISheet hiddenSheet = workbook.CreateSheet(dropDownName);
    for (int i = 0, length = dropDownValues.Count; i < length; i++) {
        string name = dropDownValues[i];
        IRow row = hiddenSheet.CreateRow(i);
        ICell cell = row.CreateCell(0);
        cell.SetCellValue(name);
    }

    //Create the dropdown using the fields of the hidden sheet
    IName namedCell = workbook.CreateName();
    namedCell.NameName = dropDownName;
    namedCell.RefersToFormula = (dropDownName + "!$A$1:$A$" + dropDownValues.Count);
    DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(dropDownName);
    CellRangeAddressList addressList = new CellRangeAddressList(startRow, lastRow, column, column);
    HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
    int hiddenSheetIndex = workbook.GetSheetIndex(hiddenSheet);
    workbook.SetSheetHidden(hiddenSheetIndex, SheetState.HIDDEN);

    //Add the Dropdown to the presenting sheet.
    sheet.AddValidationData(validation);
}