1package sheet23import (4 "errors"5 "fmt"6 "reflect"7 "slices"89 "src.rest/cat/012/internal/formula"10)1112// A pivot table summarizes a range of another sheet, as Sheets' Insert >13// Pivot table: rows grouped by the values of some columns (Rows), spread14// across others (Columns), with values summarized per group (Values), and15// rows left out by Filters. It lives on a sheet of its own, starting at16// A1. The engine owns its results: they are derived cells, recomputed17// whenever the source's cells change, never saved (the file keeps the18// definition) and never edited by hand. A sheet has at most one pivot.19type Pivot struct {20 // Source names the sheet the data is on. Like a reference written21 // with a sheet name, it follows renames, and while no sheet has the22 // name the pivot shows #REF!.23 Source string24 // Range is the data on the source sheet; its first row holds the25 // headers, which name the fields.26 Range Rect27 Rows []PivotGroup28 Columns []PivotGroup29 Values []PivotValue30 Filters []PivotFilter31 // RowTotals adds a Grand Total row, and a subtotal row after each32 // outer group when there are several row groups. ColumnTotals adds a33 // Grand Total column when there are column groups, and a subtotal34 // column after each outer group when there are several.35 RowTotals, ColumnTotals bool36 // Lost is set when the source range was deleted.37 Lost bool38}3940// PivotGroup is a column of the source whose distinct values become rows41// or columns of the pivot.42type PivotGroup struct {43 Col int // column on the source sheet44 Desc bool // Z to A, or largest first45 // SortBy orders the groups by their label (0) or by the grand total46 // of Values[SortBy-1].47 SortBy int48}4950// PivotValue is a column of the source summarized per group.51type PivotValue struct {52 Col int53 Summarize Summarize54 ShowAs ShowAs55 Name string // the header; "" for Sheets' "SUM of Sales"56}5758// PivotFilter leaves out the source rows whose value in Col doesn't meet59// Criteria, as a filter's column does.60type PivotFilter struct {61 Col int62 Criteria Criteria63}6465// ShowAs is how a summarized value shows: as itself, or as a share of a66// total, as Sheets' "Show as".67type ShowAs uint86869const (70 ShowValue ShowAs = iota71 ShowPctRow72 ShowPctColumn73 ShowPctTotal74 numShowAs75)7677var showAsNames = [numShowAs]string{"", "percent_of_row", "percent_of_column", "percent_of_total"}78var showAsTitles = [numShowAs]string{"Default", "% of row", "% of column", "% of grand total"}7980// ShowAsList lists the choices in Sheets' order.81func ShowAsList() []ShowAs { return []ShowAs{ShowValue, ShowPctRow, ShowPctColumn, ShowPctTotal} }8283func (s ShowAs) String() string { return showAsNames[s] }84func (s ShowAs) Title() string { return showAsTitles[s] }8586// ParseShowAs is the inverse of ShowAs.String.87func ParseShowAs(name string) (ShowAs, bool) {88 i := slices.Index(showAsNames[:], name)89 return ShowAs(max(i, 0)), i >= 090}9192// Errors of pivot tables.93var (94 // ErrPivotEdit is what editing a pivot table's results says, as95 // Sheets refuses to.96 ErrPivotEdit = errors.New("Pivot table results can't be edited: change the pivot with Data > Edit pivot table")97 errPivotSelf = errors.New("A pivot table can't summarize its own sheet")98 errPivotLoop = errors.New("A pivot table can't summarize a pivot table that reads it")99)100101// pivotState is what a sheet keeps about its pivot. def is the definition102// (in the undo history and the file, never modified in place); the rest103// is derived from it and the source.104type pivotState struct {105 def *Pivot106 stale bool // recompute at the end of the current change107 out Rect // what the last result covered, from A1108 err string // why the pivot shows #REF!, or ""109 // blocked is where results in the way of cells would have gone, so110 // clearing those cells recomputes the pivot.111 blocked Rect112 // fit has the result's columns widened to fit, while the sheet is113 // the pivot's own: from its creation until a width is set by hand.114 // The widths aren't undo steps; undoing the creation removes them115 // with the sheet.116 fit bool117}118119// clone returns a deep copy, so a stored pivot never shares slices.120func (p *Pivot) clone() *Pivot {121 if p == nil {122 return nil123 }124 cp := *p125 cp.Rows, cp.Columns, cp.Values = slices.Clone(p.Rows), slices.Clone(p.Columns), slices.Clone(p.Values)126 cp.Filters = make([]PivotFilter, len(p.Filters))127 for i, f := range p.Filters {128 f.Criteria.Hidden = slices.Clone(f.Criteria.Hidden)129 cp.Filters[i] = f130 }131 if len(p.Filters) == 0 {132 cp.Filters = nil133 }134 return &cp135}136137func samePivot(a, b *Pivot) bool {138 if a == nil || b == nil {139 return a == b140 }141 return reflect.DeepEqual(a.clone(), b.clone())142}143144// Pivot returns a copy of the sheet's pivot table, and false if it has145// none.146func (s *Sheet) Pivot() (Pivot, bool) {147 if s.pivot.def == nil {148 return Pivot{}, false149 }150 return *s.pivot.def.clone(), true151}152153// PivotRange returns the cells the pivot's results cover, from A1 (at154// least A1, even while it shows nothing), and false without a pivot.155func (s *Sheet) PivotRange() (Rect, bool) {156 if s.pivot.def == nil {157 return Rect{}, false158 }159 return s.pivot.out, true160}161162// InPivot reports whether r overlaps the pivot table's results, which163// can't be edited.164func (s *Sheet) InPivot(r Rect) bool {165 out, ok := s.PivotRange()166 return ok && out.From.Col <= r.To.Col && r.From.Col <= out.To.Col &&167 out.From.Row <= r.To.Row && r.From.Row <= out.To.Row168}169170// PivotError says why the pivot shows #REF!, or "" when it doesn't.171func (s *Sheet) PivotError() string { return s.pivot.err }172173// PivotSource returns the sheet the pivot reads, or nil when no sheet has174// its name.175func (s *Sheet) PivotSource() *Sheet {176 if s.pivot.def == nil {177 return nil178 }179 return s.wb.Lookup(s.pivot.def.Source)180}181182// NextPivotName is the name Sheets gives a new pivot sheet: Pivot Table 1,183// then 2 and so on.184func (w *Workbook) NextPivotName() string {185 for n := 1; ; n++ {186 if name := fmt.Sprintf("Pivot Table %d", n); w.Lookup(name) == nil {187 return name188 }189 }190}191192// CreatePivot adds a sheet named name ("" for the next Pivot Table N)193// right after src, holding p over r on src, as one undo step.194func (w *Workbook) CreatePivot(src *Sheet, r Rect, name string, p Pivot) (*Sheet, error) {195 if name == "" {196 name = w.NextPivotName()197 }198 name = w.freeName(name)199 if err := w.checkName(nil, name); err != nil {200 return nil, err201 }202 p.Source, p.Range = src.name, r203 s := w.newSheet(name)204 w.change(s, "create "+name, Rect{}, func() {205 w.recordSheets()206 w.insert(s, w.Index(src)+1)207 s.pivot = pivotState{def: p.clone(), stale: true, fit: true}208 })209 return s, nil210}211212// SetPivot replaces the sheet's pivot definition as one undo step,213// labelled label ("edit pivot table" when empty).214func (s *Sheet) SetPivot(p Pivot, label string) error {215 if err := s.checkPivot(p); err != nil {216 return err217 }218 if samePivot(&p, s.pivot.def) {219 return nil220 }221 if label == "" {222 label = "edit pivot table"223 }224 s.change(label, Rect{}, func() { s.putPivot(p.clone()) })225 return nil226}227228// checkPivot refuses a pivot reading its own sheet, or a pivot whose229// results it reads.230func (s *Sheet) checkPivot(p Pivot) error {231 name := p.Source232 for range len(s.wb.sheets) + 1 {233 src := s.wb.Lookup(name)234 switch {235 case src == nil || src.pivot.def == nil:236 return nil237 case src == s && name == p.Source:238 return errPivotSelf239 case src == s:240 return errPivotLoop241 }242 name = src.pivot.def.Source243 }244 return errPivotLoop245}246247// putPivot stores a pivot definition (nil for none), recording the old one248// for undo, and has it recomputed.249func (s *Sheet) putPivot(p *Pivot) {250 s.recordPivot()251 s.pivot.def = p252 s.pivot.stale = true253}254255// recordPivot saves the sheet's pivot before its first change in the256// open step.257func (s *Sheet) recordPivot() {258 if st := s.wb.hist.open; st != nil {259 if _, seen := st.pivots[s]; !seen {260 st.pivots[s] = s.pivot.def261 }262 }263}264265// pivotsOn returns the sheets whose pivot reads src.266func (w *Workbook) pivotsOn(src *Sheet) []*Sheet {267 var out []*Sheet268 for _, s := range w.sheets {269 if p := s.pivot.def; p != nil && w.Lookup(p.Source) == src {270 out = append(out, s)271 }272 }273 return out274}275276// renamePivots points pivots reading the sheet with key old at name.277func (w *Workbook) renamePivots(old, name string) {278 for _, s := range w.sheets {279 if p := s.pivot.def; p != nil && formula.SheetKey(p.Source) == old {280 np := p.clone()281 np.Source = name282 s.putPivot(np)283 }284 }285}286287// shiftPivots keeps the pivots reading s in step with rows or columns288// inserted or deleted on it: the range moves or resizes like a range289// reference, fields on deleted columns go, and a pivot whose whole range290// was deleted shows #REF!.291func (s *Sheet) shiftPivots(rows bool, sp formula.Span) {292 cell, rng := formula.AxisMaps(rows, sp)293 for _, t := range s.wb.pivotsOn(s) {294 p := t.pivot.def.clone()295 if r, ok := rng(p.Range); ok && !p.Lost {296 p.Range = r297 } else {298 p.Lost = true299 }300 if !rows {301 col := func(c int) (int, bool) {302 to, ok := cell(Addr{Col: c})303 return to.Col, ok304 }305 p.Rows, p.Columns = shiftGroups(p.Rows, col), shiftGroups(p.Columns, col)306 p.Values = shiftCols(p.Values, func(v *PivotValue) *int { return &v.Col }, col)307 p.Filters = shiftCols(p.Filters, func(f *PivotFilter) *int { return &f.Col }, col)308 }309 if !samePivot(p, t.pivot.def) {310 t.putPivot(p)311 }312 }313}314315func shiftGroups(gs []PivotGroup, col func(int) (int, bool)) []PivotGroup {316 return shiftCols(gs, func(g *PivotGroup) *int { return &g.Col }, col)317}318319// shiftCols moves the column of each item, dropping those on deleted320// columns.321func shiftCols[T any](items []T, at func(*T) *int, col func(int) (int, bool)) []T {322 var out []T323 for _, it := range items {324 c := at(&it)325 if to, ok := col(*c); ok {326 *c = to327 out = append(out, it)328 }329 }330 return out331}332333// FieldName is the header of column col in the pivot's source, or334// "Column B" when the header is blank.335func (w *Workbook) FieldName(p Pivot, col int) string {336 if src := w.Lookup(p.Source); src != nil && !p.Lost {337 if info, ok := src.Source(); ok && col < len(info.Shape.Cols) {338 return info.Shape.Cols[col]339 }340 if h := src.displayText(Addr{Col: col, Row: p.Range.From.Row}); h != "" {341 return h342 }343 }344 return "Column " + ColName(col)345}346347// ValueTitle is the header of a value: its name, or Sheets' "SUM of348// Sales".349func (w *Workbook) ValueTitle(p Pivot, v PivotValue) string {350 if v.Name != "" {351 return v.Name352 }353 return v.Summarize.Title() + " of " + w.FieldName(p, v.Col)354}355356// DefaultSummarize is how a new value summarizes column col, as in357// Sheets: SUM when the column holds a number, COUNTA otherwise.358func (w *Workbook) DefaultSummarize(p Pivot, col int) Summarize {359 if src := w.Lookup(p.Source); src != nil {360 if info, ok := src.Source(); ok {361 if col < len(info.Shape.Numeric) && info.Shape.Numeric[col] {362 return SumBy363 }364 return CountABy365 }366 last := src.filterData(p.Range).To.Row367 for row := p.Range.From.Row + 1; row <= last; row++ {368 if src.Value(Addr{Col: col, Row: row}).Kind == Number {369 return SumBy370 }371 }372 }373 return CountABy374}375376// PivotFilterValues lists the distinct values of column col of the377// pivot's source, among the rows its other filters let through, as a378// filter's values list does.379func (w *Workbook) PivotFilterValues(p Pivot, col int) []FilterValue {380 src := w.Lookup(p.Source)381 if src == nil || p.Lost || src.IsSource() {382 return nil // a source's values are too many to list383 }384 var others []PivotFilter385 var hidden []string386 for _, f := range p.Filters {387 if f.Col == col {388 hidden = f.Criteria.Hidden389 } else {390 others = append(others, f)391 }392 }393 return src.valuesList(p.Range, col, pivotTests(others), hidden, true)394}395396// NewPivot starts a pivot over r on src with nothing chosen yet and both397// grand totals on, as Sheets' new pivot tables.398func NewPivot(src *Sheet, r Rect) Pivot {399 return Pivot{Source: src.name, Range: r, RowTotals: true, ColumnTotals: true}400}401402// FrequencyPivot is a frequency table of column col of r: each distinct403// value with how many rows have it and their share, most frequent first,404// as VisiData's Shift+F. It is a pivot like any other; it counts rows405// rather than values, so blanks are counted too.406func FrequencyPivot(src *Sheet, r Rect, col int) Pivot {407 p := NewPivot(src, r)408 p.Rows = []PivotGroup{{Col: col, Desc: true, SortBy: 1}}409 p.Values = []PivotValue{410 {Col: col, Summarize: CountRowsBy, Name: "Count"},411 {Col: col, Summarize: CountRowsBy, ShowAs: ShowPctTotal, Name: "Percent"},412 }413 return p414}