diff --git a/ClosedXML/Excel/CalcEngine/Expression.cs b/ClosedXML/Excel/CalcEngine/Expression.cs index 3e2c9bc..831db51 100644 --- a/ClosedXML/Excel/CalcEngine/Expression.cs +++ b/ClosedXML/Excel/CalcEngine/Expression.cs @@ -97,7 +97,7 @@ } // handle nulls - if (v == null || v is String) + if (v == null || v is string) { return 0; } diff --git a/ClosedXML/Excel/CalcEngine/Functions/Information.cs b/ClosedXML/Excel/CalcEngine/Functions/Information.cs index da9e472..2af09f9 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Information.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Information.cs @@ -1,14 +1,15 @@ using System; using System.Collections.Generic; -using System.Drawing.Design; +using System.Globalization; -namespace ClosedXML.Excel.CalcEngine +namespace ClosedXML.Excel.CalcEngine.Functions { internal static class Information { public static void Register(CalcEngine ce) { - ce.RegisterFunction("ERRORTYPE",1,ErrorType); + //TODO: Add documentation + ce.RegisterFunction("ERRORTYPE",1,ErrorType); ce.RegisterFunction("ISBLANK", 1,int.MaxValue, IsBlank); ce.RegisterFunction("ISERR",1, int.MaxValue, IsErr); ce.RegisterFunction("ISERROR",1, int.MaxValue, IsError); @@ -19,7 +20,7 @@ ce.RegisterFunction("ISNUMBER",1, int.MaxValue, IsNumber); ce.RegisterFunction("ISODD",1,IsOdd); ce.RegisterFunction("ISREF",1, int.MaxValue, IsRef); - ce.RegisterFunction("ISTEXT",1, int.MaxValue, IsText); + ce.RegisterFunction("ISTEXT", 1, int.MaxValue, IsText); ce.RegisterFunction("N",1,N); ce.RegisterFunction("NA",0,NA); ce.RegisterFunction("TYPE",1,Type); @@ -33,12 +34,13 @@ static object IsBlank(List p) { - var v = (string) p[0].Evaluate(); + var v = (string) p[0]; var isBlank = string.IsNullOrEmpty(v); - p.RemoveAt(0); - if (isBlank && p.Count > 0) { - isBlank = (bool)IsBlank(p); + + if (isBlank && p.Count > 1) { + var sublist = p.GetRange(1, p.Count); + isBlank = (bool)IsBlank(sublist); } return isBlank; @@ -62,8 +64,9 @@ var v = p[0].Evaluate(); if (v is double) { - return Math.Abs((double) v%2) < 0; + return Math.Abs((double) v%2) < 1; } + //TODO: Error Exceptions throw new ArgumentException("Expression doesn't evaluate to double"); } @@ -71,11 +74,11 @@ { var v = p[0].Evaluate(); var isLogical = v is bool; - p.RemoveAt(0); - - if (isLogical && p.Count > 0) + + if (isLogical && p.Count > 1) { - isLogical = (bool) IsLogical(p); + var sublist = p.GetRange(1, p.Count); + isLogical = (bool) IsLogical(sublist); } return isLogical; @@ -95,11 +98,31 @@ static object IsNumber(List p) { var v = p[0].Evaluate(); - var isNumber = v is double; - p.RemoveAt(0); - if (isNumber && p.Count > 0) { - isNumber = (bool)IsNumber(p); + var isNumber = v is double; //Normal number formatting + if (!isNumber) + { + isNumber = v is DateTime; //Handle DateTime Format + } + if (!isNumber) + { + //Handle Number Styles + try + { + var stringValue = (string) v; + double.Parse(stringValue.TrimEnd('%', ' '), NumberStyles.Any); + isNumber = true; + } + catch (Exception) + { + isNumber = false; + } + } + + if (isNumber && p.Count > 1) + { + var sublist = p.GetRange(1, p.Count); + isNumber = (bool)IsNumber(sublist); } return isNumber; @@ -133,8 +156,7 @@ static object N(List p) { - //TODO: Write Code - throw new NotSupportedException();; + return (double) p[0]; } static object NA(List p) @@ -145,8 +167,27 @@ static object Type(List p) { - //TODO: Write Code - throw new NotSupportedException();; + if ((bool) IsNumber(p)) + { + return 1; + } + if ((bool) IsText(p)) + { + return 2; + } + if ((bool) IsLogical(p)) + { + return 4; + } + if ((bool) IsError(p)) + { + return 16; + } + if(p.Count > 1) + { + return 64; + } + return null; } } } \ No newline at end of file diff --git a/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj b/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj index dc7a265..4697852 100644 --- a/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj +++ b/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj @@ -109,9 +109,6 @@ Excel\CalcEngine\Functions\DateAndTime.cs - - Excel\CalcEngine\Functions\Is.cs - Excel\CalcEngine\Functions\Logical.cs @@ -823,6 +820,7 @@ XLHelper.cs + diff --git a/ClosedXML_Net3.5/Excel/CalcEngine/Functions/Information.cs b/ClosedXML_Net3.5/Excel/CalcEngine/Functions/Information.cs new file mode 100644 index 0000000..dcb1340 --- /dev/null +++ b/ClosedXML_Net3.5/Excel/CalcEngine/Functions/Information.cs @@ -0,0 +1,152 @@ +using System; +using System.Collections.Generic; +using System.Drawing.Design; + +namespace ClosedXML.Excel.CalcEngine.Functions +{ + internal static class Information + { + public static void Register(CalcEngine ce) + { + ce.RegisterFunction("ERRORTYPE",1,ErrorType); + ce.RegisterFunction("ISBLANK", 1,int.MaxValue, IsBlank); + ce.RegisterFunction("ISERR",1, int.MaxValue, IsErr); + ce.RegisterFunction("ISERROR",1, int.MaxValue, IsError); + ce.RegisterFunction("ISEVEN",1, IsEven); + ce.RegisterFunction("ISLOGICAL",1,int.MaxValue,IsLogical); + ce.RegisterFunction("ISNA",1, int.MaxValue, IsNa); + ce.RegisterFunction("ISNONTEXT",1, int.MaxValue, IsNonText); + ce.RegisterFunction("ISNUMBER",1, int.MaxValue, IsNumber); + ce.RegisterFunction("ISODD",1,IsOdd); + ce.RegisterFunction("ISREF",1, int.MaxValue, IsRef); + ce.RegisterFunction("ISTEXT",1, int.MaxValue, IsText); + ce.RegisterFunction("N",1,N); + ce.RegisterFunction("NA",0,NA); + ce.RegisterFunction("TYPE",1,Type); + } + + static object ErrorType(List p) + { + //TODO: Write Code + throw new NotSupportedException();; + } + + static object IsBlank(List p) + { + var v = (string) p[0].Evaluate(); + var isBlank = string.IsNullOrEmpty(v); + p.RemoveAt(0); + + if (isBlank && p.Count > 0) { + isBlank = (bool)IsBlank(p); + } + + return isBlank; + } + + //TODO: Support for Error Values + static object IsErr(List p) + { + //TODO: Write Code + throw new NotSupportedException(); + } + + static object IsError(List p) + { + //TODO: Write Code + throw new NotSupportedException(); + } + + static object IsEven(List p) + { + var v = p[0].Evaluate(); + if (v is double) + { + return Math.Abs((double) v%2) < 0; + } + throw new ArgumentException("Expression doesn't evaluate to double"); + } + + static object IsLogical(List p) + { + var v = p[0].Evaluate(); + var isLogical = v is bool; + p.RemoveAt(0); + + if (isLogical && p.Count > 0) + { + isLogical = (bool) IsLogical(p); + } + + return isLogical; + } + + static object IsNa(List p) + { + //TODO: Write Code + throw new NotSupportedException();; + } + + static object IsNonText(List p) + { + return !(bool) IsText(p); + } + + static object IsNumber(List p) + { + var v = p[0].Evaluate(); + var isNumber = v is double; + p.RemoveAt(0); + + if (isNumber && p.Count > 0) { + isNumber = (bool)IsNumber(p); + } + + return isNumber; + } + + static object IsOdd(List p) + { + return !(bool) IsEven(p); + } + + static object IsRef(List p) + { + //TODO: Write Code + throw new NotSupportedException();; + } + + static object IsText(List p) + { + //Evaluate Expressions + var isText = !(bool) IsBlank(p); + if (isText) + { + isText = !(bool) IsNumber(p); + } + if (isText) + { + isText = !(bool) IsLogical(p); + } + return isText; + } + + static object N(List p) + { + //TODO: Write Code + throw new NotSupportedException();; + } + + static object NA(List p) + { + //TODO: Write Code + throw new NotSupportedException();; + } + + static object Type(List p) + { + //TODO: Write Code + throw new NotSupportedException();; + } + } +} \ No newline at end of file diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index cd9b3c0..b4b6c83 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -70,7 +70,7 @@ - + diff --git a/ClosedXML_Tests/Excel/CalcEngine/InformationTests.cs b/ClosedXML_Tests/Excel/CalcEngine/InformationTests.cs new file mode 100644 index 0000000..2bb46a3 --- /dev/null +++ b/ClosedXML_Tests/Excel/CalcEngine/InformationTests.cs @@ -0,0 +1,331 @@ +using System; +using System.Runtime.InteropServices; +using ClosedXML.Excel; +using NUnit.Framework; + +namespace ClosedXML_Tests.Excel.CalcEngine +{ + + [TestFixture] + public class InformationTests + { + + #region IsBlank Tests + [Test] + public void IsBlank_Single_true() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + var actual = ws.Evaluate("=IsBlank(A1)"); + Assert.AreEqual(true, actual); + } + + [Test] + public void IsBlank_Single_false() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + ws.Cell("A1").Value = " "; + var actual = ws.Evaluate("=IsBlank(A1)"); + Assert.AreEqual(false, actual); + } + + [Test] + public void IsBlank_MultipleAllEmpty_true() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + var actual = ws.Evaluate("=IsBlank(A1:A3)"); + Assert.AreEqual(true,actual); + + } + + [Test] + public void IsBlank_MultipleAllFill_false() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + ws.Cell("A1").Value = "1"; + ws.Cell("A2").Value = "1"; + ws.Cell("A3").Value = "1"; + var actual = ws.Evaluate("=IsBlank(A1:A3)"); + Assert.AreEqual(false,actual); + } + + [Test] + public void IsBlank_MultipleMixedFill_false() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + ws.Cell("A1").Value = "1"; + ws.Cell("A3").Value = "1"; + var actual = ws.Evaluate("=IsBlank(A1:A3)"); + Assert.AreEqual(false, actual); + } + #endregion + + #region IsEven Tests + + [Test] + public void IsEven_Single_False() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + + ws.Cell("A1").Value = 1; + ws.Cell("A2").Value = 1.2; + ws.Cell("A3").Value = 3; + + var actual = ws.Evaluate("=IsEven(A1)"); + Assert.AreEqual(false,actual); + + actual = ws.Evaluate("=IsEven(A2)"); + Assert.AreEqual(false, actual); + + actual = ws.Evaluate("=IsEven(A3)"); + Assert.AreEqual(false, actual); + } + + [Test] + public void IsEven_Single_True() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + + ws.Cell("A1").Value = 4; + ws.Cell("A2").Value = 0.2; + ws.Cell("A3").Value = 12.2; + + var actual = ws.Evaluate("=IsEven(A1)"); + Assert.AreEqual(true, actual); + + actual = ws.Evaluate("=IsEven(A2)"); + Assert.AreEqual(true, actual); + + actual = ws.Evaluate("=IsEven(A3)"); + Assert.AreEqual(true, actual); + } + #endregion + + #region IsLogical Tests + + [Test] + public void IsLogical_Simple_True() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + + ws.Cell("A1").Value = true; + + var actual = ws.Evaluate("=IsLogical(A1)"); + Assert.AreEqual(true, actual); + } + + [Test] + public void IsLogical_Simpe_False() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + + ws.Cell("A1").Value = 123; + + var actual = ws.Evaluate("=IsLogical(A1)"); + Assert.AreEqual(false, actual); + } + #endregion + + #region IsNotText Tests + + [Test] + public void IsNotText_Simple_true() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + ws.Cell("A1").Value = "123"; //Double Value + ws.Cell("A2").Value = DateTime.Now; //Date Value + ws.Cell("A3").Value = "12,235.5"; //Coma Formatting + ws.Cell("A4").Value = "$12,235.5"; //Currency Value + ws.Cell("A5").Value = true; //Bool Value + ws.Cell("A6").Value = "12%"; //Percentage Value + + var actual = ws.Evaluate("=IsNonText(A1)"); + Assert.AreEqual(true, actual); + actual = ws.Evaluate("=IsNonText(A2)"); + Assert.AreEqual(true, actual); + actual = ws.Evaluate("=IsNonText(A3)"); + Assert.AreEqual(true, actual); + actual = ws.Evaluate("=IsNonText(A4)"); + Assert.AreEqual(true, actual); + actual = ws.Evaluate("=IsNonText(A5)"); + Assert.AreEqual(true, actual); + actual = ws.Evaluate("=IsNonText(A6)"); + Assert.AreEqual(true, actual); + } + + [Test] + public void IsNotText_Simple_false() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + ws.Cell("A1").Value = "asd"; + var actual = ws.Evaluate("=IsNonText(A1)"); + Assert.AreEqual(false, actual); + } + + #endregion + + #region IsNumber Tests + + [Test] + public void IsNumber_Simple_true() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + ws.Cell("A1").Value = "123"; //Double Value + ws.Cell("A2").Value = DateTime.Now; //Date Value + ws.Cell("A3").Value = "12,235.5"; //Coma Formatting + ws.Cell("A4").Value = "$12,235.5"; //Currency Value + ws.Cell("A5").Value = "12%"; //Percentage Value + + var actual = ws.Evaluate("=IsNumber(A1)"); + Assert.AreEqual(true, actual); + actual = ws.Evaluate("=IsNumber(A2)"); + Assert.AreEqual(true, actual); + actual = ws.Evaluate("=IsNumber(A3)"); + Assert.AreEqual(true, actual); + actual = ws.Evaluate("=IsNumber(A4)"); + Assert.AreEqual(true, actual); + actual = ws.Evaluate("=IsNumber(A5)"); + Assert.AreEqual(true, actual); + } + + [Test] + public void IsNumber_Simple_false() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + ws.Cell("A1").Value = "asd"; //String Value + ws.Cell("A2").Value = true; //Bool Value + + var actual = ws.Evaluate("=IsNumber(A1)"); + Assert.AreEqual(false, actual); + actual = ws.Evaluate("=IsNumber(A2)"); + Assert.AreEqual(false, actual); + } + #endregion + + #region IsOdd Test + + [Test] + public void IsOdd_Simple_true() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + + ws.Cell("A1").Value = 1; + ws.Cell("A2").Value = 1.2; + ws.Cell("A3").Value = 3; + + var actual = ws.Evaluate("=IsOdd(A1)"); + Assert.AreEqual(true, actual); + actual = ws.Evaluate("=IsOdd(A2)"); + Assert.AreEqual(true, actual); + actual = ws.Evaluate("=IsOdd(A3)"); + Assert.AreEqual(true, actual); + } + + [Test] + public void IsOdd_Simple_false() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + + ws.Cell("A1").Value = 4; + ws.Cell("A2").Value = 0.2; + ws.Cell("A3").Value = 12.2; + + var actual = ws.Evaluate("=IsOdd(A1)"); + Assert.AreEqual(false, actual); + actual = ws.Evaluate("=IsOdd(A2)"); + Assert.AreEqual(false, actual); + actual = ws.Evaluate("=IsOdd(A3)"); + Assert.AreEqual(false, actual); + } + #endregion + + #region IsText Tests + [Test] + public void IsText_Simple_true() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + + ws.Cell("A1").Value = "asd"; + + var actual = ws.Evaluate("=IsText(A1)"); + Assert.AreEqual(true, actual); + } + + [Test] + public void IsText_Simple_false() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + ws.Cell("A1").Value = "123"; //Double Value + ws.Cell("A2").Value = DateTime.Now; //Date Value + ws.Cell("A3").Value = "12,235.5"; //Coma Formatting + ws.Cell("A4").Value = "$12,235.5"; //Currency Value + ws.Cell("A5").Value = true; //Bool Value + ws.Cell("A6").Value = "12%"; //Percentage Value + + var actual = ws.Evaluate("=IsText(A1)"); + Assert.AreEqual(false, actual); + actual = ws.Evaluate("=IsText(A2)"); + Assert.AreEqual(false, actual); + actual = ws.Evaluate("=IsText(A3)"); + Assert.AreEqual(false, actual); + actual = ws.Evaluate("=IsText(A4)"); + Assert.AreEqual(false, actual); + actual = ws.Evaluate("=IsText(A5)"); + Assert.AreEqual(false, actual); + actual = ws.Evaluate("=IsText(A6)"); + Assert.AreEqual(false, actual); + } + #endregion + + #region N Tests + + [Test] + public void N_Date_SerialNumber() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + var testedDate = DateTime.Now; + ws.Cell("A1").Value = testedDate; + var actual = ws.Evaluate("=N(A1)"); + Assert.AreEqual(testedDate.ToOADate(),actual); + } + + [Test] + public void N_Number_Number() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + var testedValue = 123; + ws.Cell("A1").Value = testedValue; + var actual = ws.Evaluate("=N(A1)"); + Assert.AreEqual(testedValue, actual); + } + + [Test] + public void N_String_Zero() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + ws.Cell("A1").Value = "asd"; + var actual = ws.Evaluate("=N(A1)"); + Assert.AreEqual(0, actual); + } + + [Test] + public void N_True_One() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + ws.Cell("A1").Value = true; + var actual = ws.Evaluate("=N(A1)"); + Assert.AreEqual(1, actual); + } + + [Test] + public void N_False_Zero() + { + var ws = new XLWorkbook().AddWorksheet("Sheet"); + ws.Cell("A1").Value = false; + var actual = ws.Evaluate("=N(A1)"); + Assert.AreEqual(0, actual); + } + #endregion + + + } +} \ No newline at end of file diff --git a/ClosedXML_Tests/Excel/CalcEngine/IsTests.cs b/ClosedXML_Tests/Excel/CalcEngine/IsTests.cs deleted file mode 100644 index 2d98c68..0000000 --- a/ClosedXML_Tests/Excel/CalcEngine/IsTests.cs +++ /dev/null @@ -1,28 +0,0 @@ -using System; -using ClosedXML.Excel; -using NUnit.Framework; - -namespace ClosedXML_Tests.Excel.CalcEngine -{ - - [TestFixture] - public class IsTests - { - [Test] - public void IsBlank_true() - { - var ws = new XLWorkbook().AddWorksheet("Sheet"); - var actual = ws.Evaluate("=IsBlank(A1)"); - Assert.AreEqual(true, actual); - } - - [Test] - public void IsBlank_false() - { - var ws = new XLWorkbook().AddWorksheet("Sheet"); - ws.Cell("A1").Value = " "; - var actual = ws.Evaluate("=IsBlank(A1)"); - Assert.AreEqual(false, actual); - } - } -} \ No newline at end of file