Version

Configuring Data Validation (UltraSpreadsheet)

Purpose

This topic explains how to configure and set the build-in data validation rules.

Required background

The following table lists the concept and topics required as a prerequisite to understanding this topic.

Type Content

Concept

Infragistics Excel Engine
Topic Purpose

This topic explains in details the features of the UltraSpreadsheet control from developer perspective.

This topic explains what actions can be performed by the user in relation with the data validation feature.

In this topic

This topic contains the following sections:

Introduction

Summary

The UltraSpreadsheet 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.

Note
Note

You can apply a given rule to one or more cells using the DataValidationRules collection of the Worksheet.

Common Data Validation Rules Parameters

Details

All data validation rules extends from the base DataValidationRule abstract class and therefor have common configuration parameters which are listed in the table below:

Parameter Details Property

Input message description

This property sets the input message description.

Input message title

This property sets the optional input message title.

Input message visibility

By default the input message will be shown once you set the InputMessageDescription property, but you can hide it by setting this property to false.

Note
Note

You can set the position of the input message associated with the active cell using the ValidationInputMessagePosition property.

Error message description

This property sets optional error message description. If you don’t specify a value, the default description will state: "The value you entered is not valid. A user has restricted values that can be entered into this cell."

Error message title

This property sets the optional error message title. If you don’t specify a value, the application title will be used.

Error message visibility

By default the error message will be shown, but you can hide it by setting this property to false.

Error message type

You can specify three types of error messages by setting this property to one of the values of the DataValidationErrorStyle enumeration.

Note
Note

For more information regarding the differences between the error messages types please see the Data Validation Interactions topic.

Built-in Data Validation Rules

Summary

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.

Details

The following table explains briefly the built-in data validation rules, their purpose and their capabilities. Further details are available after the table.

Validation against Details Rule Type

This rule does not perform any validation but can be used to provide input message once the cell is activated.

This 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

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

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.

This rule performs a validation on the user input against a formula.

No Validation

Overview

This rule does not perform any validation but can be used to provide input message once the cell is activated.

Property settings

The following table maps the desired configuration to the property that manages it.

In order to: Use this property: And set it to:

Set input message title

InputMessageTitle

A value of type string

Set input message description

InputMessageDescription

A value of type string

Example

The following code snippet demonstrates how to create an AnyValueDataValidationRule, configure its input message and set the rule on a cell in a UltraSpreadsheet named "UltraSpreadsheet1":

In C#:

AnyValueDataValidationRule rule = new AnyValueDataValidationRule();
rule.InputMessageTitle = "Experience";
rule.InputMessageDescription = "Please list the frameworks your have experience with in this box";
this.UltraSpreadsheet1.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.UltraSpreadsheet1.Workbook.Worksheet(0).Rows(20).Cells(3).DataValidationRule = rule

Single Constraint Value or Formula

Overview

This 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

Property settings

The following table maps the desired configuration to the property/method that manage it.

In order to: Use this property/method: And set it to:

Set the validation operator (equal, less than…)

Set the validation constraint

Using the overloads of this method you can set the constraint of type double, DataTime or TimeSpan.

Example

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 UltraSpreadsheet named "UltraSpreadsheet1":

In C#:

OneConstraintDataValidationRule rule = new OneConstraintDataValidationRule();
rule.ValidationOperator = OneConstraintDataValidationOperator.GreaterThan;
rule.SetConstraint(5);
this.UltraSpreadsheet1.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.UltraSpreadsheet1.Workbook.Worksheets(0).Rows(20).Cells(3).DataValidationRule = rule

Two Constraint Values or Formulas

Overview

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

Property settings

The following table maps the desired configuration to the property/method that manage it.

In order to: Use this property/method: And set it to:

Set the validation operator (between, not between)

Set the lower validation constraint

Using the overloads of this method you can set the lower constraint of type double, DataTime or TimeSpan.

Set the upper validation constraint

Using the overloads of this method you can set the upper constraint of type double, DataTime or TimeSpan.

Example

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 UltraSpreadsheet named "UltraSpreadsheet1":

In C#:

TwoConstraintDataValidationRule rule = new TwoConstraintDataValidationRule();
rule.ValidationOperator = TwoConstraintDataValidationOperator.Between;
rule.ValidationCriteria = DataValidationCriteria.TextLength;
rule.SetLowerConstraint(10);
rule.SetUpperConstraint(15);
this.UltraSpreadsheet1.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.UltraSpreadsheet1.Workbook.Worksheets(0).Rows(20).Cells(3).DataValidationRule = rule

List of Accepted Values

Overview

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.

Property settings

The following table maps the desired configuration to the method settings that manage it.

In order to: Use this method: And provide:

Set the list of accepted values

An array of accepted object or the accepted objects are arguments

Example

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 UltraSpreadsheet named "UltraSpreadsheet1":

In Visual Basic:

Dim rule As New ListDataValidationRule()
rule.SetValues(New Object() {1, 2, 4, 8, 16, 32, 64, 128})
Me.UltraSpreadsheet1.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.UltraSpreadsheet1.Workbook.Worksheets[0].Rows[20].Cells[3].DataValidationRule = rule;

Custom Formula

Overview

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.

Property settings

The following table maps the desired configuration to the method that manage it.

In order to: Use this method: And provide arguments:

Set the formula

  • The formula

  • Base address used for reference when relative references were used in the formula

Example

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 UltraSpreadsheet named "UltraSpreadsheet1":

In Visual Basic:

Dim rule As New CustomDataValidationRule()
rule.SetFormula("=IF(A2>2,TRUE,FALSE)", Nothing)
Me.UltraSpreadsheet1.Workbook.Worksheets(0).Rows(3).Cells(3).DataValidationRule = rule

In C#:

CustomDataValidationRule rule = new CustomDataValidationRule();
rule.SetFormula("=IF(A2>2,TRUE,FALSE)", null);
this.UltraSpreadsheet1.Workbook.Worksheet s [0].Rows[3].Cells[3].DataValidationRule = rule;

Related Content

Topic

The following topic provides additional information related to this topic.

Topic Purpose

This topic explains how to handle the data validation event and how to show circles around the cells with invalid data.