String literals in formulas can't be bigger than 255 charact

最新推荐文章于 2022-10-09 10:02:55 发布

我喜欢杀生丸

最新推荐文章于 2022-10-09 10:02:55 发布

阅读量605

点赞数

本文链接：https://blog.csdn.net/qq_43685474/article/details/87706920

版权

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

0 down vote favorite

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:

enter image description here

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?

edited Jan 17 at 22:45

asked Dec 28 '11 at 11:14

Jamshid Asatillayev
2,596 1 4 26

96% accept rate

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

feedback

1 Answer

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