Improve formula conversion.
diff --git a/EPPlus/ExcelCellBase.cs b/EPPlus/ExcelCellBase.cs index 61f8b5f..05c3fa6 100644 --- a/EPPlus/ExcelCellBase.cs +++ b/EPPlus/ExcelCellBase.cs
@@ -86,6 +86,19 @@ { return Translate(value, ToAbs, row, col, -1, -1); } + + /// <summary> + /// Translates a R1C1 to an absolut address/Formula: Version 1 + /// </summary> + /// <param name="value">Address</param> + /// <param name="row">Current row</param> + /// <param name="col">Current column</param> + /// <returns>The RC address</returns> + public static string TranslateFromR1C1_V1(string value, int row, int col) + { + return Translate_V1(value, ToAbs_V1, row, col, -1, -1); + } + /// <summary> /// Translates a absolut address to R1C1 Format /// </summary> @@ -97,8 +110,21 @@ { return Translate(value, ToR1C1, row, col, -1, -1); } + /// <summary> - /// Translates betweein R1C1 or absolut addresses + /// Translates a absolute address to R1C1 Format : Version 1 + /// </summary> + /// <param name="value">R1C1 Address</param> + /// <param name="row">Current row</param> + /// <param name="col">Current column</param> + /// <returns>The absolut address/Formula</returns> + public static string TranslateToR1C1_V1(string value, int row, int col) + { + return Translate_V1(value, ToR1C1_V1, row, col, -1, -1); + } + + /// <summary> + /// Translates betweein R1C1 or absolute addresses : Version 1 /// </summary> /// <param name="value">The addresss/function</param> /// <param name="addressTranslator">The translating function</param> @@ -140,6 +166,63 @@ { ret += c; } + else + { + if ((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] + { + ret += addressTranslator(part, row, col, rowIncr, colIncr) + c; + part = ""; + } + else + { + part += c; + } + } + } + if (part != "") + { + ret += addressTranslator(part, row, col, rowIncr, colIncr); + } + return ret; + } + + private static string Translate_V1(string value, dlgTransl addressTranslator, int row, int col, int rowIncr, int colIncr) + { + if (value == "") + return ""; + bool isText = false; + string ret = ""; + string part = ""; + char prevTQ = (char)0; + for (int pos = 0; pos < value.Length; pos++) + { + char c = value[pos]; + if (c == '"' || c == '\'') + { + if (isText == true && prevTQ != c) + { + ret += c; + continue; + } + + if (isText == false && part != "" && prevTQ == c) + { + ret += addressTranslator(part, row, col, rowIncr, colIncr); + part = ""; + prevTQ = (char)0; + } + prevTQ = c; + isText = !isText; + ret += c; + } + else if (isText) + { + ret += c; + } else if (c == ':') // Keep Range expressions together { part += c; @@ -167,6 +250,7 @@ } return ret; } + /// <summary> /// Translate to R1C1 /// </summary> @@ -179,11 +263,49 @@ private static string ToR1C1(string part, int row, int col, int rowIncr, int colIncr) { int addrRow, addrCol; + string Ret = "R"; + if (GetRowCol(part, out addrRow, out addrCol, false)) + { + if (addrRow == 0 || addrCol == 0) + { + return part; + } + if (part.IndexOf('$', 1) > 0) + { + Ret += addrRow.ToString(); + } + else if (addrRow - row != 0) + { + Ret += string.Format("[{0}]", addrRow - row); + } + + if (part.StartsWith("$")) + { + return Ret + "C" + addrCol; + } + else if (addrCol - col != 0) + { + return Ret + "C" + string.Format("[{0}]", addrCol - col); + } + else + { + return Ret + "C"; + } + } + else + { + return part; + } + } + + private static string ToR1C1_V1(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); + return RangeToR1C1_V1(part, row, col, rowIncr, colIncr); } string Ret = "R"; @@ -221,28 +343,28 @@ } } - private static string RangeToR1C1(string part, int row, int col, int rowIncr, int colIncr) + private static string RangeToR1C1_V1(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 += RangeCellToR1C1_V1(cellValues[0], row, col, rowIncr, colIncr); result += ":"; - result += RangeCellToR1C1(cellValues[1], row, col, rowIncr, colIncr); + result += RangeCellToR1C1_V1(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) + private static string RangeCellToR1C1_V1(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 (GetRowCol_V1(part, out addrRow, out addrCol, false, out fixedRow, out fixedCol)) { if (addrRow > 0) { @@ -291,9 +413,50 @@ /// <returns></returns> /// - + private static string ToAbs(string part, int row, int col, int rowIncr, int colIncr) { + string check = part.ToUpper(CultureInfo.InvariantCulture); + + int rStart = check.IndexOf("R"); + if (rStart != 0) + return part; + if (part.Length == 1) //R + { + return GetAddress(row, col); + } + + int cStart = check.IndexOf("C"); + bool absoluteRow, absoluteCol; + if (cStart == -1) + { + int RNum = GetRC(part, row, out absoluteRow); + if (RNum > int.MinValue) + { + return GetAddress(RNum, absoluteRow, col, false); + } + else + { + return part; + } + } + 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; + } + } + } + + private static string ToAbs_V1(string part, int row, int col, int rowIncr, int colIncr) + { bool absoluteCol = false; bool absoluteRow = false; int colNum = -1; @@ -306,7 +469,7 @@ // Handle range expressions if ((part.Length > 1) && (part.IndexOf(':', 1) > 0)) { - return RangeToA1(part, row, col, rowIncr, colIncr); + return RangeToA1_V1(part, row, col, rowIncr, colIncr); } // Ensure part is present @@ -337,7 +500,7 @@ { // "R" followed by relative row number pos += 1; - num = GetNumber(check.Substring(pos, part.Length - pos), out numLength); + num = GetNumber_V1(check.Substring(pos, part.Length - pos), out numLength); if (num == Int32.MinValue) return part; pos += numLength; @@ -358,7 +521,7 @@ else if (pos < part.Length) { // "R" followed by absolute row number - num = GetNumber(check.Substring(pos, part.Length - pos), out numLength); + num = GetNumber_V1(check.Substring(pos, part.Length - pos), out numLength); if (rowNum == Int32.MinValue) return part; pos += numLength; @@ -383,7 +546,7 @@ { // "C" followed by relative column number pos += 1; - num = GetNumber(check.Substring(pos, part.Length - pos), out numLength); + num = GetNumber_V1(check.Substring(pos, part.Length - pos), out numLength); if (num == Int32.MinValue) return part; pos += numLength; @@ -404,7 +567,7 @@ else if (pos < part.Length) { // "C" followed by absolute column number - num = GetNumber(check.Substring(pos, part.Length - pos), out numLength); + num = GetNumber_V1(check.Substring(pos, part.Length - pos), out numLength); if (num == Int32.MinValue) return part; pos += numLength; @@ -443,7 +606,7 @@ return result; } - private static int GetNumber(string value, out int length) + private static int GetNumber_V1(string value, out int length) { // Get number length length = 0; @@ -473,28 +636,28 @@ 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) + private static string RangeToA1_V1(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 += ToAbs_V1(cellValues[0], row, col, rowIncr, colIncr); result += ":"; - result += ToAbs(cellValues[1], row, col, rowIncr, colIncr); + result += ToAbs_V1(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) + private static string RangeCellToA1_V1(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 (GetRowCol_V1(part, out addrRow, out addrCol, false, out fixedRow, out fixedCol)) { if (addrRow > 0) { @@ -596,8 +759,44 @@ /// <param name="OffsetValue"></param> /// <param name="fixedAddr"></param> /// <returns></returns> + /// private static int GetRC(string value, int OffsetValue, out bool fixedAddr) { + if (value == "") + { + fixedAddr = false; + return OffsetValue; + } + int num; + if (value[0] == '[' && value[value.Length - 1] == ']') //Offset? + { + fixedAddr = false; + if (int.TryParse(value.Substring(1, value.Length - 2), out num)) + { + return (OffsetValue + num); + } + else + { + return int.MinValue; + } + } + else + { + // Absolute address + fixedAddr = true; + if (int.TryParse(value, out num)) + { + return num; + } + else + { + return int.MinValue; + } + } + } + + private static int GetRC_V1(string value, int OffsetValue, out bool fixedAddr) + { if ((value == "") || (value == "R") || (value == "C")) { // Relative address with no offset @@ -767,7 +966,6 @@ 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; @@ -791,54 +989,123 @@ for (int i = colStartIx; i < address.Length; i++) { char c = address[i]; - if (c == '$') + if (colPart && (c >= 'A' && c <= 'Z') && colLength <= 3) { - // 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++; + 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; + 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; - col = 0; - if (throwException) - { - throw (new Exception(string.Format("Invalid Address format {0}", address))); - } - else - { - return false; - } + row = 0; + col = 0; + if (throwException) + { + throw (new Exception(string.Format("Invalid Address format {0}", address))); + } + else + { + return false; + } } } return row != 0 || col != 0; } + internal static bool GetRowCol_V1(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; + row = 0; + fixedRow = false; + fixedCol = false; + + if (address.EndsWith("#REF!")) + { + row = 0; + col = 0; + return true; + } + + int sheetMarkerIndex = address.IndexOf('!'); + if (sheetMarkerIndex >= 0) + { + colStartIx = sheetMarkerIndex + 1; + } + address = address.ToUpper(CultureInfo.InvariantCulture); + for (int i = colStartIx; i < address.Length; i++) + { + char c = address[i]; + 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 + { + row = 0; + col = 0; + if (throwException) + { + throw (new Exception(string.Format("Invalid Address format {0}", address))); + } + else + { + return false; + } + } + } + return row != 0 || col != 0; + } + private static int GetColumn(string sCol) { int col = 0; @@ -1134,6 +1401,8 @@ /// <returns></returns> internal static string UpdateFormulaReferences(string Formula, int rowIncrement, int colIncrement, int afterRow, int afterColumn, bool setFixed=false) { + return ""; + /* //return Translate(Formula, AddToRowColumnTranslator, afterRow, afterColumn, rowIncrement, colIncrement); var d=new Dictionary<string, object>(); try @@ -1192,6 +1461,7 @@ { return Formula; } + */ } #endregion