Spread WPF 18
Features / Data Validation
In This Topic
    Data Validation
    In This Topic

    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.

    Copy Code 
    // 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.";
    Copy Code 
    ' 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.

    Copy Code 
    // 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;
    Copy Code 
    ' 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.

    Copy Code 
    // 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;
    Copy Code 
    ' 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.

    Copy Code 
    // 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.";
    Copy Code 
    ' 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.

    Copy Code 
    // 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.";
    Copy Code 
    ' 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.

    Copy Code 
    // 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;
    Copy Code 
    ' 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.

    Copy Code 
    // 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.";
    Copy Code 
    ' 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."