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
