Search code examples
c#excelepplusepplus-4

EPPlus issue with R1C1-reference to absolute column in formula

I recently switched from interop/closedxml to EPPlus, I noticed EPPlus handles R1C1 absolute references differently.

Consider the following code

int colIndex = 5;
string formula = string.Format("MATCH(@""12345"",C{0},0)", colIndex);

int targetCellRow = 1;
int targetCellColumnAbs = 1;
workSheet.Cells[targetCellRow, targetCellColumnAbs].FormulaR1C1 = formula;

With EPPlus this will result in:

=MATCH("12345";Inkoopdelen!R[4]C[2];0)

With the InterOp and ClosedXML

=MATCH("12345";Inkoopdelen!C5;0)

In my end result the EPPlus formula does not work, as It references a cell and not a column range. It also references the wrong column.

The reference R[4]C[2] from cell A1 = two columns to the right from Column A = Column C.

The absolute reference to column 5 should result column E (Either C[4] OR C5).

How can I use EPPlus to respect the absolute range references I enter in the formula?

Solution

  • Turns out this is a shortcoming in EPPlus, I changed the EPPlus source to fix this issue: https://github.com/JanKallman/EPPlus/pull/22