1package sheet23import (4 "math"5 "strings"6 "testing"7)89// matching lists the cells of A1:A<n> a sheet's rules style.10func matching(s *Sheet, n int) string {11 var got []string12 for row := range n {13 if s.Look(Addr{Row: row}).Styled {14 got = append(got, Addr{Row: row}.String())15 }16 }17 return strings.Join(got, " ")18}1920// Top and bottom values, above and below average, duplicates and21// uniques compare a cell with the rest of the rule's cells.22func TestRankingRules(t *testing.T) {23 s := sheetOf(t, map[string]string{24 "A1": "10", "A2": "40", "A3": "30", "A4": "40", "A5": "Apple", "A6": "apple", "A7": "pear", "A8": "",25 })26 for _, c := range []struct {27 op RuleOp28 arg string29 match string30 }{31 {RuleTop, "2", "A2 A4"}, // ties count32 {RuleTop, "3", "A2 A3 A4"},33 {RuleBottom, "1", "A1"},34 {RuleTopPercent, "50", "A2 A4"},35 {RuleBottomPercent, "10", "A1"}, // at least one36 {RuleTop, "99", "A1 A2 A3 A4"},37 {RuleAboveAverage, "", "A2 A4"}, // the mean is 3038 {RuleBelowAverage, "", "A1"},39 {RuleDuplicate, "", "A2 A4 A5 A6"},40 {RuleUnique, "", "A1 A3 A7"},41 } {42 s.rules = rulesState{}43 mustAdd(t, s, CondFormat{Ranges: ranges("A1:A8"), Op: c.op, Args: [2]string{c.arg}, Style: green})44 if got := matching(s, 8); got != c.match {45 t.Errorf("%s %s: matched %q, want %q", c.op, c.arg, got, c.match)46 }47 }48 for _, bad := range []CondFormat{49 {Ranges: ranges("A1:A8"), Op: RuleTop, Args: [2]string{"0"}, Style: green},50 {Ranges: ranges("A1:A8"), Op: RuleTopPercent, Args: [2]string{"101"}, Style: green},51 {Ranges: ranges("A1:A8"), Op: RuleBottom, Args: [2]string{"x"}, Style: green},52 } {53 if bad.Check() == nil {54 t.Errorf("%s %q passed", bad.Op, bad.Args[0])55 }56 }57 f := CondFormat{Ranges: ranges("A1"), Op: RuleTopPercent, Args: [2]string{"10"}, Style: green}58 if f.Summary() != "Top 10%" {59 t.Errorf("summary %q", f.Summary())60 }61}6263// Date rules take periods: this, last or next week, month or year, and64// the past week, month or year. 2026-09-26 is a Saturday.65func TestDatePeriods(t *testing.T) {66 fixedNow(t)67 s := sheetOf(t, map[string]string{68 "A1": "2026-09-19", "A2": "2026-09-20", "A3": "2026-09-26", "A4": "2026-09-27", "A5": "2026-10-15",69 "A6": "2026-08-31", "A7": "2025-12-31", "A8": "2026-01-01",70 })71 for _, c := range []struct {72 op RuleOp73 arg string74 match string75 }{76 {RuleDateIs, "this week", "A2 A3"},77 {RuleDateIs, "last week", "A1"},78 {RuleDateIs, "next week", "A4"},79 {RuleDateIs, "this month", "A1 A2 A3 A4"},80 {RuleDateIs, "Next month", "A5"},81 {RuleDateIs, "last month", "A6"},82 {RuleDateIs, "this year", "A1 A2 A3 A4 A5 A6 A8"},83 {RuleDateIs, "last year", "A7"},84 {RuleDateIs, "past week", "A2 A3"},85 {RuleDateIs, "in the past month", "A1 A2 A3 A6"},86 {RuleDateBefore, "this week", "A1 A6 A7 A8"},87 {RuleDateAfter, "this month", "A5"},88 {RuleDateIs, "today", "A3"},89 } {90 s.rules = rulesState{}91 mustAdd(t, s, CondFormat{Ranges: ranges("A1:A8"), Op: c.op, Args: [2]string{c.arg}, Style: green})92 if got := matching(s, 8); got != c.match {93 t.Errorf("%s %q: matched %q, want %q", c.op, c.arg, got, c.match)94 }95 }96 if !IsPeriod("last month") || IsPeriod("2026-09-01") || IsPeriod("soon") {97 t.Error("IsPeriod")98 }99 if (CondFormat{Ranges: ranges("A1"), Op: RuleDateIs, Args: [2]string{"soon"}, Style: green}).Check() == nil {100 t.Error("a period that isn't one passed")101 }102}103104// A data bar is as long as its number is far from the shortest point,105// zero for positive numbers, to the longest; an icon set gives each106// number the icon of the last threshold it reaches.107func TestDataBarsAndIcons(t *testing.T) {108 s := sheetOf(t, map[string]string{"A1": "0", "A2": "25", "A3": "50", "A4": "100", "A5": "x"})109 mustAdd(t, s, CondFormat{Ranges: ranges("A2:A5"), Bar: ColorBlue,110 Scale: []ScalePoint{{Kind: PointMin}, {Kind: PointMax}}})111 for cell, want := range map[string]float64{"A2": 0.25, "A3": 0.5, "A4": 1} {112 l := s.Look(at(cell))113 if !l.Bar || l.BarColor != ColorBlue || math.Abs(l.BarLen-want) > 1e-9 {114 t.Errorf("%s: %+v, want a bar %v long", cell, l, want)115 }116 }117 if s.Look(at("A5")).Bar {118 t.Error("text has a bar")119 }120 s.rules = rulesState{}121 icons := CondFormat{Ranges: ranges("A1:A4"), Icons: IconsArrows,122 Scale: []ScalePoint{{Kind: PointPercent, Value: "33"}, {Kind: PointPercent, Value: "67"}}}123 mustAdd(t, s, icons)124 for cell, want := range map[string]string{"A1": "↓", "A2": "↓", "A3": "→", "A4": "↑"} {125 if l := s.Look(at(cell)); l.Icon != want {126 t.Errorf("%s: icon %q, want %q", cell, l.Icon, want)127 }128 }129 if l := s.Look(at("A4")); l.IconColor != ColorGreen || l.ValueHidden {130 t.Errorf("A4: %+v", l)131 }132 icons.Reverse, icons.BarOnly = true, true133 s.SetCondFormat(0, icons)134 if l := s.Look(at("A4")); l.Icon != "↓" || l.IconColor != ColorRed || !l.ValueHidden {135 t.Errorf("reversed A4: %+v", l)136 }137 for _, bad := range []CondFormat{138 {Ranges: ranges("A1"), Icons: IconsSymbols, Scale: []ScalePoint{{Kind: PointPercent, Value: "50"}}},139 {Ranges: ranges("A1"), Icons: IconsArrows, Scale: []ScalePoint{{Kind: PointPercent, Value: "150"}, {Kind: PointNumber, Value: "2"}}},140 {Ranges: ranges("A1"), Bar: ColorRed, Scale: []ScalePoint{{Kind: PointMin}}},141 } {142 if bad.Check() == nil {143 t.Errorf("%+v passed", bad)144 }145 }146}147148// Data bars and icon sets are lines of the file as the other rules are.149func TestBarAndIconJSON(t *testing.T) {150 for _, line := range []string{151 `{"ranges":"B2:B9","dataBar":{"color":"blue","min":{"type":"num","value":"0"},"max":{"type":"percentile","value":"90"},"barOnly":true}}`,152 `{"ranges":"C2:C9","iconSet":{"icons":"rating","points":[{"type":"percent","value":"20"},{"type":"percent","value":"40"},{"type":"percent","value":"60"},{"type":"percent","value":"80"}],"reverse":true,"iconOnly":true}}`,153 `{"ranges":"D2:D9","condition":"top_percent","values":["10"],"fill":"green"}`,154 `{"ranges":"E2:E9","condition":"date_is","values":["last month"],"bold":true}`,155 } {156 f, err := ParseCondFormat(line)157 if err != nil {158 t.Errorf("%s: %v", line, err)159 continue160 }161 if got := f.JSON(); got != line {162 t.Errorf("read and written:\n%s\nwant\n%s", got, line)163 }164 }165 for _, bad := range []string{166 `{"ranges":"B2","dataBar":{"color":"","min":{"type":"min"},"max":{"type":"max"}}}`,167 `{"ranges":"B2","iconSet":{"icons":"stars","points":[]}}`,168 `{"ranges":"B2","iconSet":{"icons":"symbols","points":[{"type":"percent","value":"50"}]}}`,169 } {170 if _, err := ParseCondFormat(bad); err == nil {171 t.Errorf("%s read", bad)172 }173 }174}175176// A checkbox may hold values of its own: checked when a cell shows the177// first, unchecked for the second or a blank, invalid otherwise.178func TestCustomCheckbox(t *testing.T) {179 s := sheetOf(t, map[string]string{"A1": "Yes", "A2": "no", "A3": "", "A4": "TRUE", "A5": "maybe"})180 v := Validation{Ranges: ranges("A1:A5"), Kind: ValidCheckbox, Items: []string{"Yes", "No"}}181 if err := s.AddValidation(v); err != nil {182 t.Fatal(err)183 }184 for cell, want := range map[string][2]bool{"A1": {true, false}, "A2": {false, false}, "A3": {false, false}, "A4": {false, true}, "A5": {false, true}} {185 l := s.Look(at(cell))186 if !l.Checkbox || l.Checked != want[0] || l.Invalid != want[1] {187 t.Errorf("%s: %+v, want checked %v invalid %v", cell, l, want[0], want[1])188 }189 }190 if s.CheckboxInput(at("A1"), true) != "Yes" || s.CheckboxInput(at("A1"), false) != "No" {191 t.Error("toggled values")192 }193 if bad := s.CheckEntry(at("A3"), "maybe"); bad == nil || bad.Help != "Input must be Yes or No" {194 t.Errorf("entry check %+v", bad)195 }196 for _, bad := range []Validation{197 {Ranges: ranges("B1"), Kind: ValidCheckbox, Items: []string{"", "No"}},198 {Ranges: ranges("B1"), Kind: ValidCheckbox, Items: []string{"x", "X"}},199 {Ranges: ranges("B1"), Kind: ValidCheckbox, Items: []string{"a", "b", "c"}},200 } {201 if bad.Check() == nil {202 t.Errorf("%q passed", bad.Items)203 }204 }205 for _, line := range []string{206 `{"ranges":"A1:A5","criteria":"checkbox","items":["Yes","No"]}`,207 `{"ranges":"B1:B5","criteria":"list","items":["a","b"],"display":"chip"}`,208 `{"ranges":"C1:C5","criteria":"range","source":"A1:A5","display":"plain"}`,209 } {210 v, err := ParseValidation(line)211 if err != nil || v.JSON() != line {212 t.Errorf("%s read as %s, %v", line, v.JSON(), err)213 }214 }215 if _, err := ParseValidation(`{"ranges":"A1","criteria":"list","items":["a"],"display":"bubble"}`); err == nil {216 t.Error("an unknown display read")217 }218 s.AddValidation(Validation{Ranges: ranges("B1"), Kind: ValidList, Items: []string{"a"}, Display: DropChip})219 if l := s.Look(at("B1")); !l.Dropdown || l.Display != DropChip {220 t.Errorf("chip look %+v", l)221 }222}