Introduction

Aspose.Grid.Desktop also supports adding validations (or validation rules) to the cells of a worksheet. By applying validation rules to cells, developers can restrict users to enter data into Grid in a specific format. Different modes of validations are supported by Aspose.Grid.Desktop. In this topic, we will not only discuss about those validation modes but also explain the manipulation of these validations.

 

Validation Modes

There are three validation modes supported by Aspose.Grid.Desktop as follows:

• Is Required Validation Mode
• Regular Expressions Validation Mode
• Custom Validation Mode

 

Is Required Validation Mode

In this validation mode, users are restricted to enter values into specified cells. Once Is Required Validation is applied on a worksheet cell, it becomes must for a user to enter value into that cell.

 

Regular Expressions Validation Mode

In this mode, restrictions are applied on worksheet cells for the users to submit data into cells in a specific format. The pattern of data format is provided in the form of a Regular Expression.

 

Custom Validation Mode

To use Custom Validation, It is must for developers to implement Aspose.Grid.Desktop.ICustomValidation interface. The interface provides a Validate method. This method returns true if data is valid otherwise returns false.

Adding Validation

 

To add any kind of validation to a worksheet cell, please follow the steps below:

• Add Aspose.Grid.Desktop control to your Form
• Access any desired Worksheet
• Add a desired validation to the Validations collection of the Worksheet to specify that which validation would be applied on which cell

Example:

[C#]

 

 

//Accessing first worksheet of the Grid

Worksheet sheet = gridDesktop1.Worksheets[0];

 

 

//Adding values to specific cells of the worksheet

sheet.Cells["a2"].Value="Required";

sheet.Cells["a4"].Value="100";

sheet.Cells["a6"].Value="2006-07-21";

sheet.Cells["a8"].Value="101.2";

 

 

//Adding Is Required Validation to a cell

sheet.Validations.Add("a2", true, "");

 

 

//Adding simple Regular Expression Validation to a cell

sheet.Validations.Add("a4", true, @"\d+");

 

 

//Adding complex Regular Expression Validation to a cell

sheet.Validations.Add("a6", true, @"\d{4}-\d{2}-\d{2}");

 

 

//Adding Custom Validation to a cell

sheet.Validations.Add("a8", new CustomValidation());

 

 

[VB.NET]

 

 

'Accessing first worksheet of the Grid

Dim sheet As Worksheet = gridDesktop1.Worksheets(0)

 

 

'Adding values to specific cells of the worksheet

sheet.Cells("a2").Value="Required"

sheet.Cells("a4").Value="100"

sheet.Cells("a6").Value="2006-07-21"

sheet.Cells("a8").Value="101.2"

 

 

'Adding Is Required Validation to a cell

sheet.Validations.Add("a2", True, "")

 

 

'Adding simple Regular Expression Validation to a cell

sheet.Validations.Add("a4", True, "\d+")

 

 

'Adding complex Regular Expression Validation to a cell

sheet.Validations.Add("a6", True, "\d{4}-\d{2}-\d{2}")

 

 

'Adding Custom Validation to a cell

sheet.Validations.Add("a8", New CustomValidation)

 

The output of above code snippet is displayed in the following figure:

 

 

Figure: Different kinds of Validations applied on cells

 

NOTE: In the above figure, we have also mentioned the validation rules in front of cells where these validation rules are applied. If any invalid value (that is not valid according to the validation rule defined for that cell) is entered, a MessageBox would appear to notify user about the invalid entry.

 

Implementing ICustomValidation

In the above code snippet, we have added a custom validation in a8 cell but we have not implemented that custom validation yet. As we have explained in the beginning of this topic that to apply custom validation, we have to implement ICustomValidation interface. So, let's try creating a class to implement ICustomValidation interface.

In the code snippet given below, we have implemented a custom validation to perform the following checks:

• Check if the cell's address is accurate in which the validation is added
• Check if the data type of the cell's value is double
• Check if the value of the cell is greater than 100

Example:

[C#]

 

 

//Implementing ICustomValidation interface

public class CustomValidation : Aspose.Grid.Desktop.ICustomValidation

{

 

 

   //Implementing Validate method already defined in the interface

   public bool Validate(Worksheet worksheet, int row, int col, object value)

   {

     //Checking the cell's address

     if (row == 7 && col == 0)

     {

 

 

        //Checking the data type of cell's value

         double d = 0;

          try

          {

            d = (double)value;

          }

          catch

          {

            return false;

          }

 

 

        //Checking if the cell's value is greater than 100

          if (d > 100)

                return true;

     }

     return false;

  } 

}

 

 

[VB.NET]

 

 

'Implementing ICustomValidation interface

Public Class CustomValidation

     Implements Aspose.Grid.Desktop.ICustomValidation

 

 

   'Implementing Validate method already defined in the interface

   Public Function Validate(ByVal worksheet As Aspose.Grid.Desktop.Worksheet, ByVal row As Integer,

        ByVal col As Integer, ByVal value As Object) As Boolean

        Implements Aspose.Grid.Desktop.ICustomValidation.Validate

 

 

         'Checking the cell's address

          If row = 7 And col = 0 Then

 

 

             'Checking the data type of cell's value

              Dim d As Double = 0

              Try

                  d = CType(value, Double)

              Catch

                  Return False

              End Try

 

 

             'Checking if the cell's value is greater than 100

              If d > 100 Then

                  Return True

              End If

          End If

          Return False

   End Function

 

 

End Class

 

Accessing Validation

Once a validation is added to a specific worksheet cell, it may be required by developers to access and modify the the attributes of a specific validation at runtime. So, Aspose.Grid.Desktop has made it simple for developers to accomplish this task.

To access a specific validation, please follow the steps below:

• Access a desired Worksheet
• Access a specific Validation in the worksheet by specifying the cell name on which the validation was applied
• Edit Validation attributes, if desired

NOTE: Validations collection has two indexers. One indexer (that is used in the exmaple below) allows to access a Validation object by taking a cell name as its index while the other indexer takes two parameters (that is row and column numbers) to perform the same task.

Example:

[C#]

 

 

//Accessing first worksheet of the Grid

Worksheet sheet = gridDesktop1.Worksheets[0];

 

 

//Accessing the Validation object applied on "a8" cell

Validation validation = sheet.Validations["a8"];

 

 

//Editing the attributes of Validation

validation.IsRequired = true;

validation.RegEx = "";

validation.CustomValidation = null;

 

 

[VB.NET]

 

 

'Accessing first worksheet of the Grid

Dim sheet As Worksheet = gridDesktop1.Worksheets(0)

 

 

'Accessing the Validation object applied on "a8" cell

Dim validation As validation = sheet.Validations("a8")

 

 

'Editing the attributes of Validation

validation.IsRequired = True

validation.RegEx = ""

validation.CustomValidation = Nothing

 

Removing Validation

To remove a specific validation from the worksheet, please follow the steps below:

• Access a desired Worksheet
• Remove a specific Validation from the Worksheet by specifying the cell name on which the validation was applied

Example:

[C#]

 

 

//Accessing first worksheet of the Grid

Worksheet sheet = gridDesktop1.Worksheets[0];

 

 

//Removing the Validation object applied on "a6" cell

sheet.Validations.Remove("a6");

 

 

[VB.NET]

 

 

'Accessing first worksheet of the Grid

Dim sheet As Worksheet = gridDesktop1.Worksheets(0)

 

 

'Removing the Validation object applied on "a6" cell

sheet.Validations.Remove("a6")