1package sheet23import (4 "bytes"5 "strings"6 "testing"7)89func wantShown(t *testing.T, s *Sheet, want map[string]string) {10 t.Helper()11 for a, w := range want {12 if got := s.Value(at(a)).String(); got != w {13 t.Errorf("%s = %q, want %q", a, got, w)14 }15 }16}1718func TestSpillSequence(t *testing.T) {19 s := sheetOf(t, map[string]string{"A1": "3", "B2": "=SEQUENCE(A1, 2)"})20 wantShown(t, s, map[string]string{"B2": "1", "C2": "2", "B3": "3", "C3": "4", "B4": "5", "C4": "6", "B5": ""})21 if a, ok := s.SpillAnchor(at("C3")); !ok || a != at("B2") {22 t.Errorf("C3's anchor = %v %v, want B2", a, ok)23 }24 if _, ok := s.SpillAnchor(at("B2")); ok {25 t.Error("the anchor is its own cell, not spilled")26 }27 if r, ok := s.SpillArea(at("B2")); !ok || r != rect("B2:C4") {28 t.Errorf("area %v %v, want B2:C4", r, ok)29 }30 // Growing and shrinking rewrite the spilled cells.31 s.Set(at("A1"), "4")32 wantShown(t, s, map[string]string{"B5": "7", "C5": "8"})33 s.Set(at("A1"), "1")34 wantShown(t, s, map[string]string{"B2": "1", "C2": "2", "B3": "", "C5": ""})35 if s.Cell(at("B3")) != nil {36 t.Error("a cell the spill left is still stored")37 }38 // Undo brings the bigger array back.39 s.Book().Undo()40 wantShown(t, s, map[string]string{"B5": "7", "C5": "8"})41}4243func TestSpillBlocked(t *testing.T) {44 s := sheetOf(t, map[string]string{"A1": "=SEQUENCE(3)", "A3": "note"})45 wantShown(t, s, map[string]string{"A1": "#REF!", "A2": "", "A3": "note"})46 if got := s.ExplainError(at("A1")); got != "Array result was not expanded because it would overwrite data in A3" {47 t.Errorf("A1 explained as %q", got)48 }49 // Clearing what's in the way lets it spill.50 if err := s.Set(at("A3"), ""); err != nil {51 t.Fatal(err)52 }53 wantShown(t, s, map[string]string{"A1": "1", "A2": "2", "A3": "3"})54 // Spilled cells can't be typed into.55 if err := s.Set(at("A2"), "x"); err != ErrSpillEdit {56 t.Errorf("Set in a spill: %v, want ErrSpillEdit", err)57 }58 if a, ok := s.InSpill(rect("A2:B9")); !ok || a != at("A2") {59 t.Errorf("InSpill(A2:B9) = %v %v", a, ok)60 }61 if _, ok := s.InSpill(rect("A1:A3")); ok {62 t.Error("a range holding the anchor can be edited as a whole")63 }64 // The sheet's edge blocks too.65 s.Set(at("A1048575"), "=SEQUENCE(5)")66 if got := s.ExplainError(at("A1048575")); !strings.Contains(got, "edge of the sheet") {67 t.Errorf("at the edge: %q", got)68 }69}7071func TestSpillDependents(t *testing.T) {72 w := NewBook()73 s := w.Sheet(0)74 other, err := w.AddSheet("Other", 1)75 if err != nil {76 t.Fatal(err)77 }78 for a, in := range map[string]string{"A1": "5", "A2": "3", "A3": "8", "C1": "=SORT(A1:A3)", "E1": "=C3*10", "E2": "=SUM(C1:C9)"} {79 if err := s.Set(at(a), in); err != nil {80 t.Fatal(err)81 }82 }83 if err := other.Set(at("A1"), "=Sheet1!C2+1"); err != nil {84 t.Fatal(err)85 }86 wantShown(t, s, map[string]string{"C1": "3", "C2": "5", "C3": "8", "E1": "80", "E2": "16"})87 wantShown(t, other, map[string]string{"A1": "6"})88 s.Set(at("A2"), "9")89 wantShown(t, s, map[string]string{"C1": "5", "C2": "8", "C3": "9", "E1": "90", "E2": "22"})90 wantShown(t, other, map[string]string{"A1": "9"})91 // A spill reading a spill.92 s.Set(at("G1"), "=FILTER(C1:C3, C1:C3>5)")93 wantShown(t, s, map[string]string{"G1": "8", "G2": "9", "G3": ""})94 s.Set(at("A1"), "1")95 wantShown(t, s, map[string]string{"C1": "1", "G1": "8", "G2": "9"})96 // Deleting the anchor clears its spill, and what read it follows.97 s.Set(at("C1"), "")98 wantShown(t, s, map[string]string{"C2": "", "C3": "", "E1": "0", "E2": "0", "G1": "#N/A"})99 wantShown(t, other, map[string]string{"A1": "1"})100 w.Undo()101 wantShown(t, s, map[string]string{"C3": "9", "E1": "90", "G1": "8"})102}103104func TestSpillNotSaved(t *testing.T) {105 s := sheetOf(t, map[string]string{"A1": "=SEQUENCE(2)", "B1": "x"})106 var buf bytes.Buffer107 if err := s.Book().Write(&buf); err != nil {108 t.Fatal(err)109 }110 if strings.Contains(buf.String(), `"A2"`) {111 t.Errorf("spilled cell saved:\n%s", buf.String())112 }113 w, err := ReadBook(&buf)114 if err != nil {115 t.Fatal(err)116 }117 wantShown(t, w.Sheet(0), map[string]string{"A1": "1", "A2": "2"})118}119120func TestSpillStructure(t *testing.T) {121 s := sheetOf(t, map[string]string{"A1": "=SEQUENCE(3)", "C1": "=SUM(A1:A3)", "A5": "end"})122 if err := s.InsertRows(1, 1); err != nil {123 t.Fatal(err)124 }125 wantShown(t, s, map[string]string{"A1": "1", "A2": "2", "A3": "3", "A4": "", "A6": "end", "C1": "6"})126 s.DeleteRows(0, 1) // the anchor goes127 wantShown(t, s, map[string]string{"A1": "", "A2": "", "A5": "end"})128 if len(s.spills) != 0 {129 t.Errorf("spills left: %v", s.spills)130 }131 s.Book().Undo()132 s.Book().Undo()133 wantShown(t, s, map[string]string{"A1": "1", "A3": "3", "A5": "end", "C1": "6"})134}135136func TestSpillFormattingKept(t *testing.T) {137 s := sheetOf(t, map[string]string{"A1": "=SEQUENCE(3)"})138 s.SetStyle(rect("A2:A2"), func(st *Style) { st.Bold = true })139 wantShown(t, s, map[string]string{"A2": "2"})140 if c := s.Cell(at("A2")); !c.Spilled() || !c.Style.Bold {141 t.Errorf("A2 = %+v, want spilled and bold", c)142 }143 s.Set(at("A1"), "=SEQUENCE(1)")144 if c := s.Cell(at("A2")); c == nil || c.Spilled() || !c.Style.Bold || !c.Blank() {145 t.Errorf("A2 after the spill shrank = %+v, want blank and bold", c)146 }147}148149func TestSpillCopiesValues(t *testing.T) {150 s := sheetOf(t, map[string]string{"A1": "=SEQUENCE(3)"})151 clip := s.Copy(rect("A1:A3"))152 if _, err := s.Paste(clip, rect("C1:C1"), false); err != nil {153 t.Fatal(err)154 }155 if got := s.Cell(at("C2")).Input; got != "2" {156 t.Errorf("pasted spilled cell = %q, want its value", got)157 }158}159160// A note on a blank cell an array spills into stays with the cell, as its161// formatting does, but spilled cells take no new notes.162func TestSpillNotes(t *testing.T) {163 s := sheetOf(t, map[string]string{"A1": "=SEQUENCE(1)"})164 if err := s.SetNote(at("A2"), "check"); err != nil {165 t.Fatal(err)166 }167 s.Set(at("A1"), "=SEQUENCE(3)")168 if c := s.Cell(at("A2")); !c.Spilled() || c.Note != "check" || s.Value(at("A2")).String() != "2" {169 t.Errorf("A2 under the spill = %+v", c)170 }171 if err := s.SetNote(at("A3"), "x"); err != ErrSpillEdit {172 t.Errorf("note on a spilled cell: %v, want ErrSpillEdit", err)173 }174 var buf bytes.Buffer175 if err := s.Book().Write(&buf); err != nil {176 t.Fatal(err)177 }178 if !strings.Contains(buf.String(), `"A2": {"note":"check"}`) {179 t.Errorf("the note wasn't saved on its own:\n%s", buf.String())180 }181 s.Set(at("A1"), "")182 if c := s.Cell(at("A2")); c == nil || c.Spilled() || c.Note != "check" {183 t.Errorf("A2 after the spill went = %+v", c)184 }185}186187// Rules over a spill see its values: a conditional format colors spilled188// cells and a validation rule marks spilled values it doesn't accept,189// but neither stops the array from spilling, as rules only ever judge190// what's typed.191func TestSpillRules(t *testing.T) {192 s := sheetOf(t, map[string]string{"A1": "=SEQUENCE(4)"})193 if err := s.AddCondFormat(CondFormat{Ranges: []Rect{rect("A1:A9")}, Op: RuleGreater, Args: [2]string{"2"}, Style: RuleStyle{Bold: true}}); err != nil {194 t.Fatal(err)195 }196 if err := s.AddValidation(Validation{Ranges: []Rect{rect("A1:A9")}, Kind: ValidNumber, Op: RuleLessEq, Args: [2]string{"3"}, Reject: true}); err != nil {197 t.Fatal(err)198 }199 wantShown(t, s, map[string]string{"A4": "4"})200 if l := s.Look(at("A3")); !l.Styled || l.Invalid {201 t.Errorf("A3's look = %+v, want styled and valid", l)202 }203 if l := s.Look(at("A4")); !l.Styled || !l.Invalid {204 t.Errorf("A4's look = %+v, want styled and invalid", l)205 }206}207208// When two arrays need the same cells, the one anchored first, row by209// row, gets them, whichever was typed first, even where the other's210// cells are blank; reopening the file agrees.211func TestOverlappingArraysFirstAnchorWins(t *testing.T) {212 cases := []struct {213 first, second [2]string214 win, lose string215 }{216 {[2]string{"C9", "=SORT(E4:G8)"}, [2]string{"D8", "=SEQUENCE(2)"}, "D8", "C9"},217 {[2]string{"D8", "=SEQUENCE(2)"}, [2]string{"C9", "=SORT(E4:G8)"}, "D8", "C9"},218 {[2]string{"E5", "=SEQUENCE(3,2)"}, [2]string{"F4", "=SEQUENCE(3)"}, "F4", "E5"},219 {[2]string{"F4", "=SEQUENCE(3)"}, [2]string{"E5", "=SEQUENCE(3,2)"}, "F4", "E5"},220 }221 for _, c := range cases {222 s := sheetOf(t, map[string]string{"E4": "5", "G8": "1"})223 s.Set(at(c.first[0]), c.first[1])224 s.Set(at(c.second[0]), c.second[1])225 for name, sh := range map[string]*Sheet{"typed": s, "reopened": roundTrip(t, s)} {226 if v := sh.Value(at(c.lose)); v != ErrRef {227 t.Errorf("%v then %v, %s: %s = %v", c.first, c.second, name, c.lose, v)228 }229 if _, ok := sh.SpillArea(at(c.win)); !ok {230 t.Errorf("%v then %v, %s: %s doesn't spill", c.first, c.second, name, c.win)231 }232 }233 // Taking the second back frees the first to spill.234 s.Undo()235 if _, ok := s.SpillArea(at(c.first[0])); !ok {236 t.Errorf("%v then %v, undone: %s doesn't spill", c.first, c.second, c.first[0])237 }238 }239}240241// A formula reading an array blocked by that formula's own cell reads242// the anchor's #REF!, settling rather than going back and forth as the243// formula's own array comes and goes.244func TestReadingABlockedArray(t *testing.T) {245 s := New()246 s.Set(at("A18"), "=SORT(A16:C16)")247 s.Set(at("A16"), "=SEQUENCE(3)")248 for name, sh := range map[string]*Sheet{"typed": s, "reopened": roundTrip(t, s)} {249 for _, a := range []string{"A16", "A18"} {250 if v := sh.Value(at(a)); v != ErrRef {251 t.Errorf("%s: %s = %v", name, a, v)252 }253 }254 if sh.Book().Circular {255 t.Errorf("%s: arrays kept moving", name)256 }257 }258}259260// An array whose formula reads its own cells through another formula261// is a circular dependency too: #REF! for it and what reads it, typed,262// recalculated or opened, without going back and forth. Here the263// formula reads the array's anchor too (VLOOKUP's range holds C19), a264// cycle of formulas, found whether or not VLOOKUP reads that far.265func TestSpillIntoItsInputThroughAFormula(t *testing.T) {266 s := New()267 s.Set(at("F11"), "=VLOOKUP(F14,C17:D19,1,FALSE)")268 s.Set(at("C19"), "=SORT(E9:F11)")269 s.Set(at("H15"), "=MAX(E:C)")270 check := func(name string, sh *Sheet) {271 t.Helper()272 for _, a := range []string{"C19", "H15"} {273 if v := sh.Value(at(a)); v != ErrRef {274 t.Errorf("%s: %s = %v", name, a, v)275 }276 }277 if got := sh.ExplainError(at("C19")); !strings.Contains(got, "Circular") {278 t.Errorf("%s: %q", name, got)279 }280 if sh.Filled(at("C20")) {281 t.Errorf("%s: the array spilled", name)282 }283 }284 check("typed", s)285 check("reopened", roundTrip(t, s))286 s.RecalcAll()287 check("recalculated", s)288}289290// An array that would spill into the cells its formula reads is a291// circular dependency: #REF!, whether typed or opened, rather than a292// result that depends on what was computed first.293func TestSpillIntoItsInput(t *testing.T) {294 s := sheetOf(t, map[string]string{"C8": "3", "A9": "=SORT(B8:D9)"})295 for name, sh := range map[string]*Sheet{"typed": s, "reopened": roundTrip(t, s)} {296 if v := sh.Value(at("A9")); v != ErrRef {297 t.Errorf("%s: A9 = %v", name, v)298 }299 if sh.Filled(at("B9")) || sh.Filled(at("B10")) {300 t.Errorf("%s: spilled into its input", name)301 }302 if got := sh.ExplainError(at("A9")); !strings.Contains(got, "Circular dependency") {303 t.Errorf("%s: %q", name, got)304 }305 }306}307308// An array isn't left blocked by a cycle through the cells of an array309// that stopped spilling in the same recalculation: deleting a row moves310// the array at A3 to A2, and puts at A3 a formula reading B2 whose311// array spilled over C3, which G1 reads, until it's computed again.312func TestNoCycleThroughAnArrayGone(t *testing.T) {313 s := sheetOf(t, map[string]string{"A3": "=D11:F11", "A4": "=B3", "G2": "=C4", "F11": "=G2"})314 if sp := s.spills[at("A3")]; sp == nil || sp.why != "" {315 t.Fatalf("A3's array: %+v", sp)316 }317 s.DeleteRows(0, 1)318 if sp := s.spills[at("A2")]; sp == nil || sp.why != "" {319 t.Fatalf("A2's array after deleting a row: %+v", sp)320 }321}