SPB Git

Repository Public

EdgeTests.swift 14.3 KB 240 lines swift

1import Foundation2import Testing3import SheetCore4import SheetFormat5@testable import FunctionsText67func n(_ v: Value) -> Double { v.numberValue ?? .nan }89@Suite struct RegistrationTests {10    @Test func registrationIsComplete() {11        _ = Book.registered12        for name in TextFunctionsCatalog.all {13            guard let spec = FunctionRegistry.standard.lookup(name) else { Issue.record("\(name) is not registered"); continue }14            #expect(spec.signature.hasPrefix(name + "("), "\(name) signature: \(spec.signature)")15            #expect(spec.signature.hasSuffix(")"))16            #expect(!spec.summary.isEmpty && spec.summary.hasSuffix("."), "\(name) summary")17            #expect(!spec.summary.contains("\n"))18            #expect(spec.category == (TextFunctionsCatalog.dateTime.contains(name) ? .dateTime : .text), "\(name) category")19            #expect(spec.minArgs <= spec.maxArgs)20        }21        // a private registry gets exactly the library's functions22        let r = FunctionRegistry()23        TextFunctions.register(into: r)24        let extras = Set(r.all.map(\.name)).subtracting(TextFunctionsCatalog.all)25        #expect(extras.isEmpty)26        #expect(Set(TextFunctionsCatalog.all).isSubset(of: Set(r.all.map(\.name))))27        #expect(TextFunctionsCatalog.text.count == 41 && TextFunctionsCatalog.dateTime.count == 23)28    }2930    @Test func arityIsEnforced() {31        let b = Book()32        #expect(b.eval("LEFT()").isError)33        #expect(b.eval("LEFT(\"a\",1,2)").isError)34        #expect(b.eval("DATE(2023,1)").isError)35        #expect(b.eval("TEXTJOIN(\",\",TRUE)").isError)36    }37}3839@Suite struct TextEdgeTests {40    let b = Book()4142    @Test func liftingOverArraysAndRanges() {43        #expect(b.eval("SUM(LEN({\"a\",\"bb\",\"ccc\"}))") == .number(6))44        #expect(b.eval("TEXTJOIN(\",\",TRUE,UPPER({\"a\",\"b\"}))") == .text("A,B"))45        #expect(b.eval("INDEX(LEFT({\"abc\",\"def\"},2),1,2)") == .text("de"))46        #expect(b.eval("INDEX(MID(\"abcdef\",{1;3;5},2),3,1)") == .text("ef"))47        #expect(b.eval("SUM(YEAR({45000,36526}))") == .number(4023))48        #expect(b.eval("TEXTJOIN(\"|\",TRUE,TEXT({1,2.5},\"0.0\"))") == .text("1.0|2.5"))49        #expect(b.eval("SUM(FIND(\"b\",{\"abc\",\"xb\"}))") == .number(4))50        #expect(b.eval("TEXTJOIN(\"/\",TRUE,TEXTBEFORE({\"a-b\",\"c-d\"},\"-\"))") == .text("a/c"))51        #expect(b.eval("INDEX(LEFT({\"abc\",\"def\"},{1;2}),2,2)") == .text("de"))52        b.set("A1", "alpha"); b.set("A2", "beta"); b.set("A3", "gamma"); b.set("A5", "last")53        b.set("B1", "=CONCAT(A1:A3)")54        b.set("B2", "=TEXTJOIN(\"-\",TRUE,A1:A5)")55        b.set("B3", "=TEXTJOIN(\"-\",FALSE,A1:A5)")56        b.set("B4", "=SUM(LEN(A1:A3))")57        b.set("B5", "=ARRAYTOTEXT(A1:A3)")58        b.set("B6", "=LEN(A4)")59        b.set("B7", "=LEFT(A4)&\"|\"&UPPER(A4)&\"|\"&TRIM(A4)")60        b.set("B8", "=UPPER(A1:A3)")61        #expect(b.get("B1") == .text("alphabetagamma"))62        #expect(b.get("B2") == .text("alpha-beta-gamma-last"))63        #expect(b.get("B3") == .text("alpha-beta-gamma--last"))64        #expect(b.get("B4") == .number(14))65        #expect(b.get("B5") == .text("alpha, beta, gamma"))66        #expect(b.get("B6") == .number(0))67        #expect(b.get("B7") == .text("||"))68        #expect(b.get("B8") == .text("ALPHA"))69        #expect(b.get("B9") == .text("BETA"))70    }7172    @Test func errorsAndBlanks() {73        #expect(b.eval("LEFT(1/0,1)") == .error(.div0))74        #expect(b.eval("LEN(NA())") == .error(.na))75        #expect(b.eval("TEXTJOIN(\",\",TRUE,\"a\",NA())") == .error(.na))76        #expect(b.eval("CONCAT(\"a\",{1,#N/A})") == .error(.na))77        #expect(b.eval("INDEX(LEN({\"a\",#N/A,\"ccc\"}),1,2)") == .error(.na))78        #expect(b.eval("INDEX(LEN({\"a\",#N/A,\"ccc\"}),1,3)") == .number(3))79        #expect(b.eval("VALUE(Z99)") == .number(0))80        #expect(b.eval("TEXT(Z99,\"0.00\")") == .text("0.00"))81        #expect(b.eval("LEFT(\"abc\",)") == .text(""))82        #expect(b.eval("T(Z99)") == .text(""))83        #expect(b.eval("EXACT(Z99,\"\")") == .bool(true))84        #expect(b.eval("VALUETOTEXT(NA())") == .text("#N/A"))85        #expect(b.eval("VALUETOTEXT(1/0,1)") == .text("#DIV/0!"))86        #expect(b.eval("VALUETOTEXT(\"a\",2)") == .error(.value))87        #expect(b.eval("ARRAYTOTEXT({1,#N/A},1)") == .text("{1,#N/A}"))88        #expect(b.eval("TEXTAFTER(\"abc\",\"x\",,,,NA())") == .error(.na))89        #expect(b.eval("TEXTBEFORE(\"abc\",\"x\",,,,1/0)") == .error(.div0))90        #expect(b.eval("REPT(\"abc\",11000)") == .error(.value))91        #expect(b.eval("REPT(\"a\",32767)").textValue?.count == 32767)92        #expect(b.eval("VALUE(TRUE)") == .error(.value))93        #expect(b.eval("DATEVALUE(45000)") == .error(.value))94        #expect(b.eval("YEAR(\"abc\")") == .error(.value))95        #expect(b.eval("NETWORKDAYS(45000,45100,{45005,#N/A})") == .error(.na))96        #expect(b.eval("NETWORKDAYS(45000,45100,TRUE)") == .error(.value))97    }9899    @Test func utf16Positions() {100        #expect(b.eval("LEN(\"😀\")") == .number(2))101        #expect(b.eval("LEFT(\"😀a\",2)") == .text("😀"))102        #expect(b.eval("MID(\"a😀b\",2,2)") == .text("😀"))103        #expect(b.eval("RIGHT(\"a😀b\",1)") == .text("b"))104        #expect(b.eval("FIND(\"b\",\"a😀b\")") == .number(4))105        #expect(b.eval("SEARCH(\"B\",\"a😀b\")") == .number(4))106        #expect(b.eval("LEN(\"e\"&UNICHAR(769))") == .number(2))      // combining accent counts separately107        #expect(b.eval("UNICODE(\"😀\")") == .number(128512))108        #expect(b.eval("UNICHAR(55357)") == .error(.na))                 // lone surrogate109        #expect(b.eval("UNICHAR(1114112)") == .error(.value))110        #expect(b.eval("REPLACE(\"a😀b\",2,2,\"-\")") == .text("a-b"))111    }112113    @Test func codePage1252() {114        #expect(b.eval("CHAR(128)") == .text("€"))115        #expect(b.eval("CODE(\"€\")") == .number(128))116        #expect(b.eval("CHAR(233)") == .text("é"))117        #expect(b.eval("CODE(\"é\")") == .number(233))118        #expect(b.eval("CHAR(150)") == .text("–"))119        #expect(b.eval("CODE(\"π\")") == .number(63))120        #expect(b.eval("CODE(CHAR(255))") == .number(255))121        #expect(b.eval("SUM(CODE(CHAR({65,153,200})))") == .number(418))122    }123124    @Test func microsoftDocumentedExamples() {125        #expect(b.eval("PROPER(\"this is a TITLE\")") == .text("This Is A Title"))126        #expect(b.eval("PROPER(\"2-way street\")") == .text("2-Way Street"))127        #expect(b.eval("PROPER(\"76BudGet\")") == .text("76Budget"))128        #expect(b.eval("REPLACE(\"abcdefghijk\",6,5,\"*\")") == .text("abcde*k"))129        #expect(b.eval("REPLACE(\"2009\",3,2,\"10\")") == .text("2010"))130        #expect(b.eval("SEARCH(\"e\",\"Statements\",6)") == .number(7))131        #expect(b.eval("SEARCH(\"margin\",\"Profit Margin\")") == .number(8))132        #expect(b.eval("FIND(\"M\",\"Miriam McGovern\")") == .number(1))133        #expect(b.eval("FIND(\"m\",\"Miriam McGovern\")") == .number(6))134        #expect(b.eval("FIND(\"M\",\"Miriam McGovern\",3)") == .number(8))135        #expect(b.eval("SUBSTITUTE(\"Quarter 1, 2011\",\"1\",\"2\",3)") == .text("Quarter 1, 2012"))136        #expect(b.eval("SUBSTITUTE(\"Sales Data\",\"Sales\",\"Cost\")") == .text("Cost Data"))137        #expect(b.eval("FIXED(1234.567,1)") == .text("1,234.6"))138        #expect(b.eval("FIXED(1234.567,-1)") == .text("1,230"))139        #expect(b.eval("FIXED(-1234.567,-1,TRUE)") == .text("-1230"))140        #expect(b.eval("FIXED(44.332)") == .text("44.33"))141        #expect(b.eval("DOLLAR(1234.567,2)") == .text("$1,234.57"))142        #expect(b.eval("DOLLAR(1234.567,-2)") == .text("$1,200"))143        #expect(b.eval("DOLLAR(-0.123,4)") == .text("-$0.1230"))144        #expect(b.eval("VALUE(\"$1,000\")") == .number(1000))145        #expect(abs(n(b.eval("VALUE(\"16:48:00\")-VALUE(\"12:00:00\")")) - 0.2) < 1e-12)146        #expect(b.eval("TEXTBEFORE(\"Red riding hood's, red hood\",\"hood\")") == .text("Red riding "))147        #expect(b.eval("TEXTBEFORE(\"Red riding hood's, red hood\",\"hood\",2)") == .text("Red riding hood's, red "))148        #expect(b.eval("TEXTBEFORE(\"Red riding hood's, red hood\",\"hood\",-1)") == .text("Red riding hood's, red "))149        #expect(b.eval("TEXTBEFORE(\"Red riding hood's, red hood\",\"hood\",-2)") == .text("Red riding "))150        #expect(b.eval("TEXTBEFORE(\"Red riding hood's, red hood\",\"Red\")") == .text(""))151        #expect(b.eval("TEXTBEFORE(\"Red riding hood's, red hood\",\"basket\")") == .error(.na))152        #expect(b.eval("TEXTAFTER(\"Red riding hood's, red hood\",\"Red\")") == .text(" riding hood's, red hood"))153        #expect(b.eval("TEXTAFTER(\"Red riding hood's, red hood\",\"red\",1,1)") == .text(" riding hood's, red hood"))154        #expect(b.eval("TEXTAFTER(\"Red riding hood's, red hood\",\"red\",-1,1)") == .text(" hood"))155        #expect(b.eval("TEXTAFTER(\"Red riding hood's, red hood\",\"basket\")") == .error(.na))156        #expect(b.eval("TEXTJOIN(\" \",TRUE,TEXTSPLIT(\"Dakota Lennon Sanchez\",\" \"))") == .text("Dakota Lennon Sanchez"))157        #expect(b.eval("COLUMNS(TEXTSPLIT(\"Dakota Lennon Sanchez\",\" \"))") == .number(3))158        #expect(b.eval("TEXTJOIN(\", \",TRUE,\"The\",\"sun\",\"will\",\"come\",\"up\",\"tomorrow.\")") == .text("The, sun, will, come, up, tomorrow."))159        #expect(b.eval("REGEXTEST(\"alfalfa\",\"a\")") == .bool(true))160        #expect(b.eval("REGEXEXTRACT(\"DylanWilliams\",\"[A-Z][a-z]+\")") == .text("Dylan"))161        #expect(b.eval("TEXTJOIN(\",\",TRUE,REGEXEXTRACT(\"DylanWilliams\",\"[A-Z][a-z]+\",1))") == .text("Dylan,Williams"))162        #expect(b.eval("REGEXREPLACE(\"Sonia Rees(378) 555-4195\",\"[0-9]+-\",\"***-\")") == .text("Sonia Rees(378) ***-4195"))163        #expect(b.eval("REGEXREPLACE(\"SoniaBrown\",\"([A-Z][a-z]+)([A-Z][a-z]+)\",\"$2, $1\")") == .text("Brown, Sonia"))164        #expect(b.eval("ENCODEURL(\"a~b.c-d_e\")") == .text("a~b.c-d_e"))165        #expect(b.eval("ENCODEURL(\"\")") == .text(""))166    }167168    @Test func textFunction() {169        #expect(b.eval("TEXT(TRUE,\"0\")") == .text("TRUE"))170        #expect(b.eval("TEXT(-1,\"yyyy-mm-dd\")") == .error(.value))171        #expect(b.eval("TEXT(5,\"\")") == .text(""))172        #expect(b.eval("TEXT(3.14159265358979,\"General\")") == .text("3.141592654"))173        #expect(b.eval("TEXT(-1/3,\"General\")") == .text("-0.333333333"))174        #expect(b.eval("TEXT(123456789012,\"General\")") == .text("1.23457E+11"))175        #expect(b.eval("1/3&\"\"") == .text("0.333333333333333"))176        #expect(b.eval("TEXT(60,\"yyyy-mm-dd\")") == .text("1900-02-29"))177        #expect(b.eval("TEXT(\"2023-03-15\",\"dddd\")") == .text("Wednesday"))178        #expect(b.eval("TEXT(\"abc\",\"0;0;0;\"\"<\"\"@\"\">\"\"\")") == .text("<abc>"))179        #expect(b.eval("TEXT(1234.5,\"[$-409]#,##0.00\")") == .text("1,234.50"))180        #expect(b.eval("TEXT(0.5,\"h:mm AM/PM\")") == .text("12:00 PM"))181        #expect(b.eval("TEXT(1/0,\"0\")") == .error(.div0))182    }183184    @Test func numberValueAndValue() {185        #expect(b.eval("NUMBERVALUE(\"1,234.5\")") == .number(1234.5))186        #expect(b.eval("NUMBERVALUE(\"2.5\")") == .number(2.5))187        #expect(b.eval("NUMBERVALUE(\"\")") == .number(0))188        #expect(b.eval("NUMBERVALUE(\"3.5%\")") == .number(0.035))189        #expect(b.eval("NUMBERVALUE(\"2.500,27\",\",\",\".\")") == .number(2500.27))190        #expect(b.eval("NUMBERVALUE(\"1.5\",\"\")") == .error(.value))191        #expect(b.eval("NUMBERVALUE(7)") == .number(7))192        #expect(b.eval("VALUE(\"1,23\")") == .error(.value))193        #expect(b.eval("VALUE(\"  42  \")") == .number(42))194        #expect(b.eval("VALUE(\"2/29/1900\")") == .number(60))195        #expect(b.eval("VALUE(\"1.2.3\")") == .error(.value))196        #expect(b.eval("YEAR(DATEVALUE(\"3/15\"))=YEAR(TODAY())") == .bool(true))197        #expect(b.eval("MONTH(DATEVALUE(\"Mar 15\"))&\"-\"&DAY(DATEVALUE(\"15 Mar\"))") == .text("3-15"))198        #expect(b.eval("DATEVALUE(\"Mar 45\")") == .number(16497))       // March 1945199        #expect(b.eval("DATEVALUE(\"3/2023\")") == .number(44986))200        #expect(b.eval("DATEVALUE(\"13:30\")") == .number(0))201        #expect(b.eval("TIMEVALUE(\"3/15/2023\")") == .number(0))202        #expect(b.eval("TIMEVALUE(\"6 PM\")") == .number(0.75))203        #expect(b.eval("TIMEVALUE(\"13:00 PM\")") == .error(.value))204        #expect(b.eval("VALUE(\"30:00\")") == .number(1.25))205    }206207    @Test func wildcardsAndSubstitute() {208        #expect(b.eval("SEARCH(\"a~*b\",\"xa*b\")") == .number(2))209        #expect(b.eval("SEARCH(\"a?b\",\"xa*b a.b\")") == .number(2))210        #expect(b.eval("SEARCH(\"(\",\"f(x)\")") == .number(2))211        #expect(b.eval("SEARCH(\"[a]\",\"x[a]\")") == .number(2))212        #expect(b.eval("SEARCH(\"*\",\"\")") == .error(.value))213        #expect(b.eval("FIND(\"\",\"\")") == .number(1))214        #expect(b.eval("SEARCH(\"\",\"\")") == .number(1))215        #expect(b.eval("FIND(\"\",\"abc\",4)") == .error(.value))216        #expect(b.eval("SUBSTITUTE(\"aaa\",\"aa\",\"b\")") == .text("ba"))217        #expect(b.eval("SUBSTITUTE(\"abc\",\"b\",\"\",-1)") == .error(.value))218        #expect(b.eval("TRIM(\" a \"&CHAR(160)&\" b \")") == .text("a \u{00A0} b"))219        #expect(b.eval("CLEAN(\"a\"&CHAR(127)&\"b\")") == .text("a\u{7F}b"))220    }221222    @Test func splitDetails() {223        #expect(b.eval("ARRAYTOTEXT(TEXTSPLIT(\"a,b;c\",\",\",\";\"),1)") == .text("{\"a\",\"b\";\"c\",#N/A}"))224        #expect(b.eval("ARRAYTOTEXT(TEXTSPLIT(\"a,b;c\",\",\",\";\",,,\"-\"),1)") == .text("{\"a\",\"b\";\"c\",\"-\"}"))225        #expect(b.eval("ARRAYTOTEXT(TEXTSPLIT(\"a,,b\",\",\"),1)") == .text("{\"a\",\"\",\"b\"}"))226        #expect(b.eval("ARRAYTOTEXT(TEXTSPLIT(\"a,,b\",\",\",,TRUE),1)") == .text("{\"a\",\"b\"}"))227        #expect(b.eval("ARRAYTOTEXT(TEXTSPLIT(\"a1b22c\",{\"1\",\"22\"}),1)") == .text("{\"a\",\"b\",\"c\"}"))228        #expect(b.eval("TEXTSPLIT(\",\",\",\",,TRUE)") == .error(.calc))229        #expect(b.eval("TEXTSPLIT(\"abc\",)") == .error(.value))230        #expect(b.eval("TEXTSPLIT(1/0,\",\")") == .error(.div0))231        #expect(b.eval("TEXTBEFORE(\"abc\",\"\")") == .text(""))232        #expect(b.eval("TEXTAFTER(\"abc\",\"\")") == .text("abc"))233        #expect(b.eval("TEXTBEFORE(\"abc\",\"\",-1)") == .text("abc"))234        #expect(b.eval("TEXTAFTER(\"abc\",\"\",-1)") == .text(""))235        #expect(b.eval("TEXTBEFORE(\"abc\",\"b\",5)") == .error(.value))   // instance beyond the text length236        #expect(b.eval("TEXTBEFORE(\"abc\",\"b\",1,2)") == .error(.value))237        #expect(b.eval("VALUETOTEXT(\"a\"\"b\",1)") == .text("\"a\"\"\"\"b\"".replacingOccurrences(of: "\"\"\"\"", with: "\"\"")))238    }239}240
Files main
Menu

Theme