Articles in this section
Category / Section

How to get the list of dependent cells for a particular cell in WinForms Spreadsheet (SfSpreadsheet)?

1 min read

Dependent cells for a particular cell

SfSpreadsheet provides support to get the list of dependent cells for a particular cell. You can get the list of cells dependent on the cell by looping the PrecedentCells of FormulaEngine, like below code example.

C#

public List<String> GetDependentCells(int rowindex,int columnindex)
{
    var list = new List<string>();
    var grid = Spreadsheet.ActiveGrid;
    var family = FormulaEngine.GetSheetFamilyItem(grid);
    var cellref = family.GetTokenForGridModel(grid) + GridRangeInfo.GetAlphaLabel(columnindex) + rowindex;
    //To get the dependent cell
    if (grid.FormulaEngine.PrecedentCells.ContainsKey(cellref) && grid.FormulaEngine.PrecedentCells[cellref] != null)
    {
       Calculatedependentcell(cellref,list);
    }
    return list;
}
public void Calculatedependentcell(string precedentCell,List<string> list)
{
    var grid = Spreadsheet.ActiveGrid;
    if (grid.FormulaEngine.PrecedentCells[precedentCell] == null) return;         
      var ht = (Hashtable)((Hashtable)grid.FormulaEngine.PrecedentCells[precedentCell]).Clone();            
      foreach (var o in ht.Keys)
      {             
          var s1 = o as string;
          var row = grid.FormulaEngine.RowIndex(s1);
          var col = grid.FormulaEngine.ColumnIndex(s1);
          var cell = GridRangeInfo.GetAlphaLabel(col) + row;
          if (s1 != null)
          {
             if (ht.Keys.Count == 1)
                list.Add(s1);
             else
             {
                list.Add(s1);
                Calculatedependentcell(s1,list);//recursive call 
             }            
          }
      }
}

If you want to get the dependent cells of particular cell when its value changed, then subscribe the CellValueChanged event of Worksheet like below.

Spreadsheet.ActiveSheet.CellValueChanged += ActiveSheet_CellValueChanged;
private void ActiveSheet_CellValueChanged(object sender, CellValueChangedEventArgs e)
 {
     var list = GetDependentCells(e.Range.Row, e.Range.Column);
 }

Sample:

WPF

WinForms

UWP

Did you find this information helpful?
Yes
No
Help us improve this page
Please provide feedback or comments
Comments (0)
Please sign in to leave a comment
Access denied
Access denied