SPB Git

Repository Public

LookupTests.swift 15.2 KB 288 lines swift

1import Foundation2import Testing3import SheetCore4@testable import FunctionsLookup56/// Excel behaviour that LibreOffice cannot arbitrate (it differs or lacks the feature), written by hand.7@Suite struct LookupEdgeCaseTests {8    /// A:number key (unsorted), B:text, C:number; E: sorted ascending; G: mixed types.9    func sample() -> Book {10        let b = Book()11        b.column("A1", [30, 10, 20, 10, 50])12        b.column("B1", ["thirty", "ten", "twenty", "TEN-bis", "fifty"])13        b.column("C1", [3, 1, 2, 1.5, 5])14        b.column("E1", [1, 2, 4, 8, 16, 32])15        b.column("G1", [5, "5", true, nil, .error(.div0), "", 0, false, "abc"])16        return b17    }1819    @Test func typeAwareMatching() {20        let b = sample()21        // text "5" never matches the number 5 and vice versa22        #expect(b.eval("=MATCH(\"5\",G1:G9,0)") == 2)23        #expect(b.eval("=MATCH(5,G1:G9,0)") == 1)24        #expect(b.eval("=VLOOKUP(\"10\",A1:C5,2,FALSE)") == NA)25        // logical values only match logical values (TRUE is not 1, FALSE is not 0)26        #expect(b.eval("=MATCH(TRUE,G1:G9,0)") == 3)27        #expect(b.eval("=MATCH(1,G1:G9,0)") == NA)28        #expect(b.eval("=MATCH(0,G1:G9,0)") == 7)29        #expect(b.eval("=MATCH(FALSE,G1:G9,0)") == 8)30        #expect(b.eval("=XMATCH(FALSE,G1:G9)") == 8)31        // case-insensitive text32        #expect(b.eval("=MATCH(\"ABC\",G1:G9,0)") == 9)33        #expect(b.eval("=XLOOKUP(\"TEN\",B1:B5,A1:A5)") == 10)34        // errors inside the lookup range are skipped, an error lookup value propagates35        #expect(b.eval("=MATCH(0,G1:G9,0)") == 7)36        #expect(b.eval("=MATCH(1/0,G1:G9,0)") == .error(.div0))37        #expect(b.eval("=VLOOKUP(NA(),A1:C5,2,FALSE)") == NA)38        #expect(b.eval("=XLOOKUP(SQRT(-1),A1:A5,B1:B5,\"x\")").isError)39    }4041    @Test func blankHandling() {42        let b = sample()43        // classic functions: an empty lookup cell counts as 0; empty cells in the range never match 044        #expect(b.eval("=MATCH(Z99,G1:G9,0)") == 7)45        #expect(b.eval("=MATCH(\"\",G1:G9,0)") == 6)46        // XMATCH/XLOOKUP: an empty lookup value matches the first empty cell or empty string47        #expect(b.eval("=XMATCH(Z99,G1:G9)") == 4)48        #expect(b.eval("=XMATCH(\"\",G1:G9)") == 4)49        // a found empty cell is returned as blank (displayed 0 in a cell)50        b.set("K1", "=VLOOKUP(20,A1:D5,4,FALSE)")51        #expect(b.get("K1") == 0)52    }5354    @Test func exactMatchReturnsFirstAndReverseReturnsLast() {55        let b = sample()56        #expect(b.eval("=VLOOKUP(10,A1:C5,2,FALSE)") == "ten")57        #expect(b.eval("=MATCH(10,A1:A5,0)") == 2)58        #expect(b.eval("=XMATCH(10,A1:A5,0,-1)") == 4)59        #expect(b.eval("=XLOOKUP(10,A1:A5,B1:B5,,0,-1)") == "TEN-bis")60    }6162    @Test func approximateOnSortedData() {63        let b = sample()64        #expect(b.eval("=MATCH(8,E1:E6)") == 4)65        #expect(b.eval("=MATCH(9,E1:E6,1)") == 4)66        #expect(b.eval("=MATCH(0.5,E1:E6,1)") == NA)67        #expect(b.eval("=MATCH(1000,E1:E6,1)") == 6)68        #expect(b.eval("=VLOOKUP(31.9,E1:E6,1)") == 16)69        #expect(b.eval("=LOOKUP(5,E1:E6)") == 4)70        // text lookup in a numeric column and the reverse find nothing71        #expect(b.eval("=MATCH(\"a\",E1:E6,1)") == NA)72        #expect(b.eval("=MATCH(5,{\"a\",\"b\",\"c\"},1)") == NA)73        // numbers sort before text: the binary search lands on the last number74        #expect(b.eval("=MATCH(99,{1,2,3,\"a\",\"b\"},1)") == 3)75        #expect(b.eval("=MATCH(\"b\",{1,2,3,\"a\",\"b\",\"c\"},1)") == 5)76        // duplicates: approximate match returns the last equal item77        #expect(b.eval("=MATCH(2,{1,2,2,2,3},1)") == 4)78        // trailing and interior empty cells do not disturb the search79        #expect(b.eval("=MATCH(8,E1:E20,1)") == 4)80        #expect(b.eval("=MATCH(100,E1:E20,1)") == 6)81        b.column("M1", [1, 2, nil, nil, 5, 6, nil, 8])82        for (k, want) in [(1.0, 1.0), (2, 2), (4.5, 2), (5, 5), (7, 6), (8, 8), (9, 8)] {83            #expect(b.eval("=MATCH(\(k),M1:M8,1)") == .number(want), "key \(k)")84        }85        // descending data, match_type -1: smallest value ≥ the key86        #expect(b.eval("=MATCH(5,{9,7,5,3,1},-1)") == 3)87        #expect(b.eval("=MATCH(6,{9,7,5,3,1},-1)") == 2)88        #expect(b.eval("=MATCH(10,{9,7,5,3,1},-1)") == NA)89        #expect(b.eval("=MATCH(0,{9,7,5,3,1},-1)") == 5)90    }9192    @Test func wildcards() {93        let b = Book()94        b.column("A1", ["apple", "apricot", "a*b", "what?", "tilde~x", "banana", ""])95        b.column("B1", [1, 2, 3, 4, 5, 6, 7])96        #expect(b.eval("=MATCH(\"ap*\",A1:A7,0)") == 1)97        #expect(b.eval("=MATCH(\"*an*\",A1:A7,0)") == 6)98        #expect(b.eval("=MATCH(\"a~*b\",A1:A7,0)") == 3)99        #expect(b.eval("=MATCH(\"what~?\",A1:A7,0)") == 4)100        #expect(b.eval("=MATCH(\"tilde~~x\",A1:A7,0)") == 5)101        #expect(b.eval("=MATCH(\"?????\",A1:A7,0)") == 1)102        #expect(b.eval("=MATCH(\"??????\",A1:A7,0)") == 6)103        #expect(b.eval("=VLOOKUP(\"B?N*\",A1:B7,2,FALSE)") == 6)104        // XLOOKUP/XMATCH treat * and ? literally unless match_mode is 2105        #expect(b.eval("=XMATCH(\"ap*\",A1:A7)") == NA)106        #expect(b.eval("=XMATCH(\"a*b\",A1:A7)") == 3)107        #expect(b.eval("=XMATCH(\"ap*\",A1:A7,2)") == 1)108        #expect(b.eval("=XLOOKUP(\"*t\",A1:A7,B1:B7,,2)") == 2)109        #expect(b.eval("=XLOOKUP(\"*t\",A1:A7,B1:B7,\"none\")") == "none")110        // wildcards never match numbers111        #expect(b.eval("=MATCH(\"*\",B1:B7,0)") == NA)112        // approximate matching ignores wildcards113        #expect(b.eval("=VLOOKUP(\"ap*\",A1:B2,2,TRUE)") == NA)114        #expect(Wildcard("a*c?e").matches("ABxxCdE"))115        #expect(!Wildcard("a*c?e").matches("abxxcde!"))116        #expect(Wildcard("**").matches(""))117        #expect(!Wildcard("?").matches(""))118        #expect(Wildcard("~").matches("~"))119    }120121    @Test func errorResults() {122        let b = sample()123        #expect(b.eval("=VLOOKUP(10,A1:C5,0,FALSE)") == .error(.value))124        #expect(b.eval("=VLOOKUP(10,A1:C5,4,FALSE)") == .error(.ref))125        #expect(b.eval("=HLOOKUP(30,A1:C5,6,FALSE)") == .error(.ref))126        #expect(b.eval("=VLOOKUP(99,A1:C5,2,FALSE)") == NA)127        #expect(b.eval("=MATCH(10,A1:B5,0)") == NA)                      // two-dimensional lookup array128        #expect(b.eval("=XMATCH(10,A1:B5)") == .error(.value))129        #expect(b.eval("=XMATCH(10,A1:A5,3)") == .error(.value))130        #expect(b.eval("=XMATCH(10,A1:A5,0,0)") == .error(.value))131        #expect(b.eval("=XMATCH(\"a*\",B1:B5,2,2)") == .error(.value))     // wildcard with binary search132        #expect(b.eval("=XLOOKUP(10,A1:A5,B1:B4)") == .error(.value))      // size mismatch133        #expect(b.eval("=XLOOKUP(10,A1:B5,C1:C5)") == .error(.value))134        #expect(b.eval("=LOOKUP(0,E1:E6)") == NA)135        #expect(b.eval("=ADDRESS(0,1)") == .error(.value))136        #expect(b.eval("=ADDRESS(1,16385)") == .error(.value))137        #expect(b.eval("=ADDRESS(1,1,9)") == .error(.value))138        #expect(b.eval("=AREAS(5)") == .error(.value))139        #expect(b.eval("=VLOOKUP(10,A1:C5,2,\"x\")") == .error(.value))140    }141142    @Test func omittedAndEmptyRangeLookupArguments() {143        let b = sample()144        #expect(b.eval("=VLOOKUP(9,E1:E6,1)") == 8)                 // omitted → approximate145        #expect(b.eval("=VLOOKUP(9,E1:E6,1,)") == NA)               // empty → FALSE (exact)146        #expect(b.eval("=MATCH(9,E1:E6,)") == NA)                   // empty match_type → 0147        #expect(b.eval("=VLOOKUP(9,E1:E6,1,1)") == 8)148        #expect(b.eval("=VLOOKUP(9,E1:E6,1,0)") == NA)149    }150151    @Test func xlookupReturnsReferences() {152        let b = sample()153        // the result is a reference, so it can be a range endpoint or feed reference functions154        #expect(b.eval("=SUM(XLOOKUP(10,A1:A5,C1:C5):XLOOKUP(50,A1:A5,C1:C5))") == 9.5)155        #expect(b.eval("=ROW(XLOOKUP(20,A1:A5,C1:C5))") == 3)156        #expect(b.eval("=COLUMN(XLOOKUP(20,A1:A5,C1:C5))") == 3)157        #expect(b.eval("=COLUMNS(XLOOKUP(20,A1:A5,B1:C5))") == 2)158        #expect(b.eval("=SUM(OFFSET(XLOOKUP(30,A1:A5,C1:C5),0,0,3,1))") == 6)159        // with array constants a value comes back160        #expect(b.eval("=XLOOKUP(2,{1,2,3},{\"a\",\"b\",\"c\"})") == "b")161        #expect(b.eval("=XLOOKUP(2,{1;2;3},{\"a\",\"x\";\"b\",\"y\";\"c\",\"z\"})") == grid([["b", "y"]]))162        // if_not_found is evaluated and returned as is163        #expect(b.eval("=XLOOKUP(99,A1:A5,C1:C5,\"n/a\")") == "n/a")164        #expect(b.eval("=SUM(XLOOKUP(99,A1:A5,C1:C5,E1:E3))") == 7)165        #expect(b.eval("=XLOOKUP(99,A1:A5,C1:C5)") == NA)166    }167168    @Test func xlookupSpillsIntoCells() {169        let b = sample()170        b.set("J1", "=XLOOKUP(20,A1:A5,B1:C5)")171        #expect(b.get("J1") == "twenty")172        #expect(b.get("K1") == 2)173        b.set("J3", "=XLOOKUP(E1:E3,A1:A5,B1:B5,\"-\")")          // one result per lookup value174        #expect(b.get("J3") == "-")175        #expect(b.get("J5") == "-")176        b.set("J8", "=XLOOKUP({10;50;7},A1:A5,C1:C5,0)")177        #expect([b.get("J8"), b.get("J9"), b.get("J10")] == [1, 5, 0])178        // dependents recalculate when the looked-up data changes179        b.set("C3", 22)180        #expect(b.get("K1") == 22)181    }182183    @Test func approximateModesOnUnsortedData() {184        let b = sample()185        // XLOOKUP match modes -1 / 1 scan the whole range (no sorting required)186        #expect(b.eval("=XLOOKUP(25,A1:A5,B1:B5,,-1)") == "twenty")187        #expect(b.eval("=XLOOKUP(25,A1:A5,B1:B5,,1)") == "thirty")188        #expect(b.eval("=XLOOKUP(5,A1:A5,B1:B5,\"below\",-1)") == "below")189        #expect(b.eval("=XLOOKUP(60,A1:A5,B1:B5,\"above\",1)") == "above")190        #expect(b.eval("=XMATCH(15,A1:A5,-1)") == 2)                 // first of the two 10s191        #expect(b.eval("=XMATCH(15,A1:A5,-1,-1)") == 4)              // last of the two 10s192        #expect(b.eval("=XMATCH(\"m\",{\"z\",\"a\",\"k\",\"p\"},-1)") == 3)193        #expect(b.eval("=XMATCH(\"m\",{\"z\",\"a\",\"k\",\"p\"},1)") == 4)194    }195196    @Test func binarySearchModes() {197        let b = sample()198        #expect(b.eval("=XMATCH(8,E1:E6,0,2)") == 4)199        #expect(b.eval("=XMATCH(9,E1:E6,0,2)") == NA)200        #expect(b.eval("=XMATCH(9,E1:E6,-1,2)") == 4)201        #expect(b.eval("=XMATCH(9,E1:E6,1,2)") == 5)202        #expect(b.eval("=XMATCH(64,E1:E6,1,2)") == NA)203        #expect(b.eval("=XMATCH(0,E1:E6,-1,2)") == NA)204        #expect(b.eval("=XMATCH(9,{32,16,8,4,2,1},-1,-2)") == 3)205        #expect(b.eval("=XMATCH(9,{32,16,8,4,2,1},1,-2)") == 2)206        #expect(b.eval("=XMATCH(4,{32,16,8,4,2,1},0,-2)") == 4)207        #expect(b.eval("=XLOOKUP(\"kilo\",{\"alpha\",\"golf\",\"kilo\",\"zulu\"},{1,2,3,4},,0,2)") == 3)208    }209210    @Test func lookupVectorAndArrayForms() {211        let b = sample()212        #expect(b.eval("=LOOKUP(4,E1:E6,{\"a\";\"b\";\"c\";\"d\";\"e\";\"f\"})") == "c")213        #expect(b.eval("=LOOKUP(5,E1:E6,{\"a\",\"b\",\"c\",\"d\",\"e\",\"f\"})") == "c")   // result vector may be a row214        #expect(b.eval("=LOOKUP(2,{1,2,3;\"x\",\"y\",\"z\"})") == "y")                  // wider than tall: first row → last row215        #expect(b.eval("=LOOKUP(2,{1,\"x\";2,\"y\";3,\"z\"})") == "y")                  // otherwise first column → last column216        #expect(b.eval("=LOOKUP(2,{1,2;3,4})") == 2)                                    // square: first column {1;3} → row 1 → last column217        #expect(b.eval("=LOOKUP(4,E1:E6,C1:C2)") == NA)                                 // result vector too short218        #expect(b.eval("=LOOKUP({1;4;9},E1:E6)") == grid([[1], [4], [8]]))219    }220221    @Test func referenceHelpers() {222        let b = sample()223        #expect(b.eval("=ADDRESS(2,3)") == "$C$2")224        #expect(b.eval("=ADDRESS(2,3,2)") == "C$2")225        #expect(b.eval("=ADDRESS(2,3,3)") == "$C2")226        #expect(b.eval("=ADDRESS(2,3,4)") == "C2")227        #expect(b.eval("=ADDRESS(2,3,2,FALSE)") == "R2C[3]")228        #expect(b.eval("=ADDRESS(2,3,1,FALSE,\"[Book1]Sheet1\")") == "'[Book1]Sheet1'!R2C3")229        #expect(b.eval("=ADDRESS(2,3,1,TRUE,\"EXCEL SHEET\")") == "'EXCEL SHEET'!$C$2")230        #expect(b.eval("=ADDRESS(1,1,4,TRUE,\"Data\")") == "Data!A1")231        #expect(b.eval("=ADDRESS(1048576,16384,4)") == "XFD1048576")232        #expect(b.eval("=SUM(INDIRECT(ADDRESS(1,5)&\":\"&ADDRESS(3,5)))") == 7)233        #expect(b.eval("=AREAS(A1:C5)") == 1)234        #expect(b.eval("=HYPERLINK(\"https://example.com\",\"Example\")") == "Example")235        #expect(b.eval("=HYPERLINK(\"https://example.com\")") == "https://example.com")236        #expect(b.eval("=HYPERLINK(\"https://example.com\",C5)") == 5)237        #expect(b.eval("=TRANSPOSE({1,2;3,4;5,6})") == grid([[1, 3, 5], [2, 4, 6]]))238        #expect(b.eval("=TRANSPOSE(A1:B2)") == grid([[30, 10], ["thirty", "ten"]]))239        #expect(b.eval("=SUM(TRANSPOSE(E1:E6)*{1,1,1,1,1,1})") == 63)240        b.set("J1", "=TRANSPOSE(E1:E3)")241        #expect([b.get("J1"), b.get("K1"), b.get("L1")] == [1, 2, 4])242    }243244    @Test func liftedKeysUseTheHashIndexConsistently() {245        // more than 4 keys against more than 16 items takes the hash-index path; it must agree with the linear scan246        let b = Book()247        var values = [Value?]()248        for i in 0..<400 { values.append(i % 7 == 3 ? .text("Key\(i % 50)") : .number(Double((i * 37) % 101))) }249        values[13] = .bool(true); values[200] = .bool(false); values[77] = nil; values[90] = .text("")250        b.column("A1", values)251        b.column("B1", (0..<400).map { .number(Double($0)) })252        let keys: [String] = ["5", "100", "\"key3\"", "\"KEY45\"", "TRUE", "FALSE", "\"missing\"", "101", "\"ke*\"", "0", "\"\""]253        let list = "{" + keys.joined(separator: ";") + "}"254        for fn in ["MATCH(%@,A1:A400,0)", "XMATCH(%@,A1:A400)", "XMATCH(%@,A1:A400,0,-1)", "XMATCH(%@,A1:A400,2)",255                   "VLOOKUP(%@,A1:B400,2,FALSE)", "XLOOKUP(%@,A1:A400,B1:B400,-1)", "XLOOKUP(%@,A1:A400,B1:B400,-1,0,-1)"] {256            guard case .array(let lifted) = b.eval("=" + fn.replacingOccurrences(of: "%@", with: list)) else { Issue.record("no array for \(fn)"); continue }257            #expect(lifted.rows == keys.count && lifted.cols == 1)258            for (i, k) in keys.enumerated() {259                #expect(lifted.items[i] == b.eval("=" + fn.replacingOccurrences(of: "%@", with: k)), "\(fn) key \(k)")260            }261        }262    }263264    @Test func wholeColumnExactMatchIsFast() {265        let b = Book()266        let n = 200_000267        b.engine.transaction("fill") {268            for i in 0..<n {269                b.engine.set(sheet: 0, row: i, col: 0, .value(.number(Double(i * 2))))270                b.engine.set(sheet: 0, row: i, col: 1, .value(.number(Double(i))))271            }272        }273        b.engine.recalculate()274        let clock = ContinuousClock()275        var result = Value.blank276        let single = clock.measure { result = b.eval("=VLOOKUP(399998,A:B,2,FALSE)") }277        #expect(result == .number(199_999))278        #expect(single < .milliseconds(400), "single whole-column lookup took \(single)")279        // 2 000 lookup values against the same columns: one hash index instead of 2 000 scans280        let many = clock.measure { result = b.eval("=SUM(XLOOKUP(SEQUENCE(2000,1,0,200),A:A,B:B,0))") }281        #expect(result == .number((0..<2000).reduce(0.0) { $0 + Double($1 * 100) }))282        #expect(many < .milliseconds(1500), "2 000 lifted lookups took \(many)")283        #expect(b.eval("=MATCH(123456,A:A,0)") == 61729)284        #expect(b.eval("=XMATCH(123457,A:A)") == NA)285        #expect(b.eval("=MATCH(123457,A:A,1)") == 61729)286    }287}288
Files main
Menu

Theme