Implementing Data Validation in Excel Cells Using C#
Tags: fileformats, csharp, datavalidation, excel
Published: 2023-08-14 04:44:32

---
title: Implementing Data Validation in Excel Cells Using C#
published: true
date: 2023-08-11 09:57:59 UTC
tags: fileformats, csharp, datavalidation, excel
canonical_url: https://www.syncfusion.com/blogs/post/data-validation-excel-csharp.aspx
cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/knyuzmoal2dequg8z8em.png
---
[Data validation](https://en.wikipedia.org/wiki/Data_validation "Wikipedia Link: Data validation") allows you to define rules that restrict the type, length, and data values that can be entered into an Excel cell. By implementing data validation, you can prevent data entry errors, enforce consistency, and improve the overall quality of your Excel workbooks. Whether you’re building a data entry form, creating templates, or automating data import processes, adding data validation using C# provides a robust and flexible solution.

Syncfusion [Excel library (XlsIO)](https://www.syncfusion.com/document-processing/excel-framework/net/excel-library ".NET Excel Library") is a C# library that provides a high-level API for creating and editing Excel files. It provides support for adding [data validation](https://help.syncfusion.com/file-formats/xlsio/working-with-data-validation "Working with data validation in Excel") to your Excel documents.

This blog post will show you how to use the Syncfusion Excel Library to add data validation to an Excel worksheet. We will cover the following data validation contexts:

- [Dropdown List](#Dropdown)
- [Number](#Number)
- [Text Length](#Text)
- [Date or Time](#Date)
- [Formula](#Formula)

## <a name="Dropdown">Dropdown List</a>

A dropdown list is a common worksheet feature that is very helpful for restricting a cell value to a predefined set of values. The user can provide an array of values for the list separately or specify them from a cell range inside the workbook. This ensures that the cell value is always one of the expected values.

The following code example illustrates how to create a dropdown list data validation:

```csharp
//List data validation
IDataValidation listValidation = worksheet.Range["C3"].DataValidation;
listValidation.ListOfValues = new string[] { "ListItem1", "ListItem2", "ListItem3" };
```

## <a name="Number">Number</a>

Number validation lets you specify whether the cell should accept only integers or decimal numbers with specific limits.

The following code example illustrates how to set number validation:

```csharp
//Data Validation for Numbers
IDataValidation numberValidation = worksheet.Range["D3"].DataValidation;
numberValidation.AllowType = ExcelDataType.Integer;
numberValidation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
numberValidation.FirstFormula = "0";
numberValidation.SecondFormula = "10";
```

## <a name="Text">Text length</a>

Text length validation is useful to limit the number of characters entered in a cell. By specifying the minimum and maximum length, you can ensure that only valid text is entered by the users.

Here’s an example of setting up text length validation:

```csharp
//Text length data validation
IDataValidation txtLengthValidation = worksheet.Range["A3"].DataValidation;
txtLengthValidation.AllowType = ExcelDataType.TextLength;
txtLengthValidation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
txtLengthValidation.FirstFormula = "0";
txtLengthValidation.SecondFormula = "5";
```

## <a name="Date">Date or time</a>

Restricting date or time values is crucial in many applications. You can validate and limit the entered date or time range to ensure data accuracy. For example, start date, end date, buy time, and sell time are important key values that need to be accurate for processing data and producing a finance report.

The following code example illustrates how to set date and time data validation:

```csharp
// Date validation
IDataValidation dateValidation = worksheet.Range["E3"].DataValidation;
dateValidation.AllowType = ExcelDataType.Date;
dateValidation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
dateValidation.FirstDateTime = new DateTime(2003, 5, 10);
dateValidation.SecondDateTime = new DateTime(2004, 5, 10);

// Time validation
IDataValidation timeValidation = worksheet.Range["B3"].DataValidation;
timeValidation.AllowType = ExcelDataType.Time;
timeValidation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
timeValidation.FirstFormula = "10.00";
timeValidation.SecondFormula = "12.00";
```

## <a name="Formula">Formula</a>

In cases where you need to evaluate cell values based on custom conditions, formula validation can be helpful. Excel formulas can be employed to validate cell values against specific criteria.

The following code example illustrates how to set formula validation:

```csharp
// Custom data validation
IDataValidation dataValidation = worksheet.Range["A3"].DataValidation;

// Set the data validation type to Formula
dataValidation.AllowType = ExcelDataType.Formula;

// Set the formula for validation
dataValidation.FirstFormula = "=A3>0";
```

The following example shows the code for adding data validation to an Excel document:

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

    //Adding list validation
    IDataValidation listValidation = sheet.Range["C7"].DataValidation;
    sheet.Range["B7"].Text = "Select an item from the dropdown list";
    listValidation.ListOfValues = new string[] { "Brand", "Price", "Product" };
    listValidation.PromptBoxText = "List validation";
    listValidation.IsPromptBoxVisible = true;
    listValidation.ShowPromptBox = true;

    //Adding number validation
    IDataValidation numbervalidation = sheet.Range["C9"].DataValidation;
    sheet.Range["B9"].Text = "Enter a number between 0 to 10";
    numbervalidation.AllowType = ExcelDataType.Integer;
    numbervalidation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
    numbervalidation.FirstFormula = "0";
    numbervalidation.SecondFormula = "10";
    numbervalidation.ShowErrorBox = true;
    numbervalidation.ErrorBoxText = "Enter value between only 0 to 10";
    numbervalidation.ErrorBoxTitle = "ERROR";
    numbervalidation.PromptBoxText = "Number validation";
    numbervalidation.ShowPromptBox = true;

    //Adding date validation
    IDataValidation dateValidation = sheet.Range["C11"].DataValidation;
    sheet.Range["B11"].Text = "Enter a date between 5/10/2003 to 5/10/2004";
    dateValidation.AllowType = ExcelDataType.Date;
    dateValidation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
    dateValidation.FirstDateTime = new DateTime(2003, 5, 10);
    dateValidation.SecondDateTime = new DateTime(2004, 5, 10);
    dateValidation.ShowErrorBox = true;
    dateValidation.ErrorBoxText = "Enter value between 5/10/2003 to 5/10/2004";
    dateValidation.ErrorBoxTitle = "ERROR";
    dateValidation.PromptBoxText = "Date validation";
    dateValidation.ShowPromptBox = true;

    //Adding text length validation
    IDataValidation textValidation = sheet.Range["C13"].DataValidation;
    sheet.Range["B13"].Text = "Enter a text of 6 characters or less";
    textValidation.AllowType = ExcelDataType.TextLength;
    textValidation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
    textValidation.FirstFormula = "1";
    textValidation.SecondFormula = "6";
    textValidation.ShowErrorBox = true;
    textValidation.ErrorBoxText = "Enter a text with length of maximum 6 characters";
    textValidation.ErrorBoxTitle = "ERROR";
    textValidation.PromptBoxText = "Text length validation";
    textValidation.ShowPromptBox = true;

    //Adding time validation
    IDataValidation timeValidation = sheet.Range["C15"].DataValidation;
    sheet.Range["B15"].Text = "Enter a time between 10:00 AM to 12:00 PM";
    timeValidation.AllowType = ExcelDataType.Time;
    timeValidation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
    timeValidation.FirstFormula = "10";
    timeValidation.SecondFormula = "12";
    timeValidation.ShowErrorBox = true;
    timeValidation.ErrorBoxText = "Enter the time between 10 to 12 ";
    timeValidation.ErrorBoxTitle = "ERROR";
    timeValidation.PromptBoxText = "Time validation";
    timeValidation.ShowPromptBox = true;

    //Adding time validation
    IDataValidation formulaValidation = sheet.Range["C17"].DataValidation;
    sheet.Range["B17"].Text = "Enter a negative number";
    formulaValidation.AllowType = ExcelDataType.Formula;
    formulaValidation.FirstFormula = "=C17 < 0";
    formulaValidation.ShowErrorBox = true;
    formulaValidation.ErrorBoxText = "Enter only negative numbers";
    formulaValidation.ErrorBoxTitle = "ERROR";
    formulaValidation.PromptBoxText = "Formula validation";
    formulaValidation.ShowPromptBox = true;

    sheet.Range["B2:C2"].Merge();

    sheet.Range["B2"].Text = "Data validation";
    sheet.Range["B5"].Text = "Validation criteria";
    sheet.Range["C5"].Text = "Validation";
    sheet.Range["B5"].CellStyle.Font.Bold = true;
    sheet.Range["C5"].CellStyle.Font.Bold = true;
    sheet.Range["B2"].CellStyle.Font.Bold = true;
    sheet.Range["B2"].CellStyle.Font.Size = 16;
    sheet.Range["B2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;

    sheet.UsedRange.AutofitColumns();
    sheet.UsedRange.AutofitRows();

    //Saving the workbook
    FileStream outputStream = new FileStream("DataValidation.xlsx", FileMode.Create, FileAccess.Write);
    workbook.SaveAs(outputStream);
    outputStream.Dispose();
}
```
<figure>

  <img src="https://www.syncfusion.com/blogs/wp-content/uploads/2023/08/Adding-Data-Validation-to-Excel-Cells-Using-C.gif" alt="Adding Data Validation to Excel Cells Using C#" style="width:100%">

  <figcaption>Adding Data Validation to Excel Cells Using C#</figcaption>

</figure>

## GitHub samples

You can download examples of data validation in C# from [this GitHub page](https://github.com/SyncfusionExamples/XlsIO-Examples/tree/master/Data%20Validation "Data validation in Excel using C# GitHub demo").

## Conclusion

As you can see, the [Syncfusion Excel (XlsIO) Library](https://www.syncfusion.com/excel-framework/net "Syncfusion Excel (XlsIO) Library") supports [data validation](https://help.syncfusion.com/file-formats/xlsio/working-with-data-validation "Working with data validation in Excel") in C#. Take a moment to peruse the [documentation](https://help.syncfusion.com/file-formats/xlsio/working-with-data#importing-data-to-worksheets "Importing data to Work Sheets"), where you’ll find other Excel options and features like [conditional formatting](https://help.syncfusion.com/file-formats/xlsio/working-with-conditional-formatting "Working with Conditional Formatting in Excel"), [tables](https://help.syncfusion.com/file-formats/xlsio/working-with-excel-tables "Working with Excel Tables"), [pivot tables](https://help.syncfusion.com/file-formats/xlsio/working-with-pivot-tables "Working with Pivot Tables in Excel"), and [charts](https://help.syncfusion.com/file-formats/xlsio/working-with-charts "Working with charts in Excel").

Using the XlsIO library, you can export Excel data to [PDF](https://help.syncfusion.com/file-formats/xlsio/excel-to-pdf-conversion "Excel to PDF Conversion"), [image](https://help.syncfusion.com/file-formats/xlsio/worksheet-to-image-conversion "Worksheet to Image conversion"), [data table](https://help.syncfusion.com/file-formats/xlsio/working-with-data#exporting-from-worksheet-to-data-table "Exporting from Worksheet to Data Table"), [CSV](https://www.syncfusion.com/kb/9092/convert-excel-file-to-csv-in-c-vb-net "Converting Excel file to CSV"), [TSV](https://www.syncfusion.com/kb/8556/does-xlsio-support-tsv-files "Excel to TSV conversion"), [HTML](https://help.syncfusion.com/file-formats/xlsio/working-with-excel-worksheet#save-worksheet-as-html "Save Work sheet as HTML"), [collections of objects](https://help.syncfusion.com/file-formats/xlsio/working-with-data#exporting-from-worksheet-to-clr-objects "Working with Excel Data"), [ODS](https://help.syncfusion.com/file-formats/xlsio/excel-to-ods-conversion "Excel to ODS Conversion"), [JSON](https://help.syncfusion.com/file-formats/xlsio/excel-to-json-conversion "Excel to JSON Conversion"), and more file formats.

If you are new to our Excel Library, following our [getting started guide](https://help.syncfusion.com/file-formats/xlsio/getting-started-create-excel-file-csharp-vbnet "Create Excel file in C# and VB.NET") is highly recommended.

Are you already a Syncfusion user? You can download the product setup [here](https://www.syncfusion.com/account/downloads/studio/ "Essential Studio License and Downloads page"). If you’re not a Syncfusion user yet, you can download a free 30-day trial [here](https://www.syncfusion.com/downloads/fileformats/confirm "Get the free evaluation of the Essential Studio for File Formats").

Please let us know in the comments section below if you have any questions about these features. Also, you can contact us through our [support forum](https://www.syncfusion.com/forums "Syncfusion Support Forum"), [support portal](https://support.syncfusion.com/ "Syncfusion Support Portal"), or [feedback portal](https://www.syncfusion.com/feedback/excel "Syncfusion Feedback Portal"). We are always happy to assist you!

## Related blogs

- [Easiest Ways to Highlight Data in Excel Using C#](https://blog.syncfusion.com/blogs/post/highlight-data-in-excel-using-csharp.aspx "Blog: Easiest Ways to Highlight Data in Excel Using C#")
- [6 Easy Ways to Export Data to Excel in C#](https://www.syncfusion.com/blogs/post/6-easy-ways-to-export-data-to-excel-in-c-sharp.aspx "Blog: 6 Easy Ways to Export Data to Excel in C#")
- [Export Data from Collection to Excel and Group It in C#](https://www.syncfusion.com/blogs/post/export-data-from-collection-to-excel-and-group-csharp.aspx "Blog: Export Data from Collection to Excel and Group It in C#")
- [Export Data to a Predefined Excel Template in C#](https://www.syncfusion.com/blogs/post/export-data-to-a-predefined-excel-template-in-c.aspx "Blog: Export Data to a Predefined Excel Template in C#")
- [Easy Steps to Export HTML Tables to an Excel Worksheet in C#](https://www.syncfusion.com/blogs/post/easy-steps-to-export-html-tables-to-an-excel-worksheet-in-c.aspx "Blog: Easy Steps to Export HTML Tables to an Excel Worksheet in C#")