1package sheet23import (4 "bytes"5 "encoding/json"6 "errors"7 "fmt"8 "strings"9 "testing"1011 "src.rest/cat/012/internal/locale"12)1314// readWhole reads a file the way encoding/json reads it whole, into15// fileFormat, as the reference the streaming reader is held to. It16// reports ambiguous for a file whose result depends on map order: a17// sheet naming one cell by two keys ("A1" and "a1").18func readWhole(data []byte) (w *Workbook, ambiguous bool, err error) {19 var f fileFormat20 if err := json.NewDecoder(bytes.NewReader(data)).Decode(&f); err != nil {21 return nil, false, err22 }23 if f.Version < 1 || f.Version > fileVersion {24 return nil, false, fmt.Errorf("unsupported file version %d", f.Version)25 }26 bodies := f.Sheets27 if f.Version < 4 {28 bodies = []fileSheet{f.fileSheet}29 if bodies[0].Name == "" {30 bodies[0].Name = "Sheet1"31 }32 }33 if len(bodies) == 0 {34 return nil, false, errors.New("the file has no sheets")35 }36 w = emptyBook()37 for _, body := range bodies {38 if err := w.checkName(nil, body.Name); err != nil {39 return nil, false, err40 }41 w.insert(w.newSheet(body.Name), len(w.sheets))42 }43 if err := w.readNames(f.Names); err != nil {44 return nil, false, err45 }46 var old []oldRegion47 for i, body := range bodies {48 s := w.sheets[i]49 amb, err := placeWhole(s, body.Cells, f.Version)50 if err != nil {51 return nil, false, err52 }53 ambiguous = ambiguous || amb54 if err := s.read(body, &old); err != nil {55 return nil, false, err56 }57 }58 w.active = clampInt(f.Active, 0, len(w.sheets)-1)59 w.settleHidden()60 w.decimal = f.Arithmetic == "decimal"61 if l, ok := locale.Lookup(f.Locale); ok {62 w.locale = l63 }64 if err := w.readMacros(f.Macros); err != nil {65 return nil, false, err66 }67 w.macroOrigin = f.MacroOrigin68 w.convertOld(old)69 w.nb.changed = 070 w.RecalcAll()71 return w, ambiguous, nil72}7374// placeWhole places a sheet's cells decoded whole, and reports whether75// two keys name one cell.76func placeWhole(s *Sheet, cells map[string]json.RawMessage, version int) (ambiguous bool, err error) {77 seen := map[Addr]bool{}78 for name, raw := range cells {79 a, ok := ParseAddr(name)80 if !ok {81 return false, fmt.Errorf("invalid cell %q", name)82 }83 ambiguous = ambiguous || seen[a]84 seen[a] = true85 e, err := decodeNoted(raw)86 if err != nil {87 return false, err88 }89 c, err := e.cell(version < 2)90 if err != nil {91 return false, err92 }93 if c != nil {94 s.place(a, c)95 }96 }97 return ambiguous, nil98}99100// written is the file w writes.101func written(t testing.TB, w *Workbook) string {102 var b bytes.Buffer103 if err := w.Write(&b); err != nil {104 t.Fatal(err)105 }106 return b.String()107}108109// sameAsWhole checks that the streaming reader reads data as readWhole110// does: both fail, or both make workbooks that write the same file.111func sameAsWhole(t *testing.T, data []byte) {112 want, ambiguous, werr := readWhole(data)113 got, gerr := ReadBook(bytes.NewReader(data))114 switch {115 case errors.Is(gerr, errTwice):116 // Refused, where decoding the whole file took the last (and for117 // sheets, merged the two lists).118 case gerr != nil && werr == nil && duplicateKeys(data):119 // Streaming reads every entry of a cell given twice, and refuses120 // an invalid one that decoding the whole file overwrote.121 case (werr == nil) != (gerr == nil):122 t.Fatalf("reading %q: streaming %v, whole %v", data, gerr, werr)123 case werr == nil && !ambiguous:124 if g, w := written(t, got), written(t, want); g != w {125 t.Fatalf("reading %q: streaming writes\n%s\nwhole writes\n%s", data, g, w)126 }127 }128}129130// duplicateKeys reports whether an object in data gives a key twice.131func duplicateKeys(data []byte) bool {132 dec := json.NewDecoder(bytes.NewReader(data))133 type level struct {134 keys map[string]bool135 isKey bool // the next token in this object is a key136 }137 var stack []*level138 for {139 tok, err := dec.Token()140 if err != nil {141 return false142 }143 top := (*level)(nil)144 if len(stack) > 0 {145 top = stack[len(stack)-1]146 }147 switch tok {148 case json.Delim('{'), json.Delim('['):149 if top != nil && top.keys != nil {150 top.isKey = true // the object or array is the value151 }152 l := &level{}153 if tok == json.Delim('{') {154 l.keys, l.isKey = map[string]bool{}, true155 }156 stack = append(stack, l)157 continue158 case json.Delim('}'), json.Delim(']'):159 stack = stack[:len(stack)-1]160 if len(stack) == 0 {161 return false162 }163 continue164 }165 if top == nil || top.keys == nil {166 continue167 }168 if top.isKey {169 k := tok.(string)170 if top.keys[k] {171 return true172 }173 top.keys[k] = true174 }175 top.isKey = !top.isKey176 }177}178179// streamSeeds are files to read: what Write makes of sheets holding the180// parser's fuzz seeds and every kind of field, and files written by hand181// with keys out of order, in other cases, escaped and given twice.182func streamSeeds(t testing.TB) [][]byte {183 s := New()184 inputs := []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",185 "Rent", "TRUE", "1.50", "'007", "<b>&</b>", "tab\tquote\"back\\slash", "café
", strings.Repeat("long ", 20000)}186 for i, in := range inputs {187 s.Set(Addr{Col: i % 4, Row: i / 4}, in)188 }189 s.SetFormat(NewRect(Addr{Row: 10}, Addr{Col: 2, Row: 12}), Format{Kind: FmtCurrency, Decimals: 2})190 s.SetStyle(NewRect(Addr{Row: 11}, Addr{Col: 3, Row: 11}), func(st *Style) { st.Bold = true })191 s.SetNote(Addr{Col: 5, Row: 1}, "a note")192 s.SetColWidth(1, 20)193 s.SetStyle(NewRect(Addr{Row: 4}, Addr{Row: 4}), func(st *Style) { st.Wrap = WrapOn })194 s.SetStyle(NewRect(Addr{Col: 4}, Addr{Col: 4, Row: MaxRows - 1}), func(st *Style) { st.Wrap = WrapClip })195 s.SetBorders(NewRect(Addr{Row: 1}, Addr{Col: 2, Row: 3}), BorderAll, LineThin)196 s.SetBorders(NewRect(Addr{Row: 1}, Addr{Col: 2, Row: 3}), BorderOuter, LineDouble)197 s.SetBorderStroke(NewRect(Addr{Col: 3, Row: 1}, Addr{Col: 3, Row: 2}), BorderRight, Stroke{Line: LineThick, Color: ColorMagenta})198 s.SetStyle(NewRect(Addr{Row: 6}, Addr{Col: 1, Row: 6}), func(st *Style) { st.VAlign = VAlignMiddle })199 s.SetRowHeight(6, 7, 3)200 s.LoadMerge(NewRect(Addr{Col: 6}, Addr{Col: 8, Row: 1}))201 one := []byte(written(t, s.Book()))202 if _, err := s.Book().AddSheet("Other", 1); err != nil {203 t.Fatal(err)204 }205 s.Book().Sheet(1).Set(Addr{}, "=SUM(Sheet1!B1:B5)")206 two := []byte(written(t, s.Book()))207 // Many cells, so keys and values straddle the reader's buffer.208 big := New()209 for r := range 20000 {210 big.Load(Addr{Row: r}, fmt.Sprintf("%d.%02d", r, r%100), Format{}, Style{})211 big.Load(Addr{Col: 1, Row: r}, fmt.Sprintf("text %d", r%300), Format{}, Style{Bold: r%7 == 0})212 }213 return [][]byte{one, two, []byte(written(t, big.Book())),214 []byte(`{"version": 1, "widths": {"A": 14}, "cells": {"A1": "Rent", "B1": "$1,450", "B2": "=B1*12"}}`),215 []byte(`{"cells": {"A1": "5", "B1": "=A1+1"}, "version": 2}`),216 []byte(`{"Cells": {"A1": "5"}, "VERSION": 3, "cells": {"A1": "6", "A2": {"input": "7", "bold": true}}}`),217 []byte(`{"sheets": [{"cells": {"A1": "1"}, "name": "S"}], "version": 4}`),218 []byte(`{"version": 4, "sheets": [{"name": "A", "cells": {"A1": "1"}}, null], "sheets": [{"name": "B", "cells": {"B2": "A\n"}}]}`),219 []byte(`{"version": 2, "cells": {"A1": "x", "a1": "y"}}`),220 []byte(`{"version": 6, "sheets": [{"name": "S", "tables": [{"name":"Sales","range":"A1:B3","columns":["Item","Amount"],"banded":true}], "cells": {"A1": "Item", "B1": "Amount", "B2": "5", "C1": "=SUM(Sales[Amount])+Sales[[#Headers],[Item]]"}}]}`),221 []byte(`{"version": 2, "cells": {"A1": "=(", "A1": "1", "B1": "2", "B1": ""}}`),222 []byte(`{"version": 2, "cells": null, "sheets": 5}`),223 []byte(`{"version": 4, "cells": "x", "sheets": []}`),224 []byte(`{"version": 2, "cells": {"A1": "😀", "A2": "bad \xff", "ZZ9": 5}}`),225 []byte(`{"version": 2, "cells": {"A1": "1"}} trailing`),226 []byte(`{"version": 2, "heights": {"2": 3, "9": 99}, "merges": ["A1:B1", "B1:C2", "D4"], "cells": {"A1": {"input": "5", "wrap": "wrap", "borders": {"top": "thin", "right": "double"}}}}`),227 []byte(`{"version": 2, "cells": {"A1": {"input": "x", "wrap": "sideways"}, "B1": {"borders": {"left": "dotted"}}}}`),228 []byte(`{"version": 2, "cells": {"A1": {"input": "5", "valign": "top", "borders": {"top": "thick", "topColor": "red", "left": "thin", "leftColor": "blue"}}, "B1": {"borders": {"rightColor": "green"}}}}`),229 []byte(`{"version": 2, "cells": {"A1": {"input": "x", "valign": "sideways"}, "B1": {"borders": {"left": "thin", "leftColor": "mauve"}}}}`),230 []byte(`{"version": 2, "cells": {"A1": "5"}, "conditionalFormats": [231 {"ranges":"A1:A9","dataBar":{"color":"blue","min":{"type":"min"},"max":{"type":"percentile","value":"90"},"barOnly":true}},232 {"ranges":"B1:B9","iconSet":{"icons":"circles","points":[{"type":"percent","value":"20"},{"type":"percent","value":"40"},{"type":"num","value":"6"},{"type":"percent","value":"80"}],"reverse":true}},233 {"ranges":"C1:C9","condition":"top_percent","values":["10"],"fill":"green"},234 {"ranges":"D1:D9","condition":"date_is","values":["next month"],"bold":true}],235 "validations": [{"ranges":"E1:E9","criteria":"checkbox","items":["Yes","No"]}, {"ranges":"F1:F9","criteria":"list","items":["a"],"display":"chip"}]}`),236 []byte(`{"version": 2, "conditionalFormats": [{"ranges":"A1","dataBar":{"color":"none","min":{"type":"min"},"max":{"type":"max"}}}]}`),237 []byte(`{"version": 2, "conditionalFormats": [{"ranges":"A1","iconSet":{"icons":"symbols","points":[{"type":"percent","value":"50"}]}}]}`),238 []byte(`{"version": 2, "validations": [{"ranges":"A1","criteria":"list","items":["a"],"display":"bubbles"}]}`),239 []byte(`{"version": 2, "heights": {"0": 2}, "merges": ["A1:"], "cells": {}}`),240 []byte(`{"version": 2, "macroOrigin": "m", "cells": {"C1": "x"}, "regions": [{"name": "app", "at": "A1", "path": "logs/app.csv", "window": 3}, {"name": "t", "at": "E5", "path": "/tmp/t.db", "format": "SQLite", "query": "select 1"}]}`),241 []byte(`{"version": 4, "sheets": [{"name": "Log", "cells": {}, "regions": [{"name": "a", "at": "B2", "path": "a.nuon"}]}, {"name": "S", "cells": {"A1": "=SUM(Log!B:B)+SUM(nu.a)"}}]}`),242 []byte(`{"version": 2, "cells": {}, "regions": [{"name": "a", "at": "A1", "path": "a", "command": "ls"}, {"name": "b", "at": "A3", "path": "b", "window": -2}]}`),243 []byte(`{"version": 2, "macroOrigin": "m1", "shellHistory": ["ls", "$r1 | first"], "notebook": true, "cells": {"A3": {"bold": true}}, "regions": [244 {"name": "r1", "command": "ls", "at": "A1", "rows": 3, "cols": 2},245 {"name": "big", "command": "$r1 | where size > 1kb", "at": "A6", "rows": 2, "cols": 2, "reads": ["r1"], "input": "Sheet1!D1:E4", "sort": [{"column": 2, "desc": true}]}]}`),246 []byte(`{"version": 2, "regions": [{"name": "r1", "command": "ls", "at": "A1"}, {"name": "R1", "command": "x", "at": "A5"}]}`),247 []byte(`{"version": 2, "regions": [{"name": "in", "command": "ls", "at": "A1"}, {"name": "r2", "at": "ZZZZ1", "rows": -1}]}`),248 []byte(`{"version": 2, "regions": [{"name": "a", "command": "$b", "at": "A1", "reads": ["b"]}, {"name": "b", "command": "$a", "at": "A3", "reads": ["a"], "sort": [{"column": 0}]}]}`),249 []byte(`{"version": 4, "sheets": [{"name": "S", "cells": {}, "regions": [{"name": "files", "at": "B2", "output": true}]},250 {"name": "Notebook", "tab": "notebook", "reactive": true, "cells": {}, "notebookCells": [251 {"kind": "note", "source": "# Files"}, {"source": "files = ls", "output": "[[name, size]; [a, 2kb]]"},252 {"source": "$files | nope", "error": "Command not found", "detail": "help: ls", "note": "x"}, {"source": "ls **/*", "unsaved": true}]}]}`),253 []byte(`{"version": 4, "sheets": [{"name": "N", "tab": "notebook", "cells": {}, "notebookCells": [{"kind": "table", "source": "x"}]}]}`),254 []byte(`{"version": 4, "sheets": [{"name": "N", "cells": {}, "notebookCells": [{"source": "x"}]}]}`),255 []byte(`{"version": 2, "cells": {}, "regions": [{"name": "o", "at": "A1", "output": true, "command": "ls"}, {"name": "p", "at": "A1", "output": true, "path": "p"}]}`),256 []byte(`{"version": 4, "sheets": [{"name": "Notebook", "tab": "notebook", "cells": {}}, {"name": "Shell 1", "notebook": true, "cells": {}, "regions": [{"name": "r1", "command": "ls", "at": "A1"}]}]}`),257 []byte(`null`), []byte(`[]`), []byte(`{"version": 2, "cells": {"A1": "1",}}`), []byte(`{"version": 2, "cells": {"A1"`),258 }259}260261// The streaming reader reads every seed as encoding/json reading the262// whole file did.263func TestStreamReadsAsWhole(t *testing.T) {264 for _, data := range streamSeeds(t) {265 sameAsWhole(t, data)266 }267}268269func FuzzRead(f *testing.F) {270 for _, data := range streamSeeds(f) {271 f.Add(data)272 }273 f.Fuzz(func(t *testing.T, data []byte) {274 if len(data) > 1<<16 {275 return276 }277 sameAsWhole(t, data)278 })279}280281// Cells are written as encodeCell writes each, in row-major order, one282// per line.283func TestWriteCellsAsEncoded(t *testing.T) {284 data := streamSeeds(t)[0]285 s, err := Read(bytes.NewReader(data))286 if err != nil {287 t.Fatal(err)288 }289 var addrs []Addr290 for a := range s.cells.keys() {291 addrs = append(addrs, a)292 }293 sortAddrs(addrs)294 var want strings.Builder295 want.WriteString(`"cells": {`)296 for i, a := range addrs {297 raw, err := encodeCell(s.cells.get(a).saved())298 if err != nil {299 t.Fatal(err)300 }301 if i > 0 {302 want.WriteByte(',')303 }304 fmt.Fprintf(&want, "\n %q: %s", a.String(), raw)305 }306 want.WriteString("\n }")307 if got := written(t, s.Book()); !strings.Contains(got, want.String()) || string(data) != got {308 t.Errorf("cells written as\n%s\nwant\n%s", got, want.String())309 }310 for _, a := range []Addr{{}, {Col: 25, Row: 9}, {Col: 26}, {Col: 701, Row: 99}, {Col: 702}, {Col: MaxCols - 1, Row: MaxRows - 1}} {311 if got := string(appendAddr(nil, a)); got != a.String() {312 t.Errorf("appendAddr %q, want %q", got, a.String())313 }314 }315}