How to get the list of dependent cells for a particular cell in WinForms Spreadsheet?
Dependent cells for a particular cell
WinForms Spreadsheet (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 the code example below.
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);
}Samples: