diff --git a/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML.csproj index 7a5d76a..8b01542 100644 --- a/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML.csproj @@ -64,6 +64,7 @@ + diff --git a/ClosedXML/Excel/CalcEngine/CalcEngine.cs b/ClosedXML/Excel/CalcEngine/CalcEngine.cs index f646ddc..b946fa2 100644 --- a/ClosedXML/Excel/CalcEngine/CalcEngine.cs +++ b/ClosedXML/Excel/CalcEngine/CalcEngine.cs @@ -287,6 +287,7 @@ // register built-in functions (and constants) Is.Register(this); Logical.Register(this); + Lookup.Register(this); MathTrig.Register(this); Text.Register(this); Statistical.Register(this); diff --git a/ClosedXML/Excel/CalcEngine/Expression.cs b/ClosedXML/Excel/CalcEngine/Expression.cs index f309af7..3e2c9bc 100644 --- a/ClosedXML/Excel/CalcEngine/Expression.cs +++ b/ClosedXML/Excel/CalcEngine/Expression.cs @@ -144,13 +144,13 @@ } // handle doubles - if (v is double) + if (v is double || v is int) { return DateTime.FromOADate((double)x); } - // handle everything else - CultureInfo _ci = Thread.CurrentThread.CurrentCulture; + // handle everything else + CultureInfo _ci = Thread.CurrentThread.CurrentCulture; return (DateTime)Convert.ChangeType(v, typeof(DateTime), _ci); } @@ -182,7 +182,12 @@ // make sure types are the same if (c1.GetType() != c2.GetType()) { - c2 = Convert.ChangeType(c2, c1.GetType()) as IComparable; + if (c1 is DateTime) + c2 = ((DateTime)other); + else if (c2 is DateTime) + c1 = ((DateTime)this); + else + c2 = Convert.ChangeType(c2, c1.GetType()) as IComparable; } // compare diff --git a/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs b/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs new file mode 100644 index 0000000..4a5b720 --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs @@ -0,0 +1,118 @@ +using System; +using System.Collections.Generic; +using System.Linq; + +namespace ClosedXML.Excel.CalcEngine.Functions +{ + internal static class Lookup + { + public static void Register(CalcEngine ce) + { + //ce.RegisterFunction("ADDRESS", , Address); // Returns a reference as text to a single cell in a worksheet + //ce.RegisterFunction("AREAS", , Areas); // Returns the number of areas in a reference + //ce.RegisterFunction("CHOOSE", , Choose); // Chooses a value from a list of values + //ce.RegisterFunction("COLUMN", , Column); // Returns the column number of a reference + //ce.RegisterFunction("COLUMNS", , Columns); // Returns the number of columns in a reference + //ce.RegisterFunction("FORMULATEXT", , Formulatext); // Returns the formula at the given reference as text + //ce.RegisterFunction("GETPIVOTDATA", , Getpivotdata); // Returns data stored in a PivotTable report + ce.RegisterFunction("HLOOKUP", 4, Hlookup); // Looks in the top row of an array and returns the value of the indicated cell + //ce.RegisterFunction("HYPERLINK", , Hyperlink); // Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet + //ce.RegisterFunction("INDEX", , Index); // Uses an index to choose a value from a reference or array + //ce.RegisterFunction("INDIRECT", , Indirect); // Returns a reference indicated by a text value + //ce.RegisterFunction("LOOKUP", , Lookup); // Looks up values in a vector or array + //ce.RegisterFunction("MATCH", , Match); // Looks up values in a reference or array + //ce.RegisterFunction("OFFSET", , Offset); // Returns a reference offset from a given reference + //ce.RegisterFunction("ROW", , Row); // Returns the row number of a reference + //ce.RegisterFunction("ROWS", , Rows); // Returns the number of rows in a reference + //ce.RegisterFunction("RTD", , Rtd); // Retrieves real-time data from a program that supports COM automation + //ce.RegisterFunction("TRANSPOSE", , Transpose); // Returns the transpose of an array + ce.RegisterFunction("VLOOKUP", 4, Vlookup); // Looks in the first column of an array and moves across the row to return the value of a cell + } + + private static object Hlookup(List p) + { + var lookup_value = p[0]; + + var table_array = p[1] as XObjectExpression; + var range_reference = table_array.Value as CellRangeReference; + var range = range_reference.Range; + + var row_index_num = (int)(p[2]); + 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"); + + if (row_index_num < 1) + throw new ApplicationException("col_index_num has to be positive"); + + if (row_index_num > range.RowCount()) + 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); + if (range_lookup && matching_column == null) + { + matching_column = range.FindColumn(c => + { + if (c.ColumnNumber() < range.ColumnsUsed().Count() && new Expression(c.Cell(1).Value).CompareTo(lookup_value) <= 0 && new Expression(c.ColumnRight().Cell(1).Value).CompareTo(lookup_value) > 0) + return true; + else if (c.ColumnNumber() == range.ColumnsUsed().Count() && new Expression(c.Cell(1).Value).CompareTo(lookup_value) <= 0) + return true; + else + return false; + }); + } + + if (matching_column == null) + throw new ApplicationException("No matches found."); + + return matching_column + .Cell(row_index_num) + .Value; + } + + private static object Vlookup(List p) + { + var lookup_value = p[0]; + + var table_array = p[1] as XObjectExpression; + var range_reference = table_array.Value as CellRangeReference; + var range = range_reference.Range; + + var col_index_num = (int)(p[2]); + 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"); + + if (col_index_num < 1) + throw new ApplicationException("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"); + + IXLRangeRow matching_row; + matching_row = range.FindRow(r => new Expression(r.Cell(1).Value).CompareTo(lookup_value) == 0); + if (range_lookup && matching_row == null) + { + matching_row = range.FindRow(r => + { + if (r.RowNumber() < range.RowsUsed().Count() && new Expression(r.Cell(1).Value).CompareTo(lookup_value) <= 0 && new Expression(r.RowBelow().Cell(1).Value).CompareTo(lookup_value) > 0) + return true; + else if (r.RowNumber() == range.RowsUsed().Count() && new Expression(r.Cell(1).Value).CompareTo(lookup_value) <= 0) + return true; + else + return false; + }); + } + + if (matching_row == null) + throw new ApplicationException("No matches found."); + + return matching_row + .Cell(col_index_num) + .Value; + } + } +} \ No newline at end of file diff --git a/ClosedXML/Excel/Ranges/IXLRange.cs b/ClosedXML/Excel/Ranges/IXLRange.cs index b813037..26761ba 100644 --- a/ClosedXML/Excel/Ranges/IXLRange.cs +++ b/ClosedXML/Excel/Ranges/IXLRange.cs @@ -83,7 +83,14 @@ /// /// The columns to return. IXLRangeColumns Columns(string columns); - + /// + /// Returns the first row that matches the given predicate + /// + IXLRangeColumn FindColumn(Func predicate); + /// + /// Returns the first row that matches the given predicate + /// + IXLRangeRow FindRow(Func predicate); /// /// Gets the first row of the range. /// diff --git a/ClosedXML/Excel/Ranges/XLRange.cs b/ClosedXML/Excel/Ranges/XLRange.cs index 6ffdff1..d032c83 100644 --- a/ClosedXML/Excel/Ranges/XLRange.cs +++ b/ClosedXML/Excel/Ranges/XLRange.cs @@ -806,5 +806,33 @@ base.Clear(clearOptions); return this; } + + public IXLRangeColumn FindColumn(Func predicate) + { + Int32 columnCount = ColumnCount(); + for (Int32 c = 1; c <= columnCount; c++) + { + var column = Column(c); + if (predicate == null || predicate(column)) + return column; + else + column.Dispose(); + } + return null; + } + + public IXLRangeRow FindRow(Func predicate) + { + Int32 rowCount = RowCount(); + for (Int32 r = 1; r <= rowCount; r++) + { + var row = Row(r); + if (predicate(row)) + return row; + else + row.Dispose(); + } + return null; + } } } diff --git a/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj b/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj index cec4201..f39ac33 100644 --- a/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj +++ b/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj @@ -115,6 +115,9 @@ Excel\CalcEngine\Functions\Logical.cs + + Excel\CalcEngine\Functions\Lookup.cs + Excel\CalcEngine\Functions\MathTrig.cs diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index 654d8ea..d6f595c 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -72,6 +72,7 @@ + diff --git a/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs b/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs new file mode 100644 index 0000000..5d232f6 --- /dev/null +++ b/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs @@ -0,0 +1,116 @@ +using ClosedXML.Excel; +using NUnit.Framework; +using System; + +namespace ClosedXML_Tests.Excel.CalcEngine +{ + [TestFixture] + public class LookupTests + { + private XLWorkbook workbook; + + [OneTimeSetUp] + public void Init() + { + // Make sure tests run on a deterministic culture + System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); + workbook = SetupWorkbook(); + } + + private XLWorkbook SetupWorkbook() + { + var wb = new XLWorkbook(); + var ws = wb.AddWorksheet("Data"); + var data = new object[] + { + new {Id=1,OrderDate = DateTime.Parse("2015-01-06"), Region = "East", Rep = "Jones", Item = "Pencil", Units = 95, UnitCost = 1.99, Total = 189.05 }, + new {Id=2,OrderDate = DateTime.Parse("2015-01-23"), Region = "Central", Rep = "Kivell", Item = "Binder", Units = 50, UnitCost = 19.99, Total = 999.5}, + new {Id=3,OrderDate = DateTime.Parse("2015-02-09"), Region = "Central", Rep = "Jardine", Item = "Pencil", Units = 36, UnitCost = 4.99, Total = 179.64}, + new {Id=4,OrderDate = DateTime.Parse("2015-02-26"), Region = "Central", Rep = "Gill", Item = "Pen", Units = 27, UnitCost = 19.99, Total = 539.73}, + new {Id=5,OrderDate = DateTime.Parse("2015-03-15"), Region = "West", Rep = "Sorvino", Item = "Pencil", Units = 56, UnitCost = 2.99, Total = 167.44}, + new {Id=6,OrderDate = DateTime.Parse("2015-04-01"), Region = "East", Rep = "Jones", Item = "Binder", Units = 60, UnitCost = 4.99, Total = 299.4}, + new {Id=7,OrderDate = DateTime.Parse("2015-04-18"), Region = "Central", Rep = "Andrews", Item = "Pencil", Units = 75, UnitCost = 1.99, Total = 149.25}, + new {Id=8,OrderDate = DateTime.Parse("2015-05-05"), Region = "Central", Rep = "Jardine", Item = "Pencil", Units = 90, UnitCost = 4.99, Total = 449.1}, + new {Id=9,OrderDate = DateTime.Parse("2015-05-22"), Region = "West", Rep = "Thompson", Item = "Pencil", Units = 32, UnitCost = 1.99, Total = 63.68}, + new {Id=10,OrderDate = DateTime.Parse("2015-06-08"), Region = "East", Rep = "Jones", Item = "Binder", Units = 60, UnitCost = 8.99, Total = 539.4}, + new {Id=11,OrderDate = DateTime.Parse("2015-06-25"), Region = "Central", Rep = "Morgan", Item = "Pencil", Units = 90, UnitCost = 4.99, Total = 449.1}, + new {Id=12,OrderDate = DateTime.Parse("2015-07-12"), Region = "East", Rep = "Howard", Item = "Binder", Units = 29, UnitCost = 1.99, Total = 57.71}, + new {Id=13,OrderDate = DateTime.Parse("2015-07-29"), Region = "East", Rep = "Parent", Item = "Binder", Units = 81, UnitCost = 19.99, Total = 1619.19}, + new {Id=14,OrderDate = DateTime.Parse("2015-08-15"), Region = "East", Rep = "Jones", Item = "Pencil", Units = 35, UnitCost = 4.99, Total = 174.65}, + new {Id=15,OrderDate = DateTime.Parse("2015-09-01"), Region = "Central", Rep = "Smith", Item = "Desk", Units = 2, UnitCost = 125, Total = 250}, + new {Id=16,OrderDate = DateTime.Parse("2015-09-18"), Region = "East", Rep = "Jones", Item = "Pen Set", Units = 16, UnitCost = 15.99, Total = 255.84}, + new {Id=17,OrderDate = DateTime.Parse("2015-10-05"), Region = "Central", Rep = "Morgan", Item = "Binder", Units = 28, UnitCost = 8.99, Total = 251.72}, + new {Id=18,OrderDate = DateTime.Parse("2015-10-22"), Region = "East", Rep = "Jones", Item = "Pen", Units = 64, UnitCost = 8.99, Total = 575.36}, + new {Id=19,OrderDate = DateTime.Parse("2015-11-08"), Region = "East", Rep = "Parent", Item = "Pen", Units = 15, UnitCost = 19.99, Total = 299.85}, + new {Id=20,OrderDate = DateTime.Parse("2015-11-25"), Region = "Central", Rep = "Kivell", Item = "Pen Set", Units = 96, UnitCost = 4.99, Total = 479.04}, + new {Id=21,OrderDate = DateTime.Parse("2015-12-12"), Region = "Central", Rep = "Smith", Item = "Pencil", Units = 67, UnitCost = 1.29, Total = 86.43}, + new {Id=22,OrderDate = DateTime.Parse("2015-12-29"), Region = "East", Rep = "Parent", Item = "Pen Set", Units = 74, UnitCost = 15.99, Total = 1183.26}, + new {Id=23,OrderDate = DateTime.Parse("2016-01-15"), Region = "Central", Rep = "Gill", Item = "Binder", Units = 46, UnitCost = 8.99, Total = 413.54}, + new {Id=24,OrderDate = DateTime.Parse("2016-02-01"), Region = "Central", Rep = "Smith", Item = "Binder", Units = 87, UnitCost = 15, Total = 1305}, + new {Id=25,OrderDate = DateTime.Parse("2016-02-18"), Region = "East", Rep = "Jones", Item = "Binder", Units = 4, UnitCost = 4.99, Total = 19.96}, + new {Id=26,OrderDate = DateTime.Parse("2016-03-07"), Region = "West", Rep = "Sorvino", Item = "Binder", Units = 7, UnitCost = 19.99, Total = 139.93}, + new {Id=27,OrderDate = DateTime.Parse("2016-03-24"), Region = "Central", Rep = "Jardine", Item = "Pen Set", Units = 50, UnitCost = 4.99, Total = 249.5}, + new {Id=28,OrderDate = DateTime.Parse("2016-04-10"), Region = "Central", Rep = "Andrews", Item = "Pencil", Units = 66, UnitCost = 1.99, Total = 131.34}, + new {Id=29,OrderDate = DateTime.Parse("2016-04-27"), Region = "East", Rep = "Howard", Item = "Pen", Units = 96, UnitCost = 4.99, Total = 479.04}, + new {Id=30,OrderDate = DateTime.Parse("2016-05-14"), Region = "Central", Rep = "Gill", Item = "Pencil", Units = 53, UnitCost = 1.29, Total = 68.37}, + new {Id=31,OrderDate = DateTime.Parse("2016-05-31"), Region = "Central", Rep = "Gill", Item = "Binder", Units = 80, UnitCost = 8.99, Total = 719.2}, + new {Id=32,OrderDate = DateTime.Parse("2016-06-17"), Region = "Central", Rep = "Kivell", Item = "Desk", Units = 5, UnitCost = 125, Total = 625}, + new {Id=33,OrderDate = DateTime.Parse("2016-07-04"), Region = "East", Rep = "Jones", Item = "Pen Set", Units = 62, UnitCost = 4.99, Total = 309.38}, + new {Id=34,OrderDate = DateTime.Parse("2016-07-21"), Region = "Central", Rep = "Morgan", Item = "Pen Set", Units = 55, UnitCost = 12.49, Total = 686.95}, + new {Id=35,OrderDate = DateTime.Parse("2016-08-07"), Region = "Central", Rep = "Kivell", Item = "Pen Set", Units = 42, UnitCost = 23.95, Total = 1005.9}, + new {Id=36,OrderDate = DateTime.Parse("2016-08-24"), Region = "West", Rep = "Sorvino", Item = "Desk", Units = 3, UnitCost = 275, Total = 825}, + new {Id=37,OrderDate = DateTime.Parse("2016-09-10"), Region = "Central", Rep = "Gill", Item = "Pencil", Units = 7, UnitCost = 1.29, Total = 9.03}, + new {Id=38,OrderDate = DateTime.Parse("2016-09-27"), Region = "West", Rep = "Sorvino", Item = "Pen", Units = 76, UnitCost = 1.99, Total = 151.24}, + new {Id=39,OrderDate = DateTime.Parse("2016-10-14"), Region = "West", Rep = "Thompson", Item = "Binder", Units = 57, UnitCost = 19.99, Total = 1139.43}, + new {Id=40,OrderDate = DateTime.Parse("2016-10-31"), Region = "Central", Rep = "Andrews", Item = "Pencil", Units = 14, UnitCost = 1.29, Total = 18.06}, + new {Id=41,OrderDate = DateTime.Parse("2016-11-17"), Region = "Central", Rep = "Jardine", Item = "Binder", Units = 11, UnitCost = 4.99, Total = 54.89}, + new {Id=42,OrderDate = DateTime.Parse("2016-12-04"), Region = "Central", Rep = "Jardine", Item = "Binder", Units = 94, UnitCost = 19.99, Total = 1879.06}, + new {Id=43,OrderDate = DateTime.Parse("2016-12-21"), Region = "Central", Rep = "Andrews", Item = "Binder", Units = 28, UnitCost = 4.99, Total = 139.72} + }; + ws.FirstCell().InsertTable(data); + return wb; + } + + [Test] + public void Hlookup() + { + // Range lookup false + var value = workbook.Evaluate(@"=HLOOKUP(""Total"",Data!$A$1:$H$70,4,FALSE)"); + Assert.AreEqual(179.64, value); + } + + [Test] + public void Vlookup() + { + // Range lookup false + var value = workbook.Evaluate("=VLOOKUP(3,Data!$A$1:$H$70,3,FALSE)"); + Assert.AreEqual("Central", value); + + value = workbook.Evaluate("=VLOOKUP(DATE(2015,5,22),Data!B:H,7,FALSE)"); + Assert.AreEqual(63.68, value); + + value = workbook.Evaluate(@"=VLOOKUP(""Central"",Data!C:D,2,FALSE)"); + Assert.AreEqual("Kivell", value); + + // Range lookup true + value = workbook.Evaluate("=VLOOKUP(3,Data!$A$1:$H$70,8,TRUE)"); + Assert.AreEqual(179.64, value); + + value = workbook.Evaluate("=VLOOKUP(14.5,Data!$A$1:$H$70,8,TRUE)"); + Assert.AreEqual(174.65, value); + + value = workbook.Evaluate("=VLOOKUP(50,Data!$A$1:$H$70,8,TRUE)"); + Assert.AreEqual(139.72, value); + } + + [Test] + public void Vlookup_Exceptions() + { + Assert.That(() => workbook.Evaluate(@"=VLOOKUP("""",Data!$A$1:$H$70,3,FALSE)"), Throws.Exception); + Assert.That(() => workbook.Evaluate(@"=VLOOKUP(50,Data!$A$1:$H$70,3,FALSE)"), Throws.Exception); + Assert.That(() => workbook.Evaluate(@"=VLOOKUP(20,Data!$A$1:$H$70,9,FALSE)"), Throws.Exception); + + Assert.That(() => workbook.Evaluate(@"=VLOOKUP(-1,Data!$A$1:$H$70,9,TRUE)"), Throws.Exception); + } + } +} \ No newline at end of file