Inherited models data disables custom (user defined) functions

Posted by: kbj on 20 September 2019, 1:11 am EST

  • Posted 20 September 2019, 1:11 am EST

    As illustrated in this windows forms example:

    Imports FarPoint.Win.Spread.Model
    Imports GrapeCity.CalcEngine

    Public Class Form1
    Dim dm As classDM
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
    dm = New classDM
    dm.RowCount = 10
    dm.ColumnCount = 10
    FpSpread1_Sheet1.Models.Data = dm
    FpSpread1.AddCustomFunction(New classNullFunction)
    FpSpread1.AddCustomFunction(New classDebugFunction)
    FpSpread1_Sheet1.Cells(0, 0).Formula = "NULL()"
    FpSpread1_Sheet1.Cells(1, 0).Formula = "DEBUG()"
    End Sub
    End Class
    Class classNullFunction
    Inherits GrapeCity.CalcEngine.[Function]
    Public Sub New()
    MyBase.New("NULL", 0, 0, FunctionAttributes.Variant)
    End Sub
    Protected Overrides Sub Evaluate(ByVal arguments As IArguments, ByVal result As IValue)
    result.SetValue(CalcError.Null)
    End Sub
    End Class
    Class classDebugFunction
    Inherits GrapeCity.CalcEngine.[Function]
    Public Sub New()
    MyBase.New("DEBUG", 0, 0, FunctionAttributes.Number)
    End Sub
    Protected Overrides Sub Evaluate(ByVal arguments As IArguments, ByVal result As IValue)
    result.SetValue(1)
    End Sub
    End Class
    Public Class classDM
    Inherits DefaultSheetDataModel
    End Class
  • Replied 22 September 2019, 6:16 pm EST

    Hello,

    I am able to reproduce the issue at our end. I will escalate the issue to our development team(275972) and inform you once I get any reply from them.

    Thanks.
  • Replied 25 September 2019, 3:41 am EST

    While I wait semi-patiently for some response from the development team, could you tell me how to set the value of a cell to Null? Is there a way to set the value to an Error value?
    -Kingman
  • Replied 26 September 2019, 11:33 pm EST

    Hello,

    Sorry for the delay!

    Actually, it is by design. It works with internal data model only. The reason is that the customized data model is the concept of Spread WinForms product but not the new core engine.
    In case you want to use custom data model, you must inherit the custom function from FunctionInfo(Old Calculation Engine).

    If you still want to use new function (new calculation engine) with the custom data model, please share your use case so that we can forward this to our PM's team.

    Thanks,
    Mohit
  • Replied 29 September 2019, 9:10 am EST

    Hi Mohit,
    I am not sure I can make any sense of your response. For the time being, all I need is the ability to set the value of a cell to null, or perhaps and error value in certain situations.
    I will always need to use a data model that is inherited from the DefaultSheetDataModel as I wrap the data models cell value with additional information during the GetValue Function. This works great at providing an associated CellType with information during formatting, painting or anything else. Of course all this wouldn't be required if CellType subs and function could know what Spread/Sheet/Cell they were processing.
    -Kingman
  • Replied 29 September 2019, 10:04 pm EST

    Hello,

    Sorry for the confusion.

    Actually new core engine does not work with the customer data model. you need to use the default model.

    As a workaround, you can use the old "FunctionInfo" class instead of "Fucntion" to achieve your desired behavior as follow:


    Public Class Form1
    Dim dm As classDM
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
    dm = New classDM
    dm.RowCount = 10
    dm.ColumnCount = 10

    FpSpread1_Sheet1.Models.Data = dm

    FpSpread1.ActiveSheet.AddCustomFunction(New classNullFunction)
    FpSpread1.ActiveSheet.AddCustomFunction(New classDebugFunction)

    FpSpread1_Sheet1.Cells(0, 0).Formula = "NULL()"
    FpSpread1_Sheet1.Cells(1, 0).Formula = "DEBUG()"
    End Sub
    End Class
    Class classNullFunction
    Inherits FarPoint.CalcEngine.FunctionInfo

    Public Overrides ReadOnly Property Name As String
    Get
    Return "NULL"
    End Get
    End Property

    Public Overrides ReadOnly Property MinArgs As Integer
    Get
    Return 0

    End Get
    End Property

    Public Overrides ReadOnly Property MaxArgs As Integer
    Get
    Return 0

    End Get
    End Property

    Public Overrides Function Evaluate(args() As Object) As Object
    Return CalcError.Null
    End Function
    'Public Sub New()
    ' MyBase.New("NULL", 0, 0, FunctionAttributes.Variant)
    'End Sub
    'Protected Overrides Sub Evaluate(ByVal arguments As IArguments, ByVal result As IValue)
    ' result.SetValue(CalcError.Null)
    'End Sub
    End Class
    Class classDebugFunction
    Inherits FarPoint.CalcEngine.FunctionInfo

    Public Overrides ReadOnly Property Name As String
    Get
    Return "DEBUG"
    End Get
    End Property

    Public Overrides ReadOnly Property MinArgs As Integer
    Get
    Return 0

    End Get
    End Property

    Public Overrides ReadOnly Property MaxArgs As Integer
    Get
    Return 0

    End Get
    End Property

    Public Overrides Function Evaluate(args() As Object) As Object
    Return 1
    End Function
    'Public Sub New()
    ' MyBase.New("DEBUG", 0, 0, FunctionAttributes.Number)
    'End Sub
    'Protected Overrides Sub Evaluate(ByVal arguments As IArguments, ByVal result As IValue)
    ' result.SetValue(1)
    'End Sub
    End Class
    Public Class classDM
    Inherits DefaultSheetDataModel


    If you still want to use the new core engine, could you please explain your business use case so that we can forward this to our PM's team.

    Hope it clarifies.

    Thanks,
    Mohit
  • Replied 30 September 2019, 8:41 am EST

    Hi Mohit,
    Thanks for your quick reply. Right now all I want to do is set a cell value to Null or some sort of Error. Could you tell me how to do that?
    In the future, i'd like to be able to create any type of function. Am I to believe that the the new core engine (is that the calculation engine?) can't handle custom functions?
    Thanks,
    Kingman
  • Marked as Answer

    Replied 30 September 2019, 3:54 pm EST

    Hello,

    >I want to do is set a cell value to Null or some sort of Error
    you can easily set the null or error value to cell as follow:
    FpSpread1_Sheet1.Cells(2, 0).Value = CalcError.Null

    >>Am I to believe that the the new core engine (is that the calculation engine?) can't handle custom functions?
    Right now, custom function with inherited datamodel is not supported. Either you use inherited datamodel or custom function in your application. However, you can create custom function using "FunctionInfo" class to use with inherited datamodel as shown in previous reply.

    Thanks,
    Mohit

  • Replied 30 September 2019, 11:56 pm EST

    Thank you Mohit!
Need extra support?

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

Learn More

Forum Channels