1package sheet23import (4 "cmp"5 "maps"6 "reflect"7 "slices"8 "strings"9)1011// A filter hides the rows of a range whose values don't meet criteria set12// per column, as Sheets' Data > Create a filter. Rows are hidden, never13// deleted: formulas still see them (SUM over a filtered range includes14// hidden rows, as in Sheets). The range's first row holds the headers and15// is never hidden. Criteria are re-applied whenever values change.16type Filter struct {17 Range Rect18 Cols map[int]Criteria // by column; a column without criteria hides nothing19}2021// Criteria is what one column of a filter lets through: its displayed22// value must not be one of Hidden (Sheets' "Filter by values", unchecked23// values; "" stands for blanks) and must meet Cond ("Filter by condition").24type Criteria struct {25 Hidden []string26 Cond Condition27}2829// IsZero reports whether the criteria let every row through.30func (c Criteria) IsZero() bool { return len(c.Hidden) == 0 && c.Cond.Op == CondNone }3132// Condition is a test on a cell, e.g. "greater than 100".33type Condition struct {34 Op CondOp35 Arg string // the value compared with, as typed; unused by CondEmpty and CondNotEmpty36}3738// CondOp is one of Sheets' filter conditions.39type CondOp uint84041const (42 CondNone CondOp = iota43 CondEmpty44 CondNotEmpty45 CondContains46 CondNotContains47 CondStartsWith48 CondEndsWith49 CondExactly50 CondGreater51 CondGreaterEq52 CondLess53 CondLessEq54 CondEqual55 CondNotEqual56 numCondOps57)5859var condNames = [numCondOps]string{"", "empty", "not_empty", "contains", "not_contains",60 "starts_with", "ends_with", "exactly", "gt", "ge", "lt", "le", "eq", "ne"}6162var condTitles = [numCondOps]string{"None", "Is empty", "Is not empty", "Text contains",63 "Text does not contain", "Text starts with", "Text ends with", "Text is exactly",64 "Greater than", "Greater than or equal to", "Less than", "Less than or equal to",65 "Is equal to", "Is not equal to"}6667// CondOps lists the conditions in the order Sheets' menu shows them.68func CondOps() []CondOp {69 out := make([]CondOp, numCondOps)70 for i := range out {71 out[i] = CondOp(i)72 }73 return out74}7576// String names the condition as stored in files.77func (op CondOp) String() string { return condNames[op] }7879// Title names the condition for people, e.g. "Greater than".80func (op CondOp) Title() string { return condTitles[op] }8182// OnText reports whether the condition tests the text a cell shows83// (contains, starts with ...), which is in the locale's rendering, so84// its value is kept as typed rather than read as a number or date.85func (op CondOp) OnText() bool { return op >= CondContains && op <= CondExactly }8687// TakesArg reports whether the condition compares with a value.88func (op CondOp) TakesArg() bool { return op != CondNone && op != CondEmpty && op != CondNotEmpty }8990// ParseCondOp is the inverse of CondOp.String.91func ParseCondOp(s string) (CondOp, bool) {92 for i, n := range condNames {93 if n == s {94 return CondOp(i), true95 }96 }97 return CondNone, false98}99100// clone returns a deep copy, so a stored filter is never changed in place.101func (f *Filter) clone() *Filter {102 if f == nil {103 return nil104 }105 cp := &Filter{Range: f.Range, Cols: make(map[int]Criteria, len(f.Cols))}106 for c, cr := range f.Cols {107 if cr.IsZero() {108 continue109 }110 cr.Hidden = slices.Clone(cr.Hidden)111 cp.Cols[c] = cr112 }113 return cp114}115116// Filter returns a copy of the sheet's filter, or nil if it has none.117func (s *Sheet) Filter() *Filter { return s.view.filter.clone() }118119// FilterRange returns the filter's range, and false if there is no120// filter.121func (s *Sheet) FilterRange() (Rect, bool) {122 if f := s.view.filter; f != nil {123 return f.Range, true124 }125 return Rect{}, false126}127128// ColumnFiltered reports whether the filter has criteria for column col.129func (s *Sheet) ColumnFiltered(col int) bool {130 f := s.view.filter131 return f != nil && !f.Cols[col].IsZero()132}133134// CreateFilter puts a filter on r, whose first row is the header row.135func (s *Sheet) CreateFilter(r Rect) {136 s.setFilter("create a filter on "+r.String(), &Filter{Range: r, Cols: map[int]Criteria{}})137}138139// RemoveFilter removes the filter, showing every row again.140func (s *Sheet) RemoveFilter() {141 s.setFilter("remove the filter", nil)142}143144// FilterColumn sets the criteria for column col of the filter.145func (s *Sheet) FilterColumn(col int, cr Criteria) {146 f := s.view.filter.clone()147 if f == nil {148 return149 }150 if cr.IsZero() {151 delete(f.Cols, col)152 } else {153 cr.Hidden = slices.Clone(cr.Hidden)154 slices.Sort(cr.Hidden)155 cr.Hidden = slices.Compact(cr.Hidden)156 f.Cols[col] = cr157 }158 s.setFilter("filter column "+ColName(col), f)159}160161func (s *Sheet) setFilter(label string, f *Filter) {162 if reflect.DeepEqual(f.clone(), s.view.filter.clone()) {163 return164 }165 focus := Rect{}166 if f != nil {167 focus = f.Range168 } else if old := s.view.filter; old != nil {169 focus = old.Range170 }171 s.change(label, focus, func() {172 s.recordView()173 s.view.filter = f.clone()174 s.hidden.valid = false175 })176}177178// hiddenCache remembers which rows the filter hides until values or the179// filter change, so navigation and drawing stay cheap. The rows of the180// filter's range past its last filled row are all blank, so they are181// hidden or shown together: a filter on whole columns tests the rows182// with data and one blank row, not a million.183type hiddenCache struct {184 valid bool185 rows map[int]struct{}186 tail Rect // the blank rows at the end of the range, when hidden (From.Row > To.Row otherwise)187}188189// RowHidden reports whether the filter hides row r.190func (s *Sheet) RowHidden(r int) bool {191 if s.view.filter == nil {192 return false193 }194 s.updateHidden()195 if r >= s.hidden.tail.From.Row && r <= s.hidden.tail.To.Row {196 return true197 }198 _, hid := s.hidden.rows[r]199 return hid200}201202// HiddenRows returns how many rows the filter hides.203func (s *Sheet) HiddenRows() int {204 if s.view.filter == nil {205 return 0206 }207 s.updateHidden()208 return len(s.hidden.rows) + max(s.hidden.tail.To.Row-s.hidden.tail.From.Row+1, 0)209}210211// NextShownRow returns the first row from r+d on (d is 1 or -1) that the212// filter doesn't hide, jumping over the hidden blank rows at the end of213// its range at once, and false past the edge of the sheet.214func (s *Sheet) NextShownRow(r, d int) (int, bool) {215 for r += d; r >= 0 && r < MaxRows; r += d {216 if !s.RowHidden(r) {217 return r, true218 }219 if t := s.hidden.tail; r >= t.From.Row && r <= t.To.Row {220 r = t.To.Row221 if d < 0 {222 r = t.From.Row223 }224 }225 }226 return r, false227}228229// filterData is the part of the filter's range that may hold data: its230// rows through the last filled one.231func (s *Sheet) filterData(r Rect) Rect {232 b, ok := s.cells.filled.bounds(r)233 r.To.Row = r.From.Row234 if ok {235 r.To.Row = b.To.Row236 }237 return r238}239240func (s *Sheet) updateHidden() {241 if s.hidden.valid {242 return243 }244 s.hidden = hiddenCache{valid: true, rows: map[int]struct{}{}, tail: Rect{From: Addr{Row: 1}}}245 f := s.view.filter246 if f == nil || len(f.Cols) == 0 {247 return248 }249 tests := s.criteriaTests(f, -1)250 data := s.filterData(f.Range)251 for row := f.Range.From.Row + 1; row <= data.To.Row; row++ {252 if !s.rowPasses(row, tests) {253 s.hidden.rows[row] = struct{}{}254 }255 }256 if blank := data.To.Row + 1; blank <= f.Range.To.Row && !s.rowPasses(blank, tests) {257 s.hidden.tail = Rect{From: Addr{Row: blank}, To: Addr{Row: f.Range.To.Row}}258 }259}260261// colTest is one column's criteria, ready to run.262type colTest struct {263 col int264 hidden map[string]bool265 cond func(Value, string) bool266 local bool // cond tests the text as the locale shows it267}268269// criteriaTests prepares the filter's criteria, leaving out column skip.270func (s *Sheet) criteriaTests(f *Filter, skip int) []colTest {271 var tests []colTest272 for _, c := range slices.Sorted(maps.Keys(f.Cols)) {273 cr := f.Cols[c]274 if c == skip || cr.IsZero() {275 continue276 }277 t := colTest{col: c, cond: cr.Cond.test(), local: cr.Cond.Op.OnText() && !s.Locale().IsCanonical()}278 if len(cr.Hidden) > 0 {279 t.hidden = make(map[string]bool, len(cr.Hidden))280 for _, h := range cr.Hidden {281 t.hidden[h] = true282 }283 }284 tests = append(tests, t)285 }286 return tests287}288289func (s *Sheet) rowPasses(row int, tests []colTest) bool {290 for _, t := range tests {291 a := Addr{Col: t.col, Row: row}292 shown := s.ShownText(a)293 if t.hidden[shown] {294 return false295 }296 if t.local {297 shown = s.LocalText(a)298 }299 if !t.cond(s.Value(a), shown) {300 return false301 }302 }303 return true304}305306// ShownText is the cell's value as its format displays it, with no width307// limit: what filters and the values list compare.308func (s *Sheet) ShownText(a Addr) string {309 if !s.cells.filledAt(a) {310 return ""311 }312 return FormatText(s.cells.value(a), s.DisplayFormat(a))313}314315// Matches reports whether a cell with value v, shown as shown, meets the316// condition, as the filter tests it.317func (c Condition) Matches(v Value, shown string) bool { return c.test()(v, shown) }318319// Test compiles the condition, for what filters rows outside a sheet (a320// linked source's view): see test.321func (c Condition) Test() func(v Value, shown string) bool { return c.test() }322323// test compiles the condition. Text conditions ignore case; comparisons324// need a number for a number (entered as $1,200 or 12% too) and text for325// text, and never match blanks.326func (c Condition) test() func(v Value, shown string) bool {327 arg := strings.ToLower(c.Arg)328 argVal := Value{Kind: Text, Str: c.Arg}329 if n, _, ok := ParseValue(strings.TrimSpace(c.Arg)); ok {330 argVal = Value{Kind: Number, Num: n}331 }332 lower := func(s string) string { return strings.ToLower(s) }333 cmpArg := func(v Value) (int, bool) {334 if v.Kind != argVal.Kind || (v.Kind != Number && v.Kind != Text) {335 return 0, false336 }337 return compare(v, argVal), true338 }339 ordered := func(ok func(int) bool) func(Value, string) bool {340 return func(v Value, _ string) bool {341 d, same := cmpArg(v)342 return same && ok(d)343 }344 }345 switch c.Op {346 case CondEmpty:347 return func(v Value, shown string) bool { return shown == "" }348 case CondNotEmpty:349 return func(v Value, shown string) bool { return shown != "" }350 case CondContains:351 return func(_ Value, shown string) bool { return strings.Contains(lower(shown), arg) }352 case CondNotContains:353 return func(_ Value, shown string) bool { return !strings.Contains(lower(shown), arg) }354 case CondStartsWith:355 return func(_ Value, shown string) bool { return strings.HasPrefix(lower(shown), arg) }356 case CondEndsWith:357 return func(_ Value, shown string) bool { return strings.HasSuffix(lower(shown), arg) }358 case CondExactly:359 return func(_ Value, shown string) bool { return lower(shown) == arg }360 case CondGreater:361 return ordered(func(d int) bool { return d > 0 })362 case CondGreaterEq:363 return ordered(func(d int) bool { return d >= 0 })364 case CondLess:365 return ordered(func(d int) bool { return d < 0 })366 case CondLessEq:367 return ordered(func(d int) bool { return d <= 0 })368 case CondEqual:369 return ordered(func(d int) bool { return d == 0 })370 case CondNotEqual:371 return func(v Value, shown string) bool {372 d, same := cmpArg(v)373 return !same || d != 0374 }375 }376 return func(Value, string) bool { return true }377}378379// FilterValue is one entry of a filter's values list: a value as shown,380// how many rows have it, and whether it is checked (shown).381type FilterValue struct {382 Text string // as en-US shows it, as filters keep it; "" for blanks383 Label string // as the sheet's locale shows it, for the list384 Count int385 Shown bool386}387388// FilterValues lists the distinct values in column col of the filter's389// data rows, among the rows the other columns' criteria let through, as390// Sheets' "Filter by values" does. Numbers come first in numeric order,391// then text alphabetically, then blanks.392func (s *Sheet) FilterValues(col int) []FilterValue {393 f := s.view.filter394 if f == nil {395 return nil396 }397 return s.valuesList(f.Range, col, s.criteriaTests(f, col), f.Cols[col].Hidden, false)398}399400// valuesList lists the distinct values shown in column col of r's data401// rows among those passing tests, with hidden unchecked, as a filter's402// values list. With skipBlank, rows blank across r are left out.403func (s *Sheet) valuesList(r Rect, col int, tests []colTest, hide []string, skipBlank bool) []FilterValue {404 hidden := map[string]bool{}405 for _, h := range hide {406 hidden[h] = true407 }408 counts := map[string]int{}409 values := map[string]Value{}410 labels := map[string]string{}411 data := s.filterData(r)412 for row := r.From.Row + 1; row <= data.To.Row; row++ {413 if !s.rowPasses(row, tests) || skipBlank && s.rowBlank(row, r) {414 continue415 }416 a := Addr{Col: col, Row: row}417 t := s.ShownText(a)418 if _, seen := counts[t]; !seen {419 values[t], labels[t] = s.Value(a), s.localLabel(a, t)420 }421 counts[t]++422 }423 // The rows past the data are blank: they count as one value.424 if blank := data.To.Row + 1; blank <= r.To.Row && !skipBlank && s.rowPasses(blank, tests) {425 counts[""] += r.To.Row - blank + 1426 values[""] = Value{}427 }428 // Unchecked values stay listed even when no row has them any more, so429 // they can be checked again.430 for h := range hidden {431 if _, ok := counts[h]; !ok {432 counts[h], labels[h] = 0, LocalArg(h, s.Locale())433 values[h] = Value{Kind: Text, Str: h}434 if n, _, ok := ParseValue(h); ok {435 values[h] = Value{Kind: Number, Num: n}436 }437 }438 }439 out := make([]FilterValue, 0, len(counts))440 for t, n := range counts {441 out = append(out, FilterValue{Text: t, Label: labels[t], Count: n, Shown: !hidden[t]})442 }443 slices.SortFunc(out, func(a, b FilterValue) int {444 if (a.Text == "") != (b.Text == "") {445 if a.Text == "" {446 return 1447 }448 return -1449 }450 return cmp.Or(sortCompare(values[a.Text], values[b.Text]), cmp.Compare(a.Text, b.Text))451 })452 return out453}