[]
DEMOS DOCS API BLOG SUPPORT PRICING DOWNLOAD
(Showing Draft Content)

Data Validation

Data validation in Spread for WPF allows you to control the type of data that users can enter into the cells. You can limit the range of acceptable values or restrict entries to a specified format, such as text, numbers, dates, and more. This feature helps prevent errors and ensures data consistency by restricting invalid data entry.

Data Validation Alerts

There are various ways in which data can be validated such as restricting invalid input keys and displaying error or warning messages. The following table lists different types of data validation alerts that can be triggered when users enter invalid data.

Alert Type

Description

Options

Stop

Prevents users from entering invalid data in a cell or cell range.

When this alert appears, you must enter a value that must satisfy the data validation criteria specified for the cell.

  • OK - Select to close the dialog and enter a valid value.

  • Cancel - Select to remove the invalid value.

Warning

Warns users that the data entered in the cell or cell range is invalid.

  • Yes - Select to accept the invalid data.

  • No - Select to edit the invalid data.

  • Cancel - Select to remove the invalid data.

Information

Informs users that the data is invalid but does not prevent them from continuing.

  • OK - Select to accept the invalid data.

  • Cancel - Select to remove the invalid data.

Types of Data Validation

Spread for WPF supports several types of data validation, which can be applied using the DataValidationType enumeration in the GrapeCity.Spreadsheet namespace.

Number Validation

This type of validation checks if the value entered in a cell, is a whole number. Number validation stops users from entering numbers that fall outside the specified criteria. Use the WholeNumber property of the DataValidationType enumeration to apply this validation.

C#

// Validate integer value.
spreadSheet1.Workbook.ActiveSheet.Cells["A1"].Text = "Integer validation";
var dvWholeNumber =
spreadSheet1.Workbook.ActiveSheet.Cells["A2:B3"].Validation.Add(GrapeCity.Spreadsheet.DataValidationType.WholeNumber,
GrapeCity.Spreadsheet.DataValidationErrorStyle.Warning, GrapeCity.Spreadsheet.DataValidationOperator.Between, "18", "59");
dvWholeNumber.ShowError = true;
dvWholeNumber.IgnoreBlank = false;
dvWholeNumber.ErrorMessage = "Please enter an integer between 18 and 59.";
dvWholeNumber.ErrorTitle = "Invalid data.";

VB

' Validate integer value.
spreadSheet1.Workbook.ActiveSheet.Cells("A1").Text = "Integer validation"
Dim dvWholeNumber = spreadSheet1.Workbook.ActiveSheet.Cells("A2:B3").Validation.Add(GrapeCity.Spreadsheet.DataValidationType.WholeNumber, GrapeCity.Spreadsheet.DataValidationErrorStyle.Warning, GrapeCity.Spreadsheet.DataValidationOperator.Between, "18", "59")
dvWholeNumber.ShowError = True
dvWholeNumber.IgnoreBlank = False
dvWholeNumber.ErrorMessage = "Please enter an integer between 18 and 59."
dvWholeNumber.ErrorTitle = "Invalid data."

Decimal Validation

This type of validation ensures users only enter decimal numbers in a cell or range of cells. Use the Decimal property of the DataValidationType enumeration to apply decimal validation in the worksheet.

C#

// Validate decimal value.
spreadSheet1.Workbook.ActiveSheet.Cells["A1"].Text = "Validate Decimal Point";
var dvDecimal = spreadSheet1.Workbook.ActiveSheet.Cells["B4:C5"].
Validation.Add(GrapeCity.Spreadsheet.DataValidationType.Decimal,
GrapeCity.Spreadsheet.DataValidationErrorStyle.Stop,
GrapeCity.Spreadsheet.DataValidationOperator.GreaterThan, "100.1", "");
dvDecimal.ShowError = true;

VB

