1package sheet23import (4 "errors"5 "fmt"6 "maps"7 "slices"8 "strings"910 "src.rest/cat/012/internal/formula"11)1213// Named ranges, as in Sheets' Data > Named ranges: a name such as Sales14// stands for a range in formulas, =SUM(Sales). Formulas keep the name and15// resolve it when they're evaluated, so redefining or deleting a name16// recalculates every formula that uses it. Inserting, deleting and moving17// cells moves a name's range like any other range reference.1819// Name is a named range. Names belong to the workbook, as in Sheets, and20// each points at a range on one of its sheets.21type Name struct {22 Name string // as the user spelled it; formulas match it in any case23 Sheet *Sheet // the sheet the range is on24 Range Rect25 // Lost is set when every cell of the range was deleted: formulas using26 // the name show #REF!, as in Sheets. Range is then zero.27 Lost bool28}2930// Gone reports whether the name no longer points at cells: its cells or31// its sheet were deleted.32func (n Name) Gone() bool { return n.Lost || n.Sheet == nil || !n.Sheet.live }3334// Ref is what the name stands for, e.g. "B2:B20", or "#REF!" when gone.35// In a workbook of several sheets it names the sheet: "Sales!B2:B20".36func (n Name) Ref() string {37 switch {38 case n.Gone():39 return "#REF!"40 case n.Sheet.wb.Len() > 1:41 return Qualified(n.Sheet.name, n.Range)42 }43 return n.Range.String()44}4546// maxNameLen is Sheets' limit on the length of a name.47const maxNameLen = 2504849func nameKey(name string) string { return strings.ToUpper(name) }5051// ValidName checks that name can be a named range, following Sheets'52// rules: letters, digits, _ and ., starting with a letter or _, and not53// something a formula would read as a cell or a boolean.54func ValidName(name string) error { return checkName(name, formula.LooksLikeRef) }5556// checkName checks name against the rules of names, isRef telling57// which read as a cell reference.58func checkName(name string, isRef func(string) bool) error {59 switch {60 case name == "":61 return errors.New("Enter a name")62 case len(name) > maxNameLen:63 return fmt.Errorf("A name can be at most %d characters", maxNameLen)64 case !isLetter(name[0]) && name[0] != '_':65 return errors.New("A name must start with a letter or _")66 }67 for i := range len(name) {68 if c := name[i]; !isLetter(c) && !isDigit(c) && c != '_' && c != '.' {69 return errors.New("A name can only have letters, digits, _ and .")70 }71 }72 switch k := nameKey(name); {73 case k == "TRUE" || k == "FALSE":74 return errors.New("TRUE and FALSE can't be names")75 case isRef(k):76 return errors.New("A name can't look like a cell reference, e.g. A1 or R1C1")77 }78 return nil79}8081// Names returns the named ranges, sorted by name.82func (w *Workbook) Names() []Name {83 // The keys are already upper case: sorting names by nameKey upper-cased84 // both sides of every comparison, 20,000 allocations a frame for 100085 // names, since the formula bar looks for the selection's name.86 keys := slices.Sorted(maps.Keys(w.names))87 out := make([]Name, len(keys))88 for i, k := range keys {89 out[i] = w.names[k]90 }91 return out92}9394// LookupName finds a named range, ignoring case.95func (w *Workbook) LookupName(name string) (Name, bool) {96 n, ok := w.names[nameKey(name)]97 return n, ok98}99100// NameUsers returns how many formulas mention the name.101func (w *Workbook) NameUsers(name string) int { return len(w.nameUsers[nameKey(name)]) }102103// DefineName names the range r on sheet s, as one undo step.104func (w *Workbook) DefineName(name string, s *Sheet, r Rect) error {105 if err := ValidName(name); err != nil {106 return err107 }108 if old, ok := w.LookupName(name); ok {109 return fmt.Errorf("%s already names %s", old.Name, old.Ref())110 }111 if _, _, ok := w.regionName(nameKey(name)); ok {112 return fmt.Errorf("%s names a shell region", name)113 }114 if _, t, ok := w.Table(name); ok {115 return fmt.Errorf("%s names a table", t.Name)116 }117 w.change(s, "name "+r.String()+" "+name, r, func() { w.putName(nameKey(name), &Name{Name: name, Sheet: s, Range: r}) })118 return nil119}120121// EditName renames the named range old and points it at r on sheet s, as122// one undo step. Formulas that use it are rewritten to the new name, as123// in Sheets.124func (w *Workbook) EditName(old, name string, s *Sheet, r Rect) error {125 cur, ok := w.LookupName(old)126 if !ok {127 return fmt.Errorf("There's no range named %s", old)128 }129 if err := ValidName(name); err != nil {130 return err131 }132 if other, ok := w.LookupName(name); ok && nameKey(name) != nameKey(old) {133 return fmt.Errorf("%s already names %s", other.Name, other.Ref())134 }135 w.change(s, "edit "+name, r, func() {136 from, to := nameKey(cur.Name), nameKey(name)137 if cur.Name != name {138 w.renameInFormulas(from, name)139 }140 if from != to {141 w.putName(from, nil)142 }143 w.putName(to, &Name{Name: name, Sheet: s, Range: r})144 })145 return nil146}147148// DeleteName removes a named range, as one undo step. Formulas that use149// it show #NAME? until it is defined again.150func (w *Workbook) DeleteName(name string) error {151 cur, ok := w.LookupName(name)152 if !ok {153 return fmt.Errorf("There's no range named %s", name)154 }155 w.change(cur.Sheet, "delete "+cur.Name, cur.Range, func() { w.putName(nameKey(name), nil) })156 return nil157}158159// The sheet's name methods are the workbook's, with ranges on this sheet.160161func (s *Sheet) Names() []Name { return s.wb.Names() }162func (s *Sheet) LookupName(name string) (Name, bool) { return s.wb.LookupName(name) }163func (s *Sheet) NameUsers(name string) int { return s.wb.NameUsers(name) }164func (s *Sheet) DefineName(name string, r Rect) error { return s.wb.DefineName(name, s, r) }165func (s *Sheet) DeleteName(name string) error { return s.wb.DeleteName(name) }166func (s *Sheet) EditName(old, name string, r Rect) error {167 return s.wb.EditName(old, name, s, r)168}169170// renameInFormulas rewrites the formulas that use the name with key from171// to spell it to instead.172func (w *Workbook) renameInFormulas(from, to string) {173 rw := formula.Rewriter{Name: func(n formula.Name) Node {174 if nameKey(n.Name) == from {175 return formula.Name{Name: to}176 }177 return n178 }}179 for _, l := range slices.Collect(maps.Keys(w.nameUsers[from])) {180 l.s.place(l.a, l.s.cells.get(l.a).rewritten(rw))181 }182}183184// namePtr returns a copy of the named range with key k, or nil.185func (w *Workbook) namePtr(k string) *Name {186 if n, ok := w.names[k]; ok {187 return &n188 }189 return nil190}191192// putName stores (or with nil removes) the named range with key k,193// recording it for undo, and returns the formulas that use it, which need194// recalculating. Every change to names goes through here.195func (w *Workbook) putName(k string, n *Name) []loc {196 w.recordName(k)197 if n == nil {198 delete(w.names, k)199 } else {200 w.names[k] = *n201 }202 users := slices.Collect(maps.Keys(w.nameUsers[k]))203 if w.hist.open != nil {204 w.hist.dirty = append(w.hist.dirty, users...)205 }206 return users207}208209// remapNames moves the named ranges on this sheet along with the cells210// they cover, when rows or columns are inserted or deleted or cells are211// moved. A range whose cells are all gone is lost.212func (s *Sheet) remapNames(rng func(Rect) (Rect, bool)) {213 w := s.wb214 for k, n := range w.names {215 if n.Lost || n.Sheet != s {216 continue217 }218 next := n219 if r, ok := rng(n.Range); ok {220 next.Range = r221 } else {222 next.Range, next.Lost = Rect{}, true223 }224 if next != n {225 w.putName(k, &next)226 }227 }228}