diff --git a/ClosedXML/Excel/CalcEngine/CalcEngine.cs b/ClosedXML/Excel/CalcEngine/CalcEngine.cs index e9c2405..def655b 100644 --- a/ClosedXML/Excel/CalcEngine/CalcEngine.cs +++ b/ClosedXML/Excel/CalcEngine/CalcEngine.cs @@ -256,9 +256,9 @@ { ["#REF!"] = ErrorExpression.ExpressionErrorType.CellReference, ["#VALUE!"] = ErrorExpression.ExpressionErrorType.CellValue, - ["#DIV/0!"] = ErrorExpression.ExpressionErrorType.DivideByZero, + ["#DIV/0!"] = ErrorExpression.ExpressionErrorType.DivisionByZero, ["#NAME?"] = ErrorExpression.ExpressionErrorType.NameNotRecognized, - ["#N/A!"] = ErrorExpression.ExpressionErrorType.NoValueAvailable, + ["#N/A"] = ErrorExpression.ExpressionErrorType.NoValueAvailable, ["#NULL!"] = ErrorExpression.ExpressionErrorType.NullValue, ["#NUM!"] = ErrorExpression.ExpressionErrorType.NumberInvalid }; diff --git a/ClosedXML/Excel/CalcEngine/Expression.cs b/ClosedXML/Excel/CalcEngine/Expression.cs index 2936a4c..fca1cb5 100644 --- a/ClosedXML/Excel/CalcEngine/Expression.cs +++ b/ClosedXML/Excel/CalcEngine/Expression.cs @@ -463,7 +463,7 @@ { CellReference, CellValue, - DivideByZero, + DivisionByZero, NameNotRecognized, NoValueAvailable, NullValue, @@ -489,7 +489,7 @@ throw new CellReferenceException(); case ExpressionErrorType.CellValue: throw new CellValueException(); - case ExpressionErrorType.DivideByZero: + case ExpressionErrorType.DivisionByZero: throw new DivisionByZeroException(); case ExpressionErrorType.NameNotRecognized: throw new NameNotRecognizedException(); diff --git a/ClosedXML/Excel/CalcEngine/Functions/Information.cs b/ClosedXML/Excel/CalcEngine/Functions/Information.cs index 6df1a1e..836ebf1 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Information.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Information.cs @@ -1,3 +1,4 @@ +using ClosedXML.Excel.CalcEngine.Exceptions; using System; using System.Collections.Generic; using System.Globalization; @@ -9,27 +10,42 @@ public static void Register(CalcEngine ce) { //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); - 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("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); + ce.RegisterFunction("N", 1, N); + ce.RegisterFunction("NA", 0, NA); + ce.RegisterFunction("TYPE", 1, Type); } + static IDictionary errorTypes = new Dictionary() + { + [ErrorExpression.ExpressionErrorType.NullValue] = 1, + [ErrorExpression.ExpressionErrorType.DivisionByZero] = 2, + [ErrorExpression.ExpressionErrorType.CellValue] = 3, + [ErrorExpression.ExpressionErrorType.CellReference] = 4, + [ErrorExpression.ExpressionErrorType.NameNotRecognized] = 5, + [ErrorExpression.ExpressionErrorType.NumberInvalid] = 6, + [ErrorExpression.ExpressionErrorType.NoValueAvailable] = 7 + }; + static object ErrorType(List p) { - //TODO: Write Code - throw new NotSupportedException();; + var v = p[0].Evaluate(); + + if (v is ErrorExpression.ExpressionErrorType) + return errorTypes[(ErrorExpression.ExpressionErrorType)v]; + else + throw new NoValueAvailableException(); } static object IsBlank(List p) @@ -46,17 +62,19 @@ return isBlank; } - //TODO: Support for Error Values static object IsErr(List p) { - //TODO: Write Code - throw new NotSupportedException(); + var v = p[0].Evaluate(); + + return v is ErrorExpression.ExpressionErrorType + && ((ErrorExpression.ExpressionErrorType)v) != ErrorExpression.ExpressionErrorType.NoValueAvailable; } - + static object IsError(List p) { - //TODO: Write Code - throw new NotSupportedException(); + var v = p[0].Evaluate(); + + return v is ErrorExpression.ExpressionErrorType; } static object IsEven(List p) @@ -74,7 +92,7 @@ { var v = p[0].Evaluate(); var isLogical = v is bool; - + if (isLogical && p.Count > 1) { var sublist = p.GetRange(1, p.Count); @@ -86,8 +104,10 @@ static object IsNa(List p) { - //TODO: Write Code - throw new NotSupportedException();; + var v = p[0].Evaluate(); + + return v is ErrorExpression.ExpressionErrorType + && ((ErrorExpression.ExpressionErrorType)v) == ErrorExpression.ExpressionErrorType.NoValueAvailable; } static object IsNonText(List p) @@ -118,8 +138,8 @@ isNumber = false; } } - - if (isNumber && p.Count > 1) + + if (isNumber && p.Count > 1) { var sublist = p.GetRange(1, p.Count); isNumber = (bool)IsNumber(sublist); @@ -135,8 +155,13 @@ static object IsRef(List p) { - //TODO: Write Code - throw new NotSupportedException();; + var oe = p[0] as XObjectExpression; + if (oe == null) + return false; + + var crr = oe.Value as CellRangeReference; + + return crr != null; } static object IsText(List p) @@ -161,8 +186,7 @@ static object NA(List p) { - //TODO: Write Code - throw new NotSupportedException();; + return ErrorExpression.ExpressionErrorType.NoValueAvailable; } static object Type(List p) @@ -190,4 +214,4 @@ return null; } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/CalcEngine/InformationTests.cs b/ClosedXML_Tests/Excel/CalcEngine/InformationTests.cs index 93ad203..19de6ca 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/InformationTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/InformationTests.cs @@ -158,6 +158,17 @@ } #endregion IsLogical Tests + [Test] + public void IsNA() + { + object actual; + actual = XLWorkbook.EvaluateExpr("ISNA(#N/A)"); + Assert.AreEqual(true, actual); + + actual = XLWorkbook.EvaluateExpr("ISNA(#REF!)"); + Assert.AreEqual(false, actual); + } + #region IsNotText Tests [Test] @@ -288,6 +299,30 @@ } #endregion IsOdd Test + [Test] + public void IsRef() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet"); + ws.Cell("A1").Value = "123"; + + ws.Cell("B1").FormulaA1 = "ISREF(A1)"; + ws.Cell("B2").FormulaA1 = "ISREF(5)"; + ws.Cell("B3").FormulaA1 = "ISREF(YEAR(TODAY()))"; + + bool actual; + actual = ws.Cell("B1").GetValue(); + Assert.AreEqual(true, actual); + + actual = ws.Cell("B2").GetValue(); + Assert.AreEqual(false, actual); + + actual = ws.Cell("B3").GetValue(); + Assert.AreEqual(false, actual); + } + } + #region IsText Tests [Test] diff --git a/ClosedXML_Tests/Excel/Misc/FormulaTests.cs b/ClosedXML_Tests/Excel/Misc/FormulaTests.cs index 5f49caa..0ba9217 100644 --- a/ClosedXML_Tests/Excel/Misc/FormulaTests.cs +++ b/ClosedXML_Tests/Excel/Misc/FormulaTests.cs @@ -165,7 +165,7 @@ Assert.Throws(() => XLWorkbook.EvaluateExpr("YEAR(#VALUE!)")); Assert.Throws(() => XLWorkbook.EvaluateExpr("YEAR(#DIV/0!)")); Assert.Throws(() => XLWorkbook.EvaluateExpr("YEAR(#NAME?)")); - Assert.Throws(() => XLWorkbook.EvaluateExpr("YEAR(#N/A!)")); + Assert.Throws(() => XLWorkbook.EvaluateExpr("YEAR(#N/A)")); Assert.Throws(() => XLWorkbook.EvaluateExpr("YEAR(#NULL!)")); Assert.Throws(() => XLWorkbook.EvaluateExpr("YEAR(#NUM!)")); }