/// <summary>
/// Updates the Excel formula so that all the cellAddresses are incremented by the row and column increments
/// if they fall after the afterRow and afterColumn.
/// Supports inserting rows and columns into existing templates.
/// </summary>
/// <param name="Formula">The Excel formula</param>
/// <param name="rowIncrement">The amount to increment the cell reference by</param>
/// <param name="colIncrement">The amount to increment the cell reference by</param>
/// <param name="afterRow">Only change rows after this row</param>
/// <param name="afterColumn">Only change columns after this column</param>
/// <returns></returns>
public static string UpdateFormulaReferences(string Formula, int rowIncrement, int colIncrement, int afterRow, int afterColumn)
{
string newFormula = "";
Regex getAlphaNumeric = new Regex(@"[^a-zA-Z0-9]", RegexOptions.IgnoreCase);
Regex getSigns = new Regex(@"[a-zA-Z0-9]", RegexOptions.IgnoreCase);
string alphaNumeric = getAlphaNumeric.Replace(Formula, " ").Replace(" ", " ");
string signs = getSigns.Replace(Formula, " ");
char[] chrSigns = signs.ToCharArray();
int count = 0;
int length = 0;
foreach (string cellAddress in alphaNumeric.Split(' '))
{
count++;
length += cellAddress.Length;
// if the cellAddress contains an alpha part followed by a number part, then it is a valid cellAddress
int row = GetRowNumber(cellAddress);
int col = GetColumnNumber(cellAddress);
string newCellAddress = "";
if (ExcelCell.GetCellAddress(row, col) == cellAddress) // this checks if the cellAddress is valid
{
// we have a valid cell address so change its value (if necessary)
if (row >= afterRow)
{
row += rowIncrement;
}
if (col >= afterColumn)
{
col += colIncrement;
}
newCellAddress = GetCellAddress(row, col);
}
if (newCellAddress == "")
{
newFormula += cellAddress;
}
else
{
newFormula += newCellAddress;
}
for (int i = length; i < signs.Length; i++)
{
if (chrSigns[i] == ' ')
{
break;
}
if (chrSigns[i] != ' ')
{
length++;
newFormula += chrSigns[i].ToString();
}
}
}
return(newFormula);
}
