Category / Section
How to replace a function in the GridFormulaEngine library in WinForms GridControl?
5 mins read
Add and remove function
Refer to the following steps to replace a function in the GridFormulaEngine Library.
- 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")
- 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:
Reference link: https://help.syncfusion.com/windowsforms/grid-control/formula-support
