Spread for ASP.NET 13 Product Documentation
Spread for ASP.NET 13 Product Documentation / Developer's Guide / Customizing with Cell Types / Using Validation Controls
In This Topic
    Using Validation Controls
    In This Topic

    You can prevent a user from entering invalid characters in a cell by using a validation control in the Spread cell. You can validate the data when pasting to a cell or cell range by setting the NonEditModeValidation property to True when using a validation control. Set the AllowServerValidators property to True to support server validation using custom validator controls. You can also display an error message when the data is invalid with the ValidationErrorMessage property.

    Initialize the validation control and then add the validation control to the Spread cell to provide validation.

    You can use validation controls instead of the standard validation by setting the Validators property. The following cell types provide this option:

    If the validation fails, the onErrorMessageShown event occurs and the user cannot change the active cell.

    The validation is not supported if the ShowEditor property is true.

    If you receive an error such as, "UnobtrusiveValidationMode requires a ScriptResourceMapping for 'jquery'" in Visual Studio 2015, then you may need to add the following information to web.config.

    <appSettings>
    <add key="ValidationSettings:UnobtrusiveValidationMode" value="None"/>
    </appSettings>

    Using Code

    1. Add the validator code to the ASPX page.
    2. Create the cell.
    3. Create a validator.
    4. Create validation messages.
    5. Set the Validators property.

    Example

    This example creates a cell and assigns a basic validator to the cell.

    Code
    Copy Code

    <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
      <asp:CompareValidator ID="CompareValidator1" runat="server" ErrorMessage="CompareValidator"></asp:CompareValidator>
      <asp:RangeValidator ID="RangeValidator1" runat="server" ErrorMessage="RangeValidator"></asp:RangeValidator>

    C#
    Copy Code

    protected void Page_Load(object sender, System.EventArgs e)
    {
    if (IsPostBack) return;
    FpSpread1.Sheets[0].RowCount = 10;
    FpSpread1.Sheets[0].ColumnCount = 7;
    // RequiredFieldValidator, from code
    FarPoint.Web.Spread.TextCellType txt1 = new FarPoint.Web.Spread.TextCellType();
    RequiredFieldValidator rfv = new RequiredFieldValidator();
    rfv.ErrorMessage = "RequiredFieldValidator, from code: value required!";
    txt1.Validators.Add(rfv);
    FpSpread1.ActiveSheetView.Cells[0, 0].Text = "RequiredFieldValidator, from code";
    FpSpread1.ActiveSheetView.Cells[0, 1].CellType = txt1;

    // RequiredFieldValidator, from toolbox
    FarPoint.Web.Spread.TextCellType txt2 = new FarPoint.Web.Spread.TextCellType();
    RequiredFieldValidator1.ErrorMessage = "RequiredFieldValidator, from toolbox: value required!";
    txt2.Validators.Add(RequiredFieldValidator1);
    FpSpread1.ActiveSheetView.Cells[1, 0].Text = "RequiredFieldValidator, from toolbox";
    FpSpread1.ActiveSheetView.Cells[1, 1].CellType = txt2;

    // CompareValidator, from toolbox
    FarPoint.Web.Spread.TextCellType txt3 = new FarPoint.Web.Spread.TextCellType();
    CompareValidator1.ErrorMessage = "CompareValidator, from toolbox: password does not match! Enter \"Spread\"";
    CompareValidator1.ValueToCompare = "Spread";
    txt3.Validators.Add(CompareValidator1);
    FpSpread1.ActiveSheetView.Cells[2, 0].Text = "CompareValidator, from toolbox";
    FpSpread1.ActiveSheetView.Cells[2, 1].CellType = txt3;

    // CompareValidator, from code
    FarPoint.Web.Spread.TextCellType txt4 = new FarPoint.Web.Spread.TextCellType();
    CompareValidator cv = new CompareValidator();
    cv.ErrorMessage = "CompareValidator, from toolbox: password does not match! Enter \"Spread\"";
    cv.ValueToCompare = "Spread";
    txt4.Validators.Add(cv);
    FpSpread1.ActiveSheetView.Cells[3, 0].Text = "CompareValidator, from code";
    FpSpread1.ActiveSheetView.Cells[3, 1].CellType = txt4;

    // RangeValidator, from toolbox
    FarPoint.Web.Spread.TextCellType txt5 = new FarPoint.Web.Spread.TextCellType();
    RangeValidator1.ErrorMessage = "RangeValidator, from toolbox: value should in range [10 - 15]";
    RangeValidator1.MinimumValue = "10"; RangeValidator1.MaximumValue = "15";
    RangeValidator1.Type = ValidationDataType.Integer;
    txt5.Validators.Add(RangeValidator1);
    FpSpread1.ActiveSheetView.Cells[4, 0].Text = "RangeValidator, from toolbox";
    FpSpread1.ActiveSheetView.Cells[4, 1].CellType = txt5;

    // RangeValidator, from code
    FarPoint.Web.Spread.TextCellType txt6 = new FarPoint.Web.Spread.TextCellType();
    RangeValidator rv = new RangeValidator();
    rv.ErrorMessage = "RangeValidator, from toolbox: value should in range [10 - 15]";
    rv.MinimumValue = "10"; rv.MaximumValue = "15";
    rv.Type = ValidationDataType.Integer;
    txt6.Validators.Add(rv);
    FpSpread1.ActiveSheetView.Cells[5, 0].Text = "RangeValidator, from toolbox";
    FpSpread1.ActiveSheetView.Cells[5, 1].CellType = txt6;
    }

    VB
    Copy Code

    Protected Sub Page_Load(sender As Object, e As System.EventArgs)
    If IsPostBack Then
     Return
    End If
    FpSpread1.Sheets(0).RowCount = 10
    FpSpread1.Sheets(0).ColumnCount = 7
    ' RequiredFieldValidator, from code
    Dim txt1 As New FarPoint.Web.Spread.TextCellType()
    Dim rfv As New RequiredFieldValidator()
    rfv.ErrorMessage = "RequiredFieldValidator, from code: value required!"
    txt1.Validators.Add(rfv)
    FpSpread1.ActiveSheetView.Cells(0, 0).Text = "RequiredFieldValidator, from code"
    FpSpread1.ActiveSheetView.Cells(0, 1).CellType = txt1

    ' RequiredFieldValidator, from toolbox
    Dim txt2 As New FarPoint.Web.Spread.TextCellType()
    RequiredFieldValidator1.ErrorMessage = "RequiredFieldValidator, from toolbox: value required!"
    txt2.Validators.Add(RequiredFieldValidator1)
    FpSpread1.ActiveSheetView.Cells(1, 0).Text = "RequiredFieldValidator, from toolbox"
    FpSpread1.ActiveSheetView.Cells(1, 1).CellType = txt2

    ' CompareValidator, from toolbox
    Dim txt3 As New FarPoint.Web.Spread.TextCellType()
    CompareValidator1.ErrorMessage = "CompareValidator, from toolbox: password does not match! Enter ""Spread"""
    CompareValidator1.ValueToCompare = "Spread"
    txt3.Validators.Add(CompareValidator1)
    FpSpread1.ActiveSheetView.Cells(2, 0).Text = "CompareValidator, from toolbox"
    FpSpread1.ActiveSheetView.Cells(2, 1).CellType = txt3

    ' CompareValidator, from code
    Dim txt4 As New FarPoint.Web.Spread.TextCellType()
    Dim cv As New CompareValidator()
    cv.ErrorMessage = "CompareValidator, from toolbox: password does not match! Enter ""Spread"""
    cv.ValueToCompare = "Spread"
    txt4.Validators.Add(cv)
    FpSpread1.ActiveSheetView.Cells(3, 0).Text = "CompareValidator, from code"
    FpSpread1.ActiveSheetView.Cells(3, 1).CellType = txt4

    ' RangeValidator, from toolbox
    Dim txt5 As New FarPoint.Web.Spread.TextCellType()
    RangeValidator1.ErrorMessage = "RangeValidator, from toolbox: value should in range [10 - 15]"
    RangeValidator1.MinimumValue = "10"
    RangeValidator1.MaximumValue = "15"
    RangeValidator1.Type = ValidationDataType.[Integer]
    txt5.Validators.Add(RangeValidator1)
    FpSpread1.ActiveSheetView.Cells(4, 0).Text = "RangeValidator, from toolbox"
    FpSpread1.ActiveSheetView.Cells(4, 1).CellType = txt5

    ' RangeValidator, from code
    Dim txt6 As New FarPoint.Web.Spread.TextCellType()
    Dim rv As New RangeValidator()
    rv.ErrorMessage = "RangeValidator, from toolbox: value should in range [10 - 15]"
    rv.MinimumValue = "10"
    rv.MaximumValue = "15"
    rv.Type = ValidationDataType.[Integer]
    txt6.Validators.Add(rv)
    FpSpread1.ActiveSheetView.Cells(5, 0).Text = "RangeValidator, from toolbox"
    FpSpread1.ActiveSheetView.Cells(5, 1).CellType = txt6
    End Sub

    This example supports server validation using a custom validator control.

    Code
    Copy Code
    <FarPoint:FpSpread ID="FpSpread1" runat="server" BorderColor="Black" BorderStyle="Solid" BorderWidth="1px" Height="200" Width="400">
       <commandbar backcolor="Control" buttonfacecolor="Control" buttonhighlightcolor="ControlLightLight" buttonshadowcolor="ControlDark"></commandbar>
       <sheets>
          <FarPoint:SheetView SheetName="Sheet1"></FarPoint:SheetView>
       </sheets>
    </FarPoint:FpSpread>
          
    <asp:CustomValidator ID="CustomValidator2" runat="server" ErrorMessage="Error of server side" OnServerValidate="CustomValidator2_ServerValidate"></asp:CustomValidator>
    
    C#
    Copy Code
    protected void Page_Load(object sender, EventArgs e)
    {
      if (IsPostBack) return;
      TextCellType txt = new FarPoint.Web.Spread.TextCellType();
      txt.AllowServerValidators = true;// New property to enable server validating with validator controls
      txt.Validators.Add(CustomValidator2);
      FpSpread1.ActiveSheetView.Cells[1, 1].CellType = txt;
      FpSpread1.ActiveSheetView.Cells[1, 1].BackColor = Color.LightPink;
    }
    protected void CustomValidator2_ServerValidate(object source, ServerValidateEventArgs args)
    {
    int value = 0;
    args.IsValid = int.TryParse(args.Value, out value) && value < 10;// Accept integer number less than 10;
    }
    
    VB
    Copy Code
    Protected Sub Page_Load(sender As Object, e As System.EventArgs)
            If IsPostBack Then
             Return
                   
    Dim txt As New FarPoint.Web.Spread.TextCellType()
      txt.AllowServerValidators = True 'New property to enable server validating with validator controls
      txt.Validators.Add(CustomValidator2)
      FpSpread1.ActiveSheetView.Cells(1, 1).CellType = txt
      FpSpread1.ActiveSheetView.Cells(1, 1).BackColor = Color.LightPink
    End Sub
    Protected Sub CustomValidator2_ServerValidate(source As Object, args As ServerValidateEventArgs)
     Dim value As Integer = 0
     args.IsValid = Integer.TryParse(args.Value, value) AndAlso value < 10  'Accept integer number less than 10
    End Sub
    
    See Also