diff --git a/ClosedXML/Excel/CalcEngine/Functions/Text.cs b/ClosedXML/Excel/CalcEngine/Functions/Text.cs index 0da3a1a..360fd82 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Text.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Text.cs @@ -1,9 +1,9 @@ using System; -using System.Diagnostics; -using System.Globalization; using System.Collections.Generic; +using System.Globalization; using System.Linq; using System.Text; +using System.Text.RegularExpressions; namespace ClosedXML.Excel.CalcEngine { @@ -16,7 +16,7 @@ ce.RegisterFunction("CHAR", 1, _Char); // Returns the character specified by the code number ce.RegisterFunction("CLEAN", 1, Clean); // Removes all nonprintable characters from text ce.RegisterFunction("CODE", 1, Code); // Returns a numeric code for the first character in a text string - ce.RegisterFunction("CONCATENATE", 1, int.MaxValue, Concat); // Joins several text items into one text item + ce.RegisterFunction("CONCATENATE", 1, int.MaxValue, Concatenate); // Joins several text items into one text item ce.RegisterFunction("DOLLAR", 1, 2, Dollar); // Converts a number to text, using the $ (dollar) currency format ce.RegisterFunction("EXACT", 2, Exact); // Checks to see if two text values are identical ce.RegisterFunction("FIND", 2, 3, Find); //Finds one text value within another (case-sensitive) @@ -31,7 +31,7 @@ ce.RegisterFunction("REPLACE", 4, Replace); // Replaces characters within text ce.RegisterFunction("REPT", 2, Rept); // Repeats text a given number of times ce.RegisterFunction("RIGHT", 1, 2, Right); // Returns the rightmost characters from a text value - ce.RegisterFunction("SEARCH", 2, Search); // Finds one text value within another (not case-sensitive) + ce.RegisterFunction("SEARCH", 2, 3, Search); // Finds one text value within another (not case-sensitive) ce.RegisterFunction("SUBSTITUTE", 3, 4, Substitute); // Substitutes new text for old text in a text string ce.RegisterFunction("T", 1, T); // Converts its arguments to text ce.RegisterFunction("TEXT", 2, _Text); // Formats a number and converts it to text @@ -41,17 +41,21 @@ ce.RegisterFunction("HYPERLINK", 1, Hyperlink); } - static object _Char(List p) + private static object _Char(List p) { - var c = (char)(int)p[0]; + var i = (int)p[0]; + if (i < 1 || i > 255) throw new IndexOutOfRangeException(); + var c = (char)i; return c.ToString(); } - static object Code(List p) + + private static object Code(List p) { var s = (string)p[0]; return (int)s[0]; } - static object Concat(List p) + + private static object Concatenate(List p) { var sb = new StringBuilder(); foreach (var x in p) @@ -60,11 +64,8 @@ } return sb.ToString(); } - static object Find(List p) - { - return IndexOf(p, StringComparison.Ordinal); - } - static int IndexOf(List p, StringComparison cmp) + + private static object Find(List p) { var srch = (string)p[0]; var text = (string)p[1]; @@ -73,10 +74,14 @@ { start = (int)p[2] - 1; } - var index = text.IndexOf(srch, start, cmp); - return index > -1 ? index + 1 : index; + var index = text.IndexOf(srch, start, StringComparison.Ordinal); + if (index == -1) + throw new Exception("String not found."); + else + return index + 1; } - static object Left(List p) + + private static object Left(List p) { var str = (string)p[0]; var n = 1; @@ -88,15 +93,18 @@ return str.Substring(0, n); } - static object Len(List p) + + private static object Len(List p) { return ((string)p[0]).Length; } - static object Lower(List p) + + private static object Lower(List p) { return ((string)p[0]).ToLower(); } - static object Mid(List p) + + private static object Mid(List p) { var str = (string)p[0]; var start = (int)p[1] - 1; @@ -107,12 +115,26 @@ return str.Substring(start); return str.Substring(start, length); } - static object Proper(List p) + + private static string MatchHandler(Match m) + { + return m.Groups[1].Value.ToUpper() + m.Groups[2].Value; + } + + private static object Proper(List p) { var s = (string)p[0]; - return s.Substring(0, 1).ToUpper() + s.Substring(1).ToLower(); + if (s.Length == 0) return ""; + + MatchEvaluator evaluator = new MatchEvaluator(MatchHandler); + StringBuilder sb = new StringBuilder(); + + string pattern = "\\b(\\w)(\\w+)?\\b"; + Regex regex = new Regex(pattern, RegexOptions.Multiline | RegexOptions.IgnoreCase); + return regex.Replace(s.ToLower(), evaluator); } - static object Replace(List p) + + private static object Replace(List p) { // old start len new var s = (string)p[0]; @@ -120,6 +142,8 @@ var len = (int)p[2]; var rep = (string)p[3]; + if (s.Length == 0) return rep; + var sb = new StringBuilder(); sb.Append(s.Substring(0, start)); sb.Append(rep); @@ -127,17 +151,21 @@ return sb.ToString(); } - static object Rept(List p) + + private static object Rept(List p) { var sb = new StringBuilder(); var s = (string)p[0]; - for (int i = 0; i < (int)p[1]; i++) + var repeats = (int)p[1]; + if (repeats < 0) throw new IndexOutOfRangeException(nameof(repeats)); + for (int i = 0; i < repeats; i++) { sb.Append(s); } return sb.ToString(); } - static object Right(List p) + + private static object Right(List p) { var str = (string)p[0]; var n = 1; @@ -145,22 +173,56 @@ { n = (int)p[1]; } - + if (n >= str.Length) return str; return str.Substring(str.Length - n); } - static object Search(List p) + + private static string WildcardToRegex(string pattern) { - return IndexOf(p, StringComparison.OrdinalIgnoreCase); + return Regex.Escape(pattern) + .Replace(".", "\\.") + .Replace("\\*", ".*") + .Replace("\\?", "."); } - static object Substitute(List p) + + private static object Search(List p) + { + var search = WildcardToRegex((string)p[0]); + var text = (string)p[1]; + + if ("" == text) throw new Exception("Invalid input string."); + + var start = 0; + if (p.Count > 2) + { + start = (int)p[2] - 1; + } + + Regex r = new Regex(search, RegexOptions.Compiled | RegexOptions.IgnoreCase); + var match = r.Match(text.Substring(start)); + if (!match.Success) + throw new Exception("Search failed."); + else + return match.Index + start + 1; + //var index = text.IndexOf(search, start, StringComparison.OrdinalIgnoreCase); + //if (index == -1) + // throw new Exception("String not found."); + //else + // return index + 1; + } + + private static object Substitute(List p) { // get parameters var text = (string)p[0]; var oldText = (string)p[1]; var newText = (string)p[2]; + if ("" == text) return ""; + if ("" == oldText) return text; + // if index not supplied, replace all if (p.Count == 3) { @@ -183,42 +245,61 @@ ? text.Substring(0, pos) + newText + text.Substring(pos + oldText.Length) : text; } - static object T(List p) + + private static object T(List p) { - return (string)p[0]; + if (p[0]._token.Value.GetType() == typeof(string)) + return (string)p[0]; + else + return ""; } - static object _Text(List p) + + private static object _Text(List p) { - return ((double)p[0]).ToString((string)p[1], CultureInfo.CurrentCulture); + var number = (double)p[0]; + var format = (string)p[1]; + if (string.IsNullOrEmpty(format.Trim())) return ""; + + // We'll have to guess as to whether the format represents a date and/or time. + // Not sure whether there's a better way to detect this. + bool isDateFormat = new string[] { "y", "m", "d", "h", "s" }.Any(part => format.ToLower().Contains(part.ToLower())); + + if (isDateFormat) + return DateTime.FromOADate(number).ToString(format, CultureInfo.CurrentCulture); + else + return number.ToString(format, CultureInfo.CurrentCulture); } - static object Trim(List p) + + private static object Trim(List p) { //Should not trim non breaking space //See http://office.microsoft.com/en-us/excel-help/trim-function-HP010062581.aspx return ((string)p[0]).Trim(' '); } - static object Upper(List p) + + private static object Upper(List p) { return ((string)p[0]).ToUpper(); } - static object Value(List p) + + private static object Value(List p) { return double.Parse((string)p[0], NumberStyles.Any, CultureInfo.InvariantCulture); } - static object Asc(List p) + private static object Asc(List p) { return (string)p[0]; } - static object Hyperlink(List p) + private static object Hyperlink(List p) { String address = p[0]; String toolTip = p.Count == 2 ? p[1] : String.Empty; return new XLHyperlink(address, toolTip); } - static object Clean(List p) + private static object Clean(List p) { var s = (string)p[0]; @@ -229,7 +310,8 @@ } return result.ToString(); } - static object Dollar(List p) + + private static object Dollar(List p) { Double value = p[0]; int dec = p.Count == 2 ? (int)p[1] : 2; @@ -237,7 +319,7 @@ return value.ToString("C" + dec); } - static object Exact(List p) + private static object Exact(List p) { var t1 = (string)p[0]; var t2 = (string)p[1]; @@ -245,17 +327,20 @@ return t1 == t2; } - static object Fixed(List p) + private static object Fixed(List p) { + if (p[0]._token.Value.GetType() == typeof(string)) + throw new ApplicationException("Input type can't be string"); + Double value = p[0]; - int dec = p.Count >= 2 ? (int)p[1] : 2; - Boolean com = p.Count != 3 || p[2]; + int decimal_places = p.Count >= 2 ? (int)p[1] : 2; + Boolean no_commas = p.Count == 3 && p[2]; - var retVal = value.ToString("N" + dec); - if (com) + var retVal = value.ToString("N" + decimal_places); + if (no_commas) + return retVal.Replace(",", String.Empty); + else return retVal; - - return retVal.Replace(",", String.Empty); } - } -} + } +} \ No newline at end of file diff --git a/ClosedXML_Tests/Excel/CalcEngine/FunctionsTests.cs b/ClosedXML_Tests/Excel/CalcEngine/FunctionsTests.cs index f43aa6a..4852726 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/FunctionsTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/FunctionsTests.cs @@ -124,7 +124,7 @@ actual = XLWorkbook.EvaluateExpr("Fixed(12345.123, 1)"); Assert.AreEqual("12,345.1", actual); - actual = XLWorkbook.EvaluateExpr("Fixed(12345.123, 1, FALSE)"); + actual = XLWorkbook.EvaluateExpr("Fixed(12345.123, 1, TRUE)"); Assert.AreEqual("12345.1", actual); } diff --git a/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs b/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs index ebb55e9..cac5476 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs @@ -1,12 +1,196 @@ -using System; -using ClosedXML.Excel; +using ClosedXML.Excel; using NUnit.Framework; +using System; +using System.Globalization; +using System.Threading; namespace ClosedXML_Tests.Excel.CalcEngine { [TestFixture] public class TextTests { + [TestFixtureSetUp] + public void Init() + { + // Make sure tests run on a deterministic culture + Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US"); + } + + [Test] + [ExpectedException] + public void Char_Empty_Input_String() + { + Object actual = XLWorkbook.EvaluateExpr(@"Char("""")"); + } + + [Test] + [ExpectedException] + public void Char_Input_Too_Large() + { + Object actual = XLWorkbook.EvaluateExpr(@"Char(9797)"); + } + + [Test] + public void Char_Value() + { + Object actual = XLWorkbook.EvaluateExpr(@"Char(97)"); + Assert.AreEqual("a", actual); + } + + [Test] + public void Clean_Empty_Input_String() + { + Object actual = XLWorkbook.EvaluateExpr(@"Clean("""")"); + Assert.AreEqual("", actual); + } + + [Test] + public void Clean_Value() + { + Object actual = XLWorkbook.EvaluateExpr(@"Clean(CHAR(9)&""Monthly report""&CHAR(10))"); + Assert.AreEqual("Monthly report", actual); + + actual = XLWorkbook.EvaluateExpr(@"Clean("" "")"); + Assert.AreEqual(" ", actual); + } + + [Test] + [ExpectedException] + public void Code_Empty_Input_String() + { + // Todo: more specific exception - ValueException? + Object actual = XLWorkbook.EvaluateExpr(@"Code("""")"); + } + + [Test] + public void Code_Value() + { + Object actual = XLWorkbook.EvaluateExpr(@"Code(""A"")"); + Assert.AreEqual(65, actual); + + actual = XLWorkbook.EvaluateExpr(@"Code(""BCD"")"); + Assert.AreEqual(66, actual); + } + + [Test] + public void Concat_Value() + { + Object actual = XLWorkbook.EvaluateExpr(@"Concatenate(""ABC"", ""123"")"); + Assert.AreEqual("ABC123", actual); + + actual = XLWorkbook.EvaluateExpr(@"Concatenate("""", ""123"")"); + Assert.AreEqual("123", actual); + } + + [Test] + [ExpectedException] + public void Dollar_Empty_Input_String() + { + Object actual = XLWorkbook.EvaluateExpr(@"Dollar("", 3)"); + } + + [Test] + public void Dollar_Value() + { + Object actual = XLWorkbook.EvaluateExpr(@"Dollar(123.54)"); + Assert.AreEqual("$123.54", actual); + + actual = XLWorkbook.EvaluateExpr(@"Dollar(123.54, 3)"); + Assert.AreEqual("$123.540", actual); + } + + [Test] + public void Exact_Empty_Input_String() + { + Object actual = XLWorkbook.EvaluateExpr(@"Exact("""", """")"); + Assert.AreEqual(true, actual); + } + + [Test] + public void Exact_Value() + { + Object actual = XLWorkbook.EvaluateExpr(@"Exact(""asdf"", ""asdf"")"); + Assert.AreEqual(true, actual); + + actual = XLWorkbook.EvaluateExpr(@"Exact(""asdf"", ""ASDF"")"); + Assert.AreEqual(false, actual); + + actual = XLWorkbook.EvaluateExpr(@"Exact(123, 123)"); + Assert.AreEqual(true, actual); + + actual = XLWorkbook.EvaluateExpr(@"Exact(321, 123)"); + Assert.AreEqual(false, actual); + } + + [Test] + [ExpectedException] + public void Find_Start_Position_Too_Large() + { + Object actual = XLWorkbook.EvaluateExpr(@"Find(""abc"", ""abcdef"", 10)"); + } + + [Test] + [ExpectedException] + public void Find_String_In_Another_Empty_String() + { + Object actual = XLWorkbook.EvaluateExpr(@"Find(""abc"", """")"); + } + + [Test] + [ExpectedException] + public void Find_String_Not_Found() + { + Object actual = XLWorkbook.EvaluateExpr(@"Find(""123"", ""asdf"")"); + } + + [Test] + [ExpectedException] + public void Find_Case_Sensitive_String_Not_Found() + { + // Find is case-sensitive + Object actual = XLWorkbook.EvaluateExpr(@"Find(""excel"", ""Microsoft Excel 2010"")"); + } + + [Test] + public void Find_Value() + { + Object actual = XLWorkbook.EvaluateExpr(@"Find(""Tuesday"", ""Today is Tuesday"")"); + Assert.AreEqual(10, actual); + + actual = XLWorkbook.EvaluateExpr(@"Find("""", """")"); + Assert.AreEqual(1, actual); + + actual = XLWorkbook.EvaluateExpr(@"Find("""", ""asdf"")"); + Assert.AreEqual(1, actual); + } + + [Test] + [ExpectedException] + public void Fixed_Input_Is_String() + { + Object actual = XLWorkbook.EvaluateExpr(@"Fixed(""asdf"")"); + } + + [Test] + public void Fixed_Value() + { + Object actual = XLWorkbook.EvaluateExpr(@"Fixed(17300.67, 4)"); + Assert.AreEqual("17,300.6700", actual); + + actual = XLWorkbook.EvaluateExpr(@"Fixed(17300.67, 2, TRUE)"); + Assert.AreEqual("17300.67", actual); + + actual = XLWorkbook.EvaluateExpr(@"Fixed(17300.67)"); + Assert.AreEqual("17,300.67", actual); + } + + [Test] + public void Left_Bigger_Than_Length() + { + Object actual = XLWorkbook.EvaluateExpr(@"Left(""ABC"", 5)"); + Assert.AreEqual("ABC", actual); + } + [Test] public void Left_Default() { @@ -15,6 +199,13 @@ } [Test] + public void Left_Empty_Input_String() + { + Object actual = XLWorkbook.EvaluateExpr(@"Left("""")"); + Assert.AreEqual("", actual); + } + + [Test] public void Left_Value() { Object actual = XLWorkbook.EvaluateExpr(@"Left(""ABC"", 2)"); @@ -22,20 +213,124 @@ } [Test] - public void Left_BiggerThanLength() + public void Len_Empty_Input_String() { - Object actual = XLWorkbook.EvaluateExpr(@"Left(""ABC"", 5)"); + Object actual = XLWorkbook.EvaluateExpr(@"Len("""")"); + Assert.AreEqual(0, actual); + } + + [Test] + public void Len_Value() + { + Object actual = XLWorkbook.EvaluateExpr(@"Len(""word"")"); + Assert.AreEqual(4, actual); + } + + [Test] + public void Lower_Empty_Input_String() + { + Object actual = XLWorkbook.EvaluateExpr(@"Lower("""")"); + Assert.AreEqual("", actual); + } + + [Test] + public void Lower_Value() + { + Object actual = XLWorkbook.EvaluateExpr(@"Lower(""AbCdEfG"")"); + Assert.AreEqual("abcdefg", actual); + } + + [Test] + public void Mid_Bigger_Than_Length() + { + Object actual = XLWorkbook.EvaluateExpr(@"Mid(""ABC"", 1, 5)"); Assert.AreEqual("ABC", actual); } [Test] - public void Left_Empty() + public void Mid_Empty_Input_String() { - Object actual = XLWorkbook.EvaluateExpr(@"Left("""")"); + Object actual = XLWorkbook.EvaluateExpr(@"Mid("""", 1, 1)"); Assert.AreEqual("", actual); } [Test] + public void Mid_Start_After() + { + Object actual = XLWorkbook.EvaluateExpr(@"Mid(""ABC"", 5, 5)"); + Assert.AreEqual("", actual); + } + + [Test] + public void Mid_Value() + { + Object actual = XLWorkbook.EvaluateExpr(@"Mid(""ABC"", 2, 2)"); + Assert.AreEqual("BC", actual); + } + + [Test] + public void Proper_Empty_Input_String() + { + Object actual = XLWorkbook.EvaluateExpr(@"Proper("""")"); + Assert.AreEqual("", actual); + } + + [Test] + public void Proper_Value() + { + Object actual = XLWorkbook.EvaluateExpr(@"Proper(""my name is francois botha"")"); + Assert.AreEqual("My Name Is Francois Botha", actual); + } + + [Test] + public void Replace_Empty_Input_String() + { + Object actual = XLWorkbook.EvaluateExpr(@"Replace("""", 1, 1, ""newtext"")"); + Assert.AreEqual("newtext", actual); + } + + [Test] + public void Replace_Value() + { + Object actual = XLWorkbook.EvaluateExpr(@"Replace(""Here is some obsolete text to replace."", 14, 13, ""new text"")"); + Assert.AreEqual("Here is some new text to replace.", actual); + } + + [Test] + public void Rept_Empty_Input_Strings() + { + Object actual = XLWorkbook.EvaluateExpr(@"Rept("""", 3)"); + Assert.AreEqual("", actual); + } + + [Test] + [ExpectedException] + public void Rept_Start_Is_Negative() + { + Object actual = XLWorkbook.EvaluateExpr(@"Rept(""Francois"", -1)"); + } + + [Test] + public void Rept_Value() + { + Object actual = XLWorkbook.EvaluateExpr(@"Rept(""Francois Botha,"", 3)"); + Assert.AreEqual("Francois Botha,Francois Botha,Francois Botha,", actual); + + actual = XLWorkbook.EvaluateExpr(@"Rept(""123"", 5/2)"); + Assert.AreEqual("123123", actual); + + actual = XLWorkbook.EvaluateExpr(@"Rept(""Francois"", 0)"); + Assert.AreEqual("", actual); + } + + [Test] + public void Right_Bigger_Than_Length() + { + Object actual = XLWorkbook.EvaluateExpr(@"Right(""ABC"", 5)"); + Assert.AreEqual("ABC", actual); + } + + [Test] public void Right_Default() { Object actual = XLWorkbook.EvaluateExpr(@"Right(""ABC"")"); @@ -43,6 +338,13 @@ } [Test] + public void Right_Empty_Input_String() + { + Object actual = XLWorkbook.EvaluateExpr(@"Right("""")"); + Assert.AreEqual("", actual); + } + + [Test] public void Right_Value() { Object actual = XLWorkbook.EvaluateExpr(@"Right(""ABC"", 2)"); @@ -50,47 +352,192 @@ } [Test] - public void Right_BiggerThanLength() + [ExpectedException] + public void Search_No_Parameters_With_Values() { - Object actual = XLWorkbook.EvaluateExpr(@"Right(""ABC"", 5)"); - Assert.AreEqual("ABC", actual); + Object actual = XLWorkbook.EvaluateExpr(@"Search("""", """")"); } [Test] - public void Right_Empty() + public void Search_Empty_Search_String() { - Object actual = XLWorkbook.EvaluateExpr(@"Right("""")"); + Object actual = XLWorkbook.EvaluateExpr(@"Search("""", ""asdf"")"); + Assert.AreEqual(1, actual); + } + + [Test] + [ExpectedException] + public void Search_Start_Position_Too_Large() + { + Object actual = XLWorkbook.EvaluateExpr(@"Search(""abc"", ""abcdef"", 10)"); + } + + [Test] + [ExpectedException] + public void Search_Empty_Input_String() + { + Object actual = XLWorkbook.EvaluateExpr(@"Search(""abc"", """")"); + } + + [Test] + [ExpectedException] + public void Search_String_Not_Found() + { + Object actual = XLWorkbook.EvaluateExpr(@"Search(""123"", ""asdf"")"); + } + + [Test] + [ExpectedException] + public void Search_Wildcard_String_Not_Found() + { + Object actual = XLWorkbook.EvaluateExpr(@"Search(""soft?2010"", ""Microsoft Excel 2010"")"); + } + + [Test] + [ExpectedException] + public void Search_Start_Position_Too_Large2() + { + Object actual = XLWorkbook.EvaluateExpr(@"Search(""text"", ""This is some text"", 15)"); + } + + // http://www.excel-easy.com/examples/find-vs-search.html + [Test] + public void Search_Value() + { + Object actual = XLWorkbook.EvaluateExpr(@"Search(""Tuesday"", ""Today is Tuesday"")"); + Assert.AreEqual(10, actual); + + // Find is case-INsensitive + actual = XLWorkbook.EvaluateExpr(@"Search(""excel"", ""Microsoft Excel 2010"")"); + Assert.AreEqual(11, actual); + + actual = XLWorkbook.EvaluateExpr(@"Search(""soft*2010"", ""Microsoft Excel 2010"")"); + Assert.AreEqual(6, actual); + + actual = XLWorkbook.EvaluateExpr(@"Search(""Excel 20??"", ""Microsoft Excel 2010"")"); + Assert.AreEqual(11, actual); + + actual = XLWorkbook.EvaluateExpr(@"Search(""text"", ""This is some text"", 14)"); + Assert.AreEqual(14, actual); + } + + [Test] + public void Substitute_Value() + { + Object actual = XLWorkbook.EvaluateExpr(@"Substitute(""This is a Tuesday."", ""Tuesday"", ""Monday"")"); + Assert.AreEqual("This is a Monday.", actual); + + actual = XLWorkbook.EvaluateExpr(@"Substitute(""This is a Tuesday. Next week also has a Tuesday."", ""Tuesday"", ""Monday"", 1)"); + Assert.AreEqual("This is a Monday. Next week also has a Tuesday.", actual); + + actual = XLWorkbook.EvaluateExpr(@"Substitute(""This is a Tuesday. Next week also has a Tuesday."", ""Tuesday"", ""Monday"", 2)"); + Assert.AreEqual("This is a Tuesday. Next week also has a Monday.", actual); + + actual = XLWorkbook.EvaluateExpr(@"Substitute("""", """", ""Monday"")"); Assert.AreEqual("", actual); - } + actual = XLWorkbook.EvaluateExpr(@"Substitute(""This is a Tuesday. Next week also has a Tuesday."", """", ""Monday"")"); + Assert.AreEqual("This is a Tuesday. Next week also has a Tuesday.", actual); - - [Test] - public void Mid_Value() - { - Object actual = XLWorkbook.EvaluateExpr(@"Mid(""ABC"", 2, 2)"); - Assert.AreEqual("BC", actual); + actual = XLWorkbook.EvaluateExpr(@"Substitute(""This is a Tuesday. Next week also has a Tuesday."", ""Tuesday"", """")"); + Assert.AreEqual("This is a . Next week also has a .", actual); } [Test] - public void Mid_BiggerThanLength() + public void T_Empty_Input_String() { - Object actual = XLWorkbook.EvaluateExpr(@"Mid(""ABC"", 1, 5)"); - Assert.AreEqual("ABC", actual); - } - - [Test] - public void Mid_StartAfter() - { - Object actual = XLWorkbook.EvaluateExpr(@"Mid(""ABC"", 5, 5)"); + Object actual = XLWorkbook.EvaluateExpr(@"T("""")"); Assert.AreEqual("", actual); } [Test] - public void Mid_Empty() + public void T_Value() { - Object actual = XLWorkbook.EvaluateExpr(@"Mid("""", 1, 1)"); + Object actual = XLWorkbook.EvaluateExpr(@"T(""asdf"")"); + Assert.AreEqual("asdf", actual); + + actual = XLWorkbook.EvaluateExpr(@"T(Today())"); Assert.AreEqual("", actual); + + actual = XLWorkbook.EvaluateExpr(@"T(TRUE)"); + Assert.AreEqual("", actual); + } + + [Test] + public void Text_Empty_Input_String() + { + Object actual = XLWorkbook.EvaluateExpr(@"Text(1913415.93, """")"); + Assert.AreEqual("", actual); + } + + [Test] + public void Text_Value() + { + Object actual = XLWorkbook.EvaluateExpr(@"Text(Date(2010, 1, 1), ""yyyy-MM-dd"")"); + Assert.AreEqual("2010-01-01", actual); + + actual = XLWorkbook.EvaluateExpr(@"Text(1469.07, ""0,000,000.00"")"); + Assert.AreEqual("0,001,469.07", actual); + + actual = XLWorkbook.EvaluateExpr(@"Text(1913415.93, ""#,000.00"")"); + Assert.AreEqual("1,913,415.93", actual); + + actual = XLWorkbook.EvaluateExpr(@"Text(2800, ""$0.00"")"); + Assert.AreEqual("$2800.00", actual); + + actual = XLWorkbook.EvaluateExpr(@"Text(0.4, ""0%"")"); + Assert.AreEqual("40%", actual); + + actual = XLWorkbook.EvaluateExpr(@"Text(Date(2010, 1, 1), ""MMMM yyyy"")"); + Assert.AreEqual("January 2010", actual); + + actual = XLWorkbook.EvaluateExpr(@"Text(Date(2010, 1, 1), ""M/d/y"")"); + Assert.AreEqual("1/1/10", actual); + } + + [Test] + public void Trim_EmptyInput_Striong() + { + Object actual = XLWorkbook.EvaluateExpr(@"Trim("""")"); + Assert.AreEqual("", actual); + } + + [Test] + public void Trim_Value() + { + Object actual = XLWorkbook.EvaluateExpr(@"Trim("" some text with padding "")"); + Assert.AreEqual("some text with padding", actual); + } + + [Test] + public void Upper_Empty_Input_String() + { + Object actual = XLWorkbook.EvaluateExpr(@"Upper("""")"); + Assert.AreEqual("", actual); + } + + [Test] + public void Upper_Value() + { + Object actual = XLWorkbook.EvaluateExpr(@"Upper(""AbCdEfG"")"); + Assert.AreEqual("ABCDEFG", actual); + } + + [Test] + [ExpectedException] + public void Value_Input_String_Is_Not_A_Number() + { + Object actual = XLWorkbook.EvaluateExpr(@"Value(""asdf"")"); + } + + [Test] + public void Value_Value() + { + Object actual = XLWorkbook.EvaluateExpr(@"Value(""123.54"")"); + Assert.AreEqual(123.54, actual); + + actual = XLWorkbook.EvaluateExpr(@"Value(654.32)"); + Assert.AreEqual(654.32, actual); } } } \ No newline at end of file