Improve 'A1 to R1C1' and 'R1C1 to A1' formula conversion.
diff --git a/EPPlus/ExcelCellBase.cs b/EPPlus/ExcelCellBase.cs
index b60f79f..a6bbc1c 100644
--- a/EPPlus/ExcelCellBase.cs
+++ b/EPPlus/ExcelCellBase.cs
@@ -134,10 +134,14 @@
{
ret += c;
}
+ else if (c == ':') // Keep Range expressions together
+ {
+ part += c;
+ }
else
{
if ((c == '-' || c == '+' || c == '*' || c == '/' ||
- c == '=' || c == '^' || c == ',' || c == ':' ||
+ c == '=' || c == '^' || c == ',' ||
c == '<' || c == '>' || c == '(' || c == ')' || c == '!' ||
c == ' ' || c == '&' || c == '%') &&
(pos == 0 || value[pos - 1] != '[')) //Last part to allow for R1C1 style [-x]
@@ -169,6 +173,13 @@
private static string ToR1C1(string part, int row, int col, int rowIncr, int colIncr)
{
int addrRow, addrCol;
+
+ // Handle range expressions
+ if ((part.Length > 1) && (part.IndexOf(':', 1) > 0))
+ {
+ return RangeToR1C1(part, row, col, rowIncr, colIncr);
+ }
+
string Ret = "R";
if (GetRowCol(part, out addrRow, out addrCol, false))
{
@@ -203,6 +214,66 @@
return part;
}
}
+
+ private static string RangeToR1C1(string part, int row, int col, int rowIncr, int colIncr)
+ {
+ // Split range expression
+ string[] cellValues = part.Split(new char[] { ':' }, StringSplitOptions.RemoveEmptyEntries);
+
+ // Convert range expressions
+ string result = "";
+ result += RangeCellToR1C1(cellValues[0], row, col, rowIncr, colIncr);
+ result += ":";
+ result += RangeCellToR1C1(cellValues[1], row, col, rowIncr, colIncr);
+
+ // Return converted range expression
+ return result;
+ }
+
+ private static string RangeCellToR1C1(string part, int row, int col, int rowIncr, int colIncr)
+ {
+ int addrRow, addrCol;
+ bool fixedRow, fixedCol;
+
+ string result = "";
+ if (GetRowCol(part, out addrRow, out addrCol, false, out fixedRow, out fixedCol))
+ {
+ if (addrRow > 0)
+ {
+ result += "R";
+ if (fixedRow)
+ {
+ // Absolute row
+ result += addrRow.ToString();
+ }
+ else if (addrRow - row != 0)
+ {
+ // Relative row
+ result += string.Format("[{0}]", addrRow - row);
+ }
+ }
+
+ if (addrCol > 0)
+ {
+ result += "C";
+ if (fixedCol)
+ {
+ // Absolute column
+ result += addrCol;
+ }
+ else if (addrCol - col != 0)
+ {
+ // Relative column
+ result += string.Format("[{0}]", addrCol - col);
+ }
+ }
+ return result;
+ }
+ else
+ {
+ return part;
+ }
+ }
/// <summary>
/// Translates to absolute address
/// </summary>
@@ -212,46 +283,250 @@
/// <param name="rowIncr"></param>
/// <param name="colIncr"></param>
/// <returns></returns>
+ ///
+
+
private static string ToAbs(string part, int row, int col, int rowIncr, int colIncr)
{
- string check = part.ToUpper(CultureInfo.InvariantCulture);
+ bool absoluteCol = false;
+ bool absoluteRow = false;
+ int colNum = -1;
+ int rowNum = -1;
+ int num;
+ int numLength;
+ int pos = 0;
- int rStart = check.IndexOf("R");
- if (rStart != 0)
- return part;
- if (part.Length == 1) //R
+
+ // Handle range expressions
+ if ((part.Length > 1) && (part.IndexOf(':', 1) > 0))
{
- return GetAddress(row, col);
+ return RangeToA1(part, row, col, rowIncr, colIncr);
}
- int cStart = check.IndexOf("C");
- bool absoluteRow, absoluteCol;
- if (cStart == -1)
+ // Ensure part is present
+ if (string.IsNullOrWhiteSpace(part))
+ return "";
+
+ // Convert to upper case
+ string check = part.ToUpper(CultureInfo.InvariantCulture);
+
+ // Parse "R", if any
+ if (pos < part.Length && check[pos] == 'R')
{
- int RNum = GetRC(part, row, out absoluteRow);
- if (RNum > int.MinValue)
+ pos += 1;
+
+ if (pos >= part.Length)
{
- return GetAddress(RNum, absoluteRow, col, false);
+ // Only "R" present
+ absoluteRow = false;
+ rowNum = row;
}
- else
+ else if (pos < part.Length && check[pos] == 'C')
{
- return part;
+ // "R" followed by "C"
+ absoluteRow = false;
+ rowNum = row;
}
+ else if (pos < part.Length && check[pos] == '[')
+ {
+ // "R" followed by relative row number
+ pos += 1;
+ num = GetNumber(check.Substring(pos, part.Length - pos), out numLength);
+ if (num == Int32.MinValue)
+ return part;
+ pos += numLength;
+
+ if (pos < part.Length && check[pos] == ']')
+ {
+ pos += 1;
+ }
+ else
+ {
+ return part;
+ }
+
+ absoluteRow = false;
+ rowNum = row + num;
+
+ }
+ else if (pos < part.Length)
+ {
+ // "R" followed by absolute row number
+ num = GetNumber(check.Substring(pos, part.Length - pos), out numLength);
+ if (rowNum == Int32.MinValue)
+ return part;
+ pos += numLength;
+
+ absoluteRow = true;
+ rowNum = num;
+ }
+ }
+
+ // Parse "C", if any
+ if (pos < part.Length && check[pos] == 'C')
+ {
+ pos += 1;
+
+ if (pos >= part.Length)
+ {
+ // Only "C" present
+ absoluteCol = false;
+ colNum = col;
+ }
+ else if (pos < part.Length && check[pos] == '[')
+ {
+ // "C" followed by relative column number
+ pos += 1;
+ num = GetNumber(check.Substring(pos, part.Length - pos), out numLength);
+ if (num == Int32.MinValue)
+ return part;
+ pos += numLength;
+
+ if (pos < part.Length && check[pos] == ']')
+ {
+ pos += 1;
+ }
+ else
+ {
+ return part;
+ }
+
+ absoluteCol = false;
+ colNum = col + num;
+
+ }
+ else if (pos < part.Length)
+ {
+ // "C" followed by absolute column number
+ num = GetNumber(check.Substring(pos, part.Length - pos), out numLength);
+ if (num == Int32.MinValue)
+ return part;
+ pos += numLength;
+
+ absoluteCol = true;
+ colNum = num;
+ }
+ }
+
+ // Ensure nothing remains unparsed
+ if (pos < part.Length)
+ return part;
+
+ // Exit if neither row nor column is present
+ if ((rowNum == Int32.MinValue) && (colNum == Int32.MinValue))
+ return part;
+
+ // Append column
+ string result = "";
+ if (colNum >= 0)
+ {
+ if (absoluteCol)
+ result += "$";
+ result += GetColumnLetter(colNum);
+ }
+
+ // Append row
+ if (rowNum >= 0)
+ {
+ if (absoluteRow)
+ result += "$";
+ result += rowNum.ToString();
+ }
+
+ // Return result
+ return result;
+ }
+
+ private static int GetNumber(string value, out int length)
+ {
+ // Get number length
+ length = 0;
+
+ // Ensure value is present
+ if (string.IsNullOrWhiteSpace(value))
+ return Int32.MinValue;
+
+ // Check for sign
+ if ((length < value.Length) && ((value[length] == '-') || (value[length] == '+')))
+ {
+ length += 1;
+ }
+
+ // Get number length
+ while (length < value.Length && value[length] >= '0' && value[length] <= '9')
+ {
+ length += 1;
+ }
+
+ // No number found
+ if (length == 0)
+ return Int32.MinValue;
+
+ // Return number value
+ int result;
+ return (int.TryParse(value.Substring(0, length), out result)) ? result : Int32.MinValue;
+ }
+
+ private static string RangeToA1(string part, int row, int col, int rowIncr, int colIncr)
+ {
+ // Split range expression
+ string[] cellValues = part.Split(new char[] { ':' }, StringSplitOptions.RemoveEmptyEntries);
+
+ // Convert range expressions
+ string result = "";
+ result += ToAbs(cellValues[0], row, col, rowIncr, colIncr);
+ result += ":";
+ result += ToAbs(cellValues[1], row, col, rowIncr, colIncr);
+
+ // Return converted range expression
+ return result;
+ }
+
+ private static string RangeCellToA1(string part, int row, int col, int rowIncr, int colIncr)
+ {
+ int addrRow, addrCol;
+ bool fixedRow, fixedCol;
+
+ string result = "";
+ if (GetRowCol(part, out addrRow, out addrCol, false, out fixedRow, out fixedCol))
+ {
+ if (addrRow > 0)
+ {
+ result += "R";
+ if (fixedRow)
+ {
+ // Absolute row
+ result += addrRow.ToString();
+ }
+ else if (addrRow - row != 0)
+ {
+ // Relative row
+ result += string.Format("[{0}]", addrRow - row);
+ }
+ }
+
+ if (addrCol > 0)
+ {
+ result += "C";
+ if (fixedCol)
+ {
+ // Absolute column
+ result += addrCol;
+ }
+ else if (addrCol - col != 0)
+ {
+ // Relative column
+ result += string.Format("[{0}]", addrCol - col);
+ }
+ }
+ return result;
}
else
{
- int RNum = GetRC(part.Substring(1, cStart - 1), row, out absoluteRow);
- int CNum = GetRC(part.Substring(cStart + 1, part.Length - cStart - 1), col, out absoluteCol);
- if (RNum > int.MinValue && CNum > int.MinValue)
- {
- return GetAddress(RNum, absoluteRow, CNum, absoluteCol);
- }
- else
- {
- return part;
- }
+ return part;
}
}
+
/// <summary>
/// Adds or subtracts a row or column to an address
/// </summary>
@@ -317,16 +592,18 @@
/// <returns></returns>
private static int GetRC(string value, int OffsetValue, out bool fixedAddr)
{
- if (value == "")
+ if ((value == "") || (value == "R") || (value == "C"))
{
+ // Relative address with no offset
fixedAddr = false;
return OffsetValue;
}
int num;
- if (value[0] == '[' && value[value.Length - 1] == ']') //Offset?
+ if (value[1] == '[' && value[value.Length - 1] == ']') //Offset?
{
+ // Relative address
fixedAddr = false;
- if (int.TryParse(value.Substring(1, value.Length - 2), out num))
+ if (int.TryParse(value.Substring(2, value.Length - 3), out num))
{
return (OffsetValue + num);
}
@@ -337,8 +614,9 @@
}
else
{
+ // Absolute address
fixedAddr = true;
- if (int.TryParse(value, out num))
+ if (int.TryParse(value.Substring(1, value.Length - 1), out num))
{
return num;
}
@@ -483,6 +761,7 @@
internal static bool GetRowCol(string address, out int row, out int col, bool throwException, out bool fixedRow, out bool fixedCol)
{
bool colPart = true;
+ bool isFixed = false;
int colStartIx = 0;
int colLength = 0;
col = 0;
@@ -506,31 +785,37 @@
for (int i = colStartIx; i < address.Length; i++)
{
char c = address[i];
- if (colPart && (c >= 'A' && c <= 'Z') && colLength <= 3)
+ if (c == '$')
{
+ // Absolute address
+ isFixed = true;
+ }
+ else if (colPart && (c >= 'A' && c <= 'Z') && colLength <= 3)
+ {
+ // Column portion of address
+ if (isFixed)
+ {
+ fixedCol = true;
+ isFixed = false;
+ }
+
col *= 26;
col += ((int)c) - 64;
colLength++;
}
else if (c >= '0' && c <= '9')
{
+ // Row portion of address
+ if (isFixed)
+ {
+ fixedRow = true;
+ isFixed = false;
+ }
+
row *= 10;
row += ((int)c) - 48;
colPart = false;
}
- else if (c == '$')
- {
- if (i == colStartIx)
- {
- colStartIx++;
- fixedCol = true;
- }
- else
- {
- colPart = false;
- fixedRow = true;
- }
- }
else
{
row = 0;
