Excel for .NET
データの検証

Data validation lets you control the data that an end-user can enter into a cell. There are various ways in which a data can be validated such as restricting invalid input keys, showing the error or warning information or revert the value to original on getting an invalid input from the user.

The following image showcases data validation applied to the Excel worksheet. Here, we display an input message to notify the end-users to enter a value ranging between 1-5.

In Excel, data validation can be implemented using the XLDataValidation class. The XLDataValidation class provides various properties such as ErrorValidationTypeShowErrorMessage, ShowInputMessage, and much more for implementing enhanced data validation. The ValidationType property accepts input from the XLValidationType enumeration to set the validation type to one of the following:

Enumeration Values Descriptions
Custom Specifies that the data validation uses a custom formula to check the cell value.
Date Specifies that the data validation checks for and allows date values that meet the given condition.
Decimal Specifies that the data validation checks for and allows decimal values that meet the given condition.
List Specifies that the data validation checks for and allows a value that matches one in a list of values.
None Specifies that the data validation allows any type of value and does not check for a type or range of values.
TextLength Specifies that the data validation checks for and allows text values whose length meet the given condition.
Time Specifies that the data validation checks for and allows time values that meet the given condition.
Whole Specifies that the data validation checks for and allows whole number values that meet the given condition.

To apply data validation in Excel, you can use the following code. In this example, we apply data validation to restrict the end-users to enter a number between 1 to 5 by displaying an input messages using Prompt property and an error message using Error property.

C#
コードのコピー
var validation = new XLDataValidation(c1XLBook1);
validation.AllowBlank = true;
validation.Prompt = "Input a value between 1 and 5(included), please";
validation.PromptTitle = "Tips";
validation.Error = "input value does not fall between 1 and 5(included)";
validation.ErrorTitle = "Error";
validation.ShowErrorMessage = true;
validation.ShowInputMessage = true;
validation.Ranges.Add(new XLRange(1, 1, 5, 1));
validation.ValidationType = XLValidationType.Whole;
validation.Operator = XLComparisonOperator.Between;
validation.FirstFormula = "1";
validation.FirstFormula = "5";
c1XLBook.Sheets[0].DataValidations.Add(validation);