Using Data Validation

You can 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.

The default invalid image is a red ellipse.

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

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.

Using Code

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

CS	Copy Code
gcSpreadSheet1.HighlightInvalidData = true; var valid = GrapeCity.Xaml.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.Xaml.SpreadSheet.Data.DataValidator.CreateNumberValidator(GrapeCity.Xaml.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.Xaml.SpreadSheet.Data.DataValidator.CreateTextLengthValidator(GrapeCity.Xaml.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.";

Copy Code

gcSpreadSheet1.HighlightInvalidData = true;
var valid = GrapeCity.Xaml.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.Xaml.SpreadSheet.Data.DataValidator.CreateNumberValidator(GrapeCity.Xaml.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.Xaml.SpreadSheet.Data.DataValidator.CreateTextLengthValidator(GrapeCity.Xaml.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	Copy Code
GcSpreadSheet1.HighlightInvalidData = True Dim valid = GrapeCity.Xaml.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.Xaml.SpreadSheet.Data.DataValidator.CreateNumberValidator(GrapeCity.Xaml.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.Xaml.SpreadSheet.Data.DataValidator.CreateTextLengthValidator(GrapeCity.Xaml.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."

Copy Code

GcSpreadSheet1.HighlightInvalidData = True
Dim valid = GrapeCity.Xaml.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.Xaml.SpreadSheet.Data.DataValidator.CreateNumberValidator(GrapeCity.Xaml.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.Xaml.SpreadSheet.Data.DataValidator.CreateTextLengthValidator(GrapeCity.Xaml.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."

Using Code

The following code customizes the invalid data image.

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

CS	Copy Code
gcSpreadSheet1.Sheets[0].Cells[0, 2].Value = 5; gcSpreadSheet1.Sheets[0].Cells[1, 2].Value = 4; gcSpreadSheet1.Sheets[0].Cells[2, 2].Value = 5; gcSpreadSheet1.HighlightInvalidData = true; var valid = GrapeCity.Xaml.SpreadSheet.Data.DataValidator.CreateFormulaListValidator("$C$1:$C$3"); gcSpreadSheet1.Sheets[0].Cells[0, 1].DataValidator = valid; gcSpreadSheet1.Sheets[0].Cells[0, 0].Text = "Formulas";

Copy Code

gcSpreadSheet1.Sheets[0].Cells[0, 2].Value = 5;
gcSpreadSheet1.Sheets[0].Cells[1, 2].Value = 4;
gcSpreadSheet1.Sheets[0].Cells[2, 2].Value = 5;
gcSpreadSheet1.HighlightInvalidData = true;
var valid = GrapeCity.Xaml.SpreadSheet.Data.DataValidator.CreateFormulaListValidator("$C$1:$C$3");
gcSpreadSheet1.Sheets[0].Cells[0, 1].DataValidator = valid;
gcSpreadSheet1.Sheets[0].Cells[0, 0].Text = "Formulas";

VB	Copy Code
GcSpreadSheet1.Sheets(0).Cells(0, 2).Value = 5 GcSpreadSheet1.Sheets(0).Cells(1, 2).Value = 4 GcSpreadSheet1.Sheets(0).Cells(2, 2).Value = 5 GcSpreadSheet1.HighlightInvalidData = True Dim valid = GrapeCity.Xaml.SpreadSheet.Data.DataValidator.CreateFormulaListValidator("$C$1:$C$3") GcSpreadSheet1.Sheets(0).Cells(0, 1).DataValidator = valid GcSpreadSheet1.Sheets(0).Cells(0, 0).Text = "Formulas"