Set Data Validation in Excel with the Syncfusion .NET Excel Library

Overview

The Syncfusion^® .NET Excel Library (XlsIO) enables you to create, read, and edit Excel documents programmatically without Microsoft Excel or interop dependencies. Using this library, you can set data validation in Excel files using C#.

Watch this video to see how to set data validation in Excel using the Syncfusion .NET Excel Library:

Watch the video

Set data validation in Excel

Follow these steps to set data validation in an Excel file using the Syncfusion .NET Excel Library.

Step 1: Create a new project

Start by creating a new C# Console Application project.

Step 2: Install the NuGet package

Add the Syncfusion.XlsIO.Net.Core package to your project from NuGet.org.

Step 3: Include namespaces

Add the following namespaces to your Program.cs file:

c#
using System.IO;
using Syncfusion.XlsIO;

Step 4: Add code to set data validation in an Excel file

Use the following code in the Program.cs file to create an Excel document and set data validation in it.

Run

c#
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Create(1);
    IWorksheet worksheet = workbook.Worksheets[0];

    //Data validation for list.
    IDataValidation listValidation = worksheet.Range["C3"].DataValidation;
    worksheet.Range["C1"].Text = "Data Validation List in C3";
    worksheet.Range["C1"].AutofitColumns();
    listValidation.ListOfValues = new string[] { "ListItem1", "ListItem2", "ListItem3" };

    //Show the error message.
    listValidation.ErrorBoxText = "Choose the value from the list";
    listValidation.ErrorBoxTitle = "ERROR";
    listValidation.PromptBoxText = "Data validation for list";
    listValidation.IsPromptBoxVisible = true;
    listValidation.ShowPromptBox = true;

    #region Save
    //Saving the workbook.
    FileStream outputStream = new FileStream(Path.GetFullPath(@"Output/ListValidation.xlsx"), FileMode.Create, FileAccess.Write);
    workbook.SaveAs(outputStream);
    #endregion

    //Dispose streams.
    outputStream.Dispose();
}