使用Apache POI在Excel中生成下拉列表时，是否有最大数量的项目？

Z时代
2024-01-10
分类：问答

我正在尝试使用Apache

POI为一个单元添加一个下拉列表。下拉列表包含302个字符串。我总是收到此错误：Excel在test.xlsx中发现了不可读的内容。

然后，我进行了以下测试。当项目数<= 88时，下拉列表创建成功。当数字> 88时，打开excel文件且没有下拉列表时出现错误。

谢谢！！！

import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.ss.usermodel.*;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.TreeSet;
public class Test {
   public static void main(String[] args) {
    TreeSet<String> temp_rxGroups = new TreeSet<String>();
        for (int i = 0; i < 100; i++) {
            temp_rxGroups.add("" + i);
        }
        String[] countryName = temp_rxGroups.toArray(new String[temp_rxGroups.size()]);
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet realSheet = workbook.createSheet("realSheet");
        XSSFSheet hidden = workbook.createSheet("hidden");
        for (int i = 0, length= countryName.length; i < length; i++) {
            String name = countryName[i];
            XSSFRow row = hidden.createRow(i);
            XSSFCell cell = row.createCell(0);
            cell.setCellValue(name);
        }
        Name namedCell = workbook.createName();
        namedCell.setNameName("hidden");
        namedCell.setRefersToFormula("hidden!$A$1:$A$" + countryName.length);
        DataValidation dataValidation = null;
        DataValidationConstraint constraint = null;
        DataValidationHelper validationHelper = null;
        validationHelper=new XSSFDataValidationHelper(hidden);
        CellRangeAddressList addressList = new  CellRangeAddressList(0,10,0,0);
        //line
        constraint =validationHelper.createExplicitListConstraint(countryName); 
        dataValidation = validationHelper.createValidation(constraint, addressList);
        dataValidation.setSuppressDropDownArrow(true);
        workbook.setSheetHidden(1, true);
        realSheet.addValidationData(dataValidation);
        FileOutputStream stream = new FileOutputStream("c:\\test.xlsx");
        workbook.write(stream);
        stream.close();
    }
}

}

回答：

首先，我发现这不是Apache

POI错误。这是Excel的限制。这是链接，

“在数据验证下拉列表中显示的项目数有限制：

该列表可以显示以显示工作表上的列表中的32,767个项目。如果您在数据验证对话框（定界列表）中键入项目，则限制为256个字符，包括分隔符。”

显然，该行明确输入了256个以上的字符。

constraint =validationHelper.createExplicitListConstraint(countryName);

其次，这是我的解决方案。它工作正常。

public class Test {
    public static void main(String[] args) throws IOException{
        TreeSet<String> temp_rxGroups = new TreeSet<String>();
        for (int i = 0; i < 302; i++) {
            temp_rxGroups.add("" + i);
        }
        String[] countryName = temp_rxGroups.toArray(new String[temp_rxGroups.size()]);
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet realSheet = workbook.createSheet("realSheet");
        XSSFSheet hidden = workbook.createSheet("hidden");
        for (int i = 0, length= countryName.length; i < length; i++) {
            String name = countryName[i];
            XSSFRow row = hidden.createRow(i);
            XSSFCell cell = row.createCell(0);
            cell.setCellValue(name);
        }
        DataValidation dataValidation = null;
        DataValidationConstraint constraint = null;
        DataValidationHelper validationHelper = null;
        validationHelper=new XSSFDataValidationHelper(realSheet);
        CellRangeAddressList addressList = new  CellRangeAddressList(0,0,0,0);
        constraint =validationHelper.createFormulaListConstraint("hidden!$A$1:$A$" + countryName.length);
        dataValidation = validationHelper.createValidation(constraint, addressList);
        dataValidation.setSuppressDropDownArrow(true);
        workbook.setSheetHidden(1, true);
        realSheet.addValidationData(dataValidation);
        FileOutputStream stream = new FileOutputStream("c:\\test.xlsx");
        workbook.write(stream);
        stream.close();
    }
}