Custom Function using Lambda parameter

Custom Function using Lambda parameter

Posted by: niels on 5 August 2024, 6:41 am EST

• 

  niels

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 5 August 2024, 6:41 am EST - Updated 5 August 2024, 6:46 am EST

  version 17.1.20241.0
  
  I am trying to extend GrapeCity.CalcEngine.Function to create a function ForEach like ByCol / ByRow but than for 2 dimensions. How to support a Lambda parameter in my function. Is that possible? Is DelegateValue required? But that is not public.

  This is the goal
  
  

• 

  aastha.gupta

  

  • Post Options:
  • Link
    Copy

  Posted 6 August 2024, 5:59 am EST

  Hi Niels,

  We are currently discussing this issue with the developers. [Internal Tracking ID: SPNET-40799]

  We will update you on this as soon as we hear back from them.

  Thanks & Regards,
  
  Aastha

• 

  niels

  • Post Options:
  • Link
    Copy

  Posted 15 August 2024, 8:21 am EST

  Some more background.
  
  At least one of my customers is using this currently up to 300 columns and 100 rows. It can be done with 2 extra helper sheets. But for maintainability, it would better to have a single formula in B2. The workbooks are already quite complex.

• 

  aastha.gupta

  

  • Post Options:
  • Link
    Copy

  Posted 16 August 2024, 6:19 am EST

  Hi Niels,

  You do not need to work with DelegateValue class which is internal. You instead work with IDelegate, which represents the result of “calculation” parameter of LAMBDA function.

  Here is the code snippet that shows how to implement custom function using lambda parameter:

  fpSpread1.AsWorkbook().WorkbookSet.CalculationEngine.CalcFeatures = CalcFeatures.All;
  fpSpread1.AddCustomFunction(new ForEachFunction());
  fpSpread1_Sheet1.AsWorksheet().Cells["A2"].Formula2 = "{\"idA\";\"idB\";\"idC\"}";
  fpSpread1_Sheet1.AsWorksheet().Cells["B1"].Formula2 = "{\"id1\",\"id2\",\"id3\"}";
  fpSpread1.ActiveSheet.AsWorksheet().Cells["B2"].Formula2 = "ForEach(A2#,B1#,LAMBDA(y,x,CONCAT(x,y)))";

  public class ForEachFunction : Function
  {
      public ForEachFunction() : base("ForEach", 3, 3, FunctionAttributes.Array | FunctionAttributes.Spillable)
      {
      }
      protected override void Evaluate(IArguments arguments, IValue result)
      {
          IValue rowsArg = arguments[0];
          switch (rowsArg.ValueType)
          {
              case GrapeCity.CalcEngine.ValueType.Reference:
              case GrapeCity.CalcEngine.ValueType.AdjustableReference:
                  IValue colsArg = arguments[1];
                  switch (colsArg.ValueType)
                  {
                      case GrapeCity.CalcEngine.ValueType.Reference:
                      case GrapeCity.CalcEngine.ValueType.AdjustableReference:
                          IValue funcArg = arguments[2];
                          if (funcArg.ValueType == GrapeCity.CalcEngine.ValueType.Delegate)
                          {
                              IDelegate func = funcArg.GetDelegate();
                              IEvaluationContext context = arguments.EvaluationContext;
                              IReferenceSource rowSource = rowsArg.GetReferenceSource(context);
                              RangeReference rowRange = rowsArg.GetReference(context);
                              IReferenceSource colSource = colsArg.GetReferenceSource(context);
                              RangeReference colRange = colsArg.GetReference(context);
                              IValuesArray array = result.CreateValuesArray(rowRange.Row2 - rowRange.Row + 1, colRange.Column2 - colRange.Column + 1);
                              for (int row = rowRange.Row, row2 = rowRange.Row2; row <= row2; row++)
                              {
                                  rowSource.GetValue(context, row, rowRange.Column, func[1].Value);
                                  for (int col = colRange.Column, col2 = colRange.Column2; col <= col2; col++)
                                  {
                                      colSource.GetValue(context, colRange.Row, col, func[0].Value);
                                      context.Evaluate(func, result);
                                      array[row - rowRange.Row, col - colRange.Column].SetValue(result.GetValue(context));
                                  }
                              }
                              result.SetValue(array);
                          }
                          else
                          {
                              result.SetValue(CalcError.Calc);
                          }
                          break;
                      default:
                          result.SetValue(CalcError.Reference);
                          break;
                  }
                  break;
              default:
                  result.SetValue(CalcError.Reference);
                  break;
              }
          }
      }
  }

  Kindly refer to the attached sample for full implementation. [See LamdaParamDemo.zip]

  In case this does not meet your requirement, we kindly request you share your workbooks with us. If you can’t share the files here, you can create a case on our private support portal: https://developer.mescius.com/my-account/my-support

  Hope this helps!

  Thanks & Regards,
  
  Aastha

• 

  niels

  • Post Options:
  • Link
    Copy

  Posted 18 August 2024, 3:40 pm EST

  O wow! Thank you, Aastha and team. Work perfect. Best support ever. Small feedback, I think exchanging func[1] and func[0] is better.