//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); IWorksheet worksheet = workbook.Worksheets[0]; worksheet.Range["E2"].Formula = "=sum(C1:C2)"; worksheet.Range["C1"].Formula = "=B1"; worksheet.Range["B1"].Formula = "=sum(A1:A2)"; worksheet.Range["A1"].Value = 1; worksheet.Range["A2"].Value = 2; worksheet.Range["C2"].Value = 3; List<IRange> list = new List<IRange>(); foreach (var item in worksheet.Range["E2"].GetPrecedents()) { list.Add(item); } while (list.Count > 0) { var temp = list; list = new List<IRange>(); foreach (var item in temp) { for (int i = 0; i < item.RowCount; i++) { for (int j = 0; j < item.ColumnCount; j++) { var dependents = item.Cells[i, j].GetPrecedents(); if (dependents.Count == 0) { item.Cells[i, j].Interior.Color = Color.SkyBlue; } else { item.Cells[i, j].Interior.Color = Color.Gray; list.AddRange(dependents); } } } } } // Save to an excel file workbook.Save("GetAllPrecedents.xlsx");
' Create a new Workbook Dim workbook As New Workbook Dim worksheet As IWorksheet = workbook.Worksheets(0) With worksheet.Range !E2.Formula = "=sum(C1:C2)" !C1.Formula = "=B1" !B1.Formula = "=sum(A1:A2)" !A1.Value = 1 !A2.Value = 2 !C2.Value = 3 End With ' Add precedents of E2 Dim precedentsList = worksheet.Range!E2.GetPrecedents.ToList ' Add inner precedents of E2 Dim nextLayer = precedentsList Do While nextLayer.Count > 0 Dim currentLayer = nextLayer nextLayer = New List(Of IRange) For Each precedentRange In currentLayer For i = 0 To precedentRange.RowCount - 1 For j = 0 To precedentRange.ColumnCount - 1 Dim innerPrecedents = precedentRange.Cells(i, j).GetPrecedents() If innerPrecedents.Count = 0 Then precedentRange.Cells(i, j).Interior.Color = Color.SkyBlue Else precedentRange.Cells(i, j).Interior.Color = Color.Gray nextLayer.AddRange(innerPrecedents) End If Next j Next i Next precedentRange Loop ' save to an excel file workbook.Save("GetAllPrecedents.xlsx")