1package sheet23import (4 "bytes"5 "strconv"6 "strings"7 "testing"8)910// decimalSheet returns a sheet with inputs placed down column A from A111// and the formula in B1, in the given arithmetic.12func decimalSheet(t *testing.T, decimal bool, formula string, inputs ...string) *Sheet {13 t.Helper()14 s := New()15 for i, in := range inputs {16 if err := s.Set(Addr{Row: i}, in); err != nil {17 t.Fatal(err)18 }19 }20 if err := s.Set(at("B1"), formula); err != nil {21 t.Fatal(err)22 }23 s.SetDecimal(decimal)24 return s25}2627// The cases where binary and decimal arithmetic differ, and some where28// they must agree. Values are compared as General shows them with every29// digit (strconv's shortest form), so 0.30000000000000004 is visible.30func TestDecimalArithmetic(t *testing.T) {31 tenCents := strings.Split(strings.Repeat("0.1,", 10), ",")[:10]32 tests := []struct {33 name string34 formula string35 inputs []string36 binary, decim string37 }{38 {"sum of two", "=0.1+0.2", nil, "0.30000000000000004", "0.3"},39 {"comparison", "=0.1+0.2=0.3", nil, "TRUE", "TRUE"}, // at the 15 digits shown40 {"comparison with zero", "=0.1+0.2-0.3=0", nil, "FALSE", "TRUE"},41 {"residue", "=1.1-1-0.1", nil, "8.326672684688674E-17", "0"},42 {"product", "=0.07*3", nil, "0.21000000000000002", "0.21"},43 {"cents to whole", "=INT(A1*100)", []string{"$4.35"}, "434", "435"},44 {"currency sum", "=SUM(A1:A10)", tenCents, "0.9999999999999999", "1"},45 {"currency sum equals", "=SUM(A1:A10)-1=0", tenCents, "FALSE", "TRUE"},46 {"average", "=AVERAGE(0.1, 0.2)", nil, "0.15000000000000002", "0.15"},47 {"division", "=0.3/0.1", nil, "2.9999999999999996", "3"},48 {"postfix percent", "=1.1%", nil, "0.011000000000000001", "0.011"},49 {"round of a residue", "=ROUND(0.1+0.2-0.3, 20)", nil, "5.551E-17", "0"},50 // Rounding already works on the digits a cell shows, so these agree.51 {"round half up", "=ROUND(2.675, 2)", nil, "2.68", "2.68"},52 {"round 1.005", "=ROUND(1.005, 2)", nil, "1.01", "1.01"},53 {"round negative half", "=ROUND(-2.5)", nil, "-3", "-3"},54 {"round tens", "=ROUND(1234.5, -2)", nil, "1200", "1200"},55 {"roundup", "=ROUNDUP(2.301, 1)", nil, "2.4", "2.4"},56 {"rounddown", "=ROUNDDOWN(-2.39, 1)", nil, "-2.3", "-2.3"},57 {"trunc", "=TRUNC((0.1+0.7)*10)", nil, "8", "8"},58 // Typed percentages are exact decimals on entry in both modes.59 {"typed percent", "=A1", []string{"0.7%"}, "0.007", "0.007"},60 // Division rounds: decimal doesn't make thirds whole again.61 {"thirds", "=1/3*3", nil, "1", "0.9999999999999999"},62 {"one third", "=1/3", nil, "0.3333333333333333", "0.3333333333333333"},63 // Outside the boundary, and errors, nothing changes.64 {"power stays binary", "=1.1^2", nil, "1.2100000000000002", "1.2100000000000002"},65 {"sqrt stays binary", "=SQRT(2)", nil, "1.4142135623730951", "1.4142135623730951"},66 {"div by zero", "=1/0", nil, "#DIV/0!", "#DIV/0!"},67 {"overflow", "=1E308*10", nil, "#NUM!", "#NUM!"},68 {"text", `="a"+1`, nil, "#VALUE!", "#VALUE!"},69 {"sum of text", `=SUM("a")`, nil, "#VALUE!", "#VALUE!"},70 {"average of nothing", "=AVERAGE(A1:A2)", nil, "#DIV/0!", "#DIV/0!"},71 {"sum ignores text in ranges", "=SUM(A1:A3)", []string{"0.1", "x", "0.2"}, "0.30000000000000004", "0.3"},72 {"huge sum falls back", "=SUM(1E308, 1E308)", nil, "#NUM!", "#NUM!"},73 // PRODUCT, SUMIF, SUMIFS and SUMPRODUCT have decimal twins too.74 {"product", "=PRODUCT(A1:A2)", []string{"1.1", "1.1"}, "1.2100000000000002", "1.21"},75 {"product of direct", "=PRODUCT(0.07, 3)", nil, "0.21000000000000002", "0.21"},76 {"product skips text in ranges", "=PRODUCT(A1:A3)", []string{"1.1", "x", "1.1"}, "1.2100000000000002", "1.21"},77 {"product of nothing", "=PRODUCT(A1:A2)", nil, "0", "0"},78 {"product of text", `=PRODUCT("a")`, nil, "#VALUE!", "#VALUE!"},79 {"huge product falls back", "=PRODUCT(1E308, 10)", nil, "#NUM!", "#NUM!"},80 {"sumif", `=SUMIF(A1:A10, ">0")`, tenCents, "0.9999999999999999", "1"},81 {"sumif equals", `=SUMIF(A1:A10, ">0")-1=0`, tenCents, "FALSE", "TRUE"},82 {"sumif with sum range", `=SUMIF(A1:A3, "<>x", A1:A3)`, []string{"0.1", "x", "0.2"}, "0.30000000000000004", "0.3"},83 {"sumif error", `=SUMIF(A1:A2, ">0")`, []string{"1", "=1/0"}, "1", "1"},84 {"sumifs", `=SUMIFS(A1:A10, A1:A10, "0.1")`, tenCents, "0.9999999999999999", "1"},85 {"sumifs mismatched", `=SUMIFS(A1:A10, A1:A9, ">0")`, tenCents, "#VALUE!", "#VALUE!"},86 {"sumproduct", "=SUMPRODUCT(A1:A3, A1:A3)", []string{"0.1", "0.2", "0.3"}, "0.14", "0.14"},87 {"sumproduct of cents", "=SUMPRODUCT(A1:A2, A3:A4)", []string{"0.1", "0.2", "3", "3"}, "0.9000000000000001", "0.9"},88 {"sumproduct mismatched", "=SUMPRODUCT(A1:A2, A1:A3)", []string{"1", "2", "3"}, "#VALUE!", "#VALUE!"},89 {"sumproduct error", "=SUMPRODUCT(A1:A2)", []string{"1", "=1/0"}, "#DIV/0!", "#DIV/0!"},90 }91 show := func(v Value) string {92 if v.Kind == Number {93 return formatShortest(v.Num)94 }95 return v.String()96 }97 for _, tt := range tests {98 for _, dec := range []bool{false, true} {99 want := tt.binary100 if dec {101 want = tt.decim102 }103 s := decimalSheet(t, dec, tt.formula, tt.inputs...)104 if got := show(s.Value(at("B1"))); got != want {105 t.Errorf("%s: %s with decimal=%v = %s, want %s", tt.name, tt.formula, dec, got, want)106 }107 }108 }109}110111// Turning the setting on and off recalculates, is one undo step, and112// undo and redo recalculate too.113func TestDecimalUndo(t *testing.T) {114 s := decimalSheet(t, false, "=0.1+0.2-0.3=0")115 s.ClearHistory()116 check := func(when string, want bool) {117 t.Helper()118 if got := s.Value(at("B1")); got != boolean(want) || s.Decimal() != want {119 t.Errorf("%s: B1 = %v, Decimal() = %v; want %v", when, got, s.Decimal(), want)120 }121 }122 check("start", false)123 s.SetDecimal(true)124 check("on", true)125 s.SetDecimal(true) // no-op, no step126 if c, ok := s.Undo(); !ok || c.Label != "turn on decimal arithmetic" {127 t.Fatalf("undo = %+v, %v", c, ok)128 }129 check("undone", false)130 if s.CanUndo() {131 t.Error("the no-op made an undo step")132 }133 s.Redo()134 check("redone", true)135 s.SetDecimal(false)136 check("off", false)137}138139// The setting round-trips through the file without a version bump, and140// a file without it computes in binary.141func TestDecimalFile(t *testing.T) {142 s := decimalSheet(t, true, "=A1+A2", "0.1", "0.2")143 var b bytes.Buffer144 if err := s.Write(&b); err != nil {145 t.Fatal(err)146 }147 out := b.String()148 if !strings.Contains(out, `"version": 2,`) || !strings.Contains(out, `"arithmetic": "decimal",`) {149 t.Fatalf("file:\n%s", out)150 }151 r, err := Read(strings.NewReader(out))152 if err != nil {153 t.Fatal(err)154 }155 if !r.Decimal() || r.Value(at("B1")).Num != 0.3 {156 t.Errorf("read back: decimal %v, B1 %v", r.Decimal(), r.Value(at("B1")))157 }158 if r.CanUndo() {159 t.Error("loading made an undo step")160 }161 for _, mode := range []string{"", `"arithmetic": "binary",`, `"arithmetic": "quantum",`} {162 in := strings.Replace(out, `"arithmetic": "decimal",`, mode, 1)163 r, err := Read(strings.NewReader(in))164 if err != nil || r.Decimal() {165 t.Errorf("%q: decimal %v, %v", mode, r != nil && r.Decimal(), err)166 }167 }168 s.SetDecimal(false)169 b.Reset()170 if err := s.Write(&b); err != nil {171 t.Fatal(err)172 }173 if strings.Contains(b.String(), "arithmetic") {174 t.Errorf("binary sheet mentions arithmetic:\n%s", b.String())175 }176}177178// Formats inferred from the formula survive the decimal twins: a sum of179// currency is currency in both modes.180func TestDecimalKeepsFormats(t *testing.T) {181 for _, dec := range []bool{false, true} {182 s := decimalSheet(t, dec, "=SUM(A1:A2)", "$1.10", "$2.20")183 if f := s.DisplayFormat(at("B1")); f.Kind != FmtCurrency {184 t.Errorf("decimal=%v: format %v", dec, f)185 }186 if got, _ := Display(s.Value(at("B1")), s.DisplayFormat(at("B1")), 12); got != "$3.30" {187 t.Errorf("decimal=%v: shows %q", dec, got)188 }189 }190}191192func formatShortest(f float64) string {193 return strings.ToUpper(strconv.FormatFloat(f, 'g', -1, 64))194}195196func BenchmarkArithmetic(b *testing.B) {197 for _, dec := range []bool{false, true} {198 name := "binary"199 if dec {200 name = "decimal"201 }202 b.Run(name, func(b *testing.B) {203 s := New()204 for r := range 1000 {205 s.Set(Addr{Row: r}, "19.99")206 s.Set(Addr{Col: 1, Row: r}, "=A"+strconv.Itoa(r+1)+"*1.0825+0.01")207 }208 s.Set(at("C1"), "=SUM(B1:B1000)")209 s.SetDecimal(dec)210 b.ReportAllocs()211 b.ResetTimer()212 for range b.N {213 s.Set(at("A1"), "20.01")214 }215 })216 }217}