Baldman68
Forum Replies Created
-
AuthorPosts
-
June 27, 2016 at 5:00 pm in reply to: Update CellFormula with offset, only for cells in current worksheet #3541
I decided to try another way. This appears to be working for me. I apologize for the formatting issues. Essentially what I do is parse the pieces of the code myself. I look for bangs and colons. If the part has a bang then I don’t apply the offset because it’s referring to a static sheet. If it has a : but no bang then I need to break apart the piece and apply the offset to both sides of the range. The code is below.
`
/// <summary>
/// Converts the cell formula with offset.
/// </summary>
/// <param name=”formula”>The formula.</param>
/// <param name=”offset”>The offset.</param>
/// <returns>CellFormula.</returns>
public CellFormula ConvertCellFormulaWithOffset(CellFormula formula, int offset)
{
try
{
CellFormula rtnVal = new CellFormula();//Split up the formula based on a regex which allows alphanumeric bangs and colons.
//those indicate worksheetnames and ranges
//If the part of the formula refers to a worksheet by name we don’t want to apply an offset
//because those worksheets are static.
string pattern = “[^a-zA-Z0-9!: -]”;
string formulaText = formula.Text;
string[] parts = Regex.Split(formulaText, pattern);//Loop through the array of formula parts
foreach (String s in parts)
{
string stringVal = s;//Make sure we only bring back parts that have both alpha and numeric (cell references have both) but don’t alter anything
//That contains a worksheet name (indicated by the presence of the bang (!)
if (!s.Contains(‘!’) && ContainsAlphaAndNumeric(s) && !string.IsNullOrEmpty(s) || (!s.Contains(‘!’) && !s.Contains(‘:’) && ContainsAlphaAndNumeric(s)))
{
//Get the converted cell reference
stringVal = ConvertCellRefWithOffset(s, offset).ToString();//Replace the part of the formula with the updated cell reference.
formulaText = formulaText.Replace(s, stringVal);
}}
//Set the new formula and return it
formula.Text = formulaText;
rtnVal = formula;
return formula;
}
catch (Exception ex)
{
throw ex;
}
}public StringValue ConvertCellRefWithOffset(StringValue cellRef, int offset)
{
try
{
string rtnVal = “”;
//Check to see if we are dealing with a range
if (cellRef.ToString().Contains(“:”))
{
//We have a range so we have to handle it differently by looping through the 2 parts
StringBuilder sb = new StringBuilder();
string[] rangeParts = cellRef.ToString().Split(‘:’);for(int i = 0; i<=1; i++)
{
string s = rangeParts[i];
//strip apart the cell reference to isolate the row number
string rownum = Regex.Replace(s, @”[^\d]”, “”);
string colLetter = Regex.Replace(s, @”(\p{L}+).*”, “$1”);//subtract the row offset from the current rownumer in the cell reference
int rownumInteger = Convert.ToInt16(rownum) – offset;
sb.Append(colLetter + rownumInteger.ToString());
if (i == 0)
{
//first part of the range do append the : into it.
sb.Append(“:”);
}}
rtnVal = sb.ToString();
}
else //no range so just a straight processing of the cell reference
{
//strip apart the cell reference to isolate the row number
string rownum = Regex.Replace(cellRef, @”[^\d]”, “”);
string colLetter = Regex.Replace(cellRef, @”(\p{L}+).*”, “$1″);//subtract the row offset from the current rownumer in the cell reference
int rownumInteger = Convert.ToInt16(rownum) – offset;//Piece the col letter with the new row number and return
rtnVal = colLetter + rownumInteger.ToString();
}
return rtnVal;
}
catch (Exception ex)
{
throw ex;
}
}/// <summary>
/// Determines whether [contains alpha and numeric] [the specified string to search].
/// </summary>
/// <param name=”stringToSearch”>The string to search.</param>
/// <returns><c>true</c> if [contains alpha and numeric] [the specified string to search]; otherwise, <c>false</c>.</returns>
public bool ContainsAlphaAndNumeric(string stringToSearch)
{
try
{
bool rtnVal = false;Regex rgAlpha = new Regex(@”[a-zA-Z]”);
Regex rgNumeric = new Regex(@”[0-9]”);
if (rgAlpha.IsMatch(stringToSearch) && rgNumeric.IsMatch(stringToSearch))
{
rtnVal = true;
}return rtnVal;
}
catch (Exception ex)
{
throw ex;
}
}` -
AuthorPosts