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: