1package sheet23import (4 "slices"5 "strings"67 "src.rest/cat/012/internal/formula"8 "src.rest/cat/012/internal/functions"9)1011// ExplainError says why the cell at a shows an error, for the context12// line: where the error starts and what went wrong, e.g. "Division by13// zero in B3/0", or "From B5: division by zero in B3/0" when it comes from14// another cell. It is empty for cells without errors.15func (s *Sheet) ExplainError(a Addr) string {16 return s.explain(a, nil)17}1819// label names a cell for an explanation, with its sheet when it isn't on20// the sheet being explained: B3 or Sheet2!B3.21func (l loc) label(from *Sheet) string {22 if l.s == from {23 return l.a.String()24 }25 return formula.QuoteSheet(l.s.name) + "!" + l.a.String()26}2728// maxExplainExpr caps how much of a formula an explanation quotes.29const maxExplainExpr = 403031func (s *Sheet) explain(a Addr, path []loc) string {32 here := loc{s, a}33 c := s.cells.get(a)34 v := s.Value(a)35 if c != nil && c.derived && v.Kind == Error && s.pivot.err != "" {36 return s.pivot.err37 }38 if why := s.spillError(a); why != "" && v == ErrRef {39 return why40 }41 if anchor, ok := s.SpillAnchor(a); ok && v.Kind == Error {42 why := s.explain(anchor, path)43 if why == "" {44 return "Spilled from " + anchor.String()45 }46 return "Spilled from " + anchor.String() + ": " + strings.ToLower(why[:1]) + why[1:]47 }48 if c == nil || v.Kind != Error || !c.IsFormula() {49 return ""50 }51 if why := s.sourceFailure(here, c); why != "" {52 return why53 }54 // Cells are named relative to the sheet the explanation starts on.55 home := s56 if len(path) > 0 {57 home = path[0].s58 }59 if i := slices.Index(path, here); i >= 0 {60 names := make([]string, 0, len(path)-i+1)61 for _, p := range path[i:] {62 names = append(names, p.label(home))63 }64 return "Circular reference: " + strings.Join(append(names, here.label(home)), " → ")65 }66 path = append(path, here)67 n, from := s.errorOrigin(c.expr, a, v)68 if from != nil {69 why := from.s.explain(from.a, path)70 name := from.label(home)71 if why == "" {72 return "From " + name73 }74 if strings.HasPrefix(why, "Circular") || strings.HasPrefix(why, "From ") {75 return why76 }77 return "From " + name + ": " + strings.ToLower(why[:1]) + why[1:]78 }79 if r, ok := n.(formula.Ref); ok && s.wb.resolve(s, r.Sheet) == nil {80 return "Unresolved sheet name " + formula.QuoteSheet(r.Sheet)81 }82 if r, ok := n.(formula.Range); ok && s.wb.resolve(s, r.Sheet) == nil {83 return "Unresolved sheet name " + formula.QuoteSheet(r.Sheet)84 }85 return describeError(v, n)86}8788// errorOrigin finds the innermost part of n that produces the error want89// itself rather than passing it on. When the error comes from a90// referenced cell, it returns that cell. The formula is the one at here.91func (s *Sheet) errorOrigin(n Node, here Addr, want Value) (Node, *loc) {92 return errorSearch{s: s, here: here, want: want, get: s.wb.values(s)}.find(n)93}9495// errorSearch follows an error through a formula on s to where it starts.96type errorSearch struct {97 s *Sheet98 here Addr // the formula's cell, for ranges read as one of their cells99 want Value100 get *reader101}102103func (e errorSearch) same(v Value) bool { return v.Kind == Error && v.Str == e.want.Str }104105// at is the cell a reference points at, or nil when its sheet name is106// unresolved: then the reference is the error itself.107func (e errorSearch) at(sheet string, a Addr) *loc {108 if t := e.s.wb.resolve(e.s, sheet); t != nil {109 return &loc{t, a}110 }111 return nil112}113114func (e errorSearch) find(n Node) (Node, *loc) {115 switch n := n.(type) {116 case formula.Ref:117 if e.same(e.get.Cell(n.Sheet, n.Addr)) {118 return n, e.at(n.Sheet, n.Addr)119 }120 case formula.Range:121 if a, ok := functions.Intersect(n.Rect, e.here); ok && e.same(e.get.Cell(n.Sheet, a)) {122 return n, e.at(n.Sheet, a)123 }124 case formula.Unary:125 return e.operand(n, n.X)126 case formula.Binary:127 return e.operand(n, n.L, n.R)128 case formula.Call:129 return e.inCall(n)130 }131 return n, nil132}133134// operand follows the first of n's operands that has the error, or stops135// at n when none has.136func (e errorSearch) operand(n Node, operands ...Node) (Node, *loc) {137 for _, x := range operands {138 if e.same(functions.EvalAt(x, e.get.lib, e.here)) {139 return e.find(x)140 }141 }142 return n, nil143}144145// inCall follows the first argument of a call that has the error: for a146// range, its first cell with it.147func (e errorSearch) inCall(n formula.Call) (Node, *loc) {148 if funcOf(n).Remote() {149 return n, nil // JEV explains its own answers150 }151 for _, arg := range n.Args {152 if r, ok := arg.(formula.Range); ok && r.Rect.From != r.Rect.To {153 if l, found := e.inRange(r); found {154 return arg, l155 }156 continue157 }158 if e.same(functions.EvalAt(arg, e.get.lib, e.here)) {159 return e.find(arg)160 }161 }162 return n, nil163}164165// inRange finds the first cell of r, row by row, with the error. An166// unresolved sheet name is found, as the error itself, with no cell.167func (e errorSearch) inRange(r formula.Range) (*loc, bool) {168 t := e.s.wb.resolve(e.s, r.Sheet)169 if t == nil {170 return nil, true171 }172 cells := t.cellsIn(r.Rect)173 sortAddrs(cells)174 for _, a := range cells {175 if e.same(t.Value(a)) {176 return &loc{t, a}, true177 }178 }179 return nil, false180}181182// lookupFuncs report #N/A when they find no match.183var lookupFuncs = []string{"VLOOKUP", "HLOOKUP", "XLOOKUP", "LOOKUP", "MATCH", "XMATCH"}184185// describeError explains an error code produced by n.186func describeError(v Value, n Node) string {187 expr := formula.Expr(n)188 if r := []rune(expr); len(r) > maxExplainExpr {189 expr = string(r[:maxExplainExpr-1]) + "…"190 }191 switch v.Str {192 case ErrDiv0.Str:193 return "Division by zero in " + expr194 case ErrValue.Str:195 if _, ok := n.(formula.Range); ok {196 return "A range where one value is expected: " + expr197 }198 return "Wrong type of value in " + expr199 case ErrName.Str:200 if nm, ok := n.(formula.Name); ok {201 return "Unknown name " + nm.Name202 }203 return "Unknown name in " + expr204 case ErrNA.Str:205 if call, ok := n.(formula.Call); ok && slices.Contains(lookupFuncs, funcOf(call).Name) {206 return "No match found by " + expr207 }208 return "Value not available in " + expr209 case ErrNum.Str:210 return "Number out of range in " + expr211 case ErrRef.Str:212 if _, ok := n.(formula.RefErr); ok {213 return "Reference to deleted cells"214 }215 return "Invalid reference in " + expr216 }217 return "Error in " + expr218}