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?

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

Answer 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);
}

Answer 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

Answer 4

This is how I used the FormulaListConstraint using the XSSF model

byte[] addConstraint() throws IOException {
    ByteArrayOutputStream byteArrayOutputStream = null;
    byte[] output = null;
    XSSFWorkbook workbook = null;

    try {
        Resource resource = new ClassPathResource("Test.xlsx");
        InputStream input = resource.getInputStream();
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        org.apache.commons.io.IOUtils.copy(input, baos);
        byte[] uploadedFile = baos.toByteArray();
        ByteArrayInputStream bais = new ByteArrayInputStream(uploadedFile);
        workbook = new XSSFWorkbook(bais);
        XSSFSheet sheet = workbook.getSheetAt(0);
        String originalSheet = "myhiddenSheet";
        String[] roles = new String[] { "val1", "val2"/* ...... */, "valn" };
        addHiddenSheet(workbook, roles, originalSheet);
        String formulae = originalSheet.concat("!$A$1:$A$");
        Name namedCell = workbook.createName();
        namedCell.setNameName(originalSheet);
        namedCell.setRefersToFormula(formulae + roles.length);
        XSSFDataValidationHelper userRoleDataValidationHelper = new XSSFDataValidationHelper(sheet);
        XSSFDataValidationConstraint userRoleConstraint = (XSSFDataValidationConstraint) userRoleDataValidationHelper
                .createFormulaListConstraint(originalSheet);
        CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
        XSSFDataValidation userStatusValidation = (XSSFDataValidation) userRoleDataValidationHelper
                .createValidation(userRoleConstraint, addressList);
        sheet.addValidationData(userStatusValidation);

        byteArrayOutputStream = new ByteArrayOutputStream();
        workbook.write(byteArrayOutputStream);
        output = byteArrayOutputStream.toByteArray();
    } finally {
        workbook.close();
        byteArrayOutputStream.close();
    }
    return output;
}
private void addHiddenSheet(Workbook workbook, String[] values, String hiddenSheetName) {
    Sheet hiddenSheet = workbook.createSheet(hiddenSheetName);
    for (int i = 0, length = values.length; i < length; i++) {
        String name = values[i];
        Row row = hiddenSheet.createRow(i);
        Cell cell = row.createCell(0);
        cell.setCellValue(name);
    }
    workbook.setSheetHidden(1, true);

}