Is there a max number items while generating drop down list in Excel using Apache POI?

Question

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.

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.

Thank you !!!

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

    }
}

}

What's the limit in Excel? And what version of Apache POI are you trying this with? — Gagravarr
I use Apache POI 3.9. Like I said, when the dropdown list contains more than 100 items, excel said: excel found unreadable content. And there is no drop down list in excel. I use office 2010. — Bryan
Why are you using an old release like 3.9? What happens if you upgrade to the latest one, 3.11? — Gagravarr
I just tested 3.11 and 3.11-beta3. Excel throws the same error. — Bryan
Can you get it so that adding x items works, and x+1 fails? Ideally we want 3 files, x works, x+1 fails, x loaded into excel + 1 more added by Excel — Gagravarr

Bryan Bryan · Accepted Answer · 2014-12-24T16:17:55

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:

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."

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

    }

}

Is there a max number items while generating drop down list in Excel using Apache POI?

1 Answers