1package sheet23import (4 "reflect"5 "slices"6 "strings"78 "src.rest/cat/012/internal/formula"9)1011// Rules are what a sheet says about ranges of its cells beyond their own12// formatting: conditional formats (condfmt.go), which change how cells13// look by their values, and data validation (validation.go), which says14// what may be entered. Both belong to the sheet, as in Sheets: they're15// saved with it, follow inserted and deleted rows and columns, and every16// change to them is an undo step. They never write cells.1718// Color is one of the named colors rules draw with. Each is an ANSI19// color slot, so the terminal's palette or the color scheme applies:20// the UI maps them to theme roles, never to fixed RGB.21type Color uint82223const (24 ColorNone Color = iota25 ColorRed26 ColorYellow27 ColorGreen28 ColorCyan29 ColorBlue30 ColorMagenta31 numColors32)3334var colorNames = [numColors]string{"", "red", "yellow", "green", "cyan", "blue", "magenta"}3536// NumColors is how many colors there are, ColorNone included, for37// tables indexed by Color.38const NumColors = int(numColors)3940// Colors lists the named colors, ColorNone first, in the order the rules41// editor offers them.42func Colors() []Color {43 out := make([]Color, numColors)44 for i := range out {45 out[i] = Color(i)46 }47 return out48}4950// String names the color as files store it; "" for none.51func (c Color) String() string {52 if c >= numColors {53 return ""54 }55 return colorNames[c]56}5758// Title names the color for people, e.g. "Green", or "None".59func (c Color) Title() string {60 if s := c.String(); s != "" {61 return strings.ToUpper(s[:1]) + s[1:]62 }63 return "None"64}6566// ParseColor is the inverse of Color.String, ignoring case.67func ParseColor(s string) (Color, bool) {68 i := slices.Index(colorNames[:], strings.ToLower(strings.TrimSpace(s)))69 return Color(max(i, 0)), i >= 070}7172// RuleStyle is what a conditional format rule does to the cells it73// matches: a text color, a fill, and text styles added to the cell's own.74type RuleStyle struct {75 Text, Fill Color76 Bold, Italic, Underline, Strikethrough bool77}7879// IsZero reports whether the style changes nothing.80func (s RuleStyle) IsZero() bool { return s == RuleStyle{} }8182// RuleOp is a test on a cell's value, as Sheets' conditional formatting83// and data validation offer them. Conditional formats use them all;84// validation uses the comparisons (and RuleNone for "any date").85type RuleOp uint88687const (88 RuleNone RuleOp = iota89 RuleEmpty90 RuleNotEmpty91 RuleContains92 RuleNotContains93 RuleStartsWith94 RuleEndsWith95 RuleExactly96 RuleDateIs97 RuleDateBefore98 RuleDateAfter99 RuleGreater100 RuleGreaterEq101 RuleLess102 RuleLessEq103 RuleEqual104 RuleNotEqual105 RuleBetween106 RuleNotBetween107 RuleFormula108 // The tests of a cell against the others in the rule's ranges, as109 // Excel's top 10, above average and duplicate values rules (see110 // condrank.go).111 RuleTop112 RuleBottom113 RuleTopPercent114 RuleBottomPercent115 RuleAboveAverage116 RuleBelowAverage117 RuleDuplicate118 RuleUnique119 numRuleOps120)121122var ruleOpNames = [numRuleOps]string{"", "empty", "not_empty", "contains", "not_contains",123 "starts_with", "ends_with", "exactly", "date_is", "date_before", "date_after",124 "gt", "ge", "lt", "le", "eq", "ne", "between", "not_between", "formula",125 "top", "bottom", "top_percent", "bottom_percent", "above_average", "below_average", "duplicate", "unique"}126127var ruleOpTitles = [numRuleOps]string{"None", "Is empty", "Is not empty", "Text contains",128 "Text does not contain", "Text starts with", "Text ends with", "Text is exactly",129 "Date is", "Date is before", "Date is after",130 "Greater than", "Greater than or equal to", "Less than", "Less than or equal to",131 "Is equal to", "Is not equal to", "Is between", "Is not between", "Custom formula is",132 "Top values", "Bottom values", "Top percent", "Bottom percent", "Above average", "Below average",133 "Duplicate values", "Unique values"}134135// String names the test as files store it.136func (op RuleOp) String() string {137 if op >= numRuleOps {138 return ""139 }140 return ruleOpNames[op]141}142143// Title names the test for people, e.g. "Greater than".144func (op RuleOp) Title() string {145 if op >= numRuleOps {146 return ""147 }148 return ruleOpTitles[op]149}150151// Args is how many values the test compares with: 0, 1, or 2 for152// between.153func (op RuleOp) Args() int {154 switch op {155 case RuleNone, RuleEmpty, RuleNotEmpty, RuleAboveAverage, RuleBelowAverage, RuleDuplicate, RuleUnique:156 return 0157 case RuleBetween, RuleNotBetween:158 return 2159 }160 return 1161}162163// OnText reports whether the test is on the text a cell shows, in the164// locale's rendering, so its value is kept as typed (see CondOp.OnText).165func (op RuleOp) OnText() bool { return op >= RuleContains && op <= RuleExactly }166167// ParseRuleOp is the inverse of RuleOp.String.168func ParseRuleOp(s string) (RuleOp, bool) {169 i := slices.Index(ruleOpNames[:], s)170 return RuleOp(max(i, 0)), i >= 0171}172173// CondFormatOps lists the tests a single-color conditional format may174// use, in the order Sheets' menu shows them.175func CondFormatOps() []RuleOp {176 return []RuleOp{RuleEmpty, RuleNotEmpty, RuleContains, RuleNotContains, RuleStartsWith,177 RuleEndsWith, RuleExactly, RuleDateIs, RuleDateBefore, RuleDateAfter, RuleGreater,178 RuleGreaterEq, RuleLess, RuleLessEq, RuleEqual, RuleNotEqual, RuleBetween,179 RuleNotBetween, RuleTop, RuleBottom, RuleTopPercent, RuleBottomPercent, RuleAboveAverage,180 RuleBelowAverage, RuleDuplicate, RuleUnique, RuleFormula}181}182183// Ranks reports whether the test compares a cell with the rest of the184// rule's cells (top values, above average, duplicates) rather than with185// values of its own.186func (op RuleOp) Ranks() bool { return op >= RuleTop && op < numRuleOps }187188// CompareOps are the comparisons data validation offers for numbers,189// dates and text lengths.190func CompareOps() []RuleOp {191 return []RuleOp{RuleBetween, RuleNotBetween, RuleEqual, RuleNotEqual, RuleGreater,192 RuleGreaterEq, RuleLess, RuleLessEq}193}194195// rulesState is a sheet's rules. The slices are never changed in place:196// a change replaces them, so undo steps can keep them as they were.197type rulesState struct {198 formats []CondFormat199 validations []Validation200}201202func (r rulesState) equal(o rulesState) bool {203 return reflect.DeepEqual(r.formats, o.formats) && reflect.DeepEqual(r.validations, o.validations)204}205206func (r rulesState) empty() bool { return len(r.formats) == 0 && len(r.validations) == 0 }207208// recordRules saves the sheet's rules before their first change in the209// open step, and drops what was worked out from them.210func (s *Sheet) recordRules() {211 s.looks.reset()212 st := s.wb.hist.open213 if st == nil {214 return215 }216 if _, seen := st.rules[s]; !seen {217 st.rules[s] = s.rules218 }219}220221// setRules replaces the sheet's rules as one undo step.222func (s *Sheet) setRules(label string, focus Rect, r rulesState) {223 if r.equal(s.rules) {224 return225 }226 s.change(label, focus, func() {227 s.recordRules()228 s.rules = r229 })230}231232// rangesText writes ranges as Sheets lists them, "A1:A9,C1:C9".233func rangesText(rs []Rect) string {234 parts := make([]string, len(rs))235 for i, r := range rs {236 parts[i] = r.String()237 }238 return strings.Join(parts, ",")239}240241// ParseRanges reads ranges written as "A1:A9,C1:C9" (spaces or commas242// between), as the rules editor takes them.243func ParseRanges(s string) ([]Rect, bool) {244 fields := strings.FieldsFunc(s, func(r rune) bool { return r == ',' || r == ' ' })245 if len(fields) == 0 {246 return nil, false247 }248 out := make([]Rect, 0, len(fields))249 for _, f := range fields {250 r, ok := ParseRange(strings.ReplaceAll(f, "$", ""))251 if !ok {252 return nil, false253 }254 out = append(out, r)255 }256 return out, true257}258259// RangesText writes ranges as ParseRanges reads them.260func RangesText(rs []Rect) string { return rangesText(rs) }261262// inRanges reports whether a is in one of rs.263func inRanges(rs []Rect, a Addr) bool {264 for _, r := range rs {265 if r.Contains(a) {266 return true267 }268 }269 return false270}271272// anchor is the top-left cell of the first range, which relative273// references in a rule's formula are written for.274func anchor(rs []Rect) Addr {275 if len(rs) == 0 {276 return Addr{}277 }278 return rs[0].From279}280281// shiftRanges moves ranges with inserted or deleted lines, as a formula's282// range would move, dropping those deleted entirely.283func shiftRanges(rs []Rect, rng func(Rect) (Rect, bool)) []Rect {284 var out []Rect285 for _, r := range rs {286 if to, ok := rng(r); ok {287 out = append(out, to)288 }289 }290 return out291}292293// shiftFormula rewrites a rule's formula (with its "=") for moved cells,294// or returns it unchanged when it doesn't parse.295func shiftFormula(src string, rw formula.Rewriter) string {296 if src == "" {297 return src298 }299 n, err := Parse(src)300 if err != nil {301 return src302 }303 if out, changed := formula.Rewrite(n, rw); changed {304 return formula.Text(out)305 }306 return src307}308309// shiftRules keeps every sheet's rules in step with rows or columns310// inserted or deleted on s: the ranges of s's rules move, and formulas311// and list sources follow the cells they read, on s or on s by name.312func (s *Sheet) shiftRules(rows bool, sp formula.Span) {313 cell, rng := formula.AxisMaps(rows, sp)314 for _, t := range s.wb.sheets {315 if t.rules.empty() {316 continue317 }318 on := func(name string) bool {319 if name == "" {320 return t == s321 }322 return formula.SheetKey(name) == formula.SheetKey(s.name)323 }324 rw := formula.Relocate(on, cell, rng)325 next := t.rules.rewritten(func(rs []Rect) []Rect {326 if t != s {327 return rs328 }329 return shiftRanges(rs, rng)330 }, rw)331 if !next.equal(t.rules) {332 t.recordRules()333 t.rules = next334 }335 }336}337338// renameRules rewrites the formulas and list sources of every rule that339// name the sheet with key old, to name.340func (w *Workbook) renameRules(old, name string) {341 rename := func(sheet string) string {342 if sheet != "" && formula.SheetKey(sheet) == old {343 return name344 }345 return sheet346 }347 rw := formula.Rewriter{348 Ref: func(n formula.Ref) Node { n.Sheet = rename(n.Sheet); return n },349 Range: func(n formula.Range) Node { n.Sheet = rename(n.Sheet); return n },350 }351 for _, t := range w.sheets {352 if t.rules.empty() {353 continue354 }355 next := t.rules.rewritten(func(rs []Rect) []Rect { return rs }, rw)356 if !next.equal(t.rules) {357 t.recordRules()358 t.rules = next359 }360 }361}362363// rewritten is the rules with their ranges mapped by ranges and their364// formulas and sources rewritten by rw; rules left with no range go.365func (r rulesState) rewritten(ranges func([]Rect) []Rect, rw formula.Rewriter) rulesState {366 var out rulesState367 for _, f := range r.formats {368 f.Ranges = ranges(f.Ranges)369 if len(f.Ranges) == 0 {370 continue371 }372 if f.Op == RuleFormula {373 f.Args[0] = shiftFormula(f.Args[0], rw)374 }375 out.formats = append(out.formats, f)376 }377 for _, v := range r.validations {378 v.Ranges = ranges(v.Ranges)379 if len(v.Ranges) == 0 {380 continue381 }382 switch v.Kind {383 case ValidFormula:384 v.Args[0] = shiftFormula(v.Args[0], rw)385 case ValidRange:386 v.Source = strings.TrimPrefix(shiftFormula("="+v.Source, rw), "=")387 }388 out.validations = append(out.validations, v)389 }390 return out391}