Update CellFormula with offset, only for cells in current worksheet

[Baldman68Participant]

Hi, First off, THANK YOU for creating the PowerTools! I believe the parser is going to help me tremendously. I’m relatively new to the world of OpenXmL and have been drinking from the firehose for a couple weeks. I need to apply a row offset to parts of the formula that reference only the worksheet I’m on yet leave the parts that reference other worksheets as is.

Is there a way to accomplish this?

Thanks!

[Eric WhiteKeymaster]

I believe that you can do this. After parsing a formula, you must go into the abstract syntax tree, change values (apply your row offset), and then serialize back to a string, which you then set into the formula for the cell.

That parser was written by Bob McClellan (another developer of Open-Xml-PowerTools), quite some time ago, and I have not made use of it in the fashion you are contemplating. However, I believe it is doable.

Please let me know how you get on.

[Baldman68Participant]

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;
}
}`

[Author]

Posts