1package sheet23import (4 "bytes"5 "math"6 "math/rand/v2"7 "slices"8 "strings"9 "testing"10)1112func ranges(s string) []Rect {13 rs, ok := ParseRanges(s)14 if !ok {15 panic(s)16 }17 return rs18}1920func mustAdd(t *testing.T, s *Sheet, f CondFormat) {21 t.Helper()22 if err := s.AddCondFormat(f); err != nil {23 t.Fatal(err)24 }25}2627var green = RuleStyle{Fill: ColorGreen}2829func TestCondFormatTests(t *testing.T) {30 fixedNow(t)31 s := sheetOf(t, map[string]string{32 "A1": "150", "A2": "50", "A3": "", "A4": "apple pie", "A5": "Pineapple", "A6": "2026-09-25",33 "A7": "2026-09-27", "A8": "12", "B1": "=A1*2",34 })35 s.SetFormat(rng("A3"), Format{Kind: FmtNumber, Decimals: 2}) // formatted but blank36 cases := []struct {37 op RuleOp38 args [2]string39 match string // cells of A1:A8 that match40 }{41 {RuleGreater, [2]string{"100"}, "A1 A6 A7"},42 {RuleLessEq, [2]string{"50"}, "A2 A8"},43 {RuleBetween, [2]string{"60", "10"}, "A2 A8"},44 {RuleNotBetween, [2]string{"10", "60"}, "A1 A6 A7"},45 {RuleEmpty, [2]string{}, "A3"},46 {RuleNotEmpty, [2]string{}, "A1 A2 A4 A5 A6 A7 A8"},47 {RuleContains, [2]string{"APPLE"}, "A4 A5"},48 {RuleStartsWith, [2]string{"apple"}, "A4"},49 {RuleEndsWith, [2]string{"pie"}, "A4"},50 {RuleExactly, [2]string{"pineapple"}, "A5"},51 {RuleDateBefore, [2]string{"today"}, "A1 A2 A6 A8"},52 {RuleDateIs, [2]string{"tomorrow"}, "A7"},53 {RuleDateAfter, [2]string{"2026-09-26"}, "A7"},54 {RuleEqual, [2]string{"=A$8"}, "A8"},55 {RuleFormula, [2]string{"=$A1>=150"}, "A1 A4 A5 A6 A7"}, // text sorts after numbers,56 {RuleFormula, [2]string{"=B1=300"}, "A1"},57 }58 for _, c := range cases {59 s.rules = rulesState{}60 mustAdd(t, s, CondFormat{Ranges: ranges("A1:A8"), Op: c.op, Args: c.args, Style: green})61 var got []string62 for row := range 8 {63 if s.Look(Addr{Row: row}).Styled {64 got = append(got, Addr{Row: row}.String())65 }66 }67 if strings.Join(got, " ") != c.match {68 t.Errorf("%s %v: matched %v, want %s", c.op, c.args, got, c.match)69 }70 }71}7273func TestCondFormatOrderAndScale(t *testing.T) {74 s := sheetOf(t, map[string]string{"A1": "0", "A2": "5", "A3": "10", "A4": "x"})75 red := RuleStyle{Text: ColorRed, Bold: true}76 mustAdd(t, s, CondFormat{Ranges: ranges("A1:A4"), Op: RuleGreater, Args: [2]string{"7"}, Style: red})77 mustAdd(t, s, CondFormat{Ranges: ranges("A1:A4"), Scale: []ScalePoint{78 {Kind: PointMin, Color: ColorRed}, {Kind: PointPercentile, Value: "50", Color: ColorYellow}, {Kind: PointMax, Color: ColorGreen}}})79 if l := s.Look(at("A3")); !l.Styled || l.Style != red || l.Scaled {80 t.Errorf("the first rule wins: %+v", l)81 }82 l := s.Look(at("A2"))83 if !l.Scaled || l.From != ColorRed || l.To != ColorYellow || l.Pos != 1 {84 t.Errorf("midpoint: %+v", l)85 }86 if l := s.Look(at("A1")); !l.Scaled || l.From != ColorRed || l.Pos != 0 {87 t.Errorf("min: %+v", l)88 }89 if l := s.Look(at("A4")); l.Scaled || l.Styled {90 t.Errorf("text on a scale: %+v", l)91 }92 s.MoveCondFormat(1, 0)93 if l := s.Look(at("A3")); !l.Scaled || l.Pos != 1 || l.To != ColorGreen {94 t.Errorf("after moving the scale first: %+v", l)95 }96 // The looks follow edits: the scale reads the new values.97 s.Set(at("A3"), "20")98 if l := s.Look(at("A2")); l.Pos != 1 || l.To != ColorYellow || math.Abs(s.Look(at("A3")).Pos-1) > 1e-9 {99 t.Errorf("after an edit: %+v", l)100 }101 if l := s.Look(at("A1")); l.From != ColorRed || l.To != ColorYellow {102 t.Errorf("min segment: %+v", l)103 }104}105106func TestCondFormatCheck(t *testing.T) {107 bad := []CondFormat{108 {Op: RuleGreater, Args: [2]string{"1"}, Style: green},109 {Ranges: ranges("A1"), Op: RuleGreater, Style: green},110 {Ranges: ranges("A1"), Op: RuleGreater, Args: [2]string{"1"}},111 {Ranges: ranges("A1"), Op: RuleFormula, Args: [2]string{"A1>1"}, Style: green},112 {Ranges: ranges("A1"), Op: RuleFormula, Args: [2]string{"=A1>"}, Style: green},113 {Ranges: ranges("A1"), Op: RuleDateIs, Args: [2]string{"soon"}, Style: green},114 {Ranges: ranges("A1"), Scale: []ScalePoint{{Kind: PointMin, Color: ColorRed}}},115 {Ranges: ranges("A1"), Scale: []ScalePoint{{Kind: PointMin, Color: ColorRed}, {Kind: PointPercent, Value: "150", Color: ColorRed}}},116 }117 for _, f := range bad {118 if f.Check() == nil {119 t.Errorf("accepted %+v", f)120 }121 }122 s := New()123 if err := s.AddCondFormat(bad[2]); err == nil || s.CanUndo() {124 t.Error("a bad rule was added")125 }126}127128func TestRulesUndoAndFile(t *testing.T) {129 s := sheetOf(t, map[string]string{"A1": "1"})130 mustAdd(t, s, CondFormat{Ranges: ranges("A1:A9,C1:C9"), Op: RuleFormula, Args: [2]string{"=$B1>100"}, Style: RuleStyle{Text: ColorBlue, Italic: true}})131 mustAdd(t, s, CondFormat{Ranges: ranges("B1:B9"), Scale: []ScalePoint{{Kind: PointNumber, Value: "0", Color: ColorRed}, {Kind: PointMax, Color: ColorGreen}}})132 if err := s.AddValidation(Validation{Ranges: ranges("D1:D9"), Kind: ValidList, Items: []string{"Yes", "No"}, Reject: true, Help: "Pick one"}); err != nil {133 t.Fatal(err)134 }135 if err := s.AddValidation(Validation{Ranges: ranges("E1:E9"), Kind: ValidNumber, Op: RuleBetween, Args: [2]string{"1", "10"}}); err != nil {136 t.Fatal(err)137 }138 if s.Book().UndoLabel() != "add data validation E1:E9" {139 t.Errorf("label %q", s.Book().UndoLabel())140 }141 var buf bytes.Buffer142 if err := s.Write(&buf); err != nil {143 t.Fatal(err)144 }145 for _, want := range []string{146 `"conditionalFormats": [`,147 `{"ranges":"A1:A9,C1:C9","condition":"formula","values":["=$B1\u003e100"],"text":"blue","italic":true}`,148 `{"ranges":"B1:B9","scale":[{"type":"num","value":"0","color":"red"},{"type":"max","color":"green"}]}`,149 `{"ranges":"D1:D9","criteria":"list","items":["Yes","No"],"reject":true,"help":"Pick one"}`,150 `{"ranges":"E1:E9","criteria":"number","condition":"between","values":["1","10"]}`,151 `"version": 2,`,152 } {153 if !strings.Contains(buf.String(), want) {154 t.Errorf("file lacks %s:\n%s", want, buf.String())155 }156 }157 back, err := Read(bytes.NewReader(buf.Bytes()))158 if err != nil {159 t.Fatal(err)160 }161 if !back.rules.equal(s.rules) {162 t.Errorf("round trip:\n%+v\n%+v", back.rules, s.rules)163 }164 for range 4 {165 s.Undo()166 }167 if !s.rules.empty() {168 t.Errorf("undo left %+v", s.rules)169 }170 s.Redo()171 if len(s.CondFormats()) != 1 {172 t.Errorf("redo: %+v", s.rules)173 }174}175176func TestRulesFollowInsertsAndRenames(t *testing.T) {177 w := NewBook()178 s := w.Sheet(0)179 lists, _ := w.AddSheet("Lists", 1)180 mustAdd(t, s, CondFormat{Ranges: ranges("B2:B9"), Op: RuleFormula, Args: [2]string{"=$C2>Lists!$A$1"}, Style: green})181 if err := s.AddValidation(Validation{Ranges: ranges("D2:D9"), Kind: ValidRange, Source: "Lists!A1:A5"}); err != nil {182 t.Fatal(err)183 }184 s.InsertRows(0, 2)185 s.InsertCols(0, 1)186 lists.InsertRows(0, 1)187 f, v := s.CondFormats()[0], s.Validations()[0]188 if RangesText(f.Ranges) != "C4:C11" || f.Args[0] != "=$D4>Lists!$A$2" {189 t.Errorf("format after inserts: %s %s", RangesText(f.Ranges), f.Args[0])190 }191 if RangesText(v.Ranges) != "E4:E11" || v.Source != "Lists!A2:A6" {192 t.Errorf("validation after inserts: %s %s", RangesText(v.Ranges), v.Source)193 }194 if err := w.RenameSheet(lists, "Choices"); err != nil {195 t.Fatal(err)196 }197 if f := s.CondFormats()[0]; f.Args[0] != "=$D4>Choices!$A$2" || s.Validations()[0].Source != "Choices!A2:A6" {198 t.Errorf("after rename: %s %s", f.Args[0], s.Validations()[0].Source)199 }200 s.DeleteCols(2, 1)201 if len(s.CondFormats()) != 0 {202 t.Error("a rule whose cells were deleted stays")203 }204 w.Undo()205 if len(s.CondFormats()) != 1 {206 t.Error("undo didn't bring the rule back")207 }208}209210func TestValidation(t *testing.T) {211 fixedNow(t)212 s := sheetOf(t, map[string]string{"A1": "Red", "A2": "Blue", "A3": "red", "A4": "", "A5": "Green", "B1": "Blue"})213 add := func(v Validation) {214 t.Helper()215 if err := s.AddValidation(v); err != nil {216 t.Fatal(err)217 }218 }219 add(Validation{Ranges: ranges("B1:B9"), Kind: ValidRange, Source: "$A$1:$A$9", Reject: true})220 add(Validation{Ranges: ranges("C1:C9"), Kind: ValidList, Items: []string{"Yes", "No"}})221 add(Validation{Ranges: ranges("D1:D9"), Kind: ValidNumber, Op: RuleBetween, Args: [2]string{"1", "10"}})222 add(Validation{Ranges: ranges("E1:E9"), Kind: ValidDate, Op: RuleGreater, Args: [2]string{"2026-01-01"}})223 add(Validation{Ranges: ranges("F1:F9"), Kind: ValidLength, Op: RuleLessEq, Args: [2]string{"3"}})224 add(Validation{Ranges: ranges("G1:G9"), Kind: ValidFormula, Args: [2]string{"=G1<>A1"}})225 add(Validation{Ranges: ranges("H1:H9"), Kind: ValidCheckbox})226 if got := strings.Join(s.DropdownItems(at("B2")), ","); got != "Red,Blue,Green" {227 t.Errorf("items %q", got)228 }229 cases := []struct {230 cell, input string231 ok bool232 }{233 {"B1", "green", true}, {"B1", "Purple", false}, {"C1", "yes", true}, {"C1", "maybe", false},234 {"D1", "10", true}, {"D1", "11", false}, {"D1", "=2*3", true}, {"D1", "ten", false},235 {"E1", "2026-03-01", true}, {"E1", "2025-12-31", false}, {"E1", "45000", false},236 {"F1", "abc", true}, {"F1", "abcd", false}, {"G1", "Red", false}, {"G1", "Pink", true},237 {"H1", "TRUE", true}, {"H1", "yes", false}, {"Z1", "anything", true}, {"D1", "", true},238 }239 for _, c := range cases {240 err := s.CheckEntry(at(c.cell), c.input)241 if (err == nil) != c.ok {242 t.Errorf("%s %q: %v", c.cell, c.input, err)243 }244 }245 err := s.CheckEntry(at("B1"), "Purple")246 if err == nil || !err.Reject || err.Help != "Input must fall within specified range" {247 t.Errorf("reject: %+v", err)248 }249 if err := s.CheckEntry(at("D1"), "0"); err == nil || err.Reject || err.Help != "Input must be a number between 1 and 10" {250 t.Errorf("warn: %+v", err)251 }252 s.Set(at("D2"), "42")253 s.Set(at("H2"), "TRUE")254 if l := s.Look(at("D2")); !l.Invalid {255 t.Errorf("invalid mark: %+v", l)256 }257 if l := s.Look(at("H2")); !l.Checkbox || !l.Checked {258 t.Errorf("checkbox: %+v", l)259 }260 if l := s.Look(at("H3")); !l.Checkbox || l.Checked || l.Invalid {261 t.Errorf("blank checkbox: %+v", l)262 }263 if l := s.Look(at("C3")); !l.Dropdown {264 t.Errorf("dropdown: %+v", l)265 }266 // A rule over cells of another takes them.267 add(Validation{Ranges: ranges("C5:D6"), Kind: ValidCheckbox})268 if got := RangesText(s.Validations()[1].Ranges); got != "C1:C4,C7:C9" {269 t.Errorf("list rule's cells: %s", got)270 }271 if got := RangesText(s.Validations()[2].Ranges); got != "D1:D4,D7:D9" {272 t.Errorf("number rule's cells: %s", got)273 }274 s.ClearValidations(rng("A1:XFD1048576"))275 if len(s.Validations()) != 0 {276 t.Errorf("clear left %+v", s.Validations())277 }278}279280func TestRuleJSON(t *testing.T) {281 lines := []string{282 `{"ranges":"A1:B2","condition":"between","values":["1","2"],"fill":"yellow","underline":true}`,283 `{"ranges":"A:A","scale":[{"type":"min","color":"red"},{"type":"percentile","value":"50","color":"yellow"},{"type":"max","color":"green"}]}`,284 }285 for _, l := range lines {286 f, err := ParseCondFormat(l)287 if err != nil || f.JSON() != l {288 t.Errorf("%s: %v %s", l, err, f.JSON())289 }290 }291 v, err := ParseValidation(`{"ranges":"C1:C9","criteria":"date","condition":"lt","values":["2026-12-31"],"reject":true}`)292 if err != nil || v.Kind != ValidDate || v.Op != RuleLess || !v.Reject {293 t.Errorf("%+v %v", v, err)294 }295 if _, err := ParseValidation(`{"ranges":"C1","criteria":"list"}`); err == nil {296 t.Error("a list without items")297 }298 if _, err := ParseCondFormat(`{"ranges":"C1","condition":"gt","values":["1"],"fill":"mauve"}`); err == nil {299 t.Error("an unknown color")300 }301}302303// Percentiles by selection agree with sorting, duplicates and all.304func TestPercentileBySelection(t *testing.T) {305 r := rand.New(rand.NewPCG(3, 4))306 for _, n := range []int{1, 2, 3, 10, 101, 1000} {307 vals := make([]float64, n)308 for i := range vals {309 vals[i] = float64(r.IntN(20))310 }311 sorted := slices.Sorted(slices.Values(vals))312 for _, p := range []float64{0, 0.1, 0.25, 0.5, 0.9, 1} {313 x := p * float64(n-1)314 i := int(x)315 want := sorted[i]316 if i < n-1 {317 want += (x - float64(i)) * (sorted[i+1] - sorted[i])318 }319 if got := percentile(slices.Clone(vals), p); math.Abs(got-want) > 1e-9 {320 t.Errorf("n %d p %v: %v, want %v", n, p, got, want)321 }322 }323 }324}325326func TestSubtract(t *testing.T) {327 got := RangesText(subtract(rng("A1:E5"), rng("B2:C3")))328 if got != "A1:E1,A4:E5,A2:A3,D2:E3" {329 t.Errorf("%s", got)330 }331 if got := RangesText(subtract(rng("A1:A5"), rng("C1:C5"))); got != "A1:A5" {332 t.Errorf("disjoint: %s", got)333 }334 if got := subtract(rng("B2:B3"), rng("A1:C9")); len(got) != 0 {335 t.Errorf("covered: %v", got)336 }337}