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;