| 1 | package format |
| 2 | |
| 3 | import ( |
| 4 | "math" |
| 5 | "strconv" |
| 6 | "testing" |
| 7 | "time" |
| 8 | |
| 9 | "amadan.net/oficina/sheets/internal/sheets/calc" |
| 10 | ) |
| 11 | |
| 12 | // strconv.FormatFloat/fmt round half-to-even ("banker's rounding"): |
| 13 | // 0.005 at "0.00" comes out "0.00" in Go, but Excel (and every other |
| 14 | // spreadsheet) rounds half-away-from-zero and shows "0.01". This test |
| 15 | // pins that Display follows Excel, not Go's default — see roundDecimal |
| 16 | // in numeric.go, which works from a decimal-string representation |
| 17 | // specifically to make this the natural outcome rather than a special |
| 18 | // case bolted on top of float rounding. |
| 19 | func TestDisplayRoundsHalfAwayFromZero(t *testing.T) { |
| 20 | for _, tc := range []struct { |
| 21 | num float64 |
| 22 | want string |
| 23 | }{ |
| 24 | {0.005, "0.01"}, |
| 25 | {0.015, "0.02"}, |
| 26 | } { |
| 27 | if got := Display(numValue(tc.num), "0.00"); got != tc.want { |
| 28 | t.Errorf("Display(%v, %q) = %q, want %q", tc.num, "0.00", got, tc.want) |
| 29 | } |
| 30 | } |
| 31 | } |
| 32 | |
| 33 | // Negative zero (-0.0 < 0 is false under IEEE 754) must not leak a "-" |
| 34 | // through to the display string. Reachable in practice from a formula |
| 35 | // like =0*-1, which calc does not normalise away. |
| 36 | func TestDisplayNegativeZero(t *testing.T) { |
| 37 | negZero := math.Copysign(0, -1) |
| 38 | if !math.Signbit(negZero) { |
| 39 | t.Fatal("test setup: math.Copysign(0, -1) did not produce a signed zero") |
| 40 | } |
| 41 | if got := Display(numValue(negZero), "0.00"); got != "0.00" { |
| 42 | t.Errorf("Display(-0.0, %q) = %q, want %q", "0.00", got, "0.00") |
| 43 | } |
| 44 | if got := Display(numValue(negZero), "General"); got != "0" { |
| 45 | t.Errorf("Display(-0.0, %q) = %q, want %q", "General", got, "0") |
| 46 | } |
| 47 | } |
| 48 | |
| 49 | // Every ErrKind must display as its Excel text under any number format, |
| 50 | // not just #DIV/0! under one format — the single case the brief pins. |
| 51 | func TestDisplayEveryErrorIgnoresNumberFormat(t *testing.T) { |
| 52 | kinds := []calc.ErrKind{ |
| 53 | calc.ErrDiv0, calc.ErrValue, calc.ErrRef, calc.ErrName, |
| 54 | calc.ErrNum, calc.ErrNA, calc.ErrCircular, |
| 55 | } |
| 56 | for _, k := range kinds { |
| 57 | want := k.String() |
| 58 | for _, numFmt := range []string{"General", "0.00", "#,##0.00", "0%", "yyyy-mm-dd"} { |
| 59 | if got := Display(errValue(k), numFmt); got != want { |
| 60 | t.Errorf("Display(%v, %q) = %q, want %q", k, numFmt, got, want) |
| 61 | } |
| 62 | } |
| 63 | } |
| 64 | } |
| 65 | |
| 66 | // KindEmpty is the zero value of ValueKind, so a Value{} — an untouched |
| 67 | // cell — must render as "" under any format, never as the number "0". |
| 68 | func TestDisplayBlankIsEmptyString(t *testing.T) { |
| 69 | for _, numFmt := range []string{"General", "0.00", "#,##0.00", "0%", `"€"#,##0.00`, "yyyy-mm-dd"} { |
| 70 | if got := Display(calc.Value{}, numFmt); got != "" { |
| 71 | t.Errorf("Display(blank, %q) = %q, want \"\"", numFmt, got) |
| 72 | } |
| 73 | } |
| 74 | } |
| 75 | |
| 76 | func TestDisplayBoolIgnoresNumberFormat(t *testing.T) { |
| 77 | trueV := calc.Value{Kind: calc.KindBool, Bool: true} |
| 78 | falseV := calc.Value{Kind: calc.KindBool, Bool: false} |
| 79 | if got := Display(trueV, "0.00"); got != "TRUE" { |
| 80 | t.Errorf("Display(TRUE, %q) = %q, want TRUE", "0.00", got) |
| 81 | } |
| 82 | if got := Display(falseV, "0.00"); got != "FALSE" { |
| 83 | t.Errorf("Display(FALSE, %q) = %q, want FALSE", "0.00", got) |
| 84 | } |
| 85 | } |
| 86 | |
| 87 | // A text value is not reformatted by a numeric format code (matching |
| 88 | // Excel: a number format only applies to a text section, the 4th of |
| 89 | // up to four ';'-separated sections, absent here). |
| 90 | func TestDisplayTextPassesThroughNumericFormat(t *testing.T) { |
| 91 | v := calc.Value{Kind: calc.KindStr, Str: "hello"} |
| 92 | if got := Display(v, "0.00"); got != "hello" { |
| 93 | t.Errorf("Display(%q, %q) = %q, want %q", "hello", "0.00", got, "hello") |
| 94 | } |
| 95 | } |
| 96 | |
| 97 | func TestDisplayTextSection(t *testing.T) { |
| 98 | v := calc.Value{Kind: calc.KindStr, Str: "n/a"} |
| 99 | if got := Display(v, `0;0;0;"got: "@`); got != "got: n/a" { |
| 100 | t.Errorf("Display with text section = %q, want %q", got, "got: n/a") |
| 101 | } |
| 102 | } |
| 103 | |
| 104 | // A negative number under a single-section format gets an automatic |
| 105 | // "-" over the absolute value's rendering. |
| 106 | func TestDisplayNegativeSingleSectionAutoSign(t *testing.T) { |
| 107 | if got := Display(numValue(-1234.5), "#,##0.00"); got != "-1,234.50" { |
| 108 | t.Fatalf("Display(-1234.5, %q) = %q, want %q", "#,##0.00", got, "-1,234.50") |
| 109 | } |
| 110 | } |
| 111 | |
| 112 | // A two-or-more-section format supplies its own negative styling with |
| 113 | // NO automatic sign — matching Excel, where "0.00;(0.00)" is how you |
| 114 | // spell accounting-style parenthesized negatives. |
| 115 | func TestDisplayNegativeSectionNoAutoSign(t *testing.T) { |
| 116 | if got := Display(numValue(-5), "0.00;(0.00)"); got != "(5.00)" { |
| 117 | t.Fatalf("Display(-5, %q) = %q, want %q", "0.00;(0.00)", got, "(5.00)") |
| 118 | } |
| 119 | } |
| 120 | |
| 121 | // A section with no digit or date placeholders is a fixed literal — |
| 122 | // punctuation in it (not a bare letter) is not an unsupported construct, |
| 123 | // it just displays the same text regardless of value. |
| 124 | func TestDisplayLiteralOnlySection(t *testing.T) { |
| 125 | if got := Display(numValue(5), `"-"`); got != "-" { |
| 126 | t.Errorf(`Display(5, "-") = %q, want "-"`, got) |
| 127 | } |
| 128 | if got := Display(numValue(-99), `"n/a"`); got != "n/a" { |
| 129 | t.Errorf(`Display(-99, "n/a") = %q, want "n/a"`, got) |
| 130 | } |
| 131 | } |
| 132 | |
| 133 | // An unsupported construct (here '_' — Excel's space-padding fill |
| 134 | // character) falls the WHOLE value back to General rather than |
| 135 | // mangling the output partially. |
| 136 | func TestDisplayUnsupportedFallsBackToGeneral(t *testing.T) { |
| 137 | for _, numFmt := range []string{"0.00_)", "q", "0.00E+00", "# ?/?"} { |
| 138 | got := Display(numValue(1234.5678), numFmt) |
| 139 | want := Display(numValue(1234.5678), "General") |
| 140 | if got != want { |
| 141 | t.Errorf("Display(1234.5678, %q) = %q, want General's %q", numFmt, got, want) |
| 142 | } |
| 143 | } |
| 144 | } |
| 145 | |
| 146 | // serialFor mirrors calc's dateSerial (fn_date.go) and this package's |
| 147 | // excelEpoch (dates.go) independently, so a date test here pins the |
| 148 | // same convention rather than assuming it. |
| 149 | func serialFor(y int, mo time.Month, d, h, mi, s int) float64 { |
| 150 | epoch := time.Date(1899, time.December, 30, 0, 0, 0, 0, time.UTC) |
| 151 | tm := time.Date(y, mo, d, h, mi, s, 0, time.UTC) |
| 152 | return tm.Sub(epoch).Hours() / 24 |
| 153 | } |
| 154 | |
| 155 | func TestDisplayDates(t *testing.T) { |
| 156 | for _, tc := range []struct { |
| 157 | name string |
| 158 | serial float64 |
| 159 | numFmt string |
| 160 | want string |
| 161 | }{ |
| 162 | {"iso date", serialFor(2024, time.March, 5, 0, 0, 0), "yyyy-mm-dd", "2024-03-05"}, |
| 163 | {"slash date", serialFor(2024, time.March, 5, 0, 0, 0), "dd/mm/yyyy", "05/03/2024"}, |
| 164 | {"abbreviated month", serialFor(2024, time.March, 5, 0, 0, 0), "mmm d, yyyy", "Mar 5, 2024"}, |
| 165 | {"full month and weekday", serialFor(2024, time.March, 5, 0, 0, 0), "dddd, mmmm d, yyyy", |
| 166 | time.Date(2024, time.March, 5, 0, 0, 0, 0, time.UTC).Weekday().String() + ", March 5, 2024"}, |
| 167 | {"24h time", serialFor(2024, time.March, 5, 14, 30, 9), "hh:mm:ss", "14:30:09"}, |
| 168 | {"12h time PM", serialFor(2024, time.March, 5, 13, 5, 9), "hh:mm:ss AM/PM", "01:05:09 PM"}, |
| 169 | {"12h time midnight", serialFor(2024, time.March, 5, 0, 0, 0), "hh:mm:ss AM/PM", "12:00:00 AM"}, |
| 170 | {"date and minutes not month", serialFor(2024, time.March, 5, 14, 30, 0), "yyyy-mm-dd hh:mm", "2024-03-05 14:30"}, |
| 171 | } { |
| 172 | t.Run(tc.name, func(t *testing.T) { |
| 173 | if got := Display(numValue(tc.serial), tc.numFmt); got != tc.want { |
| 174 | t.Errorf("Display(%s, %q) = %q, want %q", tc.name, tc.numFmt, got, tc.want) |
| 175 | } |
| 176 | }) |
| 177 | } |
| 178 | } |
| 179 | |
| 180 | // Automatic ("General") shows at most 15 significant digits, the most a |
| 181 | // double holds for certain and what Excel's own General stops at. It |
| 182 | // printed the shortest round-trip representation instead, so =C4*0.1 |
| 183 | // with C4 = 920.5 showed 92.05000000000001 in the table view and |
| 184 | // "92.050000000…" clipped in the grid (audit, sheets-16/45/64). The |
| 185 | // stored value is the exact double either way; only the display rounds. |
| 186 | func TestGeneralShowsAtMostFifteenSignificantDigits(t *testing.T) { |
| 187 | // Variables, not constant expressions: Go folds 920.5 * 0.1 exactly |
| 188 | // at compile time, and the artefact only exists in float64 |
| 189 | // arithmetic, which is what the evaluator does. |
| 190 | tenth, a, b, c1, c2 := 0.1, 920.5, 3228.33, 0.2, 3.0 |
| 191 | if s := strconv.FormatFloat(a*tenth, 'f', -1, 64); s != "92.05000000000001" { |
| 192 | t.Fatalf("the float artefact this test is about did not happen (%s)", s) |
| 193 | } |
| 194 | for _, c := range []struct { |
| 195 | v float64 |
| 196 | want string |
| 197 | }{ |
| 198 | {a * tenth, "92.05"}, |
| 199 | {b * tenth, "322.833"}, |
| 200 | {tenth + c1, "0.3"}, |
| 201 | {1 / c2, "0.333333333333333"}, |
| 202 | {1234.5678, "1234.5678"}, |
| 203 | {-a * tenth, "-92.05"}, |
| 204 | {100, "100"}, |
| 205 | {1e21, "1000000000000000000000"}, |
| 206 | {0.000001, "0.000001"}, |
| 207 | } { |
| 208 | if got := Display(numValue(c.v), "General"); got != c.want { |
| 209 | t.Errorf("Display(%v, General) = %q, want %q", c.v, got, c.want) |
| 210 | } |
| 211 | if got := Display(numValue(c.v), ""); got != c.want { |
| 212 | t.Errorf("Display(%v, Automatic) = %q, want %q", c.v, got, c.want) |
| 213 | } |
| 214 | } |
| 215 | } |
| 216 | |
amadan