How can we create a dependent drop down list in excel using apache poi

Viewed 29

I wanted to create dependent drop down list in one sheet and display the options in another sheet. I have implemented this but the drop down doesn't show any options

`XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("dataSheet");

            Row headerRow = sheet.createRow(10);

            Cell headerCell = headerRow.createCell(0);
            headerCell.setCellValue("Animal");

            headerCell = headerRow.createCell(1);
            headerCell.setCellValue("Vegetable");

        
           

           //names for the list constraints
           Row row = sheet.createRow(11);
           Cell cell = row.createCell(0);
           cell.setCellValue("Lion");
           
           cell = row.createCell(1);
           cell.setCellValue("Tiger");
           
           
           row = sheet.createRow(12);
           cell = row.createCell(0);
           cell.setCellValue("Cabbage");
           
           cell = row.createCell(1);
           cell.setCellValue("Spinach");
          

           sheet.setSelected(false);
           sheet = workbook.createSheet("Sheet1");
           
           Name namedCell = workbook.createName();
           namedCell.setNameName("CHOICES");
           String reference = "dataSheet!$A$11:$B$11";
           namedCell.setRefersToFormula(reference);
           
           namedCell.setNameName("ANIMAL");
           reference = "dataSheet!$A$12:$B$12"; //List 1 to 4
           namedCell.setRefersToFormula(reference);
           
           namedCell.setNameName("VEGETABLE");
           reference = "dataSheet!$A$13:$B$13"; //List 1 to 4
           namedCell.setRefersToFormula(reference);
           
           //data validations
           DataValidationHelper dvHelper = sheet.getDataValidationHelper();
           DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint("CHOICES");
           CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);            
           DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
           validation.setSuppressDropDownArrow(true);
           sheet.addValidationData(validation);

           dvConstraint = dvHelper.createFormulaListConstraint("INDIRECT(UPPER($A$1))");
           addressList = new CellRangeAddressList(0, 0, 1, 1);            
           validation = dvHelper.createValidation(dvConstraint, addressList);
           validation.setSuppressDropDownArrow(true);
           sheet.addValidationData(validation);`
0 Answers
Related