首页 文章

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

提问于
浏览
3

我正在尝试使用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();

    }
}

}

1 回答

  • 3

    首先,我发现这不是Apache POI错误 . 这是Excel的限制 . 这是link

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

    该列表最多可显示工作表上的列表中的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();
    
        }
    
    }
    

相关问题