diff --git a/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML.csproj index 4bc2ce0..4d4338c 100644 --- a/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML.csproj @@ -65,6 +65,14 @@ + + + + + + + + diff --git a/ClosedXML/Excel/CalcEngine/CalcEngine.cs b/ClosedXML/Excel/CalcEngine/CalcEngine.cs index 8cc752e..c1a1aa1 100644 --- a/ClosedXML/Excel/CalcEngine/CalcEngine.cs +++ b/ClosedXML/Excel/CalcEngine/CalcEngine.cs @@ -253,6 +253,18 @@ #region ** token/keyword tables + private static readonly IDictionary ErrorMap = new Dictionary() + { + ["#REF!"] = ErrorExpression.ExpressionErrorType.CellReference, + ["#VALUE!"] = ErrorExpression.ExpressionErrorType.CellValue, + ["#DIV/0!"] = ErrorExpression.ExpressionErrorType.DivisionByZero, + ["#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() { @@ -463,6 +475,10 @@ } break; + + case TKTYPE.ERROR: + x = new ErrorExpression((ErrorExpression.ExpressionErrorType)_token.Value); + break; } // make sure we got something... @@ -663,26 +679,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 new file mode 100644 index 0000000..962b1c6 --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/Exceptions/CalcEngineException.cs @@ -0,0 +1,18 @@ +using System; + +namespace ClosedXML.Excel.CalcEngine.Exceptions +{ + internal abstract class CalcEngineException : ArgumentException + { + protected CalcEngineException() + : base() + { } + protected CalcEngineException(string message) + : base(message) + { } + + protected CalcEngineException(string message, Exception innerException) + : base(message, innerException) + { } + } +} diff --git a/ClosedXML/Excel/CalcEngine/Exceptions/CellReferenceException.cs b/ClosedXML/Excel/CalcEngine/Exceptions/CellReferenceException.cs new file mode 100644 index 0000000..f0fd88c --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/Exceptions/CellReferenceException.cs @@ -0,0 +1,27 @@ +using System; + +namespace ClosedXML.Excel.CalcEngine.Exceptions +{ + /// + /// This error occurs when you delete a cell referred to in the + /// formula or if you paste cells over the ones referred to in the + /// formula. + /// Corresponds to the #REF! error in Excel + /// + /// + internal class CellReferenceException : CalcEngineException + { + public CellReferenceException() + : base() + { } + + public CellReferenceException(string message) + : base(message) + { } + + public CellReferenceException(string message, Exception innerException) + : base(message, innerException) + { } + + } +} diff --git a/ClosedXML/Excel/CalcEngine/Exceptions/CellValueException.cs b/ClosedXML/Excel/CalcEngine/Exceptions/CellValueException.cs new file mode 100644 index 0000000..0716353 --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/Exceptions/CellValueException.cs @@ -0,0 +1,26 @@ +using System; + +namespace ClosedXML.Excel.CalcEngine.Exceptions +{ + /// + /// This error is most often the result of specifying a + /// mathematical operation with one or more cells that contain + /// text. + /// Corresponds to the #VALUE! error in Excel + /// + /// + internal class CellValueException : CalcEngineException + { + public CellValueException() + : base() + { } + + public CellValueException(string message) + : base(message) + { } + + public CellValueException(string message, Exception innerException) + : base(message, innerException) + { } + } +} diff --git a/ClosedXML/Excel/CalcEngine/Exceptions/DivisionByZeroException.cs b/ClosedXML/Excel/CalcEngine/Exceptions/DivisionByZeroException.cs new file mode 100644 index 0000000..53e2ed1 --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/Exceptions/DivisionByZeroException.cs @@ -0,0 +1,26 @@ +using System; + +namespace ClosedXML.Excel.CalcEngine.Exceptions +{ + /// + /// The division operation in your formula refers to a cell that + /// contains the value 0 or is blank. + /// Corresponds to the #DIV/0! error in Excel + /// + /// + internal class DivisionByZeroException : CalcEngineException + { + public DivisionByZeroException() + : base() + { } + + public DivisionByZeroException(string message) + : base(message) + { } + + public DivisionByZeroException(string message, Exception innerException) + : base(message, innerException) + { } + + } +} \ No newline at end of file diff --git a/ClosedXML/Excel/CalcEngine/Exceptions/NameNotRecognizedException.cs b/ClosedXML/Excel/CalcEngine/Exceptions/NameNotRecognizedException.cs new file mode 100644 index 0000000..0f51e56 --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/Exceptions/NameNotRecognizedException.cs @@ -0,0 +1,27 @@ +using System; + +namespace ClosedXML.Excel.CalcEngine.Exceptions +{ + /// + /// This error value appears when you incorrectly type the range + /// name, refer to a deleted range name, or forget to put quotation + /// marks around a text string in a formula. + /// Corresponds to the #NAME? error in Excel + /// + /// + internal class NameNotRecognizedException : CalcEngineException + { + public NameNotRecognizedException() + : base() + { } + + public NameNotRecognizedException(string message) + : base(message) + { } + + public NameNotRecognizedException(string message, Exception innerException) + : base(message, innerException) + { } + + } +} diff --git a/ClosedXML/Excel/CalcEngine/Exceptions/NoValueAvailableException.cs b/ClosedXML/Excel/CalcEngine/Exceptions/NoValueAvailableException.cs new file mode 100644 index 0000000..0e97fe5 --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/Exceptions/NoValueAvailableException.cs @@ -0,0 +1,27 @@ +using System; + +namespace ClosedXML.Excel.CalcEngine.Exceptions +{ + /// + /// Technically, this is not an error value but a special value + /// that you can manually enter into a cell to indicate that you + /// don’t yet have a necessary value. + /// Corresponds to the #N/A error in Excel. + /// + /// + internal class NoValueAvailableException : CalcEngineException + { + public NoValueAvailableException() + : base() + { } + + public NoValueAvailableException(string message) + : base(message) + { } + + public NoValueAvailableException(string message, Exception innerException) + : base(message, innerException) + { } + + } +} \ No newline at end of file diff --git a/ClosedXML/Excel/CalcEngine/Exceptions/NullValueException.cs b/ClosedXML/Excel/CalcEngine/Exceptions/NullValueException.cs new file mode 100644 index 0000000..d3153d7 --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/Exceptions/NullValueException.cs @@ -0,0 +1,27 @@ +using System; + +namespace ClosedXML.Excel.CalcEngine.Exceptions +{ + /// + /// Because a space indicates an intersection, this error will + /// occur if you insert a space instead of a comma(the union operator) + /// between ranges used in function arguments. + /// Corresponds to the #NULL! error in Excel + /// + /// + internal class NullValueException : CalcEngineException + { + public NullValueException() + : base() + { } + + public NullValueException(string message) + : base(message) + { } + + public NullValueException(string message, Exception innerException) + : base(message, innerException) + { } + + } +} diff --git a/ClosedXML/Excel/CalcEngine/Exceptions/NumberException.cs b/ClosedXML/Excel/CalcEngine/Exceptions/NumberException.cs new file mode 100644 index 0000000..4ce87d0 --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/Exceptions/NumberException.cs @@ -0,0 +1,27 @@ +using System; + +namespace ClosedXML.Excel.CalcEngine.Exceptions +{ + /// + /// This error can be caused by an invalid argument in an Excel + /// function or a formula that produces a number too large or too small + /// to be represented in the worksheet. + /// Corresponds to the #NUM! error in Excel + /// + /// + internal class NumberException : CalcEngineException + { + public NumberException() + : base() + { } + + public NumberException(string message) + : base(message) + { } + + public NumberException(string message, Exception innerException) + : base(message, innerException) + { } + + } +} diff --git a/ClosedXML/Excel/CalcEngine/Expression.cs b/ClosedXML/Excel/CalcEngine/Expression.cs index 9dfdf28..b9a29ca 100644 --- a/ClosedXML/Excel/CalcEngine/Expression.cs +++ b/ClosedXML/Excel/CalcEngine/Expression.cs @@ -1,3 +1,4 @@ +using ClosedXML.Excel.CalcEngine.Exceptions; using System; using System.Collections; using System.Collections.Generic; @@ -79,12 +80,18 @@ 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(); @@ -119,6 +126,9 @@ public static implicit operator bool(Expression x) { + if (x is ErrorExpression) + (x as ErrorExpression).ThrowApplicableException(); + // evaluate var v = x.Evaluate(); @@ -146,6 +156,9 @@ public static implicit operator DateTime(Expression x) { + if (x is ErrorExpression) + (x as ErrorExpression).ThrowApplicableException(); + // evaluate var v = x.Evaluate(); @@ -490,6 +503,52 @@ } } + internal class ErrorExpression : Expression + { + internal enum ExpressionErrorType + { + CellReference, + CellValue, + DivisionByZero, + 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.DivisionByZero: + 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/Functions/Information.cs b/ClosedXML/Excel/CalcEngine/Functions/Information.cs index 6df1a1e..6b19dfb 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) @@ -110,16 +130,16 @@ try { var stringValue = (string) v; - double.Parse(stringValue.TrimEnd('%', ' '), NumberStyles.Any); - isNumber = true; + double dv; + return double.TryParse(stringValue.TrimEnd('%', ' '), NumberStyles.Any, null, out dv); } catch (Exception) { 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/Excel/CalcEngine/Functions/Lookup.cs b/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs index 964e72b..801a195 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs @@ -1,3 +1,4 @@ +using ClosedXML.Excel.CalcEngine.Exceptions; using System; using System.Collections.Generic; using System.Linq; @@ -35,11 +36,11 @@ var table_array = p[1] as XObjectExpression; if (table_array == null) - throw new ArgumentException("table_array has to be a range"); + throw new NoValueAvailableException("table_array has to be a range"); var range_reference = table_array.Value as CellRangeReference; if (range_reference == null) - throw new ArgumentException("table_array has to be a range"); + throw new NoValueAvailableException("table_array has to be a range"); var range = range_reference.Range; @@ -49,10 +50,10 @@ || (bool)(p[3]); if (row_index_num < 1) - throw new ArgumentOutOfRangeException("Row index", "row_index_num has to be positive"); + throw new CellReferenceException("Row index has to be positive"); if (row_index_num > range.RowCount()) - throw new ArgumentOutOfRangeException("Row index", "row_index_num has to be positive"); + throw new CellReferenceException("Row index has to be positive"); IXLRangeColumn matching_column; matching_column = range.FindColumn(c => !c.Cell(1).IsEmpty() && new Expression(c.Cell(1).Value).CompareTo(lookup_value) == 0); @@ -72,7 +73,7 @@ } if (matching_column == null) - throw new ArgumentException("No matches found."); + throw new NoValueAvailableException("No matches found."); return matching_column .Cell(row_index_num) @@ -85,11 +86,11 @@ var table_array = p[1] as XObjectExpression; if (table_array == null) - throw new ArgumentException("table_array has to be a range"); + throw new NoValueAvailableException("table_array has to be a range"); var range_reference = table_array.Value as CellRangeReference; if (range_reference == null) - throw new ArgumentException("table_array has to be a range"); + throw new NoValueAvailableException("table_array has to be a range"); var range = range_reference.Range; @@ -99,13 +100,20 @@ || (bool)(p[3]); if (col_index_num < 1) - throw new ArgumentOutOfRangeException("Column index", "col_index_num has to be positive"); + throw new CellReferenceException("Column index has to be positive"); if (col_index_num > range.ColumnCount()) - throw new ArgumentOutOfRangeException("Column index", "col_index_num must be smaller or equal to the number of columns in the table array"); + throw new CellReferenceException("Colum index must be smaller or equal to the number of columns in the table array"); IXLRangeRow matching_row; - matching_row = range.FindRow(r => !r.Cell(1).IsEmpty() && new Expression(r.Cell(1).Value).CompareTo(lookup_value) == 0); + try + { + matching_row = range.FindRow(r => !r.Cell(1).IsEmpty() && new Expression(r.Cell(1).Value).CompareTo(lookup_value) == 0); + } + catch (Exception ex) + { + throw new NoValueAvailableException("No matches found", ex); + } if (range_lookup && matching_row == null) { var first_row = range.FirstRow().RowNumber(); @@ -122,7 +130,7 @@ } if (matching_row == null) - throw new ArgumentException("No matches found."); + throw new NoValueAvailableException("No matches found."); return matching_row .Cell(col_index_num) diff --git a/ClosedXML/Excel/CalcEngine/Functions/Text.cs b/ClosedXML/Excel/CalcEngine/Functions/Text.cs index 6046455..8ff1da3 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Text.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Text.cs @@ -1,3 +1,4 @@ +using ClosedXML.Excel.CalcEngine.Exceptions; using System; using System.Collections.Generic; using System.Globalization; @@ -44,7 +45,9 @@ private static object _Char(List p) { var i = (int)p[0]; - if (i < 1 || i > 255) throw new IndexOutOfRangeException(); + if (i < 1 || i > 255) + throw new CellValueException(string.Format("The number {0} is out of the required range (1 to 255)", i)); + var c = (char)i; return c.ToString(); } 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 f5e9084..ee602c2 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -399,8 +399,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; } @@ -439,7 +438,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 (SetTableHeader(value)) return; diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index a0834a3..152dbca 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -77,6 +77,7 @@ + diff --git a/ClosedXML_Tests/Excel/CalcEngine/CalcEngineExceptionTests.cs b/ClosedXML_Tests/Excel/CalcEngine/CalcEngineExceptionTests.cs new file mode 100644 index 0000000..b1be35f --- /dev/null +++ b/ClosedXML_Tests/Excel/CalcEngine/CalcEngineExceptionTests.cs @@ -0,0 +1,29 @@ +using ClosedXML.Excel; +using ClosedXML.Excel.CalcEngine.Exceptions; +using NUnit.Framework; +using System; +using System.Collections.Generic; +using System.Globalization; +using System.Linq; +using System.Text; +using System.Threading; + +namespace ClosedXML_Tests.Excel.CalcEngine +{ + [TestFixture] + public class CalcEngineExceptionTests + { + [OneTimeSetUp] + public void SetCultureInfo() + { + Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("en-US"); + } + + [Test] + public void InvalidCharNumber() + { + Assert.Throws(() => XLWorkbook.EvaluateExpr("CHAR(-2)")); + Assert.Throws(() => XLWorkbook.EvaluateExpr("CHAR(270)")); + } + } +} 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/CalcEngine/LookupTests.cs b/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs index f416784..a18e509 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs @@ -1,4 +1,5 @@ using ClosedXML.Excel; +using ClosedXML.Excel.CalcEngine.Exceptions; using NUnit.Framework; using System; @@ -122,11 +123,11 @@ [Test] public void Vlookup_Exceptions() { - Assert.That(() => workbook.Evaluate(@"=VLOOKUP("""",Data!$B$2:$I$71,3,FALSE)"), Throws.TypeOf()); - Assert.That(() => workbook.Evaluate(@"=VLOOKUP(50,Data!$B$2:$I$71,3,FALSE)"), Throws.TypeOf()); - Assert.That(() => workbook.Evaluate(@"=VLOOKUP(20,Data!$B$2:$I$71,9,FALSE)"), Throws.TypeOf()); + Assert.Throws(() => workbook.Evaluate(@"=VLOOKUP("""",Data!$B$2:$I$71,3,FALSE)")); + Assert.Throws(() => workbook.Evaluate(@"=VLOOKUP(50,Data!$B$2:$I$71,3,FALSE)")); + Assert.Throws(() => workbook.Evaluate(@"=VLOOKUP(-1,Data!$B$2:$I$71,2,TRUE)")); - Assert.That(() => workbook.Evaluate(@"=VLOOKUP(-1,Data!$B$2:$I$71,9,TRUE)"), Throws.TypeOf()); + Assert.Throws(() => workbook.Evaluate(@"=VLOOKUP(20,Data!$B$2:$I$71,9,FALSE)")); } } } diff --git a/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs b/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs index efb58d3..59d0a59 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs @@ -1,5 +1,6 @@ using ClosedXML.Excel; using ClosedXML.Excel.CalcEngine; +using ClosedXML.Excel.CalcEngine.Exceptions; using NUnit.Framework; using System; using System.Globalization; @@ -20,13 +21,13 @@ [Test] public void Char_Empty_Input_String() { - Assert.That(() => XLWorkbook.EvaluateExpr(@"Char("""")"), Throws.TypeOf()); + Assert.Throws(() => XLWorkbook.EvaluateExpr(@"Char("""")")); } [Test] public void Char_Input_Too_Large() { - Assert.That(() => XLWorkbook.EvaluateExpr(@"Char(9797)"), Throws.TypeOf()); + Assert.Throws< CellValueException>(() => XLWorkbook.EvaluateExpr(@"Char(9797)")); } [Test] diff --git a/ClosedXML_Tests/Excel/Misc/FormulaTests.cs b/ClosedXML_Tests/Excel/Misc/FormulaTests.cs index 16ac2a1..3ff4c55 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; @@ -173,5 +174,17 @@ actual = XLWorkbook.EvaluateExpr("+MID(\"This is a test\", 6, 2)"); Assert.AreEqual("is", 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!)")); + } } }