Reference formulas and external linkage Excel Addin C#

Reference formulas and external linkage Excel Addin C#

Reference Formulas: refers to formulas which contains reference to other cells, sheets, workbook.

External Linkages: refers to external file path which your formula linked to. If you move file from source location the formula goes in error state but the file path remains within formula string.

In this article we will see how we can identify external linkages and reference formulas in a sheet. Basically there are three types of formulas can be seen withing a workbook:

  • Constant formulas (e.g. 10+30)
  • Reference formula (e.g. A40+B60 * ‘external excel file name file’!sheetname$J$11)
  • Mixed formula having constant and reference both (e.g. 20+A4)

Code example: Below code would use Regex class to determine reference in a cell and returns true/false in result:

public static bool HasCellReferenceFormula(Excel.Range oRange)
{
	try
	{
		string testExpression;
		Regex objRegEx = null;
		testExpression = oRange.Formula.ToString();
		string pattern = "([\'].*?[\'!])?([[A-Z0-9_]+[!])?(\\$?[A-Z]+\\$?(\\d)+(:\\$?[A-Z]+\\$?(\\d)+)?|\\$?[A-Z]+:\\$?[A-Z]+|(\\$?[A-Z]+\\$?(\\d)+))";
		MatchCollection result = Regex.Matches(testExpression, pattern);
		if (result.Count > 0)
		{
			return true;
		}
		else
		{
			return false;
		}
	}
	catch (Exception ex)
	{
		throw;
	}
}

Code example: get list of external linkages

//Get the values in the sheet
Excel.Range rng = modelSheet.UsedRange;
object[,] values = null;
try
{
	if (rng.Value2.GetType().IsArray)
	{
		values = (object[,])rng.FormulaArray;

	}
	else
	{
		values = new object[2, 2];
		values[1, 1] = rng.FormulaArray;
	}
}
catch
{ }

Next>>Converting Column Number to Column Letter in Excel using C#

Leave a Reply

Your email address will not be published.