Create a Drop-Down List in Excel in Java

Excel drop-down list restricts the user to selecting a value from a pre-given list of values in a cell. It is a very helpful feature that facilitates error-free data entry. This article will share two examples of creating a drop-down list programmatically using a free Java API.

Install the Free API (2 Methods)

1# Download (Free Spire.XLS for Java) and unzip it, then add the Spire.Xls.jar file to your project as dependency.

2# Directly add the jar dependency to maven project by adding the following configurations to the pom.xml.

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls.free</artifactId>
        <version>5.1.0</version>
    </dependency>
</dependencies>

Example 1 ▶ Create a Drop-Down List Based on Values in a Cell Range

Free Spire.XLS for Java allows you to create a drop-down list by refering to values in a cell range as the data validation source. A simple sample code is shown below.

import com.spire.xls.*;

import java.awt.*;

public class DropdownList {
    public static void main(String[] args) {

        //Create a Workbook object
        Workbook workbook = new Workbook();

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Add text to specified cells
        sheet.getCellRange("B2").setValue("Student");
        sheet.getCellRange("B3").setValue("Kelvin");
        sheet.getCellRange("B4").setValue("Frankie");
        sheet.getCellRange("C2").setValue("Subject");

        sheet.getCellRange("A10").setValue("MATH");
        sheet.getCellRange("A11").setValue("HISTORY");
        sheet.getCellRange("A12").setValue("SOCIAL");
        sheet.getCellRange("A13").setValue("PHYSICS");

        //Set font and cell styles
        sheet.getCellRange("B2:C2").getStyle().getFont().isBold(true);
        sheet.getCellRange("B2:C2").getStyle().getFont().setColor(Color.BLUE);
        sheet.getCellRange("B2:C4").getStyle().getFont().setSize(11);
        sheet.getCellRange("B2:C4").setRowHeight(18);
        sheet.getCellRange("B2:C4").setColumnWidth(12);

        //Create a drop-down list by referring to a specified data range as the data validation source
        sheet.getCellRange("C3:C4").getDataValidation().setDataRange(sheet.getCellRange("A10:A13"));

        //Save the result document
        workbook.saveToFile("ExcelDropdownList.xlsx", ExcelVersion.Version2013);
    }
}

Example 2 ▶ Create a Drop-Down List Based on Values in a String Array

With Free Spire.XLS for Java, you can also create a drop-down list by refering to values in a string array. A simple sample code is shown below.

import com.spire.xls.*;

import java.awt.*;

public class ExcelDropdownList {
    public static void main(String[] args) {

        //Create a Workbook object
        Workbook workbook = new Workbook();

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Add text to specified cells
        sheet.getCellRange("B2").setValue("Student");
        sheet.getCellRange("B3").setValue("Kelvin");
        sheet.getCellRange("B4").setValue("Frankie");
        sheet.getCellRange("C2").setValue("Subject");

        //Set font and cell styles
        sheet.getCellRange("B2:C2").getStyle().getFont().isBold(true);
        sheet.getCellRange("B2:C2").getStyle().getFont().setColor(Color.BLUE);
        sheet.getCellRange("B2:C4").getStyle().getFont().setSize(11);
        sheet.getCellRange("B2:C4").setRowHeight(18);
        sheet.getCellRange("B2:C4").setColumnWidth(12);

        //Set the values of the drop-down list
        sheet.getCellRange("C3:C4").getDataValidation().setValues(new String[]{"MATH", "HISTORY", "SOCIAL", "PHYSICS"});

        //Create a drop-down list in the specified cell
        sheet.getCellRange("C3:C4").getDataValidation().isSuppressDropDownArrow(false);

        //Save the result document
        workbook.saveToFile("ExcelDropdownList2.xlsx", ExcelVersion.Version2013);
    }
}