Version

Working with Data Validation (UltraSpreadsheet)

Purpose

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

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.

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

In this topic

This topic contains the following sections:

When Validation Occurs

Summary

The UltraSpreadsheet’s data validation occurs when a user performed cell editing is done. If multiple cells are affected (for example when confirming editing with Ctrl+Enter) only the active cell will be validated.

If you change the value of a cell as a result of other operations (for example selecting cell(s) and pressing Delete or pasting values) no validation will be performed.

Data Validation Event

The control supports a data validation-related event which is raised on cells which have validation rule assigned and when the user confirms a new input value which does not pass the validation. In the event arguments object of the event handler you have access to the edited cell and its validation rule. You can also implement your own logic which determines what action to be taken (accept, revert, stay in edit mode and show prompt) by the control.

Settings

The following table maps the desired task with the property from that manage it.

Task Use this property/event: And

Attach data validation event handler

Attach an event handler to it

Obtain the cell being edited

Read its value

Obtain the validation rule

Read its value

Obtain or set the control’s next action

Read or set its value of type SpreadsheetEditModeValidationErrorEventArgs
Note
Note

You can use the CanStayInEditMode property of the event arguments to check if the cell is currently allowed to stay in editing mode.

Example

The following code snippet demonstrates adding of a data validation event handler. The logic of this handler just checks if the new value can be parsed as integer and accepts the change:

In Visual Basic:

Me.UltraSpreadsheet1.EditModeValidationError += EditModeValidationError_Handler
Public Sub EditModeValidationError_Handler(sender As Object, _
            e As SpreadsheetEditModeValidationErrorEventArgs)
    Dim obj = Me.UltraSpreadsheet1.ActiveWorksheet.Rows(e.Cell.Row).Cells(e.Cell.Column).Value
    Dim number As Integer
    If Integer.TryParse(obj.ToString(), number) Then
        e.Action = SpreadsheetEditModeValidationErrorAction.AcceptChange
    Else
        e.Action = SpreadsheetEditModeValidationErrorAction.StayInEditMode
    End If
End Sub

In C#:

this.UltraSpreadsheet1.EditModeValidationError += UltraSpreadsheet_EditModeValidationError;
void UltraSpreadsheet1_EditModeValidationError(object sender, SpreadsheetEditModeValidationErrorEventArgs e)
       {
           var obj = this.UltraSpreadsheet1.ActiveWorksheet.Rows[e.Cell.Row].Cells[e.Cell.Column].Value;
           int number;
           if (int.TryParse(obj.ToString(), out number))
           {
               e.Action = SpreadsheetEditModeValidationErrorAction.AcceptChange;
           }
           else
           {
               e.Action = SpreadsheetEditModeValidationErrorAction.StayInEditMode;
           }
       }

Invalid Data Circles

Summary

The UltraSpreadsheet control supports a visual notification option which draws a circle around each cell which has a validation rule set and its value does not pass the data validation.

The following screenshot shows an invalid value circle around one of the control’s cells:

xamSpreadsheet val1.png

Example

The following code snippet shows how to invoke the command which shows the invalid data circles:

In C#:

private void ultraButton1_Click(object sender, EventArgs e)
 {
   ultraSpreadsheet1.PerformAction(UltraSpreadsheetAction.CircleInvalidData);
 }

In VB:

Private Sub ultraButton1_Click(sender As Object, e As EventArgs)
 ultraSpreadsheet1.PerformAction(UltraSpreadsheetAction.CircleInvalidData)
End Sub

The following code snippet shows how to invoke the command which hides the invalid data circles:

C#

private void ultraButton1_Click(object sender, EventArgs e)
 {
   ultraSpreadsheet1.PerformAction(UltraSpreadsheetAction.ClearValidationCircles);
 }

In VB:

Private Sub ultraButton1_Click(sender As Object, e As EventArgs)
 ultraSpreadsheet1.PerformAction(UltraSpreadsheetAction.ClearValidationCircles)
End Sub

Related Content

The following topics provide additional information related to this topic.

Topic Purpose

This topic explains the activation feature from developer perspective.

This topic explains how to set or obtain active selection’s formatting.