| Summary: | HSSF - Unsupported Names in LIST data validation formulas | ||
|---|---|---|---|
| Product: | POI | Reporter: | Konrad Mrożek <konrad.mrozek> |
| Component: | HSSF | Assignee: | POI Developers List <dev> |
| Status: | RESOLVED FIXED | ||
| Severity: | normal | ||
| Priority: | P2 | ||
| Version: | 3.0-dev | ||
| Target Milestone: | --- | ||
| Hardware: | PC | ||
| OS: | Windows XP | ||
| Attachments: | Patch | ||
Fixed with bug 44953. The junit (TestDataValidation) was augmented to include some tests for named ranges. Your patch works for this particular case (a formula with a single named range), however updating RVA (operand class) settings is rather complex (rules are applied via the tree structure, not to isolated tokens). What was required was to set the operand type of the tree root node. There are different rules for determining the RVA values of nodes elsewhere in the tree. Note - the API was changed a little bit . The following code should now do what you described: HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Sheet1"); HSSFName namedRange = wb.createName(); namedRange.setNameName("NAMEDAREA"); namedRange.setReference("Sheet1!$A$2:$A$7"); // add list data to cells A1:A9 for (int i = 0; i < 10; i++) { sheet.createRow(i + 0).createCell((short) 0).setCellValue(new HSSFRichTextString("Data " + i)); } // Add data validation to cell C1 DVConstraint dc = DVConstraint.createFormulaListConstraint("NAMEDAREA"); CellRangeAddressList cral = new CellRangeAddressList(0, 0, 2, 2); // cell "C1" HSSFDataValidation dv = new HSSFDataValidation(cral, dc); dv.setEmptyCellAllowed(false); dv.setErrorStyle(HSSFDataValidation.ErrorStyle.STOP); dv.setShowErrorBox(true); dv.createErrorBox("Error", "invalid entry"); dv.setSuppressDropDownArrow(false); sheet.addValidationData(dv); try { OutputStream os = new FileOutputStream("c:/temp/testDV-namedRange.xls"); wb.write(os); os.close(); } catch (IOException e) { throw new RuntimeException(e); } |
Created attachment 22330 [details] Patch You cannot set source of dropdown in List Data Validation type. To reproduce it: 1. create excel with named area NAMEDAREA. 2. load it using POI. 3. create HSSFDataValidation object. 4. set validation type to DATA_TYPE_LIST. 5. set first formula to "NAMEDAREA". 6. save workbook to file. 7. open excel. 8. try to type anything to cell. (result: excel crashes). 9. try to expand list for cell. (it cannot be expaned). The reason for this bug is wrong formula class of NAME token. It should be reference, not value. I attached patch, which solves the problem.