Java: Create a Drop-Down List in Excel

The drop-down list in Excel enables users to select a desired item from a pre-defined list of items as input data. It restricts the input of unwanted or ambiguous data and reduces the occurrence of spelling errors, which is a great option for speeding up data entry tasks. In this article, you will learn how to programmatically create a drop-down list in Excel using Spire.XLS for Java.

Install Spire.XLS for Java

First of all, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.

<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</artifactId>
        <version>14.3.2</version>
    </dependency>
</dependencies>
    

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

With Spire.XLS for Java, you can add values to a range of cells and then refer to that range of cells as the data validation source to create a drop-down list. This method might be slightly time-consuming, but it allows you to easily update the items in the drop-down list by directly modifying the values in the cells of the result document. The detailed steps are as follows.

  • Create a Workbook object.
  • Load a sample Excel document using Workbook.loadFromFile() method.
  • Get a specified worksheet using Workbook.getWorksheets().get() method.
  • Get a specified cell or cell range using Worksheet.getCellRange() method.
  • Add values to specified cells using CellRange.setValue() method.
  • Get data validation of the specified cell range using CellRange.getDataValidation() method.
  • Create a drop-down list by referring to a specified data range as the data validation source using Validation.setDataRange() method.
  • Save the result document using Workbook.saveToFile() method.
  • Java
import com.spire.xls.*;

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

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

        //Load a sample Excel document
        workbook.loadFromFile("input.xlsx");

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

        //Add values to specified cells
        sheet.getCellRange("A13").setValue("Complete");
        sheet.getCellRange("A14").setValue("Pending");
        sheet.getCellRange("A15").setValue("Cancelled");

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

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

Java: Create a Drop-Down List in Excel

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

In Excel, you can create a drop-down list by manually entering a list of values in the “Source” box of the Data Validation menu. By doing this, you don't need to add data in Excel cells, which keeps your Excel document neat and organized. The following steps shows how to use Spire.XLS for Java to achieve the same functionality.

  • Create a Workbook object.
  • Get a specified worksheet using Workbook.getWorksheets().get() method.
  • Add text to a cell and set its font style.
  • Get a specified cell or cell range using Worksheet.getCellRange() method.
  • Get data validation of the specified cell range using CellRange.getDataValidation() method.
  • Set the values of the drop-down list using Validation.setValues() method.
  • Create a drop-down list in the specified cell by setting the values of Validation.isSuppressDropDownArrow() method to false.
  • Save the result document using Workbook.saveToFile() method.
  • Java
import com.spire.xls.*;

public class DropdownList2 {
    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 cell B2 and set its font style
        sheet.getCellRange("B2").setValue("Country");
        sheet.getCellRange("B2").getStyle().getFont().isBold(true);
        sheet.getCellRange("B2").getStyle().setKnownColor(ExcelColors.LightTurquoise);

        //Set the values of the drop-down list
        sheet.getCellRange("C2").getDataValidation().setValues(new String[]{"France", "Japan", "Canada", "China", "Germany"});

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

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

Java: Create a Drop-Down List in Excel

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.