Copy Excel formula in a relative way

I use NPOI to copy Excel formula, but can’t find an option to do it in a relative way. Therefore, I’ve written a function to implement it. The task is “easy”. When copying a formula A1+B1 of C1 to C2, the result will be A2+B2. But the formula can do more than that. For example:

• Left(A1, 3) of B1 to B2 => Left(A2, 3)
• (AB1 - AB$1) * $AB1 of AC1 to AD2 => (AC2 - AC$1) * $AB2
• A1 & "-B1-C1" of B1 to B2 => A2 & "-B1-C1"

The difficult part is identifying the cell and handling the $.

public string GetCellForumulaRelative(string formula, int columnOffset, int rowOffset)
{
    var cells = formula.Split("+-*/(),:&^>=< ".ToCharArray(), StringSplitOptions.RemoveEmptyEntries)
        // start w/ A-Z
        .Where(i => (Convert.ToChar(i.Substring(0, 1).ToUpperInvariant()) >= 'A' &&
                     Convert.ToChar(i.Substring(0, 1).ToUpperInvariant()) <= 'Z') ||
                     Convert.ToChar(i.Substring(0, 1).ToUpperInvariant()) == '$')
        // end w/ 0-9
        .Where(i => Convert.ToChar(i.Substring(i.Length - 1, 1)) >= '0' &&
                    Convert.ToChar(i.Substring(i.Length - 1, 1)) <= '9');

    int startIdx = 0;
    foreach (var cell in cells)
    {
        int sepIdx = cell.IndexOfAny("0123456789".ToCharArray());
        if (cell.Substring(sepIdx - 1, 1).Equals("$"))
            sepIdx--;

        string col = cell.Substring(0, sepIdx);
        if (col.StartsWith("$") == false)
            col = GetExcelColumnName(ExcelColumnNameToNumber(col) + columnOffset);

        string row = cell.Substring(sepIdx);
        if (row.StartsWith("$") == false)
            row = (Convert.ToInt32(row) + rowOffset).ToString();

        startIdx = formula.IndexOf(cell, startIdx);
        formula = formula.Substring(0, startIdx) +
                  col + row +
                  formula.Substring(startIdx + cell.Length);
        startIdx += cell.Length;
    }

    return formula;
}

Any suggestions will be appreciated: performance, improvements, one-liners, or bugs.

I haven’t used NPOI and I would believe you have valid reasons for using it (I’ll take a wild guess at saying the thing is running on a server that doesn’t have Excel installed), but for the record, if my memory isn’t failing me if you used Microsoft VSTO / Excel Interop, you could use a plain & simple Copy+Paste and let Excel do the hard part.

Your code shows how much of a pain this could be otherwise.

That said @ANeves’ comment about using Regular Expressions, along with @Zonko’s comment about R1C1 references, could make your code much simpler:

Here’s the magic regex: ($?[A-Z]+$?d)(?=([^"]*"[^"]*")*[^"]*$)

…and the proof (using Expresso):

So the above regex pattern will spoonfeed you all A1 cell references that you need to analyze – those that aren’t surrounded by quotes.

I don’t know if NPOI facilitates this in any way (is there a Range object to play with?), but I would highly recommend getting the equivalent R1C1 cell references, so instead of AB$1 you get R1C[28]; then you can easily run a [much, much simpler] regex on these addresses to get the rows and columns and add your offsets, rebuild the addresses and rebuild the formulas.

You might be interested by the equivalent post on POI (https://stackoverflow.com/questions/1636759/poi-excel-applying-formulas-in-a-relative-way).

I have adapted the code from this post to work with C#’s NPOI.

What it simply does is shifting the destination cell row & column ids from the origin cell.

This is made available by the FormulaParser class that extracts ids from the given string formula.

I have tested the below function, it both works on cells & ranges.

public static void CopyUpdateRelativeFormula(ISheet Sheet, ICell FromCell, ICell ToCell)
{
    if (FromCell == null || ToCell == null || Sheet == null || FromCell.CellType != CellType.Formula) {
        return;
    }

    if (FromCell.IsPartOfArrayFormulaGroup()) {
        return;
    }

    var MyFormula = FromCell.CellFormula;
    int ShiftRows = ToCell.RowIndex() - FromCell.RowIndex();
    int ShiftCols = ToCell.ColumnIndex() - FromCell.ColumnIndex();

    XSSFEvaluationWorkbook WorkbookWrapper = XSSFEvaluationWorkbook.Create((XSSFWorkbook)Sheet.Workbook);
    var Ptgs = FormulaParser.Parse(MyFormula, WorkbookWrapper, FormulaType.Cell, Sheet.Workbook.GetSheetIndex(Sheet));

    foreach (void Ptg_loopVariable in Ptgs) {
        Ptg = Ptg_loopVariable;
        if (Ptg is RefPtgBase) {
            // base class for cell references
            RefPtgBase RefPtgBase = (RefPtgBase)Ptg;

            if (RefPtgBase.IsColRelative) {
                RefPtgBase.Column = RefPtgBase.Column + ShiftCols;
            }

            if (RefPtgBase.IsRowRelative) {
                RefPtgBase.Row = RefPtgBase.Row + ShiftRows;
            }
        } else if (Ptg is AreaPtg) {
            // base class for range references
            AreaPtg RefArea = (AreaPtg)Ptg;

            if (RefArea.IsFirstColRelative) {
                RefArea.FirstColumn = RefArea.FirstColumn + ShiftCols;
            }

            if (RefArea.IsLastColRelative) {
                RefArea.LastColumn = RefArea.LastColumn + ShiftCols;
            }

            if (RefArea.IsFirstRowRelative) {
                RefArea.FirstRow = RefArea.FirstRow + ShiftRows;
            }

            if (RefArea.IsLastRowRelative) {
                RefArea.LastRow = RefArea.LastRow + ShiftRows;
            }
        }
    }

    MyFormula = FormulaRenderer.ToFormulaString(WorkbookWrapper, Ptgs);
    ToCell.SetCellFormula(MyFormula);
}