1package sheet23import (4 "strings"5 "testing"6)78// A range where one value is wanted reads as the cell in the formula's9// row (a column) or column (a row), as in Sheets and Excel; functions10// that take ranges still read all of them, and a range that is a cell's11// whole formula spills, as in Sheets.12func TestImplicitIntersection(t *testing.T) {13 for _, decimal := range []bool{false, true} {14 s := sheetOf(t, map[string]string{15 "A1": "Rent", "B1": "Amount",16 "B2": "1400", "B3": "1450", "B4": "1500",17 "B6": "10", "C6": "20", "D6": "30",18 "F2": "=B2:B4*2", "F3": "=Rent*2", "F4": "=Rent*2", "F5": "=Rent*2",19 "G3": "=+B2:B4", "G4": "=ABS(-Rent)", "G5": "=B:B+1",20 "H3": "=SUM(Rent)", "H4": "=SUMPRODUCT(Rent)", "H2": "=MATCH(1450,Rent,0)",21 "H5": "=SUM(Rent*2)", "H6": "=SUMPRODUCT(Rent*2)",22 "C7": "=B6:D6+1", "E7": "=B6:D6", "A8": "=B2:C4",23 "J3": "=IF(TRUE,Rent)", "K3": "=Rent&\"!\"", "L3": "=COUNTIF(Rent,\">1420\")",24 })25 s.SetDecimal(decimal)26 if err := s.DefineName("Rent", rect("B2:B4")); err != nil {27 t.Fatal(err)28 }29 for a, want := range map[string]string{30 "F2": "2800", "F3": "2900", "F4": "3000", "F5": "#VALUE!",31 "G3": "1450", "G4": "1500", "G5": "1",32 "H3": "4350", "H4": "4350", "H2": "2",33 "H5": "8700", "H6": "8700", // array arguments: computed over the whole range34 "C7": "21", "E7": "10", "G7": "30", "A8": "1400", "A10": "1500", // E7 and A8 spill35 "J3": "1450", "K3": "1450!", "L3": "2",36 } {37 if got := s.Value(at(a)).String(); got != want {38 t.Errorf("decimal %v: %s = %s, want %s", decimal, a, got, want)39 }40 }41 s.Set(at("B3"), "1475") // the intersected cell is a dependency42 if got := s.Value(at("F3")).String(); got != "2950" {43 t.Errorf("decimal %v: F3 after B3 changed = %s", decimal, got)44 }45 }46}4748// Ranges on other sheets intersect with the formula's own row or column.49func TestImplicitIntersectionOtherSheet(t *testing.T) {50 s := New()51 w := s.Book()52 data, err := w.AddSheet("Q3 plan", 1)53 if err != nil {54 t.Fatal(err)55 }56 for a, in := range map[string]string{"C2": "5", "C3": "6", "C4": "7", "B9": "1", "C9": "2", "D9": "3"} {57 if err := data.Set(at(a), in); err != nil {58 t.Fatal(err)59 }60 }61 if err := w.DefineName("Plan", data, rect("C2:C4")); err != nil {62 t.Fatal(err)63 }64 for a, in := range map[string]string{65 "A3": "='Q3 plan'!C2:C4*10", "A4": "=Plan+1", "A5": "=Plan",66 "C1": "=+'Q3 plan'!B9:D9", "E1": "=+'Q3 plan'!B9:D9",67 } {68 if err := s.Set(at(a), in); err != nil {69 t.Fatal(err)70 }71 }72 for a, want := range map[string]string{"A3": "60", "A4": "8", "A5": "5", "A7": "7", "C1": "2", "E1": "#VALUE!"} { // A5 spills73 if got := s.Value(at(a)).String(); got != want {74 t.Errorf("%s = %s, want %s", a, got, want)75 }76 }77 data.Set(at("C3"), "9")78 if got := s.Value(at("A3")).String(); got != "90" {79 t.Errorf("A3 after 'Q3 plan'!C3 changed = %s", got)80 }81}8283// An error read through an intersected range is traced to its cell.84func TestImplicitIntersectionExplain(t *testing.T) {85 s := sheetOf(t, map[string]string{"B2": "1", "B3": "=1/0", "B4": "3", "D3": "=B2:B4*2"})86 if got := s.ExplainError(at("D3")); !strings.HasPrefix(got, "From B3") {87 t.Errorf("D3 explained as %q", got)88 }89}