6
\$\begingroup\$

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.

\$\endgroup\$
2
  • 1
    \$\begingroup\$ Do you know R1C1 reference style ? It seems to me your are trying to re-invent it. \$\endgroup\$
    – Zonko
    Jun 20, 2012 at 15:42
  • 2
    \$\begingroup\$ Have you considered using regular expressions? I get the sudden feeling you could match @"\b$?[A-Z]+$?\d+\b" and transpose as necessary. \$\endgroup\$
    – ANeves
    Jun 20, 2012 at 16:58

2 Answers 2

3
\$\begingroup\$

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):

enter image description here

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.

\$\endgroup\$
1
\$\begingroup\$

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);
}
\$\endgroup\$
1
  • 2
    \$\begingroup\$ You have presented an alternative solution, but haven't reviewed the code. Please explain your reasoning (how your solution works and how it improves upon the original) so that the author can learn from your thought process. \$\endgroup\$
    – Malachi
    Apr 15, 2016 at 16:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.