Limitation while generating excel drop down list with Apache POI

by

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

More Related Contents:

  1. How to load a large xlsx file with Apache POI?
  2. Processing large xlsx file
  3. Apache POI error loading XSSFWorkbook class
  4. Excel Cell Style issue
  5. Java POI the supplied data appears to be in the Office 2007+ XML
  6. What does the statement Object[][] data = new Object [][] mean?
  7. Get Cell Value from Excel Sheet with Apache Poi
  8. Writing a large resultset to an Excel file using POI
  9. Best language to parse extremely large Excel 2007 files [closed]
  10. How to read Excel cell having Date with Apache POI?
  11. Apache POI add a Series name into LineChart
  12. Replacing a text in Apache POI XWPF
  13. What is the better API to Reading Excel sheets in java – JXL or Apache POI [closed]
  14. How to get an Excel Blank Cell Value in Apache POI?
  15. Getting Exception(org.apache.poi.openxml4j.exception – no content type [M1.13]) when reading xlsx file using Apache POI?
  16. Password Protected Excel File
  17. Get the cell value as how it was presented in excel
  18. Trying to make simple PDF document with Apache poi
  19. Error While Reading Large Excel Files (xlsx) Via Apache POI
  20. Apache-POI sorting rows in excel
  21. how to add a picture to a .docx document with Apache POI XWPF in java
  22. Creating a Table of Contents for a XWPFDocument with page numbers’ indication
  23. Java POI : How to read Excel cell value and not the formula computing it?
  24. How to convert HSSFWorkbook to XSSFWorkbook using Apache POI?
  25. Apache POI evaluate formula
  26. Convert Word to HTML with Apache POI
  27. Apache POI Line Chart colors
  28. Determine MS Excel file type with Apache POI
  29. Reading data from xlsx with Apache POI’s SXSSFSheet
  30. POI DataFormatter returns 2 digits year instead of 4 digits year for date cells [duplicate]

Leave a Comment