Limitation while generating excel drop down list with Apache POI

Question

I'm trying to generate an excel file with some validations, I've read the poi dev guides for implementing it. During implementation, I got an exception (String literals in formulas can't be bigger than 255 characters ASCII). POI concatenates all drop down options into '0' deliminated string and checking its length and giving me exception. :(
I'm using latest version of POI 3.8 beta 5.
And my code is:

try {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("new sheet");
    HSSFRow row = sheet.createRow((short) 0);
    //CellRangeAddressList from org.apache.poi.ss.util package
    CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
    DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(getCountries());
    DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
    dataValidation.setSuppressDropDownArrow(false);
    sheet.addValidationData(dataValidation);
    FileOutputStream fileOut = new FileOutputStream("c:\\test.xls");
    wb.write(fileOut);
    fileOut.close();
    } catch (IOException e) {
       e.printStackTrace();
  }

After that I have tried with XSSFWorkBook with this code:

XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("new sheet");
DataValidationHelper validationHelper = new XSSFDataValidationHelper(sheet);
DataValidationConstraint constraint = validationHelper.createExplicitListConstraint(getCountries());
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
DataValidation dataValidation = validationHelper.createValidation(constraint, addressList);
dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
dataValidation.setSuppressDropDownArrow(true);
sheet.addValidationData(dataValidation);
FileOutputStream fileOut = new FileOutputStream("c:\\test.xlsx");

Unfortunately, no success with such result which is comma delimenated long string in one cell:

But manually in excel, I can create dropdown cells with this long country list.
Is there any way generate dropdown with long strings, or API does not support?

Solution 1

I understood it, Excel itself does not allow entering validation range string more than 255 characters, this was not POI limitation. And now I'm using Named Ranges and Named Cells and it's working properly for me. So I had to put my validation range tokens in another sheet(made hidden) and I referenced desired cell ranges from my real sheet. Here is my working code:

HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet realSheet = workbook.createSheet("Sheet xls");
HSSFSheet hidden = workbook.createSheet("hidden");
for (int i = 0, length= countryName.length; i < length; i++) {
   String name = countryName[i];
   HSSFRow row = hidden.createRow(i);
   HSSFCell cell = row.createCell(0);
   cell.setCellValue(name);
 }
 Name namedCell = workbook.createName();
 namedCell.setNameName("hidden");
 namedCell.setRefersToFormula("hidden!$A$1:$A$" + countryName.length);
 DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden");
 CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
 HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
 workbook.setSheetHidden(1, true);
 realSheet.addValidationData(validation);
 FileOutputStream stream = new FileOutputStream("c:\\range.xls");
 workbook.write(stream);
 stream.close();

Solution 2

The formidable solution provided by Õzbek needs only slight modification to work flawlessly with NPOI (using C# on .NET).

Here's my code, provided as convenience for C# coders. It takes a sheet and positional elements as input and can also handle multiple dropdown, when they are arranged in columns.

public static void CreateDropDownListForExcel(this ISheet sheet, IList<string> dropDownValues, int startRow, int lastRow, int column) {
    if (sheet == null) {
        return;
    }

    //Create a hidden sheet on the workbook (using the column as an id) with the dropdown values
    IWorkbook workbook = sheet.Workbook;
    string dropDownName = sheet.SheetName + "DropDownValuesForColumn" + column;
    ISheet hiddenSheet = workbook.CreateSheet(dropDownName);
    for (int i = 0, length = dropDownValues.Count; i < length; i++) {
        string name = dropDownValues[i];
        IRow row = hiddenSheet.CreateRow(i);
        ICell cell = row.CreateCell(0);
        cell.SetCellValue(name);
    }

    //Create the dropdown using the fields of the hidden sheet
    IName namedCell = workbook.CreateName();
    namedCell.NameName = dropDownName;
    namedCell.RefersToFormula = (dropDownName + "!$A$1:$A$" + dropDownValues.Count);
    DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(dropDownName);
    CellRangeAddressList addressList = new CellRangeAddressList(startRow, lastRow, column, column);
    HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
    int hiddenSheetIndex = workbook.GetSheetIndex(hiddenSheet);
    workbook.SetSheetHidden(hiddenSheetIndex, SheetState.HIDDEN);

    //Add the Dropdown to the presenting sheet.
    sheet.AddValidationData(validation);
}

Solution 3

Above code works fine. But if I use XSSF classes instead of HSSF, it fails saying Exception in thread

"main" java.lang.NoSuchMethodError: org.apache.poi.ss.formula.FormulaParser.parse(Ljava/lang/String;Lorg/apache/poi/‌​ss/formula/FormulaParsingWorkbook;II)[Lorg/apache/poi/ss/formula/ptg/Ptg; at org.apache.poi.xssf.usermodel.XSSFName.setRefersToFormula(XSSFName.java:195) at XLDropdown.main(XLDropdown.java:35)

To create dropdown list using XSSF interfaces I got the solution at

create dropdown list using apache poi XSSF interfaces

Comments

[centic]

sounds like a bug/limitation in the POI implementation, maybe you should rather report this as bug at poi.apache.org

[Russ Hayward]

Great answer. I had to use "hidden!$A$1:$A$" + countryName.length to get the constraints to work.

[Herin]

Hello, I am also facing the same problem. Can you please provide me the output excel file using this code ? so that I can be better understand how it will work and execute the same. Thanks.

[Jama A.]

Hi Herin, unfortunately I worked on that project long time ago and i don't have sample output now... But here, what i'm doing is: creating an excel file with 2 sheets: 1st is actual sheet which user will see, the other is hidden used for validating the actual sheet. And then, creating a list of cells in the loop for hidden sheet, which is used for validation in actual list. So, if you put a 'countryName' String array at the beginning of code, it should produce an excel file which has a dropdown list with the strings from 'countryName' String array.

[Rodrigo Prieto]

Thanks, save my day. One thing, the: string dropDownName = sheet.SheetName + "DropDownValuesForColumn" + column; doesn't work if the SheetName is big, better change the "DropDownValuesForColumn" for a smaller string.

[Deva44]

For newer POI versions, refer to stackoverflow.com/a/27639609. For re-use of drop-down values, we can use a drop-down id as the hidden sheet name.