1package sheet23import (4 "fmt"5 "slices"6 "strings"78 "src.rest/cat/012/internal/formula"9 "src.rest/cat/012/internal/functions"10)1112// How formulas read tables (table.go): a structured reference,13// Sales[Amount], is kept as written and turned into the range it names14// each time the formula is evaluated, as named ranges are (names.go), so15// a table that grows, or whose columns move, is followed without16// rewriting a formula. A formula reading a table is one of the table's17// users, by its name's key, so a change to the table's cells or to the18// table itself recalculates it.19//20// A name alone that names no range but a table stands for the table's21// data rows, Sales as Sales[]. A region's table is found by the region's22// name the same way: app[status], app alone for its rows under the23// header, and nu.app, the whole table with its header row, as app[#All].2425// tableView is a table as formulas read it: a table's, or a region's.26type tableView struct {27 s *Sheet28 r Rect // header row included29 cols []string // the columns' names30 ok bool // false for a region with no table shown: #REF!31 // opening is set on a source whose host hasn't opened it yet: its32 // references read Loading… until it has.33 opening bool34}3536// findTable finds the table or region formulas name with the key k.37func (w *Workbook) findTable(k string) (tableView, bool) {38 for _, s := range w.sheets {39 if i := s.tableIndex(k); i >= 0 {40 t := s.view.tables[i]41 return tableView{s: s, r: t.Range, cols: t.Cols, ok: true}, true42 }43 }44 for _, s := range w.sheets {45 if i := s.regionIndex(k); i >= 0 {46 if reg := s.regions.list[i]; reg.File.Paged {47 r, shape, ok := s.sourceTable(reg)48 return tableView{s: s, r: r, cols: shape.Cols, ok: ok, opening: !ok && s.sourceErr(reg) == ""}, true49 }50 r, ok := s.RegionTable(s.regions.list[i].Name)51 if !ok {52 return tableView{s: s}, true53 }54 return tableView{s: s, r: r, cols: s.rowTexts(r), ok: true}, true55 }56 }57 return tableView{}, false58}5960// rowTexts are the texts of r's first row, as shown.61func (s *Sheet) rowTexts(r Rect) []string {62 out := make([]string, 0, r.To.Col-r.From.Col+1)63 for c := r.From.Col; c <= r.To.Col; c++ {64 out = append(out, strings.TrimSpace(s.ShownText(Addr{Col: c, Row: r.From.Row})))65 }66 return out67}6869// col is the index of the column named name, ignoring case, or -1.70func (v tableView) col(name string) int {71 for i, c := range v.cols {72 if strings.EqualFold(c, name) {73 return i74 }75 }76 return -177}7879// rows are the first and last row it names on the table's sheet, for a80// formula at a on the sheet same says whether it's the table's.81func (v tableView) rows(it formula.TableItems, a Addr, same bool) (int, int, bool) {82 h, last := v.r.From.Row, v.r.To.Row83 if it == formula.ItemThisRow {84 if !same || a.Row <= h || a.Row > last {85 return 0, 0, false86 }87 return a.Row, a.Row, true88 }89 lo, hi := -1, -190 if it&formula.ItemHeaders != 0 {91 lo, hi = h, h92 }93 if it&formula.ItemData != 0 && last > h {94 if lo < 0 {95 lo = h + 196 }97 hi = last98 }99 return lo, hi, lo >= 0100}101102// colSpan is the first and last column t names on the sheet.103func (v tableView) colSpan(t formula.TableRef) (int, int, bool) {104 from, to := t.Cols()105 if from == "" {106 return v.r.From.Col, v.r.To.Col, true107 }108 i, j := v.col(from), v.col(to)109 if i < 0 || j < 0 {110 return 0, 0, false111 }112 return v.r.From.Col + min(i, j), v.r.From.Col + max(i, j), true113}114115// bindTable is what the structured reference t in a formula at a on s116// stands for: a cell or a range, or #REF! when the table, a column or117// the row it names isn't there. thisRow is the absolute markers a118// reference to the formula's own row takes; every other is absolute.119func (s *Sheet) bindTable(a Addr, t formula.TableRef, thisRow formula.Abs) Node {120 v, found := s.wb.findTable(nameKey(t.Table))121 if found && v.opening {122 return functions.Const(Pending)123 }124 if !found || !v.ok {125 return formula.RefErr{}126 }127 lo, hi, okRows := v.rows(t.Rows(), a, v.s == s)128 c0, c1, okCols := v.colSpan(t)129 if !okRows || !okCols {130 return formula.RefErr{}131 }132 sheet := ""133 if v.s != s {134 sheet = v.s.name135 }136 abs := formula.AbsCol | formula.AbsRow137 if t.Items == formula.ItemThisRow {138 abs = thisRow139 }140 r := Rect{From: Addr{Col: c0, Row: lo}, To: Addr{Col: c1, Row: hi}}141 if r.From == r.To {142 return formula.Ref{Addr: r.From, Abs: abs, Sheet: sheet}143 }144 return formula.Range{Rect: r, Abs: [2]formula.Abs{abs, abs}, Sheet: sheet}145}146147// bound returns the formula at a in c with its names and structured148// references replaced by the ranges they stand for: what is evaluated149// and what dependencies are traced through. Undefined names stay, and150// evaluate to #NAME?.151func (s *Sheet) bound(a Addr, c *Cell) Node {152 if len(c.names) == 0 {153 return c.expr154 }155 const fixed = formula.AbsCol | formula.AbsRow156 n, _ := formula.Rewrite(c.expr, formula.Rewriter{157 Name: func(nn formula.Name) Node {158 nm, ok := s.wb.names[nameKey(nn.Name)]159 if !ok {160 return s.boundTableName(a, nn)161 }162 if nm.Gone() {163 return formula.RefErr{}164 }165 sheet := ""166 if nm.Sheet != s {167 sheet = nm.Sheet.name168 }169 if nm.Range.From == nm.Range.To {170 return formula.Ref{Addr: nm.Range.From, Abs: fixed, Sheet: sheet}171 }172 return formula.Range{Rect: nm.Range, Abs: [2]formula.Abs{fixed, fixed}, Sheet: sheet}173 },174 Table: func(t formula.TableRef) Node { return s.bindTable(a, t, fixed) },175 })176 return n177}178179// boundTableName is a name in a formula at a that isn't a named range:180// a table's data rows (Sales), a region's whole table (nu.app), or the181// name as written, which shows #NAME?.182func (s *Sheet) boundTableName(a Addr, nn formula.Name) Node {183 if _, found := s.wb.findTable(nameKey(nn.Name)); found {184 return s.bindTable(a, formula.TableRef{Table: nn.Name}, formula.AbsCol|formula.AbsRow)185 }186 return s.boundRegion(nn)187}188189// tablesInUse are the tables and regions formulas name, by structured190// references or their names alone, with their cells, for finding what191// reads a changed cell. A region named as nu.app is among regionsInUse.192func (w *Workbook) tablesInUse() []namedUsers {193 var out []namedUsers194 for k, users := range w.nameUsers {195 if _, isName := w.names[k]; isName {196 continue197 }198 if v, ok := w.findTable(k); ok && v.ok {199 out = append(out, namedUsers{s: v.s, r: v.r, users: users})200 }201 }202 return out203}204205// renameTableInFormulas rewrites the formulas reading the table with206// key from to name it to instead.207func (w *Workbook) renameTableInFormulas(from, to string) {208 w.rewriteUsers(from, formula.Rewriter{209 Name: func(n formula.Name) Node {210 if nameKey(n.Name) == from {211 return formula.Name{Name: to}212 }213 return n214 },215 Table: func(t formula.TableRef) Node {216 if nameKey(t.Table) == from {217 t.Table = to218 }219 return t220 },221 })222}223224// renameColumnInFormulas rewrites the structured references to the225// column old of the table with key k to name the column name instead.226func (w *Workbook) renameColumnInFormulas(k, old, name string) {227 w.rewriteUsers(k, formula.Rewriter{Table: func(t formula.TableRef) Node {228 if nameKey(t.Table) != k {229 return t230 }231 if strings.EqualFold(t.From, old) {232 t.From = name233 }234 if strings.EqualFold(t.To, old) {235 t.To = name236 }237 return t238 }})239}240241// unbindTable rewrites the formulas reading the table with key k to242// read the cells it stands for by address: its column in a row of its243// own relative to the formula (C5), the rest absolute ($C$2:$C$9).244func (w *Workbook) unbindTable(k string) {245 var users []loc246 for u := range w.nameUsers[k] {247 users = append(users, u)248 }249 for _, u := range users {250 c := u.s.cells.get(u.a)251 out := c.rewritten(formula.Rewriter{252 Name: func(n formula.Name) Node {253 if nameKey(n.Name) != k {254 return n255 }256 if _, isName := w.names[k]; isName {257 return n258 }259 return u.s.bindTable(u.a, formula.TableRef{Table: n.Name}, 0)260 },261 Table: func(t formula.TableRef) Node {262 if nameKey(t.Table) != k {263 return t264 }265 return u.s.bindTable(u.a, t, formula.AbsCol)266 },267 })268 if out != c {269 u.s.place(u.a, out)270 }271 }272}273274// rewriteUsers rewrites the formulas using the name or table with key275// k with rw.276func (w *Workbook) rewriteUsers(k string, rw formula.Rewriter) {277 var users []loc278 for u := range w.nameUsers[k] {279 users = append(users, u)280 }281 for _, u := range users {282 c := u.s.cells.get(u.a)283 if out := c.rewritten(rw); out != c {284 u.s.place(u.a, out)285 }286 }287}288289// TableInfo is a table as formulas find it: a table's, or a region's.290type TableInfo struct {291 Name string292 Sheet *Sheet293 // Range is its cells, header row included, when Shown.294 Range Rect295 shown bool296 Cols []string297 // Region is set on a region's table, which its source names and298 // shapes: the commands that change tables leave it alone.299 Region bool300}301302// Shown reports whether the table has cells: a region's may have none303// yet.304func (t TableInfo) Shown() bool { return t.shown }305306// TableInfos returns every table formulas can read, sheet by sheet:307// each sheet's tables in the order they were made, then its regions'.308func (w *Workbook) TableInfos() []TableInfo {309 var out []TableInfo310 for _, s := range w.sheets {311 for _, t := range s.view.tables {312 out = append(out, TableInfo{Name: t.Name, Sheet: s, Range: t.Range, shown: true, Cols: slices.Clone(t.Cols)})313 }314 for _, r := range s.regions.list {315 v, _ := w.findTable(nameKey(r.Name))316 out = append(out, TableInfo{Name: r.Name, Sheet: s, Range: v.r, shown: v.ok, Cols: v.cols, Region: true})317 }318 }319 return out320}321322// LookupTable finds a table formulas can read by name, ignoring case.323func (w *Workbook) LookupTable(name string) (TableInfo, bool) {324 k := nameKey(name)325 for _, t := range w.TableInfos() {326 if nameKey(t.Name) == k {327 return t, true328 }329 }330 return TableInfo{}, false331}332333// TableLook says how the cell at a draws as part of a table: in the334// header row's style, or on a band (every other data row), when its335// table has them.336func (s *Sheet) TableLook(a Addr) (header, band bool) {337 for i := range s.view.tables {338 t := &s.view.tables[i]339 if !t.Range.Contains(a) {340 continue341 }342 if a.Row == t.Range.From.Row {343 return t.Header, false344 }345 return false, t.Banded && (a.Row-t.Range.From.Row)%2 == 0346 }347 return false, false348}349350// TableRange finds the cells a structured reference written alone351// stands for (Sales[Amount], Sales[#All]), as a formula outside the352// table reads them, or with a table's name alone its whole table,353// header row included. ok is false when ref is neither; err says why354// one names no cells.355func (w *Workbook) TableRange(ref string) (s *Sheet, r Rect, ok bool, err error) {356 n, perr := formula.Parse(ref, parserFuncs)357 if perr != nil {358 return nil, Rect{}, false, nil359 }360 var t formula.TableRef361 switch n := n.(type) {362 case formula.TableRef:363 t = n364 case formula.Name:365 t = formula.TableRef{Table: n.Name, Items: formula.ItemAll}366 default:367 return nil, Rect{}, false, nil368 }369 v, found := w.findTable(nameKey(t.Table))370 switch {371 case !found:372 if _, isName := n.(formula.Name); isName {373 return nil, Rect{}, false, nil374 }375 return nil, Rect{}, true, fmt.Errorf("there's no table named %s", t.Table)376 case !v.ok:377 return nil, Rect{}, true, fmt.Errorf("%s has no rows yet", t.Table)378 }379 lo, hi, okRows := v.rows(t.Rows(), Addr{}, false)380 c0, c1, okCols := v.colSpan(t)381 switch {382 case !okCols:383 return nil, Rect{}, true, fmt.Errorf("%s has no such column (columns: %s)", t.Table, strings.Join(v.cols, ", "))384 case !okRows:385 return nil, Rect{}, true, fmt.Errorf("%s names no rows of %s here: a formula's own row, or rows it hasn't", ref, t.Table)386 }387 return v.s, Rect{From: Addr{Col: c0, Row: lo}, To: Addr{Col: c1, Row: hi}}, true, nil388}