Click here to Skip to main content
15,867,686 members
Articles / Programming Languages / C# 4.0

Apply Data Validation to Excel Cells in C#

Rate me:
Please Sign up or sign in to vote.
5.00/5 (5 votes)
31 Mar 2016CPOL2 min read 55.2K   1.2K   8  
This article provides a solution to add data validation to Excel cells in C#.

Introduction

Data validation is an Excel feature that you can use to define restrictions on what data can or should be entered in a cell. For example, you may want to restrict data entry to a certain range of numbers or limit choices by using a list. This tip presents how to add data validation to a cell programmatically using free Excel API with C#.

This Excel library provides a class named CellRange which contains DataValidation property, enabling programmers to handle validation in a specific cell or range directly. Following sections will demonstrate:

  • How to add a numeric validation
  • How to create a list validation
  • How to handle a data validation alert

Using the Code

Part 1 - Restrict Data Entry to a Decimal Number Within Limits

Initialize an object of Workbook class and get the first sheet to write data.

C#
Workbook wb = new Workbook();

Worksheet sheet = wb.Worksheets[0];

Input same text in the cell B9 and format the cell.

C#
sheet.Range["B9"].Text = "Input Number:";
sheet.Range["B9"].Style.Font.IsBold = true;
sheet.Range["B9"].Style.KnownColor = ExcelColors.Turquoise;

Add numeric validation to C9 by setting the allowed data type as decimal, setting the data range and comparison operator. In order to let users know what kind of data should be entered in the cell, you can choose to show an input message when the user selects the cell.

C#
sheet.Range["C9"].DataValidation.AllowType = CellDataType.Decimal;
sheet.Range["C9"].DataValidation.Formula1 = "1";
sheet.Range["C9"].DataValidation.Formula2 = "10";
sheet.Range["C9"].DataValidation.CompareOperator = ValidationComparisonOperator.Between;         
sheet.Range["C9"].DataValidation.InputMessage = "Type a number between 1-10 in this cell.";
sheet.Range["C9"].Style.KnownColor = ExcelColors.LightGreen1;

Output

Image 1

Part 2 - Restrict Entry to Predefined Items in a List

Insert some data in the cell B2 and format the cell.

C#
sheet.Range["B2"].Text = "Department:";
sheet.Range["B2"].Style.Font.IsBold = true;
sheet.Range["B2"].Style.KnownColor = ExcelColors.Turquoise;

To create an in-cell dropdown list, we need to input some data in source that will be displayed as items, then set IsSuppressDropDownArrow property as false to show arrow button which, when clicked, will display the dropdown list.

C#
sheet.Range["C2"].DataValidation.Values = new string[]{ "Sales", "HR", "R&D", "Finance" };
sheet.Range["C2"].DataValidation.IsSuppressDropDownArrow = false;
sheet.Range["C2"].Style.KnownColor = ExcelColors.LightGreen1;

Output

Image 2

Part 3 - Custom Data Validation Alert

Once the data validation is applied, people are not allowed to enter data in a cell that doesn’t match validation in the cell. If they do, they’ll see a data validation error alert.

Image 3

Instead of showing the default error alert, we could also custom the error alert through the following method:

C#
sheet.Range["C2"].DataValidation.AlertStyle = AlertStyleType.Warning;
sheet.Range["C2"].DataValidation.ShowError = true;
sheet.Range["C2"].DataValidation.ErrorTitle = "Error001";
sheet.Range["C2"].DataValidation.ErrorMessage = "Please select an item from list!";

Image 4

Conclusion

This article only gives examples for numeric and list validation, if interested, you can download this project (not including the DLL file) and create validations to restrict data entry to a date/time within a time frame, restrict data entry to text of a specified length, etc.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
-- There are no messages in this forum --