1package sheet23import (4 "strings"5 "testing"6)78func rect(s string) Rect {9 r, _ := ParseRange(s)10 return r11}1213func TestValidName(t *testing.T) {14 for _, tc := range []struct {15 name string16 want string // start of the error, "" for valid17 }{18 {"Sales", ""},19 {"_tax.rate", ""},20 {"Q1_2026", ""},21 {"R2D2", ""},22 {"CAT", ""},23 {"", "Enter a name"},24 {"1st", "A name must start"},25 {"net profit", "A name can only"},26 {"$A", "A name must start"},27 {"true", "TRUE and FALSE"},28 {"A1", "A name can't look like a cell"},29 {"xfd1048576", "A name can't look like a cell"},30 {"R1C1", "A name can't look like a cell"},31 {"rc", "A name can't look like a cell"},32 {"C", "A name can't look like a cell"},33 {strings.Repeat("a", 251), "A name can be at most"},34 } {35 err := ValidName(tc.name)36 switch {37 case tc.want == "" && err != nil:38 t.Errorf("%q: %v", tc.name, err)39 case tc.want != "" && (err == nil || !strings.HasPrefix(err.Error(), tc.want)):40 t.Errorf("%q: got %v, want %q", tc.name, err, tc.want)41 }42 }43}4445func TestNamesInFormulas(t *testing.T) {46 s := sheetOf(t, map[string]string{47 "B1": "10", "B2": "20", "B3": "30", "D1": "0.5",48 "C1": "=SUM(sales)", "C2": "=Rate*2", "C3": "=AVERAGE(Sales)*Rate",49 })50 if v := s.Value(at("C1")); v != ErrName {51 t.Fatalf("undefined name: %v", v)52 }53 if err := s.DefineName("Sales", rect("B1:B3")); err != nil {54 t.Fatal(err)55 }56 if err := s.DefineName("Rate", rect("D1")); err != nil {57 t.Fatal(err)58 }59 check := func(when string, want map[string]float64) {60 t.Helper()61 for a, n := range want {62 if v := s.Value(at(a)); v.Kind != Number || v.Num != n {63 t.Errorf("%s: %s = %v, want %v", when, a, v, n)64 }65 }66 }67 check("defined", map[string]float64{"C1": 60, "C2": 1, "C3": 10})68 s.Set(at("B2"), "50")69 s.Set(at("D1"), "1")70 check("inputs changed", map[string]float64{"C1": 90, "C2": 2, "C3": 30})7172 if err := s.EditName("sales", "Sales", rect("B1:B2")); err != nil {73 t.Fatal(err)74 }75 check("range edited", map[string]float64{"C1": 60})76 if s.Value(at("B3")).Num != 30 {77 t.Error("B3 changed")78 }79 if err := s.DeleteName("RATE"); err != nil {80 t.Fatal(err)81 }82 if v := s.Value(at("C2")); v != ErrName {83 t.Errorf("deleted name: C2 = %v", v)84 }85 if err := s.DefineName("SALES", rect("A1")); err == nil || err.Error() != "Sales already names B1:B2" {86 t.Errorf("duplicate: %v", err)87 }88}8990func TestRenameRewritesFormulas(t *testing.T) {91 s := sheetOf(t, map[string]string{"A1": "4", "B1": "=Old*2+SUM(old)", "B2": "=Other"})92 s.DefineName("Old", rect("A1"))93 if err := s.EditName("Old", "Fresh", rect("A1")); err != nil {94 t.Fatal(err)95 }96 if got := s.Cell(at("B1")).Input; got != "=Fresh*2+SUM(Fresh)" {97 t.Errorf("B1 = %q", got)98 }99 if s.Value(at("B1")).Num != 12 || s.Cell(at("B2")).Input != "=Other" {100 t.Errorf("B1 %v B2 %q", s.Value(at("B1")), s.Cell(at("B2")).Input)101 }102 if _, ok := s.LookupName("old"); ok {103 t.Error("old name kept")104 }105 s.Undo()106 if got := s.Cell(at("B1")).Input; got != "=Old*2+SUM(old)" || s.Value(at("B1")).Num != 12 {107 t.Errorf("undo: B1 = %q %v", got, s.Value(at("B1")))108 }109}110111func TestNamesFollowStructureChanges(t *testing.T) {112 for _, tc := range []struct {113 name string114 op func(s *Sheet)115 want string // the name's range after the change116 }{117 {"insert rows above", func(s *Sheet) { s.InsertRows(0, 2) }, "B4:B6"},118 {"insert rows inside", func(s *Sheet) { s.InsertRows(2, 1) }, "B2:B5"},119 {"insert columns left", func(s *Sheet) { s.InsertCols(0, 1) }, "C2:C4"},120 {"delete rows inside", func(s *Sheet) { s.DeleteRows(2, 1) }, "B2:B3"},121 {"delete all rows", func(s *Sheet) { s.DeleteRows(1, 3) }, "#REF!"},122 {"delete the column", func(s *Sheet) { s.DeleteCols(1, 1) }, "#REF!"},123 {"move the range", func(s *Sheet) { s.Move(rect("B2:B4"), at("D5")) }, "D5:D7"},124 {"move part of it", func(s *Sheet) { s.Move(rect("B2:B3"), at("D5")) }, "B2:B4"},125 } {126 t.Run(tc.name, func(t *testing.T) {127 s := sheetOf(t, map[string]string{"B2": "1", "B3": "2", "B4": "3", "A10": "=SUM(Data)"})128 s.DefineName("Data", rect("B2:B4"))129 tc.op(s)130 n, _ := s.LookupName("data")131 if n.Ref() != tc.want {132 t.Fatalf("range %s, want %s", n.Ref(), tc.want)133 }134 // The formula still reads the name and follows it.135 for _, a := range s.Addrs() {136 if c := s.Cell(a); c.IsFormula() && c.Input != "=SUM(Data)" {137 t.Errorf("formula %s", c.Input)138 }139 }140 if n.Lost {141 if v := s.Value(s.Addrs()[len(s.Addrs())-1]); v != ErrRef {142 t.Errorf("lost name evaluates to %v", v)143 }144 }145 s.Undo()146 if n, _ := s.LookupName("Data"); n.Ref() != "B2:B4" {147 t.Errorf("undo: %s", n.Ref())148 }149 })150 }151}152153func TestNamesUndoRedo(t *testing.T) {154 s := sheetOf(t, map[string]string{"A1": "5", "B1": "=Price"})155 s.DefineName("Price", rect("A1"))156 if s.Value(at("B1")).Num != 5 {157 t.Fatalf("B1 = %v", s.Value(at("B1")))158 }159 c, ok := s.Undo()160 if !ok || c.Label != "name A1 Price" || s.Value(at("B1")) != ErrName || len(s.Names()) != 0 {161 t.Fatalf("undo: %v %v B1=%v names %v", c, ok, s.Value(at("B1")), s.Names())162 }163 s.Redo()164 if s.Value(at("B1")).Num != 5 {165 t.Fatalf("redo: B1 = %v", s.Value(at("B1")))166 }167 s.DeleteName("price")168 s.Undo()169 if n, ok := s.LookupName("PRICE"); !ok || n.Name != "Price" || s.Value(at("B1")).Num != 5 {170 t.Errorf("undo delete: %v %v", n, s.Value(at("B1")))171 }172}173174func TestNameCycles(t *testing.T) {175 s := sheetOf(t, map[string]string{"A1": "=SUM(Loop)", "A2": "1"})176 s.DefineName("Loop", rect("A1:A2"))177 if !s.Book().Circular || s.Value(at("A1")) != ErrRef {178 t.Errorf("circular %v A1 %v", s.Book().Circular, s.Value(at("A1")))179 }180}181182func TestNamesFileRoundTrip(t *testing.T) {183 s := sheetOf(t, map[string]string{"A1": "3", "A2": "4", "B1": "=SUM(Pair)", "B2": "=Gone"})184 s.DefineName("Pair", rect("A1:A2"))185 s.DefineName("Gone", rect("C5"))186 s.DeleteCols(2, 1)187 var b strings.Builder188 if err := s.Write(&b); err != nil {189 t.Fatal(err)190 }191 out := b.String()192 for _, want := range []string{`"version": 3`, `"names": {"Gone": "#REF!", "Pair": "A1:A2"}`} {193 if !strings.Contains(out, want) {194 t.Errorf("missing %s in\n%s", want, out)195 }196 }197 r, err := Read(strings.NewReader(out))198 if err != nil {199 t.Fatal(err)200 }201 if nameRefs(r) != nameRefs(s) || r.Value(at("B1")).Num != 7 || r.Value(at("B2")) != ErrRef {202 t.Errorf("read back %v B1=%v B2=%v", r.Names(), r.Value(at("B1")), r.Value(at("B2")))203 }204 if r.CanUndo() {205 t.Error("loading is undoable")206 }207208 for _, bad := range []string{209 `{"version": 3, "names": {"A1": "B2"}, "cells": {}}`,210 `{"version": 3, "names": {"x": "B2:"}, "cells": {}}`,211 `{"version": 3, "names": {"x": "B2", "X": "B3"}, "cells": {}}`,212 } {213 if _, err := Read(strings.NewReader(bad)); err == nil {214 t.Errorf("accepted %s", bad)215 }216 }217}218219func TestPrecedentsAndDependents(t *testing.T) {220 s := sheetOf(t, map[string]string{221 "A1": "1", "A2": "2", "B1": "3",222 "C1": "=A1+SUM(A1:A2)+Both+A1", "C2": "=A2*2", "C3": "=C1", "D1": "text",223 })224 s.DefineName("Both", rect("A2:B2"))225 if got, want := targets(s, s.Precedents(at("C1"))), "A1 A1:A2 A2:B2"; got != want {226 t.Errorf("precedents of C1 = %v, want %v", got, want)227 }228 if got := s.Precedents(at("D1")); got != nil {229 t.Errorf("precedents of text: %v", got)230 }231 if got, want := targets(s, s.Dependents(at("A2"))), "C1 C2"; got != want {232 t.Errorf("dependents of A2 = %v, want %v", got, want)233 }234 if got, want := targets(s, s.Dependents(at("B2"))), "C1"; got != want {235 t.Errorf("dependents of B2 through a name = %v, want %v", got, want)236 }237 if got := s.Dependents(at("B1")); len(got) != 0 {238 t.Errorf("dependents of B1 = %v", got)239 }240}241242// targets writes traced ranges as seen from s, e.g. "A1 Data!B2:B3".243func targets(s *Sheet, ts []Target) string {244 var out []string245 for _, t := range ts {246 ref := t.Range.String()247 if t.Sheet != s {248 ref = Qualified(t.Sheet.Name(), t.Range)249 }250 out = append(out, ref)251 }252 return strings.Join(out, " ")253}254255// nameRefs lists a workbook's names and what they stand for, to compare256// names across workbooks.257func nameRefs(s *Sheet) string {258 var b strings.Builder259 for _, n := range s.Names() {260 b.WriteString(n.Name + "=" + n.Ref() + " ")261 }262 return b.String()263}