1package fileio23import (4 "bytes"5 "context"6 "encoding/binary"7 "math"8 "os"9 "path/filepath"10 "strings"11 "testing"1213 "src.rest/cat/012/internal/sheet"14)1516// wk1 encodes a worksheet for tests: 1-2-3 files don't ship with 012.17type wk1 struct{ bytes.Buffer }1819func newWK1(version uint16) *wk1 {20 w := &wk1{}21 w.record(wkBOF, le16(version))22 return w23}2425func le16(v uint16) []byte { return binary.LittleEndian.AppendUint16(nil, v) }2627func le64(f float64) []byte { return binary.LittleEndian.AppendUint64(nil, math.Float64bits(f)) }2829func (w *wk1) record(op uint16, payload ...[]byte) {30 body := bytes.Join(payload, nil)31 w.Write(le16(op))32 w.Write(le16(uint16(len(body))))33 w.Write(body)34}3536func cellHead(format byte, a string) []byte {37 ad, _ := sheet.ParseAddr(a)38 return append([]byte{format}, append(le16(uint16(ad.Col)), le16(uint16(ad.Row))...)...)39}4041func (w *wk1) label(a, text string) { w.record(wkLabel, cellHead(0xFF, a), []byte(text+"\x00")) }42func (w *wk1) number(a string, format byte, v float64) {43 w.record(wkNumber, cellHead(format, a), le64(v))44}45func (w *wk1) integer(a string, v int16) { w.record(wkInteger, cellHead(0xFF, a), le16(uint16(v))) }46func (w *wk1) formula(a string, format byte, cached float64, code []byte) {47 w.record(wkFormula, cellHead(format, a), le64(cached), le16(uint16(len(code))), code)48}49func (w *wk1) colWidth(col uint16, width byte) { w.record(wkColW, le16(col), []byte{width}) }50func (w *wk1) end() []byte { w.record(wkEOF); return w.Bytes() }5152// Formula bytecode.53func fnum(v float64) []byte { return append([]byte{0x00}, le64(v)...) }54func fint(v int16) []byte { return append([]byte{0x05}, le16(uint16(v))...) }55func fstr(s string) []byte { return append(append([]byte{0x06}, s...), 0) }5657// fref encodes a reference to target from the formula cell at; relative58// coordinates are stored as offsets with bit 15 set.59func fref(at, target string, absCol, absRow bool) []byte {60 a, _ := sheet.ParseAddr(at)61 t, _ := sheet.ParseAddr(target)62 col := uint16(t.Col)63 if !absCol {64 col = 0x8000 | uint16(uint8(int8(t.Col-a.Col)))65 }66 row := uint16(t.Row)67 if !absRow {68 row = 0x8000 | uint16(t.Row-a.Row)&0x1FFF69 }70 return append(le16(col), le16(row)...)71}72func fcell(at, target string) []byte { return append([]byte{0x01}, fref(at, target, false, false)...) }73func frange(at, from, to string) []byte {74 return append(append([]byte{0x02}, fref(at, from, false, false)...), fref(at, to, false, false)...)75}76func code(parts ...[]byte) []byte { return append(bytes.Join(parts, nil), 0x03) }7778func TestWK1Formulas(t *testing.T) {79 op := func(b byte) []byte { return []byte{b} }80 va := func(b byte, n byte) []byte { return []byte{b, n} }81 for _, tc := range []struct {82 name string83 code []byte84 want string85 ok bool86 }{87 {"reference", code(fcell("C3", "A1")), "A1", true},88 {"absolute", code(append([]byte{0x01}, fref("C3", "B2", true, true)...)), "$B$2", true},89 {"mixed", code(append([]byte{0x01}, fref("C3", "B2", true, false)...)), "$B2", true},90 {"below", code(fcell("A1", "D9")), "D9", true},91 {"precedence", code(fcell("C3", "A1"), fnum(2), op(0x09), fint(3), op(0x0B)), "(A1+2)*3", true},92 {"no extra parentheses", code(fcell("C3", "A1"), fnum(2), fint(3), op(0x0B), op(0x09)), "A1+2*3", true},93 {"left associative", code(fint(1), fint(2), fint(3), op(0x0A), op(0x0A)), "1-(2-3)", true},94 {"written parentheses", code(fint(1), fint(2), op(0x09), op(0x04)), "(1+2)", true},95 {"power and negation", code(fint(2), op(0x08), fint(2), op(0x0D)), "-2^2", true},96 {"negated power", code(fint(2), fint(2), op(0x0D), op(0x08)), "-(2^2)", true},97 {"comparison and logic", code(fcell("C3", "A1"), fint(1), op(0x13), fcell("C3", "B1"), fint(2), op(0x12), op(0x14)), "A1>1#AND#B1<2", true},98 {"not", code(fcell("C3", "A1"), op(0x16)), "#NOT#A1", true},99 {"strings", code(fstr(`say "hi"`), fcell("C3", "A1"), op(0x18)), `"say ""hi"""&A1`, true},100 {"sum", code(frange("C3", "A1", "A2"), fcell("C3", "B1"), va(0x50, 2)), "SUM(A1:A2,B1)", true},101 {"avg count min max", code(frange("C3", "A1", "B2"), va(0x51, 1), frange("C3", "A1", "B2"), va(0x52, 1), op(0x09)), "AVERAGE(A1:B2)+COUNTA(A1:B2)", true},102 {"if", code(fcell("C3", "A1"), fint(0), op(0x13), fstr("yes"), fstr("no"), op(0x3B)), `IF(A1>0,"yes","no")`, true},103 {"round", code(fnum(2.345), fint(2), op(0x3F)), "ROUND(2.345,2)", true},104 {"int truncates", code(fnum(-2.5), op(0x22)), "TRUNC(-2.5)", true},105 {"pi", code(op(0x26), fint(2), op(0x0B)), "PI()*2", true},106 {"pmt reorders", code(fint(1000), fnum(0.01), fint(12), op(0x38)), "PMT(0.01,12,-1000)", true},107 {"choose counts from 0", code(fcell("C3", "A1"), fint(10), fint(20), va(0x30, 3)), "CHOOSE((A1)+1,10,20)", true},108 {"vlookup offset", code(fcell("C3", "A1"), frange("C3", "D1", "E9"), fint(1), op(0x55)), "VLOOKUP(A1,D1:E9,2)", true},109 {"date years since 1900", code(fint(126), fint(9), fint(26), op(0x36)), "DATE(1900+126,9,26)", true},110 {"year", code(fcell("C3", "A1"), op(0x3E)), "(YEAR(A1)-1900)", true},111 {"no equivalent", code(fcell("C3", "A1"), fint(2), op(0x48)), "@STRING(A1,2)", false},112 {"database function", code(frange("C3", "A1", "B9"), fint(1), frange("C3", "D1", "D2"), va(0x5B, 3)), "@DSUM(A1:B9,1,D1:D2)", false},113 {"unknown opcode", code([]byte{0xEE}), "", false},114 {"stack underflow", code(op(0x09)), "", false},115 {"cut off", []byte{0x00, 1, 2}, "", false},116 {"off the sheet", code(fcell("A1", "A1")[:1], []byte{0xFB, 0x80, 0x00, 0x80}), "#REF!", false},117 } {118 t.Run(tc.name, func(t *testing.T) {119 got, ok := lotusFormula(tc.code, addr(t, "C3"), wk1File)120 if tc.name == "below" {121 got, ok = lotusFormula(tc.code, addr(t, "A1"), wk1File)122 }123 if got != tc.want || ok != tc.ok {124 t.Errorf("= %q, %v; want %q, %v", got, ok, tc.want, tc.ok)125 }126 if ok {127 if _, err := sheet.Parse("=" + got); err != nil {128 t.Errorf("%q doesn't parse: %v", got, err)129 }130 }131 })132 }133}134135func TestWK1Import(t *testing.T) {136 w := newWK1(0x0406)137 w.record(wkRange, le16(0), le16(0), le16(3), le16(6))138 w.colWidth(0, 14)139 w.label("A1", "'Budget 1986")140 w.label("B1", `"Right`)141 w.label("C1", "^Mid")142 w.label("D1", `\-`)143 w.label("A2", "'123")144 w.label("A3", "caf\xe9")145 w.integer("B2", 42)146 w.number("B3", 0x22, 1450.5) // currency, 2 decimals147 w.number("B4", 0x31, 0.125) // percent, 1 decimal148 w.number("B5", 0x72, 31679) // DD-MMM-YY149 w.number("B6", 0x02, 3.14159) // fixed, 2 decimals150 w.number("C2", 0x42, 1234567) // comma, 2 decimals151 w.formula("C3", 0x22, 1492.5, code(fcell("C3", "B2"), fcell("C3", "B3"), op9())) // =B2+B3152 w.formula("C4", 0xFF, 7, code(fcell("C4", "A1"), fint(2), []byte{0x48})) // @STRING: kept153 w.record(wkString, cellHead(0xFF, "C4"), []byte("1,986.00\x00"))154 w.formula("C5", 0xFF, 5, code(fint(5), []byte{0x7A})) // unknown opcode: kept155 w.record(wkName, make([]byte, 24))156 name := filepath.Join(t.TempDir(), "budget.wk1")157 os.WriteFile(name, w.end(), 0o644)158159 res, err := Import(context.Background(), name, Options{Progress: NewProgress()})160 if err != nil {161 t.Fatal(err)162 }163 s := res.Sheet164 for cell, want := range map[string]string{165 "A1": "Budget 1986", "B1": "Right", "C1": "Mid", "D1": "---------", "A2": "123", "A3": "café",166 "B2": "42", "B3": "$1,450.50", "B4": "12.5%", "B5": "24-Sep-86", "B6": "3.14", "C2": "1,234,567.00",167 "C3": "$1,492.50", "C4": "1,986.00", "C5": "5",168 } {169 if g := shown(s, addr(t, cell)); g != want {170 t.Errorf("%s shows %q, want %q", cell, g, want)171 }172 }173 if g := input(s, addr(t, "C3")); g != "=B2+B3" {174 t.Errorf("C3 input %q", g)175 }176 if g := input(s, addr(t, "A2")); g != "'123" {177 t.Errorf("A2 input %q", g)178 }179 if al := s.Cell(addr(t, "B1")).Style.Align; al != sheet.AlignRight {180 t.Errorf("B1 align %v", al)181 }182 if al := s.Cell(addr(t, "C1")).Style.Align; al != sheet.AlignCenter {183 t.Errorf("C1 align %v", al)184 }185 if w := s.ColWidth(0); w != 15 {186 t.Errorf("A width %d", w)187 }188 notes := strings.Join(res.Notes, "; ")189 for _, want := range []string{"1 named range not imported", "2 formulas kept as values, e.g. C4 @STRING(A1,2)"} {190 if !strings.Contains(notes, want) {191 t.Errorf("notes %q, want %q", notes, want)192 }193 }194}195196func op9() []byte { return []byte{0x09} }197198func TestWK1Errors(t *testing.T) {199 for _, tc := range []struct {200 name string201 data []byte202 want string203 }{204 {"empty", nil, "the file is empty"},205 {"not lotus", []byte("PK\x03\x04 a zip file"), "doesn't start with a BOF record"},206 {"cut off", append(newWK1(0x0406).Bytes(), 0x0F, 0x00, 0x20, 0x00, 1, 2), "record 2 (opcode 0x0F) is cut off"},207 } {208 t.Run(tc.name, func(t *testing.T) {209 _, _, _, err := readWK1(context.Background(), bytes.NewReader(tc.data), func(int) {})210 if err == nil || !strings.Contains(err.Error(), tc.want) {211 t.Errorf("error %v, want %q", err, tc.want)212 }213 })214 }215 // A file without an EOF record keeps what was read.216 w := newWK1(0x0404)217 w.integer("A1", 7)218 s, _, _, err := readWK1(context.Background(), bytes.NewReader(w.Bytes()), func(int) {})219 if err != nil || shown(s, addr(t, "A1")) != "7" {220 t.Errorf("no EOF: %v", err)221 }222}223224func FuzzReadWK1(f *testing.F) {225 w := newWK1(0x0406)226 w.label("A1", "'x")227 w.number("B1", 0x22, 2)228 w.formula("C1", 0xFF, 3, code(fcell("C1", "B1"), fint(1), op9()))229 w.formula("C2", 0xFF, 3, code(frange("C2", "A1", "B1"), []byte{0x50, 1}))230 f.Add(w.end())231 f.Add(newWK1(0x0404).end())232 f.Fuzz(func(t *testing.T, data []byte) {233 readWK1(context.Background(), bytes.NewReader(data), func(int) {})234 lotusFormula(data, sheet.Addr{Col: 3, Row: 3}, wk1File)235 })236}