Bug 56599 - getDataValidations does not return dataValidations referring to different sheets.
Summary: getDataValidations does not return dataValidations referring to different she...
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 4.0.x-dev
Hardware: PC All
: P2 major with 4 votes (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2014-06-06 12:07 UTC by dirk.niebuhr
Modified: 2018-03-29 06:54 UTC (History)
2 users (show)



Attachments
Sample sheet and code to reproduce the bug. (9.33 KB, application/zip)
2014-06-06 12:07 UTC, dirk.niebuhr
Details
Example sheet.xml extracted from an .xslx file, shows two storage locations for data validations (2.01 KB, text/xml)
2018-01-25 14:45 UTC, Phili0815
Details

Note You need to log in before you can comment on or make changes to this bug.
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.