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