是否有最大数量的物品，而使用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.

然后我做了以下测试。当项目＆lt数= 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屋！