1package headless23import (4 "bytes"5 "context"6 "io"7 "os"8 "path/filepath"9 "strings"10 "testing"11 "time"1213 "src.rest/cat/012/internal/notebook"14 "src.rest/cat/012/internal/nushell"15 "src.rest/cat/012/internal/sheet"16)1718func addr(s string) sheet.Addr {19 a, _ := sheet.ParseAddr(s)20 return a21}2223// book is a workbook of two sheets, Sheet1 and 'Q3 plan', and a name.24func book(t *testing.T) *sheet.Workbook {25 t.Helper()26 w := sheet.NewBook()27 s := w.Sheet(0)28 for a, in := range map[string]string{"A1": "Item", "B1": "Price", "A2": "Apple", "B2": "1.5", "A3": "Pear", "B3": "$2", "B4": "=SUM(B2:B3)", "C1": "=1/0"} {29 if err := s.Set(addr(a), in); err != nil {30 t.Fatal(err)31 }32 }33 q, err := w.AddSheet("Q3 plan", 1)34 if err != nil {35 t.Fatal(err)36 }37 q.Set(addr("A1"), "2026-09-29")38 q.Set(addr("B2"), "=Sheet1!B4*2")39 if err := w.DefineName("Prices", s, sheet.NewRect(addr("B2"), addr("B3"))); err != nil {40 t.Fatal(err)41 }42 w.SetActive(s)43 return w44}4546func TestResolve(t *testing.T) {47 w := book(t)48 for ref, want := range map[string]string{49 "": "Sheet1",50 "B7": "Sheet1!B7",51 "$B$7": "Sheet1!B7",52 "A1:C9": "Sheet1!A1:C9",53 "'Q3 plan'!B2": "'Q3 plan'!B2",54 "Q3 plan!B2": "'Q3 plan'!B2",55 "Q3 plan": "'Q3 plan'",56 "'Q3 plan'!": "'Q3 plan'",57 "Prices": "Sheet1!B2:B3",58 } {59 got, err := Resolve(w, ref)60 if err != nil || got.String() != want {61 t.Errorf("Resolve(%q) = %s, %v; want %s", ref, got, err, want)62 }63 }64 for ref, want := range map[string]string{65 "Q4!A1": `no sheet named "Q4" (sheets: Sheet1, Q3 plan)`,66 "what": `"what" isn't a cell, a range, a named range, a table or a sheet`,67 } {68 if _, err := Resolve(w, ref); err == nil || !strings.Contains(err.Error(), want) {69 t.Errorf("Resolve(%q): %v, want %q", ref, err, want)70 }71 }72 if _, _, err := ResolveCell(w, "A1:B2"); err == nil || !strings.Contains(err.Error(), "name one cell") {73 t.Errorf("ResolveCell of a range: %v", err)74 }75}7677// Tables are found by name, whole, or by structured references as78// formulas read them; set types into a cell one names.79func TestTables(t *testing.T) {80 w := book(t)81 if err := w.Sheet(0).CreateTable("Fruit", sheet.NewRect(addr("A1"), addr("B3"))); err != nil {82 t.Fatal(err)83 }84 for ref, want := range map[string]string{85 "Fruit": "Sheet1!A1:B3",86 "fruit[Price]": "Sheet1!B2:B3",87 "Fruit[#All]": "Sheet1!A1:B3",88 "Fruit[[#Headers],[Price]]": "Sheet1!B1",89 } {90 if got, err := Resolve(w, ref); err != nil || got.String() != want {91 t.Errorf("Resolve(%q) = %s, %v; want %s", ref, got, err, want)92 }93 }94 for ref, want := range map[string]string{95 "Fruit[Cost]": "Fruit has no such column (columns: Item, Price)",96 "Fruit[@Item]": "names no rows of Fruit here",97 "Veg[Item]": "there's no table named Veg",98 } {99 if _, err := Resolve(w, ref); err == nil || !strings.Contains(err.Error(), want) {100 t.Errorf("Resolve(%q): %v, want %q", ref, err, want)101 }102 }103 if got := get(t, w, "Fruit", GetOptions{Format: "nuon"}); got != "[[Item, Price]; [Apple, 1.5],\n[Pear, 2]]\n" {104 t.Errorf("get Fruit %q", got)105 }106 if _, err := Set(w, []Entry{{Ref: "Fruit[[#Headers],[Price]]", Input: "Cost"}}, SetOptions{}); err != nil {107 t.Fatal(err)108 }109 if got := get(t, w, "Fruit[[#Headers],[Cost]]", GetOptions{}); got != "Cost\n" {110 t.Errorf("the header set %q", got)111 }112}113114func get(t *testing.T, w *sheet.Workbook, ref string, o GetOptions) string {115 t.Helper()116 target, err := Resolve(w, ref)117 if err != nil {118 t.Fatal(err)119 }120 var b bytes.Buffer121 if err := Get(&b, target, o); err != nil {122 t.Fatalf("get %s: %v", ref, err)123 }124 return b.String()125}126127func TestGet(t *testing.T) {128 w := book(t)129 for _, c := range []struct {130 ref string131 o GetOptions132 want string133 }{134 {"B4", GetOptions{}, "3.5\n"},135 {"B3", GetOptions{}, "$2\n"},136 {"B3", GetOptions{Format: "nuon"}, "2\n"},137 {"B3", GetOptions{Format: "json"}, "{\"currency\":2,\"decimals\":0}\n"},138 {"B4", GetOptions{Input: true}, "=SUM(B2:B3)\n"},139 {"B4", GetOptions{Input: true, Format: "json"}, "\"=SUM(B2:B3)\"\n"},140 {"A1", GetOptions{Format: "nuon"}, "Item\n"},141 {"Z9", GetOptions{Format: "nuon"}, "null\n"},142 {"Z9", GetOptions{}, "\n"},143 {"C1", GetOptions{}, "#DIV/0!\n"},144 {"'Q3 plan'!B2", GetOptions{}, "7\n"},145 {"A1:B3", GetOptions{}, "Item Price\nApple 1.5\nPear $2\n"},146 {"A1:B3", GetOptions{Format: "csv"}, "Item,Price\nApple,1.5\nPear,$2\n"},147 {"A1:B3", GetOptions{Format: "nuon"}, "[[Item, Price]; [Apple, 1.5],\n[Pear, 2]]\n"},148 {"A2:B3", GetOptions{Format: "json", NoHeader: true}, "[\n{\"A\":\"Apple\",\"B\":1.5},\n{\"A\":\"Pear\",\"B\":{\"currency\":2,\"decimals\":0}}\n]\n"},149 {"B1:B4", GetOptions{Format: "json", Input: true}, "[\n{\"Price\":\"1.5\"},\n{\"Price\":\"$2\"},\n{\"Price\":\"=SUM(B2:B3)\"}\n]\n"},150 } {151 if got := get(t, w, c.ref, c.o); got != c.want {152 t.Errorf("get %s %+v = %q, want %q", c.ref, c.o, got, c.want)153 }154 }155 if got := get(t, w, "'Q3 plan'!A1", GetOptions{Format: "nuon"}); !strings.HasPrefix(got, "2026-09-29T00:00:00") {156 t.Errorf("a date as NUON: %q", got)157 }158 target, _ := Resolve(w, "A1:B2")159 if err := Get(io.Discard, target, GetOptions{Format: "xml"}); err == nil {160 t.Error("--format xml was taken")161 }162}163164func TestSet(t *testing.T) {165 w := book(t)166 warn, err := Set(w, []Entry{{Ref: "B2", Input: "10"}, {Ref: "'Q3 plan'!C1", Input: "=B2+1"}, {Ref: "A2", Input: ""}}, SetOptions{})167 if err != nil || len(warn) > 0 {168 t.Fatal(warn, err)169 }170 s := w.Sheet(0)171 if v := s.Value(addr("B4")); v.Num != 12 {172 t.Errorf("B4 = %v after setting B2", v)173 }174 if s.Filled(addr("A2")) {175 t.Error("an empty input didn't clear A2")176 }177 if !w.CanUndo() {178 t.Error("set wasn't a change")179 }180 for _, c := range []struct {181 e Entry182 want string183 }{184 {Entry{Ref: "B5", Input: "=SUM(B2:B3"}, "Sheet1!B5: Expected , or ) in SUM, at character 11 of =SUM(B2:B3"},185 {Entry{Ref: "A1:A2", Input: "x"}, "A1:A2 is the range A1:A2: name one cell"},186 {Entry{Ref: "Q9!A1", Input: "x"}, `no sheet named "Q9"`},187 } {188 if _, err := Set(w, []Entry{c.e}, SetOptions{}); err == nil || !strings.Contains(err.Error(), c.want) {189 t.Errorf("set %v: %v, want %q", c.e, err, c.want)190 }191 }192}193194func TestSetRules(t *testing.T) {195 w := book(t)196 s := w.Sheet(0)197 s.Protect(sheet.Protection{Range: sheet.NewRect(addr("D1"), addr("D9"))})198 if _, err := Set(w, []Entry{{Ref: "D2", Input: "1"}}, SetOptions{}); err == nil || !strings.Contains(err.Error(), "Sheet1!D2 is protected (D1:D9): --force") {199 t.Errorf("a protected cell: %v", err)200 }201 if _, err := Set(w, []Entry{{Ref: "D2", Input: "1"}}, SetOptions{Force: true}); err != nil {202 t.Errorf("--force: %v", err)203 }204 s.AddValidation(sheet.Validation{Ranges: []sheet.Rect{sheet.NewRect(addr("E1"), addr("E9"))}, Kind: sheet.ValidNumber, Op: sheet.RuleBetween, Args: [2]string{"1", "9"}, Reject: true})205 s.AddValidation(sheet.Validation{Ranges: []sheet.Rect{sheet.NewRect(addr("F1"), addr("F9"))}, Kind: sheet.ValidNumber, Op: sheet.RuleBetween, Args: [2]string{"1", "9"}})206 if _, err := Set(w, []Entry{{Ref: "E1", Input: "5"}, {Ref: "E2", Input: "50"}}, SetOptions{}); err == nil || !strings.HasPrefix(err.Error(), "Sheet1!E2: ") {207 t.Errorf("a rejected entry: %v", err)208 }209 warn, err := Set(w, []Entry{{Ref: "F1", Input: "50"}}, SetOptions{})210 if err != nil || len(warn) != 1 || !strings.HasPrefix(warn[0], "Sheet1!F1: ") {211 t.Errorf("an entry marked invalid: %v %v", warn, err)212 }213}214215func TestOpenSave(t *testing.T) {216 dir := t.TempDir()217 path := filepath.Join(dir, "book.012")218 if _, err := Open(path, false); err == nil {219 t.Error("opened a missing file")220 }221 f, err := Open(path, true)222 if err != nil || !f.New {223 t.Fatal(f, err)224 }225 if _, err := Set(f.Book, []Entry{{Ref: "A1", Input: "5"}, {Ref: "A2", Input: "=A1*2"}}, SetOptions{}); err != nil {226 t.Fatal(err)227 }228 if err := f.Save(); err != nil {229 t.Fatal(err)230 }231 os.Chmod(path, 0o600)232 old := time.Now().Add(-time.Hour)233 os.Chtimes(path, old, old)234 f, err = Open(path, false)235 if err != nil {236 t.Fatal(err)237 }238 if v := f.Book.Sheet(0).Value(addr("A2")); v.Num != 10 {239 t.Errorf("A2 = %v", v)240 }241 if err := f.Save(); err != nil {242 t.Fatal(err)243 }244 if st, _ := os.Stat(path); !st.ModTime().Equal(old) {245 t.Error("saving an unchanged workbook rewrote it")246 }247 Set(f.Book, []Entry{{Ref: "A1", Input: "6"}}, SetOptions{})248 if err := f.Save(); err != nil {249 t.Fatal(err)250 }251 st, _ := os.Stat(path)252 if st.ModTime().Equal(old) || st.Mode().Perm() != 0o600 {253 t.Errorf("saved %v with mode %v", st.ModTime(), st.Mode())254 }255 if entries, _ := os.ReadDir(dir); len(entries) != 1 {256 t.Errorf("left files behind: %v", entries)257 }258 os.WriteFile(path, []byte("{nope"), 0o644)259 if _, err := Open(path, false); err == nil || !strings.HasPrefix(err.Error(), path+": ") {260 t.Errorf("a broken file: %v", err)261 }262}263264func TestRecalc(t *testing.T) {265 w := book(t)266 w.Sheet(0).Set(addr("D1"), "=D2")267 w.Sheet(0).Set(addr("D2"), "=D1")268 problems, circular := Recalc(w)269 if !circular {270 t.Error("no circular reference")271 }272 var got []string273 for _, p := range problems {274 got = append(got, p.At()+" "+p.Value)275 }276 if strings.Join(got, ", ") != "Sheet1!C1 #DIV/0!, Sheet1!D1 #REF!, Sheet1!D2 #REF!" {277 t.Errorf("problems: %v", got)278 }279 if problems[0].Why == "" {280 t.Error("no explanation")281 }282}283284// fakeNu answers commands with NUON.285type fakeNu map[string]string286287func (f fakeNu) Run(_ context.Context, job nushell.Job, _ string, stdout io.Writer) error {288 out, ok := f[job.Command]289 if !ok {290 return &nushell.Error{Msg: "Command `" + job.Command + "` not found"}291 }292 _, err := io.WriteString(stdout, out)293 return err294}295296func TestRunNotebooks(t *testing.T) {297 w := sheet.NewBook()298 grid := w.Sheet(0)299 grid.Set(addr("D1"), "7")300 nb, err := w.AddNotebook("Notebook", grid)301 if err != nil {302 t.Fatal(err)303 }304 nb.SetNotebookCells("add cells", []notebook.Cell{305 {Kind: notebook.Code, Source: "files = ls"},306 {Kind: notebook.Note, Source: "# Files"},307 {Kind: notebook.Code, Source: "n = $files | length"},308 {Kind: notebook.Code, Source: "$sheet.D1:D1"},309 {Kind: notebook.Code, Source: "boom"},310 {Kind: notebook.Code, Source: "last = 1"},311 })312 if err := grid.AddRegion(sheet.Region{Name: "files", At: addr("A1"), Output: true}); err != nil {313 t.Fatal(err)314 }315 nu := fakeNu{"ls": "[[name, size]; [a, 1], [b, 2]]", "$files | length": "2", "$__sheet1": "[[D]; [7]]"}316 failed := RunNotebooks(context.Background(), w, NotebookOptions{Runner: nu, Timeout: time.Second})317 if len(failed) != 1 || failed[0] != "Notebook cell 5: Command `boom` not found" {318 t.Errorf("failed: %q", failed)319 }320 target, _ := Resolve(w, "A1:B3")321 var b bytes.Buffer322 Get(&b, target, GetOptions{Format: "csv"})323 if b.String() != "name,size\na,1\nb,2\n" {324 t.Errorf("the output sent to Sheet1:\n%s", b.String())325 }326 cells := nb.NotebookCells()327 if o := w.Output(cells[2].ID); o == nil || string(o.NUON) != "2" || o.Reads["files"] == 0 {328 t.Errorf("$files | length: %+v", o)329 }330 if o := w.Output(cells[4].ID); o == nil || o.Err != "Command `boom` not found" {331 t.Errorf("boom: %+v", o)332 }333 if o := w.Output(cells[5].ID); o != nil {334 t.Errorf("a cell after a failure ran: %+v", o)335 }336 if o := w.Output(cells[1].ID); o != nil {337 t.Errorf("a note ran: %+v", o)338 }339}340341func TestOpenSendsOutputs(t *testing.T) {342 path := filepath.Join(t.TempDir(), "nb.012")343 os.WriteFile(path, []byte(`{"version": 4, "sheets": [344{"name": "Sheet1", "regions": [{"name": "files", "at": "B2", "output": true}], "cells": {}},345{"name": "Notebook", "tab": "notebook", "notebookCells": [{"source": "files = ls", "output": "[[name]; [a.txt]]"}], "cells": {}}]}`), 0o644)346 f, err := Open(path, false)347 if err != nil {348 t.Fatal(err)349 }350 if got := f.Book.Sheet(0).LocalText(addr("B3")); got != "a.txt" {351 t.Errorf("B3 = %q, want the output the file kept", got)352 }353}