1package headless23import (4 "fmt"5 "strings"67 "src.rest/cat/012/internal/sheet"8)910// FilterColumn is one column's criteria for Filter: a condition (as11// Data > Create a filter's "Filter by condition" names them) with its12// value, and values to hide ("Filter by values").13type FilterColumn struct {14 Column string `json:"column" jsonschema:"a column letter (B) or the header's text"`15 Condition string `json:"condition,omitempty" jsonschema:"empty, not_empty, contains, not_contains, starts_with, ends_with, exactly, gt, ge, lt, le, eq or ne"`16 Value string `json:"value,omitempty" jsonschema:"what the condition compares with, as typed: 100, 2026-01-31, North"`17 Hide []string `json:"hide,omitempty" jsonschema:"values to hide, as the cells show them"`18}1920// Filter puts a filter on the range ref names (its first row the21// header) with the columns' criteria, replacing the sheet's filter, as22// Data > Create a filter does; remove takes the sheet's filter off.23func Filter(w *sheet.Workbook, ref string, cols []FilterColumn, remove bool) error {24 t, err := Resolve(w, ref)25 if err != nil {26 return err27 }28 s := t.Sheet29 if remove {30 s.RemoveFilter()31 return nil32 }33 r := targetRect(t)34 if f := s.Filter(); f == nil || f.Range != r {35 s.CreateFilter(r)36 }37 for _, c := range cols {38 col, err := columnOf(s, r, c.Column, true)39 if err != nil {40 return err41 }42 cr := sheet.Criteria{Hidden: c.Hide}43 if c.Condition != "" {44 op, ok := sheet.ParseCondOp(c.Condition)45 if !ok {46 return fmt.Errorf("no condition %q: empty, not_empty, contains, not_contains, starts_with, ends_with, exactly, gt, ge, lt, le, eq or ne", c.Condition)47 }48 cr.Cond = sheet.Condition{Op: op, Arg: sheet.CondArg(op, c.Value, s.Locale())}49 }50 s.FilterColumn(col, cr)51 }52 return nil53}5455// FindOptions tune Find.56type FindOptions struct {57 Ref string `json:"ref,omitempty" jsonschema:"a sheet or range to search; every sheet when empty"`58 MatchCase bool `json:"match_case,omitempty"`59 WholeCell bool `json:"whole_cell,omitempty" jsonschema:"the whole cell must match"`60 Regex bool `json:"regex,omitempty" jsonschema:"the query is a regular expression"`61 InFormulas bool `json:"in_formulas,omitempty" jsonschema:"search formulas' text rather than their values"`62 Limit int `json:"limit,omitempty" jsonschema:"the most matches to return; 100 when 0"`63}6465// Match is a cell Find found: where, what it shows and what was typed.66type Match struct {67 Cell string `json:"cell"`68 Text string `json:"text"`69 Input string `json:"input"`70}7172// Found is what Find returns: the first matches, and how many there73// are in all.74type Found struct {75 Matches []Match `json:"matches"`76 Total int `json:"total"`77}7879// Find searches the workbook's sheets, or the sheet or range o.Ref80// names, as Edit > Find and replace does.81func Find(w *sheet.Workbook, query string, o FindOptions) (Found, error) {82 if o.Limit <= 0 {83 o.Limit = 10084 }85 sheets, within := w.Sheets(), (*sheet.Rect)(nil)86 if o.Ref != "" {87 t, err := Resolve(w, o.Ref)88 if err != nil {89 return Found{}, err90 }91 sheets = []*sheet.Sheet{t.Sheet}92 if !t.Whole {93 r := t.Range94 within = &r95 }96 }97 out := Found{Matches: []Match{}}98 for _, s := range sheets {99 if s.IsNotebook() {100 continue101 }102 addrs, err := s.Find(query, sheet.FindOptions{MatchCase: o.MatchCase, WholeCell: o.WholeCell, Regex: o.Regex,103 InFormulas: o.InFormulas, Within: within})104 if err != nil {105 return Found{}, err106 }107 out.Total += len(addrs)108 for _, a := range addrs {109 if len(out.Matches) == o.Limit {110 break111 }112 m := Match{Cell: sheet.Qualified(s.Name(), sheet.Rect{From: a, To: a}), Text: s.LocalText(a)}113 if c := s.Cell(a); c != nil {114 m.Input = c.Input115 }116 out.Matches = append(out.Matches, m)117 }118 }119 return out, nil120}121122// Evaluated is a formula's result, computed without keeping it.123type Evaluated struct {124 Cell string `json:"cell"` // where it was computed125 Value Value `json:"value"` // typed as ReadRange types values126 Text string `json:"text"` // as the cell would show it127 Error string `json:"error"` // why, when Value is an error128 Spill *Range `json:"spill,omitempty"`129}130131// Evaluate computes formula as if typed in the cell at names (a cell132// below the shown sheet's data when ""), and returns what it shows.133// It changes w: call it on a workbook to throw away.134func Evaluate(w *sheet.Workbook, formula, at string) (Evaluated, error) {135 if !strings.HasPrefix(formula, "=") {136 formula = "=" + formula137 }138 s, a, err := evalCell(w, at)139 if err != nil {140 return Evaluated{}, err141 }142 out := Evaluated{Cell: sheet.Qualified(s.Name(), sheet.Rect{From: a, To: a})}143 if err := w.Batch(sheet.Change{Label: "evaluate", Sheet: s}, func() error { return setFormula(s, a, formula) }); err != nil {144 return out, err145 }146 res := ReadRange(Target{Sheet: s, Range: sheet.Rect{From: a, To: a}}, ReadOptions{})147 out.Value, out.Text = res.Values[0][0], s.LocalText(a)148 if v := s.Value(a); v.Kind == sheet.Error {149 out.Error = s.ExplainError(a)150 }151 if area, ok := s.SpillArea(a); ok && area != (sheet.Rect{From: a, To: a}) {152 spill := ReadRange(Target{Sheet: s, Range: area}, ReadOptions{MaxCells: 1000})153 out.Spill = &spill154 }155 return out, nil156}157158// evalCell is where Evaluate computes: the cell at names, or the first159// cell of the column A two rows below the shown sheet's data.160func evalCell(w *sheet.Workbook, at string) (*sheet.Sheet, sheet.Addr, error) {161 if at != "" {162 return ResolveCell(w, at)163 }164 s := w.Sheet(w.Active())165 row := 0166 if used, ok := s.UsedRange(); ok {167 row = min(used.To.Row+2, sheet.MaxRows-1)168 }169 return s, sheet.Addr{Row: row}, nil170}171172// setFormula types formula in a, with a parse error said as Set says it.173func setFormula(s *sheet.Sheet, a sheet.Addr, formula string) error {174 _, err := setOne(s.Book(), Entry{Ref: sheet.Qualified(s.Name(), sheet.Rect{From: a, To: a}), Input: formula}, SetOptions{Force: true})175 return err176}