Bug 44953 - Datavalidation across sheets (list mode)
Summary: Datavalidation across sheets (list mode)
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: unspecified
Hardware: PC Windows Vista
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2008-05-08 11:27 UTC by Graham
Modified: 2008-08-03 15:48 UTC (History)
1 user (show)

validation fault across sheets (11.23 KB, text/java)
2008-05-08 11:27 UTC, Graham

Note You need to log in before you can comment on or make changes to this bug.
Description Graham 2008-05-08 11:27:15 UTC
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
Comment 1 Bernhard 2008-06-19 01:14:49 UTC
It is a limitation in Excel (I have version 2002).
Comment 2 E Simone 2008-07-21 16:06:52 UTC
I have validated this bug.  The other comment is incorrect.  It is supported in Excel.  Any ideas about how this can be fixed?
Comment 3 Josh Micich 2008-08-03 15:48:27 UTC
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.