This topic explains how to configure and set the build-in data validation rules.
The following table lists the concept and topics required as a prerequisite to understanding this topic.
This topic contains the following sections:
The XamSpreadsheet data validation feature is based on rules (set on worksheet’s cells) which perform validations on different constraints and allow or deny user input. Each worksheet cell can have only one validation rule set using the cell’s DataValidationRule property.
All data validation rules extends from the base DataValidationRule abstract class and therefor have common configuration parameters which are listed in the table below:
There are several predefined data validation rules supported by the control which you can use to achieve different data validation by just instantiating the appropriate rule, configure its parameters and set it to the worksheet cell’s DataValidationRule property.
The following table explains briefly the built-in data validation rules, their purpose and their capabilities. Further details are available after the table.
This rule does not perform any validation but can be used to provide input message once the cell is activated.
The following table maps the desired configuration to the property that manages it.
The following code snippet demonstrates how to create an AnyValueDataValidationRule, configure its input message and set the rule on a cell in a XamSpreadsheet named "XamSpreadsheet1":
In C#:
AnyValueDataValidationRule rule = new AnyValueDataValidationRule();
rule.InputMessageTitle = "Experience";
rule.InputMessageDescription = "Please list the frameworks your have experience with in this box";
this.XamSpreadsheet1.Workbook.Worksheet[0].Rows[20].Cells[3].DataValidationRule = rule;In Visual Basic:
Dim rule As New AnyValueDataValidationRule()
rule.InputMessageTitle = "Experience"
rule.InputMessageDescription = "Please list the frameworks your have experience with in this box"
Me.XamSpreadsheet1.Workbook.Worksheet(0).Rows(20).Cells(3).DataValidationRule = ruleThis rule performs a validation on the user input against a single constraint value. For example the input value must be:
greater than the number 5
less than or equal to the double 7.35
equal to a time span of 2 days
not equal to the date of April 15, 2015
The following table maps the desired configuration to the property/method that manage it.
The following code snippet demonstrates how to create a OneConstrantDataValidationRule, configure the validation rule to allow values which are greater than 5 and set the rule on a cell in a XamSpreadsheet named "XamSpreadsheet1":
In C#:
OneConstraintDataValidationRule rule = new OneConstraintDataValidationRule(); rule.ValidationOperator = OneConstraintDataValidationOperator.GreaterThan; rule.SetConstraint(5); this.XamSpreadsheet1.Workbook.Worksheets[0].Rows[20].Cells[3].DataValidationRule = rule;
In Visual Basic:
Dim rule As New OneConstraintDataValidationRule() rule.ValidationOperator = OneConstraintDataValidationOperator.GreaterThan rule.SetConstraint(5) Me.XamSpreadsheet1.Workbook.Worksheets(0).Rows(20).Cells(3).DataValidationRule = rule
This rule performs a validation on the user input against two constraint values. For example the input value must be:
with a text length between 10 and 15 characters
not between the dates of June 16, 2015 and July 10, 2015
The following table maps the desired configuration to the property/method that manage it.
The following code snippet demonstrates how to create a TwoConstrantDataValidationRule, configure the validation rule to allow values which text length is between 10 and 15 characters and set the rule on a cell in a XamSpreadsheet named "XamSpreadsheet1":
In C#:
TwoConstraintDataValidationRule rule = new TwoConstraintDataValidationRule(); rule.ValidationOperator = TwoConstraintDataValidationOperator.Between; rule.ValidationCriteria = DataValidationCriteria.TextLength; rule.SetLowerConstraint(10); rule.SetUpperConstraint(15); this.XamSpreadsheet1.Workbook.Worksheets[0].Rows[20].Cells[3].DataValidationRule = rule;
In Visual Basic:
Dim rule As New TwoConstraintDataValidationRule() rule.ValidationOperator = TwoConstraintDataValidationOperator.Between rule.ValidationCriteria = DataValidationCriteria.TextLength rule.SetLowerConstraint(10) rule.SetUpperConstraint(15) Me.XamSpreadsheet1.Workbook.Worksheets(0).Rows(20).Cells(3).DataValidationRule = rule
This rule performs a validation on the user input against a list of accepted values. For example the input value must be one of the numbers: 1, 2, 4, 8, 16, 32, 64 and 128.
The following table maps the desired configuration to the method settings that manage it.
The following code snippet demonstrates how to create a ListDataValidationRule, configure the validation rule to accept the following values: 1, 2, 4, 8, 16, 32, 64 and 128 and set the rule on a cell in a XamSpreadsheet named "XamSpreadsheet1":
In Visual Basic:
Dim rule As New ListDataValidationRule()
rule.SetValues(New Object() {1, 2, 4, 8, 16, 32, 64, 128})
Me.XamSpreadsheet1.Workbook.Worksheets(0).Rows(20).Cells(3).DataValidationRule = rule
In C#:
ListDataValidationRule rule = new ListDataValidationRule();
rule.SetValues(new object[] {1, 2, 4, 8, 16, 32, 64, 128});
this.XamSpreadsheet1.Workbook.Worksheets[0].Rows[20].Cells[3].DataValidationRule = rule;
This rule allows you to set a formula which returns a Boolean result based on which the new cell value is accepted or denied. To accept the new value the formula should evaluate to: True, "True" (case-insensitive), null or a non-zero numeric. To deny the new value the formula should evaluate to: False, 0, any ErrorValue (such as #VALUE!) or any string other than "True" (case-insensitive).
When a relative address is used in the formula, you can set base reference address as second argument. If such is not provided the top-left will be used.
The following table maps the desired configuration to the method that manage it.
The following code snippet demonstrates how to create a CustomDataValidationRule, configure the validation formula rule to accept values when a specific cell in the worksheet (in this case A2) has a value bigger then 2 and set the rule on a cell in a XamSpreadsheet named "XamSpreadsheet1":
In Visual Basic:
Dim rule As New CustomDataValidationRule()
rule.SetFormula("=IF(A2>2,TRUE,FALSE)", Nothing)
Me.XamSpreadsheet1.Workbook.Worksheets(0).Rows(3).Cells(3).DataValidationRule = ruleIn C#:
CustomDataValidationRule rule = new CustomDataValidationRule();
rule.SetFormula("=IF(A2>2,TRUE,FALSE)", null);
this.XamSpreadsheet1.Workbook.Worksheet s [0].Rows[3].Cells[3].DataValidationRule = rule;
The following topic provides additional information related to this topic.