You can use data validation to control the type of data and values that users are allowed to enter in a cell. This can be useful if you only want to allow correct values in a cell based on your data. Spread WPF supports data validation.

Create validators to validate the user data. You can display a list of valid values for the user and display an invalid data image if the user types invalid data.

You can use any of several types of validator methods to create the validation criteria.

  • CreateDateValidator Method

  • CreateFormulaListValidator Method

  • CreateNumberValidator Method

  • CreateTextLengthValidator Method

  • CreateFormulaValidator Method

  • CreateListValidator Method


Set the HighlightInvalidData property to use the red ellipse as an invalid data image. You can also customize the invalid data image with the InvalidDataPresenter control.

The following code uses different types of validators to validate the data in B1, B2, and B3.

Spread WPF and Validation
Validators


C#
<code class="language-csharp">gcSpreadSheet1.HighlightInvalidData = true;
var valid = GrapeCity.Windows.SpreadSheet.Data.DataValidator.CreateListValidator("5,10,15,20");
gcSpreadSheet1.Sheets[0].Cells[0, 1].DataValidator = valid;
gcSpreadSheet1.Sheets[0].Cells[0, 0].Text = "5, 10, 15, and 20 are valid numbers.";
var valid1 = GrapeCity.Windows.SpreadSheet.Data.DataValidator.CreateNumberValidator(GrapeCity.Windows.SpreadSheet.Data.ComparisonOperator.GreaterThan, "5", "20", true);
gcSpreadSheet1.Sheets[0].Cells[1, 1].DataValidator = valid1;
gcSpreadSheet1.Sheets[0].Cells[1,0].Text = "A number greater than 5 is valid.";
var valid2 = GrapeCity.Windows.SpreadSheet.Data.DataValidator.CreateTextLengthValidator(GrapeCity.Windows.SpreadSheet.Data.ComparisonOperator.GreaterThan, "4", "20");
gcSpreadSheet1.Sheets[0].Cells[2, 1].DataValidator = valid2;
gcSpreadSheet1.Sheets[0].Cells[2, 0].Text = "Type more than four characters.";

VB
<code class="language-csharp">GcSpreadSheet1.HighlightInvalidData = True
Dim valid = GrapeCity.Windows.SpreadSheet.Data.DataValidator.CreateListValidator("5,10,15,20")
GcSpreadSheet1.Sheets(0).Cells(0, 1).DataValidator = valid
GcSpreadSheet1.Sheets(0).Cells(0, 0).Text = "5, 10, 15, and 20 are valid numbers."
Dim valid1 = GrapeCity.Windows.SpreadSheet.Data.DataValidator.CreateNumberValidator(GrapeCity.Windows.SpreadSheet.Data.ComparisonOperator.GreaterThan, "5", "20", True)
GcSpreadSheet1.Sheets(0).Cells(1, 1).DataValidator = valid1
GcSpreadSheet1.Sheets(0).Cells(1, 0).Text = "A number greater than 5 is valid."
Dim valid2 = GrapeCity.Windows.SpreadSheet.Data.DataValidator.CreateTextLengthValidator(GrapeCity.Windows.SpreadSheet.Data.ComparisonOperator.GreaterThan, "4", "20")
GcSpreadSheet1.Sheets(0).Cells(2, 1).DataValidator = valid2
GcSpreadSheet1.Sheets(0).Cells(2, 0).Text = "Type more than four characters."

The following code customizes the invalid data image. The following image illustrates a valid value for the cell.

Spread WPF and Validation
Valid Value


The following image illustrates an invalid value.

Spread WPF and Validation
Invalid Value


XAML





C#
<code class="language-csharp">gcSpreadSheet1.ActiveSheet.Cells["C1"].Value = 1;
gcSpreadSheet1.ActiveSheet.Cells["C2"].Value = 2;
gcSpreadSheet1.ActiveSheet.Cells["C3"].Value = 3;
gcSpreadSheet1.HighlightInvalidData = true;
var valid = GrapeCity.Windows.SpreadSheet.Data.DataValidator.CreateFormulaListValidator("C1:C3");
gcSpreadSheet1.Sheets[0].Cells[1, 1].DataValidator = valid;
gcSpreadSheet1.Sheets[0].Cells[1, 0].Text = "Formulas";

