使用 apache poi 从 Excel 中读取下拉列表内容 [英] Read drop down list content from Excel using apache poi

问题描述

我需要在 Excel 工作表中的特定单元格上创建一个下拉列表(数据验证)并读取它们.

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

借助Apache POI 提供的教程，我可以在 Excel 工作表中创建下拉列表，但我在阅读时还需要阅读下拉列表的内容再次，以便我可以在 UI 上呈现类似的下拉列表.

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.

有什么建议吗?

推荐答案

DataValidation 甚至存储在 HSSF 工作簿中，但它位于库的 Internal Sheet 中，因为它是 private 所以应用程序程序员不能访问它.我使用 Java Reflection API 来访问内部工作表.这段代码对我来说很好用.

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;
}

这篇关于使用 apache poi 从 Excel 中读取下拉列表内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！