Value of Complex Formula in Cell Returns #NAME?

1 Answer 91 Views
Spreadsheet
Nicholas
Top achievements
Rank 1
Nicholas asked on 04 Mar 2022, 08:47 PM

I'm using the RadSpreadProcessing library to import Excel files, process them, and export back out.

I come across an issue when cells have complex formulas.

These complex formulas have dependencies on other cell values which those values get set (SetValue(...) method) while processing the worksheet after its been imported.

When I try to get the cell value of the complex formula using this call:

cellSelection.GetValue().Value.GetResultValueAsString(cellSelection.GetFormat().Value)

it returns me a value of #NAME?.

When I try to get the value of the complex formula using this call:

cellSelection.GetValue().Value

I noticed a ResultValueType of Error.

But when the workbook is exported back out the cells do not contain #NAME? in them. They contain the correctly auto-calculated values.

In the attached ComplexFormulaIssue.PNG, Row 1 column A value gets set while processing the worksheet using the SetValue(...) method (set it from NX: 12 to 12 for example).

Row 1 Column B formula depends on Row 1 column A value (which works fine since it's an easier formula I guess. No issue here).

Row 1 Column C and D are normal text values.

Row 1 Column E has a complex formula that has dependencies to all the other cell values (this is the one that gives me issues).

I attached 2 other PNGs as examples of formulas I used in Row 1 Columns B and E.

Nicholas
Top achievements
Rank 1
commented on 04 Mar 2022, 08:53 PM

Column B formula:

=IF(LEFT($A1,3)="NX:","", A1)

Column E formula:

=IF(LEFT(A1,3)="NX:","",IF(ISBLANK(A1),"",IF(A1<10,CONCAT(LEFT(C1,3),D1,"00",B1,0),CONCAT(LEFT(C1,3),D1,0,B1,0))))

1 Answer, 1 is accepted

Sort by
0
Vladislav
Telerik team
answered on 09 Mar 2022, 08:06 AM

Hello Nicholas,

I was able to reproduce this issue. This behavior is observed, because of an unsupported function. I have logged a feature request on your behalf and you can find it here - SpreadProcessing: Add support for CONCAT function. You can vote for the implementation and subscribe to it, to receive updates when its status changes. As an appreciation for pointing this to our attention, I have updated your Telerik points.

Currently, only the CONCATENATE function is supported. As a workaround, you can use it instead of CONCAT. Another possible approach would be to implement it yourself - using custom functions. You can find a sample implementation for the function:

public class ConcatFunction : StringsInFunction
{
    public static readonly string FunctionName = "CONCAT";
    private static readonly FunctionInfo Info;

    public override string Name
    {
        get
        {
            return FunctionName;
        }
    }

    public override FunctionInfo FunctionInfo
    {
        get
        {
            return Info;
        }
    }

    public override ArgumentConversionRules ArgumentConversionRules
    {
        get
        {
            return ArgumentConversionRules.NaryNumberFunctionConversion;
        }
    }

    static ConcatFunction()
    {
        string description = "Joins several text strings into one text string.";
        string descriptionKey = "Spreadsheet_Functions_Concatenate_Info";

        IEnumerable<ArgumentInfo> requiredArguments = new ArgumentInfo[]
        {
            new ArgumentInfo("Text",
                                "Text1, text2, ...     are 1 to 254 text items or ranges to be joined into a single text item. The text items can be text strings, numbers, or cell references.",
                                ArgumentType.Text)
        };

        IEnumerable<ArgumentInfo> optionalArguments = new ArgumentInfo[]
        {
            new ArgumentInfo("Text",
                                "Text1, text2, ...     are 1 to 254 text items or ranges to be joined into a single text item. The text items can be text strings, numbers, or cell references.",
                                ArgumentType.Text)
        };

        Info = new FunctionInfo(FunctionName, FunctionCategory.Text, description, requiredArguments, optionalArguments, 254, true, descriptionLocalizationKey: descriptionKey);
    }

    protected override RadExpression EvaluateOverride(FunctionEvaluationContext<string> context)
    {
        StringBuilder builder = new StringBuilder();

        for (int i = 0; i < context.Arguments.Length; i++)
        {
            builder.Append(context.Arguments[i]);
        }

        return new StringExpression(builder.ToString());
    }
}

I hope you find this information helpful.

Regards,
Vladislav
Progress Telerik

Love the Telerik and Kendo UI products and believe more people should try them? Invite a fellow developer to become a Progress customer and each of you can get a $50 Amazon gift voucher.

Tags
Spreadsheet
Asked by
Nicholas
Top achievements
Rank 1
Answers by
Vladislav
Telerik team
Share this question
or