1package formula23import (4 "reflect"5 "strings"6 "testing"7)89// testFunc is a function for the parser, with its signature only.10type testFunc Signature1112func (f testFunc) Signature() Signature { return Signature(f) }1314// testFuncs is a small function table: what the parser needs to know.15func testFuncs(name string) (Func, bool) {16 sigs := map[string]Signature{17 "SUM": {Name: "SUM", Args: "value1, [value2, ...]", Min: 1, Max: -1},18 "IF": {Name: "IF", Args: "condition, value_if_true, [value_if_false]", Min: 2, Max: 3},19 "PI": {Name: "PI", Max: 0},20 "ROUND": {Name: "ROUND", Args: "value, [places]", Min: 1, Max: 2},21 "SUMIFS": {Name: "SUMIFS", Args: "sum_range, criteria_range1, criterion1, ...", Min: 3, Max: -1, Step: 2},22 }23 sig, ok := sigs[name]24 return testFunc(sig), ok25}2627func at(s string) Addr {28 a, ok := ParseAddr(s)29 if !ok {30 panic("bad address " + s)31 }32 return a33}3435func TestAddr(t *testing.T) {36 tests := []struct {37 in string38 want Addr39 ok bool40 }{41 {"A1", Addr{0, 0}, true},42 {"z10", Addr{25, 9}, true},43 {"AA1", Addr{26, 0}, true},44 {"IV8192", Addr{255, 8191}, true},45 {"ZZ1", Addr{701, 0}, true},46 {"AAA1", Addr{702, 0}, true},47 {"XFD1048576", Addr{16383, 1048575}, true},48 {"$B$2", Addr{1, 1}, true},49 {"XFE1", Addr{}, false},50 {"A0", Addr{}, false},51 {"A1048577", Addr{}, false},52 {"ABCD1", Addr{}, false},53 {"A+1", Addr{}, false},54 {"1A", Addr{}, false},55 }56 for _, tt := range tests {57 got, ok := ParseAddr(tt.in)58 if ok != tt.ok || (ok && got != tt.want) {59 t.Errorf("ParseAddr(%q) = %v, %v; want %v, %v", tt.in, got, ok, tt.want, tt.ok)60 }61 }62 for c := range MaxCols {63 if got, ok := ParseCol(ColName(c)); !ok || got != c {64 t.Fatalf("ParseCol(ColName(%d)) = %d, %v", c, got, ok)65 }66 }67}6869func TestParseRange(t *testing.T) {70 for _, in := range []string{"B3..A1", "a1:b3", "$A$1:$B$3"} {71 r, ok := ParseRange(in)72 if !ok || r.String() != "A1:B3" {73 t.Errorf("ParseRange(%q) = %v, %v", in, r, ok)74 }75 }76 for in, want := range map[string]string{"B:A": "A:B", "$3:2": "2:3", "XFD:XFD": "XFD:XFD", "A1:A1048576": "A:A"} {77 if r, ok := ParseRange(in); !ok || r.String() != want {78 t.Errorf("ParseRange(%q) = %v, %v; want %s", in, r, ok, want)79 }80 }81 for _, in := range []string{"A:1", "0:1", "1:1048577", "XFE:XFE", "A:"} {82 if r, ok := ParseRange(in); ok {83 t.Errorf("ParseRange(%q) = %v, want no range", in, r)84 }85 }86}8788func TestParseAbsoluteRefs(t *testing.T) {89 tests := []struct {90 in string91 want Node92 }{93 {"=A1", Ref{at("A1"), 0, ""}},94 {"=$A1", Ref{at("A1"), AbsCol, ""}},95 {"=A$1", Ref{at("A1"), AbsRow, ""}},96 {"=$a$1", Ref{at("A1"), AbsCol | AbsRow, ""}},97 {"=$B$3:A1", Range{NewRect(at("A1"), at("B3")), [2]Abs{0, AbsCol | AbsRow}, ""}},98 // Each marker stays with its column or row when corners swap.99 {"=$B1:A$2", Range{NewRect(at("A1"), at("B2")), [2]Abs{0, AbsCol | AbsRow}, ""}},100 {"=A1..$B2", Range{NewRect(at("A1"), at("B2")), [2]Abs{0, AbsCol}, ""}},101 {"=A$$1", Name{"A$$1"}},102 {"=A1$", Name{"A1$"}},103 {"=#REF!+1", Binary{Op: "+", L: RefErr{}, R: Num{1}}},104 }105 for _, tt := range tests {106 got, err := Parse(tt.in, testFuncs)107 if err != nil || !reflect.DeepEqual(got, tt.want) {108 t.Errorf("Parse(%q) = %#v, %v; want %#v", tt.in, got, err, tt.want)109 }110 }111}112113func TestPrintFormula(t *testing.T) {114 tests := []struct{ in, want string }{115 {"=a1+$b$2*C$3", "=A1+$B$2*C$3"},116 {"=@SUM(A1..B3; 2)", "=SUM(A1:B3,2)"},117 {"=(1+2)*3", "=(1+2)*3"},118 {"=1-(2-3)", "=1-(2-3)"},119 {"=(1-2)-3", "=1-2-3"},120 {"=2^3^2", "=2^3^2"},121 {"=2^(3^2)", "=2^(3^2)"},122 {"=-2^2", "=-2^2"},123 {"=(-2)^2", "=-2^2"}, // negation binds tighter than ^124 {"=-(2^2)", "=-(2^2)"},125 {"=-(A1+1)", "=-(A1+1)"},126 {"=50%", "=50%"},127 {"=(A1+1)%", "=(A1+1)%"},128 {"=#NOT#(A1=1) #AND# B1", "=#NOT#A1=1#AND#B1"},129 {"=1+(#NOT#A1)", "=1+(#NOT#A1)"},130 {`="say ""hi"""&A1`, `="say ""hi"""&A1`},131 {"=1.5E3+1e-9+2E21", "=1500+1E-09+2E+21"},132 {"=@PI", "=PI()"},133 {"=IF(A1>=2,TRUE,profit)", "=IF(A1>=2,TRUE,profit)"},134 {"=SUM(#REF!)", "=SUM(#REF!)"},135 {"+A1", "=+A1"},136 {"=SUM(a:c)", "=SUM(A:C)"},137 {"=SUM($C:a)", "=SUM(A:$C)"},138 {"=SUM(A1:A1048576)", "=SUM(A:A)"},139 {"=SUM(2:5)+SUM($3:$3)", "=SUM(2:5)+SUM($3:$3)"},140 {"=SUM(Data!B:B)+SUM('Q 1'!1:2)", "=SUM(Data!B:B)+SUM('Q 1'!1:2)"},141 {"=SUM(Data!XFD:Data!XFD)", "=SUM(Data!XFD:XFD)"},142 {"=ZZZ1+AAA1:XFD9", "=ZZZ1+AAA1:XFD9"},143 }144 for _, tt := range tests {145 n, err := Parse(tt.in, testFuncs)146 if err != nil {147 t.Fatalf("Parse(%q): %v", tt.in, err)148 }149 if got := Text(n); got != tt.want {150 t.Errorf("print(%q) = %q, want %q", tt.in, got, tt.want)151 }152 }153}154155func TestShiftRefs(t *testing.T) {156 tests := []struct {157 in string158 dc, dr int159 want string160 }{161 {"=A1+B2", 1, 2, "=B3+C4"},162 {"=$A$1+A1", 1, 1, "=$A$1+B2"},163 {"=$A1+A$1", 2, 3, "=$A4+C$1"},164 {"=SUM(A1:B2)", 0, 5, "=SUM(A6:B7)"},165 {"=SUM($A$1:B2)", 1, 1, "=SUM($A$1:C3)"},166 {"=A2", 0, -1, "=A1"},167 {"=A2", 0, -2, "=#REF!"},168 {"=SUM(A1:B2)+1", -1, 0, "=SUM(#REF!)+1"},169 {"=$A2", -1, 0, "=$A2"},170 {"=XFD1", 1, 0, "=#REF!"},171 {"=SUM(A:A)", 2, 7, "=SUM(C:C)"},172 {"=SUM($A:B)", 1, 7, "=SUM($A:C)"},173 {"=SUM(3:4)", 5, 2, "=SUM(5:6)"},174 {"=A1048576", 0, 1, "=#REF!"},175 {"=ZZ1", 1, 0, "=AAA1"},176 // A mixed range can flip when one corner is anchored.177 {"=SUM($C1:D1)", -2, 0, "=SUM(B1:$C1)"},178 }179 for _, tt := range tests {180 n, _ := Parse(tt.in, testFuncs)181 out, _ := Rewrite(n, Shift(tt.dc, tt.dr))182 if got := Text(out); got != tt.want {183 t.Errorf("shift(%q, %d, %d) = %q, want %q", tt.in, tt.dc, tt.dr, got, tt.want)184 }185 }186}187188func TestSpanInterval(t *testing.T) {189 tests := []struct {190 sp Span191 lo, hi int192 wLo, wHi int193 wOK bool194 name string195 checkPoint bool196 }{197 {Span{2, 2, 100}, 0, 1, 0, 1, true, "insert below", false},198 {Span{2, 2, 100}, 1, 3, 1, 5, true, "insert inside grows", false},199 {Span{2, 2, 100}, 2, 3, 4, 5, true, "insert at start shifts", false},200 {Span{2, 2, 100}, 50, 99, 52, 99, true, "insert clamps at the edge", false},201 {Span{2, -2, 100}, 1, 4, 1, 2, true, "delete inside shrinks", false},202 {Span{2, -2, 100}, 2, 4, 2, 2, true, "delete top part", false},203 {Span{2, -2, 100}, 1, 2, 1, 1, true, "delete bottom part", false},204 {Span{2, -2, 100}, 3, 6, 2, 4, true, "delete overlapping start", false},205 {Span{2, -2, 100}, 5, 6, 3, 4, true, "delete above shifts", false},206 {Span{2, -2, 100}, 2, 3, 0, 0, false, "delete all of it", false},207 }208 for _, tt := range tests {209 lo, hi, ok := tt.sp.Interval(tt.lo, tt.hi)210 if ok != tt.wOK || (ok && (lo != tt.wLo || hi != tt.wHi)) {211 t.Errorf("%s: interval(%d, %d) = %d, %d, %v", tt.name, tt.lo, tt.hi, lo, hi, ok)212 }213 }214}215216func TestParseSheetRefs(t *testing.T) {217 tests := []struct{ in, out, err string }{218 {in: "=Sheet2!A1", out: "=Sheet2!A1"},219 {in: "=sheet2!$a$1+1", out: "=sheet2!$A$1+1"},220 {in: "='My Sheet'!B2:C9", out: "='My Sheet'!B2:C9"},221 {in: "=SUM('Bob''s'!A1:A3)", out: "=SUM('Bob''s'!A1:A3)"},222 {in: "=Data!B3:Data!A1", out: "=Data!A1:B3"},223 {in: "='2026'!A1", out: "='2026'!A1"},224 {in: "='A1'!A1", out: "='A1'!A1"},225 {in: "=Q3.plan!A1..B2", out: "=Q3.plan!A1:B2"},226 {in: "=Sheet2!Sales", err: "Expected a cell after Sheet2!"},227 {in: "=Sheet2!A1:Other!B2", err: "A range can't span sheets"},228 {in: "='Open!A1", err: "Expected ! after a quoted sheet name"},229 {in: "='x'A1", err: "Expected ! after a quoted sheet name"},230 }231 for _, tt := range tests {232 n, err := Parse(tt.in, testFuncs)233 switch {234 case tt.err != "":235 if err == nil || err.Error() != tt.err {236 t.Errorf("%s: error %v, want %q", tt.in, err, tt.err)237 }238 case err != nil:239 t.Errorf("%s: %v", tt.in, err)240 case Text(n) != tt.out:241 t.Errorf("%s prints as %s, want %s", tt.in, Text(n), tt.out)242 }243 }244}245246func TestQuoteSheet(t *testing.T) {247 for name, want := range map[string]string{248 "Sheet1": "Sheet1", "Q3_plan": "Q3_plan", "My Sheet": "'My Sheet'", "Bob's": "'Bob''s'",249 "2026": "'2026'", "AB12": "'AB12'", "R1C1": "'R1C1'", "Café": "'Café'",250 } {251 if got := QuoteSheet(name); got != want {252 t.Errorf("QuoteSheet(%q) = %s, want %s", name, got, want)253 }254 }255}256257func TestSplitSheet(t *testing.T) {258 tests := []struct{ in, sheet, rest string }{259 {"A1", "", "A1"},260 {"Sheet2!A1:B3", "Sheet2", "A1:B3"},261 {"'Q3 plan'!B2", "Q3 plan", "B2"},262 {"'Bob''s'!C1", "Bob's", "C1"},263 {" Data!Z9 ", "Data", "Z9"},264 }265 for _, tt := range tests {266 if sheet, rest := SplitSheet(tt.in); sheet != tt.sheet || rest != tt.rest {267 t.Errorf("SplitSheet(%q) = %q, %q", tt.in, sheet, rest)268 }269 }270}271272// FuzzPrint checks that printing a parsed formula gives text that parses273// back to the same expression.274func FuzzPrint(f *testing.F) {275 for _, s := range []string{276 "=A1*2", "=$A$1+A$2-$B3", "=SUM($A1:B$3)", "=-2^-2%", "=1-(2-3)", "=#NOT#A1=1#OR#B1",277 "=2^(3^2)", `=IF(A1>2,"x""y",#REF!)`, "=1.5E-3^2", "=@SUM(A1..B3)", "=(1+2)%*-A1",278 // At and past the nesting cap.279 "=" + strings.Repeat("(", MaxDepth-1) + "1" + strings.Repeat(")", MaxDepth-1),280 "=" + strings.Repeat("-(", MaxDepth/2) + "1" + strings.Repeat(")", MaxDepth/2),281 "=" + strings.Repeat("(", MaxDepth+1) + "1" + strings.Repeat(")", MaxDepth+1),282 } {283 f.Add(s)284 }285 f.Fuzz(func(t *testing.T, in string) {286 n, err := Parse(in, testFuncs)287 if err != nil {288 return289 }290 text := Text(n)291 back, err := Parse(text, testFuncs)292 if err != nil {293 t.Fatalf("%q printed as %q, which doesn't parse: %v", in, text, err)294 }295 if !reflect.DeepEqual(n, back) {296 t.Fatalf("%q printed as %q parses differently:\n%#v\n%#v", in, text, n, back)297 }298 })299}300301// Nesting is capped at MaxDepth levels, with the error at the level302// past it; anything within the cap parses.303func TestParseDepth(t *testing.T) {304 nested := func(n int) string {305 return "=" + strings.Repeat("(", n) + "1" + strings.Repeat(")", n)306 }307 if _, err := Parse(nested(MaxDepth-1), testFuncs); err != nil {308 t.Errorf("%d parentheses: %v", MaxDepth-1, err)309 }310 for _, in := range []string{311 nested(MaxDepth),312 nested(100_000),313 "=" + strings.Repeat("-", 5000) + "1",314 "=" + strings.Repeat("SUM(", 2000) + "1" + strings.Repeat(")", 2000),315 "=" + strings.Repeat("(", 5000),316 } {317 _, err := Parse(in, testFuncs)318 pe, ok := err.(*ParseError)319 if !ok || !strings.HasPrefix(pe.Msg, "Formula is nested too deeply") {320 t.Errorf("Parse(%.20q...) error = %v", in, err)321 continue322 }323 if pe.Pos < MaxDepth || pe.Pos > len(in) {324 t.Errorf("Parse(%.20q...) error at %d", in, pe.Pos)325 }326 }327}328329func TestParseErrors(t *testing.T) {330 tests := []struct {331 in string332 pos int333 msg string334 }{335 {"=A1+", 4, "Formula is incomplete"},336 {"=SUM(A1", 7, "Expected , or ) in SUM"},337 {"=NOPE(1)", 1, "Unknown function NOPE"},338 {"=(1", 3, "Missing )"},339 {"=A1:", 4, "Expected a cell after :"},340 {"=1 2", 3, "Unexpected 2"},341 {`="abc`, 1, "Missing closing quote"},342 {"=ROUND()", 1, "Wrong number of arguments to ROUND(value, [places])"},343 {"=PI(1)", 1, "Wrong number of arguments to PI()"},344 {"=SUMIFS(A1:A3,B1:B3,1,C1:C3)", 1, "Wrong number of arguments to SUMIFS(sum_range, criteria_range1, criterion1, ...)"},345 {"=1#XOR#2", 2, "Unknown operator #XOR#"},346 {"=1#2", 2, "Unexpected #"},347 {"=@(1)", 1, "Missing function name after @"},348 {"=1!", 2, `Unexpected '!'`},349 }350 for _, tt := range tests {351 _, err := Parse(tt.in, testFuncs)352 pe, ok := err.(*ParseError)353 if !ok || pe.Msg != tt.msg || pe.Pos != tt.pos {354 t.Errorf("Parse(%q) error = %#v, want %q at %d", tt.in, err, tt.msg, tt.pos)355 }356 }357}358359func TestLexLiterals(t *testing.T) {360 tests := []struct {361 in string362 want Node363 }{364 {`="say ""hi"""`, Str{`say "hi"`}},365 {`=""""`, Str{`"`}},366 {`="plain"`, Str{"plain"}},367 {"=.5", Num{0.5}},368 {"=1.5e+3", Num{1500}},369 {"=#ref!", RefErr{}},370 {"=SUMIFS(A1:A3,B1:B3,1)", nil},371 }372 for _, tt := range tests {373 got, err := Parse(tt.in, testFuncs)374 if tt.want == nil {375 if err != nil {376 t.Errorf("Parse(%q): %v", tt.in, err)377 }378 continue379 }380 if err != nil || !reflect.DeepEqual(got, tt.want) {381 t.Errorf("Parse(%q) = %#v, %v; want %#v", tt.in, got, err, tt.want)382 }383 }384}