1package sheet23import (4 "maps"5 "strings"6 "testing"7)89// Cycles evaluation doesn't walk are cycles all the same, #REF! in every10// cell, typed in any order or opened (cyclefind.go).11func TestCyclesFoundAsWritten(t *testing.T) {12 for _, tc := range []cycleCase{13 {"an IF branch not taken", map[string]string{"A1": "=IF(FALSE,B1,1)", "B1": "=A1+1"}, []string{"A1", "B1"}, nil},14 {"a range read cut short by an error", map[string]string{"A1": "=SUM(B1:B3)", "B1": "=1/0", "B3": "=A1"}, []string{"A1", "B3"}, map[string]string{"B1": "#DIV/0!"}},15 {"INDEX reading one cell", map[string]string{"A1": "=INDEX(A2:A5,1)", "A2": "7", "A4": "=A1"}, []string{"A1", "A4"}, nil},16 {"itself, behind a branch", map[string]string{"C3": "=IF(TRUE,1,C3)", "D3": "=IFERROR(C3,5)"}, []string{"C3"}, map[string]string{"D3": "5"}},17 {"another sheet", map[string]string{"A1": "=IF(FALSE,Data!A1,1)", "Data!A1": "=Sheet1!A1"}, []string{"A1", "Data!A1"}, nil},18 {"a name", map[string]string{"A1": "=IF(FALSE,SUM(Near),1)", "B2": "=A1"}, []string{"A1", "B2"}, nil},19 } {20 orders := [][]string{nil, nil}21 for a := range tc.cells {22 orders[0] = append(orders[0], a)23 orders[1] = append([]string{a}, orders[1]...)24 }25 for _, order := range orders {26 tc.check(t, order)27 }28 }29}3031// cycleCase is cells to type, those of the cycle they make, and what32// others show.33type cycleCase struct {34 name string35 cells map[string]string36 cycle []string37 others map[string]string38}3940// check types the cells in order, on a workbook with a sheet Data and a41// name Near for A1:B2, and checks what they show, and show once opened.42func (tc cycleCase) check(t *testing.T, order []string) {43 t.Helper()44 s := New()45 w := s.Book()46 w.AddSheet("Data", 1)47 w.DefineName("Near", s, rect("A1:B2"))48 for _, a := range order {49 setIn(t, w, a, tc.cells[a])50 }51 reopened := roundTrip(t, s).Book()52 if !reopened.Circular {53 t.Errorf("%s: not circular", tc.name)54 }55 want := map[string]string{}56 maps.Copy(want, tc.others)57 for _, a := range tc.cycle {58 want[a] = "#REF!"59 }60 for _, sh := range []*Workbook{w, reopened} {61 for a, v := range want {62 if got := show(t, sh, qualified(a)); got != v {63 t.Errorf("%s, typed %v: %s = %s, want %s", tc.name, order, a, got, v)64 }65 }66 }67}6869// setIn types in into the cell a names, on Sheet1 unless it names a70// sheet.71func setIn(t *testing.T, w *Workbook, a, in string) {72 t.Helper()73 s := w.Sheet(0)74 if sheet, cell, ok := strings.Cut(a, "!"); ok {75 s, a = w.Lookup(sheet), cell76 }77 if err := s.Set(at(a), in); err != nil {78 t.Fatal(err)79 }80}8182func qualified(a string) string {83 if strings.Contains(a, "!") {84 return a85 }86 return "Sheet1!" + a87}8889// Formulas reading each other without a cycle aren't taken for one:90// a table's formulas reading their own row, running totals over a91// column of formulas, and a total above the column it adds up.92func TestNoCycleWithout(t *testing.T) {93 s := salesSheet(t)94 s.Set(at("D1"), "Total")95 w := s.Book()96 w.ResizeTable("Sales", rect("A1:D4"))97 for row := 2; row <= 4; row++ {98 s.Set(Addr{Col: 3, Row: row - 1}, "=Sales[@Units]*Sales[@Amount]")99 }100 for row := 1; row <= 30; row++ {101 s.Set(Addr{Col: 6, Row: row}, "=F"+itoa(row+1)+"*2")102 s.Set(Addr{Col: 7, Row: row}, "=SUM(G$2:G"+itoa(row+1)+")")103 }104 s.Set(at("G1"), "=SUM(G2:G31)")105 for _, sh := range []*Workbook{w, roundTrip(t, s).Book()} {106 if sh.Circular {107 t.Error("circular")108 }109 if got := show(t, sh, "Sheet1!D4"); got != "150" {110 t.Errorf("D4 = %s", got)111 }112 if got := show(t, sh, "Sheet1!H31"); got != "0" {113 t.Errorf("H31 = %s", got)114 }115 }116}