1package functions23import (4 "cmp"5 "strings"6 "unicode/utf8"78 "src.rest/cat/012/internal/formula"9 "src.rest/cat/012/internal/value"10)1112// Argument helpers shared by the function library. Errors come back as a13// *Value to return as is, so functions propagate the first error they14// meet, as Sheets does.1516func numArg(n Node, get lookup) (float64, *Value) {17 v := eval1(n, get)18 if v.Kind == value.Error {19 return 0, errOf(v)20 }21 return toNum(v)22}2324// given reports whether argument i was passed and not left empty.25func given(args []Node, i int) bool {26 if i >= len(args) {27 return false28 }29 _, empty := args[i].(formula.Empty)30 return !empty31}3233// optNum is argument i as a number, or def when it wasn't given.34func optNum(args []Node, i int, def float64, get lookup) (float64, *Value) {35 if !given(args, i) {36 return def, nil37 }38 return numArg(args[i], get)39}4041// intArg is a number argument truncated toward zero, as Sheets does for42// counts and positions.43func intArg(args []Node, i int, def float64, get lookup) (int, *Value) {44 f, err := optNum(args, i, def, get)45 if err != nil {46 return 0, err47 }48 if f > 1e9 || f < -1e9 {49 return 0, &value.ErrNum50 }51 return int(f), nil52}5354func textArg(n Node, get lookup) (string, *Value) {55 v := eval1(n, get)56 if v.Kind == value.Error {57 return "", errOf(v)58 }59 return text(v), nil60}6162func boolArg(args []Node, i int, def bool, get lookup) (bool, *Value) {63 if !given(args, i) {64 return def, nil65 }66 f, err := numArg(args[i], get)67 return f != 0, err68}6970// matrix is a rectangular block of values: a range, or a single value71// treated as a 1x1 block. Only its top-left dataRows x dataCols may hold72// data: every cell past them is blank, so functions that walk a range73// (SUMIF(A:A, ...), MATCH, COUNTBLANK) visit what the sheet holds rather74// than the million rows of a whole column, while ROWS, INDEX and the75// positions they return keep the range's full size.76type matrix struct {77 rows, cols int78 cell func(r, c int) Value79 origin Addr // top-left cell when the block is a reference80 sheet string // the reference's sheet, as written81 ref bool8283 dataRows, dataCols int84 blank Value // every cell outside the data: blank, or #REF! for a missing sheet85}8687func (m matrix) at(i int) Value { return m.cell(i/m.cols, i%m.cols) }8889func (m matrix) size() int { return m.rows * m.cols }9091// vector reports whether m is a single row or column.92func (m matrix) vector() bool { return m.rows == 1 || m.cols == 1 }9394// dataLen is how many of a vector's leading entries may hold data.95func (m matrix) dataLen() int {96 if m.cols == 1 {97 return m.dataRows98 }99 return m.dataCols100}101102func matrixArg(n Node, get lookup) matrix {103 switch n := get.refOf(n).(type) {104 case formula.Range:105 return rectMatrix(n.Sheet, n.Rect, get)106 case formula.Ref:107 return rectMatrix(n.Sheet, Rect{From: n.Addr, To: n.Addr}, get)108 }109 v := wholeArg(n, get)110 if a := get.arrayOf(v); a != nil {111 return arrayMatrix(a)112 }113 if v.Kind == value.Array {114 v = value.ErrValue // a LAMBDA115 }116 return matrix{rows: 1, cols: 1, cell: func(int, int) Value { return v }, dataRows: 1, dataCols: 1, blank: v}117}118119// arrayMatrix is an array as a block of values.120func arrayMatrix(a *Array) matrix {121 return matrix{rows: a.Rows, cols: a.Cols, cell: a.At, dataRows: a.DRows, dataCols: a.DCols, blank: a.Fill}122}123124// wholeArg evaluates an argument that takes ranges or arrays, in an125// array context: the whole of an argument standing in for one being126// mapped over (liftArg), not its entry.127func wholeArg(n Node, get lookup) Value {128 if a, ok := n.(liftArg); ok {129 return a.value(get)130 }131 return evalArr(n, get)132}133134func rectMatrix(sheet string, r Rect, get lookup) matrix {135 m := matrix{136 rows: r.To.Row - r.From.Row + 1, cols: r.To.Col - r.From.Col + 1,137 origin: r.From, sheet: sheet, ref: true,138 }139 switch b, any, exists := get.book.Bounds(sheet, r); {140 case !exists:141 m.blank = value.ErrRef142 case get.dense:143 m.dataRows, m.dataCols = m.rows, m.cols144 case any:145 m.dataRows, m.dataCols = b.To.Row-r.From.Row+1, b.To.Col-r.From.Col+1146 }147 rows, cols, blank := m.dataRows, m.dataCols, m.blank148 m.cell = func(row, col int) Value {149 if row >= rows || col >= cols {150 return blank151 }152 return get.cell(sheet, Addr{Col: r.From.Col + col, Row: r.From.Row + row})153 }154 return m155}156157// part is the top-left rows x cols of a block that isn't a range.158func (m matrix) part(rows, cols int) matrix {159 m.rows, m.cols = rows, cols160 m.dataRows, m.dataCols = min(m.dataRows, rows), min(m.dataCols, cols)161 return m162}163164// resized returns a block of rows x cols from the same top-left cell, as165// SUMIF does with a sum range of a different size.166func (m matrix) resized(rows, cols int, get lookup) matrix {167 if !m.ref {168 return m169 }170 to := Addr{Col: m.origin.Col + cols - 1, Row: m.origin.Row + rows - 1}171 return rectMatrix(m.sheet, Rect{From: m.origin, To: to}, get)172}173174// nums collects the numbers in args with aggregate semantics: in ranges175// only numbers count; direct arguments are coerced.176func nums(args []Node, get lookup) ([]float64, *Value) {177 var out []float64178 e := each(args, get, func(v Value, direct bool) *Value {179 switch {180 case v.Kind == value.Error:181 return errOf(v)182 case v.Kind == value.Empty, v.Kind != value.Number && !direct:183 return nil184 }185 f, err := toNum(v)186 if err != nil {187 return err188 }189 out = append(out, f)190 return nil191 })192 return out, e193}194195// texts flattens args to strings for CONCATENATE and TEXTJOIN: the cells196// of ranges that hold something, and any other argument, blank or not.197func texts(args []Node, get lookup) ([]string, *Value) {198 var out []string199 e := each(args, get, func(v Value, _ bool) *Value {200 if v.Kind == value.Error {201 return errOf(v)202 }203 out = append(out, text(v))204 return nil205 })206 return out, e207}208209// textsWithBlanks is texts with every blank cell of a range as "", as210// TEXTJOIN keeping empty entries needs, but at most limit of them in all:211// past that many the joined text is too long anyway.212func textsWithBlanks(args []Node, get lookup, limit int) ([]string, *Value) {213 var out []string214 blanks := func(n int) {215 for ; n > 0 && len(out) < limit; n-- {216 out = append(out, "")217 }218 }219 for _, arg := range args {220 rn, ok := get.refOf(arg).(formula.Range)221 if !ok {222 if e := eachOf(arg, get, func(v Value, _ bool) *Value {223 if v.Kind == value.Error {224 return errOf(v)225 }226 out = append(out, text(v))227 return nil228 }); e != nil {229 return nil, e230 }231 continue232 }233 r, last := rn.Rect, -1234 width := r.To.Col - r.From.Col + 1235 var e *Value236 get.cells(rn.Sheet, r, func(a Addr, v Value) bool {237 if v.Kind == value.Error {238 e = errOf(v)239 return false240 }241 p := (a.Row-r.From.Row)*width + a.Col - r.From.Col242 blanks(p - last - 1)243 out, last = append(out, text(v)), p244 return true245 })246 if e != nil {247 return nil, e248 }249 blanks(width*(r.To.Row-r.From.Row+1) - last - 1)250 }251 return out, nil252}253254func runeLen(s string) int { return utf8.RuneCountInString(s) }255256// wildMatch matches s against a Sheets wildcard pattern, case-insensitively:257// * is any run, ? any one character, ~ escapes the next.258func wildMatch(pattern, s string) bool {259 p := []rune(strings.ToLower(pattern))260 t := []rune(strings.ToLower(s))261 // Classic backtracking over the last *.262 pi, ti, star, mark := 0, 0, -1, 0263 for ti < len(t) {264 switch {265 case pi < len(p) && p[pi] == '~' && pi+1 < len(p) && p[pi+1] == t[ti]:266 pi += 2267 ti++268 case pi < len(p) && p[pi] != '~' && (p[pi] == '?' || p[pi] == t[ti]) && p[pi] != '*':269 pi++270 ti++271 case pi < len(p) && p[pi] == '*':272 star, mark = pi, ti273 pi++274 case star >= 0:275 pi = star + 1276 mark++277 ti = mark278 default:279 return false280 }281 }282 for pi < len(p) && p[pi] == '*' {283 pi++284 }285 return pi == len(p)286}287288func hasWildcards(s string) bool { return strings.ContainsAny(s, "*?~") }289290// criterion is a condition of SUMIF, COUNTIFS and friends: 5, ">5",291// "<>done", "a*", "" (blank) or TRUE.292type criterion struct {293 op string // =, <>, <, >, <=, >=294 kind value.Kind // Number, Text, Bool or Empty (blank)295 num float64296 text string297}298299func newCriterion(v Value) criterion {300 c := criterion{op: "=", kind: v.Kind, num: v.Num}301 if v.Kind == value.Text {302 s := v.Str303 for _, op := range []string{"<=", ">=", "<>", "<", ">", "="} {304 if rest, ok := strings.CutPrefix(s, op); ok {305 c.op, s = op, rest306 break307 }308 }309 c.text = s310 switch n, _, isNum := value.ParseValue(s); {311 case s == "":312 c.kind = value.Empty313 case isNum:314 c.kind, c.num = value.Number, n315 case strings.EqualFold(s, "TRUE") || strings.EqualFold(s, "FALSE"):316 c.kind, c.num = value.Bool, 0317 if strings.EqualFold(s, "TRUE") {318 c.num = 1319 }320 }321 }322 return c323}324325// test reports whether a cell value satisfies the criterion.326func (c criterion) test(v Value) bool {327 blank := v.Kind == value.Empty || v.Kind == value.Text && v.Str == ""328 if c.kind == value.Empty {329 switch c.op {330 case "=":331 return blank332 case "<>":333 return !blank334 }335 return false336 }337 if c.kind == value.Text {338 if v.Kind != value.Text {339 return c.op == "<>"340 }341 switch c.op {342 case "=":343 return wildMatch(c.text, v.Str)344 case "<>":345 return !wildMatch(c.text, v.Str)346 }347 return cmpResult(c.op, strings.Compare(strings.ToLower(v.Str), strings.ToLower(c.text)))348 }349 // Numbers and booleans only match their own kind, except that a350 // number equals text that reads as it, as in Sheets: 3 matches "3".351 if v.Kind != c.kind {352 if v.Kind == value.Text && c.kind == value.Number && (c.op == "=" || c.op == "<>") {353 if n, ok := value.ParseNumber(v.Str); ok {354 return cmpResult(c.op, numCmp(n, c.num))355 }356 }357 return c.op == "<>"358 }359 return cmpResult(c.op, numCmp(v.Num, c.num))360}361362// numCmp orders two numbers as the comparison operators do, equal when363// they agree to the 15 digits a cell shows.364func numCmp(a, b float64) int {365 if value.SameShown(a, b) {366 return 0367 }368 return cmp.Compare(a, b)369}370371func cmpResult(op string, d int) bool {372 switch op {373 case "=":374 return d == 0375 case "<>":376 return d != 0377 case "<":378 return d < 0379 case ">":380 return d > 0381 case "<=":382 return d <= 0383 }384 return d >= 0385}386387// criteriaArgs reads the (range, criterion) pairs starting at args[i].388// All ranges must be the same size.389func criteriaArgs(args []Node, i int, get lookup) ([]matrix, []criterion, *Value) {390 var ms []matrix391 var cs []criterion392 for ; i+1 < len(args); i += 2 {393 m := matrixArg(args[i], get)394 cv := eval(args[i+1], get)395 if cv.Kind == value.Error {396 return nil, nil, &cv397 }398 if len(ms) > 0 && (m.rows != ms[0].rows || m.cols != ms[0].cols) {399 return nil, nil, &value.ErrValue400 }401 ms, cs = append(ms, m), append(cs, newCriterion(cv))402 }403 return ms, cs, nil404}