IfError() and Match()

5 posts, 0 answers
  1. MikeWiese
    MikeWiese avatar
    43 posts
    Member since:
    Apr 2007

    Posted 16 Mar 2017 Link to this post

    The IfError and Match functions are not implemented. Any chance of getting these implemented?

     

    Also Vlookup, but this has already been mentioned on another thread

  2. Tanya
    Admin
    Tanya avatar
    718 posts

    Posted 21 Mar 2017 Link to this post

    Hello Mike,

    The tasks scheduled for each release depend on their priority in our backlog, which is calculated depending on many factors, the most important of which is the client demand.

    The three functions are currently not scheduled and I am unable to give you a time frame when they will be available. I can shed some light on their current priority. VLOOKUP and IFERROR are among the functions with the highest priority, while MATCH is with lower priority - I just created a feedback item for it. I can see that you have already found several items but here are the links to the feature requests for the discussed functions:

      - IFERROR function
      - MATCH function
      - VLOOKUP function

    Although the functions are not built-in, you can implement them as custom functions and use them in your application. The public feedback items contain links to the resources you can use as a reference for the implementation.

    Hope this is helpful.

    Regards,
    Tanya
    Telerik by Progress

  3. Guy
    Guy avatar
    21 posts
    Member since:
    Oct 2012

    Posted 24 Oct 2017 Link to this post

    Hi

    I am using the below class and it appears to work alright. Maybe someone at Telerik can correct any errors.

     

    public class TelerikSpreadsheetIfError : FunctionWithArguments
        {
            public static readonly string FunctionName = "IFERROR";
            private static readonly FunctionInfo Info;
     
            public override string Name
            {
                get
                {
                    return FunctionName;
                }
            }
     
            public override FunctionInfo FunctionInfo
            {
                get
                {
                    return Info;
                }
            }
     
            static TelerikSpreadsheetIfError()
            {
                string description = "Excel compatible function. Returns value_if_error if the expression is an error and the value of the expression itself otherwise.";
     
                IEnumerable<ArgumentInfo> requiredArguments = new ArgumentInfo[]
                {
                    new ArgumentInfo("value", "Any value or expression or reference", ArgumentType.Any),
                    new ArgumentInfo("value_if_error", "The value to use if the first argument is an error.", ArgumentType.Any),
                };
     
                Info = new FunctionInfo(FunctionName, FunctionCategory.Logical, description, requiredArguments);
            }
     
            protected override RadExpression EvaluateOverride(FunctionEvaluationContext<object> context)
            {
                try
                {
                    object firstArg = context.Arguments[0];
                    object secondArg = context.Arguments[1];
                     
                    if ( firstArg is ErrorExpression )
                    {
                        return secondArg as RadExpression;
                    }
                    else
                    {
                        return firstArg as RadExpression;
                    }
                }
                catch (System.Exception ex)
                {               
                    return ErrorExpressions.NullError;
                }
            }
        }
  4. Guy
    Guy avatar
    21 posts
    Member since:
    Oct 2012

    Posted 24 Oct 2017 Link to this post

    Ok, that evaluation function is not going to work in all cases. Try this instead:

     

    protected override RadExpression EvaluateOverride(FunctionEvaluationContext<object> context)
            {
                try
                {
                    object firstArg = context.Arguments[0];
                    object secondArg = context.Arguments[1];
     
                    var firstArgConst = ((RadExpression)context.Arguments[0]).GetValueAsNonArrayConstantExpression(false);
                     
                    if (firstArgConst is ErrorExpression )
                    {
                        return secondArg as RadExpression;
                    }
                    else
                    {
                        return firstArg as RadExpression;
                    }
                }
                catch (System.Exception ex)
                {               
                    return ErrorExpressions.NullError;
                }
            }
  5. Anna
    Admin
    Anna avatar
    129 posts

    Posted 26 Oct 2017 Link to this post

    Hi Guy,

    I tested your code with all possible errors and scenarios that I could think of and that I found in the xlsx specification. Looks good! Thank you for providing it in the forum where others might make use of it as well.

    Regards,
    Anna
    Progress Telerik

Back to Top