' Validate decimal value.
spreadSheet1.Workbook.ActiveSheet.Cells("A1").Text = "Validate Decimal Point"
Dim dvDecimal = spreadSheet1.Workbook.ActiveSheet.Cells("B4:C5").Validation.Add(GrapeCity.Spreadsheet.DataValidationType.Decimal, GrapeCity.Spreadsheet.DataValidationErrorStyle.Stop, GrapeCity.Spreadsheet.DataValidationOperator.GreaterThan, "100.1", "")
dvDecimal.ShowError = True

List Validation

This type of validation checks that the input matches a value from a predefined list present in the cell. Use the List property of the DataValidationType enumeration to apply list validation in the worksheet.

C#

// Validate list options.
spreadSheet1.Workbook.ActiveSheet.Cells["A1"].Text = "List validation";
var dvList =
spreadSheet1.Workbook.ActiveSheet.Cells["A2:C8"].
Validation.Add(GrapeCity.Spreadsheet.DataValidationType.List,
GrapeCity.Spreadsheet.DataValidationErrorStyle.Stop,
GrapeCity.Spreadsheet.DataValidationOperator.Between, "XS,S,M,L,XL, XXL", "");
dvList.ShowError = true;
dvList.IgnoreBlank = true;
dvList.InCellDropdown = true;

VB

' Validate list options.
spreadSheet1.Workbook.ActiveSheet.Cells("A1").Text = "List validation"
Dim dvList = spreadSheet1.Workbook.ActiveSheet.Cells("A2:C8").Validation.Add(GrapeCity.Spreadsheet.DataValidationType.List, GrapeCity.Spreadsheet.DataValidationErrorStyle.Stop, GrapeCity.Spreadsheet.DataValidationOperator.Between, "XS,S,M,L,XL, XXL", "")
dvList.ShowError = True
dvList.IgnoreBlank = True
dvList.InCellDropdown = True

Date Validation

This type of validation ensures that the entered data in the cell or cell range is in a valid date format. It restricts users from entering an invalid date based on the criteria you specified for the cell. Use the Date property of the DataValidationType enumeration for date validation.

C#

// Validate date.
spreadSheet1.Workbook.ActiveSheet.Cells["A1"].Text = "Date validation";
var dvDate =
spreadSheet1.Workbook.ActiveSheet.Cells["B1:C4"].
Validation.Add(GrapeCity.Spreadsheet.DataValidationType.Date,
GrapeCity.Spreadsheet.DataValidationErrorStyle.Stop,
GrapeCity.Spreadsheet.DataValidationOperator.LessThan, "=TODAY()", "");
dvDate.IgnoreBlank = true;
dvDate.ShowError = true;
dvDate.ErrorTitle = "Invalid date.";
dvDate.ErrorMessage = "Date must be before today.";

VB

' Validate date.
spreadSheet1.Workbook.ActiveSheet.Cells("A1").Text = "Date validation"
Dim dvDate = spreadSheet1.Workbook.ActiveSheet.Cells("B1:C4").Validation.Add(GrapeCity.Spreadsheet.DataValidationType.Date, GrapeCity.Spreadsheet.DataValidationErrorStyle.Stop, GrapeCity.Spreadsheet.DataValidationOperator.LessThan, "=TODAY()", "")
dvDate.IgnoreBlank = True
dvDate.ShowError = True
dvDate.ErrorTitle = "Invalid date."
dvDate.ErrorMessage = "Date must be before today."

Time Validation

This type of validation checks the time entered in the cell or cell range. It restricts invalid time entries based on your specified criteria. Use the Time property of the DataValidationType enumeration to apply time validation in a worksheet.

C#

// Validate time.
spreadSheet1.Workbook.ActiveSheet.Cells["A1"].Text = "Validate time";
var dvTime = spreadSheet1.Workbook.ActiveSheet.Cells["B1:C4"].
Validation.Add(GrapeCity.Spreadsheet.DataValidationType.Time,
GrapeCity.Spreadsheet.DataValidationErrorStyle.Stop,
GrapeCity.Spreadsheet.DataValidationOperator.Between, "09:00", "17:00");
dvTime.ShowError = true;
dvTime.IgnoreBlank = true;
dvTime.ErrorTitle = "Invalid time.";
dvTime.ErrorMessage = "Time must be between 09:00 and 17:00.";

