1package sheet23import (4 "fmt"5 "slices"6 "time"7)89// A pivot's results are laid out as Sheets lays them out, from A1: header10// rows (one per column field, naming the field and its values, then one11// naming the row fields and the values), then a row per group of the row12// fields, their labels shown where they change, with a subtotal row after13// each outer group and a Grand Total row when row totals are on. Columns14// are the row labels, then each column group's values, then the Grand15// Total column's, with a subtotal column after each outer column group16// when column totals are on and there are several column fields.1718// pivotCell is one cell of a pivot's results.19type pivotCell struct {20 a Addr21 v Value22 f Format23 bold bool24}2526// pivotLayout is a pivot's results being laid out.27type pivotLayout struct {28 c *pivotCalc29 cells []pivotCell30 cols []*colGroup // the column groups shown, nil for the Grand Total31 lc int // label columns32 y int // the next row33 over bool // something fell off the sheet34}3536func (c *pivotCalc) layout() *pivotLayout {37 l := &pivotLayout{c: c, lc: max(len(c.p.Rows), 1)}38 if len(c.p.Values) > 0 {39 if len(c.p.Columns) == 0 {40 l.cols = []*colGroup{nil}41 } else {42 l.cols = c.columnOrder()43 if c.p.ColumnTotals {44 l.cols = append(l.cols, nil)45 }46 }47 }48 l.header()49 l.body()50 return l51}5253func (l *pivotLayout) put(col, row int, v Value, f Format, bold bool) {54 if col >= MaxCols || row >= MaxRows {55 l.over = true56 return57 }58 l.cells = append(l.cells, pivotCell{Addr{Col: col, Row: row}, v, f, bold})59}6061func (l *pivotLayout) text(col, row int, s string, bold bool) {62 l.put(col, row, Value{Kind: Text, Str: s}, Format{}, bold)63}6465// label shows a group's value in its own format; a group of blank cells66// has a blank label, as in Sheets.67func (l *pivotLayout) label(col, row int, v Value, f Format, bold bool) {68 if v.Kind == Empty {69 return70 }71 l.put(col, row, v, f, bold)72}7374func (l *pivotLayout) header() {75 c := l.c76 nv := len(c.p.Values)77 if nv > 0 {78 for k, g := range c.p.Columns {79 l.text(l.lc-1, l.y, c.w.FieldName(*c.p, g.Col), true)80 var prev *colGroup81 for j, cg := range l.cols {82 l.colHeader(l.lc+j*nv, k, prev, cg)83 prev = cg84 }85 l.y++86 }87 }88 for d, g := range c.p.Rows {89 l.text(d, l.y, c.w.FieldName(*c.p, g.Col), true)90 }91 for j := range l.cols {92 for i, v := range c.p.Values {93 l.text(l.lc+j*nv+i, l.y, c.w.ValueTitle(*c.p, v), true)94 }95 }96 l.y++97}9899// colHeader labels column group cg, after prev, in the header row of100// column field k at column x: the field's value where it changes, a101// subtotal's name in its outer field's row, Grand Total in the first.102func (l *pivotLayout) colHeader(x, k int, prev, cg *colGroup) {103 switch n := len(l.c.p.Columns); {104 case cg == nil && k == 0:105 l.text(x, l.y, "Grand Total", true)106 case cg == nil:107 case len(cg.labels) < n:108 if k == len(cg.labels)-1 {109 l.text(x, l.y, totalName(cg.labels[k], cg.formats[k]), true)110 }111 case prev == nil || len(prev.labels) <= k || !sameLabels(prev.labels[:k+1], cg.labels[:k+1]):112 // The outer group's spelling, the first seen, as rows show.113 og := l.c.outer(cg.labels[:k+1])114 l.label(x, l.y, og.labels[k], og.formats[k], true)115 }116}117118func sameLabels(a, b []Value) bool {119 for i := range a {120 if keyOf(a[i], true) != keyOf(b[i], true) {121 return false122 }123 }124 return true125}126127func (l *pivotLayout) body() {128 c := l.c129 if len(c.p.Rows) == 0 {130 if len(c.p.Values) > 0 {131 l.total(c.root, "Grand Total", 0)132 }133 return134 }135 l.rows(c.root, 0)136 if l.totals() {137 l.total(c.root, "Grand Total", 0)138 }139}140141// rows lays out n's groups at depth: a label where each starts, then its142// groups or, at the last depth, its values.143func (l *pivotLayout) rows(n *pivotNode, depth int) {144 last := depth == len(l.c.p.Rows)-1145 for _, kid := range n.kids {146 l.label(depth, l.y, kid.label, kid.format, false)147 if last {148 l.data(kid, false)149 l.y++150 continue151 }152 l.rows(kid, depth+1)153 if l.totals() {154 l.total(kid, totalName(kid.label, kid.format), depth)155 }156 }157}158159// totals reports whether total rows show: with row totals on, and values160// to total.161func (l *pivotLayout) totals() bool { return l.c.p.RowTotals && len(l.c.p.Values) > 0 }162163// total is a bold row of n's values labelled name in column col.164func (l *pivotLayout) total(n *pivotNode, name string, col int) {165 l.text(col, l.y, name, true)166 l.data(n, true)167 l.y++168}169170// data lays out n's values in the current row.171func (l *pivotLayout) data(n *pivotNode, bold bool) {172 nv := len(l.c.p.Values)173 for j, cg := range l.cols {174 id := -1175 if cg != nil {176 id = cg.id177 }178 for i := range nv {179 if v, ok := l.c.shown(n, id, i); ok {180 l.put(l.lc+j*nv+i, l.y, v, l.c.formats[i], bold)181 }182 }183 }184}185186// PivotInfo describes one pivot recomputation, for telemetry. It holds187// counts only, never contents.188type PivotInfo struct {189 Records int // source rows summarized190 Groups int // groups of the row fields191 Cells int // cells of the results192 Failed bool193 Duration time.Duration194}195196// OnPivot, when set, is called after every pivot recomputation, like197// OnRecalc (see OnBegin).198var OnPivot func(trace any, i PivotInfo)199200// maxPivotChain bounds pivots reading pivots that are refreshed in turn201// after one change.202const maxPivotChain = 16203204// stalePivots returns the sheets whose pivot needs recomputing: its205// definition changed, a cell in its results was overwritten, or a cell206// of its source range is being recalculated. It reads the sheets' calc207// marks, so it runs between affected and evaluate.208func (w *Workbook) stalePivots() []*Sheet {209 var out []*Sheet210 for _, s := range w.sheets {211 if s.pivot.stale {212 out = append(out, s)213 continue214 }215 p := s.pivot.def216 if p == nil {217 continue218 }219 if src := w.Lookup(p.Source); src != nil && src != s && touched(src.calc, p.Range) {220 out = append(out, s)221 }222 }223 return out224}225226func touched(calc map[Addr]int, r Rect) bool {227 for a := range calc {228 if r.Contains(a) {229 return true230 }231 }232 return false233}234235// allPivots returns every live sheet that has a pivot or was told to236// recompute one.237func (w *Workbook) allPivots() []*Sheet {238 var out []*Sheet239 for _, s := range w.sheets {240 if s.pivot.def != nil || s.pivot.stale {241 out = append(out, s)242 }243 }244 return out245}246247// refreshPivots recomputes the pivots of sheets, then recalculates what248// reads their results, which may be other pivots.249func (w *Workbook) refreshPivots(sheets []*Sheet) {250 var changed []loc251 for _, s := range sheets {252 changed = append(changed, w.refreshPivot(s)...)253 }254 if len(changed) == 0 {255 return256 }257 if w.pivotDepth >= maxPivotChain {258 w.Circular = true259 return260 }261 circular := w.Circular262 w.pivotDepth++263 w.recalc(changed)264 w.pivotDepth--265 w.Circular = w.Circular || circular266}267268// refreshPivot recomputes s's pivot and writes its results, returning the269// cells that changed.270func (w *Workbook) refreshPivot(s *Sheet) []loc {271 var start time.Time272 if OnPivot != nil {273 if OnBegin != nil {274 OnBegin(w.trace, "pivot")275 }276 start = time.Now()277 }278 s.pivot.stale = false279 want, calc, err := w.computePivot(s)280 s.pivot.err, s.pivot.blocked = "", Rect{}281 if err != nil {282 // Where the results would go, so clearing a cell in their way283 // has the pivot try again.284 s.pivot.blocked = extent(want)285 s.pivot.err = err.Error()286 want = []pivotCell{{v: ErrRef, bold: true}}287 if s.typedAt(Addr{}) {288 want = nil // never write over what the user typed289 }290 }291 s.pivot.out = extent(want)292 changed := s.writeDerived(want)293 if s.pivot.fit {294 s.fitPivot(want)295 }296 if OnPivot != nil {297 info := PivotInfo{Cells: len(want), Failed: err != nil, Duration: time.Since(start)}298 if calc != nil {299 info.Records, info.Groups = calc.records, calc.groups()300 }301 OnPivot(w.trace, info)302 }303 return changed304}305306// extent is the range from A1 covering cells.307func extent(cells []pivotCell) Rect {308 r := Rect{}309 for _, pc := range cells {310 r = union(r, Rect{From: pc.a, To: pc.a})311 }312 return r313}314315// groups counts the groups of the row fields, at every depth.316func (c *pivotCalc) groups() int {317 n := 0318 var walk func(p *pivotNode)319 walk = func(p *pivotNode) {320 n += len(p.kids)321 for _, k := range p.kids {322 walk(k)323 }324 }325 walk(c.root)326 return n327}328329// computePivot works out the cells of s's pivot, or why it can't.330func (w *Workbook) computePivot(s *Sheet) ([]pivotCell, *pivotCalc, error) {331 p := s.pivot.def332 if p == nil || !s.live {333 return nil, nil, nil334 }335 src := w.Lookup(p.Source)336 switch {337 case src == nil:338 return nil, nil, fmt.Errorf("The pivot table's source sheet %s doesn't exist", p.Source)339 case p.Lost:340 return nil, nil, fmt.Errorf("The pivot table's source range was deleted")341 case src == s:342 return nil, nil, errPivotSelf343 }344 for _, col := range p.fieldCols() {345 if col < p.Range.From.Col || col > p.Range.To.Col {346 return nil, nil, fmt.Errorf("Column %s is outside the pivot table's source range %s", ColName(col), p.Range)347 }348 }349 var c *pivotCalc350 if reg, ok := src.pagedRegion(); ok {351 var err error352 switch c, err = w.sourcePivot(s, reg, p); {353 case err != nil:354 return nil, nil, err355 case c == nil:356 return []pivotCell{{v: Pending}}, nil, nil357 }358 } else {359 c = newPivotCalc(w, p, sheetData{src})360 c.gather()361 }362 l := c.layout()363 if l.over {364 return nil, c, fmt.Errorf("The pivot table doesn't fit on a sheet")365 }366 for _, pc := range l.cells {367 if s.typedAt(pc.a) {368 return l.cells, c, fmt.Errorf("The pivot table's results would overwrite data in %s", pc.a)369 }370 }371 return l.cells, c, nil372}373374// fieldCols lists the source columns the pivot's fields read.375func (p *Pivot) fieldCols() []int {376 var cols []int377 for _, g := range slices.Concat(p.Rows, p.Columns) {378 cols = append(cols, g.Col)379 }380 for _, v := range p.Values {381 cols = append(cols, v.Col)382 }383 for _, f := range p.Filters {384 cols = append(cols, f.Col)385 }386 return cols387}388389// writeDerived makes the sheet's derived cells exactly want, touching only390// cells that differ, outside the undo history (undo restores the source391// and the definition, and the pivot is recomputed from them). It returns392// the cells that changed.393func (s *Sheet) writeDerived(want []pivotCell) []loc {394 next := make(map[Addr]*Cell, len(want))395 for _, pc := range want {396 c := &Cell{Input: derivedInput(pc.v), Value: pc.v, Format: pc.f, derived: true}397 c.Style.Bold = pc.bold398 next[pc.a] = c399 }400 var changed []loc401 var gone []Addr402 for a := range s.cells.pivotKeys() {403 if next[a] == nil {404 gone = append(gone, a)405 }406 }407 for _, a := range gone {408 s.setDerived(a, nil)409 changed = append(changed, loc{s, a})410 }411 for a, c := range next {412 if v, l, kind := s.cells.derivedOf(a); kind == slotPivot && v == c.Value && l.f == c.Format && l.st == c.Style {413 continue // its input is its value's414 }415 s.setDerived(a, c)416 changed = append(changed, loc{s, a})417 }418 return changed419}420421// typedAt reports whether the cell at a has contents that aren't the422// pivot's results: what the pivot mustn't write over.423func (s *Sheet) typedAt(a Addr) bool {424 if !s.cells.filledAt(a) {425 return false426 }427 return s.cells.derivedAt(a) != slotPivot428}429430// setDerived stores or removes a derived cell without recording it for431// undo. Derived cells have no formula, so there are no references to432// index.433func (s *Sheet) setDerived(a Addr, c *Cell) {434 s.version++435 s.trackShape(a, c)436 s.unlink(a)437 if c != nil {438 s.cells.set(a, c)439 }440}441442// maxPivotWidth caps how wide fitting makes a pivot's column.443const maxPivotWidth = 30444445// fitPivot widens the result's columns to their widest text, never below446// the default width. Numbers in the General format count at most447// maxGeneralFit characters: General shows fewer digits in a narrower448// column, as an average's 15 digits needn't all show.449func (s *Sheet) fitPivot(cells []pivotCell) {450 const maxGeneralFit = 10451 widest := map[int]int{}452 for _, pc := range cells {453 n := len([]rune(s.ShownText(pc.a)))454 if pc.v.Kind == Number && pc.f.IsZero() {455 n = min(n, maxGeneralFit)456 }457 widest[pc.a.Col] = max(widest[pc.a.Col], n)458 }459 for col, w := range widest {460 s.setWidth(col, clampInt(w+2, DefaultWidth, maxPivotWidth))461 }462}463464// derivedInput is what the formula bar shows for a result: the value.465func derivedInput(v Value) string {466 if v.Kind == Text {467 return v.Str468 }469 return valueInput(v)470}471472// plain returns a copy of c as an ordinary cell: a pivot's result becomes473// the constant it shows, so copying results pastes values.474func (c *Cell) plain() *Cell {475 if c == nil || !c.derived && !c.spilled {476 return c.clone()477 }478 p, err := newCell(valueInput(c.Value), c.Format, c.Style, false)479 if err != nil || p == nil {480 return &Cell{Input: "'" + c.Value.String(), Value: c.Value, Format: c.Format, Style: c.Style}481 }482 return p483}