Repository Public
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