TAdvSpreadGrid : custom function libraries

TAdvSpreadGrid allows to use libraries that extend the built-in functions. A function library is a class that descends from TMathLib defined in the unit AdvPars. In order to be able to use multiple different function libraries simultaneously, a TLibBinder component can be assigned to TAdvSpreadGrid. Multiple TMathLib components can be assigned to the TLibBinder.

Anatomy of TMathLib

TMathLib implements a number of public virtual functions that can be overridden to implement custom functions:

Implemented functions

The methods HandlesFunction and HandlesStrFunction are simple methods being called by the TLibBinder and assumed to return true when the library implements the function with the name 'FuncName'.

Example

This shows a library implementing 2 statistical functions HARMEAN and GEOMEAN. HandlesFunction should return true for functions that return a floating point result type. If a library implements a function with a string result, the HandlesStrFunction should be used.

Function calculation

The method CalcFunction implements the actual calculation of the function. The first parameter is the function name that should be calculated, the second parameter is a list of function parameters. The 2 last parameters can be set if an incorrect parameter is specified and the index of this incorrect parameter.

Example

function TMiscMathLib.CalcFunction(FuncName: string; Params: TParamList;
   var ErrType,ErrParam: Integer): Double;
var
   k: Integer;
   d: Double;

begin
   Result := 0.0;

   ErrType := Error_NoError;

   if FuncName = 'HARMEAN' then
   begin
       d := 0;
       for k := 1 to Params.Count do
       begin
           if Params.Items[k - 1] <> 0 then
           begin
               d := d + (1 / Params.Items[k - 1]);
           end
           else
           begin
               ErrType := Error_DivisionByZero;
               ErrParam := k - 1;
           end;
         Result := 1/d * Params.Count;
       end;
      Exit;
   end;

   if FuncName = 'GEOMEAN' then
   begin
       d := 1;
       for k := 1 to Params.Count do
       begin
           d := d * Params.Items[k - 1];
       end;
       if Params.Count > 0 then
       begin
           Result := exp(1/Params.Count * ln(d));
       end
       else
       begin
           ErrType := Error_DivisionByZero;
           ErrParam := 0;
       end;
   end;
end;

In this example, the functions HARMEAN and GEOMEAN are implemented. This shows how the method loops through the number of parameters passed to the function and calculates the result. As shown here in the code, a parameter of these functions cannot be zero. If a zero parameter is found, the method sets the error type to Error_DivisionByZero and if needed the index of the parameter that caused the actual error. The supported types of errors are:

Error_NoError = 0;
Error_NoFormula = 1;
Error_DivisionByZero = 2;
Error_InvalidValue = 3;
Error_InvalidCellRef = 4;
Error_InvalidRangeRef = 5;
Error_InvalidGridRef = 6;
Error_InvalidNrOfParams = 7;
Error_CircularReference = 8;
Error_NoOpenParenthesis = 9;
Error_NoCloseParenthesis = 10;
Error_PrematureEndOfFormula = 11;
Error_UnknownError = 12;
Error_InvalidQualifier = 13;
Error_InvalidTokenAtPosition= 14;
Error_Overflow = 15;
Error_Underflow = 16;
Error_CircularRange = 17;


The string function handling is equivalent, as shown in this sample code:

function TStringMathLib.CalcStrFunction(FuncName: string;
   Params: TStringList; var ErrType, ErrParam: Integer): String;
var
   i: Integer;
   s: string;
begin
   Result := '';
   ErrType := 0;

   if Params.Count = 0 then
   begin
       ErrType := Error_InvalidNrOfParams;
       Exit;
   end;

   Result := '';

   if FuncName = 'REVERSE' then
   begin
       s := Params.Strings[0];
       for i := 1 to Length(s) do
         Result := Result + s[Length(s)- i + 1];
   end;

   if FuncName = 'CAPITALIZE' then
   begin
       s := Params.Strings[0];
       for i := 1 to Length(s) do
         if (i = 1) or ( (i > 1) and (s[i - 1]=' ')) then
             Result := Result + upcase(s[i])
         else
           Result := Result + s[i];
   end;

end;

function TStringMathLib.HandlesStrFunction(FuncName: string): Boolean;
begin
   Result := (FuncName = 'REVERSE') or
               (FuncName = 'CAPITALIZE');
end;

Function parameter hints

An additional feature of a function library is the capability to provide parameter hints while entering the function in TAdvSpreadGrid. This capability is enabled with the method : GetEditHint. This method is called during editing when entering parameters for a function that the library implements. The library should return a string that contains the hint text. This hint text can contain HTML formatting tags. The ParamIndex parameter of the method GetEditHint indicates the index of the parameter being entered. This can be used to highlight with a tag, the parameter being entered.

Example

function TMyMathLib.GetEditHint(FuncName: string;
   ParamIndex: Integer): string;
begin
   if FuncName = 'MYPROC' then
   begin
     case ParamIndex of
       1: Result := 'MyProc(<b>n: Integer Value: string)<HR>Returns the n''character from string Value';
       2: Result := 'MyProc(n: Integer;<b> Value: string)<HR>Returns the n''character from string Value';
       end;
   end;
end;

In this example, the first parameter is bold, when the user is entering the first parameter and the 2nd parameter is bold when the user is entering the 2nd parameter.