hi, in excel formula, it might contain cell reference (eg: =A9, =$B$90…), External Link (=‘H:\Myfolder[sample.xlsx]LinkSheet’!B5), Internal Link (=Sheet1!A1), name range(=MyName)…
is there anyway we can check the formula contain sheet name? external link? name range?
for example, we have a sheet call ‘A’, name ranges call: ‘A’, also there is ‘A1’ in the sheet…
the below is the way i found the cell…
var nextCell = worksheet.Cells.Find(‘A’, nextCell, LookInType.OnlyFormulas);
but the formula we found can be cell referenc(eg: =A1), sheet (eg: =A!B9), name(eg: A)…
how do we know which one we found?
@catalparue26,
Well, it is tricky to evaluate cell reference, external link, named range from formula strings. You need to evaluate using your own logic and code if the (formula) string found is cell reference, sheet name or named range, etc. For example, if the found string (e.g “A”) has char, i.e., “!” next to it, then surely it is referring to worksheet call. Similarly you may evaluate if a named range exist using the following line of code:
e.g
Sample code:
var range = _workbook.Worksheets.GetRangeByName("A");
if (range != null)
{
Console.Writeline("Named range found");
}
Regarding Cell reference, and external link evaluations, you may make use of GetPrecedents() and other relevant APIs for it, see the sample code segment for your reference:
e.g
Sample code:
............
bool blExternalLinks = cell.ContainsExternalLink;
ReferredAreaCollection ret = cell.GetPrecedents();
//Printing all the precedent cells' name.
if (ret != null)
{
for (int m = 0; m < ret.Count; m++)
{
ReferredArea area = ret[m];
StringBuilder stringBuilder = new StringBuilder();
if (area.IsExternalLink)
{
stringBuilder.Append("[");
stringBuilder.Append(area.ExternalFileName);
stringBuilder.Append("]");
}
stringBuilder.Append(area.SheetName);
stringBuilder.Append("!");
stringBuilder.Append(CellsHelper.CellIndexToName(area.StartRow, area.StartColumn));
if (area.IsArea)
{
stringBuilder.Append(":");
stringBuilder.Append(CellsHelper.CellIndexToName(area.EndRow, area.EndColumn));
}
Console.WriteLine(stringBuilder.ToString());
}
}
.....