String literals in formulas can't be bigger than 255 characters ASCII

http://stackoverflow.com/questions/8655185/limitation-while-generating-excel-drop-down-list-with-apache-poi

0down vote favorite

share [g+]share [fb] share [tw]

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 long this country list.
Is there any way generate dropdown with long strings, or API does not support?

javaapache-poi xssf

link|improve this question

edited Jan 17 at 22:45

asked Dec 28 '11 at 11:14

Jamshid Asatillayev
2,5961426

96% accept rate

 

sounds like a bug/limitation in the POI implementation, maybe you should rather report this as bug atpoi.apache.org – centicDec 28 '11 at 13:26

feedback

1 Answer

activeoldest votes

up vote 2 down vote accepted

I understood it, Excel itself does not allow entering validation range string more than 255 characters, this was not POI limitation. And now with usingNamed Ranges and Named Cells is working properly for me. So I 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!A1: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(); 

link|improve this answer