1package sheet23import (4 "bytes"5 "errors"6 "strings"7 "testing"8 "unicode"910 "src.rest/cat/012/internal/formula"11)1213func at(s string) Addr {14 a, ok := ParseAddr(s)15 if !ok {16 panic("bad address " + s)17 }18 return a19}2021func txt(s string) Value { return Value{Kind: Text, Str: s} }2223func TestFormulas(t *testing.T) {24 s := New()25 for addr, in := range map[string]string{26 "A1": "10", "A2": "20", "A3": "30", "B1": "Name", "B2": "'42", "B3": "TRUE",27 } {28 if err := s.Set(at(addr), in); err != nil {29 t.Fatal(err)30 }31 }32 tests := []struct {33 in string34 want Value35 }{36 {"=A1+A2*2", num(50)},37 {"=(A1+A2)*2", num(60)},38 {"=-2^2", num(4)}, // negation first, as in Sheets39 {"=2^-1", num(0.5)},40 {"=-A1^2", num(100)},41 {"=A1/4", num(2.5)},42 {"+A1*2", num(20)}, // Sheets accepts a leading +43 {"=SUM(A1:A3)", num(60)},44 {"=sum(A1:A3, 5)", num(65)},45 {"=@SUM(A1..A3)", num(60)}, // 1-2-3 function syntax still parses46 {"=AVERAGE(A1:B3)", num(20)},47 {"=AVG(A1:A3)", num(20)},48 {"=COUNT(A1:B3)", num(3)}, // numbers only49 {"=COUNTA(A1:B3)", num(6)}, // anything non-empty50 {"=MIN(A1:A3)", num(10)},51 {"=MAX(A1:A3)", num(30)},52 {"=ROUND(PI(), 2)", num(3.14)},53 {"=ROUND(2.5)", num(3)},54 {"=MOD(-7, 3)", num(2)},55 {"=INT(-1.5)", num(-2)},56 {`=IF(A1>5, "big", "small")`, txt("big")},57 {"=IF(A1>50, 1)", boolean(false)},58 {"=A1>5", boolean(true)},59 {"=AND(A1>5, A2<10)", boolean(false)},60 {"=OR(B3, FALSE)", boolean(true)},61 {"=NOT(A1=10)", boolean(false)},62 {"=B2*2", num(84)}, // numeric text coerces63 {"=B1+1", ErrValue}, // other text doesn't64 {"=SUM(B1)", num(0)}, // text in a referenced cell is ignored65 {`=SUM("a")`, ErrValue}, // but not as a direct argument66 {"=B3+1", num(2)}, // TRUE is 167 {`=B1&" X"`, txt("Name X")},68 {`="say ""hi"""`, txt(`say "hi"`)},69 {"=A1&B3", txt("10TRUE")},70 {"=A1/0", ErrDiv0},71 {"=SQRT(-1)", ErrNum},72 {"=NA()", ErrNA},73 {"=NA()+1", ErrNA},74 {"=IFERROR(A1/0, 0)", num(0)},75 {"=Z99", Value{}},76 {"=10%", num(0.1)},77 {"=A1*50%", num(5)},78 {"=profit", ErrName},79 {"=1.5E3", num(1500)},80 }81 for _, tt := range tests {82 if err := s.Set(at("C1"), tt.in); err != nil {83 t.Errorf("Set(%q): %v", tt.in, err)84 continue85 }86 if got := s.Value(at("C1")); got != tt.want {87 t.Errorf("%q = %+v, want %+v", tt.in, got, tt.want)88 }89 }90}9192func TestEntryKinds(t *testing.T) {93 tests := []struct {94 in string95 want Value96 formula bool97 }{98 {"42", num(42), false},99 {"-3.5", num(-3.5), false},100 {"$1,200.50", num(1200.5), false},101 {"12%", num(0.12), false},102 {"1e3", num(1000), false},103 {"true", boolean(true), false},104 {"Rent", txt("Rent"), false},105 {"'42", txt("42"), false},106 {"1,23", txt("1,23"), false}, // bad grouping is text107 {"-hello", Value{Kind: Error, Str: "#NAME?"}, true},108 {"A1+1", txt("A1+1"), false}, // no = means text109 {"=1+1", num(2), true},110 {"-A1", num(0), true},111 }112 for _, tt := range tests {113 s := New()114 if err := s.Set(at("A2"), tt.in); err != nil {115 t.Errorf("Set(%q): %v", tt.in, err)116 continue117 }118 c := s.Cell(at("A2"))119 if c.Value != tt.want || c.IsFormula() != tt.formula || c.Input != tt.in {120 t.Errorf("%q = %+v formula=%v input=%q", tt.in, c.Value, c.IsFormula(), c.Input)121 }122 }123}124125func TestParseErrors(t *testing.T) {126 tests := []struct{ in, msg string }{127 {"=A1+", "Formula is incomplete"},128 {"=SUM(A1", "Expected , or ) in SUM"},129 {"=NOPE(1)", "Unknown function NOPE"},130 {"=(1", "Missing )"},131 {"=A1:", "Expected a cell after :"},132 {"=1 2", "Unexpected 2"},133 {`="abc`, "Missing closing quote"},134 {"=ROUND()", "Wrong number of arguments to ROUND(value, [places])"},135 {"=PI(1)", "Wrong number of arguments to PI()"},136 {"=" + strings.Repeat("(", 2000) + "1" + strings.Repeat(")", 2000), "Formula is nested too deeply (more than 1024 levels)"},137 }138 for _, tt := range tests {139 var pe *ParseError140 err := New().Set(at("A1"), tt.in)141 if !errors.As(err, &pe) || pe.Msg != tt.msg {142 t.Errorf("Set(%q) error = %v, want %q", tt.in, err, tt.msg)143 }144 }145}146147func TestRecalcPropagates(t *testing.T) {148 s := New()149 s.Set(at("A1"), "1")150 s.Set(at("A2"), "=A1*2")151 s.Set(at("A3"), "=A2+A1")152 s.Set(at("B1"), "=SUM(A1:A10)")153154 s.Set(at("A1"), "5")155 if got := s.Value(at("A3")).Num; got != 15 {156 t.Errorf("A3 = %v, want 15", got)157 }158 if got := s.Value(at("B1")).Num; got != 30 {159 t.Errorf("B1 = %v, want 30", got)160 }161162 // Filling a blank cell inside a range updates the range user.163 s.Set(at("A9"), "100")164 if got := s.Value(at("B1")).Num; got != 130 {165 t.Errorf("B1 = %v, want 130", got)166 }167168 s.EraseRange(NewRect(at("A1"), at("A1")))169 if got := s.Value(at("A3")).Num; got != 0 {170 t.Errorf("A3 after erase = %v, want 0", got)171 }172173 // Replacing a formula drops its old dependency edges.174 s.Set(at("A2"), "7")175 if len(s.dependents[at("A1")]) != 1 { // only A3 remains176 t.Errorf("A1 dependents = %v", s.dependents[at("A1")])177 }178}179180func TestCircular(t *testing.T) {181 s := New()182 s.Set(at("A1"), "=B1+1")183 s.Set(at("B1"), "=A1+1")184 if !s.Book().Circular || s.Value(at("A1")) != ErrRef || s.Value(at("B1")) != ErrRef {185 t.Errorf("circular: %v %+v %+v", s.Book().Circular, s.Value(at("A1")), s.Value(at("B1")))186 }187 s.Set(at("B1"), "3")188 if s.Book().Circular || s.Value(at("A1")).Num != 4 {189 t.Errorf("after break: %v %+v", s.Book().Circular, s.Value(at("A1")))190 }191 s.Set(at("C1"), "=SUM(C1:C2)")192 if !s.Book().Circular {193 t.Error("self-referencing range not flagged")194 }195}196197func TestFormatValue(t *testing.T) {198 tests := []struct {199 v Value200 width int201 want string202 }{203 {num(42), 10, " 42 "},204 {num(-3.5), 10, " -3.5 "},205 {num(1.0 / 3), 10, "0.3333333 "},206 {num(9.9999), 4, " 10 "},207 {num(123456789012), 10, "1.235E+11 "},208 {num(0.000000001), 10, "1.000E-09 "},209 {num(0), 3, " 0 "},210 {num(123456), 3, "## "},211 {boolean(true), 10, " TRUE "},212 {ErrDiv0, 10, " #DIV/0! "},213 }214 for _, tt := range tests {215 if got := FormatValue(tt.v, tt.width); got != tt.want {216 t.Errorf("FormatValue(%v, %d) = %q, want %q", tt.v, tt.width, got, tt.want)217 }218 }219}220221func TestParseNumber(t *testing.T) {222 for in, want := range map[string]float64{223 "0": 0, "12": 12, "-12.5": -12.5, "+3": 3, ".5": 0.5, "1,234": 1234,224 "1,234,567.89": 1234567.89, "$99": 99, "-$5": -5, "50%": 0.5, "2.5e2": 250,225 } {226 if got, ok := ParseNumber(in); !ok || got != want {227 t.Errorf("ParseNumber(%q) = %v, %v", in, got, ok)228 }229 }230 for _, in := range []string{"", "abc", "1,2", "12,34", "$", "%", "1.2.3", "--1", "1 2"} {231 if _, ok := ParseNumber(in); ok {232 t.Errorf("ParseNumber(%q) accepted", in)233 }234 }235}236237func FuzzParse(f *testing.F) {238 for _, s := range []string{"=A1*2", "=SUM(A1:B3)", "=IF(AND(A1>2,B1),1,NA())", `="a"&"b"`, "=1.5E-3^2", "$1,200", "12%", "@SUM(A1..B3)", "=$A$1+A$2*$B3", "=SUM($A1:B$3)", "=#REF!+1", "=A$$1",239 "=SUM(Sales[Amount])", "=Sales[[#Headers],[#Data],[A]:[B]]", "=T[@[Unit Price]]*T[@Qty]", "=T['#a']]", "=T[[#This Row],[x]]", "=T[#All]", "=T[[A]"} {240 f.Add(s)241 }242 // Around the nesting cap: parentheses, calls and prefix operators.243 for _, n := range []int{formula.MaxDepth - 2, formula.MaxDepth + 1, 4 * formula.MaxDepth} {244 f.Add("=" + strings.Repeat("(", n) + "1" + strings.Repeat(")", n))245 f.Add("=" + strings.Repeat("ABS(", n) + "A1" + strings.Repeat(")", n))246 f.Add("=" + strings.Repeat("-+", n/2) + "1")247 f.Add("=" + strings.Repeat("(", n))248 }249 f.Fuzz(func(t *testing.T, in string) {250 s := New()251 printable := strings.ContainsFunc(in, func(r rune) bool { return !unicode.IsControl(r) })252 if err := s.Set(Addr{}, in); err == nil && printable && s.Cell(Addr{}) == nil {253 t.Fatalf("Set(%q) succeeded but cell is blank", in)254 }255 })256}257258func TestFileRoundTrip(t *testing.T) {259 s := New()260 s.Set(at("A1"), "Total")261 s.Set(at("B1"), "=SUM(B2:B3)")262 s.Set(at("B2"), "2")263 s.Set(at("B3"), "=B2*10")264 s.SetColWidth(0, 14)265266 var buf bytes.Buffer267 if err := s.Write(&buf); err != nil {268 t.Fatal(err)269 }270 got, err := Read(&buf)271 if err != nil {272 t.Fatal(err)273 }274 if v := got.Value(at("B1")).Num; v != 22 {275 t.Errorf("B1 = %v, want 22", v)276 }277 if got.Cell(at("A1")).Input != "Total" || got.ColWidth(0) != 14 {278 t.Errorf("A1 = %q, width %d", got.Cell(at("A1")).Input, got.ColWidth(0))279 }280}281282func TestStripsControlChars(t *testing.T) {283 s := New()284 s.Set(at("A1"), "evil\x1b]52;c;aGk=\x07label")285 if got := s.Cell(at("A1")).Input; got != "evil]52;c;aGk=label" {286 t.Errorf("Input = %q", got)287 }288}