1package sheet23import "testing"45// Cycles through arrays settle the same typed in any order, recalculated6// or opened (spillblock.go).78// wantSettled types cells in order, then checks what the cells of want9// show, as typed, once everything is recalculated, and opened.10func wantSettled(t *testing.T, name string, order []string, cells, want map[string]string) {11 t.Helper()12 s := New()13 for _, a := range order {14 s.Set(at(a), cells[a])15 }16 check := func(how string, sh *Sheet) {17 t.Helper()18 for a, v := range want {19 if got := sh.Value(at(a)).String(); got != v {20 t.Errorf("%s, typed %v, %s: %s = %q, want %q", name, order, how, a, got, v)21 }22 }23 }24 check("typed", s)25 reopened := roundTrip(t, s)26 check("reopened", reopened)27 s.RecalcAll()28 check("recalculated", s)29}3031// Two arrays reading each other's cells, one's size following what it32// reads: blocked, each shows #REF!, which the other reads, and its33// smaller array would free the first. They end blocked together.34func TestArraysGoingAroundACycle(t *testing.T) {35 cells := map[string]string{"C3": "=SORT(C13:E16)", "D13": "-5", "B14": "=SORT(C2:E5)"}36 want := map[string]string{"C3": "#REF!", "B14": "#REF!", "D3": ""}37 for _, order := range [][]string{{"C3", "D13", "B14"}, {"B14", "D13", "C3"}, {"D13", "C3", "B14"}} {38 wantSettled(t, "around", order, cells, want)39 }40}4142// A formula typed last that reads an array's cells closes a cycle43// through it and another array, which is computed again for it: both44// are blocked, as when the file opens.45func TestCycleClosedThroughAnotherArray(t *testing.T) {46 cells := map[string]string{"D9": "=SORT(G15:H19)", "H19": "=SORT(D13:F16)", "D16": "=ABS($A$9)+1", "G16": `=COUNTIF(H20,">2")`}47 wantSettled(t, "closed", []string{"D9", "H19", "D16", "G16"}, cells, map[string]string{"D9": "#REF!", "H19": "#REF!"})48}4950// An array blocked by a cycle whose formula gives way to a value frees51// the arrays the cycle went through.52func TestCycleGoneWithAnArray(t *testing.T) {53 s := sheetOf(t, map[string]string{"A9": "=SORT(B9:D12)"})54 s.Set(at("C9"), "=SORT(A11:C14)")55 s.Set(at("C11"), `=COUNTIF(A10:A13,">2")`)56 if v := s.Value(at("C9")); v != ErrRef {57 t.Fatalf("C9 in a cycle = %v", v)58 }59 s.Set(at("A9"), "-5")60 if sp := s.spills[at("C9")]; sp == nil || sp.why != "" {61 t.Errorf("C9 once A9 is a value: %+v", sp)62 }63 if sp := roundTrip(t, s).spills[at("C9")]; sp == nil || sp.why != "" {64 t.Errorf("C9 opened: %+v", sp)65 }66}6768// An array spilling into the inputs of another, blocked by a value but69// only as wide as it is for the first's spill, and reading what the70// other needs: counted as spilling, the blocked one closes the cycle,71// so both are blocked whichever was typed first, as when opened.72func TestCycleThroughABlockedArray(t *testing.T) {73 cells := map[string]string{"C9": "1.5", "C13": "=SORT(D19:E20)", "D18": "=SORT(C9:E13)", "E19": "=SEQUENCE(3)"}74 want := map[string]string{"C13": "#REF!", "D18": "#REF!", "D13": "", "E20": "2"}75 for _, order := range [][]string{{"C9", "E19", "C13", "D18"}, {"C9", "D18", "C13", "E19"}, {"D18", "C13", "C9", "E19"}} {76 wantSettled(t, "blocked", order, cells, want)77 }78}7980// A formula typed last closing a cycle through an array blocked by a81// value has the arrays checked again, as when the file opens.82func TestCycleClosedThroughABlockedArray(t *testing.T) {83 cells := map[string]string{"D9": "=SORT(G15:H19)", "E9": "=COUNTIF(A9:A12,\">2\")", "B16": "=ABS($A$9)+1", "H19": "=SORT(B13:D16)", "H16": "=COUNTIF(H20,\">2\")"}84 wantSettled(t, "closed", []string{"D9", "E9", "B16", "H19", "H16"}, cells, map[string]string{"D9": "#REF!", "H19": "#REF!"})85}8687// A blocked array that goes, its formula computing one value, frees the88// arrays whose cycle went through the cells it needed (spill.walked):89// once Total is defined, B7 reads its own #REF! through G9.90func TestCycleGoneWithABlockedArray(t *testing.T) {91 cells := map[string]string{92 "H9": "=VLOOKUP(G8,G14:G15,1,FALSE)", "E7": "=SORT(F10:G14)", "G9": "=SUM(Total)*2", "B7": "=SORT(G7:H11)",93 "H5": "=SORT(C4:E8)", "B9": "=VLOOKUP(A13,C14:D18,1,FALSE)", "G18": "=ABS(C14)+1",94 }95 s := New()96 for _, a := range []string{"H9", "E7", "G9", "B7", "H5", "B9"} {97 s.Set(at(a), cells[a])98 }99 s.Book().DefineName("Total", s, NewRect(Addr{}, Addr{Col: 1, Row: 9}))100 s.Set(at("G18"), cells["G18"])101 wantShown(t, s, map[string]string{"B7": "#REF!", "H5": ""})102}