1package fileio23import (4 "context"5 "path/filepath"6 "reflect"7 "slices"8 "strings"9 "testing"1011 "github.com/xuri/excelize/v2"1213 "src.rest/cat/012/internal/sheet"14)1516// months is a small table with a header row: months, amounts and notes.17func months(t *testing.T) *sheet.Sheet {18 return build(t, map[string]string{19 "A1": "Month", "B1": "Amount", "C1": "Note",20 "A2": "Jan", "B2": "$1,200", "C2": "rent*",21 "A3": "Feb", "B3": "80", "C3": "food",22 "A4": "Mar", "B4": "1450", "C4": "",23 "A5": "Apr", "B5": "=B4*2", "C5": "Fees",24 })25}2627// exportImport writes s as XLSX and reads it back.28func exportImport(t *testing.T, s *sheet.Sheet) (string, *Result) {29 t.Helper()30 name := filepath.Join(t.TempDir(), "view.xlsx")31 if _, err := Export(context.Background(), name, XLSX, SnapBook(s), ExportOptions{}); err != nil {32 t.Fatal(err)33 }34 got, err := Import(context.Background(), name, Options{})35 if err != nil {36 t.Fatal(err)37 }38 return name, got39}4041func hiddenRows(s *sheet.Sheet) []int {42 var out []int43 for r := range 10 {44 if s.RowHidden(r) {45 out = append(out, r)46 }47 }48 return out49}5051// Frozen panes and a filter go out as Excel's and come back the same.52func TestXLSXFrozenAndFilterRoundTrip(t *testing.T) {53 src := months(t)54 src.SetFrozen(1, 2)55 src.CreateFilter(sheet.NewRect(addr(t, "A1"), addr(t, "C5")))56 src.FilterColumn(0, sheet.Criteria{Hidden: []string{"Feb"}})57 src.FilterColumn(1, sheet.Criteria{Cond: sheet.Condition{Op: sheet.CondLess, Arg: "2000"}})58 want := []int{2, 4} // Feb, and Apr's 290059 if got := hiddenRows(src); !slices.Equal(got, want) {60 t.Fatalf("hidden before export: %v", got)61 }62 name, got := exportImport(t, src)6364 // excelize reads what Excel would.65 x, err := excelize.OpenFile(name)66 if err != nil {67 t.Fatal(err)68 }69 defer x.Close()70 if p, _ := x.GetPanes("Sheet1"); !p.Freeze || p.XSplit != 2 || p.YSplit != 1 || p.TopLeftCell != "C2" || p.ActivePane != "bottomRight" {71 t.Errorf("panes %+v", p)72 }73 for row, shown := range map[int]bool{1: true, 2: true, 3: false, 4: true, 5: false} {74 if v, _ := x.GetRowVisible("Sheet1", row); v != shown {75 t.Errorf("row %d visible %v in Excel", row, v)76 }77 }78 db := false79 for _, dn := range x.GetDefinedName() {80 db = db || dn.Name == "_xlnm._FilterDatabase" && dn.RefersTo == "Sheet1!$A$1:$C$5" && dn.Scope == "Sheet1"81 }82 if !db {83 t.Errorf("no _FilterDatabase name: %+v", x.GetDefinedName())84 }85 afs, _, _, err := referenceAutoFilters(name)86 if err != nil {87 t.Fatal(err)88 }89 wantAF := &xlsxAutoFilter{ref: "A1:C5", cols: []xlsxFilterColumn{90 {col: 0, filters: true, values: []string{"Apr", "Jan", "Mar"}},91 {col: 1, custom: []xlsxCustomFilter{{op: "lessThan", val: "2000"}}},92 }}93 if !reflect.DeepEqual(afs[0], wantAF) {94 t.Errorf("autoFilter %+v, want %+v", afs[0], wantAF)95 }9697 s := got.Sheet98 if r, c := s.Frozen(); r != 1 || c != 2 {99 t.Errorf("frozen %d rows, %d cols", r, c)100 }101 if !reflect.DeepEqual(s.Filter(), src.Filter()) {102 t.Errorf("filter %+v, want %+v", s.Filter(), src.Filter())103 }104 if h := hiddenRows(s); !slices.Equal(h, want) {105 t.Errorf("hidden after import: %v", h)106 }107 if s.Book().CanUndo() {108 t.Error("import left an undo step")109 }110}111112// Each condition goes out as Excel's custom filter (or its blanks113// filter) and comes back hiding the same rows.114func TestXLSXFilterConditions(t *testing.T) {115 for _, c := range []sheet.Condition{116 {Op: sheet.CondEmpty}, {Op: sheet.CondNotEmpty},117 {Op: sheet.CondContains, Arg: "E"}, {Op: sheet.CondContains, Arg: "t*"}, {Op: sheet.CondNotContains, Arg: "oo"},118 {Op: sheet.CondStartsWith, Arg: "rent"}, {Op: sheet.CondEndsWith, Arg: "s"}, {Op: sheet.CondExactly, Arg: "FOOD"},119 {Op: sheet.CondGreater, Arg: "100"}, {Op: sheet.CondGreaterEq, Arg: "$1,450"}, {Op: sheet.CondLess, Arg: "1200"},120 {Op: sheet.CondLessEq, Arg: "80"}, {Op: sheet.CondEqual, Arg: "1450"}, {Op: sheet.CondNotEqual, Arg: "80"},121 } {122 col := 2 // text conditions on the notes, the others on the amounts123 if c.Op >= sheet.CondGreater {124 col = 1125 }126 src := months(t)127 src.CreateFilter(sheet.NewRect(addr(t, "A1"), addr(t, "C5")))128 src.FilterColumn(col, sheet.Criteria{Cond: c})129 want := hiddenRows(src)130 _, got := exportImport(t, src)131 s := got.Sheet132 cr := s.Filter().Cols[col]133 if cr.Cond.Op != c.Op || len(cr.Hidden) > 0 {134 t.Errorf("%s %q came back as %+v", c.Op, c.Arg, cr)135 }136 if h := hiddenRows(s); !slices.Equal(h, want) {137 t.Errorf("%s %q hides %v, want %v", c.Op, c.Arg, h, want)138 }139 if len(got.Notes) > 0 {140 t.Errorf("%s: notes %q", c.Op, got.Notes)141 }142 }143}144145// A values list with a condition goes out as the values both let146// through; criteria Excel has and 012 hasn't are left out with a note.147func TestXLSXFilterLimits(t *testing.T) {148 src := months(t)149 src.CreateFilter(sheet.NewRect(addr(t, "A1"), addr(t, "C5")))150 src.FilterColumn(0, sheet.Criteria{Hidden: []string{"Feb"}, Cond: sheet.Condition{Op: sheet.CondStartsWith, Arg: "a"}})151 _, got := exportImport(t, src)152 if cr := got.Sheet.Filter().Cols[0]; !slices.Equal(cr.Hidden, []string{"Feb", "Jan", "Mar"}) {153 t.Errorf("values and condition came back as %+v", cr)154 }155156 dir := t.TempDir()157 x := excelize.NewFile()158 for i, row := range [][]any{{"Name", "Score"}, {"a", 1}, {"b", 7}, {"c", 3}} {159 x.SetSheetRow("Sheet1", "A"+itoa(i+1), &row)160 }161 x.AutoFilter("Sheet1", "A1:B4", []excelize.AutoFilterOptions{{Column: "B", Expression: "x > 1 and x < 5"}, {Column: "A", Expression: "x == a?"}})162 name := filepath.Join(dir, "limits.xlsx")163 if err := x.SaveAs(name); err != nil {164 t.Fatal(err)165 }166 res, err := Import(context.Background(), name, Options{})167 if err != nil {168 t.Fatal(err)169 }170 if f := res.Sheet.Filter(); f == nil || len(f.Cols) != 0 || f.Range != sheet.NewRect(addr(t, "A1"), addr(t, "B4")) {171 t.Errorf("filter %+v", f)172 }173 if n := strings.Join(res.Notes, "; "); n != `2 criteria of a filter 012 can't apply left out, e.g. column B on Sheet1 (two conditions)` {174 t.Errorf("notes %q", n)175 }176}