VB
<code class="language-csharp">GcSpreadSheet1.ActiveSheet.Cells("C1").Value = 1
GcSpreadSheet1.ActiveSheet.Cells("C2").Value = 2
GcSpreadSheet1.ActiveSheet.Cells("C3").Value = 3
GcSpreadSheet1.HighlightInvalidData = True
Dim valid = GrapeCity.Windows.SpreadSheet.Data.DataValidator.CreateFormulaListValidator("C1:C3")
GcSpreadSheet1.Sheets(0).Cells(1, 1).DataValidator = valid
GcSpreadSheet1.Sheets(0).Cells(1, 0).Text = "Formulas"

This example validates a date with the CreateDateValidator method.

Spread WPF and Validation
Date Validator


C#
<code class="language-csharp">//Type a date in cell (0,1)
gcSpreadSheet1.HighlightInvalidData = true;
var valid = GrapeCity.Windows.SpreadSheet.Data.DataValidator.CreateDateValidator(GrapeCity.Windows.SpreadSheet.Data.ComparisonOperator.Between, new System.DateTime(2012, 12, 31), DateTime.Now, false);
gcSpreadSheet1.Sheets[0].Cells[0, 1].DataValidator = valid;
gcSpreadSheet1.Sheets[0].Cells[0, 0].Text = "Enter a date between 12/31/2012 and now.";
gcSpreadSheet1.Sheets[0].SetColumnWidth(0, GrapeCity.Windows.SpreadSheet.Data.SheetArea.ColumnHeader, 300);
gcSpreadSheet1.Sheets[0].SetColumnWidth(1, GrapeCity.Windows.SpreadSheet.Data.SheetArea.ColumnHeader, 100);

VB
<code class="language-csharp">'Type a date in cell (0,1)
GcSpreadSheet1.HighlightInvalidData = True
Dim valid = GrapeCity.Windows.SpreadSheet.Data.DataValidator.CreateDateValidator(GrapeCity.Windows.SpreadSheet.Data.ComparisonOperator.Between, New System.DateTime(2012, 12, 31), DateTime.Now, False)
GcSpreadSheet1.Sheets(0).Cells(0, 1).DataValidator = valid
GcSpreadSheet1.Sheets(0).Cells(0, 0).Text = "Enter a date between 12/31/2012 and now."
GcSpreadSheet1.Sheets(0). SetColumnWidth(0, GrapeCity.Windows.SpreadSheet.Data.SheetArea.ColumnHeader, 300)
GcSpreadSheet1.Sheets(0). SetColumnWidth(1, GrapeCity.Windows.SpreadSheet.Data.SheetArea.ColumnHeader, 100)

This example uses a validator to check that the integer value is greater than 0.

Spread WPF and Validation
Formula Validator


C#
<code class="language-csharp">gcSpreadSheet1.HighlightInvalidData = true;
var valid = GrapeCity.Windows.SpreadSheet.Data.DataValidator.CreateFormulaValidator("B2>0");
valid.InputMessage = "Enter a value greater than 0 in B2";
valid.ShowInputMessage = true;
valid.InputTitle= "Tip";
gcSpreadSheet1.Sheets[0].Cells[1, 1].DataValidator = valid;
gcSpreadSheet1.Sheets[0].Cells[1, 0].Text = "Enter a value greater than 0 in B2.";

VB
<code class="language-csharp">GcSpreadSheet1.HighlightInvalidData = True
Dim valid = GrapeCity.Windows.SpreadSheet.Data.DataValidator.CreateFormulaValidator("B2>0")
valid.InputMessage = "Enter a value greater than 0 in B2"
valid.ShowInputMessage = True
valid.InputTitle = "Tip"
GcSpreadSheet1.Sheets(0).Cells(1, 1).DataValidator = valid
GcSpreadSheet1.Sheets(0).Cells(1, 0).Text = "Enter a value greater than 0 in B2."