diff --git a/ClosedXML/Excel/CalcEngine/CalcEngine.cs b/ClosedXML/Excel/CalcEngine/CalcEngine.cs index 71e3d72..e9c2405 100644 --- a/ClosedXML/Excel/CalcEngine/CalcEngine.cs +++ b/ClosedXML/Excel/CalcEngine/CalcEngine.cs @@ -252,6 +252,18 @@ #region ** token/keyword tables + private static readonly IDictionary ErrorMap = new Dictionary() + { + ["#REF!"] = ErrorExpression.ExpressionErrorType.CellReference, + ["#VALUE!"] = ErrorExpression.ExpressionErrorType.CellValue, + ["#DIV/0!"] = ErrorExpression.ExpressionErrorType.DivideByZero, + ["#NAME?"] = ErrorExpression.ExpressionErrorType.NameNotRecognized, + ["#N/A!"] = ErrorExpression.ExpressionErrorType.NoValueAvailable, + ["#NULL!"] = ErrorExpression.ExpressionErrorType.NullValue, + ["#NUM!"] = ErrorExpression.ExpressionErrorType.NumberInvalid + }; + + // build/get static token table private Dictionary GetSymbolTable() { @@ -462,6 +474,10 @@ } break; + + case TKTYPE.ERROR: + x = new ErrorExpression((ErrorExpression.ExpressionErrorType)_token.Value); + break; } // make sure we got something... @@ -662,26 +678,12 @@ return; } - // parse dates (review) - if (c == '#') + // parse #REF! (and other errors) in formula + if (c == '#' && ErrorMap.Any(pair => _len > _ptr+pair.Key.Length && _expr.Substring(_ptr, pair.Key.Length).Equals(pair.Key, StringComparison.OrdinalIgnoreCase))) { - // look for end # - for (i = 1; i + _ptr < _len; i++) - { - c = _expr[_ptr + i]; - if (c == '#') break; - } - - // check that we got the end of the date - if (c != '#') - { - Throw("Can't find final date delimiter ('#')."); - } - - // end of date - var lit = _expr.Substring(_ptr + 1, i - 1); - _ptr += i + 1; - _token = new Token(DateTime.Parse(lit, _ci), TKID.ATOM, TKTYPE.LITERAL); + var errorPair = ErrorMap.Single(pair => _len > _ptr + pair.Key.Length && _expr.Substring(_ptr, pair.Key.Length).Equals(pair.Key, StringComparison.OrdinalIgnoreCase)); + _ptr += errorPair.Key.Length; + _token = new Token(errorPair.Value, TKID.ATOM, TKTYPE.ERROR); return; } diff --git a/ClosedXML/Excel/CalcEngine/Exceptions/CalcEngineException.cs b/ClosedXML/Excel/CalcEngine/Exceptions/CalcEngineException.cs index 5050313..962b1c6 100644 --- a/ClosedXML/Excel/CalcEngine/Exceptions/CalcEngineException.cs +++ b/ClosedXML/Excel/CalcEngine/Exceptions/CalcEngineException.cs @@ -4,14 +4,14 @@ { internal abstract class CalcEngineException : ArgumentException { - public CalcEngineException() + protected CalcEngineException() : base() { } - public CalcEngineException(string message) + protected CalcEngineException(string message) : base(message) { } - public CalcEngineException(string message, Exception innerException) + protected CalcEngineException(string message, Exception innerException) : base(message, innerException) { } } diff --git a/ClosedXML/Excel/CalcEngine/Expression.cs b/ClosedXML/Excel/CalcEngine/Expression.cs index dcac3c0..2936a4c 100644 --- a/ClosedXML/Excel/CalcEngine/Expression.cs +++ b/ClosedXML/Excel/CalcEngine/Expression.cs @@ -1,8 +1,9 @@ +using ClosedXML.Excel.CalcEngine.Exceptions; using System; -using System.Threading; using System.Collections; using System.Collections.Generic; using System.Globalization; +using System.Threading; namespace ClosedXML.Excel.CalcEngine { @@ -19,31 +20,36 @@ internal class Expression : IComparable { //--------------------------------------------------------------------------- + #region ** fields internal Token _token; - #endregion + #endregion ** fields //--------------------------------------------------------------------------- + #region ** ctors internal Expression() { _token = new Token(null, TKID.ATOM, TKTYPE.IDENTIFIER); } + internal Expression(object value) { _token = new Token(value, TKID.ATOM, TKTYPE.LITERAL); } + internal Expression(Token tk) { _token = tk; } - #endregion + #endregion ** ctors //--------------------------------------------------------------------------- + #region ** object model public virtual object Evaluate() @@ -54,23 +60,32 @@ } return _token.Value; } + public virtual Expression Optimize() { return this; } - #endregion + #endregion ** object model //--------------------------------------------------------------------------- + #region ** implicit converters public static implicit operator string(Expression x) { + if (x is ErrorExpression) + (x as ErrorExpression).ThrowApplicableException(); + var v = x.Evaluate(); return v == null ? string.Empty : v.ToString(); } + public static implicit operator double(Expression x) { + if (x is ErrorExpression) + (x as ErrorExpression).ThrowApplicableException(); + // evaluate var v = x.Evaluate(); @@ -102,8 +117,12 @@ CultureInfo _ci = Thread.CurrentThread.CurrentCulture; return (double)Convert.ChangeType(v, typeof(double), _ci); } + public static implicit operator bool(Expression x) { + if (x is ErrorExpression) + (x as ErrorExpression).ThrowApplicableException(); + // evaluate var v = x.Evaluate(); @@ -128,8 +147,12 @@ // handle everything else return (double)x == 0 ? false : true; } + public static implicit operator DateTime(Expression x) { + if (x is ErrorExpression) + (x as ErrorExpression).ThrowApplicableException(); + // evaluate var v = x.Evaluate(); @@ -150,9 +173,10 @@ return (DateTime)Convert.ChangeType(v, typeof(DateTime), _ci); } - #endregion + #endregion ** implicit converters //--------------------------------------------------------------------------- + #region ** IComparable public int CompareTo(Expression other) @@ -197,15 +221,16 @@ return c1.CompareTo(c2); } - #endregion + #endregion ** IComparable } + /// /// Unary expression, e.g. +123 /// - class UnaryExpression : Expression + internal class UnaryExpression : Expression { // ** fields - Expression _expr; + private Expression _expr; // ** ctor public UnaryExpression(Token tk, Expression expr) : base(tk) @@ -220,11 +245,13 @@ { case TKID.ADD: return +(double)_expr; + case TKID.SUB: return -(double)_expr; } throw new ArgumentException("Bad expression."); } + public override Expression Optimize() { _expr = _expr.Optimize(); @@ -233,19 +260,21 @@ : this; } } + /// /// Binary expression, e.g. 1+2 /// - class BinaryExpression : Expression + internal class BinaryExpression : Expression { // ** fields - Expression _lft; - Expression _rgt; + private Expression _lft; + + private Expression _rgt; // ** ctor public BinaryExpression(Token tk, Expression exprLeft, Expression exprRight) : base(tk) { - _lft = exprLeft; + _lft = exprLeft; _rgt = exprRight; } @@ -272,18 +301,25 @@ { case TKID.CONCAT: return (string)_lft + (string)_rgt; + case TKID.ADD: return (double)_lft + (double)_rgt; + case TKID.SUB: return (double)_lft - (double)_rgt; + case TKID.MUL: return (double)_lft * (double)_rgt; + case TKID.DIV: return (double)_lft / (double)_rgt; + case TKID.DIVINT: return (double)(int)((double)_lft / (double)_rgt); + case TKID.MOD: return (double)(int)((double)_lft % (double)_rgt); + case TKID.POWER: var a = (double)_lft; var b = (double)_rgt; @@ -297,6 +333,7 @@ } throw new ArgumentException("Bad expression."); } + public override Expression Optimize() { _lft = _lft.Optimize(); @@ -306,19 +343,22 @@ : this; } } + /// /// Function call expression, e.g. sin(0.5) /// - class FunctionExpression : Expression + internal class FunctionExpression : Expression { // ** fields - FunctionDefinition _fn; - List _parms; + private FunctionDefinition _fn; + + private List _parms; // ** ctor internal FunctionExpression() { } + public FunctionExpression(FunctionDefinition function, List parms) { _fn = function; @@ -330,6 +370,7 @@ { return _fn.Function(_parms); } + public override Expression Optimize() { bool allLits = true; @@ -350,32 +391,35 @@ : this; } } + /// /// Simple variable reference. /// - class VariableExpression : Expression + internal class VariableExpression : Expression { - Dictionary _dct; - string _name; + private Dictionary _dct; + private string _name; public VariableExpression(Dictionary dct, string name) { _dct = dct; _name = name; } + public override object Evaluate() { return _dct[_name]; } } + /// /// Expression that represents an external object. /// - class XObjectExpression : + internal class XObjectExpression : Expression, IEnumerable { - object _value; + private object _value; // ** ctor internal XObjectExpression(object value) @@ -398,6 +442,7 @@ // return raw object return _value; } + public IEnumerator GetEnumerator() { return (_value as IEnumerable).GetEnumerator(); @@ -407,11 +452,57 @@ /// /// Expression that represents an omitted parameter. /// - class EmptyValueExpression : Expression + internal class EmptyValueExpression : Expression { internal EmptyValueExpression() { } } + internal class ErrorExpression : Expression + { + internal enum ExpressionErrorType + { + CellReference, + CellValue, + DivideByZero, + NameNotRecognized, + NoValueAvailable, + NullValue, + NumberInvalid + } + + internal ErrorExpression(ExpressionErrorType eet) + : base(new Token(eet, TKID.ATOM, TKTYPE.ERROR)) + { } + + public override object Evaluate() + { + return this._token.Value; + } + + public void ThrowApplicableException() + { + var eet = (ExpressionErrorType)_token.Value; + switch (eet) + { + // TODO: include last token in exception message + case ExpressionErrorType.CellReference: + throw new CellReferenceException(); + case ExpressionErrorType.CellValue: + throw new CellValueException(); + case ExpressionErrorType.DivideByZero: + throw new DivisionByZeroException(); + case ExpressionErrorType.NameNotRecognized: + throw new NameNotRecognizedException(); + case ExpressionErrorType.NoValueAvailable: + throw new NoValueAvailableException(); + case ExpressionErrorType.NullValue: + throw new NullValueException(); + case ExpressionErrorType.NumberInvalid: + throw new NumberException(); + } + } + } + /// /// Interface supported by external objects that have to return a value /// other than themselves (e.g. a cell range object should return the diff --git a/ClosedXML/Excel/CalcEngine/Token.cs b/ClosedXML/Excel/CalcEngine/Token.cs index 67351df..4766a51 100644 --- a/ClosedXML/Excel/CalcEngine/Token.cs +++ b/ClosedXML/Excel/CalcEngine/Token.cs @@ -1,12 +1,13 @@ namespace ClosedXML.Excel.CalcEngine { /// - /// Represents a node in the expression tree. + /// Represents a node in the expression tree. /// internal class Token - { + { // ** fields - public TKID ID; + public TKID ID; + public TKTYPE Type; public object Value; @@ -15,22 +16,25 @@ { Value = value; ID = id; - Type = type; - } + Type = type; + } } + /// /// Token types (used when building expressions, sequence defines operator priority) /// internal enum TKTYPE { - COMPARE, // < > = <= >= - ADDSUB, // + - - MULDIV, // * / - POWER, // ^ - GROUP, // ( ) , . - LITERAL, // 123.32, "Hello", etc. - IDENTIFIER // functions, external objects, bindings + COMPARE, // < > = <= >= + ADDSUB, // + - + MULDIV, // * / + POWER, // ^ + GROUP, // ( ) , . + LITERAL, // 123.32, "Hello", etc. + IDENTIFIER, // functions, external objects, bindings + ERROR // e.g. #REF! } + /// /// Token ID (used when evaluating expressions) /// diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index faf129b..3d0e55a 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -396,8 +396,7 @@ var retValEnumerable = retVal as IEnumerable; if (retValEnumerable != null && !(retVal is String)) - foreach (var v in retValEnumerable) - return v; + return retValEnumerable.Cast().First(); return retVal; } @@ -436,7 +435,7 @@ { FormulaA1 = String.Empty; - if (value as XLCells != null) throw new ArgumentException("Cannot assign IXLCells object to the cell value."); + if (value is XLCells) throw new ArgumentException("Cannot assign IXLCells object to the cell value."); if (SetRangeRows(value)) return; diff --git a/ClosedXML_Tests/Excel/Misc/FormulaTests.cs b/ClosedXML_Tests/Excel/Misc/FormulaTests.cs index 8d2d470..5f49caa 100644 --- a/ClosedXML_Tests/Excel/Misc/FormulaTests.cs +++ b/ClosedXML_Tests/Excel/Misc/FormulaTests.cs @@ -1,4 +1,5 @@ using ClosedXML.Excel; +using ClosedXML.Excel.CalcEngine.Exceptions; using NUnit.Framework; using System; using System.Linq; @@ -156,5 +157,17 @@ Assert.AreEqual(6, actual); } } + + [Test] + public void FormulasWithErrors() + { + Assert.Throws(() => XLWorkbook.EvaluateExpr("YEAR(#REF!)")); + 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(#NULL!)")); + Assert.Throws(() => XLWorkbook.EvaluateExpr("YEAR(#NUM!)")); + } } }