1package sheet23import (4 "errors"5 "fmt"6 "maps"7 "slices"8 "strings"910 "src.rest/cat/012/internal/formula"11)1213// A Workbook is an ordered list of sheets, as a Google Sheets14// spreadsheet: each sheet has its own cells, column widths, frozen panes,15// filter and charts, while named ranges, the undo history and16// recalculation are shared, so a formula on one sheet can read another17// (=Sheet2!A1, ='Q3 plan'!B2:C9) and one undo step can span sheets.18//19// Formulas refer to other sheets by name. Renaming a sheet rewrites the20// formulas that name it; deleting one leaves them as written, showing21// #REF! ("Unresolved sheet name"), until a sheet of that name exists22// again, as in Sheets.23type Workbook struct {24 sheets []*Sheet25 byKey map[string]*Sheet // live sheets by sheetKey of their name2627 // names are the named ranges by upper-case name (see names.go), and28 // nameUsers the formula cells that mention each name, defined or not,29 // so defining a name recalculates the formulas waiting for it.30 names map[string]Name31 nameUsers map[string]map[loc]struct{}3233 // crossUsers are the formulas that reference a sheet by name. Like34 // range users, they are scanned when a cell changes, but only for35 // cells on sheets some formula names (crossKeys counts references by36 // sheetKey), so sheets nobody reads by name cost nothing.37 crossUsers map[loc]struct{}38 crossKeys map[string]int3940 // Circular is set when the last recalculation found a cycle.41 Circular bool42 // cycles keeps the buffers finding cycles takes (cyclefind.go).43 cycles *cycleGraph44 // spillCycles counts, while arrays settle, the times each was found45 // blocked by a cycle (spillblock.go).46 spillCycles map[loc]int47 // spillRechecked is set once an array still blocked by a cycle has48 // had every array checked again in the recalculation.49 spillRechecked bool50 // circArrays counts the arrays blocked by a cycle (spillblock.go),51 // so arrays that stop spilling look for them only when there are.52 circArrays int5354 remote RemoteSource // answers JEV functions, see remote.go55 trace any // the owner's telemetry trace, see observe.go56 // waiting are the formulas that were shown Loading… by question key,57 // so an answer recalculates only them; evaluating is the formula58 // being evaluated, which a question is asked for (see remote.go).59 waiting map[string]map[loc]struct{}60 evaluating loc61 // sources answers what formulas and pivots ask of linked sources;62 // src keeps who asked what (sourceask.go).63 sources SourceHost64 src sourceAsks65 // depth counts the cells and operators being evaluated, nested; see66 // evaluate.go.67 depth int68 hist history // undo and redo, see history.go69 author int // who makes the steps that follow: authors.go70 active int // the sheet last shown, saved in the file71 settings7273 macros []Macro // see macros.go74 macroOrigin string7576 nb notebookState // notebooks' outputs: notebook.go7778 // structural is set when sheets were added, deleted or renamed during79 // the open change, which then recalculates everything: references by80 // sheet name may now resolve differently.81 structural bool8283 // gen counts recalculations, so what is worked out from values84 // (the looks of rules) is kept until the next one.85 gen uint648687 // pivotDepth counts pivots refreshed because a pivot they read88 // changed, to stop a loop; see pivotlayout.go.89 pivotDepth int90 // spillWork is the arrays an evaluation pass computed, to spill once91 // it's done; see spill.go.92 spillWork spillWork93 // settling is set while regions write their cells; see regionwrite.go.94 settling bool95}9697// loc is a cell on a particular sheet.98type loc struct {99 s *Sheet100 a Addr101}102103// NewBook returns a workbook with one empty sheet, Sheet1.104func NewBook() *Workbook {105 w := emptyBook()106 w.insert(w.newSheet("Sheet1"), 0)107 return w108}109110// emptyBook returns a workbook without sheets, for loaders to fill. A111// workbook given to anyone else always has at least one sheet.112func emptyBook() *Workbook {113 return &Workbook{114 byKey: map[string]*Sheet{},115 names: map[string]Name{},116 nameUsers: map[string]map[loc]struct{}{},117 crossUsers: map[loc]struct{}{},118 crossKeys: map[string]int{},119 }120}121122// newSheet makes an empty sheet belonging to w, not yet in its list.123func (w *Workbook) newSheet(name string) *Sheet {124 return &Sheet{125 wb: w,126 name: name,127 cells: newCellStore(),128 widths: make(map[int]int),129 dependents: make(map[Addr]map[Addr]struct{}),130 volatile: make(map[Addr]struct{}),131 }132}133134// Sheets returns the sheets in tab order.135func (w *Workbook) Sheets() []*Sheet { return slices.Clone(w.sheets) }136137// Len returns the number of sheets.138func (w *Workbook) Len() int { return len(w.sheets) }139140// Sheet returns the sheet at index i in tab order.141func (w *Workbook) Sheet(i int) *Sheet { return w.sheets[i] }142143// Index returns s's position in tab order, or -1 if it was deleted.144func (w *Workbook) Index(s *Sheet) int { return slices.Index(w.sheets, s) }145146// Lookup finds a sheet by name, ignoring case.147func (w *Workbook) Lookup(name string) *Sheet { return w.byKey[formula.SheetKey(name)] }148149// Active returns the index of the sheet last shown, as saved in the file.150func (w *Workbook) Active() int { return clampInt(w.active, 0, len(w.sheets)-1) }151152// SetActive records which sheet is shown, for the file. It isn't an edit.153func (w *Workbook) SetActive(s *Sheet) {154 if i := w.Index(s); i >= 0 {155 w.active = i156 }157}158159// Name returns the sheet's name.160func (s *Sheet) Name() string { return s.name }161162// Book returns the workbook the sheet belongs to.163func (s *Sheet) Book() *Workbook { return s.wb }164165// Live reports whether the sheet is in its workbook, i.e. not deleted.166func (s *Sheet) Live() bool { return s.live }167168// maxSheetName is Excel's limit on a sheet name, kept so every workbook169// can be downloaded as .xlsx with its names intact. Sheets allows 100.170const maxSheetName = 31171172// ValidSheetName checks a name for a sheet, following Excel's rules (a173// superset of what Sheets accepts is fine to read, but names written by174// 012 must open in Excel too).175func ValidSheetName(name string) error {176 switch {177 case strings.TrimSpace(name) == "":178 return errors.New("Enter a sheet name")179 case len([]rune(name)) > maxSheetName:180 return fmt.Errorf("A sheet name can be at most %d characters", maxSheetName)181 case strings.ContainsAny(name, `:\/?*[]`):182 return errors.New(`A sheet name can't contain : \ / ? * [ or ]`)183 case strings.HasPrefix(name, "'") || strings.HasSuffix(name, "'"):184 return errors.New("A sheet name can't start or end with '")185 case strings.ContainsFunc(name, func(r rune) bool { return r < ' ' || r == 0x7f }):186 return errors.New("A sheet name can't contain control characters")187 }188 return nil189}190191// nextSheetName is the first free name among Sheet1, Sheet2, ... after192// the sheet count, as Sheets numbers new sheets.193func (w *Workbook) nextSheetName() string {194 for n := len(w.sheets) + 1; ; n++ {195 if name := fmt.Sprintf("Sheet%d", n); w.Lookup(name) == nil {196 return name197 }198 }199}200201// freeName returns base, or base with a number added ("Copy of Sheet1 2")202// until no sheet has the name, within the length limit.203func (w *Workbook) freeName(base string) string { return freeIn(base, w) }204205// checkName validates a new name for s (nil for a new sheet).206func (w *Workbook) checkName(s *Sheet, name string) error {207 if err := ValidSheetName(name); err != nil {208 return err209 }210 if other := w.Lookup(name); other != nil && other != s {211 return fmt.Errorf("There's already a sheet named %s", other.name)212 }213 return nil214}215216// AddSheet inserts a new, empty sheet at index at (clamped to the ends),217// as one undo step. An empty name picks the next SheetN.218func (w *Workbook) AddSheet(name string, at int) (*Sheet, error) {219 if name == "" {220 name = w.nextSheetName()221 }222 if err := w.checkName(nil, name); err != nil {223 return nil, err224 }225 s := w.newSheet(name)226 w.change(s, "insert sheet "+name, Rect{}, func() {227 w.recordSheets()228 w.insert(s, at)229 })230 return s, nil231}232233// DuplicateSheet copies s, cells, widths, heights, frozen panes, filter,234// merges, tables and charts, to a new sheet right after it named "Copy of ...", as Sheets'235// Duplicate; each table copied takes a name of its own (Sales_2). Formulas are copied as written, so references without a236// sheet name read the copy's own cells.237func (w *Workbook) DuplicateSheet(s *Sheet) (*Sheet, error) {238 if !s.live {239 return nil, errors.New("That sheet was deleted")240 }241 cp := w.newSheet(w.freeName("Copy of " + s.name))242 for a, c := range s.cells.all() {243 if _, ok := s.RegionAt(a); ok && c.spilled {244 c = frozen(c) // a region's table, as values: the copy has no regions245 }246 c := c.clone()247 c.setExpr(c.expr) // fresh reference slices, shared with nothing248 cp.place(a, c)249 }250 for c, width := range s.widths {251 cp.widths[c] = width252 }253 cp.heights = maps.Clone(s.heights)254 cp.lines = lineFormats{cols: maps.Clone(s.lines.cols), rows: maps.Clone(s.lines.rows), sheet: s.lines.sheet}255 cp.view = s.view256 cp.view.filter = s.view.filter.clone()257 cp.view.tables = w.copiedTables(s.view.tables)258 cp.charts = slices.Clone(s.charts)259 cp.rules = s.rules // never changed in place260 cp.pivot = pivotState{def: s.pivot.def.clone(), stale: s.pivot.def != nil, out: s.pivot.out, fit: s.pivot.fit}261 w.change(cp, "duplicate "+s.name, Rect{}, func() {262 w.recordSheets()263 w.insert(cp, w.Index(s)+1)264 })265 return cp, nil266}267268// DeleteSheet removes s, as one undo step. Formulas on other sheets that269// refer to it show #REF! until it's restored or another sheet takes its270// name. The last sheet can't be deleted.271func (w *Workbook) DeleteSheet(s *Sheet) error {272 i := w.Index(s)273 switch {274 case i < 0:275 return errors.New("That sheet was already deleted")276 case len(w.sheets) == 1:277 return errors.New("A spreadsheet needs at least one sheet")278 case !s.tabHidden && w.visibleCount() == 1:279 return errLastVisible280 }281 w.change(s, "delete sheet "+s.name, Rect{}, func() {282 w.recordSheets()283 w.remove(s)284 })285 return nil286}287288// RenameSheet renames s, as one undo step, rewriting every formula that289// refers to it by its old name.290func (w *Workbook) RenameSheet(s *Sheet, name string) error {291 if !s.live {292 return errors.New("That sheet was deleted")293 }294 if err := w.checkName(s, name); err != nil {295 return err296 }297 if name == s.name {298 return nil299 }300 old := formula.SheetKey(s.name)301 w.change(s, "rename "+s.name+" to "+name, Rect{}, func() {302 w.recordSheets()303 delete(w.byKey, old)304 s.name = name305 w.byKey[formula.SheetKey(name)] = s306 if old != formula.SheetKey(name) {307 w.renameRefs(old, name)308 }309 w.renamePivots(old, name)310 w.renameRules(old, name)311 })312 return nil313}314315// renameRefs rewrites references written with the sheet key old to name.316func (w *Workbook) renameRefs(old, name string) {317 rw := formula.Rewriter{318 Ref: func(n formula.Ref) Node {319 if n.Sheet != "" && formula.SheetKey(n.Sheet) == old {320 n.Sheet = name321 }322 return n323 },324 Range: func(n formula.Range) Node {325 if n.Sheet != "" && formula.SheetKey(n.Sheet) == old {326 n.Sheet = name327 }328 return n329 },330 }331 for _, l := range w.crossList() {332 if c := l.s.cells.get(l.a); c.readsSheet(old) {333 l.s.place(l.a, c.rewritten(rw))334 }335 }336}337338// MoveSheet moves s to index to in tab order, as one undo step.339func (w *Workbook) MoveSheet(s *Sheet, to int) {340 i := w.Index(s)341 to = clampInt(to, 0, len(w.sheets)-1)342 if i < 0 || i == to {343 return344 }345 w.change(s, "move sheet "+s.name, Rect{}, func() {346 w.recordSheets()347 order := slices.Delete(slices.Clone(w.sheets), i, i+1)348 w.sheets = slices.Insert(order, to, s)349 })350}351352// insert puts s into the list at index at and indexes its formulas.353func (w *Workbook) insert(s *Sheet, at int) {354 at = clampInt(at, 0, len(w.sheets))355 w.sheets = slices.Insert(slices.Clone(w.sheets), at, s)356 w.attach(s)357}358359// remove takes s out of the list and its formulas out of the indexes.360func (w *Workbook) remove(s *Sheet) {361 if i := w.Index(s); i >= 0 {362 w.sheets = slices.Delete(slices.Clone(w.sheets), i, i+1)363 w.detach(s)364 }365}366367// attach makes s live: findable by name, its formulas indexed for368// recalculation across sheets.369func (w *Workbook) attach(s *Sheet) {370 if s.live {371 return372 }373 s.live = true374 w.byKey[formula.SheetKey(s.name)] = s375 for a, c := range s.cells.richCells() {376 w.index(loc{s, a}, c)377 }378 w.structural = true379}380381// detach is the inverse of attach, for a deleted sheet. The sheet keeps382// its cells, so undo can bring it back as it was.383func (w *Workbook) detach(s *Sheet) {384 if !s.live {385 return386 }387 s.live = false388 if w.byKey[formula.SheetKey(s.name)] == s {389 delete(w.byKey, formula.SheetKey(s.name))390 }391 for a, c := range s.cells.richCells() {392 w.unindex(loc{s, a}, c)393 }394 w.structural = true395}396397// index adds a live formula's workbook-wide dependencies: the names it398// uses and its references to other sheets.399func (w *Workbook) index(l loc, c *Cell) {400 for _, k := range c.names {401 if w.nameUsers[k] == nil {402 w.nameUsers[k] = make(map[loc]struct{})403 }404 w.nameUsers[k][l] = struct{}{}405 }406 if len(c.xrefs) > 0 {407 w.crossUsers[l] = struct{}{}408 }409 for _, x := range c.xrefs {410 w.crossKeys[x.key]++411 }412}413414func (w *Workbook) unindex(l loc, c *Cell) {415 for _, k := range c.names {416 delete(w.nameUsers[k], l)417 if len(w.nameUsers[k]) == 0 {418 delete(w.nameUsers, k)419 }420 }421 delete(w.crossUsers, l)422 for _, x := range c.xrefs {423 if w.crossKeys[x.key]--; w.crossKeys[x.key] <= 0 {424 delete(w.crossKeys, x.key)425 }426 }427}428429// crossList returns the cross-sheet formulas, so callers may change them430// while going through the list.431func (w *Workbook) crossList() []loc {432 out := make([]loc, 0, len(w.crossUsers))433 for l := range w.crossUsers {434 out = append(out, l)435 }436 return out437}438439// resolve finds the sheet a reference written with sheet points at, from440// a formula on s: s itself for "", nil when no sheet has the name.441func (w *Workbook) resolve(s *Sheet, sheet string) *Sheet {442 if sheet == "" {443 return s444 }445 return w.byKey[formula.SheetKey(sheet)]446}447448// onThis reports, for a formula on from, whether a reference written with449// a sheet name ("" for none) points at s.450func (s *Sheet) onThis(from *Sheet) func(string) bool {451 return func(sheet string) bool { return s.wb.resolve(from, sheet) == s }452}453454// xref is a reference to a range on a sheet named in the formula.455type xref struct {456 key string // sheetKey of the name as written457 r Rect458}459460// readsSheet reports whether the cell's formula names the sheet with key k.461func (c *Cell) readsSheet(k string) bool {462 if c == nil {463 return false464 }465 for _, x := range c.xrefs {466 if x.key == k {467 return true468 }469 }470 return false471}472473// Batch runs fn as a single undo step across sheets, as Sheet.Batch; the474// step is shown on c.Sheet when undone.475func (w *Workbook) Batch(c Change, fn func() error) error {476 var err error477 w.change(c.Sheet, c.Label, c.Focus, func() { err = fn() })478 return err479}