1package functions23import (4 "strings"5 "unicode"6 "unicode/utf8"78 "golang.org/x/text/cases"9 "golang.org/x/text/language"1011 "src.rest/cat/012/internal/numfmt"12 "src.rest/cat/012/internal/value"13)1415// maxText is the longest text a function may build, as in Sheets.16const maxText = 500001718func init() {19 define(20 &FuncDef{Name: "CONCATENATE", Args: "string1, [string2, ...]", Desc: "Join text, including every cell of ranges", Min: 1, Max: -1,21 eval: concatenate},22 &FuncDef{Name: "CONCAT", Args: "value1, value2", Desc: "Join two values as text", Min: 2, Max: 2,23 eval: concat},24 &FuncDef{Name: "TEXTJOIN", Args: "delimiter, ignore_empty, text1, [text2, ...]", Desc: "Join text with a delimiter", Min: 3, Max: -1,25 eval: textJoin},26 &FuncDef{Name: "LEFT", Args: "string, [number_of_characters]", Desc: "The first characters of text", Min: 1, Max: 2,27 eval: textSlice(func(r []rune, n int) string { return string(r[:min(n, len(r))]) })},28 &FuncDef{Name: "RIGHT", Args: "string, [number_of_characters]", Desc: "The last characters of text", Min: 1, Max: 2,29 eval: textSlice(func(r []rune, n int) string { return string(r[len(r)-min(n, len(r)):]) })},30 &FuncDef{Name: "MID", Args: "string, starting_at, extract_length", Desc: "Characters from the middle of text", Min: 3, Max: 3,31 eval: mid},32 &FuncDef{Name: "LEN", Args: "text", Desc: "Number of characters in text: an emoji is one, where Sheets counts two", Min: 1, Max: 1,33 eval: textFn(func(s string) Value { return num(float64(runeLen(s))) })},34 &FuncDef{Name: "UPPER", Args: "text", Desc: "Text in upper case: ß is SS", Min: 1, Max: 1,35 eval: textFn(func(s string) Value { return str(upper(s)) })},36 &FuncDef{Name: "LOWER", Args: "text", Desc: "Text in lower case", Min: 1, Max: 1,37 eval: textFn(func(s string) Value { return str(strings.ToLower(s)) })},38 &FuncDef{Name: "PROPER", Args: "text", Desc: "Text with each word capitalized", Min: 1, Max: 1,39 eval: textFn(func(s string) Value { return str(proper(s)) })},40 &FuncDef{Name: "TRIM", Args: "text", Desc: "Text without leading, trailing and repeated spaces", Min: 1, Max: 1,41 eval: textFn(trimSpaces)},42 &FuncDef{Name: "SUBSTITUTE", Args: "text, search_for, replace_with, [occurrence_number]", Desc: "Replace occurrences of text", Min: 3, Max: 4,43 eval: substitute},44 &FuncDef{Name: "REPLACE", Args: "text, position, length, new_text", Desc: "Replace characters at a position", Min: 4, Max: 4,45 eval: replaceAt},46 &FuncDef{Name: "FIND", Args: "search_for, text_to_search, [starting_at]", Desc: "Position of text, case-sensitive", Min: 2, Max: 3,47 eval: textFinder(false)},48 &FuncDef{Name: "SEARCH", Args: "search_for, text_to_search, [starting_at]", Desc: "Position of text, ignoring case, with * and ? wildcards", Min: 2, Max: 3,49 eval: textFinder(true)},50 &FuncDef{Name: "TEXT", Args: "number, format", Desc: `A number as text in a format, e.g. "$#,##0.00" or "yyyy-mm-dd"`, Min: 2, Max: 2,51 eval: textFormat},52 &FuncDef{Name: "VALUE", Args: "text", Desc: "Text as a number; dates and times too", Min: 1, Max: 1,53 eval: valueOf},54 &FuncDef{Name: "REPT", Args: "text, number_of_repetitions", Desc: "Text repeated a number of times", Min: 2, Max: 2,55 eval: rept},56 &FuncDef{Name: "EXACT", Args: "string1, string2", Desc: "TRUE if two texts are identical, case included", Min: 2, Max: 2,57 eval: exact},58 )59}6061// Evaluators for the table above, in its order.6263func concatenate(args []Node, get lookup) Value {64 parts, err := texts(args, get)65 if err != nil {66 return *err67 }68 return str(strings.Join(parts, ""))69}7071func concat(args []Node, get lookup) Value {72 a, err := textArg(args[0], get)73 if err != nil {74 return *err75 }76 b, err := textArg(args[1], get)77 if err != nil {78 return *err79 }80 return str(a + b)81}8283func textJoin(args []Node, get lookup) Value {84 delim, err := textArg(args[0], get)85 if err != nil {86 return *err87 }88 skip, err := boolArg(args, 1, true, get)89 if err != nil {90 return *err91 }92 var parts []string93 if skip || delim == "" {94 parts, err = texts(args[2:], get)95 } else {96 parts, err = textsWithBlanks(args[2:], get, maxText+2)97 }98 if err != nil {99 return *err100 }101 if skip {102 parts = slicesDeleteEmpty(parts)103 }104 return capText(strings.Join(parts, delim))105}106107func mid(args []Node, get lookup) Value {108 s, err := textArg(args[0], get)109 if err != nil {110 return *err111 }112 start, err := intArg(args, 1, 1, get)113 if err != nil {114 return *err115 }116 n, err := intArg(args, 2, 0, get)117 if err != nil {118 return *err119 }120 switch {121 case start < 1:122 return value.ErrNum // as Sheets; Excel says #VALUE!123 case n < 0:124 return value.ErrValue125 }126 r := []rune(s)127 if start > len(r) {128 return str("")129 }130 return str(string(r[start-1 : min(start-1+n, len(r))]))131}132133// upper is UPPER's full case mapping, which Sheets uses: "straße" is134// "STRASSE", where mapping each letter alone would keep the ß. A Caser135// keeps state, so each call makes its own, and ASCII needs none.136func upper(s string) string {137 for i := 0; i < len(s); i++ {138 if s[i] >= utf8.RuneSelf {139 return cases.Upper(language.Und).String(s)140 }141 }142 return strings.ToUpper(s)143}144145func trimSpaces(s string) Value {146 return str(strings.Join(strings.FieldsFunc(s, func(r rune) bool { return r == ' ' }), " "))147}148149func replaceAt(args []Node, get lookup) Value {150 s, err := textArg(args[0], get)151 if err != nil {152 return *err153 }154 pos, err := intArg(args, 1, 1, get)155 if err != nil {156 return *err157 }158 n, err := intArg(args, 2, 0, get)159 if err != nil {160 return *err161 }162 repl, err := textArg(args[3], get)163 if err != nil {164 return *err165 }166 if pos < 1 || n < 0 {167 return value.ErrValue168 }169 r := []rune(s)170 from := min(pos-1, len(r))171 to := min(from+n, len(r))172 return capText(string(r[:from]) + repl + string(r[to:]))173}174175func textFormat(args []Node, get lookup) Value {176 v := eval(args[0], get)177 if v.Kind == value.Error {178 return v179 }180 pat, err := textArg(args[1], get)181 if err != nil {182 return *err183 }184 if v.Kind == value.Bool {185 return str(v.String()) // TRUE stays TRUE whatever the format, as in Sheets186 }187 if v.Kind == value.Text {188 n, _, ok := value.ParseValue(v.Str)189 if !ok {190 return v191 }192 v = num(n)193 }194 return str(numfmt.Format(v.Num, pat))195}196197func valueOf(args []Node, get lookup) Value {198 v := eval(args[0], get)199 switch v.Kind {200 case value.Error, value.Number:201 return v202 case value.Empty:203 return num(0)204 case value.Bool:205 return value.ErrValue206 }207 if n, _, ok := value.ParseValue(strings.TrimSpace(v.Str)); ok {208 return num(n)209 }210 if strings.TrimSpace(v.Str) == "" {211 return num(0)212 }213 return value.ErrValue214}215216func rept(args []Node, get lookup) Value {217 s, err := textArg(args[0], get)218 if err != nil {219 return *err220 }221 n, err := intArg(args, 1, 0, get)222 if err != nil {223 return *err224 }225 if n < 0 || n*len(s) > maxText {226 return value.ErrValue227 }228 return str(strings.Repeat(s, n))229}230231func exact(args []Node, get lookup) Value {232 a, err := textArg(args[0], get)233 if err != nil {234 return *err235 }236 b, err := textArg(args[1], get)237 if err != nil {238 return *err239 }240 return boolean(a == b)241}242243func capText(s string) Value {244 if len(s) > maxText {245 return value.ErrValue246 }247 return str(s)248}249250func slicesDeleteEmpty(parts []string) []string {251 out := parts[:0]252 for _, p := range parts {253 if p != "" {254 out = append(out, p)255 }256 }257 return out258}259260func textFn(f func(string) Value) func([]Node, lookup) Value {261 return func(args []Node, get lookup) Value {262 s, err := textArg(args[0], get)263 if err != nil {264 return *err265 }266 return f(s)267 }268}269270// textSlice builds LEFT and RIGHT: n defaults to 1, negative is #VALUE!.271func textSlice(f func(r []rune, n int) string) func([]Node, lookup) Value {272 return func(args []Node, get lookup) Value {273 s, err := textArg(args[0], get)274 if err != nil {275 return *err276 }277 n, err := intArg(args, 1, 1, get)278 if err != nil {279 return *err280 }281 if n < 0 {282 return value.ErrValue283 }284 return str(f([]rune(s), n))285 }286}287288// proper capitalizes the first letter of every run of letters, as Sheets289// does: "it's" becomes "It'S".290func proper(s string) string {291 r := []rune(s)292 prevLetter := false293 for i, c := range r {294 if prevLetter {295 r[i] = unicode.ToLower(c)296 } else {297 r[i] = unicode.ToUpper(c)298 }299 prevLetter = unicode.IsLetter(c)300 }301 return string(r)302}303304func substitute(args []Node, get lookup) Value {305 s, err := textArg(args[0], get)306 if err != nil {307 return *err308 }309 old, err := textArg(args[1], get)310 if err != nil {311 return *err312 }313 repl, err := textArg(args[2], get)314 if err != nil {315 return *err316 }317 if len(args) < 4 {318 if old == "" {319 return str(s)320 }321 return capText(strings.ReplaceAll(s, old, repl))322 }323 nth, err := intArg(args, 3, 0, get)324 switch {325 case err != nil:326 return *err327 case nth < 1:328 return value.ErrValue329 case old == "":330 return str(s)331 }332 idx := 0333 for i := 1; ; i++ {334 j := strings.Index(s[idx:], old)335 if j < 0 {336 return str(s)337 }338 idx += j339 if i == nth {340 return capText(s[:idx] + repl + s[idx+len(old):])341 }342 idx += len(old)343 }344}345346// textFinder builds FIND (exact) and SEARCH (case-insensitive, wildcards).347// Positions count characters from 1; not found is #VALUE!.348func textFinder(search bool) func([]Node, lookup) Value {349 return func(args []Node, get lookup) Value {350 needle, err := textArg(args[0], get)351 if err != nil {352 return *err353 }354 hay, err := textArg(args[1], get)355 if err != nil {356 return *err357 }358 start, err := intArg(args, 2, 1, get)359 if err != nil {360 return *err361 }362 r := []rune(hay)363 if start < 1 || start > len(r)+1 {364 return value.ErrValue365 }366 if needle == "" {367 return num(float64(start))368 }369 for i := start - 1; i < len(r); i++ {370 rest := string(r[i:])371 switch {372 case !search && strings.HasPrefix(rest, needle):373 return num(float64(i + 1))374 case search && hasWildcards(needle) && prefixWild(needle, r[i:]):375 return num(float64(i + 1))376 case search && !hasWildcards(needle) && strings.HasPrefix(strings.ToLower(rest), strings.ToLower(needle)):377 return num(float64(i + 1))378 }379 }380 return value.ErrValue381 }382}383384// prefixWild reports whether some prefix of r matches the pattern.385func prefixWild(pattern string, r []rune) bool {386 return wildMatch(pattern+"*", string(r))387}