You can validate the contents of the cell in a number of ways. Some validation is performed by the Spread component automatically, based on the type of cell. Beyond this, to validate the input from a user, you can look for an event and run some validation routine based on the occurrence of that event. Another simple way to check whether the user enters data that is valid based on the cell type is by using the IsValid method, which is available in all the cell type classes. Each of these is discussed in turn:
- Cell Type Validation
- Event-based Validation
- IsValid Method Validation
Cell Type Validation
The cell types validate user input and verify that it fits the requirements of the cell type's format and settings. At run time, the component checks data either when it is entered by the user or code, when the component loses the focus, or both. The component validates data as it is provided, for example, as the user types the data, and when the component loses focus. User's can enter data by typing or pasting; data from code can come from property settings or a database. In general, all these methods of entering data are handled in the same way by the component, which checks the data to determine if it is valid.
Values in the component that are less than the setting of the value (MinimumValue or MinimumDate, or MinimumTime property) are allowed in the component. Components must allow values less than the minimum to let users provide a partial value that is later changed to a value greater than the minimum value. For example, if the MinimumValue property is set to 100, and the user tries to change the value to 124 by selecting the existing value and typing, as the user types the value changes to "1", which is less than the allowed minimum value. However, as the user types the value becomes "12" and then "124". The final value is above the allowed minimum value. The value provided by the user is checked to see if it is less than the minimum value when the control loses the focus.
When the control is validating data as it comes into the component, if the user tries to provide invalid data, or invalid data is coming from code or a database, the UserError event occurs.
Each cell type has some default restrictions to determine what is valid data. For example, the currency cell type regards a text string of characters such as "abcd" as an invalid value because it expects numeric data. In addition, you can set properties for each cell type that specify valid data settings.
The following table lists the default data allowed in each of the editable cell types.
|
Cell Type |
Default |
Valid Data Examples |
|---|---|---|
|
Currency |
Numeric data, which can include characters for the currency symbol, a separator, and a decimal symbol |
$3.45 $1,234.56 £45 |
|
DateTime |
Date and time data, which can include separator characters |
8/16/2002, Monday, August 05, 2002 4:40 PM |
|
Number |
Numeric data, which can include characters for a separator and a decimal symbol |
3.45 1,234.56 |
|
Mask |
Any character is accepted that fits the mask string criteria |
|
|
Percent |
Numeric data, which can include characters for the percent symbol, a separator, and a decimal symbol |
0.5 1,234% |
|
Text |
Any character is accepted. |
|
|
Hyperlink |
Any character is accepted. |
|
|
General |
Any character is accepted. |
For more information about differences between these cell types, refer to the Customizing Interaction with Cell Types.
The following table lists the additional properties you can set for each cell type that specify valid data settings.
| Cell Type | Cell Type Properties for Defining Valid Data |
|---|---|
| Currency | MaximumValue, MinimumValue |
| DateTime | MaximumDate, MinimumDate, MaximumTime, MinimumTime |
| Number | MaximumDate, MinimumDate, MaximumTime, MinimumTime |
| Mask | Mask, MaskChar |
| Percent | MaximumValue, MinimumValue |
| Text | MaxLength |
The following table lists how invalid data is handled with the number and text cell types. The Column and Cell headings in the table refer to entering the data at the column or cell level.
| Action |
Text Cell |
Number Cell |
||
|---|---|---|---|---|
|
Column |
Cell |
Column |
Cell |
|
|
Input invalid cell text while cell is in edit mode |
+* |
+* |
+* |
+* |
|
Paste invalid cell text while cell is in edit mode |
#* |
#* |
+* |
+* |
|
Paste invalid cell text while cell is not in editmode |
#* |
#* |
+* |
+* |
|
Paste copied cell with cell type and invalid value while not in edit mode |
##** |
##** |
##** |
##** |
|
Paste copied cell with invalid value and cell type not set while cell is not in edit mode |
#* |
#* |
+* |
+* |
|
Input invalid value with cell Value property (or ISheetDataModel SetValue method) |
++** |
++** |
++** |
++** |
|
Input invalid string value with cell Text property (or SheetView SetText method) |
#** |
#** |
+** |
+** |
|
Input invalid cell value with SheetView SetValue method (validate = false) |
++** |
++** |
++** |
++** |
|
Input invalid cell value with SheetView SetValue method (validate = true) |
+** |
+** |
+** |
+** |
|
Set invalid cell type after binding |
++** |
++** |
++** |
++** |
|
Use the ClipboardPasteValues field to paste invalid cell value while cell is not in edit mode |
#* |
#* |
+* |
+* |
The following list defines the conditions in the above table:
- + Reject the value
- ++ Allow (paint) the existing invalid value and make the value valid (truncate the value or change it to be within the minimum/maximum setting) when editing
- # Truncate the value
- ## Paste the value and cell type
- * Fire the Error/EditError events
- ** Do not fire the Error/EditError events
Event-based Validation
You can check for an event and run some validation routine based on the occurrence of that event. For instance, the Changed event in the SheetView class notifies your application that the user has left edit mode and the contents of the cell has changed. For more thorough validation, to handle the case where a user pastes a value from the Clipboard as opposed to typing in a value, use the Changed event on the data model (DefaultSheetDataModel class). This is a good way to evaluate the contents of a cell after it has been edited, and throw an error message or revert to original value if the data in the cell is not valid. For more information about using events, refer to Managing Events from User Actions.
IsValid Method Validation
The IsValid method for the cell type classes checks whether a value is valid for the cell editor. Spread uses that method internally to check values coming out of the cell editor to ensure that they are valid. In most cases, it will return true if the Format method is able to format the specified non-string value into a string to display in the editor, or whether the Parse method is able to parse the specified string value into a value of the appropriate type for the cell.
For advanced users, you can Evaluate the contents of a cell after it has been edited, and throw an error message and revert to the original value if the data in the cell is not valid. To do this requires handling the EditModeOn event and setting properties in SuperEditBase and thus GeneralEditor on the editor control each time edit mode is turned on. These properties include InvalidOption, InvalidColor, CanValidate, and UserEntry but are not recommended to be used without some knowledge of the workings of Spread. At the time the EditModeOff event is raised, the value in the data model has already been changed. Handle EditModeOn and store the cell's value and then in the EditModeOff event if the current value of the cell fails your validation then reset the value to the stored value.
Return to the overall list of tasks in Managing Data on a Sheet.