1package oracle23import (4 "testing"56 "github.com/xuri/excelize/v2"78 "src.rest/cat/012/internal/sheet"9)1011// intersections are formulas that use a range where one value is wanted,12// each in the cell it is written in: the range reads as the cell in the13// formula's row (a column) or column (a row), or #VALUE! when there is14// none. Rent is the named range Sheet1!B2:B4; Other!B2:B4 and B9:D9 hold15// other numbers. excelize reads the first cell of any range instead, so16// it agrees only where that is the intersection, or where a function17// takes the range; the other cases are skipped against excelize and give18// what Excel and Sheets do (excel), which 012 is checked against. A19// range that is a cell's whole formula spills in Sheets and Excel 365,20// so a unary + makes it one value where the intersection is tested.21var intersections = []struct{ cell, formula, excel string }{22 // The intersection is the first cell, or a function takes the range.23 {"F2", "=B2:B4+1", ""}, {"F2", "=Rent*2", ""}, {"B8", "=B6:D6*3", ""},24 {"F2", "=Other!B2:B4*10", ""}, {"B1", "=Other!B9:D9", ""},25 {"H3", "=SUM(Rent)", ""}, {"H4", "=MATCH(1500,Rent,0)", ""}, {"H2", `=COUNTIF(Rent,">1420")`, ""},2627 // excelize reads the first cell where Excel and Sheets intersect.28 {"F3", "=Rent*2", "2900"}, {"F4", "=Rent*2", "3000"}, {"F5", "=Rent*2", "#VALUE!"},29 {"G3", "=+B2:B4", "1450"}, {"G4", "=ABS(-Rent)", "1500"}, {"G3", `=Rent&"x"`, "1450x"},30 {"C8", "=B6:D6*3", "60"}, {"E8", "=B6:D6*3", "#VALUE!"}, {"A9", "=+B2:C4", "#VALUE!"},31 {"F3", "=Other!B2:B4*10", "20"}, {"C1", "=+Other!B9:D9", "5"}, {"F1", "=+Other!B2:B4", "#VALUE!"},32}3334func TestImplicitIntersection(t *testing.T) {35 for _, decimal := range []bool{false, true} {36 s, x := buildIntersect(t)37 s.SetDecimal(decimal)38 for _, tc := range intersections {39 key := tc.cell + " " + tc.formula40 if err := s.Set(addr(tc.cell), tc.formula); err != nil {41 t.Fatalf("012 rejects %s: %v", key, err)42 }43 ours := s.Value(addr(tc.cell))44 s.Set(addr(tc.cell), "")45 if tc.excel != "" {46 t.Logf("skip %s: excelize reads the range's first cell; Excel and Sheets intersect", key)47 if !agree(ours, tc.excel) {48 t.Errorf("decimal %v: %s: 012 %s, Excel and Sheets %s", decimal, key, show(ours), tc.excel)49 }50 continue51 }52 if theirs := calcAt(t, x, tc.cell, tc.formula); !agree(ours, theirs) {53 t.Errorf("decimal %v: %s: 012 %s, excelize %q", decimal, key, show(ours), theirs)54 }55 }56 }57}5859// buildIntersect sets up the cells and the name Rent in both engines.60func buildIntersect(t *testing.T) (*sheet.Sheet, *excelize.File) {61 t.Helper()62 s := sheet.New()63 o, err := s.Book().AddSheet("Other", 1)64 if err != nil {65 t.Fatal(err)66 }67 x := excelize.NewFile()68 if _, err := x.NewSheet("Other"); err != nil {69 t.Fatal(err)70 }71 put := func(on *sheet.Sheet, name string, cells map[string]string) {72 for a, in := range cells {73 if err := on.Set(addr(a), in); err != nil {74 t.Fatal(err)75 }76 if err := x.SetCellValue(name, a, on.Value(addr(a)).Num); err != nil {77 t.Fatal(err)78 }79 }80 }81 put(s, "Sheet1", map[string]string{82 "B2": "1400", "B3": "1450", "B4": "1500", "B6": "10", "C6": "20", "D6": "30", "C2": "7", "C3": "8", "C4": "9",83 })84 put(o, "Other", map[string]string{"B2": "1", "B3": "2", "B4": "3", "B9": "4", "C9": "5", "D9": "6"})85 if err := s.DefineName("Rent", sheet.Rect{From: addr("B2"), To: addr("B4")}); err != nil {86 t.Fatal(err)87 }88 if err := x.SetDefinedName(&excelize.DefinedName{Name: "Rent", RefersTo: "Sheet1!$B$2:$B$4"}); err != nil {89 t.Fatal(err)90 }91 return s, x92}9394// calcAt is excelize's result for the formula f in cell on Sheet1, which95// is emptied again afterwards.96func calcAt(t *testing.T, x *excelize.File, cell, f string) string {97 t.Helper()98 if err := x.SetCellFormula("Sheet1", cell, toExcel(f)); err != nil {99 t.Fatal(err)100 }101 theirs, err := x.CalcCellValue("Sheet1", cell, excelize.Options{RawCellValue: true})102 if err != nil && (theirs == "" || theirs[0] != '#') {103 theirs = err.Error()104 }105 if err := x.SetCellValue("Sheet1", cell, nil); err != nil {106 t.Fatal(err)107 }108 return theirs109}