diff --git a/ClosedXML/Excel/CalcEngine/Expression.cs b/ClosedXML/Excel/CalcEngine/Expression.cs index 831db51..1b97924 100644 --- a/ClosedXML/Excel/CalcEngine/Expression.cs +++ b/ClosedXML/Excel/CalcEngine/Expression.cs @@ -10,503 +10,510 @@ namespace ClosedXML.Excel.CalcEngine { - /// - /// Base class that represents parsed expressions. - /// - /// - /// For example: - /// - /// Expression expr = scriptEngine.Parse(strExpression); - /// object val = expr.Evaluate(); - /// - /// - internal class Expression : IComparable - { - //--------------------------------------------------------------------------- - #region ** fields + /// + /// Base class that represents parsed expressions. + /// + /// + /// For example: + /// + /// Expression expr = scriptEngine.Parse(strExpression); + /// object val = expr.Evaluate(); + /// + /// + internal class Expression : IComparable + { + //--------------------------------------------------------------------------- + #region ** fields - internal Token _token; + internal Token _token; - #endregion + #endregion - //--------------------------------------------------------------------------- - #region ** ctors + //--------------------------------------------------------------------------- + #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; - } + 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 - //--------------------------------------------------------------------------- - #region ** object model + //--------------------------------------------------------------------------- + #region ** object model - public virtual object Evaluate() - { - if (_token.Type != TKTYPE.LITERAL) - { - throw new ArgumentException("Bad expression."); - } - return _token.Value; - } - public virtual Expression Optimize() - { - return this; - } + public virtual object Evaluate() + { + if (_token.Type != TKTYPE.LITERAL) + { + throw new ArgumentException("Bad expression."); + } + return _token.Value; + } + public virtual Expression Optimize() + { + return this; + } - #endregion + #endregion - //--------------------------------------------------------------------------- - #region ** implicit converters + //--------------------------------------------------------------------------- + #region ** implicit converters - public static implicit operator string(Expression x) - { - var v = x.Evaluate(); - return v == null ? string.Empty : v.ToString(); - } - public static implicit operator double(Expression x) - { - // evaluate - var v = x.Evaluate(); + public static implicit operator string(Expression x) + { + var v = x.Evaluate(); + return v == null ? string.Empty : v.ToString(); + } + public static implicit operator double(Expression x) + { + // evaluate + var v = x.Evaluate(); - // handle doubles - if (v is double) - { - return (double)v; - } + // handle doubles + if (v is double) + { + return (double)v; + } - // handle booleans - if (v is bool) - { - return (bool)v ? 1 : 0; - } + // handle booleans + if (v is bool) + { + return (bool)v ? 1 : 0; + } - // handle dates - if (v is DateTime) - { - return ((DateTime)v).ToOADate(); - } + // handle dates + if (v is DateTime) + { + return ((DateTime)v).ToOADate(); + } - // handle nulls - if (v == null || v is string) - { - return 0; - } - - // handle everything else - CultureInfo _ci = Thread.CurrentThread.CurrentCulture; - return (double)Convert.ChangeType(v, typeof(double), _ci); - } - public static implicit operator bool(Expression x) - { - // evaluate - var v = x.Evaluate(); - - // handle booleans - if (v is bool) - { - return (bool)v; - } - - // handle nulls - if (v == null) - { - return false; - } - - // handle doubles - if (v is double) - { - return (double)v == 0 ? false : true; - } - - // handle everything else - return (double)x == 0 ? false : true; - } - public static implicit operator DateTime(Expression x) - { - // evaluate - var v = x.Evaluate(); - - // handle dates - if (v is DateTime) - { - return (DateTime)v; - } - - // handle doubles - if (v is double || v is int) - { - return DateTime.FromOADate((double)x); - } + // handle nulls + if (v == null || v is string) + { + return 0; + } // handle everything else CultureInfo _ci = Thread.CurrentThread.CurrentCulture; - return (DateTime)Convert.ChangeType(v, typeof(DateTime), _ci); - } + return (double)Convert.ChangeType(v, typeof(double), _ci); + } + public static implicit operator bool(Expression x) + { + // evaluate + var v = x.Evaluate(); - #endregion + // handle booleans + if (v is bool) + { + return (bool)v; + } - //--------------------------------------------------------------------------- - #region ** IComparable + // handle nulls + if (v == null) + { + return false; + } - public int CompareTo(Expression other) - { - // get both values - var c1 = this.Evaluate() as IComparable; - var c2 = other.Evaluate() as IComparable; + // handle doubles + if (v is double) + { + return (double)v == 0 ? false : true; + } - // handle nulls - if (c1 == null && c2 == null) - { - return 0; - } - if (c2 == null) - { - return -1; - } - if (c1 == null) - { - return +1; - } + // handle everything else + return (double)x == 0 ? false : true; + } + public static implicit operator DateTime(Expression x) + { + // evaluate + var v = x.Evaluate(); - // make sure types are the same - if (c1.GetType() != c2.GetType()) - { - if (c1 is DateTime) - c2 = ((DateTime)other); - else if (c2 is DateTime) - c1 = ((DateTime)this); - else - c2 = Convert.ChangeType(c2, c1.GetType()) as IComparable; - } + // handle dates + if (v is DateTime) + { + return (DateTime)v; + } - // compare - return c1.CompareTo(c2); - } + // handle doubles + if (v is double || v is int) + { + return DateTime.FromOADate((double)x); + } - #endregion - } - /// - /// Unary expression, e.g. +123 - /// - class UnaryExpression : Expression - { - // ** fields - Expression _expr; + // handle everything else + CultureInfo _ci = Thread.CurrentThread.CurrentCulture; + return (DateTime)Convert.ChangeType(v, typeof(DateTime), _ci); + } - // ** ctor - public UnaryExpression(Token tk, Expression expr) : base(tk) - { - _expr = expr; - } + #endregion - // ** object model - override public object Evaluate() - { - switch (_token.ID) - { - case TKID.ADD: - return +(double)_expr; - case TKID.SUB: - return -(double)_expr; - } - throw new ArgumentException("Bad expression."); - } - public override Expression Optimize() - { - _expr = _expr.Optimize(); - return _expr._token.Type == TKTYPE.LITERAL - ? new Expression(this.Evaluate()) - : this; - } - } - /// - /// Binary expression, e.g. 1+2 - /// - class BinaryExpression : Expression - { - // ** fields - Expression _lft; - Expression _rgt; + //--------------------------------------------------------------------------- + #region ** IComparable - // ** ctor - public BinaryExpression(Token tk, Expression exprLeft, Expression exprRight) : base(tk) - { - _lft = exprLeft; - _rgt = exprRight; - } + public int CompareTo(Expression other) + { + // get both values + var c1 = this.Evaluate() as IComparable; + var c2 = other.Evaluate() as IComparable; - // ** object model - override public object Evaluate() - { - // handle comparisons - if (_token.Type == TKTYPE.COMPARE) - { - var cmp = _lft.CompareTo(_rgt); - switch (_token.ID) - { - case TKID.GT: return cmp > 0; - case TKID.LT: return cmp < 0; - case TKID.GE: return cmp >= 0; - case TKID.LE: return cmp <= 0; - case TKID.EQ: return cmp == 0; - case TKID.NE: return cmp != 0; - } - } + // handle nulls + if (c1 == null && c2 == null) + { + return 0; + } + if (c2 == null) + { + return -1; + } + if (c1 == null) + { + return +1; + } - // handle everything else - switch (_token.ID) - { - 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; - if (b == 0.0) return 1.0; - if (b == 0.5) return Math.Sqrt(a); - if (b == 1.0) return a; - if (b == 2.0) return a * a; - if (b == 3.0) return a * a * a; - if (b == 4.0) return a * a * a * a; - return Math.Pow((double)_lft, (double)_rgt); - } - throw new ArgumentException("Bad expression."); - } - public override Expression Optimize() - { - _lft = _lft.Optimize(); - _rgt = _rgt.Optimize(); - return _lft._token.Type == TKTYPE.LITERAL && _rgt._token.Type == TKTYPE.LITERAL - ? new Expression(this.Evaluate()) - : this; - } - } - /// - /// Function call expression, e.g. sin(0.5) - /// - class FunctionExpression : Expression - { - // ** fields - FunctionDefinition _fn; - List _parms; + // make sure types are the same + if (c1.GetType() != c2.GetType()) + { + try + { + if (c1 is DateTime) + c2 = ((DateTime)other); + else if (c2 is DateTime) + c1 = ((DateTime)this); + else + c2 = Convert.ChangeType(c2, c1.GetType()) as IComparable; + } + catch (InvalidCastException) { return -1; } + catch (FormatException) { return -1; } + catch (OverflowException) { return -1; } + catch (ArgumentNullException) { return -1; } + } - // ** ctor - internal FunctionExpression() - { - } - public FunctionExpression(FunctionDefinition function, List parms) - { - _fn = function; - _parms = parms; - } + // compare + return c1.CompareTo(c2); + } - // ** object model - override public object Evaluate() - { - return _fn.Function(_parms); - } - public override Expression Optimize() - { - bool allLits = true; - if (_parms != null) - { - for (int i = 0; i < _parms.Count; i++) - { - var p = _parms[i].Optimize(); - _parms[i] = p; - if (p._token.Type != TKTYPE.LITERAL) - { - allLits = false; - } - } - } - return allLits - ? new Expression(this.Evaluate()) - : this; - } - } - /// - /// Simple variable reference. - /// - class VariableExpression : Expression - { - Dictionary _dct; - string _name; + #endregion + } + /// + /// Unary expression, e.g. +123 + /// + class UnaryExpression : Expression + { + // ** fields + Expression _expr; - public VariableExpression(Dictionary dct, string name) - { - _dct = dct; - _name = name; - } - public override object Evaluate() - { - return _dct[_name]; - } - } - /// - /// Expression based on an object's properties. - /// - class BindingExpression : Expression - { - CalcEngine _ce; - CultureInfo _ci; - List _bindingPath; + // ** ctor + public UnaryExpression(Token tk, Expression expr) : base(tk) + { + _expr = expr; + } - // ** ctor - internal BindingExpression(CalcEngine engine, List bindingPath, CultureInfo ci) - { - _ce = engine; - _bindingPath = bindingPath; - _ci = ci; - } + // ** object model + override public object Evaluate() + { + switch (_token.ID) + { + case TKID.ADD: + return +(double)_expr; + case TKID.SUB: + return -(double)_expr; + } + throw new ArgumentException("Bad expression."); + } + public override Expression Optimize() + { + _expr = _expr.Optimize(); + return _expr._token.Type == TKTYPE.LITERAL + ? new Expression(this.Evaluate()) + : this; + } + } + /// + /// Binary expression, e.g. 1+2 + /// + class BinaryExpression : Expression + { + // ** fields + Expression _lft; + Expression _rgt; - // ** object model - override public object Evaluate() - { - return GetValue(_ce.DataContext); - } + // ** ctor + public BinaryExpression(Token tk, Expression exprLeft, Expression exprRight) : base(tk) + { + _lft = exprLeft; + _rgt = exprRight; + } - // ** implementation - object GetValue(object obj) - { - const BindingFlags bf = - BindingFlags.IgnoreCase | - BindingFlags.Instance | - BindingFlags.Public | - BindingFlags.Static; + // ** object model + override public object Evaluate() + { + // handle comparisons + if (_token.Type == TKTYPE.COMPARE) + { + var cmp = _lft.CompareTo(_rgt); + switch (_token.ID) + { + case TKID.GT: return cmp > 0; + case TKID.LT: return cmp < 0; + case TKID.GE: return cmp >= 0; + case TKID.LE: return cmp <= 0; + case TKID.EQ: return cmp == 0; + case TKID.NE: return cmp != 0; + } + } - if (obj != null) - { - foreach (var bi in _bindingPath) - { - // get property - if (bi.PropertyInfo == null) - { - bi.PropertyInfo = obj.GetType().GetProperty(bi.Name, bf); - } + // handle everything else + switch (_token.ID) + { + 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; + if (b == 0.0) return 1.0; + if (b == 0.5) return Math.Sqrt(a); + if (b == 1.0) return a; + if (b == 2.0) return a * a; + if (b == 3.0) return a * a * a; + if (b == 4.0) return a * a * a * a; + return Math.Pow((double)_lft, (double)_rgt); + } + throw new ArgumentException("Bad expression."); + } + public override Expression Optimize() + { + _lft = _lft.Optimize(); + _rgt = _rgt.Optimize(); + return _lft._token.Type == TKTYPE.LITERAL && _rgt._token.Type == TKTYPE.LITERAL + ? new Expression(this.Evaluate()) + : this; + } + } + /// + /// Function call expression, e.g. sin(0.5) + /// + class FunctionExpression : Expression + { + // ** fields + FunctionDefinition _fn; + List _parms; - // get object - try - { - obj = bi.PropertyInfo.GetValue(obj, null); - } - catch - { - // REVIEW: is this needed? - System.Diagnostics.Debug.Assert(false, "shouldn't happen!"); - bi.PropertyInfo = obj.GetType().GetProperty(bi.Name, bf); - bi.PropertyInfoItem = null; - obj = bi.PropertyInfo.GetValue(obj, null); - } + // ** ctor + internal FunctionExpression() + { + } + public FunctionExpression(FunctionDefinition function, List parms) + { + _fn = function; + _parms = parms; + } - // handle indexers (lists and dictionaries) - if (bi.Parms != null && bi.Parms.Count > 0) - { - // get indexer property (always called "Item") - if (bi.PropertyInfoItem == null) - { - bi.PropertyInfoItem = obj.GetType().GetProperty("Item", bf); - } + // ** object model + override public object Evaluate() + { + return _fn.Function(_parms); + } + public override Expression Optimize() + { + bool allLits = true; + if (_parms != null) + { + for (int i = 0; i < _parms.Count; i++) + { + var p = _parms[i].Optimize(); + _parms[i] = p; + if (p._token.Type != TKTYPE.LITERAL) + { + allLits = false; + } + } + } + return allLits + ? new Expression(this.Evaluate()) + : this; + } + } + /// + /// Simple variable reference. + /// + class VariableExpression : Expression + { + Dictionary _dct; + string _name; - // get indexer parameters - var pip = bi.PropertyInfoItem.GetIndexParameters(); - var list = new List(); - for (int i = 0; i < pip.Length; i++) - { - var pv = bi.Parms[i].Evaluate(); - pv = Convert.ChangeType(pv, pip[i].ParameterType, _ci); - list.Add(pv); - } + public VariableExpression(Dictionary dct, string name) + { + _dct = dct; + _name = name; + } + public override object Evaluate() + { + return _dct[_name]; + } + } + /// + /// Expression based on an object's properties. + /// + class BindingExpression : Expression + { + CalcEngine _ce; + CultureInfo _ci; + List _bindingPath; - // get value - obj = bi.PropertyInfoItem.GetValue(obj, list.ToArray()); - } - } - } + // ** ctor + internal BindingExpression(CalcEngine engine, List bindingPath, CultureInfo ci) + { + _ce = engine; + _bindingPath = bindingPath; + _ci = ci; + } - // all done - return obj; - } - } - /// - /// Helper used for building BindingExpression objects. - /// - class BindingInfo - { - public BindingInfo(string member, List parms) - { - Name = member; - Parms = parms; - } - public string Name { get; set; } - public PropertyInfo PropertyInfo { get; set; } - public PropertyInfo PropertyInfoItem { get; set; } - public List Parms { get; set; } - } - /// - /// Expression that represents an external object. - /// - class XObjectExpression : - Expression, - IEnumerable - { - object _value; + // ** object model + override public object Evaluate() + { + return GetValue(_ce.DataContext); + } - // ** ctor - internal XObjectExpression(object value) - { - _value = value; - } + // ** implementation + object GetValue(object obj) + { + const BindingFlags bf = + BindingFlags.IgnoreCase | + BindingFlags.Instance | + BindingFlags.Public | + BindingFlags.Static; - public object Value { get { return _value; } } + if (obj != null) + { + foreach (var bi in _bindingPath) + { + // get property + if (bi.PropertyInfo == null) + { + bi.PropertyInfo = obj.GetType().GetProperty(bi.Name, bf); + } - // ** object model - public override object Evaluate() - { - // use IValueObject if available - var iv = _value as IValueObject; - if (iv != null) - { - return iv.GetValue(); - } + // get object + try + { + obj = bi.PropertyInfo.GetValue(obj, null); + } + catch + { + // REVIEW: is this needed? + System.Diagnostics.Debug.Assert(false, "shouldn't happen!"); + bi.PropertyInfo = obj.GetType().GetProperty(bi.Name, bf); + bi.PropertyInfoItem = null; + obj = bi.PropertyInfo.GetValue(obj, null); + } - // return raw object - return _value; - } - public IEnumerator GetEnumerator() - { - return (_value as IEnumerable).GetEnumerator(); - } - } - /// - /// Interface supported by external objects that have to return a value - /// other than themselves (e.g. a cell range object should return the - /// cell content instead of the range itself). - /// - public interface IValueObject - { - object GetValue(); - } + // handle indexers (lists and dictionaries) + if (bi.Parms != null && bi.Parms.Count > 0) + { + // get indexer property (always called "Item") + if (bi.PropertyInfoItem == null) + { + bi.PropertyInfoItem = obj.GetType().GetProperty("Item", bf); + } + + // get indexer parameters + var pip = bi.PropertyInfoItem.GetIndexParameters(); + var list = new List(); + for (int i = 0; i < pip.Length; i++) + { + var pv = bi.Parms[i].Evaluate(); + pv = Convert.ChangeType(pv, pip[i].ParameterType, _ci); + list.Add(pv); + } + + // get value + obj = bi.PropertyInfoItem.GetValue(obj, list.ToArray()); + } + } + } + + // all done + return obj; + } + } + /// + /// Helper used for building BindingExpression objects. + /// + class BindingInfo + { + public BindingInfo(string member, List parms) + { + Name = member; + Parms = parms; + } + public string Name { get; set; } + public PropertyInfo PropertyInfo { get; set; } + public PropertyInfo PropertyInfoItem { get; set; } + public List Parms { get; set; } + } + /// + /// Expression that represents an external object. + /// + class XObjectExpression : + Expression, + IEnumerable + { + object _value; + + // ** ctor + internal XObjectExpression(object value) + { + _value = value; + } + + public object Value { get { return _value; } } + + // ** object model + public override object Evaluate() + { + // use IValueObject if available + var iv = _value as IValueObject; + if (iv != null) + { + return iv.GetValue(); + } + + // return raw object + return _value; + } + public IEnumerator GetEnumerator() + { + return (_value as IEnumerable).GetEnumerator(); + } + } + /// + /// Interface supported by external objects that have to return a value + /// other than themselves (e.g. a cell range object should return the + /// cell content instead of the range itself). + /// + public interface IValueObject + { + object GetValue(); + } } diff --git a/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs b/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs index b115375..dfa41bb 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs @@ -50,16 +50,16 @@ throw new ApplicationException("col_index_num must be smaller or equal to the number of rows in the table array"); IXLRangeColumn matching_column; - matching_column = range.FindColumn(c => new Expression(c.Cell(1).Value).CompareTo(lookup_value) == 0); + matching_column = range.FindColumn(c => !c.Cell(1).IsEmpty() && new Expression(c.Cell(1).Value).CompareTo(lookup_value) == 0); if (range_lookup && matching_column == null) { var first_column = range.FirstColumn().ColumnNumber(); matching_column = range.FindColumn(c => { var column_index_in_range = c.ColumnNumber() - first_column + 1; - if (column_index_in_range < range.ColumnsUsed().Count() && new Expression(c.Cell(1).Value).CompareTo(lookup_value) <= 0 && new Expression(c.ColumnRight().Cell(1).Value).CompareTo(lookup_value) > 0) + if (column_index_in_range < range.ColumnsUsed().Count() && !c.Cell(1).IsEmpty() && new Expression(c.Cell(1).Value).CompareTo(lookup_value) <= 0 && !c.ColumnRight().Cell(1).IsEmpty() && new Expression(c.ColumnRight().Cell(1).Value).CompareTo(lookup_value) > 0) return true; - else if (column_index_in_range == range.ColumnsUsed().Count() && new Expression(c.Cell(1).Value).CompareTo(lookup_value) <= 0) + else if (column_index_in_range == range.ColumnsUsed().Count() && !c.Cell(1).IsEmpty() && new Expression(c.Cell(1).Value).CompareTo(lookup_value) <= 0) return true; else return false; @@ -95,16 +95,16 @@ throw new ApplicationException("col_index_num must be smaller or equal to the number of columns in the table array"); IXLRangeRow matching_row; - matching_row = range.FindRow(r => new Expression(r.Cell(1).Value).CompareTo(lookup_value) == 0); + matching_row = range.FindRow(r => !r.Cell(1).IsEmpty() && new Expression(r.Cell(1).Value).CompareTo(lookup_value) == 0); if (range_lookup && matching_row == null) { var first_row = range.FirstRow().RowNumber(); matching_row = range.FindRow(r => { var row_index_in_range = r.RowNumber() - first_row + 1; - if (row_index_in_range < range.RowsUsed().Count() && new Expression(r.Cell(1).Value).CompareTo(lookup_value) <= 0 && new Expression(r.RowBelow().Cell(1).Value).CompareTo(lookup_value) > 0) + if (row_index_in_range < range.RowsUsed().Count() && !r.Cell(1).IsEmpty() && new Expression(r.Cell(1).Value).CompareTo(lookup_value) <= 0 && !r.RowBelow().Cell(1).IsEmpty() && new Expression(r.RowBelow().Cell(1).Value).CompareTo(lookup_value) > 0) return true; - else if (row_index_in_range == range.RowsUsed().Count() && new Expression(r.Cell(1).Value).CompareTo(lookup_value) <= 0) + else if (row_index_in_range == range.RowsUsed().Count() && !r.Cell(1).IsEmpty() && new Expression(r.Cell(1).Value).CompareTo(lookup_value) <= 0) return true; else return false; diff --git a/ClosedXML_Tests/Excel/CalcEngine/LogicalTests.cs b/ClosedXML_Tests/Excel/CalcEngine/LogicalTests.cs index c3aeee4..a78ce97 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/LogicalTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/LogicalTests.cs @@ -33,5 +33,22 @@ Object actual = XLWorkbook.EvaluateExpr(@"if(1 = 2, ""T"", ""F"")"); Assert.AreEqual("F", actual); } + + [Test] + public void If_Comparing_Against_Empty_String() + { + Object actual; + actual = XLWorkbook.EvaluateExpr(@"if(date(2016, 1, 1) = """", ""A"",""B"")"); + Assert.AreEqual("B", actual); + + actual = XLWorkbook.EvaluateExpr(@"if("""" = date(2016, 1, 1), ""A"",""B"")"); + Assert.AreEqual("B", actual); + + actual = XLWorkbook.EvaluateExpr(@"if("""" = 123, ""A"",""B"")"); + Assert.AreEqual("B", actual); + + actual = XLWorkbook.EvaluateExpr(@"if("""" = """", ""A"",""B"")"); + Assert.AreEqual("A", actual); + } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/Misc/FormulaTests.cs b/ClosedXML_Tests/Excel/Misc/FormulaTests.cs index b336324..d9bb00a 100644 --- a/ClosedXML_Tests/Excel/Misc/FormulaTests.cs +++ b/ClosedXML_Tests/Excel/Misc/FormulaTests.cs @@ -1,5 +1,6 @@ using ClosedXML.Excel; using NUnit.Framework; +using System; namespace ClosedXML_Tests.Excel { @@ -59,5 +60,24 @@ Assert.AreEqual("'S10 Data'!B1", ws.Cell("B1").FormulaA1); } } + + [Test] + public void DateAgainstStringComparison() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + ws.Cell("A1").Value = new DateTime(2016, 1, 1); + ws.Cell("A1").DataType = XLCellValues.DateTime; + + ws.Cell("A2").FormulaA1 = @"=IF(A1 = """", ""A"", ""B"")"; + var actual = ws.Cell("A2").Value; + Assert.AreEqual(actual, "B"); + + ws.Cell("A3").FormulaA1 = @"=IF("""" = A1, ""A"", ""B"")"; + actual = ws.Cell("A3").Value; + Assert.AreEqual(actual, "B"); + } + } } }