1package sheet23import (4 "errors"5 "fmt"6 "slices"7 "strings"8)910// Conditional formats follow Sheets' Format > Conditional formatting: a11// rule applies to ranges of the sheet and either formats the cells that12// meet a test in a single color (a text color, a fill, text styles) or13// colors every number along a color scale from its lowest to its14// highest. Rules are tried in order and the first that applies to a cell15// formats it; see looks.go for how they're evaluated.1617// CondFormat is a conditional format rule.18type CondFormat struct {19 Ranges []Rect20 // Op is the test of a single-color rule and Args its values, as21 // typed: "100", "=B2" (a formula, relative to the first range's22 // top-left cell as a copied formula would be), a date, or for23 // RuleFormula the formula, e.g. "=$C2>100".24 Op RuleOp25 Args [2]string26 Style RuleStyle27 // Scale, when set, makes the rule a color scale of 2 or 3 points,28 // lowest first, and Op, Args and Style mean nothing. A data bar's29 // and an icon set's points are kept here too (condbars.go).30 Scale []ScalePoint31 // Bar, when set, makes the rule a data bar in that color, from its32 // first point to its second.33 Bar Color34 // Icons, when set, makes the rule an icon set, one icon more than35 // its points, which are the thresholds of the icons after the first.36 Icons IconSet37 // Reverse gives the icons the other way round, the lowest icon to38 // the highest values.39 Reverse bool40 // BarOnly shows a data bar or icon without the cell's value.41 BarOnly bool42}4344// ScalePoint is a point of a color scale: where it is among the values,45// and its color.46type ScalePoint struct {47 Kind PointKind48 Value string // the number, percent or percentile; unused by min and max49 Color Color50}5152// PointKind is how a color scale point is placed.53type PointKind uint85455const (56 PointMin PointKind = iota // the lowest value57 PointMax // the highest value58 PointNumber // a number59 PointPercent // a percentage of the way from lowest to highest60 PointPercentile // a percentile of the values61 numPointKinds62)6364var pointNames = [numPointKinds]string{"min", "max", "num", "percent", "percentile"}65var pointTitles = [numPointKinds]string{"Min value", "Max value", "Number", "Percent", "Percentile"}6667func (k PointKind) String() string {68 if k >= numPointKinds {69 return ""70 }71 return pointNames[k]72}7374// Title names the kind for people, e.g. "Percentile".75func (k PointKind) Title() string {76 if k >= numPointKinds {77 return ""78 }79 return pointTitles[k]80}8182// TakesValue reports whether the point needs a value.83func (k PointKind) TakesValue() bool { return k >= PointNumber }8485// ParsePointKind is the inverse of PointKind.String.86func ParsePointKind(s string) (PointKind, bool) {87 i := slices.Index(pointNames[:], s)88 return PointKind(max(i, 0)), i >= 089}9091// PointKinds lists the kinds in the order the rules editor offers them.92func PointKinds() []PointKind {93 return []PointKind{PointMin, PointMax, PointNumber, PointPercent, PointPercentile}94}9596// IsScale reports whether the rule is a color scale.97func (f CondFormat) IsScale() bool { return len(f.Scale) > 0 && !f.IsBar() && !f.IsIcons() }9899// Summary describes the rule in a few words, e.g. "Greater than 100".100func (f CondFormat) Summary() string {101 switch {102 case f.IsBar():103 return "Data bar"104 case f.IsIcons():105 return "Icon set: " + strings.ToLower(f.Icons.Title())106 case f.IsScale():107 return "Color scale"108 case f.Op.Ranks():109 return rankSummary(f.Op, f.Args[0])110 }111 switch f.Op.Args() {112 case 1:113 return f.Op.Title() + " " + f.Args[0]114 case 2:115 return f.Op.Title() + " " + f.Args[0] + " and " + f.Args[1]116 }117 return f.Op.Title()118}119120// clone copies the rule's slices, so a stored rule is never shared.121func (f CondFormat) clone() CondFormat {122 f.Ranges, f.Scale = slices.Clone(f.Ranges), slices.Clone(f.Scale)123 return f124}125126// Check reports why a rule can't be used, in words for the rules editor.127func (f CondFormat) Check() error {128 if len(f.Ranges) == 0 {129 return errors.New("Enter the range the rule applies to, e.g. A2:A100")130 }131 switch {132 case f.IsBar():133 return checkBar(f)134 case f.IsIcons():135 return checkIcons(f)136 case f.IsScale():137 return checkScale(f.Scale)138 }139 if !slices.Contains(CondFormatOps(), f.Op) {140 return errors.New("Choose a condition")141 }142 for i := range f.Op.Args() {143 if err := checkArg(f.Op, strings.TrimSpace(f.Args[i])); err != nil {144 return err145 }146 }147 if f.Style.IsZero() {148 return errors.New("Choose a text color, a fill or a text style")149 }150 return nil151}152153// checkArg checks one value of a test.154func checkArg(op RuleOp, arg string) error {155 switch {156 case arg == "":157 return errors.New("Enter a value")158 case op == RuleFormula && !IsFormulaEntry(arg):159 return errors.New("A custom formula starts with =, e.g. =$C2>100")160 case IsFormulaEntry(arg):161 if _, err := Parse(arg); err != nil {162 return fmt.Errorf("Formula: %w", err)163 }164 case op == RuleDateIs || op == RuleDateBefore || op == RuleDateAfter:165 if _, _, ok := datePeriod(arg); !ok {166 return errors.New("Enter a date, e.g. 2026-09-30, or a period: today, past week, this month, last year ...")167 }168 case op.Ranks():169 return checkRank(op, arg)170 case op == RuleBetween || op == RuleNotBetween:171 if _, _, ok := ParseValue(arg); !ok {172 return errors.New("Enter a number")173 }174 }175 return nil176}177178// checkScale checks a color scale's points: 2 or 3, each with a color179// and a value where it takes one.180func checkScale(ps []ScalePoint) error {181 if len(ps) < 2 || len(ps) > 3 {182 return errors.New("A color scale has a minimum, a maximum and at most one midpoint")183 }184 for _, p := range ps {185 if p.Kind >= numPointKinds {186 return errors.New("Unknown kind of point")187 }188 if p.Color == ColorNone {189 return errors.New("Choose a color for every point")190 }191 if !p.Kind.TakesValue() {192 continue193 }194 n, _, ok := ParseValue(strings.TrimSpace(p.Value))195 switch {196 case !ok:197 return errors.New("Enter a number for the " + strings.ToLower(p.Kind.Title()))198 case p.Kind != PointNumber && (n < 0 || n > 100):199 return errors.New(p.Kind.Title() + " goes from 0 to 100")200 }201 }202 return nil203}204205// CondFormats returns the sheet's conditional format rules, in the order206// they're tried.207func (s *Sheet) CondFormats() []CondFormat {208 out := make([]CondFormat, len(s.rules.formats))209 for i, f := range s.rules.formats {210 out[i] = f.clone()211 }212 return out213}214215// AddCondFormat adds a rule after the others, as one undo step.216func (s *Sheet) AddCondFormat(f CondFormat) error {217 if err := f.Check(); err != nil {218 return err219 }220 r := s.rules221 r.formats = append(slices.Clip(r.formats), f.clone())222 s.setRules("add conditional format "+rangesText(f.Ranges), f.Ranges[0], r)223 return nil224}225226// SetCondFormat replaces rule i, as one undo step.227func (s *Sheet) SetCondFormat(i int, f CondFormat) error {228 if i < 0 || i >= len(s.rules.formats) {229 return errors.New("That rule was removed")230 }231 if err := f.Check(); err != nil {232 return err233 }234 r := s.rules235 r.formats = slices.Clone(r.formats)236 r.formats[i] = f.clone()237 s.setRules("edit conditional format "+rangesText(f.Ranges), f.Ranges[0], r)238 return nil239}240241// DeleteCondFormat removes rule i.242func (s *Sheet) DeleteCondFormat(i int) {243 if i < 0 || i >= len(s.rules.formats) {244 return245 }246 r := s.rules247 r.formats = slices.Delete(slices.Clone(r.formats), i, i+1)248 f := s.rules.formats[i]249 s.setRules("remove conditional format "+rangesText(f.Ranges), f.Ranges[0], r)250}251252// MoveCondFormat moves rule i to position to, changing which rule wins253// where several apply.254func (s *Sheet) MoveCondFormat(i, to int) {255 n := len(s.rules.formats)256 if i < 0 || i >= n || to < 0 || to >= n || i == to {257 return258 }259 r := s.rules260 r.formats = slices.Clone(r.formats)261 f := r.formats[i]262 r.formats = slices.Insert(slices.Delete(r.formats, i, i+1), to, f)263 s.setRules("reorder conditional formats", f.Ranges[0], r)264}265266// ClearCondFormats takes the cells of cr out of every rule, removing267// rules left with no cells, as Sheets' "Clear formatting rules" for a268// selection.269func (s *Sheet) ClearCondFormats(cr Rect) {270 r := s.rules271 r.formats = nil272 for _, f := range s.rules.formats {273 f.Ranges = subtractAll(f.Ranges, cr)274 if len(f.Ranges) > 0 {275 r.formats = append(r.formats, f)276 }277 }278 s.setRules("clear conditional formats from "+cr.String(), cr, r)279}280281// LoadCondFormats adds rules as a loader does: without recording undo.282// Rules that don't check are left out and counted.283func (s *Sheet) LoadCondFormats(fs []CondFormat) (skipped int) {284 for _, f := range fs {285 if f.Check() != nil {286 skipped++287 continue288 }289 s.rules.formats = append(s.rules.formats, f.clone())290 }291 s.looks.reset()292 return skipped293}294295// subtractAll removes cut from every range of rs.296func subtractAll(rs []Rect, cut Rect) []Rect {297 var out []Rect298 for _, r := range rs {299 out = append(out, subtract(r, cut)...)300 }301 return out302}303304// subtract is r without the cells of cut: r itself, nothing, or up to305// four ranges around cut.306func subtract(r, cut Rect) []Rect {307 lo := Addr{Col: max(r.From.Col, cut.From.Col), Row: max(r.From.Row, cut.From.Row)}308 hi := Addr{Col: min(r.To.Col, cut.To.Col), Row: min(r.To.Row, cut.To.Row)}309 if lo.Col > hi.Col || lo.Row > hi.Row {310 return []Rect{r}311 }312 var out []Rect313 if r.From.Row < lo.Row { // above314 out = append(out, Rect{From: r.From, To: Addr{Col: r.To.Col, Row: lo.Row - 1}})315 }316 if hi.Row < r.To.Row { // below317 out = append(out, Rect{From: Addr{Col: r.From.Col, Row: hi.Row + 1}, To: r.To})318 }319 if r.From.Col < lo.Col { // left, beside cut320 out = append(out, Rect{From: Addr{Col: r.From.Col, Row: lo.Row}, To: Addr{Col: lo.Col - 1, Row: hi.Row}})321 }322 if hi.Col < r.To.Col { // right, beside cut323 out = append(out, Rect{From: Addr{Col: hi.Col + 1, Row: lo.Row}, To: Addr{Col: r.To.Col, Row: hi.Row}})324 }325 return out326}327328// at is where a point sits among the values of a scale's ranges, lo329// the lowest and hi the highest; vals, in any order and reordered here,330// are needed only for a percentile.331func (p ScalePoint) at(vals []float64, lo, hi float64) float64 {332 n, _, _ := ParseValue(strings.TrimSpace(p.Value))333 switch p.Kind {334 case PointMin:335 return lo336 case PointMax:337 return hi338 case PointPercent:339 return lo + (hi-lo)*n/100340 case PointPercentile:341 return percentile(vals, n/100)342 }343 return n344}345346// percentile interpolates between the values around rank p, as347// PERCENTILE.INC, finding them by selection rather than sorting.348func percentile(vals []float64, p float64) float64 {349 p = min(max(p, 0), 1)350 x := p * float64(len(vals)-1)351 i := int(x)352 v := nth(vals, i)353 if i >= len(vals)-1 {354 return v355 }356 next := slices.Min(vals[i+1:]) // nth leaves the larger values after i357 return v + (x-float64(i))*(next-v)358}359360// nth reorders vals so that vals[k] is the value a sort would put there,361// with no larger value before it and no smaller after, and returns it362// (quickselect, with the median of three as the pivot).363func nth(vals []float64, k int) float64 {364 lo, hi := 0, len(vals)-1365 for lo < hi {366 mid := lo + (hi-lo)/2367 if vals[mid] < vals[lo] {368 vals[mid], vals[lo] = vals[lo], vals[mid]369 }370 if vals[hi] < vals[lo] {371 vals[hi], vals[lo] = vals[lo], vals[hi]372 }373 if vals[hi] < vals[mid] {374 vals[hi], vals[mid] = vals[mid], vals[hi]375 }376 pivot := vals[mid]377 i, j := lo, hi378 for i <= j {379 for vals[i] < pivot {380 i++381 }382 for vals[j] > pivot {383 j--384 }385 if i <= j {386 vals[i], vals[j] = vals[j], vals[i]387 i++388 j--389 }390 }391 switch {392 case k <= j:393 hi = j394 case k >= i:395 lo = i396 default:397 return vals[k]398 }399 }400 return vals[k]401}402403// pointText writes a point as the rules editor shows it, e.g. "Min" or404// "Percentile 50".405func (p ScalePoint) String() string {406 if p.Kind.TakesValue() {407 return p.Kind.Title() + " " + p.Value408 }409 return p.Kind.Title()410}