1package fileio23import (4 "archive/zip"5 "context"6 "io"7 "path/filepath"8 "strings"9 "testing"1011 "github.com/xuri/excelize/v2"12)1314func TestExcelArrayFormulas(t *testing.T) {15 for _, tc := range []struct {16 in, want string17 arrays bool18 ok bool19 }{20 {"=SEQUENCE(3)", "_xlfn.SEQUENCE(3)", true, true},21 {"=FILTER(A1:A9, B1:B9>2)", "_xlfn._xlws.FILTER(A1:A9,B1:B9>2)", true, true},22 {"=sort(unique(A:A))", "_xlfn._xlws.SORT(_xlfn.UNIQUE(A:A))", true, true},23 {"=ARRAYFORMULA(A1:A3*2)", "A1:A3*2", true, true},24 {"=SUM(ARRAYFORMULA(A1:A3*2))", "", false, false},25 {"=LET(x, 2, x*3)", "_xlfn.LET(_xlpm.x,2,_xlpm.x*3)", true, true},26 {"=LET(f, LAMBDA(v, v+1), f(2))", "_xlfn.LET(_xlpm.f,_xlfn.LAMBDA(_xlpm.v,_xlpm.v+1),_xlpm.f(2))", true, true},27 {"=MAP(A1:A3, LAMBDA(v, v*2))", "_xlfn.MAP(A1:A3,_xlfn.LAMBDA(_xlpm.v,_xlpm.v*2))", true, true},28 {"={1,-2;\"a\",TRUE}", `{1,-2;"a",TRUE}`, true, true},29 {"={A1,B1}", "", false, false},30 {"=SUM({1,2})", "SUM({1,2})", true, true},31 {"=SORTN(A1:A9)", "", false, false},32 {`=SPLIT(A1, ",")`, "", false, false},33 {`=REGEXREPLACE(A1, "a+", "b")`, `_xlfn.REGEXREPLACE(A1, "a+", "b")`, false, true},34 {`=REGEXMATCH(A1, "a")`, "", false, false},35 {"=SUM(A1:A3*2)", "SUM(A1:A3*2)", false, true},36 } {37 got, arrays, ok := excelFormula(tc.in, nil)38 if ok != tc.ok || ok && (got != tc.want || arrays != tc.arrays) {39 t.Errorf("excelFormula(%q) = %q, %v, %v, want %q, %v, %v", tc.in, got, arrays, ok, tc.want, tc.arrays, tc.ok)40 }41 }42 for in, want := range map[string]string{43 "_xlfn._xlws.FILTER(A1:A9,B1:B9>2)": "=FILTER(A1:A9,B1:B9>2)",44 "_xlfn.LET(_xlpm.x,2,_xlpm.x*3)": "=LET(x,2,x*3)",45 "_xlfn.MAP(A1:A3,_xlfn.LAMBDA(_xlpm.v,1))": "=MAP(A1:A3,LAMBDA(v,1))",46 } {47 if got := fromExcelFormula(in); got != want {48 t.Errorf("fromExcelFormula(%q) = %q, want %q", in, got, want)49 }50 }51}5253// A spill goes out as Excel's dynamic array formula over the cells it54// spills into, which hold its values, and comes back as the formula,55// spilling again rather than blocked by its own values.56func TestXLSXSpill(t *testing.T) {57 src := build(t, map[string]string{"A1": "3", "A2": "1", "A3": "2", "C1": "=SORT(A1:A3)", "D1": "=SUM(A1:A3)", "E1": "=SPLIT(\"x,y\", \",\")"})58 name := filepath.Join(t.TempDir(), "spill.xlsx")59 res, err := Export(context.Background(), name, XLSX, SnapBook(src), ExportOptions{})60 if err != nil {61 t.Fatal(err)62 }63 if len(res.Notes) != 1 || !strings.Contains(res.Notes[0], "e.g. E1") {64 t.Errorf("notes %q", res.Notes)65 }66 parts := zipText(t, name)67 ws := parts["xl/worksheets/sheet1.xml"]68 for _, want := range []string{69 `<c r="C1" cm="1"><f t="array" ref="C1:C3">_xlfn._xlws.SORT(A1:A3)</f><v>1</v></c>`,70 `<c r="C2"><v>2</v></c>`, `<c r="C3"><v>3</v></c>`,71 `<c r="D1"><f>SUM(A1:A3)</f><v>6</v></c>`,72 `<c r="E1" t="inlineStr"><is><t xml:space="preserve">x</t></is></c>`,73 } {74 if !strings.Contains(ws, want) {75 t.Errorf("sheet1.xml lacks %s:\n%s", want, ws)76 }77 }78 if !strings.Contains(parts["xl/metadata.xml"], `fDynamic="1"`) ||79 !strings.Contains(parts["[Content_Types].xml"], `/xl/metadata.xml`) ||80 !strings.Contains(parts["xl/_rels/workbook.xml.rels"], `Target="metadata.xml"`) {81 t.Error("the dynamic array metadata part is missing or not linked")82 }83 x, err := excelize.OpenFile(name)84 if err != nil {85 t.Fatal(err)86 }87 if v, _ := x.GetCellValue("Sheet1", "C3"); v != "3" {88 t.Errorf("excelize reads C3 as %q", v)89 }90 x.Close()9192 got, err := Import(context.Background(), name, Options{})93 if err != nil {94 t.Fatal(err)95 }96 s := got.Sheet97 if in := input(s, addr(t, "C1")); in != "=SORT(A1:A3)" {98 t.Errorf("C1 read back as %q", in)99 }100 for cell, want := range map[string]string{"C1": "1", "C2": "2", "C3": "3"} {101 if v := shown(s, addr(t, cell)); v != want {102 t.Errorf("%s shows %q, want %q", cell, v, want)103 }104 }105 if !s.Cell(addr(t, "C2")).Spilled() {106 t.Error("C2 came back as a value, not spilled")107 }108}109110// A workbook without arrays has no metadata part.111func TestXLSXNoArrays(t *testing.T) {112 name := filepath.Join(t.TempDir(), "plain.xlsx")113 if _, err := Export(context.Background(), name, XLSX, SnapBook(build(t, map[string]string{"A1": "=1+1"})), ExportOptions{}); err != nil {114 t.Fatal(err)115 }116 if _, ok := zipText(t, name)["xl/metadata.xml"]; ok {117 t.Error("metadata.xml written without dynamic arrays")118 }119}120121// zipText reads every part of a zip file.122func zipText(t *testing.T, name string) map[string]string {123 t.Helper()124 zr, err := zip.OpenReader(name)125 if err != nil {126 t.Fatal(err)127 }128 defer zr.Close()129 out := map[string]string{}130 for _, f := range zr.File {131 rc, err := f.Open()132 if err != nil {133 t.Fatal(err)134 }135 b, err := io.ReadAll(rc)136 rc.Close()137 if err != nil {138 t.Fatal(err)139 }140 out[f.Name] = string(b)141 }142 return out143}