使用 Apache POI 在 Excel 中生成下拉列表时是否有最大数量的项目? [英] Is there a max number items while generating drop down list in Excel using Apache POI?

问题描述

我正在尝试使用 Apache POI 为一个单元格添加下拉列表.下拉列表包含 302 个字符串.我总是收到这个错误:Excel 在 test.xlsx 中发现不可读的内容.

I am trying to add a drop down list for one cell using Apache POI. The drop down list contains 302 Strings. I always got this error: Excel found unreadable content in test.xlsx.

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

Then I did the following test. When number of items <=88, the drop down list created successfully. When the number >88, I got an error when opening the excel file and no drop down list.

谢谢！！！

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 的限制.这是链接、

First, I found this is not an Apache POI bug. It is a limitation from Excel. This is the link,

数据验证下拉列表中显示的项目数量有限制:

"There are limits to the number of items that will show in a data validation drop down list:

列表最多可以显示工作表上的列表中的 32,767 个项目.如果您在数据验证对话框(分隔列表)中键入项目，则限制为 256 个字符，包括分隔符."

The list can show up to show 32,767 items from a list on the worksheet. If you type the items into the data validation dialog box (a delimited list), the limit is 256 characters, including the separators."

显然，这一行明确输入了超过 256 个字符.

Obviously, this line explicitly types more than 256 characters.

constraint =validationHelper.createExplicitListConstraint(countryName);

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

Second, this is my solution. It works fine.

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

    }

}

这篇关于使用 Apache POI 在 Excel 中生成下拉列表时是否有最大数量的项目?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！