How to create a dependent drop down list using Apache POI

0 votes

Here, I'm using [Apache POI] to create an Excel template that a user may fill up with data. There are two drop-down lists in that one. Want to make a drop-down list that depends on the drop-down menu in the previous cell? As an illustration, if I choose the veg category in cell 3, the items "rice, curd, and milk" will show up in the dependent drop-down list in cell 4. Here are the codes: 

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 to 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);
        }
    }
Oct 24 in Others by Kithuzzz
• 20,660 points
153 views

1 answer to this question.

0 votes

There is nothing apache poi cannot do, even create dependent dropdown lists. Excel files may be produced with the apache poi library. Dropdown list dependencies must be maintained in Excel's graphical user interface (GUI), where the created file is being used. Only the Excel file may be created by Apache Poi, making this possible.

Via named ranges for the data validation lists, whose names are subsequently obtained using INDIRECT is one method. As a result, the named ranges of the dependent lists are named in the main list. And in order to obtain the dependent list whose name has been chosen from the main list, the dependent list utilizes =INDIRECT([cell of the main list]).

Example of how this can be created using apache poi:

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.apache.poi.ss.util.*;

import java.util.Map;
import java.util.HashMap;

class CreateExcelDependentDataValidationListsUsingNamedRanges {

 public static void main(String[] args) throws Exception {

   //some data
   Map<String, String[]> categoryItems = new HashMap<String, String[]>();
   categoryItems.put("Countries", new String[]{"France", "Germany", "Italy"});
   categoryItems.put("Capitals", new String[]{"Paris", "Berlin", "Rome"});
   categoryItems.put("Fruits", new String[]{"Apple", "Peach", "Banana", "Orange"});

   Workbook workbook = new XSSFWorkbook();

   //hidden sheet for list values
   Sheet sheet = workbook.createSheet("ListSheet");

   Row row;  
   Name namedRange;
   String colLetter;
   String reference;

   int c = 0;
   //put the data in
   for (String key : categoryItems.keySet()) {
    int r = 0;
    row = sheet.getRow(r); if (row == null) row = sheet.createRow(r); r++;
    row.createCell(c).setCellValue(key);
    String[] items = categoryItems.get(key);
    for (String item : items) {
     row = sheet.getRow(r); if (row == null) row = sheet.createRow(r); r++;
     row.createCell(c).setCellValue(item);
    }
    //create names for the item list constraints, each named from the current key
    colLetter = CellReference.convertNumToColString(c);
    namedRange = workbook.createName();
    namedRange.setNameName(key);
    reference = "ListSheet!$" + colLetter + "$2:$" + colLetter + "$" + r;
    namedRange.setRefersToFormula(reference);
    c++;
   }

   //create name for Categories list constraint
   colLetter = CellReference.convertNumToColString((c-1));
   namedRange = workbook.createName();
   namedRange.setNameName("Categories");
   reference = "ListSheet!$A$1:$" + colLetter + "$1";
   namedRange.setRefersToFormula(reference);

   //unselect that sheet because we will hide it later
   sheet.setSelected(false);


   //visible data sheet
   sheet = workbook.createSheet("Sheet1");

   sheet.createRow(0).createCell(0).setCellValue("Select Category");
   sheet.getRow(0).createCell(1).setCellValue("Select item from that category");

   sheet.setActiveCell(new CellAddress("A2"));

   sheet.autoSizeColumn(0);
   sheet.autoSizeColumn(1);

   //data validations
   DataValidationHelper dvHelper = sheet.getDataValidationHelper();
   //data validation for categories in A2:
   DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint("Categories");
   CellRangeAddressList addressList = new CellRangeAddressList(1, 1, 0, 0);            
   DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
   sheet.addValidationData(validation);

   //data validation for items of the selected category in B2:
   dvConstraint = dvHelper.createFormulaListConstraint("INDIRECT($A$2)");
   addressList = new CellRangeAddressList(1, 1, 1, 1);            
   validation = dvHelper.createValidation(dvConstraint, addressList);
   sheet.addValidationData(validation);

   //hide the ListSheet
   workbook.setSheetHidden(0, true);
   //set Sheet1 active
   workbook.setActiveSheet(1);

   FileOutputStream out = new FileOutputStream("CreateExcelDependentDataValidationListsUsingNamedRanges.xlsx");
   workbook.write(out);
   workbook.close();
   out.close();

 }
}
answered Oct 24 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

Excel Drop down list using Apache POI

To set a default value, just setCellValue("first_item_value"); she ...READ MORE

answered Nov 11 in Others by narikkadan
• 37,660 points
29 views
0 votes
1 answer

How to create subnet inside a VPC in AWS using Terraform?

Hi@akhtar, You need to use aws_subnet resource inside your ...READ MORE

answered Jul 21, 2020 in Others by MD
• 95,380 points
211 views
0 votes
1 answer

How to create a App using Material Design?

Hi@akhtar, You need to use the MaterialApp method ...READ MORE

answered Jul 23, 2020 in Others by MD
• 95,380 points
206 views
0 votes
1 answer
0 votes
1 answer

Can anyone show how to modify an existing excel sheet using Apache POI?

Hello Prakash, to modify an existing excel ...READ MORE

answered Jul 17, 2019 in Selenium by Anvi
• 14,170 points
2,761 views
0 votes
1 answer

How can I read numeric data from an Excel sheet using Selenium Webdriver?

Hey Jignesh, for reading numeric data from ...READ MORE

answered Jul 17, 2019 in Selenium by Abha
• 28,120 points
6,541 views
0 votes
1 answer

How to create a drop-down list in Excel?

Making a list of the items you ...READ MORE

answered Oct 9 in Others by narikkadan
• 37,660 points
52 views
0 votes
1 answer

How to create a drop-down in excel with custom values

You can accomplish that using code rather ...READ MORE

answered Sep 25 in Others by narikkadan
• 37,660 points
56 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP