Inherited models data disables custom (user defined) functions

Inherited models data disables custom (user defined) functions

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

• 

  kbj

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 20 September 2019, 10: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
  
  

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 23 September 2019, 3:16 am 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.

• 

  kbj

  • Post Options:
  • Link
    Copy

  Posted 25 September 2019, 12:41 pm 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

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 27 September 2019, 8:33 am 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

• 

  kbj

  • Post Options:
  • Link
    Copy

  Posted 29 September 2019, 6:10 pm 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

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 30 September 2019, 7:04 am 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

• 

  kbj

  • Post Options:
  • Link
    Copy

  Posted 30 September 2019, 5:41 pm 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

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 1 October 2019, 12:54 am 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

• 

  kbj

  • Post Options:
  • Link
    Copy

  Posted 1 October 2019, 8:56 am EST

  Thank you Mohit!