Spread WPF and Validation

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. SpreadSLvalid 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. SpreadSLformlist Valid Value The following image illustrates an invalid value. SpreadSLformlist2 Invalid Value XAML <Window.Resources> </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. SpreadSLvaliddate 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. SpreadSLformvalidate 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."

MESCIUS inc.

comments powered by Disqus