1package fileio23import (4 "bytes"5 "context"6 "errors"7 "strings"8 "testing"910 "src.rest/cat/012/internal/sheet"11)1213// readParts imports a workbook of parts with the given limits.14func readParts(t testing.TB, parts map[string]string, lim xlsxLimits) (*Result, error) {15 t.Helper()16 data := zipParts(t, parts)17 bk, err := openXLSX(bytes.NewReader(data), int64(len(data)), lim)18 if err != nil {19 return nil, err20 }21 return bk.importBook(context.Background(), Options{})22}2324func TestXLSXReader(t *testing.T) {25 res, err := readParts(t, kitchenParts(), defaultXLSXLimits)26 if err != nil {27 t.Fatal(err)28 }29 book := res.Sheet.Book()30 if book.Len() != 3 || res.Sheet.Name() != "Refs" || res.Rows != 12 {31 t.Fatalf("%d sheets, showing %s, %d rows", book.Len(), res.Sheet.Name(), res.Rows)32 }33 data := book.Sheet(0)34 for cell, want := range map[string]string{35 "A1": "Region", "B1": "Rich text", "C1": "inline", "D1": "TRUE", "E1": "#N/A", "F1": `=A1&"x"`,36 "A3": "=SUM(A2:B2)",37 "B3": `=A3*2+$A$2+A$2+$A2+SUM(A1:A2)+"A1"`,38 "C3": `=B3*2+$A$2+B$2+$A2+SUM(B1:B2)+"A1"`,39 "D3": `=C3*2+$A$2+C$2+$A2+SUM(C1:C2)+"A1"`,40 "B4": `=A4*2+$A$2+A$2+$A3+SUM(A2:A3)+"A1"`,41 "C4": "9", "E5": "'=not a formula", "B6": "'123", "C6": "12",42 "A7": "7", "B7": `=CONCAT(A1,"a")`, "C7": "FALSE", "D7": "#REF!", "E7": "Region",43 "A8": "1E+300", "B8": "' 12", "C8": "abc", "D8": "", "E8": "5",44 "B9": "inA", "C9": "TRUE", "E9": "8",45 } {46 if g := input(data, addr(t, cell)); g != want {47 t.Errorf("Data!%s input %q, want %q", cell, g, want)48 }49 }50 if g := input(data, addr(t, "A1")); g != "Region" {51 t.Errorf("A1 %q", g)52 }53 for _, cell := range []string{"C2", "D8", "A6"} {54 if data.Cell(addr(t, cell)) == nil {55 t.Errorf("formatted blank %s left out", cell)56 }57 }58 for _, cell := range []string{"F5", "F8"} {59 if c := data.Cell(addr(t, cell)); c != nil {60 t.Errorf("%s kept: %+v", cell, c)61 }62 }63 checkKitchenStyles(t, data)64 for col, want := range map[int]int{0: 21, 1: 11, 2: 6, 3: 6, 4: 31, 5: 11, 200: 11} {65 if w := data.ColWidth(col); w != want {66 t.Errorf("Data column %d width %d, want %d", col, w, want)67 }68 }69 if w := book.Sheet(2).ColWidth(7); w != 13 {70 t.Errorf("Refs width %d", w)71 }72 if !book.Sheet(1).Hidden() || res.Sheet.Hidden() {73 t.Errorf("Hidden hidden %v, Refs hidden %v", book.Sheet(1).Hidden(), res.Sheet.Hidden())74 }75 if g := input(book.Sheet(1), addr(t, "B2")); g != "Region" {76 t.Errorf("Hidden!B2 %q", g)77 }78 if g := shown(book.Sheet(2), addr(t, "B1")); g != "24.50" {79 t.Errorf("Refs!B1 shows %q", g) // Data!A5*2 + SUM(Total), A2 a date: 2.580 }81 notes := strings.Join(res.Notes, "; ")82 for _, want := range []string{83 "2 named ranges left out, e.g. Local",84 "4 formulas kept as values, e.g. A7 =CUBEVALUE(1)",85 } {86 if !strings.Contains(notes, want) {87 t.Errorf("notes %q, want %q", notes, want)88 }89 }90}9192func checkKitchenStyles(t *testing.T, data *sheet.Sheet) {93 t.Helper()94 cell := func(s string) sheet.Cell {95 if c := data.Cell(addr(t, s)); c != nil {96 return *c97 }98 return sheet.Cell{}99 }100 if f := cell("A2").Format; f.Kind != sheet.FmtDate { // the row's style101 t.Errorf("A2 format %+v", f)102 }103 if f := cell("B2").Format; f != (sheet.Format{Kind: sheet.FmtCustom, Pattern: "0.000"}) {104 t.Errorf("B2 format %+v", f)105 }106 if c := cell("E2"); c.Format.Kind != sheet.FmtDate || c.Style.Bold || c.Style.Align != sheet.AlignLeft {107 t.Errorf("E2 %+v %+v (applyFont=0 isn't bold)", c.Format, c.Style)108 }109 if c := cell("A5"); !c.Style.Bold || c.Format != (sheet.Format{Kind: sheet.FmtNumber, Decimals: 2}) {110 t.Errorf("A5 %+v %+v", c.Format, c.Style)111 }112 if st := cell("E5").Style; !st.Strikethrough || st.Underline || st.Align != sheet.AlignAuto {113 t.Errorf("E5 style %+v", st)114 }115 if st := cell("A6").Style; st.Bold || st.Align != sheet.AlignCenter {116 t.Errorf("A6 style %+v", st)117 }118 if f := cell("C6").Format; !f.IsZero() {119 t.Errorf("C6 format %+v (id 5 is no built-in to excelize)", f)120 }121 if c := cell("D9"); c.Format.Kind != sheet.FmtDate || shown(data, addr(t, "D9")) != "1/30/1900" {122 t.Errorf("D9 %+v shows %s", c.Format, shown(data, addr(t, "D9")))123 }124}125126func TestShiftFormula(t *testing.T) {127 for _, tc := range []struct {128 in string129 dCol, dRow int130 want string131 }{132 {"A1+B2", 1, 1, "B2+C3"},133 {"$A$1+$A1+A$1", 2, 3, "$A$1+$A4+C$1"},134 {"SUM(A1:B2)*LOG10(A1)", 0, 1, "SUM(A2:B3)*LOG10(A2)"},135 {"Sheet2!A1+'My sheet'!B$2", 1, 1, "Sheet2!B2+'My sheet'!C$2"},136 {"SUM(A:A,1:1,$B:$B,$2:$2)", 1, 1, "SUM(B:B,2:2,$B:$B,$2:$2)"},137 {`"A1"&A1&Total&TRUE&1E+5&0.5`, 1, 0, `"A1"&B1&Total&TRUE&1E+5&0.5`},138 {"A1-1", -1, 0, "#REF!-1"},139 {"XFD1+A1048576", 1, 1, "#REF!+#REF!"},140 {"Table1[[#This Row],[A1]]+A1", 1, 0, "Table1[[#This Row],[A1]]+B1"},141 {"_xlfn.XLOOKUP(A1,B:B,C:C)", 0, 5, "_xlfn.XLOOKUP(A6,B:B,C:C)"},142 {"#REF!+A1", 0, 1, "#REF!+A2"},143 {"A1 B1", 25, 0, "Z1 AA1"},144 {"ZZ1", 1, 0, "AAA1"},145 } {146 if got := shiftFormula(tc.in, tc.dCol, tc.dRow); got != tc.want {147 t.Errorf("shiftFormula(%q, %d, %d) = %q, want %q", tc.in, tc.dCol, tc.dRow, got, tc.want)148 }149 }150}151152// Things excelize reads wrongly or not at all: shared formulas referring153// to another sheet move, the 1904 date system, t="d" ISO dates, and154// columns past 1-2-3's IV.155func TestXLSXBeyondExcelize(t *testing.T) {156 parts := oneSheetParts(`<workbookPr date1904="1"/>`,157 `<row r="1"><c r="A1"><v>1</v></c><c r="B1"><f t="shared" ref="B1:B2" si="0">Other!A1+A1</f></c><c r="C1" s="8"><v>0</v></c><c r="D1" t="d"><v>2026-09-26T14:30:00</v></c><c r="E1" t="d"><v>2026-09-26</v></c><c r="F1" t="d"><v>soon</v></c><c r="IZ1"><v>1</v></c></row>`+158 `<row r="2"><c r="B2"><f t="shared" si="0"/></c></row>`)159 res, err := readParts(t, parts, defaultXLSXLimits)160 if err != nil {161 t.Fatal(err)162 }163 s := res.Sheet164 if g := input(s, addr(t, "B2")); g != "=Other!A2+A2" {165 t.Errorf("B2 %q", g)166 }167 for cell, want := range map[string]string{"C1": "1/1/1904", "D1": "9/26/2026 14:30:00", "E1": "9/26/2026", "F1": "soon"} {168 if g := shown(s, addr(t, cell)); g != want {169 t.Errorf("%s shows %q, want %q", cell, g, want)170 }171 }172 if g := input(s, addr(t, "IZ1")); g != "1" || len(res.Notes) != 0 { // past 1-2-3's IV, inside the grid173 t.Errorf("IZ1 %q, notes %q", g, res.Notes)174 }175}176177func TestXLSXLimits(t *testing.T) {178 small := defaultXLSXLimits179 small.part, small.total, small.slack, small.token, small.ratio, small.entries, small.shared = 1<<20, 2<<20, 64<<10, 64<<10, 100, 50, 1<<20180 bomb := oneSheetParts("", strings.Repeat(`<row><c><v>0</v></c></row>`, 100_000))181 var rows, text strings.Builder182 for i := range 60_000 {183 rows.WriteString(`<row><c><v>` + itoa(i*7919%100_003) + `</v></c></row>`)184 text.WriteString(itoa(i * 7919 % 100_003))185 }186 big := oneSheetParts("", rows.String())187 long := oneSheetParts("", `<row><c t="inlineStr"><is><t>`+text.String()+`</t></is></c></row>`)188 deep := oneSheetParts("", `<row><c><v>1</v><x>`+strings.Repeat("<y>", 300)+strings.Repeat("</y>", 300)+`</x></c></row>`)189 many := kitchenParts()190 for i := range small.entries {191 many["xl/media/image"+itoa(i)+".png"] = ""192 }193 for name, tc := range map[string]struct {194 parts map[string]string195 want string196 ratio int64 // instead of small.ratio197 }{198 "zip bomb": {bomb, "compressed over 100 to 1, like a zip bomb", 0},199 "big part": {big, "unpacks to more than 1 MB", 0},200 "long text": {long, "a tag or text over", 0},201 "deep": {deep, "nests elements more than 256 deep", 0},202 "many files": {many, "files, more than 50", 0},203 "row past Excel": {oneSheetParts("", `<row r="1048577"><c><v>1</v></c></row>`), "past Excel's last row", 0},204 "col past Excel": {oneSheetParts("", `<row><c r="XFE1"><v>1</v></c></row>`), `"XFE1" isn't a cell reference`, 0},205 "bad reference": {oneSheetParts("", `<row><c r="A0"><v>1</v></c></row>`), `"A0" isn't a cell reference`, 0},206 "too many cells": {oneSheetParts("", `<row>`+strings.Repeat(`<c r="A1"/>`, excelCols+1)+`</row>`), "more than 16,384 cells", 1 << 30},207 "unsafe name": {mergeParts(kitchenParts(), "../evil.xml", "x"), "unsafe name", 0},208 "absolute name": {mergeParts(kitchenParts(), "/etc/passwd", "x"), "unsafe name", 0},209 "duplicate": {mergeParts(kitchenParts(), "XL/Workbook.xml", "x"), "twice", 0},210 "entity": {oneSheetParts("", `<row><c t="inlineStr"><is><t>&bomb;</t></is></c></row>`), "invalid character entity &bomb;", 0},211 "shared formulas": {oneSheetParts("", `<row r="1"><c r="A1"><f t="shared" ref="A1:A9" si="0">`+strings.Repeat("B1+", 1000)+`1</f></c></row>`+212 strings.Repeat(`<row><c r="A2"><f t="shared" si="0"/></c></row>`, 400)), "shared formulas expand to more than 1 MB", 0},213 "not xml": {oneSheetParts("", `<row><c>`), "closed by </sheetData>", 0},214 } {215 t.Run(name, func(t *testing.T) {216 lim := small217 if tc.ratio > 0 {218 lim.ratio = tc.ratio219 }220 _, err := readParts(t, tc.parts, lim)221 if err == nil || !strings.Contains(err.Error(), tc.want) {222 t.Fatalf("error %v, want %q", err, tc.want)223 }224 })225 }226 // The limits error wraps errXLSXLimit.227 if _, err := readParts(t, bomb, small); !errors.Is(err, errXLSXLimit) {228 t.Errorf("%v isn't a limits error", err)229 }230 // A DTD declaring entities is refused, not expanded.231 dtd := oneSheetParts("", "")232 dtd["xl/sharedStrings.xml"] = `<?xml version="1.0"?><!DOCTYPE sst [<!ENTITY a "aaaaaaaaaa"><!ENTITY b "&a;&a;&a;&a;">]><sst ` + mainNS + `><si><t>&b;</t></si></sst>`233 if _, err := readParts(t, dtd, small); err == nil || !strings.Contains(err.Error(), "invalid character entity &b;") {234 t.Errorf("entity: %v", err)235 }236}237238func mergeParts(p map[string]string, name, content string) map[string]string {239 p[name] = content240 return p241}242243// tableParts is a workbook of one sheet with a table over A1:B3 and a244// formula reading it.245func tableParts() map[string]string {246 p := oneSheetParts("", `<row r="1"><c r="A1" t="inlineStr"><is><t>Item</t></is></c><c r="B1" t="inlineStr"><is><t>Cost</t></is></c></row>`+247 `<row r="2"><c r="B2"><v>5</v></c><c r="C2"><f>SUM(T[Cost])+T[[#This Row],[Cost]]</f></c></row>`)248 p["xl/worksheets/sheet1.xml"] = strings.Replace(p["xl/worksheets/sheet1.xml"], `</worksheet>`, `<tableParts count="1"><tablePart r:id="rT"/></tableParts></worksheet>`, 1)249 p["xl/worksheets/_rels/sheet1.xml.rels"] = `<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rT" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/table" Target="../tables/table1.xml"/></Relationships>`250 p["xl/tables/table1.xml"] = `<table ` + mainNS + ` id="1" name="T" displayName="T" ref="A1:B3" totalsRowCount="1"><autoFilter ref="A1:B2"/><tableColumns count="2"><tableColumn id="1" name="Item"/><tableColumn id="2" name="Cost"/></tableColumns><tableStyleInfo name="TableStyleLight1" showRowStripes="1"/></table>`251 return p252}253254// FuzzReadXLSX feeds the reader arbitrary files: it must fail cleanly,255// never panic or run past its limits.256func FuzzReadXLSX(f *testing.F) {257 f.Add(zipParts(f, kitchenParts()))258 f.Add(zipParts(f, oneSheetParts(`<workbookPr date1904="1"/>`, `<row><c r="B2" t="d"><v>2026-01-01</v></c></row>`)))259 f.Add(zipParts(f, tableParts()))260 f.Fuzz(func(t *testing.T, data []byte) {261 lim := defaultXLSXLimits262 lim.part, lim.total = 4<<20, 8<<20263 bk, err := openXLSX(bytes.NewReader(data), int64(len(data)), lim)264 if err != nil {265 return266 }267 bk.importBook(context.Background(), Options{})268 })269}270271// FuzzReadXLSXParts fuzzes the XML of a worksheet, the shared strings272// and the styles, zipped into a workbook, where the zip's checksums273// would stop most mutations of a whole file.274func FuzzReadXLSXParts(f *testing.F) {275 k := kitchenParts()276 f.Add(k["xl/worksheets/sheet1.xml"], k["xl/sharedStrings.xml"], k["xl/styles.xml"])277 f.Add(`<worksheet><sheetData><row r="3"><c r="C3"><f t="shared" ref="C3:C9" si="0">A1:B$2+'x y'!A1</f></c></row><row><c r="C4"><f t="shared" si="0"/></c></row></sheetData></worksheet>`, "", "")278 f.Fuzz(func(t *testing.T, ws, sst, styles string) {279 p := kitchenParts()280 p["xl/worksheets/sheet1.xml"], p["xl/sharedStrings.xml"], p["xl/styles.xml"] = ws, sst, styles281 lim := defaultXLSXLimits282 lim.part, lim.total = 4<<20, 8<<20283 readParts(t, p, lim)284 })285}