2X faster development
The ultimate WinForms UI toolkit to boost your development speed.
Add and remove functionRefer to the following steps to replace a function in the GridFormulaEngine Library.
C# //Remove the SUM function engine.RemoveFunction("SUM");
VB 'Remove the SUM function engine.RemoveFunction("SUM")
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 |
2X faster development
The ultimate WinForms UI toolkit to boost your development speed.
This page will automatically be redirected to the sign-in page in 10 seconds.