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)
ofB1
toB2
=>Left(A2, 3)
(AB1 - AB$1) * $AB1
ofAC1
toAD2
=>(AC2 - AC$1) * $AB2
A1 & "-B1-C1"
ofB1
toB2
=>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.
@"\b$?[A-Z]+$?\d+\b"
and transpose as necessary. \$\endgroup\$