amadan

oficina / sheets Public

Clone 
git clone https://amadan.net/oficina/sheets

Plain git — no account needed to clone.

Download

Download this file

1package calc
2
3import "testing"
4
5// evalNum evaluates src and requires the result to be KindNum. Like
6// mustParse (eval_test.go), it never puts src into a failure message —
7// formula text is cell content, and this codebase's discipline is that
8// cell content never appears in a string a person might read off a log
9// or a test report.
10func evalNum(t *testing.T, src string, env Env) float64 {
11 t.Helper()
12 v := Eval(mustParse(t, src), env)
13 if v.Kind != KindNum {
14 t.Fatalf("result Kind = %v, want KindNum (value %+v)", v.Kind, v)
15 }
16 return v.Num
17}
18
19func TestSum(t *testing.T) {
20 if got := evalNum(t, "SUM(1,2,3)", emptyEnv{}); got != 6 {
21 t.Errorf("SUM(1,2,3) = %v, want 6", got)
22 }
23}
24
25func TestSumIgnoresBlankAndText(t *testing.T) {
26 env := mapEnv{{1, 1}: {Kind: KindNum, Num: 5}, {1, 2}: {Kind: KindStr, Str: "x"}}
27 // A1=5 (num), B1="x" (str, ignored), C1 blank (never set, ignored).
28 if got := evalNum(t, "SUM(A1,B1,C1)", env); got != 5 {
29 t.Errorf("SUM(A1,B1,C1) = %v, want 5", got)
30 }
31}
32
33func TestSumPropagatesError(t *testing.T) {
34 env := mapEnv{{1, 1}: {Kind: KindErr, Err: ErrRef}}
35 v := Eval(mustParse(t, "SUM(A1,1)"), env)
36 if v.Kind != KindErr || v.Err != ErrRef {
37 t.Fatalf("SUM(A1,1) = %v, want #REF!", v)
38 }
39}
40
41func TestAverage(t *testing.T) {
42 if got := evalNum(t, "AVERAGE(2,4,6)", emptyEnv{}); got != 4 {
43 t.Errorf("AVERAGE(2,4,6) = %v, want 4", got)
44 }
45}
46
47// A literal non-numeric string passed directly is #VALUE! (see
48// TestSumLiteralNonNumericStringIsValueError), not silently ignored — so
49// this test reaches "zero numeric values" the way Excel actually allows:
50// text read FROM references, which AVERAGE skips rather than errors on.
51func TestAverageOfNoNumbersIsDiv0(t *testing.T) {
52 env := mapEnv{{1, 1}: {Kind: KindStr, Str: "a"}, {1, 2}: {Kind: KindStr, Str: "b"}}
53 v := Eval(mustParse(t, "AVERAGE(A1,B1)"), env)
54 if v.Kind != KindErr || v.Err != ErrDiv0 {
55 t.Fatalf("AVERAGE(A1,B1) = %v, want #DIV/0!", v)
56 }
57}
58
59// COUNT counts only numbers; COUNTA counts everything non-blank; neither
60// counts a blank cell — the exact distinction the brief calls out.
61func TestCountVsCounta(t *testing.T) {
62 env := mapEnv{
63 {1, 1}: {Kind: KindNum, Num: 1},
64 {1, 2}: {Kind: KindStr, Str: "x"},
65 {1, 3}: {Kind: KindBool, Bool: true},
66 // D1 (col 4) left unset: blank.
67 }
68 if got := evalNum(t, "COUNT(A1,B1,C1,D1)", env); got != 1 {
69 t.Errorf("COUNT = %v, want 1", got)
70 }
71 if got := evalNum(t, "COUNTA(A1,B1,C1,D1)", env); got != 3 {
72 t.Errorf("COUNTA = %v, want 3", got)
73 }
74}
75
76func TestMinMax(t *testing.T) {
77 if got := evalNum(t, "MIN(4,2,9,1)", emptyEnv{}); got != 1 {
78 t.Errorf("MIN = %v, want 1", got)
79 }
80 if got := evalNum(t, "MAX(4,2,9,1)", emptyEnv{}); got != 9 {
81 t.Errorf("MAX = %v, want 9", got)
82 }
83}
84
85func TestRoundHalfAwayFromZero(t *testing.T) {
86 if got := evalNum(t, "ROUND(2.5,0)", emptyEnv{}); got != 3 {
87 t.Errorf("ROUND(2.5,0) = %v, want 3", got)
88 }
89 if got := evalNum(t, "ROUND(-2.5,0)", emptyEnv{}); got != -3 {
90 t.Errorf("ROUND(-2.5,0) = %v, want -3", got)
91 }
92}
93
94func TestRoundUpDown(t *testing.T) {
95 if got := evalNum(t, "ROUNDUP(3.001,0)", emptyEnv{}); got != 4 {
96 t.Errorf("ROUNDUP(3.001,0) = %v, want 4", got)
97 }
98 if got := evalNum(t, "ROUNDDOWN(3.99,0)", emptyEnv{}); got != 3 {
99 t.Errorf("ROUNDDOWN(3.99,0) = %v, want 3", got)
100 }
101}
102
103func TestAbsSqrtPower(t *testing.T) {
104 if got := evalNum(t, "ABS(-7)", emptyEnv{}); got != 7 {
105 t.Errorf("ABS(-7) = %v, want 7", got)
106 }
107 if got := evalNum(t, "SQRT(16)", emptyEnv{}); got != 4 {
108 t.Errorf("SQRT(16) = %v, want 4", got)
109 }
110 if got := evalNum(t, "POWER(2,10)", emptyEnv{}); got != 1024 {
111 t.Errorf("POWER(2,10) = %v, want 1024", got)
112 }
113}
114
115func TestSqrtOfNegativeIsNum(t *testing.T) {
116 v := Eval(mustParse(t, "SQRT(-1)"), emptyEnv{})
117 if v.Kind != KindErr || v.Err != ErrNum {
118 t.Fatalf("SQRT(-1) = %v, want #NUM!", v)
119 }
120}
121
122// MOD takes the sign of the divisor, matching Excel — not Go's %.
123func TestModSignFollowsDivisor(t *testing.T) {
124 if got := evalNum(t, "MOD(-10,3)", emptyEnv{}); got != 2 {
125 t.Errorf("MOD(-10,3) = %v, want 2", got)
126 }
127 if got := evalNum(t, "MOD(10,-3)", emptyEnv{}); got != -2 {
128 t.Errorf("MOD(10,-3) = %v, want -2", got)
129 }
130}
131
132func TestModByZeroIsDiv0(t *testing.T) {
133 v := Eval(mustParse(t, "MOD(10,0)"), emptyEnv{})
134 if v.Kind != KindErr || v.Err != ErrDiv0 {
135 t.Fatalf("MOD(10,0) = %v, want #DIV/0!", v)
136 }
137}
138
139func TestMathWrongArgCountIsValueError(t *testing.T) {
140 for _, formula := range []string{"ROUND(1)", "ABS()", "ABS(1,2)", "POWER(1)", "MOD(1)"} {
141 v := Eval(mustParse(t, formula), emptyEnv{})
142 if v.Kind != KindErr || v.Err != ErrValue {
143 t.Errorf("%s = %v, want #VALUE!", formula, v)
144 }
145 }
146}
147
148// POWER guards its result, not just its inputs: a negative base with a
149// non-integer exponent makes math.Pow return NaN even though -8 and
150// 1.0/3 are each individually fine — this is a cube root written as a
151// fractional power, and Excel returns #NUM!, not a NaN masquerading as
152// a number.
153func TestPowerNaNResultIsNum(t *testing.T) {
154 v := Eval(mustParse(t, "POWER(-8,1/3)"), emptyEnv{})
155 if v.Kind != KindErr || v.Err != ErrNum {
156 t.Fatalf("POWER(-8,1/3) = %v, want #NUM!", v)
157 }
158}
159
160// An overflowing result is also #NUM!, not +Inf.
161func TestPowerOverflowIsNum(t *testing.T) {
162 v := Eval(mustParse(t, "POWER(10,400)"), emptyEnv{})
163 if v.Kind != KindErr || v.Err != ErrNum {
164 t.Fatalf("POWER(10,400) = %v, want #NUM!", v)
165 }
166}
167
168// The literal-vs-reference distinction (taggedValue, fn_math.go):
169// SUM/AVERAGE/COUNT/MIN/MAX all treat a boolean or a numeric-looking
170// string typed DIRECTLY as an argument differently from the same kind of
171// value read FROM a cell reference. These tests pin that distinction
172// directly, closing the gap fn_math.go's first version flagged but did
173// not fix (caught by code review against a real-Excel-behavior corpus
174// entry for COUNT).
175
176func TestSumLiteralBooleanCoerces(t *testing.T) {
177 if got := evalNum(t, "SUM(TRUE,1)", emptyEnv{}); got != 2 {
178 t.Errorf("SUM(TRUE,1) = %v, want 2 (literal TRUE counts as 1)", got)
179 }
180}
181
182func TestSumReferenceBooleanIgnored(t *testing.T) {
183 env := mapEnv{{1, 1}: {Kind: KindBool, Bool: true}}
184 if got := evalNum(t, "SUM(A1,1)", env); got != 1 {
185 t.Errorf("SUM(A1,1) with A1=TRUE = %v, want 1 (reference TRUE ignored)", got)
186 }
187}
188
189func TestSumLiteralNonNumericStringIsValueError(t *testing.T) {
190 v := Eval(mustParse(t, `SUM(1,"abc")`), emptyEnv{})
191 if v.Kind != KindErr || v.Err != ErrValue {
192 t.Fatalf(`SUM(1,"abc") = %v, want #VALUE! (a literal you meant as a number, not silently skipped)`, v)
193 }
194}
195
196func TestMinMaxLiteralBooleanCoerces(t *testing.T) {
197 if got := evalNum(t, "MIN(TRUE,5)", emptyEnv{}); got != 1 {
198 t.Errorf("MIN(TRUE,5) = %v, want 1", got)
199 }
200 if got := evalNum(t, "MAX(FALSE,5)", emptyEnv{}); got != 5 {
201 t.Errorf("MAX(FALSE,5) = %v, want 5", got)
202 }
203}
204
205// The corpus row this exact case pins (testdata/corpus/formulas.json):
206// a logical value typed directly IS counted, but "a" isn't numeric so
207// it isn't — 3, not 4 and not 2.
208func TestCountLiteralBooleanAndNumericString(t *testing.T) {
209 if got := evalNum(t, `COUNT(1,2,"a",TRUE)`, emptyEnv{}); got != 3 {
210 t.Errorf(`COUNT(1,2,"a",TRUE) = %v, want 3`, got)
211 }
212}
213
214func TestCountReferenceBooleanNotCounted(t *testing.T) {
215 env := mapEnv{{1, 1}: {Kind: KindBool, Bool: true}}
216 if got := evalNum(t, "COUNT(A1)", env); got != 0 {
217 t.Errorf("COUNT(A1) with A1=TRUE = %v, want 0 (reference logical not counted)", got)
218 }
219}
220