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