1package sheet23import (4 "bytes"5 "strings"6 "testing"7 "time"89 "src.rest/cat/012/internal/value"10)1112// fixedNow pins TODAY() and entries without a year to 2026-09-26 14:30.13func fixedNow(t *testing.T) {14 t.Helper()15 old := value.Now16 value.Now = func() time.Time { return time.Date(2026, 9, 26, 14, 30, 0, 0, time.Local) }17 t.Cleanup(func() { value.Now = old })18}1920func TestDisplay(t *testing.T) {21 tests := []struct {22 v Value23 f Format24 width int25 want string26 align Align27 }{28 {num(1234.5), Preset(FmtNumber), 10, "1,234.50", AlignRight},29 {num(1234567.5), Preset(FmtNumber), 10, "#########", AlignRight}, // doesn't fit30 {num(0.125), Preset(FmtPercent), 10, "12.50%", AlignRight},31 {num(1234.5), Preset(FmtScientific), 10, "1.23E+03", AlignRight},32 {num(1450), Preset(FmtAccounting), 12, " $ 1,450.00 ", AlignFill},33 {num(-1450), Preset(FmtAccounting), 12, " $(1,450.00)", AlignFill},34 {num(0), Preset(FmtAccounting), 12, " $ - ", AlignFill},35 {num(1450), Preset(FmtAccounting), 10, "$1,450.00 ", AlignFill},36 {num(-1450), Preset(FmtAccounting), 10, "#########", AlignRight},37 {num(-1450), Preset(FmtFinancial), 12, "(1,450.00)", AlignRight},38 {num(1450), Preset(FmtCurrency), 10, "$1,450.00", AlignRight},39 {num(1450.5), Format{Kind: FmtCurrency}, 10, "$1,451", AlignRight},40 {num(46291), Preset(FmtDate), 10, "9/26/2026", AlignRight},41 {num(46291.6041666667), Preset(FmtTime), 12, "2:30:00 PM", AlignRight},42 {num(46291.6041666667), Preset(FmtDateTime), 20, "9/26/2026 14:30:00", AlignRight},43 {num(1.5), Preset(FmtDuration), 10, "36:00:00", AlignRight},44 {num(42), Preset(FmtText), 10, "42", AlignLeft},45 {num(1.0 / 3), Format{}, 10, "0.3333333", AlignRight},46 {num(0.1 + 0.2), Format{}, 10, "0.3", AlignRight},47 {num(1.10000000001), Format{}, 10, "1.1", AlignRight},48 {txt("Rent"), Preset(FmtCurrency), 10, "Rent", AlignLeft},49 {boolean(true), Preset(FmtCurrency), 10, "TRUE", AlignCenter},50 {ErrNA, Format{}, 10, "#N/A", AlignCenter},51 {num(3), Format{Kind: FmtCustom, Pattern: "0.00"}, 10, "3.00", AlignRight},52 }53 for _, tt := range tests {54 got, al := Display(tt.v, tt.f, tt.width)55 if got != tt.want || al != tt.align {56 t.Errorf("Display(%v, %+v, %d) = %q, %v; want %q, %v", tt.v, tt.f, tt.width, got, al, tt.want, tt.align)57 }58 }59}6061func TestEntryDetection(t *testing.T) {62 fixedNow(t)63 tests := []struct {64 in string65 want float6466 f Format67 shown string // in a 30-wide cell68 }{69 {"42", 42, Format{}, "42"},70 {"$1,200", 1200, Format{Kind: FmtCurrency}, "$1,200"},71 {"$1,200.5", 1200.5, Preset(FmtCurrency), "$1,200.50"},72 {"-$5", -5, Format{Kind: FmtCurrency}, "-$5"},73 {"12%", 0.12, Format{Kind: FmtPercent}, "12%"},74 {"12.5%", 0.125, Preset(FmtPercent), "12.50%"},75 {"0.7%", 0.007, Preset(FmtPercent), "0.70%"}, // not 0.00699999999999999976 {"1e3%", 10, Format{Kind: FmtPercent}, "1000%"},77 {"1,234", 1234, Format{Kind: FmtNumber}, "1,234"},78 {"1.5e3", 1500, Preset(FmtScientific), "1.50E+03"},79 {"9/26/2026", 46291, Preset(FmtDate), "9/26/2026"},80 {"9/26/26", 46291, Preset(FmtDate), "9/26/2026"},81 {"9/26", 46291, Preset(FmtDate), "9/26/2026"}, // this year, as Sheets82 {"2026-09-26", 46291, Format{Kind: FmtDate, Pattern: "yyyy-mm-dd"}, "2026-09-26"},83 {"2026/9/26", 46291, Format{Kind: FmtDate, Pattern: "yyyy/mm/dd"}, "2026/09/26"},84 {"9-26-2026", 46291, Preset(FmtDate), "9/26/2026"},85 {"Sep 26, 2026", 46291, Format{Kind: FmtDate, Pattern: "mmm d, yyyy"}, "Sep 26, 2026"},86 {"september 26 2026", 46291, Format{Kind: FmtDate, Pattern: "mmmm d, yyyy"}, "September 26, 2026"},87 {"26 Sep 2026", 46291, Format{Kind: FmtDate, Pattern: "d mmm yyyy"}, "26 Sep 2026"},88 {"1/1/1930", 10959, Preset(FmtDate), "1/1/1930"},89 {"14:30", 14.5 / 24, Format{Kind: FmtTime, Pattern: "h:mm:ss"}, "14:30:00"},90 {"2:30 PM", 14.5 / 24, Preset(FmtTime), "2:30:00 PM"},91 {"2pm", 14.0 / 24, Preset(FmtTime), "2:00:00 PM"},92 {"12:15 am", 0.25 / 24, Preset(FmtTime), "12:15:00 AM"},93 {"1:02:03", (3600 + 120 + 3) / 86400.0, Format{Kind: FmtTime, Pattern: "h:mm:ss"}, "1:02:03"},94 {"25:30", 25.5 / 24, Preset(FmtDuration), "25:30:00"},95 {"9/26/2026 14:30", 46291 + 14.5/24, Preset(FmtDateTime), "9/26/2026 14:30:00"},96 {"9/26/2026 2:30 pm", 46291 + 14.5/24, Format{Kind: FmtDateTime, Pattern: "m/d/yyyy h:mm:ss am/pm"}, "9/26/2026 2:30:00 PM"},97 }98 for _, tt := range tests {99 s := New()100 if err := s.Set(at("A1"), tt.in); err != nil {101 t.Fatalf("Set(%q): %v", tt.in, err)102 }103 c := s.Cell(at("A1"))104 if c.Value.Kind != Number || !near(c.Value.Num, tt.want) || c.Format != tt.f {105 t.Errorf("%q = %+v %+v, want %v %+v", tt.in, c.Value, c.Format, tt.want, tt.f)106 continue107 }108 if got, _ := Display(c.Value, s.DisplayFormat(at("A1")), 30); got != tt.shown {109 t.Errorf("%q shows %q, want %q", tt.in, got, tt.shown)110 }111 }112 // Not dates or numbers: text, as in Sheets.113 for _, in := range []string{"2/30/2026", "13/1/2026", "1:60", "Sep", "May be", "10:5", "1.2.3", "12:30 xm", "Sep 40, 2026", "0x10", "inf", "1_000"} {114 s := New()115 s.Set(at("A1"), in)116 if v := s.Value(at("A1")); v.Kind != Text {117 t.Errorf("%q = %+v, want text", in, v)118 }119 }120}121122func near(a, b float64) bool {123 d := a - b124 return d < 1e-9 && d > -1e-9125}126127func TestFormatsPersistOnBlankCells(t *testing.T) {128 s := New()129 r := NewRect(at("B2"), at("B3"))130 s.SetFormat(r, Preset(FmtCurrency))131 s.SetStyle(r, func(st *Style) { st.Bold = true })132 if s.Len() != 0 {133 t.Errorf("Len = %d, want 0 for formatted blanks", s.Len())134 }135 if _, ok := s.UsedRange(); ok {136 t.Error("UsedRange counts formatted blanks")137 }138 if got := s.Edge(at("B1"), 0, 1); got != at("B1048576") {139 t.Errorf("Edge over formatted blanks = %v", got)140 }141142 // Typing into a formatted blank uses its format, as in Sheets.143 s.Set(at("B2"), "1450")144 if got, _ := Display(s.Value(at("B2")), s.DisplayFormat(at("B2")), 10); got != "$1,450.00" {145 t.Errorf("B2 shows %q", got)146 }147 if !s.Cell(at("B2")).Style.Bold {148 t.Error("B2 lost bold")149 }150 // An entry that implies a format replaces it.151 s.Set(at("B2"), "12%")152 if got := s.Cell(at("B2")).Format; got != (Format{Kind: FmtPercent}) {153 t.Errorf("B2 format after 12%% = %+v", got)154 }155156 // Delete clears contents but keeps formatting.157 s.EraseRange(r)158 if c := s.Cell(at("B2")); c == nil || !c.Blank() || c.Format.Kind != FmtPercent || !c.Style.Bold {159 t.Errorf("after erase B2 = %+v", c)160 }161 // Clear formatting drops the blank cells entirely.162 s.ClearFormatting(r)163 if s.Cell(at("B2")) != nil || s.Cell(at("B3")) != nil {164 t.Error("cells remain after clearing formatting")165 }166}167168func TestPlainTextFormat(t *testing.T) {169 s := New()170 s.SetFormat(NewRect(at("A1"), at("A1")), Preset(FmtText))171 s.Set(at("A1"), "=1+1")172 if v := s.Value(at("A1")); v != txt("=1+1") {173 t.Errorf("plain text formula = %+v", v)174 }175 s.Set(at("A1"), "007")176 if v := s.Value(at("A1")); v != txt("007") {177 t.Errorf("plain text number = %+v", v)178 }179 // Applying plain text to a number keeps the number but shows it as text.180 s.Set(at("B1"), "12.50")181 s.SetFormat(NewRect(at("B1"), at("B1")), Preset(FmtText))182 if got, al := Display(s.Value(at("B1")), s.DisplayFormat(at("B1")), 10); got != "12.5" || al != AlignLeft {183 t.Errorf("B1 shows %q %v", got, al)184 }185}186187func TestAdjustDecimals(t *testing.T) {188 tests := []struct {189 in string190 f Format191 delta int192 want string193 }{194 {"1.5", Format{}, 1, "1.50"},195 {"1.5", Format{}, -1, "2"},196 {"1.2345", Format{}, -1, "1.235"},197 {"3", Format{}, 1, "3.0"},198 {"1234.5", Preset(FmtNumber), 1, "1,234.500"},199 {"1234.5", Preset(FmtNumber), -2, "1,235"},200 {"1234.5", Format{Kind: FmtNumber}, -1, "1,235"}, // stops at zero201 {"0.125", Preset(FmtPercent), -1, "12.5%"},202 {"$5", Format{}, 1, "$5.0"},203 {"9/26/2026", Format{}, 1, "9/26/2026"}, // dates don't change204 }205 for _, tt := range tests {206 s := New()207 r := NewRect(at("A1"), at("A1"))208 s.Set(at("A1"), tt.in)209 if !tt.f.IsZero() {210 s.SetFormat(r, tt.f)211 }212 s.AdjustDecimals(r, tt.delta)213 if got, _ := Display(s.Value(at("A1")), s.DisplayFormat(at("A1")), 20); got != tt.want {214 t.Errorf("%q %+v %+d = %q, want %q", tt.in, tt.f, tt.delta, got, tt.want)215 }216 }217}218219func TestFileV2RoundTrip(t *testing.T) {220 fixedNow(t)221 s := New()222 s.Set(at("A1"), "Total")223 s.Set(at("B1"), "$1,200")224 s.Set(at("B2"), "12%")225 s.SetFormat(NewRect(at("B2"), at("B2")), Preset(FmtNumber)) // explicit format beats the entry's226 s.Set(at("C1"), "2026-09-26")227 s.SetStyle(NewRect(at("A1"), at("A1")), func(st *Style) { st.Bold, st.Align = true, AlignCenter })228 s.SetFormat(NewRect(at("D4"), at("D4")), Preset(FmtText)) // formatted blank229 s.Set(at("E1"), "=B1*2")230231 var buf bytes.Buffer232 if err := s.Write(&buf); err != nil {233 t.Fatal(err)234 }235 for _, want := range []string{`"version": 2`, `"E1": "=B1*2"`, `"A1": {"input":"Total","bold":true,"align":"center"}`,236 `"B1": {"input":"$1,200","format":"currency","decimals":0}`, `"D4": {"format":"text"}`,237 `"C1": {"input":"2026-09-26","format":"date","pattern":"yyyy-mm-dd"}`} {238 if !strings.Contains(buf.String(), want) {239 t.Errorf("file lacks %s:\n%s", want, buf.String())240 }241 }242 got, err := Read(&buf)243 if err != nil {244 t.Fatal(err)245 }246 for _, a := range []string{"A1", "B1", "B2", "C1", "D4", "E1"} {247 w, g := s.Cell(at(a)), got.Cell(at(a))248 if g == nil || g.Input != w.Input || g.Format != w.Format || g.Style != w.Style || g.Value != w.Value {249 t.Errorf("%s = %+v, want %+v", a, g, w)250 }251 }252 if f := got.DisplayFormat(at("E1")); f != (Format{Kind: FmtCurrency}) {253 t.Errorf("E1 inferred format after load = %+v", f)254 }255 // Typing into the loaded plain-text blank keeps text.256 got.Set(at("D4"), "0012")257 if v := got.Value(at("D4")); v != txt("0012") {258 t.Errorf("D4 = %+v", v)259 }260}261262func TestFileV1StillLoads(t *testing.T) {263 v1 := `{"version": 1, "widths": {"A": 14}, "cells": {"A1": "Rent", "B1": "$1,450", "B2": "=B1*12"}}`264 s, err := Read(strings.NewReader(v1))265 if err != nil {266 t.Fatal(err)267 }268 if s.ColWidth(0) != 14 || s.Value(at("B2")).Num != 17400 {269 t.Errorf("width %d B2 %+v", s.ColWidth(0), s.Value(at("B2")))270 }271 // Entries imply formats on load, as if typed again.272 if f := s.DisplayFormat(at("B2")); f.Kind != FmtCurrency {273 t.Errorf("B2 format %+v", f)274 }275 for _, bad := range []string{276 `{"version": 4, "cells": {}}`,277 `{"version": 2, "cells": {"A1": {"format": "sparkly"}}}`,278 `{"version": 2, "cells": {"A1": {"align": "diagonal"}}}`,279 `{"version": 2, "cells": {"A1": 5}}`,280 } {281 if _, err := Read(strings.NewReader(bad)); err == nil {282 t.Errorf("Read(%s) succeeded", bad)283 }284 }285}