1package oracle23import (4 "strings"5 "testing"67 "github.com/xuri/excelize/v2"8)910// arrayFormulas compute over arrays; each is compared by the value its11// cell shows (an array's first). Those excelize can't compute have the12// value Sheets gives and the reason, and 012 is checked against that.13var arrayFormulas = []struct{ formula, sheets, why string }{14 {`=UNIQUE(G1:G4)`, "", ""},15 {`=TRANSPOSE(C1:D2)`, "", ""},16 {`=INDEX(TRANSPOSE(C1:D2), 2, 1)`, "", ""},17 {`=TEXTJOIN(",", TRUE, UNIQUE(D1:D5))`, "", ""},18 {`=SUM({1,2,3})`, "", ""},19 {`=SUMPRODUCT((B1:B5="north")*C1:C5)`, "9", "excelize reads a range in an expression as its first cell"},20 {`=SUMPRODUCT(C1:C3*G1:G3)`, "11", "excelize reads a range in an expression as its first cell"},21 {`=SUMPRODUCT(--(C1:C7>3))`, "4", "excelize doesn't negate a comparison of ranges"},22 {`=ROWS(UNIQUE(D1:D5))`, "4", "excelize's ROWS takes only a reference"},23 {`=INDEX({10,20,30}, 2)`, "20", "excelize's INDEX takes only a reference"},24 {`=SUM(C1:C3*2)`, "12", "excelize doesn't compute an expression over ranges inside SUM"},25 {`=MAX(LEN(D1:D5))`, "6", "excelize doesn't apply LEN to each cell of a range inside MAX"},26 {`=SEQUENCE(3, 2, 10)`, "10", "excelize has no SEQUENCE"},27 {`=FILTER(D1:D5, C1:C5>3)`, "date", "excelize has no FILTER"},28 {`=SORT(I1:I3)`, "10", "excelize has no SORT"},29 {`=SORTN(C1:C7, 1, 0, 1, FALSE)`, "7", "excelize has no SORTN, which only Sheets has"},30 {`=FLATTEN(C1:D2)`, "1", "excelize has no FLATTEN, which only Sheets has"},31 {`=CHOOSECOLS(C1:E2, 2)`, "Apple", "excelize has no CHOOSECOLS"},32 {`=CHOOSEROWS(C1:D3, -1)`, "3", "excelize has no CHOOSEROWS"},33 {`=ARRAYFORMULA(SUM(C1:C3*G1:G3))`, "11", "excelize has no ARRAYFORMULA, which only Sheets has"},34 {`=LET(x, C3, x*x)`, "9", "excelize has no LET"},35 {`=LAMBDA(v, v+1)(C2)`, "3", "excelize has no LAMBDA"},36 {`=REDUCE(0, C1:C5, LAMBDA(a, v, a+v))`, "15", "excelize has no REDUCE"},37 {`=MAP(C1:C3, LAMBDA(v, v*v))`, "1", "excelize has no MAP"},38 {`=REGEXEXTRACT("order 42", "[0-9]+")`, "42", "excelize has no REGEXEXTRACT"},39 {`=REGEXREPLACE("a-b", "-", "+")`, "a+b", "excelize has no REGEXREPLACE"},40 {`=REGEXMATCH("Cherry", "^Ch")`, "TRUE", "excelize has no REGEXMATCH, which only Sheets has"},41 {`=SPLIT("x,y", ",")`, "x", "excelize has no SPLIT, which only Sheets has"},42}4344func TestArraysAgainstExcelize(t *testing.T) {45 s, x := build(t)46 const cell = "Z1"47 for _, tc := range arrayFormulas {48 if err := s.Set(addr(cell), tc.formula); err != nil {49 t.Errorf("012 rejects %s: %v", tc.formula, err)50 continue51 }52 ours := s.Value(addr(cell))53 s.Set(addr(cell), "")54 if tc.why != "" {55 t.Logf("skip %s: %s", tc.formula, tc.why)56 if !agree(ours, tc.sheets) {57 t.Errorf("%s: 012 %s, Sheets %s", tc.formula, show(ours), tc.sheets)58 }59 continue60 }61 if err := x.SetCellFormula("Sheet1", cell, toExcel(tc.formula)); err != nil {62 t.Fatal(err)63 }64 theirs, err := x.CalcCellValue("Sheet1", cell, excelize.Options{RawCellValue: true})65 if err != nil && !strings.HasPrefix(theirs, "#") {66 theirs = err.Error()67 }68 if !agree(ours, theirs) {69 t.Errorf("%s: 012 %s, excelize %q", tc.formula, show(ours), theirs)70 }71 }72}