C# how to determine if a custom function argument is number or cell coordinates

[Alessandro Manotti]

Hello,

I'm writing a custom function in C#, but the problem is I cannot find a way to verify if a function argument is a number, a string, a cell coordinate or cell range (or named cell).

Example. Let's imagine my custom function is called "MyCustomFunc" in C#.

Please see the following images:

As you can see when I insert a custom function in the sheet:

=MyFunc("text"; 13; B1; C1:F5)

I can pass text, numbers, a single cell coord or a range, but the problem is the custom function in C# receives everything as strings. Is there any method in SyncFusion WinForms SPREADSHEET to verify the correct type:

"text" --> string
13 --> number
C5 --> Cell Coord
F1:I5 --> Range

since I can get the values from the referenced cells/ranges. It could be also useful if there is a method to automatically evaluate and grab the values inside the cell references.

Thank you for your help!

[Alessandro Manotti]

I partially solved using the method

engine.GetValueFromArg(cellValue)

It successfully works with cells (e.g. A3). The problem arises when the cell is in another sheet, it seems this does NOT work: "Sheet2!A1" (value to set to an instance of the object).

currentArg = engine.GetValueFromArg(cellsFromArg[theCellNum]);

[Dhanasekar Mohanraj]

Hi Alessandro Manotti,

We have checked the feasibility to achieve your requirement. By default, the computed value will be returned as a string as you mentioned. If you need to change this as the respective types then it will be achievable by using the parsing tools of CalcEngine shown below,

public string CustomMin(string args)

{

  double min = double.MaxValue;

  double d;

  var splitArgs = args.Split(new char[] { CalcEngine.ParseArgumentSeparator });

  foreach (string s in splitArgs)

  {

     if (double.TryParse(s, NumberStyles.Number | NumberStyles.AllowExponent, null, out d))

         min = Math.Min(min, d);

  }

}

For more information related to parsing, please refer to the below user guide documentation link,

UG Link: https://help.syncfusion.com/windowsforms/calculation-engine/custom-function#write-a-method

If you still facing any issues modify the attached sample based on your scenario and share the video illustration. It will be helpful for us to check on it and provide you with a solution as soon as possible.

Regards,

Dhanasekar M.

If this post is helpful, please consider Accepting it as the solution so that other members can locate it more quickly.

[Alessandro Manotti]

Thank you but I already red it and seems it does not solve my problem.

The line you mentioned, if I well, works for numbers,

if (double.TryParse(s, NumberStyles.Number | NumberStyles.AllowExponent, null, out d))

but if I haven't a number the tryparse fails ("double" in the example).

I need a kind of engine.GetValueFromArg(cellsFromArg[theCellNum]);

but the problem in that method is it is related to a Worksheet. So, if the cell/range is not in the the sheet but in another one it cannot grab the cell value.

I can say I need a similar method but related to the entire workbook, a parser that can correctly manage the sheet reference.

If I debug the "args" when another sheet is referenced I get (for example if I'm looking for at Sheet 11 --> A5, similar to the Excel syntax "Sheet11!A5"):

!10!A5

Sheet11->!10!(the components counts from zero).

Now I'm working to create a small algorithm to grab the first part of the reference (e.g. "!10!") then looking for the CalcEngine associated to that sheet (Sheet11) and get the cell value. It will work but is neither efficient nor elegant; I ask if there is a method in Spreadsheet to do this automatically.

Thank you

[Vijayarasan Sivanandham]

Alessandro Manotti,

There is no other way to directly achieve your requirement. However, your requirement can be fulfilled by using the customized algorithm below.,

//Get the Sheet token

private string SheetToken(string s)

{

    int i = 0;

    string s1 = string.Empty;

    if (i < s.Length && s[i] == '!')

    {

        i++;

        while (i < s.Length && s[i] != '!')

        {

            i++;

        }                

        if (i < s.Length)

            s1 = s.Substring(0, i + 1);

    }

    if (i < s.Length)

    {

        return s1;

    }  

   

    return s1;

}

 

 

private void btnSplitSheet_Click(object sender, EventArgs e)

{

      var sheetReference = "!10!A5";

 

      //Here Get the sheet token

      var getSheetToken = SheetToken(sheetReference);          

 

      //Get the cell range

      string cellRange = sheetReference.Replace(getSheetToken, string.Empty);

 

      //Get the cell value by using the GetValueFromArg based on the spreadsheet

      var cellValue = spreadsheet1.ActiveGrid.FormulaEngine.GetValueFromArg(cellRange);

 

      Console.WriteLine("SheetToken " + getSheetToken + "Cell Range : " + cellRange + "Cell Value : " + cellValue );

 

}

Find the sample demo in the attachment.

If this post is helpful, please consider Accepting it as the solution so that other members can locate it more quickly.

Attachment: SpreadSheetDemo_aca70b1b.zip