Data validation - dropdown list

The following example shows you how to create a dropdown list validation that restricts cell input to a predefined set of options.

C# example

using System;
using Independentsoft.Office.Spreadsheet;

namespace Sample
{
    class Program
    {
        static void Main(string[] args)
        {
            Worksheet sheet = new Worksheet();

            sheet["A1"] = new Cell("Status");
            sheet["B1"] = new Cell("Priority");

            // Dropdown list in column A
            DataValidation statusValidation = new DataValidation();
            statusValidation.Type = DataValidationType.List;
            statusValidation.Formula1 = "\"Open,In Progress,Closed\"";
            statusValidation.ReferenceSequence = "A2:A100";
            statusValidation.ShowErrorMessage = true;
            statusValidation.ErrorTitle = "Invalid Status";
            statusValidation.ErrorMessage = "Please select a valid status from the list.";

            // Dropdown list in column B
            DataValidation priorityValidation = new DataValidation();
            priorityValidation.Type = DataValidationType.List;
            priorityValidation.Formula1 = "\"Low,Medium,High,Critical\"";
            priorityValidation.ReferenceSequence = "B2:B100";
            priorityValidation.ShowErrorMessage = true;
            priorityValidation.ErrorTitle = "Invalid Priority";
            priorityValidation.ErrorMessage = "Please select Low, Medium, High or Critical.";

            DataValidations validations = new DataValidations();
            validations.Items.Add(statusValidation);
            validations.Items.Add(priorityValidation);

            sheet.DataValidations = validations;

            Workbook book = new Workbook();
            book.Sheets.Add(sheet);
            book.Save("c:\\test\\output.xlsx", true);
        }
    }
}

VB example

Imports System
Imports Independentsoft.Office.Spreadsheet

Module Module1
    Sub Main(ByVal args As String())

        Dim sheet As New Worksheet()

        sheet("A1") = New Cell("Status")
        sheet("B1") = New Cell("Priority")

        Dim statusValidation As New DataValidation()
        statusValidation.Type = DataValidationType.List
        statusValidation.Formula1 = """Open,In Progress,Closed"""
        statusValidation.ReferenceSequence = "A2:A100"
        statusValidation.ShowErrorMessage = True
        statusValidation.ErrorTitle = "Invalid Status"
        statusValidation.ErrorMessage = "Please select a valid status from the list."

        Dim priorityValidation As New DataValidation()
        priorityValidation.Type = DataValidationType.List
        priorityValidation.Formula1 = """Low,Medium,High,Critical"""
        priorityValidation.ReferenceSequence = "B2:B100"
        priorityValidation.ShowErrorMessage = True
        priorityValidation.ErrorTitle = "Invalid Priority"
        priorityValidation.ErrorMessage = "Please select Low, Medium, High or Critical."

        Dim validations As New DataValidations()
        validations.Items.Add(statusValidation)
        validations.Items.Add(priorityValidation)

        sheet.DataValidations = validations

        Dim book As New Workbook()
        book.Sheets.Add(sheet)
        book.Save("c:\test\output.xlsx", True)

    End Sub
End Module

IndependentsoftProfessional software libraries for developers

Spreadsheet .NET > Tutorial > Data validation - dropdown list

Need help? Ask our developers: