)
We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on: June 24, 2019).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

How to replace a function in the GridFormulaEngine library in WinForms GridControl?

Platform: WinForms |
Control: GridControl |
Published Date: April 30, 2011 |
Last Revised Date: December 17, 2019
Tags: formula

Add and remove function

Refer to the following steps to replace a function in the GridFormulaEngine Library.

  1. To remove a function, use the RemoveFunction method in the GridFormulaEngine class.

C#

//Remove the SUM function
engine.RemoveFunction("SUM");

 

VB

'Remove the SUM function
engine.RemoveFunction("SUM")
  1. To add a function, use the AddFunction method in the GridFormulaEngine class.

C#

//Add the SUM Function
engine.AddFunction("SUM",new GridFormulaEngine.LibraryFunction(computeMySUM));
gridControl1[3,3].Text = "= SUM(100+700)";
/// <summary>
/// Get the octal value  for the specified base10 value
/// </summary>
private string base8ToInt(string args)
{
    string s = args;
    //To match the integer in the args
    MatchCollection mc = Regex.Matches(args,@"(\d)+");
    foreach(Match m in mc)
    {
    //To convert the octal to base10 values 
    s = Regex.Replace(s,m.Value,Convert.ToInt32(m.Value ,8).ToString()); 
    }
    return s;
}
/// <summary>
/// Get the string notation for the specified range of cells
/// </summary>
private string ParseRangeOfValues(string args)
{
    //To match the cellranges
    //The group <cellRangeBegin> matches the beginning of the range.
    //The group <cellRangeEnd> matches the ending of the range.
    Match m1 = Regex.Match(args,@"(?<cellRangeBegin>((\w)+))(\s*):(\s*)(?<cellRangeEnd>((\w)+))");
    if(m1.Success)
    {
    string start = m1.Groups["cellRangeBegin"].Value;   
    string end = m1.Groups["cellRangeEnd"].Value;
    int rowFrom;
    int rowTo;
    int colFrom;
    int colTo;
    //To match the starting range for separating the column and row
    //The group <column1> matches the column name
    //The group <row1> matches the row no                          Match mrange1 = Regex.Match( start,@"(?<column1>(([a-zA-Z])))(?<row1>(\d+))");
    //To match the ending range for separating the column and row
    //The group <column1> matches the column name
    //The group <row1> matches the row no
    Match mrange2 = Regex.Match( end,@"(?<column1>(([a-zA-Z])))(?<row1>(\d+))");
    start = "";
    if(mrange1.Success && mrange2.Success)
    {
        //To convert the string row to integer row no
        rowFrom = Convert.ToInt32(mrange1.Groups["row1"].Value);  
        rowTo = Convert.ToInt32( mrange2.Groups["row1"].Value); 
        //To convert the string column to integer col no
        colFrom = mrange1.Groups["column1"].Value.ToUpper()[0] - 'A' + 1;  
        colTo = mrange2.Groups["column1"].Value.ToUpper()[0] - 'A' + 1; 
        for(int i = rowFrom ;i <= rowTo;i++)
        {
        for(int j = colFrom;j <= colTo;j++)
        {
            //To match the 8 and 9 digits to avoid the error
            if(Regex.Match(gridControl1[i,j].Text,"[89]").Success)
            return "Only Octal digits are Allowed for Sum function";
            //To convert the octal to decimal
            start += Convert.ToInt32(gridControl1[i,j].Text,8).ToString() + "+";  
        }
        }
        start += "0ab";
        start = "bn" + start;
        //To convert into required notation. 
        start = Regex.Replace(start,@"\+","an");
        //To replace the first  'an' to n 
        start = Regex.Replace(start,@"bn(?<num>(\d+))an","bn${num}n");
        return start;
    }
    }
    return "";
}
/// <summary>
/// compute the sum for specified args
/// </summary>
private string computeMySUM(string args)
{
    // to match the octal notation
    if( !Regex.Match(args,"[89]").Success )   
    {
    string s = args;
    //to match the range (:)
    Match m1 = Regex.Match(args,":");
    if( !m1.Success)
    {
         //to convert the octal to decimal
         s = this.base8ToInt(args);
    }
    else
    {
        //to parse the specified range of the cells
        s = this.ParseRangeOfValues(args);
        if(s.Equals("Only Octal digits are Allowed for Sum function"))
        return s;
    }
    string  inum = engine.ComputeSum(s);
    //to convert decimal to octal 
    return covertToBase8(int.Parse(inum));
    }
    else
    return "Only Octal digits are Allowed for Sum Function";
}
private string covertToBase8(int s)
{
    string sum = "";
    while(s >0)
    {
    sum += s % 8 ;
    s = (int)s/8;
    }
    int num = 0;
    // to reverse the sum value 
    for(int i = 0 ;i<sum.Length;i++)
    {
    num += Convert.ToInt32(sum[i].ToString()) * (int)Math.Pow(10.0,(double)i) ;
    }
    sum = num.ToString(); 
    return sum ; 
}

 

VB

'Add the SUM Function
engine.AddFunction("SUM",New GridFormulaEngine.LibraryFunction(AddressOf computeMySUM))
gridControl1(3,3).Text = "= SUM(100+700)"
''' <summary>
''' Get the octal value  for the specified base10 value
''' </summary>
private String base8ToInt(String args)
    Dim s As String = args
    'To match the integer in the args
     Dim mc As MatchCollection = Regex.Matches(args,"(\d)+")
     For Each m As Match In mc
         'To convert the octal to base10 values 
         s = Regex.Replace(s,m.Value,Convert.ToInt32(m.Value,8).ToString())
     Next m
     Return s
''' <summary>
''' Get the string notation for the specified range of cells
''' </summary>
private String ParseRangeOfValues(String args)
       'To match the cellranges
       'The group <cellRangeBegin> matches the beginning of the range.
       'The group <cellRangeEnd> matches the ending of the range.
       Dim m1 As Match = Regex.Match(args,"(?<cellRangeBegin>((\w)+))(\s*):(\s*)(?<cellRangeEnd>((\w)+))")
       If m1.Success Then
         Dim start As String = m1.Groups("cellRangeBegin").Value
         Dim [end] As String = m1.Groups("cellRangeEnd").Value
         Dim rowFrom As Integer
         Dim rowTo As Integer
         Dim colFrom As Integer
         Dim colTo As Integer
         'To match the starting range for seperating the column and row
         'The group <column1> match the column name
         'The group <row1> matches the row no
         Dim mrange1 As Match = Regex.Match(start,"(?<column1>(([a-zA-Z])))(?<row1>(\d+))")
         'To match the ending range for seperating the column and row
         'The group <column1> match the column name
         'The group <row1> matches the row no
         Dim mrange2 As Match = Regex.Match([end],"(?<column1>(([a-zA-Z])))(?<row1>(\d+))")
         start = ""
         If mrange1.Success AndAlso mrange2.Success Then
             'To convert the string row to integer row no
             rowFrom = Convert.ToInt32(mrange1.Groups("row1").Value)
             rowTo = Convert.ToInt32(mrange2.Groups("row1").Value)
             'To convert the string column to integer col no
             colFrom = mrange1.Groups("column1").Value.ToUpper()(0) - AscW("A"c) + 1
             colTo = mrange2.Groups("column1").Value.ToUpper()(0) - AscW("A"c) + 1
             For i As Integer = rowFrom To rowTo
                 For j As Integer = colFrom To colTo
                     'To match 8 and 9 digits for avoiding the error
                     If Regex.Match(gridControl1(i,j).Text,"[89]").Success Then
                         Return "Only Octal digits are Allowed for Sum function"
                     End If
                     'To convert the octal to decimal
                     start &= Convert.ToInt32(gridControl1(i,j).Text,8).ToString() & "+"
                     Next j
                     Next i
            start &= "0ab"
            start = "bn" & start
            'To convert into required notation. 
            start = Regex.Replace(start,"\+","an")
            'To replace the first  'an' to n 
            start = Regex.Replace(start,"bn(?<num>(\d+))an","bn${num}n")
            Return start
        End If
    End If
    Return ""
''' <summary>
''' compute the sum for specified args
''' </summary>
private String computeMySUM(String args)
    ' to match the octal notation
    If Not Regex.Match(args,"[89]").Success Then
        Dim s As String = args
        'To match the range (:)
        Dim m1 As Match = Regex.Match(args,":")
        If Not m1.Success Then
            'to convert the octal to decimal
            s = Me.base8ToInt(args)
        Else
            'to parse the specified range of the cells
            s = Me.ParseRangeOfValues(args)
            If s.Equals("Only Octal digits are Allowed for Sum function") Then
                Return s
            End If
        End If
        Dim inum As String = engine.ComputeSum(s)
        'to convert decimal to octal 
        Return covertToBase8(Integer.Parse(inum))
    Else
        Return "Only Octal digits are Allowed for Sum Function"
    End If
private String covertToBase8(Integer s)
    Dim sum As String = ""
    Do While s >0
        sum &= s Mod 8
        s = CInt(Fix(s))/8
    Loop
    Dim num As Integer = 0
    ' to reverse the sum value 
    For i As Integer = 0 To sum.Length - 1
        num += Convert.ToInt32(sum.Chars(i).ToString()) * CInt(Fix(Math.Pow(10.0,CDbl(i))))
    Next i
    sum = num.ToString()
    Return sum

 

Samples:

C#: Formula_library_function

VB: Formula_library_function

Reference link: https://help.syncfusion.com/windowsforms/grid-control/formula-support

2X faster development

The ultimate WinForms UI toolkit to boost your development speed.
ADD COMMENT
You must log in to leave a comment

Please sign in to access our KB

This page will automatically be redirected to the sign-in page in 10 seconds.

Up arrow icon

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile
Live Chat Icon