1package sheet23import (4 "bytes"5 "reflect"6 "testing"7)89// fruit is a small table with a header row, for filter tests.10func fruit(t *testing.T) *Sheet {11 return sheetOf(t, map[string]string{12 "A1": "Fruit", "B1": "Qty",13 "A2": "Apple", "B2": "10",14 "A3": "Banana", "B3": "3",15 "A4": "Cherry", "B4": "",16 "A5": "apple pie", "B5": "$25",17 "A6": "Date", "B6": "3",18 "B8": "=SUM(B2:B6)",19 })20}2122// shownRows lists the rows (1-based) of 1..8 the filter doesn't hide.23func shownRows(s *Sheet) []int {24 var out []int25 for r := range 8 {26 if !s.RowHidden(r) {27 out = append(out, r+1)28 }29 }30 return out31}3233func TestFilterConditions(t *testing.T) {34 tests := []struct {35 name string36 col int37 cr Criteria38 want []int39 }{40 {"none", 0, Criteria{}, []int{1, 2, 3, 4, 5, 6, 7, 8}},41 {"values", 1, Criteria{Hidden: []string{"3"}}, []int{1, 2, 4, 5, 7, 8}},42 {"values as formatted, blanks as empty", 1, Criteria{Hidden: []string{"$25", ""}}, []int{1, 2, 3, 6, 7, 8}},43 {"text contains ignores case", 0, Criteria{Cond: Condition{CondContains, "APP"}}, []int{1, 2, 5, 7, 8}},44 {"text does not contain", 0, Criteria{Cond: Condition{CondNotContains, "app"}}, []int{1, 3, 4, 6, 7, 8}},45 {"starts with", 0, Criteria{Cond: Condition{CondStartsWith, "b"}}, []int{1, 3, 7, 8}},46 {"ends with", 0, Criteria{Cond: Condition{CondEndsWith, "e"}}, []int{1, 2, 5, 6, 7, 8}},47 {"exactly", 0, Criteria{Cond: Condition{CondExactly, "apple"}}, []int{1, 2, 7, 8}},48 {"greater than", 1, Criteria{Cond: Condition{CondGreater, "3"}}, []int{1, 2, 5, 7, 8}},49 {"greater than a currency", 1, Criteria{Cond: Condition{CondGreater, "$20"}}, []int{1, 5, 7, 8}},50 {"at least", 1, Criteria{Cond: Condition{CondGreaterEq, "10"}}, []int{1, 2, 5, 7, 8}},51 {"less than skips blanks", 1, Criteria{Cond: Condition{CondLess, "10"}}, []int{1, 3, 6, 7, 8}},52 {"at most", 1, Criteria{Cond: Condition{CondLessEq, "10"}}, []int{1, 2, 3, 6, 7, 8}},53 {"equal", 1, Criteria{Cond: Condition{CondEqual, "3"}}, []int{1, 3, 6, 7, 8}},54 {"not equal keeps blanks", 1, Criteria{Cond: Condition{CondNotEqual, "3"}}, []int{1, 2, 4, 5, 7, 8}},55 {"empty", 1, Criteria{Cond: Condition{CondEmpty, ""}}, []int{1, 4, 7, 8}},56 {"not empty", 1, Criteria{Cond: Condition{CondNotEmpty, ""}}, []int{1, 2, 3, 5, 6, 7, 8}},57 {"values and condition together", 1, Criteria{Hidden: []string{"10"}, Cond: Condition{CondNotEmpty, ""}}, []int{1, 3, 5, 6, 7, 8}},58 }59 for _, tt := range tests {60 t.Run(tt.name, func(t *testing.T) {61 s := fruit(t)62 s.CreateFilter(Rect{From: at("A1"), To: at("B6")})63 s.FilterColumn(tt.col, tt.cr)64 if got := shownRows(s); !reflect.DeepEqual(got, tt.want) {65 t.Errorf("shown rows %v, want %v", got, tt.want)66 }67 // Hidden rows still count in formulas, as in Sheets.68 if v := s.Value(at("B8")).Num; v != 41 {69 t.Errorf("SUM = %v, want 41", v)70 }71 })72 }73}7475func TestFilterFollowsValuesAndUndo(t *testing.T) {76 s := fruit(t)77 s.CreateFilter(Rect{From: at("A1"), To: at("B6")})78 s.FilterColumn(1, Criteria{Cond: Condition{CondGreater, "5"}})79 if !s.RowHidden(2) || s.HiddenRows() != 3 {80 t.Fatalf("row 3 shown, %d hidden", s.HiddenRows())81 }82 // Changing a value re-applies the filter.83 s.Set(at("B3"), "30")84 if s.RowHidden(2) {85 t.Error("row 3 still hidden after its value passed")86 }87 s.Undo()88 if !s.RowHidden(2) {89 t.Error("undoing the edit didn't hide row 3 again")90 }91 s.Undo() // the condition92 if s.HiddenRows() != 0 || s.Filter() == nil {93 t.Errorf("after undoing the condition: %d hidden, filter %v", s.HiddenRows(), s.Filter())94 }95 s.Undo() // creating the filter96 if s.Filter() != nil {97 t.Error("undo didn't remove the filter")98 }99 s.Redo()100 s.Redo()101 if s.HiddenRows() != 3 {102 t.Errorf("redo: %d hidden", s.HiddenRows())103 }104 s.RemoveFilter()105 if s.Filter() != nil || s.HiddenRows() != 0 {106 t.Error("remove left the filter")107 }108}109110func TestFilterValues(t *testing.T) {111 s := fruit(t)112 s.CreateFilter(Rect{From: at("A1"), To: at("B6")})113 s.FilterColumn(1, Criteria{Hidden: []string{"3", "gone"}})114 want := []FilterValue{{"3", "3", 2, false}, {"10", "10", 1, true}, {"$25", "$25", 1, true}, {"gone", "gone", 0, false}, {"", "", 1, true}}115 if got := s.FilterValues(1); !reflect.DeepEqual(got, want) {116 t.Errorf("values\n got %v\nwant %v", got, want)117 }118 // Other columns' criteria narrow the list.119 s.FilterColumn(0, Criteria{Cond: Condition{CondContains, "apple"}})120 want = []FilterValue{{"3", "3", 0, false}, {"10", "10", 1, true}, {"$25", "$25", 1, true}, {"gone", "gone", 0, false}}121 if got := s.FilterValues(1); !reflect.DeepEqual(got, want) {122 t.Errorf("narrowed values\n got %v\nwant %v", got, want)123 }124}125126func TestFilterFollowsInsertAndDelete(t *testing.T) {127 s := fruit(t)128 s.CreateFilter(Rect{From: at("A1"), To: at("B6")})129 s.FilterColumn(1, Criteria{Hidden: []string{"3"}})130 s.InsertRows(2, 2) // inside the range: it grows131 if f := s.Filter(); f.Range.String() != "A1:B8" {132 t.Errorf("after insert: %v", f.Range)133 }134 s.InsertCols(0, 1) // before it: it moves, and so do its columns135 f := s.Filter()136 if f.Range.String() != "B1:C8" || f.Cols[2].Hidden[0] != "3" {137 t.Errorf("after insert column: %v %v", f.Range, f.Cols)138 }139 s.DeleteCols(1, 2) // all of it: the filter goes140 if s.Filter() != nil {141 t.Error("filter survived deleting its columns")142 }143 s.Undo()144 if s.Filter() == nil {145 t.Error("undo didn't bring the filter back")146 }147}148149func TestEdgeSkipsHiddenRows(t *testing.T) {150 s := fruit(t)151 s.CreateFilter(Rect{From: at("A1"), To: at("B6")})152 s.FilterColumn(1, Criteria{Cond: Condition{CondEqual, "3"}}) // rows 1, 3 and 6 show153 tests := []struct {154 from string155 dr int156 want string157 }{158 {"A1", 1, "A6"}, // through the visible block 1, 3, 6159 {"A6", -1, "A1"},160 {"B1", 1, "B6"},161 {"B6", 1, "B8"}, // row 7 is blank, then the total162 }163 for _, tt := range tests {164 if got := s.Edge(at(tt.from), 0, tt.dr).String(); got != tt.want {165 t.Errorf("Edge(%s, %d) = %s, want %s", tt.from, tt.dr, got, tt.want)166 }167 }168}169170func TestFreeze(t *testing.T) {171 s := New()172 s.SetFrozen(2, 1)173 if r, c := s.Frozen(); r != 2 || c != 1 {174 t.Fatalf("frozen %d, %d", r, c)175 }176 s.InsertRows(1, 3) // inside the frozen rows: they grow177 if r, _ := s.Frozen(); r != 5 {178 t.Errorf("after insert: %d frozen rows", r)179 }180 s.DeleteRows(0, 4)181 if r, _ := s.Frozen(); r != 1 {182 t.Errorf("after delete: %d frozen rows", r)183 }184 s.InsertCols(0, 1) // before the frozen column: still one frozen185 if _, c := s.Frozen(); c != 1 {186 t.Errorf("after insert column: %d frozen columns", c)187 }188 for s.CanUndo() {189 s.Undo()190 }191 if r, c := s.Frozen(); r != 0 || c != 0 {192 t.Errorf("undo all: frozen %d, %d", r, c)193 }194 c, ok := s.Redo()195 if !ok || c.Label != "freeze 2 rows" {196 t.Errorf("redo %v %v", c, ok)197 }198}199200func TestViewFileRoundTrip(t *testing.T) {201 s := fruit(t)202 s.SetFrozen(1, 2)203 s.CreateFilter(Rect{From: at("A1"), To: at("B6")})204 s.FilterColumn(1, Criteria{Hidden: []string{"3", ""}, Cond: Condition{CondLess, "$20"}})205 var buf bytes.Buffer206 if err := s.Write(&buf); err != nil {207 t.Fatal(err)208 }209 for _, want := range []string{`"version": 3`, `"freeze": {"rows":1,"cols":2}`,210 `"filter": {"range":"A1:B6","columns":{"B":{"hidden":["","3"],"condition":"lt","value":"$20"}}}`} {211 if !bytes.Contains(buf.Bytes(), []byte(want)) {212 t.Errorf("file lacks %s:\n%s", want, buf.String())213 }214 }215 back, err := Read(&buf)216 if err != nil {217 t.Fatal(err)218 }219 if r, c := back.Frozen(); r != 1 || c != 2 {220 t.Errorf("frozen %d, %d", r, c)221 }222 if !reflect.DeepEqual(back.Filter(), s.Filter()) || !reflect.DeepEqual(shownRows(back), shownRows(s)) {223 t.Errorf("filter %+v, want %+v", back.Filter(), s.Filter())224 }225 if back.CanUndo() {226 t.Error("loading is undoable")227 }228}