Are there any maximum items when creating a dropdown in Excel using Apache POI?

Question

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

Then I performed the following test. When the number of elements <= 88, the drop-down list was created successfully. When the number is> 88, I got an error opening the excel file and not a single drop down list.

Thank!!!

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

    }
}

}

Answer 1

Firstly, I found that this is not an Apache POI error. This is a limitation from Excel. This is a 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();

    }

}