How to create a dependent drop down list using [Apache POI]

Here I am creating one excel template using [Apache POI] in that user can fill data. In that two drop down list are there. Want to create a drop down list that depends on previous cell's drop down. For example: If I select veg category in cell 3, and "rice, curd, milk" items will appear in the dependent drop down list in cell 4. Codes are below.

Here collecting data

List<InternetItemResponse> internetItems = internetItemService.getAllByHotelId(hotelId);
if (CollectionUtils.isNotEmpty(internetItems)) {
String[] itemsName = new String[internetItems.size()];
String[] itemsCategory = new String[internetItems.size()];
String itemName;
String itemCategory;
Map<String, Set<String>> categoryVsItemName = new HashMap<>();
Set<String> itemList;
for (int i = 0; i < internetItems.size(); i++) {
    InternetItemResponse itemResponse = internetItems.get(i);
    if (itemResponse != null) {
        itemCategory = itemResponse.getCategory();
        if (!StringUtils.isEmpty(itemCategory)) {
            itemsCategory[i] = itemCategory;
            itemName = itemResponse.getTitle();
            itemsName[i] = itemName;
            if (CollectionUtils.isEmpty(categoryVsItemName.get(itemCategory))) {
                itemList = new HashSet<>();
                itemList.add(itemName);
                categoryVsItemName.put(itemCategory, itemList);
            } else {
                categoryVsItemName.get(itemCategory).add(itemName);
            }
        }
    }
}

}

Assigning the data in drop down

// Setting drop down values
for (int i = 0; i < headerColumns.length; i++) {
    if (i == 3) {
        XSSFDataValidationHelper mealdvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
        XSSFDataValidationConstraint mealdvConstraint = (XSSFDataValidationConstraint) mealdvHelper
                .createExplicitListConstraint(itemsCategory);
        // CellRangeAddressList(int firstRow, int lastRow, int firstCol, int lastCol)
        CellRangeAddressList addressListmeal = new CellRangeAddressList(1, 99, i, i);
        XSSFDataValidation categoryDataValidation = (XSSFDataValidation) mealdvHelper
                .createValidation(mealdvConstraint, addressListmeal);
        categoryDataValidation.setShowErrorBox(true);
        categoryDataValidation.setSuppressDropDownArrow(true);
        categoryDataValidation.setShowPromptBox(true);
        sheet.addValidationData(categoryDataValidation);
    } else if (i == 4) {
        XSSFDataValidationHelper rmCategorydvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
        XSSFDataValidationConstraint rmCategorydvConstraint = (XSSFDataValidationConstraint) rmCategorydvHelper
                .createExplicitListConstraint(itemsName);
        CellRangeAddressList addressListrmCategory = new CellRangeAddressList(1, 99, i, i);
        XSSFDataValidation itemNameValidation = (XSSFDataValidation) rmCategorydvHelper
                .createValidation(rmCategorydvConstraint, addressListrmCategory);
        itemNameValidation.setShowErrorBox(true);
        itemNameValidation.setSuppressDropDownArrow(true);
        itemNameValidation.setShowPromptBox(true);
        sheet.addValidationData(itemNameValidation);
    }
}
View Answers









Related Tutorials/Questions & Answers:
Advertisements