1package sheet23import (4 "strings"5 "testing"6)78// arrCase is a formula and the grid it shows from its cell, rows joined9// with | between cells: one row for a single value.10type arrCase struct {11 in string12 want []string13}1415// checkArrays enters each formula in K10, below and right of the16// fixture's data, and compares what it spills.17func checkArrays(t *testing.T, s *Sheet, tests []arrCase) {18 t.Helper()19 anchor := at("K10")20 for _, tt := range tests {21 if err := s.Set(anchor, tt.in); err != nil {22 t.Errorf("Set(%q): %v", tt.in, err)23 continue24 }25 area, ok := s.SpillArea(anchor)26 if !ok {27 area = Rect{From: anchor, To: anchor}28 }29 var got []string30 for r := area.From.Row; r <= area.To.Row; r++ {31 var row []string32 for c := area.From.Col; c <= area.To.Col; c++ {33 row = append(row, s.Value(Addr{Col: c, Row: r}).String())34 }35 got = append(got, strings.Join(row, "|"))36 }37 if strings.Join(got, "\n") != strings.Join(tt.want, "\n") {38 t.Errorf("%s spills\n %s\nwant\n %s", tt.in, strings.Join(got, "\n "), strings.Join(tt.want, "\n "))39 }40 }41 s.Set(anchor, "")42}4344func TestArrayOperators(t *testing.T) {45 checkArrays(t, fixture(t), []arrCase{46 {"={1,2;3,4}", []string{"1|2", "3|4"}},47 {"={1,2}*10", []string{"10|20"}},48 {"={1;2}+{10,20}", []string{"11|21", "12|22"}},49 {"={1,2,3}+{1,2}", []string{"2|4|#N/A"}},50 {`={"a","b"}&"!"`, []string{"a!|b!"}},51 {"=-{1,2}", []string{"-1|-2"}},52 {"={1,2}/0", []string{"#DIV/0!|#DIV/0!"}},53 {"={C1:C2,G1:G2}", []string{"1|1", "2|2"}},54 {"={C1:C2;G1}", []string{"1", "2", "1"}},55 {"={C1:C2,G1}", []string{"#VALUE!"}},56 {"={1,2;3}", []string{"#VALUE!"}},57 {"=SUM(C1:C3*2)", []string{"12"}},58 {"=SUMPRODUCT(C1:C3*G1:G3)", []string{"11"}},59 {"=SUMPRODUCT((B1:B5=\"north\")*C1:C5)", []string{"9"}},60 {"=SUM({1,2,3})", []string{"6"}},61 {"=COUNT(C:C*1)", []string{"1048576"}},62 {"=MAX(LEN(D1:D5))", []string{"6"}},63 {"=C1:C3*2", []string{"#VALUE!"}}, // row 10: no intersection64 {"=ARRAYFORMULA(C1:C3*2)", []string{"2", "4", "6"}},65 {"=ARRAYFORMULA(LEN(D1:D3))", []string{"5", "6", "6"}},66 {`=ARRAYFORMULA(IF(C1:C4>2, "big", "small"))`, []string{"small", "small", "big", "big"}},67 {"=ARRAYFORMULA(VLOOKUP(G1:G3, C1:D5, 2, FALSE))", []string{"Apple", "Banana", "Banana"}},68 {`=ARRAYFORMULA(SUMIF(B1:B5, {"north";"east"}, C1:C5))`, []string{"9", "4"}},69 {"=ARRAYFORMULA(A1:A3)", []string{"10", "20", "30"}},70 {"=ARRAYFORMULA(ROUND(E1:E3))", []string{"2", "3", "4"}},71 {"=LEN(SEQUENCE(3)*100)", []string{"3"}},72 {"=INDEX(C1:D3, 0, 2)", []string{"Apple", "Banana", "Cherry"}},73 {"=INDEX(C1:D3, 2, 0)", []string{"2|Banana"}},74 {"=INDEX({1,2;3,4}, 2, 1)", []string{"3"}},75 {"=ROWS(SEQUENCE(4))", []string{"4"}},76 {`=IFERROR(FILTER(C1:C5, C1:C5>9), "none")`, []string{"none"}},77 {"=IF(TRUE, SEQUENCE(2), 0)", []string{"1", "2"}},78 {"=MATCH(3, SEQUENCE(5), 0)", []string{"3"}},79 {"=VLOOKUP(2, {1,\"a\";2,\"b\"}, 2, FALSE)", []string{"b"}},80 })81}8283func TestArrayFunctions(t *testing.T) {84 checkArrays(t, fixture(t), []arrCase{85 {"=SEQUENCE(3)", []string{"1", "2", "3"}},86 {"=SEQUENCE(2, 3)", []string{"1|2|3", "4|5|6"}},87 {"=SEQUENCE(2, 2, 10, -5)", []string{"10|5", "0|-5"}},88 {"=SEQUENCE(0)", []string{"#NUM!"}}, // as Sheets89 {"=FILTER(D1:D5, C1:C5>3)", []string{"date", "Apple"}},90 {`=FILTER(C1:D5, B1:B5="north", C1:C5>1)`, []string{"3|Cherry", "5|Apple"}},91 {"=FILTER(C1:E1, {TRUE,FALSE,TRUE})", []string{"1|1.5"}},92 {"=FILTER(C1:C5, C1:C5>10)", []string{"#N/A"}},93 {"=FILTER(C1:C5, C1:C4>1)", []string{"#VALUE!"}},94 {"=FILTER(C:C, C:C>5)", []string{"6", "7"}},95 {"=SORT(I1:I3)", []string{"10", "20", "30"}},96 {"=SORT(C1:D3, 2, FALSE)", []string{"3|Cherry", "2|Banana", "1|Apple"}},97 {"=SORT(D1:D5, 1, TRUE)", []string{"Apple", "Apple", "Banana", "Cherry", "date"}},98 {"=SORT(C1:C3, G1:G3, FALSE)", []string{"2", "3", "1"}},99 {"=SORT(A1:A7)", []string{"-5", "10", "20", "30", "apple", "TRUE", ""}},100 {"=SORTN(C1:C7, 2, 0, 1, FALSE)", []string{"7", "6"}},101 {"=SORTN(G1:G4, 2, 1, 1, TRUE)", []string{"1", "2", "2"}},102 {"=SORTN(G1:G4, 2, 2)", []string{"1", "2"}},103 {"=SORTN(G1:G4, 2, 3)", []string{"1", "2", "2"}},104 {"=SORTN(C1:C3)", []string{"1"}},105 {"=UNIQUE(D1:D5)", []string{"Apple", "Banana", "Cherry", "date"}},106 {"=UNIQUE(G1:G4)", []string{"1", "2", "3"}},107 {"=UNIQUE(G1:G4, FALSE, TRUE)", []string{"1", "3"}},108 {`=UNIQUE({"a","a","A"}, TRUE)`, []string{"a|A"}},109 {"=UNIQUE(B:B)", []string{"north", "south", "east", "", "Ab*d"}},110 {"=TRANSPOSE(C1:D2)", []string{"1|2", "Apple|Banana"}},111 {"=FLATTEN(C1:D2)", []string{"1", "Apple", "2", "Banana"}},112 {"=FLATTEN(C1:C2, G1:G2)", []string{"1", "2", "1", "2"}},113 {"=CHOOSECOLS(C1:E2, 3, -3)", []string{"1.5|1", "2.5|2"}},114 {"=CHOOSEROWS(C1:D3, -1, 1)", []string{"3|Cherry", "1|Apple"}},115 {"=CHOOSEROWS(C1:D3, 4)", []string{"#VALUE!"}},116 {"=COUNTA(UNIQUE(B1:B5))", []string{"3"}},117 {`=TEXTJOIN(",", TRUE, FILTER(D1:D5, C1:C5<3))`, []string{"Apple,Banana"}},118 {`=TEXTJOIN("-", TRUE, SEQUENCE(3))`, []string{"1-2-3"}},119 })120}