1package sheet23import (4 "errors"5 "fmt"6 "slices"7 "strings"89 "src.rest/cat/012/internal/formula"10 "src.rest/cat/012/internal/functions"11)1213// Data validation follows Sheets' Data > Data validation: a rule on14// ranges of the sheet says what their cells may hold (an item of a15// list, TRUE or FALSE for a checkbox, a number or date in bounds, text16// of a length, or whatever meets a formula) and whether an entry that17// doesn't is rejected or kept with a warning. Each cell has at most one18// rule: adding one over cells that had another takes them from it.1920// ValidKind is what a validation rule checks.21type ValidKind uint82223const (24 ValidList ValidKind = iota // one of Items, picked from a dropdown25 ValidRange // one of the values of Source, picked from a dropdown26 ValidCheckbox // TRUE or FALSE, drawn as a checkbox27 ValidNumber // a number meeting Op28 ValidDate // a date meeting Op; any date with RuleNone29 ValidLength // text whose length meets Op30 ValidFormula // anything for which the formula Args[0] is TRUE31 numValidKinds32)3334var validNames = [numValidKinds]string{"list", "range", "checkbox", "number", "date", "length", "formula"}35var validTitles = [numValidKinds]string{"Dropdown", "Dropdown (from a range)", "Checkbox", "Number",36 "Date", "Text length", "Custom formula is"}3738func (k ValidKind) String() string {39 if k >= numValidKinds {40 return ""41 }42 return validNames[k]43}4445// Title names the kind for people, e.g. "Dropdown (from a range)".46func (k ValidKind) Title() string {47 if k >= numValidKinds {48 return ""49 }50 return validTitles[k]51}5253// ParseValidKind is the inverse of ValidKind.String.54func ParseValidKind(s string) (ValidKind, bool) {55 i := slices.Index(validNames[:], s)56 return ValidKind(max(i, 0)), i >= 057}5859// ValidKinds lists the kinds in the order the rules editor offers them.60func ValidKinds() []ValidKind {61 out := make([]ValidKind, numValidKinds)62 for i := range out {63 out[i] = ValidKind(i)64 }65 return out66}6768// Compares reports whether the kind compares with Op and Args.69func (k ValidKind) Compares() bool { return k == ValidNumber || k == ValidDate || k == ValidLength }7071// Dropdown reports whether cells of the kind offer a list to pick from.72func (k ValidKind) Dropdown() bool { return k == ValidList || k == ValidRange }7374// Validation is a data validation rule.75type Validation struct {76 Ranges []Rect77 Kind ValidKind78 // Op compares numbers, dates or lengths with Args, as typed; RuleNone79 // with ValidDate accepts any date. With ValidFormula, Args[0] is the80 // formula, relative to the first range's top-left cell.81 Op RuleOp82 Args [2]string83 Items []string // ValidList's items84 // Source is ValidRange's range, e.g. "A2:A9" or "Lists!A1:A20".85 Source string86 // Reject refuses an invalid entry; otherwise it's kept and marked.87 Reject bool88 // Help replaces the rule's own help text, shown on the context line.89 Help string90 // Display is how a dropdown's cells show it (validstyle.go). A91 // checkbox's Items, when it has any, are its checked and unchecked92 // values.93 Display DropDisplay94}9596func (v Validation) clone() Validation {97 v.Ranges, v.Items = slices.Clone(v.Ranges), slices.Clone(v.Items)98 return v99}100101// Check reports why a rule can't be used, in words for the rules editor.102func (v Validation) Check() error {103 switch {104 case len(v.Ranges) == 0:105 return errors.New("Enter the range the rule applies to, e.g. B2:B100")106 case v.Kind >= numValidKinds:107 return errors.New("Choose the criteria")108 case v.Kind == ValidList && len(v.Items) == 0:109 return errors.New("Enter the items, separated by commas")110 case v.Kind == ValidCheckbox:111 return v.checkCheckbox()112 case v.Kind == ValidRange:113 if _, _, ok := v.SourceRange(); !ok {114 return errors.New("Enter the range the items are in, e.g. Lists!A1:A20")115 }116 case v.Kind == ValidFormula:117 return checkArg(RuleFormula, strings.TrimSpace(v.Args[0]))118 case v.Kind.Compares():119 return v.checkBounds()120 }121 return nil122}123124func (v Validation) checkBounds() error {125 if v.Op == RuleNone && v.Kind == ValidDate {126 return nil127 }128 if !slices.Contains(CompareOps(), v.Op) {129 return errors.New("Choose a comparison")130 }131 for i := range v.Op.Args() {132 arg := strings.TrimSpace(v.Args[i])133 if IsFormulaEntry(arg) {134 if err := checkArg(RuleFormula, arg); err != nil {135 return err136 }137 continue138 }139 if _, _, ok := ParseValue(arg); !ok {140 what := "a number"141 if v.Kind == ValidDate {142 what = "a date, e.g. 2026-09-30"143 }144 return errors.New("Enter " + what)145 }146 }147 return nil148}149150// SourceRange is ValidRange's source: its sheet as written ("" for the151// rule's own) and range.152func (v Validation) SourceRange() (string, Rect, bool) {153 sheet, rest := SplitSheet(strings.TrimPrefix(strings.TrimSpace(v.Source), "="))154 r, ok := ParseRange(strings.ReplaceAll(rest, "$", ""))155 return sheet, r, ok156}157158// Summary describes the rule in a few words, e.g. "Number between 1 and 10".159func (v Validation) Summary() string {160 switch {161 case v.Kind == ValidList:162 return "Dropdown: " + strings.Join(v.Items, ", ")163 case v.Kind == ValidRange:164 return "Dropdown from " + v.Source165 case v.Kind == ValidFormula:166 return "Custom formula " + v.Args[0]167 case v.Kind.Compares():168 return v.Kind.Title() + " " + compareText(v.Kind, v.Op, v.Args)169 case v.Kind == ValidCheckbox && len(v.Items) > 0:170 on, off, _ := v.CheckboxValues()171 return "Checkbox: " + on + " or " + cmpOr(off, "blank")172 }173 return v.Kind.Title()174}175176func cmpOr(s, alt string) string {177 if s == "" {178 return alt179 }180 return s181}182183// compareText is a comparison in words: "between 1 and 10", "after 1/1/2026".184func compareText(k ValidKind, op RuleOp, args [2]string) string {185 if k == ValidDate && op == RuleNone {186 return "is a valid date"187 }188 words := map[RuleOp]string{RuleBetween: "between", RuleNotBetween: "not between",189 RuleEqual: "equal to", RuleNotEqual: "not equal to", RuleGreater: "greater than",190 RuleGreaterEq: "greater than or equal to", RuleLess: "less than", RuleLessEq: "less than or equal to"}191 if k == ValidDate {192 words[RuleEqual], words[RuleNotEqual] = "on", "not on"193 words[RuleGreater], words[RuleGreaterEq] = "after", "on or after"194 words[RuleLess], words[RuleLessEq] = "before", "on or before"195 }196 w := words[op]197 if op.Args() == 2 {198 return w + " " + args[0] + " and " + args[1]199 }200 return w + " " + args[0]201}202203// HelpText is what the context line says about a cell under the rule:204// the rule's own help text, or Sheets' message for its criteria.205func (v Validation) HelpText() string {206 if h := strings.TrimSpace(v.Help); h != "" {207 return h208 }209 switch v.Kind {210 case ValidList:211 return "Input must be an item on the specified list"212 case ValidRange:213 return "Input must fall within specified range"214 case ValidCheckbox:215 on, off, _ := v.CheckboxValues()216 if off == "" {217 return "Input must be " + on + " or blank"218 }219 return "Input must be " + on + " or " + off220 case ValidNumber:221 return "Input must be a number " + compareText(v.Kind, v.Op, v.Args)222 case ValidDate:223 if v.Op == RuleNone {224 return "Input must be a valid date"225 }226 return "Input must be a date " + compareText(v.Kind, v.Op, v.Args)227 case ValidLength:228 return "Input must be text whose length is " + compareText(v.Kind, v.Op, v.Args)229 }230 return "Input must satisfy the formula " + v.Args[0]231}232233// Validations returns the sheet's validation rules.234func (s *Sheet) Validations() []Validation {235 out := make([]Validation, len(s.rules.validations))236 for i, v := range s.rules.validations {237 out[i] = v.clone()238 }239 return out240}241242// Validation returns the rule of the cell at a, if it has one.243func (s *Sheet) Validation(a Addr) (Validation, bool) {244 if i := s.validationIndex(a); i >= 0 {245 return s.rules.validations[i].clone(), true246 }247 return Validation{}, false248}249250func (s *Sheet) validationIndex(a Addr) int {251 for i, v := range s.rules.validations {252 if inRanges(v.Ranges, a) {253 return i254 }255 }256 return -1257}258259// AddValidation adds a rule, taking its cells from the rules they had,260// as one undo step.261func (s *Sheet) AddValidation(v Validation) error {262 return s.putValidation(-1, v, "add data validation "+rangesText(v.Ranges))263}264265// SetValidation replaces rule i, as one undo step.266func (s *Sheet) SetValidation(i int, v Validation) error {267 if i < 0 || i >= len(s.rules.validations) {268 return errors.New("That rule was removed")269 }270 return s.putValidation(i, v, "edit data validation "+rangesText(v.Ranges))271}272273// putValidation stores v in place of rule i (-1 adds it), taking its274// cells from the others.275func (s *Sheet) putValidation(i int, v Validation, label string) error {276 if err := v.Check(); err != nil {277 return err278 }279 v = v.clone()280 r := s.rules281 r.validations = nil282 for j, o := range s.rules.validations {283 if j == i {284 r.validations = append(r.validations, v)285 continue286 }287 for _, cut := range v.Ranges {288 o.Ranges = subtractAll(o.Ranges, cut)289 }290 if len(o.Ranges) > 0 {291 r.validations = append(r.validations, o)292 }293 }294 if i < 0 {295 r.validations = append(r.validations, v)296 }297 s.setRules(label, v.Ranges[0], r)298 return nil299}300301// DeleteValidation removes rule i.302func (s *Sheet) DeleteValidation(i int) {303 if i < 0 || i >= len(s.rules.validations) {304 return305 }306 v := s.rules.validations[i]307 r := s.rules308 r.validations = slices.Delete(slices.Clone(r.validations), i, i+1)309 s.setRules("remove data validation "+rangesText(v.Ranges), v.Ranges[0], r)310}311312// ClearValidations takes the cells of cr out of every rule, as Sheets'313// "Remove rule" does for a selection.314func (s *Sheet) ClearValidations(cr Rect) {315 r := s.rules316 r.validations = nil317 for _, v := range s.rules.validations {318 v.Ranges = subtractAll(v.Ranges, cr)319 if len(v.Ranges) > 0 {320 r.validations = append(r.validations, v)321 }322 }323 s.setRules("remove data validation from "+cr.String(), cr, r)324}325326// LoadValidations adds rules as a loader does: without recording undo.327// Rules that don't check are left out and counted.328func (s *Sheet) LoadValidations(vs []Validation) (skipped int) {329 for _, v := range vs {330 if v.Check() != nil {331 skipped++332 continue333 }334 s.rules.validations = append(s.rules.validations, v.clone())335 }336 s.looks.reset()337 return skipped338}339340// InvalidEntry is an entry a cell's validation doesn't accept.341type InvalidEntry struct {342 Addr Addr343 Help string // what the rule wants, e.g. "Input must be a number between 1 and 10"344 Reject bool // the rule refuses it; otherwise it may be kept, marked invalid345}346347func (e *InvalidEntry) Error() string {348 return fmt.Sprintf("Invalid entry in %s: %s", e.Addr, e.Help)349}350351// CheckEntry reports whether the cell at a would accept input: nil when352// it has no rule or input meets it (blanks always do), or an353// *InvalidEntry. A formula is checked by the value it computes.354func (s *Sheet) CheckEntry(a Addr, input string) *InvalidEntry {355 i := s.validationIndex(a)356 if i < 0 || input == "" {357 return nil358 }359 v := s.rules.validations[i]360 val, f := s.entryValue(a, input)361 if f.IsZero() {362 f = s.DisplayFormat(a)363 }364 if v.Kind == ValidFormula {365 // The formula reads the entry in its own cell.366 s.looks.fresh(s)367 rd := s.wb.lookupOn(s, func(t *Sheet, b Addr) Value {368 if t == s && b == a {369 return val370 }371 return t.Value(b)372 })373 n, err := Parse(strings.TrimSpace(v.Args[0]))374 if err == nil {375 from := anchor(v.Ranges)376 n, _ = formula.Rewrite(n, formula.Shift(a.Col-from.Col, a.Row-from.Row))377 if truthy(functions.EvalAt(n, rd.lib, a)) {378 return nil379 }380 }381 } else if s.looks.check(s, i, a, val, f) {382 return nil383 }384 return &InvalidEntry{Addr: a, Help: v.HelpText(), Reject: v.Reject}385}386387// entryValue is what input would compute to at a, and the format it388// implies, without storing it.389func (s *Sheet) entryValue(a Addr, input string) (Value, Format) {390 n, f, err := classify(input)391 switch {392 case err != nil:393 return ErrValue, f394 case n == nil:395 return Value{Kind: Text, Str: strings.TrimPrefix(input, "'")}, f396 }397 return s.looks.eval(s, n, a), f398}399400// DropdownItems lists what a dropdown cell offers: the rule's items, or401// the distinct values shown in its range, in order, blanks left out.402func (s *Sheet) DropdownItems(a Addr) []string {403 v, ok := s.Validation(a)404 switch {405 case !ok:406 return nil407 case v.Kind == ValidList:408 return v.Items409 case v.Kind == ValidRange:410 return s.sourceItems(v)411 }412 return nil413}414415// maxDropdown caps the items a dropdown from a range lists.416const maxDropdown = 1000417418// sourceItems reads the distinct values shown in a rule's source range.419func (s *Sheet) sourceItems(v Validation) []string {420 name, r, ok := v.SourceRange()421 t := s422 if name != "" {423 t = s.wb.resolve(s, name)424 }425 if !ok || t == nil {426 return nil427 }428 seen := map[string]bool{}429 var out []string430 for a := range t.cells.keysIn(r) {431 text := strings.TrimSpace(t.ShownText(a))432 key := strings.ToLower(text)433 if text == "" || seen[key] {434 continue435 }436 seen[key] = true437 out = append(out, text)438 if len(out) == maxDropdown {439 break440 }441 }442 return out443}