VB

' Validate time.
spreadSheet1.Workbook.ActiveSheet.Cells("A1").Text = "Validate time"
Dim dvTime = spreadSheet1.Workbook.ActiveSheet.Cells("B1:C4").Validation.Add(GrapeCity.Spreadsheet.DataValidationType.Time, GrapeCity.Spreadsheet.DataValidationErrorStyle.Stop, GrapeCity.Spreadsheet.DataValidationOperator.Between, "09:00", "17:00")
dvTime.ShowError = True
dvTime.IgnoreBlank = True
dvTime.ErrorTitle = "Invalid time."
dvTime.ErrorMessage = "Time must be between 09:00 and 17:00."

Text Length Validation

This type of validation limits the number of characters that a user can enter in a cell or cell range. It is useful when limiting input, such as setting a maximum character count. Use the TextLength property of the DataValidationType enumeration to apply text length validation.

C#

// Validate text length.
spreadSheet1.Workbook.ActiveSheet.Cells["A1"].Text = "Validate text length";
var dvTextLength = spreadSheet1.Workbook.
ActiveSheet.Cells["B1:C7"].Validation.Add(GrapeCity.Spreadsheet.DataValidationType.TextLength,
GrapeCity.Spreadsheet.DataValidationErrorStyle.Stop,
GrapeCity.Spreadsheet.DataValidationOperator.Equal, "5", "");
dvTextLength.ErrorTitle = "Invalid Product Code";
dvTextLength.ErrorMessage = "Please enter a valid 5-digit product code.";
dvTextLength.ShowError = true;
dvTextLength.IgnoreBlank = true;

VB

' Validate text length.
spreadSheet1.Workbook.ActiveSheet.Cells("A1").Text = "Validate text length"
Dim dvTextLength = spreadSheet1.Workbook.ActiveSheet.Cells("B1:C7").Validation.Add(GrapeCity.Spreadsheet.DataValidationType.TextLength, GrapeCity.Spreadsheet.DataValidationErrorStyle.Stop, GrapeCity.Spreadsheet.DataValidationOperator.Equal, "5", "")
dvTextLength.ErrorTitle = "Invalid Product Code"
dvTextLength.ErrorMessage = "Please enter a valid 5-digit product code."
dvTextLength.ShowError = True
dvTextLength.IgnoreBlank = True

Custom Formula Validation

This type of validation allows you to create your own rules to check the formulas entered in a cell or cell range. Use the Custom property of the DataValidationType enumeration to apply custom formula validation in a worksheet.

C#

// Validate custom formula.
spreadSheet1.Workbook.ActiveSheet.Cells["A1"].Text = "Custom formula validation";
var dvCustomFormula =
spreadSheet1.Workbook.ActiveSheet.Cells["A3"].
Validation.Add(GrapeCity.Spreadsheet.DataValidationType.Custom,
GrapeCity.Spreadsheet.DataValidationErrorStyle.Stop,
GrapeCity.Spreadsheet.DataValidationOperator.Equal, "=ISNUMBER(A3)", "");
dvCustomFormula.ShowError = true;
dvCustomFormula.IgnoreBlank = false;
dvCustomFormula.ErrorTitle = "Invalid data.";
dvCustomFormula.ErrorMessage = "Please enter a number.";

VB

' Validate custom formula.
spreadSheet1.Workbook.ActiveSheet.Cells("A1").Text = "Custom formula validation"
Dim dvCustomFormula = spreadSheet1.Workbook.ActiveSheet.Cells("A3").Validation.Add(GrapeCity.Spreadsheet.DataValidationType.Custom, GrapeCity.Spreadsheet.DataValidationErrorStyle.Stop, GrapeCity.Spreadsheet.DataValidationOperator.Equal, "=ISNUMBER(A3)", "")
dvCustomFormula.ShowError = True
dvCustomFormula.IgnoreBlank = False
dvCustomFormula.ErrorTitle = "Invalid data."
dvCustomFormula.ErrorMessage = "Please enter a number."