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, 2022 in Others by Kithuzzz
• 38,010 points
4,617 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, 2022 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

Create Drop-Down List Using a Formula (Without Data Validation)

As far as I am aware, it ...READ MORE

answered Nov 8, 2022 in Others by narikkadan
• 63,420 points
2,236 views
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, 2022 in Others by narikkadan
• 63,420 points
532 views
0 votes
1 answer

How to create page borders using Apache POI in excel files with Java?

Microsoft Excel cannot do this. Libreoffice Calc ...READ MORE

answered Dec 13, 2022 in Others by narikkadan
• 63,420 points
680 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,440 points
431 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,150 points
3,436 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,140 points
7,686 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, 2022 in Others by narikkadan
• 63,420 points
422 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, 2022 in Others by narikkadan
• 63,420 points
712 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