1package sheet23import (4 "slices"5 "strings"67 "src.rest/cat/012/internal/formula"8)910// Tracing, as Excel's Trace Precedents and Trace Dependents: which cells11// a formula reads, and which formulas read a cell, on any sheet. Only12// direct links are followed; tracing again from a found cell goes a level13// further. The dependency indexes recalculation keeps answer both ways,14// so tracing costs what it finds, not the sheet: a cell's precedents are15// its formula's references, and its dependents come from the indexes by16// cell, by range and by name (tracedeps.go).1718// Target is a traced cell or range and the sheet it is on.19type Target struct {20 Sheet *Sheet21 Range Rect22}2324// LinkKind is how a traced cell is linked to the one traced.25type LinkKind uint82627const (28 // LinkRef is a reference or range the formula writes out, or a29 // formula reading the cell through one.30 LinkRef LinkKind = iota31 // LinkName is a named range the formula uses: Link.Name is its name.32 LinkName33 // LinkRegion is a region's table the formula names, nu.sales:34 // Link.Name is how the formula names it.35 LinkRegion36 // LinkSpill is the array formula whose values the cell shows, among37 // precedents, or the cells a formula spills into, among dependents.38 LinkSpill39 // LinkFile is the linked file a region's cells come from: Link.Name40 // is its path, and Target the region's cells.41 LinkFile42 // LinkTable is a table's cells a formula reads by a structured43 // reference or the table's name alone: Link.Name is the reference as44 // written, Sales[Amount].45 LinkTable46 // LinkOutput is the notebook cell whose output a region shows:47 // Link.Name is the cell's name, Target.Sheet the notebook tab and48 // Target.Range.From.Row the cell's index there.49 LinkOutput50)5152// Link is a precedent or dependent: where it is, and how it's linked.53type Link struct {54 Target55 Kind LinkKind56 Name string // see LinkKind57}5859// OnGrid reports whether the link is cells of a sheet's grid rather than60// a notebook cell.61func (l Link) OnGrid() bool { return !l.Sheet.IsNotebook() }6263// Precedents returns the cells and ranges the formula at a reads, in the64// order the formula mentions them; see PrecedentLinks.65func (s *Sheet) Precedents(a Addr) []Target { return targetsOf(s.PrecedentLinks(a)) }6667// Dependents returns the formula cells that read a directly; see68// DependentLinks.69func (s *Sheet) Dependents(a Addr) []Target {70 links, _ := s.DependentLinks(a, 0)71 return targetsOf(links)72}7374func targetsOf(links []Link) []Target {75 var out []Target76 for _, l := range links {77 if l.OnGrid() {78 out = append(out, l.Target)79 }80 }81 return out82}8384// PrecedentLinks returns what the cell at a comes from. For a formula,85// the cells and ranges it reads in the order it mentions them, named86// ranges and regions as their cells, with repeats dropped and references87// to sheets that don't exist left out. For a cell an array spilled into,88// the formula that spilled it; for a region's cell, the linked file or89// notebook cell the region shows.90func (s *Sheet) PrecedentLinks(a Addr) []Link {91 var out []Link92 add := func(l Link) {93 if l.Sheet != nil && !slices.ContainsFunc(out, func(o Link) bool { return o.Target == l.Target && o.Kind == l.Kind }) {94 out = append(out, l)95 }96 }97 if c, _, _ := s.cells.peek(a); c != nil && c.IsFormula() {98 s.walkPrecedents(c.expr, a, add)99 }100 if anchor, ok := s.SpillAnchor(a); ok {101 add(Link{Target: Target{s, Rect{From: anchor, To: anchor}}, Kind: LinkSpill})102 }103 if r, ok := s.RegionAt(a); ok {104 add(s.regionSource(r))105 }106 return out107}108109// walkPrecedents calls add with each reference, range, name and table110// in n, the formula at a, in the order they're written.111func (s *Sheet) walkPrecedents(n Node, a Addr, add func(Link)) {112 w := s.wb113 switch n := n.(type) {114 case formula.Ref:115 add(Link{Target: Target{w.resolve(s, n.Sheet), Rect{From: n.Addr, To: n.Addr}}})116 case formula.Range:117 add(Link{Target: Target{w.resolve(s, n.Sheet), n.Rect}})118 case formula.Name:119 if nm, ok := w.names[nameKey(n.Name)]; ok {120 if !nm.Gone() {121 add(Link{Target: Target{nm.Sheet, nm.Range}, Kind: LinkName, Name: nm.Name})122 }123 return124 }125 if _, found := w.findTable(nameKey(n.Name)); found {126 s.tableLink(a, formula.TableRef{Table: n.Name}, n.Name, add)127 return128 }129 if t, r, ok := w.regionName(nameKey(n.Name)); ok && r != (Rect{}) {130 add(Link{Target: Target{t, r}, Kind: LinkRegion, Name: s.regionSpelling(n.Name)})131 }132 case formula.TableRef:133 s.tableLink(a, n, n.String(), add)134 default:135 formula.EachChild(n, func(k Node) { s.walkPrecedents(k, a, add) })136 }137}138139// tableLink adds the cells the structured reference t, written as name140// in the formula at a, reads, when it reads any.141func (s *Sheet) tableLink(a Addr, t formula.TableRef, name string, add func(Link)) {142 var r Target143 switch n := s.bindTable(a, t, 0).(type) {144 case formula.Ref:145 r = Target{s.wb.resolve(s, n.Sheet), Rect{From: n.Addr, To: n.Addr}}146 case formula.Range:147 r = Target{s.wb.resolve(s, n.Sheet), n.Rect}148 default:149 return150 }151 add(Link{Target: r, Kind: LinkTable, Name: name})152}153154// regionSpelling is how formulas name the region a name like NU.SALES155// finds: nu. and the region's own name.156func (s *Sheet) regionSpelling(name string) string {157 if _, r, ok := s.wb.Region(name[len(regionPrefix):]); ok {158 return r.FormulaName()159 }160 return strings.ToLower(name)161}162163// regionSource is where a region's cells come from: its linked file, or164// the notebook cell whose output it shows (on the notebook's tab, when165// there is one; otherwise the region itself).166func (s *Sheet) regionSource(r Region) Link {167 area := Target{s, s.covered(r)}168 if r.Linked() {169 return Link{Target: area, Kind: LinkFile, Name: r.File.Path}170 }171 for _, nb := range s.wb.sheets {172 for i, c := range nb.regions.cells {173 if strings.EqualFold(c.Name(), r.Name) {174 return Link{Target: Target{nb, Rect{From: Addr{Row: i}, To: Addr{Row: i}}}, Kind: LinkOutput, Name: c.Name()}175 }176 }177 }178 return Link{Target: area, Kind: LinkOutput, Name: r.Name}179}