| Summary: | Datavalidation across sheets (list mode) | ||
|---|---|---|---|
| Product: | POI | Reporter: | Graham <graham> |
| Component: | HSSF | Assignee: | POI Developers List <dev> |
| Status: | RESOLVED FIXED | ||
| Severity: | normal | CC: | scrollpane |
| Priority: | P2 | ||
| Version: | unspecified | ||
| Target Milestone: | --- | ||
| Hardware: | PC | ||
| OS: | Windows Vista | ||
| Attachments: | validation fault across sheets | ||
It is a limitation in Excel (I have version 2002). I have validated this bug. The other comment is incorrect. It is supported in Excel. Any ideas about how this can be fixed? Fixed in svn r682225/r682227. This turned out to be way more work than expected. There were several bugs/deficiencies found in the original HSSFDataValidation API: - RVA settings for list formulas were NQR (probably the cause of this bug) - Lists with explicit values did not work - Formulas and simple values were not distinguished (Note that the Excel UI handles expressions starting with '=' different to those without the equals prefix). There were also problems with the java object model in that too many things were crammed into HSSFDataValidation. Extensive changes were made to resolve these issues. A new class DVConstraint was created to hold just the validation type, operator and value expressions. CellRangeAddressList was renamed and raised in the API. HSSFDataValidation was moved to org.apache.poi.hssf.usermodel (where classes like HSSFWorkbook, HSSFConditionalFormatting, etc are found). The attached test code (id=21934) is just an extract from the original TestDataValidation. TestDataValidation has been updated to cover the 'cross sheet validation' case, as well as the fixed functionality mentioned above. You can tweak the 'isSame' boolean variable to output the new version of the test spreadsheet. |
Created attachment 21934 [details] validation fault across sheets Data validation list using another sheet fails setting the data validation formula to: String strFormula = "sheet1!$B$6:$B$9" The program runs fine but on opening the xls file Excel says File error: data may have been lost If I change the formula to use defined range the the library throws an exception Validation of data list on the same sheet work attached java test source formula used on line 118 Note: source write file to root of drive c on windows poi library 3.1 - beta 1