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.

Answer 2

DataValidation is stored even in HSSF workbook, but it is in Internal Sheet of the library and since it is private so access to it is not given to application programmer. I have used Java Reflection API to access internal sheet. This code is working fine for me.

private ArrayList<DVRecord> init(FileInputStream fis) throws InvalidFormatException, IOException, NoSuchFieldException, IllegalArgumentException, IllegalAccessException {
    HSSFWorkbook hWorkbook = (HSSFWorkbook) WorkbookFactory.create(fis);
    HSSFSheet hSheet = hWorkbook.getSheetAt(1); // sheet on which you want to read data validation
    Class c = org.apache.poi.hssf.usermodel.HSSFSheet.class;
    Field field = c.getDeclaredField("_sheet");
    field.setAccessible(true);
    Object internalSheet = field.get(hSheet);
    InternalSheet is = (InternalSheet) internalSheet;
    DataValidityTable dvTable = is.getOrCreateDataValidityTable();
    Class c2 = org.apache.poi.hssf.record.aggregates.DataValidityTable.class;
    Field field2 = c2.getDeclaredField("_validationList");
    field2.setAccessible(true);
    Object records = field2.get(dvTable);
    ArrayList<DVRecord> dvRecords = (ArrayList<DVRecord>) records;
    return dvRecords;
}