1package sheet23import (4 "cmp"5 "slices"6 "strings"7)89// Summarize is how a pivot value summarizes a group's cells, as Sheets'10// "Summarize by". Each is one entry of summaries.11type Summarize uint81213const (14 SumBy Summarize = iota15 CountABy16 CountBy17 CountUniqueBy18 AverageBy19 MaxBy20 MinBy21 // CountRowsBy counts rows, blank or not. Sheets has no such choice;22 // frequency tables use it so blank values are counted.23 CountRowsBy24 numSummarize25)2627// summary is one way of summarizing: its name in files, its title (the28// function Sheets names it after), what it does, whether the result29// keeps the column's number format, and how it's computed.30type summary struct {31 name, title, desc string32 keepsFormat bool33 result func(a *accum) Value34}3536var summaries = [numSummarize]summary{37 SumBy: {"sum", "SUM", "Add the numbers", true, func(a *accum) Value {38 return a.or(num(a.sum))39 }},40 CountABy: {"counta", "COUNTA", "Count the cells that aren't blank", false, func(a *accum) Value {41 return num(float64(a.counta))42 }},43 CountBy: {"count", "COUNT", "Count the numbers", false, func(a *accum) Value {44 return num(float64(a.n))45 }},46 CountUniqueBy: {"countunique", "COUNTUNIQUE", "Count the distinct values", false, func(a *accum) Value {47 return num(float64(len(a.uniq)))48 }},49 AverageBy: {"average", "AVERAGE", "Average the numbers", true, func(a *accum) Value {50 if a.n == 0 {51 return a.or(ErrDiv0)52 }53 return a.or(num(a.sum / float64(a.n)))54 }},55 MaxBy: {"max", "MAX", "The largest number", true, func(a *accum) Value {56 return a.or(num(a.hi))57 }},58 MinBy: {"min", "MIN", "The smallest number", true, func(a *accum) Value {59 return a.or(num(a.lo))60 }},61 CountRowsBy: {"rows", "ROWS", "Count the rows, blank or not", false, func(a *accum) Value {62 return num(float64(a.rows))63 }},64}6566// Summaries lists the choices of "Summarize by", in Sheets' order.67func Summaries() []Summarize {68 return []Summarize{SumBy, CountABy, CountBy, CountUniqueBy, AverageBy, MaxBy, MinBy}69}7071func (f Summarize) String() string { return summaries[f].name }72func (f Summarize) Title() string { return summaries[f].title }73func (f Summarize) Desc() string { return summaries[f].desc }7475// ParseSummarize is the inverse of Summarize.String.76func ParseSummarize(name string) (Summarize, bool) {77 for i, s := range summaries {78 if s.name == name {79 return Summarize(i), true80 }81 }82 return SumBy, false83}8485// accum gathers one value of one group: what every summary needs, so a86// value can be summarized any way without reading the source again.87type accum struct {88 rows, counta, n int89 sum, lo, hi float6490 err Value // the first error, which SUM and the rest show91 uniq map[groupKey]struct{}92}9394func (a *accum) add(v Value, unique bool) {95 a.rows++96 switch v.Kind {97 case Empty:98 return99 case Number:100 if a.n == 0 {101 a.lo, a.hi = v.Num, v.Num102 }103 a.lo, a.hi = min(a.lo, v.Num), max(a.hi, v.Num)104 a.n++105 a.sum += v.Num106 case Error:107 if a.err.Kind == Empty {108 a.err = v109 }110 }111 a.counta++112 if unique {113 if a.uniq == nil {114 a.uniq = map[groupKey]struct{}{}115 }116 a.uniq[keyOf(v, false)] = struct{}{}117 }118}119120// or returns the first error gathered, if any, else v.121func (a *accum) or(v Value) Value {122 if a.err.Kind == Error {123 return a.err124 }125 return v126}127128// groupKey identifies a value for grouping: numbers (dates too) by value,129// text ignoring case when folded, as Sheets groups "east" with "East".130// Numbers and text never share a key, so 1 and "1" are two groups. It is131// a comparable struct, so looking a group up allocates nothing.132type groupKey struct {133 kind Kind134 num float64135 str string136}137138func keyOf(v Value, fold bool) groupKey {139 k := groupKey{kind: v.Kind, num: v.Num, str: v.Str}140 if fold && v.Kind == Text {141 k.str = strings.ToLower(v.Str)142 }143 return k144}145146// pivotNode is a group of source rows: the root holds them all, its147// children the groups of the first row field, and so on. Each keeps its148// accumulators by column group (-1 for all of them), then by value.149type pivotNode struct {150 label Value151 format Format152 kids []*pivotNode153 byKey map[groupKey]*pivotNode154 acc map[int][]accum155}156157// pivotCalc is a pivot being computed.158type pivotCalc struct {159 w *Workbook160 p *Pivot161 d pivotData // where the source's rows are read: pivotdata.go162 root *pivotNode163 cols []*colGroup // the column groups, sorted once gathered164 colRoot colNode165 nextCol int // the next column group's id166 colIDs []int // colGroupsOf's answer, reused167 unique []bool // by value: whether to gather distinct values168 formats []Format // by value: the format results show in169 records int // source rows summarized170}171172func newPivotCalc(w *Workbook, p *Pivot, d pivotData) *pivotCalc {173 c := &pivotCalc{w: w, p: p, d: d, root: &pivotNode{}}174 for _, v := range p.Values {175 c.unique = append(c.unique, v.Summarize == CountUniqueBy)176 c.formats = append(c.formats, c.valueFormat(v))177 }178 return c179}180181// valueFormat is the format a value's results show in: percent for182// shares, the column's own for sums, averages, minimums and maximums.183func (c *pivotCalc) valueFormat(v PivotValue) Format {184 if v.ShowAs != ShowValue {185 return Preset(FmtPercent)186 }187 if !summaries[v.Summarize].keepsFormat {188 return Format{}189 }190 return c.d.colFormat(v.Col, c.p.Range)191}192193// gather reads the source rows the filters let through, skipping rows194// blank across the range, into groups.195func (c *pivotCalc) gather() {196 tests := pivotTests(c.p.Filters)197 r := c.p.Range198 last := c.d.lastRow(r)199 for row := r.From.Row + 1; row <= last; row++ {200 c.gatherRow(row, tests)201 }202 c.sortRows(c.root, 0)203 c.sortCols()204}205206// gatherRow reads row into its groups, unless it is blank across the207// range or the filters leave it out.208func (c *pivotCalc) gatherRow(row int, tests []colTest) {209 if c.d.blankRow(row, c.p.Range) || !c.d.passes(row, tests) {210 return211 }212 c.records++213 ids := c.colGroupsOf(row)214 n := c.root215 c.add(n, ids, row)216 for _, g := range c.p.Rows {217 n = n.child(c.d, Addr{Col: g.Col, Row: row})218 c.add(n, ids, row)219 }220}221222// rowBlank reports whether row is blank across r's columns, looking at223// the columns that hold anything.224func (s *Sheet) rowBlank(row int, r Rect) bool {225 for _, c := range s.cells.filled.colsIn(r.From.Col, r.To.Col) {226 if s.cells.filled.has(Addr{Col: c, Row: row}) {227 return false228 }229 }230 return true231}232233// pivotTests prepares a pivot's filters as a filter's column tests.234func pivotTests(fs []PivotFilter) []colTest {235 var tests []colTest236 for _, f := range fs {237 if f.Criteria.IsZero() {238 continue239 }240 t := colTest{col: f.Col, cond: f.Criteria.Cond.test()}241 if len(f.Criteria.Hidden) > 0 {242 t.hidden = make(map[string]bool, len(f.Criteria.Hidden))243 for _, h := range f.Criteria.Hidden {244 t.hidden[h] = true245 }246 }247 tests = append(tests, t)248 }249 return tests250}251252// child returns the group of n for the value at a, adding it if new.253func (n *pivotNode) child(d pivotData, a Addr) *pivotNode {254 v := d.value(a)255 k := keyOf(v, true)256 if kid, ok := n.byKey[k]; ok {257 return kid258 }259 if n.byKey == nil {260 n.byKey = map[groupKey]*pivotNode{}261 }262 kid := &pivotNode{label: v, format: d.format(a)}263 n.byKey[k] = kid264 n.kids = append(n.kids, kid)265 return kid266}267268// add gathers row's values into n: for all column groups, and for each269// of ids, the column groups the row counts in.270func (c *pivotCalc) add(n *pivotNode, ids []int, row int) {271 if len(c.p.Values) == 0 {272 return273 }274 if n.acc == nil {275 n.acc = map[int][]accum{}276 }277 c.addTo(n, -1, row)278 for _, id := range ids {279 c.addTo(n, id, row)280 }281}282283// addTo gathers row's values into n's accumulators for column group id.284func (c *pivotCalc) addTo(n *pivotNode, id, row int) {285 accs := n.acc[id]286 if accs == nil {287 accs = make([]accum, len(c.p.Values))288 n.acc[id] = accs289 }290 for i, v := range c.p.Values {291 accs[i].add(c.d.value(Addr{Col: v.Col, Row: row}), c.unique[i])292 }293}294295// result is value i of n in column group col, as gathered (not yet shown296// as a share), and false when no row of n is in col.297func (c *pivotCalc) result(n *pivotNode, col, i int) (Value, bool) {298 accs, ok := n.acc[col]299 if !ok {300 return Value{}, false301 }302 return summaries[c.p.Values[i].Summarize].result(&accs[i]), true303}304305// sortRows orders n's children by the row field at depth, then theirs.306func (c *pivotCalc) sortRows(n *pivotNode, depth int) {307 if depth >= len(c.p.Rows) {308 return309 }310 g := c.p.Rows[depth]311 slices.SortStableFunc(n.kids, func(a, b *pivotNode) int {312 return c.order(g, []Value{a.label}, []Value{b.label}, func(i int) (Value, Value) {313 va, _ := c.result(a, -1, i)314 vb, _ := c.result(b, -1, i)315 return va, vb316 })317 })318 for _, kid := range n.kids {319 c.sortRows(kid, depth+1)320 }321}322323// order compares two groups by a field's order: by label, as Sheets sorts324// (blanks last either way), or by a value's total, labels breaking ties.325func (c *pivotCalc) order(g PivotGroup, a, b []Value, totals func(i int) (Value, Value)) int {326 sign := 1327 if g.Desc {328 sign = -1329 }330 if i := g.SortBy - 1; i >= 0 && i < len(c.p.Values) {331 va, vb := totals(i)332 if d := compareTotals(va, vb); d != 0 {333 return sign * d334 }335 sign = 1336 }337 return labelOrder(a[0], b[0], sign)338}339340// labelOrder compares group labels A to Z (sign 1) or Z to A (-1), blanks341// last either way.342func labelOrder(a, b Value, sign int) int {343 if (a.Kind == Empty) != (b.Kind == Empty) {344 if a.Kind == Empty {345 return 1346 }347 return -1348 }349 return sign * sortCompare(a, b)350}351352// compareTotals orders totals by number, anything else (an error, a353// group with no rows) below every number.354func compareTotals(a, b Value) int {355 an, bn := a.Kind == Number, b.Kind == Number356 switch {357 case an && bn:358 return cmp.Compare(a.Num, b.Num)359 case an:360 return 1361 case bn:362 return -1363 }364 return 0365}366367// shown is value i of n in column group col as the pivot shows it: as368// gathered, or as a share of its row's, column's or the grand total.369func (c *pivotCalc) shown(n *pivotNode, col, i int) (Value, bool) {370 v, ok := c.result(n, col, i)371 show := c.p.Values[i].ShowAs372 if !ok || show == ShowValue || v.Kind != Number {373 return v, ok374 }375 var total Value376 switch show {377 case ShowPctRow:378 total, _ = c.result(n, -1, i)379 case ShowPctColumn:380 total, _ = c.result(c.root, col, i)381 default:382 total, _ = c.result(c.root, -1, i)383 }384 switch {385 case total.Kind != Number:386 return total, true387 case total.Num == 0:388 return ErrDiv0, true389 }390 return num(v.Num / total.Num), true391}