java - Read drop down list content from Excel using apache poi

Question

I need to create a drop down list (Data Validation) on a particular cell in an Excel sheet and read them back.

With the help of tutorials provided by Apache POI, I am able to create a Drop-down list in an Excel sheet, but I also need to read the drop-down list content when reading that again, so that I can render a similar drop-down list on a UI.

Any suggestions?

Answer 1

I can't seem to find any mechanism in HSSF to retrieve the DataValidations from the HSSFSheet. So if you have a .xls file, you're out of luck.

However, if you have a .xlsx file, then XSSFSheet supplies a method to retrieve a list of all XSSFDataValidations on the sheet: getDataValidations, which returns a List<XSSFDataValidation>.

You'll need to loop over them all, calling regions() to examine the CellRangeAddressList to see if it applies to your cell. If it does, then call getValidationConstraint to access the DataValidationConstraint object, on which you can call getExplicitListValues to get the array of strings back.