1package functions23import (4 "math"5 "slices"67 "src.rest/cat/012/internal/formula"8 "src.rest/cat/012/internal/numfmt"9 "src.rest/cat/012/internal/value"10)1112// FuncDef describes a spreadsheet function. The table drives parsing13// (arity checks), evaluation, autocomplete and help.14type FuncDef struct {15 Name string16 Args string // signature shown to users, e.g. "value1, [value2, ...]"17 Desc string18 Min int19 Max int // -1 for variadic20 // step > 0 means arguments after Min come in groups of step, like21 // SUMIFS' (range, criterion) pairs.22 step int23 // Volatile functions (TODAY, NOW, RAND) change without their inputs24 // changing, so every recalculation recomputes them.25 Volatile bool26 eval func(args []Node, get lookup) Value27 // format infers the result's display format for Automatic cells; nil28 // means none.29 format func(args []Node, infer func(Node) Format) Format30 // remote builds the question a JEV function asks (functions_jev.go).31 remote func(args []Node, get lookup) (RemoteCall, error)32 // arrays is how the function meets arrays (array.go).33 arrays arrayUse34 // binds is how its arguments bind names, for LET and LAMBDA.35 binds formula.Binding36}3738// arrayUse is how a function meets arrays.39type arrayUse uint84041const (42 // liftScalar takes and returns one value; in an array context it is43 // applied to each entry of the arrays it's given (lift.go).44 liftScalar arrayUse = iota45 // liftPass is liftScalar, but what it returns may be an array one of46 // its arguments computed (IF, IFERROR, CHOOSE, INDEX).47 liftPass48 // takesArrays reads arrays itself and may return one (FILTER, SORT,49 // ARRAYFORMULA, LET): it is never mapped over arrays.50 takesArrays51)5253func (f *FuncDef) call(args []Node, get lookup) Value { return f.eval(args, get) }5455// Signature is how the parser checks calls to f.56func (f *FuncDef) Signature() formula.Signature {57 return formula.Signature{Name: f.Name, Args: f.Args, Min: f.Min, Max: f.Max, Step: f.step, Binds: f.binds}58}5960// Remote reports whether f asks a remote question (JEV functions).61func (f *FuncDef) Remote() bool { return f.remote != nil }6263// Question is the remote question a call of f asks with its current64// arguments, or an error when they don't make one.65func (f *FuncDef) Question(args []Node, get *Reader) (RemoteCall, error) {66 return f.remote(args, get)67}6869// Of is the function a parsed call calls: always one of the table's, as70// long as the parser found functions through LookupFunc.71func Of(c formula.Call) *FuncDef { return c.Fn.(*FuncDef) }7273func funcOf(c formula.Call) *FuncDef { return Of(c) }7475var funcs = map[string]*FuncDef{}7677// aliases maps alternative names (including 1-2-3's) to canonical ones.78var aliases = map[string]string{"AVG": "AVERAGE"}7980func define(defs ...*FuncDef) {81 for _, d := range defs {82 funcs[d.Name] = d83 }84}8586// LookupFunc finds a function by name or alias, case-insensitively87// (callers pass upper case).88func LookupFunc(name string) (*FuncDef, bool) {89 if canon, ok := aliases[name]; ok {90 name = canon91 }92 f, ok := funcs[name]93 return f, ok94}9596// Funcs returns every function, sorted by name.97func Funcs() []*FuncDef {98 out := make([]*FuncDef, 0, len(funcs))99 for _, f := range funcs {100 out = append(out, f)101 }102 slices.SortFunc(out, func(a, b *FuncDef) int {103 if a.Name < b.Name {104 return -1105 }106 return 1107 })108 return out109}110111func init() {112 define(113 &FuncDef{Name: "SUM", Args: "value1, [value2, ...]", Desc: "Sum of numbers", Min: 1, Max: -1,114 eval: aggregate(func(s Agg) Value { return num(s.sum) }), format: inherit},115 &FuncDef{Name: "AVERAGE", Args: "value1, [value2, ...]", Desc: "Average of numbers, ignoring text", Min: 1, Max: -1,116 eval: aggregate(func(s Agg) Value {117 if s.nums == 0 {118 return value.ErrDiv0119 }120 return num(s.sum / float64(s.nums))121 }), format: inherit},122 &FuncDef{Name: "COUNT", Args: "value1, [value2, ...]", Desc: "Count of numeric values, skipping errors", Min: 1, Max: -1,123 eval: counter(func(s Agg) Value { return num(float64(s.nums)) })},124 &FuncDef{Name: "COUNTA", Args: "value1, [value2, ...]", Desc: "Count of non-empty values, errors included", Min: 1, Max: -1,125 eval: counter(func(s Agg) Value { return num(float64(s.count + s.errs)) })},126 &FuncDef{Name: "MIN", Args: "value1, [value2, ...]", Desc: "Smallest number", Min: 1, Max: -1,127 eval: aggregate(func(s Agg) Value {128 if s.nums == 0 {129 return num(0)130 }131 return num(s.min)132 }), format: inherit},133 &FuncDef{Name: "MAX", Args: "value1, [value2, ...]", Desc: "Largest number", Min: 1, Max: -1,134 eval: aggregate(func(s Agg) Value {135 if s.nums == 0 {136 return num(0)137 }138 return num(s.max)139 }), format: inherit},140 &FuncDef{Name: "ABS", Args: "value", Desc: "Absolute value", Min: 1, Max: 1, eval: math1(math.Abs), format: inherit},141 &FuncDef{Name: "INT", Args: "value", Desc: "Round down to the nearest integer", Min: 1, Max: 1, eval: math1(math.Floor), format: inherit},142 &FuncDef{Name: "SQRT", Args: "value", Desc: "Square root", Min: 1, Max: 1, eval: math1(math.Sqrt)},143 &FuncDef{Name: "ROUND", Args: "value, [places]", Desc: "Round to a number of decimal places, halves away from zero, on the digits a cell shows: ROUND(1.5, 14) is 1.5", Min: 1, Max: 2,144 eval: rounder(numfmt.HalfUp), format: inheritFrom(0)},145 &FuncDef{Name: "MOD", Args: "dividend, divisor", Desc: "Remainder, with the sign of the divisor: MOD(0.3, 0.1) is 0.1 as in Excel, where Sheets gives -5.55E-17", Min: 2, Max: 2,146 eval: numeric(func(x []float64) Value {147 if x[1] == 0 {148 return value.ErrDiv0149 }150 return num(x[0] - x[1]*math.Floor(x[0]/x[1]))151 })},152 &FuncDef{Name: "PI", Desc: "The number pi", Max: 0, eval: constant(num(math.Pi))},153 &FuncDef{Name: "TRUE", Desc: "The logical value TRUE", Max: 0, eval: constant(boolean(true))},154 &FuncDef{Name: "FALSE", Desc: "The logical value FALSE", Max: 0, eval: constant(boolean(false))},155 &FuncDef{Name: "NA", Desc: "The #N/A error", Max: 0, eval: constant(value.ErrNA)},156 &FuncDef{Name: "IF", Args: "condition, value_if_true, [value_if_false]", Desc: "Choose a value by a condition", Min: 2, Max: 3, arrays: liftPass,157 eval: func(args []Node, get lookup) Value {158 t, err := truth(eval1(args[0], get))159 if err != nil {160 return *err161 }162 if t {163 return eval(args[1], get)164 }165 if len(args) < 3 {166 return boolean(false)167 }168 return eval(args[2], get)169 }, format: func(args []Node, infer func(Node) Format) Format { return inherit(args[1:], infer) }},170 &FuncDef{Name: "IFERROR", Args: "value, [value_if_error]", Desc: "A fallback when a value is an error; blank without one", Min: 1, Max: 2, arrays: liftPass,171 eval: func(args []Node, get lookup) Value {172 v := eval(args[0], get)173 if v.Kind != value.Error {174 return v175 }176 if len(args) < 2 {177 return Value{} // blank, as in Sheets178 }179 return eval(args[1], get)180 }},181 &FuncDef{Name: "AND", Args: "logical1, [logical2, ...]", Desc: "TRUE if all arguments are true", Min: 1, Max: -1,182 eval: logical(func(t, n int) bool { return t == n })},183 &FuncDef{Name: "OR", Args: "logical1, [logical2, ...]", Desc: "TRUE if any argument is true", Min: 1, Max: -1,184 eval: logical(func(t, _ int) bool { return t > 0 })},185 &FuncDef{Name: "NOT", Args: "logical", Desc: "The opposite of a logical value", Min: 1, Max: 1,186 eval: func(args []Node, get lookup) Value {187 t, err := truth(eval(args[0], get))188 if err != nil {189 return *err190 }191 return boolean(!t)192 }},193 )194}195196// Agg is the running aggregate of SUM-like functions (SUM, AVERAGE,197// COUNT, COUNTA, MIN, MAX, PRODUCT): what they read of a range, added198// in order. The engine keeps one per range shared by a recalculation199// (Book.RangeAgg).200type Agg struct {201 sum float64202 prod float64203 count int // non-empty values other than errors204 nums int205 min, max float64206 // counting is set for COUNT and COUNTA, which count errors (errs)207 // where the others stop at the first.208 counting bool209 errs int210}211212// each calls fn for every value in args: every cell of a range that213// holds something (blank cells, which every caller skips, aren't214// visited), or the value of any other argument. direct is false for cells215// of a range or a reference: as in Sheets, SUM(A1) ignores text in A1 but216// SUM("a") is #VALUE!.217func each(args []Node, get lookup, fn func(v Value, direct bool) *Value) *Value {218 for _, arg := range args {219 if e := eachOf(arg, get, fn); e != nil {220 return e221 }222 }223 return nil224}225226func eachOf(arg Node, get lookup, fn func(v Value, direct bool) *Value) *Value {227 switch arg := get.refOf(arg).(type) {228 case formula.Ref:229 return fn(get.cell(arg.Sheet, arg.Addr), false)230 case formula.Empty:231 return nil232 case formula.Range:233 var e *Value234 get.cells(arg.Sheet, arg.Rect, func(_ Addr, v Value) bool {235 e = fn(v, false)236 return e == nil237 })238 return e239 }240 v := wholeArg(arg, get)241 if a := get.arrayOf(v); a != nil {242 return eachEntry(a, fn)243 }244 if v.Kind == value.Array {245 v = value.ErrValue // a LAMBDA246 }247 return fn(v, true)248}249250// eachEntry calls fn with the entries of an array, as a range's cells:251// those it stores, then the rest, all its fill, unless that is blank.252func eachEntry(a *Array, fn func(v Value, direct bool) *Value) *Value {253 for _, v := range a.V {254 if v.Kind == value.Empty {255 continue256 }257 if e := fn(v, false); e != nil {258 return e259 }260 }261 if a.Fill.Kind == value.Empty {262 return nil263 }264 for range a.size() - len(a.V) {265 if e := fn(a.Fill, false); e != nil {266 return e267 }268 }269 return nil270}271272// Add counts v into the aggregate, with SUM's rules: blanks are skipped,273// errors returned (counted, when counting), and text counts only when274// given directly.275func (s *Agg) Add(v Value, direct bool) *Value {276 switch v.Kind {277 case value.Error:278 if s.counting {279 s.errs++280 return nil281 }282 return errOf(v)283 case value.Empty:284 return nil285 }286 s.count++287 if v.Kind != value.Number && !direct {288 return nil289 }290 f, err := toNum(v)291 if err != nil {292 return err293 }294 s.nums++295 s.sum += f296 s.prod *= f297 s.min, s.max = math.Min(s.min, f), math.Max(s.max, f)298 return nil299}300301// NewAgg is the aggregate of nothing.302func NewAgg() Agg { return Agg{prod: 1, min: math.Inf(1), max: math.Inf(-1)} }303304// aggregate builds SUM-like functions with Sheets semantics: in ranges only305// numbers count and text is ignored; direct arguments are coerced, so306// SUM("a") is #VALUE!. A range read first is served from the running307// aggregates shared by the recalculation (Book.RangeAgg; the engine's308// rangememo.go), so a thousand SUM(A:A) or a column of running totals309// read each cell once; other ranges the engine adds up itself310// (Book.Fold), as it reads them.311func aggregate(done func(Agg) Value) func([]Node, lookup) Value {312 return aggregateFrom(NewAgg(), done)313}314315// counter builds COUNT and COUNTA, which read past errors, as Sheets316// does: COUNT skips them and COUNTA counts them. Their ranges are read317// directly, as the shared aggregates stop at an error.318func counter(done func(Agg) Value) func([]Node, lookup) Value {319 s := NewAgg()320 s.counting = true321 return aggregateFrom(s, done)322}323324func aggregateFrom(start Agg, done func(Agg) Value) func([]Node, lookup) Value {325 return func(args []Node, get lookup) Value {326 s := start327 for _, arg := range args {328 if rn, ok := get.refOf(arg).(formula.Range); ok {329 var e *Value330 if s, e = rangeAgg(rn, s, get); e != nil {331 return *e332 }333 continue334 }335 if e := eachOf(arg, get, s.Add); e != nil {336 return *e337 }338 }339 return done(s)340 }341}342343// rangeAgg adds the range rn to s: from the running aggregates when s is344// empty and the range is shared, and otherwise as the engine reads it.345func rangeAgg(rn formula.Range, s Agg, get lookup) (Agg, *Value) {346 if s.count == 0 && !s.counting {347 if a, e, ok := get.book.RangeAgg(rn.Sheet, rn.Rect); ok {348 return a, e349 }350 }351 return get.book.Fold(rn.Sheet, rn.Rect, s)352}353354func logical(test func(trues, n int) bool) func([]Node, lookup) Value {355 return func(args []Node, get lookup) Value {356 trues, n := 0, 0357 e := each(args, get, func(v Value, direct bool) *Value {358 switch {359 case v.Kind == value.Error:360 return errOf(v)361 case v.Kind == value.Empty, v.Kind == value.Text && !direct:362 return nil363 }364 t, err := truth(v)365 if err != nil {366 return err367 }368 n++369 if t {370 trues++371 }372 return nil373 })374 switch {375 case e != nil:376 return *e377 case n == 0:378 return value.ErrValue379 }380 return boolean(test(trues, n))381 }382}383384// numeric evaluates every argument as a number, propagating errors.385func numeric(f func([]float64) Value) func([]Node, lookup) Value {386 return func(args []Node, get lookup) Value {387 xs := make([]float64, len(args))388 for i, a := range args {389 v := eval(a, get)390 if v.Kind == value.Error {391 return v392 }393 x, err := toNum(v)394 if err != nil {395 return *err396 }397 xs[i] = x398 }399 return f(xs)400 }401}402403func math1(f func(float64) float64) func([]Node, lookup) Value {404 return numeric(func(x []float64) Value { return num(f(x[0])) })405}406407func constant(v Value) func([]Node, lookup) Value {408 return func([]Node, lookup) Value { return v }409}410411// rounder builds ROUND, ROUNDUP, ROUNDDOWN and TRUNC: round on the 15 digits a412// spreadsheet shows, so ROUNDUP(2.3, 1) stays 2.3.413func rounder(mode numfmt.Rounding) func([]Node, lookup) Value {414 return func(args []Node, get lookup) Value {415 x, err := numArg(args[0], get)416 if err != nil {417 return *err418 }419 places, err := intArg(args, 1, 0, get)420 if err != nil {421 return *err422 }423 return num(numfmt.Round(x, clampInt(places, -308, 308), mode))424 }425}