1package sheet23import (4 "math"5 "slices"6 "strings"7 "unicode/utf8"89 "src.rest/cat/012/internal/formula"10 "src.rest/cat/012/internal/functions"11 "src.rest/cat/012/internal/locale"12)1314// Look is how a sheet's rules draw a cell: a conditional format's style15// or place on a color scale, and what its validation rule shows (a16// checkbox, a dropdown's marker, an invalid entry's mark).17type Look struct {18 // Styled is set when a single-color rule applies, with its Style.19 Styled bool20 Style RuleStyle21 // Scaled is set when a color scale colors the cell, Pos of the way22 // (0 to 1) from From to To.23 Scaled bool24 From, To Color25 Pos float6426 // Bar is set when a data bar draws across the cell, BarLen of the27 // way (0 to 1), in BarColor.28 Bar bool29 BarColor Color30 BarLen float6431 // Icon, when set, is the icon an icon set draws at the cell's left,32 // in IconColor.33 Icon string34 IconColor Color35 // ValueHidden is set when a data bar or icon shows without the36 // cell's value.37 ValueHidden bool3839 Checkbox bool // a checkbox, checked when Checked40 Checked bool41 Dropdown bool // offers a list to pick from42 Display DropDisplay // how it shows that it does43 Invalid bool // holds what its validation rule doesn't accept44}4546// Looks are worked out per cell as the screen asks for them, and kept47// until the next recalculation or change of rules: work follows what's48// drawn, and a color scale reads its range's values once per49// recalculation, not per frame. The cache is dropped whole when it50// grows past maxLooks, as scrolling through a long range would make it.51type looksCache struct {52 wb *Workbook53 gen uint64 // the workbook's recalculation when filled54 valid bool55 cells map[Addr]Look56 tests map[int]*ruleTest // compiled conditional formats by index57 scales map[int]*scaleStats // color scales' points by index58 bars map[int]*barStats // data bars' and icon sets' points by index59 ranks map[int]*rankStats // what ranking rules compare with, by index60 lists map[int]map[string]bool // dropdown sources' values by validation index61 rd *reader62}6364const maxLooks = 1 << 166566func (c *looksCache) reset() { c.valid = false }6768// fresh empties the cache if it was filled before the last69// recalculation or rule change.70func (c *looksCache) fresh(s *Sheet) {71 if c.valid && c.wb == s.wb && c.gen == s.wb.gen {72 return73 }74 *c = looksCache{wb: s.wb, gen: s.wb.gen, valid: true, cells: map[Addr]Look{}, tests: map[int]*ruleTest{},75 scales: map[int]*scaleStats{}, lists: map[int]map[string]bool{}, bars: map[int]*barStats{}, ranks: map[int]*rankStats{}}76}7778// HasRules reports whether the sheet has conditional formats or data79// validation, so drawing can skip asking for looks when it has none.80func (s *Sheet) HasRules() bool { return !s.rules.empty() }8182// Look returns how the sheet's rules draw the cell at a.83func (s *Sheet) Look(a Addr) Look {84 if s.rules.empty() {85 return Look{}86 }87 c := &s.looks88 c.fresh(s)89 if l, ok := c.cells[a]; ok {90 return l91 }92 l := c.look(s, a)93 if len(c.cells) >= maxLooks {94 clear(c.cells)95 }96 c.cells[a] = l97 return l98}99100func (c *looksCache) look(s *Sheet, a Addr) Look {101 var l Look102 v := s.Value(a)103 for i, f := range s.rules.formats {104 if inRanges(f.Ranges, a) && c.apply(s, i, f, a, v, &l) {105 break106 }107 }108 if i := s.validationIndex(a); i >= 0 {109 r := s.rules.validations[i]110 l.Checkbox, l.Dropdown, l.Display = r.Kind == ValidCheckbox, r.Kind.Dropdown(), r.Display111 l.Checked = l.Checkbox && r.isChecked(v, s.ShownText(a))112 l.Invalid = s.cells.filledAt(a) && !c.check(s, i, a, v, s.DisplayFormat(a))113 }114 return l115}116117// apply sets what rule i (f) draws on the cell at a, of value v, on l,118// reporting whether it applies there.119func (c *looksCache) apply(s *Sheet, i int, f CondFormat, a Addr, v Value, l *Look) bool {120 switch {121 case f.IsBar():122 n, ok := c.bar(s, i).barLook(v)123 if ok {124 l.Bar, l.BarLen, l.BarColor, l.ValueHidden = true, n, f.Bar, f.BarOnly125 }126 return ok127 case f.IsIcons():128 k, ok := c.bar(s, i).icon(v, f.Reverse)129 if ok {130 n := len(f.Scale) + 1131 l.Icon, l.IconColor, l.ValueHidden = f.Icons.Glyphs(n)[k], f.Icons.IconColor(k, n), f.BarOnly132 }133 return ok134 case f.IsScale():135 pos, from, to, ok := c.scale(s, i).place(v)136 if ok {137 l.Scaled, l.Pos, l.From, l.To = true, pos, from, to138 }139 return ok140 case f.Op.Ranks():141 if !c.rank(s, i).match(f.Op, v) {142 return false143 }144 case !c.test(s, i).match(c, s, a, v):145 return false146 }147 l.Styled, l.Style = true, f.Style148 return true149}150151// reader reads current values for rules' formulas.152func (c *looksCache) reader(s *Sheet) *reader {153 if c.rd == nil {154 c.rd = s.wb.values(s)155 }156 return c.rd157}158159// eval evaluates n as if in the cell at a.160func (c *looksCache) eval(s *Sheet, n Node, a Addr) Value {161 return functions.EvalAt(n, c.reader(s).lib, a)162}163164// evalFrom evaluates a rule's formula n, written for the cell from, in165// the cell at a: its relative references move as a copy's would.166func (c *looksCache) evalFrom(s *Sheet, n Node, from, a Addr) Value {167 if a != from {168 n, _ = formula.Rewrite(n, formula.Shift(a.Col-from.Col, a.Row-from.Row))169 }170 return c.eval(s, n, a)171}172173func truthy(v Value) bool { return (v.Kind == Bool || v.Kind == Number) && v.Num != 0 }174175// ruleTest is a single-color rule ready to run: its values parsed, or176// kept as formulas to evaluate in each cell.177type ruleTest struct {178 op RuleOp179 from Addr180 args [2]Node // formulas among the values; nil for constants181 nums [2]float64182 isNum [2]bool183 shared func(Value, string) bool // the filter's test, for the ops filters share with constant values184}185186// filterOps maps the tests conditional formats share with filters.187var filterOps = map[RuleOp]CondOp{RuleEmpty: CondEmpty, RuleNotEmpty: CondNotEmpty,188 RuleContains: CondContains, RuleNotContains: CondNotContains, RuleStartsWith: CondStartsWith,189 RuleEndsWith: CondEndsWith, RuleExactly: CondExactly, RuleGreater: CondGreater,190 RuleGreaterEq: CondGreaterEq, RuleLess: CondLess, RuleLessEq: CondLessEq, RuleEqual: CondEqual,191 RuleNotEqual: CondNotEqual}192193func (c *looksCache) test(s *Sheet, i int) *ruleTest {194 if t, ok := c.tests[i]; ok {195 return t196 }197 f := s.rules.formats[i]198 t := &ruleTest{op: f.Op, from: anchor(f.Ranges)}199 t.parse(f.Op, f.Args)200 if cop, ok := filterOps[f.Op]; ok && t.args[0] == nil {201 t.shared = Condition{Op: cop, Arg: f.Args[0]}.test()202 }203 c.tests[i] = t204 return t205}206207// parse reads the values of op: formulas are parsed, dates and numbers208// read.209func (t *ruleTest) parse(op RuleOp, args [2]string) {210 for k := range op.Args() {211 arg := strings.TrimSpace(args[k])212 switch {213 case IsFormulaEntry(arg):214 t.args[k], _ = Parse(arg)215 case op == RuleDateIs || op == RuleDateBefore || op == RuleDateAfter:216 t.nums[0], t.nums[1], t.isNum[0] = datePeriod(arg)217 t.isNum[1] = t.isNum[0]218 default:219 n, _, ok := ParseValue(arg)220 t.nums[k], t.isNum[k] = n, ok221 }222 }223}224225// arg is value k of the test in the cell at a.226func (t *ruleTest) arg(c *looksCache, s *Sheet, k int, a Addr) Value {227 if t.args[k] != nil {228 return c.evalFrom(s, t.args[k], t.from, a)229 }230 if t.isNum[k] {231 return Value{Kind: Number, Num: t.nums[k]}232 }233 return Value{}234}235236func (t *ruleTest) match(c *looksCache, s *Sheet, a Addr, v Value) bool {237 switch {238 case t.shared != nil:239 return t.shared(v, s.LocalText(a))240 case t.op == RuleFormula:241 return t.args[0] != nil && truthy(c.evalFrom(s, t.args[0], t.from, a))242 }243 if cop, ok := filterOps[t.op]; ok { // a shared test with a formula value244 arg, loc := t.arg(c, s, 0, a), locale.Canonical245 if cop.OnText() {246 loc = s.Locale()247 }248 return Condition{Op: cop, Arg: FormatTextIn(arg, Format{}, loc)}.test()(v, s.LocalText(a))249 }250 if v.Kind != Number {251 return false252 }253 x, lo := v.Num, t.arg(c, s, 0, a)254 if lo.Kind != Number {255 return false256 }257 switch t.op {258 case RuleDateIs, RuleDateBefore, RuleDateAfter:259 first, last, day := float64(int64(lo.Num)), float64(int64(lo.Num)), float64(int64(x))260 if t.args[0] == nil && t.isNum[1] {261 last = t.nums[1] // a period's last day262 }263 switch t.op {264 case RuleDateIs:265 return day >= first && day <= last266 case RuleDateBefore:267 return day < first268 }269 return day > last270 }271 hi := t.arg(c, s, 1, a)272 return hi.Kind == Number && compareNum(t.op, x, lo.Num, hi.Num)273}274275// compareNum compares x with one bound, or two for between.276func compareNum(op RuleOp, x, lo, hi float64) bool {277 switch op {278 case RuleBetween:279 return x >= min(lo, hi) && x <= max(lo, hi)280 case RuleNotBetween:281 return x < min(lo, hi) || x > max(lo, hi)282 case RuleEqual:283 return x == lo284 case RuleNotEqual:285 return x != lo286 case RuleGreater:287 return x > lo288 case RuleGreaterEq:289 return x >= lo290 case RuleLess:291 return x < lo292 case RuleLessEq:293 return x <= lo294 }295 return true296}297298// scaleStats is a color scale's points among the values of its ranges.299type scaleStats struct {300 ok bool301 at []float64302 colors []Color303}304305func (c *looksCache) scale(s *Sheet, i int) *scaleStats {306 if st, ok := c.scales[i]; ok {307 return st308 }309 f := s.rules.formats[i]310 // Only a percentile needs the values themselves; the rest need the311 // lowest and highest.312 keep := slices.ContainsFunc(f.Scale, func(p ScalePoint) bool { return p.Kind == PointPercentile })313 lo, hi, n := math.Inf(1), math.Inf(-1), 0314 var vals []float64315 for _, r := range f.Ranges {316 for _, v := range s.cells.valuesIn(r) {317 if v.Kind != Number {318 continue319 }320 v := v.Num321 lo, hi, n = min(lo, v), max(hi, v), n+1322 if keep {323 vals = append(vals, v)324 }325 }326 }327 st := &scaleStats{}328 if n > 0 {329 st.ok = true330 for _, p := range f.Scale {331 st.at = append(st.at, p.at(vals, lo, hi))332 st.colors = append(st.colors, p.Color)333 }334 }335 c.scales[i] = st336 return st337}338339// place is where v falls on the scale: the fraction of the way between340// the colors of the points around it.341func (st *scaleStats) place(v Value) (pos float64, from, to Color, ok bool) {342 if !st.ok || v.Kind != Number {343 return 0, 0, 0, false344 }345 k := 0346 if len(st.at) == 3 && v.Num > st.at[1] {347 k = 1348 }349 lo, hi := st.at[k], st.at[k+1]350 switch {351 case hi <= lo:352 pos = 0353 if v.Num >= hi {354 pos = 1355 }356 default:357 pos = min(max((v.Num-lo)/(hi-lo), 0), 1)358 }359 return pos, st.colors[k], st.colors[k+1], true360}361362// check reports whether value v, shown in format f, meets validation363// rule i in the cell at a. Blanks always do.364func (c *looksCache) check(s *Sheet, i int, a Addr, v Value, f Format) bool {365 c.fresh(s)366 r := s.rules.validations[i]367 switch r.Kind {368 case ValidList:369 shown := strings.TrimSpace(FormatText(v, f))370 return v.Kind == Empty || slices.ContainsFunc(r.Items, func(it string) bool { return strings.EqualFold(strings.TrimSpace(it), shown) })371 case ValidRange:372 return v.Kind == Empty || c.list(s, i)[strings.ToLower(strings.TrimSpace(FormatText(v, f)))]373 case ValidCheckbox:374 return r.checkboxValid(v, FormatText(v, f))375 case ValidFormula:376 n, err := Parse(strings.TrimSpace(r.Args[0]))377 return err == nil && truthy(c.evalFrom(s, n, anchor(r.Ranges), a))378 case ValidDate:379 if v.Kind != Number || f.Kind < FmtDate || f.Kind > FmtDateTime || f.Kind == FmtTime {380 return false381 }382 if r.Op == RuleNone {383 return true384 }385 case ValidNumber:386 if v.Kind != Number {387 return false388 }389 case ValidLength:390 v = Value{Kind: Number, Num: float64(utf8.RuneCountInString(FormatText(v, f)))}391 }392 return c.bounds(s, r, a, v.Num)393}394395// bounds compares x with a rule's values, evaluated in the cell at a.396func (c *looksCache) bounds(s *Sheet, r Validation, a Addr, x float64) bool {397 t := ruleTest{op: r.Op, from: anchor(r.Ranges)}398 t.parse(r.Op, r.Args)399 lo, hi := t.arg(c, s, 0, a), t.arg(c, s, 1, a)400 if lo.Kind != Number || r.Op.Args() == 2 && hi.Kind != Number {401 return false402 }403 return compareNum(r.Op, x, lo.Num, hi.Num)404}405406// list is the values of a dropdown's source, by lower case.407func (c *looksCache) list(s *Sheet, i int) map[string]bool {408 if l, ok := c.lists[i]; ok {409 return l410 }411 l := map[string]bool{}412 for _, it := range s.sourceItems(s.rules.validations[i]) {413 l[strings.ToLower(it)] = true414 }415 c.lists[i] = l416 return l417}