Excel Open xml sdk - Copy formula between cells with range modification

0
votes

I have a cell with formula (CellFormula). I don't know the formula content (Might be Sum, ifs or any other calculation).
The formula might include range.
When you drag the cell to other cells (manually at excel application itself) the formula range get updated.
I want the formula programatic copy to behave the same way.
If the formula contains range specifiers I want to update them to the current row.
Is there some built-in way to do so without regular expression manipulations?

1
c#excelopenxmlopenxml-sdk

1 Answers

1
votes

I don't know if it's a good solution that cover all possibilities but...

//Formula update
if (cloneFromCell.CellFormula != null && (cloneFromCell.CellFormula.FormulaType == null || !cloneFromCell.CellFormula.FormulaType.HasValue || cloneFromCell.CellFormula.FormulaType.Value == CellFormulaValues.Normal))
{
    uint cloneRowIndex = OXMLTools.GetRowIndex(cloneFromCell.CellReference);
    uint offset = rowIndex - cloneRowIndex;
    exCell.CellFormula.Text = OXMLTools.GetUpdatedFormulaToNewRow(cloneFromCell.CellFormula.Text, offset);
}

public static string GetUpdatedFormulaToNewRow(string formula, uint offset)
{
    return Regex.Replace(formula, @"[A-Za-z]+\d+", delegate(Match match)
    {
      //Calculate the new row for this cell in the formula by the given offset
      uint oldRow = GetRowIndex(match.Value);
      string col = GetColumnName(match.Value);
      uint newRow = oldRow + offset;

      //Create the new reference for this cell
      string newRef = col + newRow;
      return newRef;
    });
 }