1package sheet23import (4 "regexp"5 "strings"6)78// FindOptions mirror Google Sheets' Find and replace dialog.9type FindOptions struct {10 MatchCase bool11 WholeCell bool // the whole cell must match, not just part of it12 Regex bool // the query is a regular expression; replacements may use $113 InFormulas bool // search formula text instead of formula results14 Within *Rect // limit the search to a range; nil searches the sheet15}1617// finder is a compiled query.18type finder struct {19 re *regexp.Regexp20 o FindOptions21}2223func newFinder(query string, o FindOptions) (*finder, error) {24 pat := query25 if !o.Regex {26 pat = regexp.QuoteMeta(query)27 }28 if o.WholeCell {29 pat = "^(?:" + pat + ")$"30 }31 if !o.MatchCase {32 pat = "(?i)" + pat33 }34 re, err := regexp.Compile(pat)35 if err != nil {36 return nil, err37 }38 return &finder{re: re, o: o}, nil39}4041// text is what the search looks at in a cell: formula text when searching42// within formulas, otherwise what the cell shows.43func (f *finder) text(c *Cell) string {44 if c.IsFormula() && f.o.InFormulas {45 return c.Input46 }47 if c.IsFormula() {48 return c.Value.String()49 }50 return strings.TrimPrefix(c.Input, "'")51}5253// Find returns the cells matching query in reading order: row by row,54// left to right.55func (s *Sheet) Find(query string, o FindOptions) ([]Addr, error) {56 if query == "" {57 return nil, nil58 }59 f, err := newFinder(query, o)60 if err != nil {61 return nil, err62 }63 var out []Addr64 for _, a := range s.Addrs() {65 if o.Within != nil && !o.Within.Contains(a) {66 continue67 }68 if f.re.MatchString(f.text(s.cells.get(a))) {69 out = append(out, a)70 }71 }72 return out, nil73}7475// Replace replaces matches of query in the cell at a and reports whether76// the cell changed. Formula cells are only changed when searching within77// formulas, as in Sheets. A replacement that turns a formula invalid is78// returned as an error and leaves the cell unchanged.79func (s *Sheet) Replace(a Addr, query, repl string, o FindOptions) (bool, error) {80 f, err := newFinder(query, o)81 if err != nil {82 return false, err83 }84 return s.replace(a, f, repl)85}8687func (s *Sheet) replace(a Addr, f *finder, repl string) (bool, error) {88 c := s.cells.get(a)89 if c == nil || c.derived || c.spilled || (c.IsFormula() && !f.o.InFormulas) {90 return false, nil91 }92 old := f.text(c)93 if !f.re.MatchString(old) {94 return false, nil95 }96 if !f.o.Regex {97 repl = strings.ReplaceAll(repl, "$", "$$")98 }99 next := f.re.ReplaceAllString(old, repl)100 if strings.HasPrefix(c.Input, "'") && !c.IsFormula() {101 next = "'" + next // keep text forced as text102 }103 if next == c.Input {104 return false, nil105 }106 return true, s.Set(a, next)107}108109// ReplaceAll replaces every match and returns how many cells changed. It110// stops at the first cell whose replacement is an invalid formula.111func (s *Sheet) ReplaceAll(query, repl string, o FindOptions) (int, error) {112 cells, err := s.Find(query, o)113 if err != nil {114 return 0, err115 }116 f, err := newFinder(query, o)117 if err != nil {118 return 0, err119 }120 n := 0121 var focus Rect122 if len(cells) > 0 {123 focus = NewRect(cells[0], cells[len(cells)-1])124 }125 // One undo step for the whole replacement.126 err = s.Batch(Change{Label: "replace all", Focus: focus}, func() error {127 for _, a := range cells {128 changed, err := s.replace(a, f, repl)129 if err != nil {130 return err131 }132 if changed {133 n++134 }135 }136 return nil137 })138 return n, err139}