Custom cell type and formula validation

Posted by: makis1970 on 8 September 2017, 12:38 pm EST

  • Posted 8 September 2017, 12:38 pm EST

    Hello,


    I want to implement a custom cell type that only accepts boolean values but the question I am going to pose is irrelevant to the value type that the cell type accepts. I inherit my custom cell type class from the GeneralCellType class. If I want to validate user input, I override the IsValid method and act accordingly. The problem occurs if I have enabled user formulas on the spread control. In that case, if the user enters a formula in a cell whose cell type is my custom cell type, the IsValid method receives as (string) argument the formula that was entered. So, how can I evaluate the formula entered and check if the result is of the type I want?


    Thanks in advance.

  • Replied 8 September 2017, 12:38 pm EST

    Hello,


    My recomendation will work for the Spread for Windows Forms product as well. If you use a cutom function for your evalutaions instead of normal built in functions, then you have full control over the calculation and you can check items in the SheetView that are not passed in to a normal calculation. Since this custom function will call the Evaluate method any time any of its dependent cells are changed, you can constantly check for validation before returning a non CalcError in the CustomFunction class.


    I suggested sending the SheetView object to the custom class as a parameter, so you have access to all the property settings in the SheetView for you to do an accurate validation.

  • Replied 8 September 2017, 12:38 pm EST

    Hello,


    The only way to do this is to create a CustomFunction that takes an instance of the SheetView object. Then, in the Evaluate method you can query the SheetView for what cells are set up as what and either return a result of CalcError depending on what you want. This Evaluate method will be called everytime the Formula is recalculated.

  • Replied 8 September 2017, 12:38 pm EST

    bobbyo, thank you for your quick reply.


    Since there is no way to validate a user formula in the CellType context, I thought of an alternative behavior. Let's say that the user enters a formula; spread control does its normal processing, parsing and checking the formula for errors and, if all goes well, evaluates it. If I had somehow access to the evaluation step knowing sheet, row, column, I could check the type of the result against the type that the corresponding cell accepts as dictated by its (custom) cell type and, if they didn't match, I could assign a CalcError as the value. I don't know if something like that is possible (through the data model maybe?), so any suggestions are welcome. I have tried to implement the above functionality by handling the Change event of the spread class; the problem in this case is that if the user changes the data in a cell that is used in the formula, Change event won't be fired for the cell containing the formula.


    Thanks in advance.

  • Replied 8 September 2017, 12:38 pm EST

    prosvasis,


    There is no way to do what you want to do.  The methods in the cell type class (e.g. IsValid) are not passed a formula context (i.e. sheet, row, column) needed to parse and/or evaluate a formula.  Even if you could parse and/or evaluate a formula, you would still be out of luck.  Formulas are not strongly typed.  It is possible to create formulas that evaluate to one data type under one condition and to another data type under another condition.  For example, the formula IF(A1<20,123,IF(A1<80,TRUE,"abc")) could return a Double, a Boolean, or a String depending on the value in cell A1.

  • Replied 8 September 2017, 12:38 pm EST

    Hi,


    I was kind of hoping to be able to implement the above functionality without having to resort to custom functions. If I understand correctly what you recommend, if a user wants to enter a formula for a type-aware cell, he must use a custom function and pass the original formula as an argument.


    For example, let's say that I want the cell B1 to accept only boolean values. If the user wants to enter the formula '=A1', he should instead enter '=MyCustomFunction(A1)' and MyCustomFunction would check if the evaluation result was of boolean type (or just return CalcConvert.ToBool(args(0))) and act accordingly. Having to enter 'MyCustomFunction(formula)' where formula is the expression he would normally use, is not very elegant and I would like to avoid it.


    Is there another way of implementing the above functionality that lets the user enter a formula as normal? Or, in the case I have not completely comprehended what you suggest, please clarify.


    Thanks in advance.

  • Replied 8 September 2017, 12:38 pm EST

    Hi scotts,


    I just realized that I accidentally posted this subject in the Spread for Web Forms forums when it actually refers to the Spread for Windows Forms product. Please, feel free to move the whole thread to the Spread for Windows Forms(.NET) forums.


    Since I am not familiar with the asp.net version of the product and the possible differences it has compared to the .net version, I don't know if this change has any effect on your suggestion; meaning if the windows forms product offers additional functionality and a more direct way to implement something like that, whereas the web forms product doesn't. Please advise if this is the case.


    I am not sure I fully understand what you mean in your suggestion. Do you mean to create some kind of "dummy" custom function, mark it as volatile and set it as the formula of a hidden cell so it gets evaluated every time there is a change on the spread? Or use this custom function in the formulae of all the cells whose type I need to check? Or did you mean something else?


    Thanks in advance.

  • Replied 8 September 2017, 12:38 pm EST

    Hello,


    thank you bobbyo, using the UserFormulaEntered event is certainly easier than the previous approach.


    I would like to ask one more question though; in the code snippet that scotts posted, he first clears the formula and then sets the value of the cell to a CalcError. By experimenting a little, I noticed that, only setting a CalcError as the cell's value without first clearing the formula is not working. Is there a way to keep the formula entered?


    In similar situations, the spread control doesn't clear the formula. For example, when I enter the formula 'a()+15', it dispays the error (#NAME? in this case) but the entered formula isn't lost; I can edit it if I wish. The cell with the error has its formula property empty though, so spread must be using another mechanism to implement this.


    Thanks in advance.

  • Replied 8 September 2017, 12:38 pm EST

    Hello,


    I appologize for the delay in response. I was on vacation last week. You are correct in your assumption in how I was suggesting to implement this. The only other way I have found to do this, so the user can type normal functions, is to catch when the user goes into editmode to enter a formula (EditModeStarting event) and set a flag. Then, in the EditModeOff event, check the formula and if it is not one you want in the cell, remove the formula and enter the CalcError you want in the cell.

     
      Dim formula As Boolean

    Private Sub
    Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    FpSpread1.AllowUserFormulas = True
    End Sub

    Private Sub
    FpSpread1_EditModeOff(ByVal sender As Object, ByVal e As System.EventArgs) Handles FpSpread1.EditModeOff
    Dim fstring As String
    If
    formula Then
    formula = False
    fstring = FpSpread1.ActiveSheet.ActiveCell.Formula
    If fstring.StartsWith("SUM") Then
    FpSpread1.ActiveSheet.ActiveCell.Formula = ""
    FpSpread1.ActiveSheet.ActiveCell.Value = FarPoint.CalcEngine.CalcError.Number
    End If
    End If
    End Sub

    Private Sub
    FpSpread1_EditModeStarting(ByVal sender As Object, ByVal e As FarPoint.Win.Spread.EditModeStartingEventArgs) Handles FpSpread1.EditModeStarting
    If e.EditFormula Then
    formula = True
    End If
    End Sub
    End Class
     
  • Replied 8 September 2017, 12:38 pm EST

    Hi scotts,


    thank you for the last suggestion; I certainly prefer it over the other one, since it lets the user work as normal with the spread control, without having to change his way of entering formulae.


    I will proceed in implementing it and will keep you informed.


    Best regards.

  • Replied 8 September 2017, 12:38 pm EST

    prosvasis,


    When the application sets a cell value, a new recalculation cycle is started.  If the cell has a formula then the formula will be evaluated and the formula result will overwrite the application entered value.  Thus, the application needs to clear the formula prior to assigning the cell value.


    Formulas can evaluate to any Object including CalcError.  A formula that evaluates to a CalcError (e.g. a()+15 evalautes to #NAME?) is treated no differently than a formula that evaluates to a number (e.g. 1+2 evaluates to 3).  The formula result is placed in the cell.  The formula remains in the cell.  The user can still edit the formula.  The cell's Formula property can still access the formula.


    I am not able to reproduce the scenario you described concerning the cell's Formula property returning empty after formula evaluates to CalcError.  If you can provide the steps needed to reproduce this scenario then we will gladly look into the issue.


    Bobby

  • Replied 8 September 2017, 12:38 pm EST

    Hello,


    The problem is the formula has not evaluated by time this event fires, so the Value of the cell gets changed from the CalcError you display to the result of the calculated formula. You can try storing the formula string in the Tag property before clearing it from the cell and then when entering editmode, you can see if the user typed = (e.EditFormula = true in the EditStarting event). In this case, set the Formula back into the cell.

  • Replied 8 September 2017, 12:38 pm EST

    prosvasis,


    There is also a UserFormulaEntered event which gets fired when a user enters a formula.  The UserFormulaEntered event might be easier to use in your scenario that the EditModeOff event because you would not have to bother with the "formula" flag and the "if formula then" check.


    Bobby

  • Replied 8 September 2017, 12:38 pm EST

    Hi bobby,


    you may have misunderstood what I meant in my previous post; in my scenario, I manually set the cell's value to a CalcError if the formula entered does not evaluate to a certain data type (e.g. Boolean). The formula itself does not (have to) evaluate to a CalcError; its a standard formula and it doesn't use any custom function for implementing that check.


    For instance, the formula entered in a cell might evaluate to a numeric value but I want to restrict that cell to only Boolean values. So, I set the cell's value to a CalcError but, since doing that will start a new recalculation cycle (as you mention in your post), I need to clear the formula entered before doing that.


    I will probably follow the suggestion of scott, storing the formula in the cell Tag property before clearing it and checking for it in the EditModeStarting event.


    Thanks for all the help you have provided me.

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels