diff --git a/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML.csproj index 893e0dd..70c85b8 100644 --- a/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML.csproj @@ -65,6 +65,14 @@ + + + + + + + + diff --git a/ClosedXML/Excel/CalcEngine/Exceptions/CalcEngineException.cs b/ClosedXML/Excel/CalcEngine/Exceptions/CalcEngineException.cs new file mode 100644 index 0000000..5050313 --- /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 + { + public CalcEngineException() + : base() + { } + public CalcEngineException(string message) + : base(message) + { } + + public 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/Functions/Lookup.cs b/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs index 8a9ac19..629ac70 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; @@ -86,16 +87,23 @@ var range_lookup = p.Count < 4 || (bool)(p[3]); if (table_array == null || range_reference == null) - throw new ApplicationException("table_array has to be a range"); + throw new NoValueAvailableException("table_array has to be a range"); if (col_index_num < 1) - throw new ApplicationException("col_index_num has to be positive"); + throw new CellReferenceException("col_index_num has to be positive"); if (col_index_num > range.ColumnCount()) - throw new ApplicationException("col_index_num must be smaller or equal to the number of columns in the table array"); + throw new CellReferenceException("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 => !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(); @@ -112,7 +120,7 @@ } if (matching_row == null) - throw new ApplicationException("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 cb66322..f05118e 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_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index 487ea3a..2533a4c 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/LookupTests.cs b/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs index a112f3d..44b9156 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; +using ClosedXML.Excel.CalcEngine.Exceptions; using NUnit.Framework; using System; @@ -116,11 +117,11 @@ [Test] public void Vlookup_Exceptions() { - Assert.That(() => workbook.Evaluate(@"=VLOOKUP("""",Data!$B$2:$I$71,3,FALSE)"), Throws.Exception); - Assert.That(() => workbook.Evaluate(@"=VLOOKUP(50,Data!$B$2:$I$71,3,FALSE)"), Throws.Exception); - Assert.That(() => workbook.Evaluate(@"=VLOOKUP(20,Data!$B$2:$I$71,9,FALSE)"), Throws.Exception); + 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.Exception); + Assert.Throws(() => workbook.Evaluate(@"=VLOOKUP(20,Data!$B$2:$I$71,9,FALSE)")); } } }