Geek Answers Handbook

Reading dropdown list of content from Excel using apache poi

I need to create a drop-down list (Data Validation) in a specific cell in an Excel worksheet and read them back.

Using the tutorials provided by Apache POI , I can create a drop-down list on an Excel worksheet, but I also need to read the contents of the drop-down list when reading this again, so that I can display a similar drop-down list in the user interface.

Any suggestions?

+6
source share
2 answers

DataValidation is stored even in the HSSF workbook, but is located in the Internal Sheet library, and since it is private , therefore access to it is not provided to the application programmer. I used the Java Reflection API to access the inner sheet. This code works great 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; }
+6
source

I cannot find any mechanism in HSSF for retrieving DataValidation from HSSFSheet . Therefore, if you have a .xls file, you're out of luck.

However, if you have a .xlsx file, then the XSSFSheet provides a method for retrieving a list of all XSSFDataValidation in a worksheet: getDataValidations , which returns a List<XSSFDataValidation> .

You will need to CellRangeAddressList over them all by calling regions() to examine the CellRangeAddressList to find out if this applies to your cell. If so, call getValidationConstraint to access the DataValidationConstraint object, which you can call getExplicitListValues to return an array of strings .

+6
source

More articles:


All Articles