Bug 56599

Summary: getDataValidations does not return dataValidations referring to different sheets.
Product: POI Reporter: dirk.niebuhr
Component: XSSFAssignee: POI Developers List <dev>
Status: NEW ---    
Severity: major CC: chintan9033, clement.garin, dirk.niebuhr
Priority: P2    
Version: 4.0.x-dev   
Target Milestone: ---   
Hardware: PC   
OS: All   
Attachments: Sample sheet and code to reproduce the bug.
Example sheet.xml extracted from an .xslx file, shows two storage locations for data validations

Description dirk.niebuhr 2014-06-06 12:07:37 UTC
Created attachment 31692 [details]
Sample sheet and code to reproduce the bug.

When an excel-file contains a dataValidation referring to another sheet, this validation is not returned by the getDataValidations-method.

I have attached a spreadsheet containing a different dataValidation in each of the four cells A1:A4. As you can see when executing the sample code, the dataValidation in cell A3 is not returned by getDataValidations(), as it refers to values in sheet 2.
Comment 1 Phili0815 2018-01-25 14:43:59 UTC
This problem still exists. I looked inside the .xlsx file internals and noticed that data validations refering to other sheets are stored at a different place.

If you call XSSFSheet.getDataValidations(), the data validations will be extracted from CTWorksheet.getDataValidations(). But the data validations refering to other sheets are stored in CTWorksheet.getExtList(), which cannot be accessed by POI users.

In general it would be great if there would be a possibility to support these type of data validations.

I also think that at the moment such data validations are stored at the wrong place if you create it directly with POI.
Comment 2 Phili0815 2018-01-25 14:45:54 UTC
Created attachment 35697 [details]
Example sheet.xml extracted from an .xslx file, shows two storage locations for data validations
Comment 3 Greg Woolsey 2018-03-29 06:54:56 UTC
I'll take a look, as this will likely affect me too.  Extension elements are typically for features added to Excel after the initial spec.  POI generally doesn't do much if anything directly with them, so handling these may involve new API methods to avoid changing behavior for current ones.  I have at least one project which expects the method you point out to only return validations defined for the current sheet, we try to avoid API changes that break the contract of previous versions, when possible.