1package paged23import (4 "cmp"5 "fmt"67 "src.rest/cat/012/internal/fileio"8 "src.rest/cat/012/internal/functions"9 "src.rest/cat/012/internal/sheet"10)1112// rangeValues is a range of a source read whole, as an array: what an13// operator or a function reading it some other way is given, no bigger14// than budget cells.15func (b *book) rangeValues(name string, r sheet.Rect, budget int) sheet.SourceAnswer {16 s, ok := b.src(name)17 if !ok {18 return sheet.SourceAnswer{V: sheet.ErrRef}19 }20 part, ok := s.clip(r)21 if !ok {22 return sheet.SourceAnswer{A: &functions.Array{Rows: 1, Cols: 1}}23 }24 rows, cols := part.To.Row-part.From.Row+1, part.To.Col-part.From.Col+125 if rows > budget/cols {26 return sheet.SourceAnswer{V: sheet.ErrValue, Why: fmt.Sprintf(27 "Raise max-cells to %s to read %s whole. It is %s; SUM, COUNTIFS, XLOOKUP and the like read a source of any size.",28 grouped(rows*cols), s.name, grouped(budget))}29 }30 a := functions.NewArray(rows, cols)31 addrs, vals := make([]sheet.Addr, 1024), make([]sheet.Value, 1024)32 for from := part.From; ; {33 n := b.Scan(name, part, from, addrs, vals)34 for i := range n {35 a.Set(addrs[i].Row-part.From.Row, addrs[i].Col-part.From.Col, vals[i])36 }37 if n < len(addrs) && (n == 0 || vals[n-1].Kind != sheet.Error) {38 break39 }40 from = after(part, addrs[n-1])41 }42 return sheet.SourceAnswer{A: a}43}4445// SortedNums sorts the numbers of r on disk (functions.Sorter),46// streaming the source's rows: what isn't a number is skipped, as SUM47// skips it in a range, and the first error is the answer.48func (b *book) SortedNums(name string, r sheet.Rect) (functions.Sorted, *sheet.Value) {49 s, ok := b.src(name)50 if !ok {51 return nil, refErr()52 }53 ns := fileio.NewNumberSort(s.dir)54 part, ok := s.clip(r)55 if ok && part.From.Row == 0 { // the header: names, not numbers56 part.From.Row = 157 }58 if ok && part.From.Row <= part.To.Row {59 if e := b.sortRows(s, part, ns); e != nil {60 ns.Close()61 return nil, e62 }63 }64 sorted, err := ns.Sorted()65 if err != nil {66 b.fail(err)67 return nil, refErr()68 }69 return sorted, nil70}7172// sortRows adds the numbers of the rows of part to ns, numbered in the73// order they're read, returning the first error among them, or #REF!74// when reading or spilling failed.75func (b *book) sortRows(s snapshot, part sheet.Rect, ns *fileio.NumberSort) *sheet.Value {76 cols := make([]int, 0, part.To.Col-part.From.Col+1)77 for c := part.From.Col; c <= part.To.Col; c++ {78 cols = append(cols, c)79 }80 last := int64(part.To.Row - 1)81 var e *sheet.Value82 var pos int6483 var failed error84 err := s.h.Scan(b.ctx, int64(part.From.Row-1), cols, func(row int64, vals []sheet.LiveCell) bool {85 for _, v := range vals {86 switch v.V.Kind {87 case sheet.Error:88 e = &v.V89 return false90 case sheet.Number:91 if failed = ns.Add(v.V.Num, pos); failed != nil {92 return false93 }94 }95 pos++96 }97 return row < last98 })99 if err = cmp.Or(err, failed); err != nil {100 b.fail(err)101 return refErr()102 }103 return e104}105106// refErr is a #REF! of its own, for an answer to point at.107func refErr() *sheet.Value {108 v := sheet.ErrRef109 return &v110}111112// after is the cell after a in r, row by row.113func after(r sheet.Rect, a sheet.Addr) sheet.Addr {114 if a.Col < r.To.Col {115 return sheet.Addr{Col: a.Col + 1, Row: a.Row}116 }117 return sheet.Addr{Col: r.From.Col, Row: a.Row + 1}118}119120// pivot gathers a pivot table's groups over the source, streaming the121// columns it reads.122func (b *book) pivot(name string, p sheet.Pivot) sheet.SourceAnswer {123 s, ok := b.src(name)124 if !ok {125 return sheet.SourceAnswer{V: sheet.ErrRef}126 }127 cols := sheet.PivotCols(p)128 width := len(s.shape.Cols)129 for _, c := range cols {130 if c >= width {131 return sheet.SourceAnswer{V: sheet.ErrRef, Why: fmt.Sprintf("%s has no column %s", s.name, sheet.ColName(c))}132 }133 }134 row := make([]sheet.LiveCell, width)135 g, err := sheet.GatherPivot(p, s.shape, func(each func(int, []sheet.LiveCell) bool) error {136 return s.h.Scan(b.ctx, 0, cols, func(n int64, vals []sheet.LiveCell) bool {137 for i, c := range cols {138 row[c] = vals[i]139 }140 return each(int(n), row)141 })142 })143 if err != nil {144 b.fail(err)145 return sheet.SourceAnswer{V: sheet.ErrRef}146 }147 return sheet.SourceAnswer{Pivot: g}148}149150// grouped is n with thousands separators: 12,000,000.151func grouped(n int) string {152 s := fmt.Sprint(n)153 for i := len(s) - 3; i > 0; i -= 3 {154 s = s[:i] + "," + s[i:]155 }156 return s157}