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. Image for the cookie policy date
close icon

Support for arrayformulas

Hi,

In excel I am able to retrieve the top 'n' items in a category based on am adjacent numeric column (I.e. top 10 products sold by revenue made) by combining LARGE, INDEX, and MATCH formulas in a formula array. Is there a way to apply array formulas to a cell in the gridcontrol? If these are not supported, do you know of a way to retrieve top 'n' categories in a custom function?

Any help is greatly appreciated!

2 Replies

MG Mohanraj Gunasekaran Syncfusion Team June 24, 2019 01:56 PM UTC

Hi Travis, 

Thanks for using Syncfusion product. 

By default, GridControl does not have the support for array formulas. Currently, we are analyzing the possibilities to provide the array formula support for our GridControl. So, we will check and update the details on 26th June 2019. 

Regards, 
Mohanraj G 



JP Jagadeesan Pichaimuthu Syncfusion Team June 26, 2019 09:25 AM UTC

Hi Travis, 
  
Thanks for your patience. 
  
Our CalcEngine does have the support to calculate the array formula. To integrate the CalcEngine with our GridControl, you could implement the custom class which should derived from ICalcData. Please refer the following code example and the sample. 
  
C# 
 CalcEngine formulaEngine; 
CustoCalulate calc = new CustoCalulate(this.gridControl1); 
formulaEngine = new CalcEngine(calc); 
  
public class CustoCalulate : ICalcData 
{ 
    GridControl grid; 
    public CustoCalulate(GridControl _grid) 
    { 
        grid = _grid; 
    } 
    public event ValueChangedEventHandler ValueChanged; 
  
    public object GetValueRowCol(int row, int col) 
    { 
        return grid[row, col].CellValue; 
    } 
  
    public void SetValueRowCol(object value, int row, int col) 
    { 
        grid[row, col].FormattedText = value.ToString(); 
    } 
  
    public void WireParentObject() 
    { 
    } 
} 
  
In our sample, array formula should starts with ‘{’ and endswith ‘}’(ex:{=Max(A1:A3/B1:B3)}). Please refer the following code example. 
  
C# 
this.gridControl1.CurrentCellEditingComplete += GridControl1_CurrentCellEditingComplete; 
this.gridControl1.CurrentCellInitializeControlText += GridControl1_CurrentCellInitializeControlText; 
private void GridControl1_CurrentCellInitializeControlText(object sender,GridCurrentCellInitializeControlTextEventArgs e) 
{ 
    if(e.Style.CellType.Equals(GridCellTypeName.FormulaCell) && e.Style.Tag != null) 
    { 
        e.ControlText = e.Style.Tag.ToString(); 
    } 
} 
  
private void GridControl1_CurrentCellEditingComplete(object sender, EventArgs e) 
{ 
    GridCurrentCell cc = this.gridControl1.CurrentCell; 
    GridStyleInfo style = this.gridControl1[cc.RowIndex, cc.ColIndex]; 
    if (style != null && style.CellType.Equals(GridCellTypeName.FormulaCell)) 
    { 
        //To compute the array formula 
        if (style.Text.StartsWith("{=") && style.Text.EndsWith("}")) 
        { 
            style.Tag= style.CellValue; 
            style.FormattedText = formulaEngine.ParseAndComputeFormula(style.Text); 
                    
        } 
    } 
} 
  
  
Please get back to us if you need any further assistance on this. 
  
Regards, 
Jagadeesan 


Loader.
Live Chat Icon For mobile
Up arrow icon