Add Data Validation Input Messages to Cells of Excel in C#.NET
  1. Home
  2. Excel Editing in .NET
  3. Adding Data Validation to Spreadsheet C# Tutorial
Error processing SSI file

How to Add Data Validation to Excel in C#.NET Code

iDiTect.Excel Data Validation provides a nice mechanism to help users select from a set of acceptable choices, validation types contain Interger, Decimal, DateTime, Time, List and TextLength.

How to Create DropDown List Data Validation to Spreadsheet in C# language

Besides List data validation, we also provide full code C# demo to add Number validation, Text Length validation and Date/Time validation.

Add Acceptable Data to List

First you need prepare a range of data, the data cells can be from current worksheet, or other worksheet in the same workbook. Add a list value validation to cell "A6" in worksheet, the appearance of this validation will be shown as drop down list.

//First you need initialize a list of data
worksheet.Cell("A1").Value = 1;
worksheet.Cell("A2").Value = 2;
worksheet.Cell("A3").Value = 3;
worksheet.Cell("A4").Value = 4;
worksheet.Cell("A5").Value = 5;

var validation = worksheet.Cell("A6").SetDataValidation();
//Add data in current worksheet
validation.List("A1:A5");
//Add data in the other worksheet of the same workbook
//validation.List("Sheet2!A1:A5");

Show Prompt Title and Message

Enable ShowPromptMessage to true to show prompt message. This prompt title and message will appear when the mouse hovering on the validation cell.

validation.ShowPromptMessage = true;
validation.PromptTitle = "Enter pre-defined value";
validation.PromptMessage = "Select or enter a value from the list";

Show Error Title and Message

Enable ShowErrorMessage to true to show error message. And there are three types of error style, Stop, Warning and Information.

validation.ShowErrorMessage = true;
validation.ErrorTitle = "An invalid value was entered";
validation.ErrorMessage = "Select a value from the list";
validation.ErrorStyle = ErrorStyle.Stop;

Full code for adding Excel List data validation in C#

var workbook = new Workbook();
var worksheet = workbook.Worksheets.Add("ValidationSheet");

//First you need initialize a list of data
worksheet.Cell("A1").Value = 1;
worksheet.Cell("A2").Value = 2;
worksheet.Cell("A3").Value = 3;
worksheet.Cell("A4").Value = 4;
worksheet.Cell("A5").Value = 5;

var validation = worksheet.Cell("A6").SetDataValidation();
//Add data in current worksheet
validation.List("A1:A5");
//Add data in the other worksheet of the same workbook
//validation.List("Sheet2!A1:A5");

// Show prompt message
validation.ShowPromptMessage = true;
validation.PromptTitle = "Enter pre-defined value";
validation.PromptMessage = "Select or enter a value from the list";
// Show error message
validation.ShowErrorMessage = true;
validation.ErrorTitle = "An invalid value was entered";
validation.ErrorMessage = "Select a value from the list";
validation.ErrorStyle = ErrorStyle.Stop;

workbook.Save("ListValidation.xlsx");

Full code for adding Excel Number data validation in C#

var workbook = new Workbook();
var worksheet = workbook.Worksheets.Add("ValidationSheet");

// Add a between value validation 
var validation = worksheet.Cell("A1").SetDataValidation();
validation.Decimal.Between(1, 10);
// Show prompt message
validation.ShowPromptMessage = true;
validation.PromptTitle = "Enter decimal value";
validation.PromptMessage = "Value should be between 1 and 10";
// Show error message
validation.ShowErrorMessage = true;
validation.ErrorStyle = ErrorStyle.Stop;
validation.ErrorTitle = "An invalid value was entered";
validation.ErrorMessage = "Value must be between 1 and 10";


//You can also add other type validation, like greater than...
var validation2 = worksheet.Range("A2:A10").SetDataValidation();
validation2.Integer.GreaterThan(10);
validation2.ShowErrorMessage = true;
validation2.ErrorStyle = ErrorStyle.Warning;
validation2.ErrorTitle = "An invalid value was entered";
validation2.ErrorMessage = "Value must be integer, and greater than 10";

workbook.Save("NumberValidation.xlsx");

Full code for adding Excel Text Length data validation in C#

var workbook = new Workbook();
var worksheet = workbook.Worksheets.Add("ValidationSheet");

var validation = worksheet.Cell(1, 1).SetDataValidation();
validation.TextLength.GreaterThanOrEqual(5);

// Show prompt message
validation.ShowPromptMessage = true;
validation.PromptMessage = "Input charactor length should be greater than 4";
// Show error message
validation.ShowErrorMessage = true;
validation.ErrorStyle = ErrorStyle.Information;
validation.ErrorTitle = "Value length is invalid";
validation.ErrorMessage = "Value length must be greater or equal 5";

workbook.Save("TextLengthValidation.xlsx");

Full code for adding Excel Date and Time data validation in C#

var workbook = new Workbook();
var worksheet = workbook.Worksheets.Add("ValidationSheet");

var validation = worksheet.Cell("A1").SetDataValidation();
validation.Date.GreaterThan(new DateTime(2010, 1, 1));
// Show error message
validation.ErrorTitle = "An invalid value was entered";
validation.ErrorMessage = "Input date must be after 1/1/2010";

var validation2 = worksheet.Cell("A2").SetDataValidation();
validation2.Time.Between(new TimeSpan(9, 0, 0), new TimeSpan(17, 59, 59));
// Show error message
validation2.ErrorTitle = "An invalid value was entered";
validation2.ErrorMessage = "Input time must be between in 9:00:00 - 17:59:59";

workbook.Save("DateAndTimeValidation.xlsx");
What iDiTect .NET Document component can do

We provide powerful & profession document & image controls: Convert from HTML to PDF in CSharp HTML to PDF Converter for .NET and C# is a fast method to easily create richly-formatted PDF documents, images from HTML. C# / VB.NET mail merge Word file Provides C# Demo Codes for Word MeilMerge Processing to Users. Excel Spreadsheet Finding and Replacing Text in C#.NET allows developers to find and replace text in Excel rows and columns, especially in table area using C# or VB.NET. Converting Image Files to PDF/title> Convert Image to PDF in C#, VB.NET working on to merge multiple image files into a single, multi-page PDF (one image per page). Add AutoFilter to Excel file in c# a AutoFilter that provides information about filtered lists on the worksheet. C# - How to add a Bookmark control to a Word document at runtime Word bookmark is convenient for users to go to specified location and modify contents of bookmark range in a document.

More Programming Tutorials
More Programming FAQs

We offer full featured SDK for 30 days evaluation test.

Download Free     Order Now