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.

Validators


C#
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
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.

Valid Value


The following image illustrates an invalid value.

Invalid Value


XAML

<Window.Resources>
<Style TargetType="ss:InvalidDataPresenter">
<Setter Property="Template">
<Setter.Value>
<ControlTemplate TargetType="ss:InvalidDataPresenter">
<Grid Background="{TemplateBinding Background}">
<Ellipse Stroke="Orange"
StrokeThickness="2"
Margin="-6,-3,-6,-3"
/>
</Grid>
</ControlTemplate>
</Setter.Value>
</Setter>
</Style>
</Window.Resources>

C#
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
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.

Date Validator


C#
//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
'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.

Formula Validator


C#
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
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."