1package sheet23import (4 "errors"5 "fmt"6 "strconv"7 "sync/atomic"89 "src.rest/cat/012/internal/formula"10 "src.rest/cat/012/internal/locale"11)1213// Errors returned by operations that would lose data or don't fit.14var (15 ErrPushedOff = errors.New("There's data at the edge of the sheet that would be pushed off")16 ErrPasteEdge = errors.New("The paste doesn't fit: it would go past the edge of the sheet")17 ErrFillTooBig = errors.New("That would write more cells than max-cells allows (see File > Settings)")18)1920// DefaultMaxCells is the max-cells setting's default: about 200 MB of21// numbers at 20 bytes each (store.go).22const DefaultMaxCells = 10_000_0002324// maxCells caps how many cells a paste or fill writes at once, and how25// many an import keeps, so an accidental whole-sheet selection or a huge26// file can't stall the program or exhaust memory. It is the max-cells27// setting of the config file, process-wide like the rest of it.28var maxCells atomic.Int642930func init() { maxCells.Store(DefaultMaxCells) }3132// SetMaxCells sets the cell budget; n < 1 restores the default.33func SetMaxCells(n int) {34 if n < 1 {35 n = DefaultMaxCells36 }37 maxCells.Store(int64(n))38}3940// MaxCells returns the cell budget.41func MaxCells() int { return int(maxCells.Load()) }4243// remap moves every stored cell with cell (dropping those it rejects) and44// rewrites every reference to this sheet's cells, here and on other45// sheets, with cell and rng, as one undo step. References to other sheets46// stay as they are.47func (s *Sheet) remap(label string, focus Rect, cell func(Addr) (Addr, bool), rng func(Rect) (Rect, bool)) {48 rw := formula.Relocate(s.onThis(s), cell, rng)49 next := make(map[Addr]*Cell, s.cells.len())50 for a, c := range s.cells.all() {51 if to, ok := cell(a); ok {52 next[to] = c.rewritten(rw)53 }54 }55 others := map[loc]*Cell{}56 for _, l := range s.wb.crossList() {57 if l.s == s {58 continue59 }60 c := l.s.cells.get(l.a)61 if nc := c.rewritten(formula.Relocate(s.onThis(l.s), cell, rng)); nc != c {62 others[l] = nc63 }64 }65 s.change(label, focus, func() {66 for l, c := range others {67 l.s.place(l.a, c)68 }69 for a := range s.cells.keys() {70 if c := next[a]; !s.cells.holds(a, c) {71 s.place(a, c)72 }73 }74 for a, c := range next {75 if !s.cells.holds(a, c) {76 s.place(a, c)77 }78 }79 })80}8182// InsertRows inserts n blank rows before row at, shifting the rows below83// down and adjusting every reference to them.84func (s *Sheet) InsertRows(at, n int) error {85 return s.insert(true, at, n)86}8788// InsertCols inserts n blank columns before column at.89func (s *Sheet) InsertCols(at, n int) error {90 return s.insert(false, at, n)91}9293// DeleteRows deletes n rows starting at row at. References to deleted94// cells become #REF!; ranges that lose some of their rows shrink.95func (s *Sheet) DeleteRows(at, n int) {96 s.restructure(true, formula.Span{At: at, N: -n, Size: MaxRows})97}9899// DeleteCols deletes n columns starting at column at.100func (s *Sheet) DeleteCols(at, n int) {101 s.restructure(false, formula.Span{At: at, N: -n, Size: MaxCols})102}103104func (s *Sheet) insert(rows bool, at, n int) error {105 size := MaxCols106 if rows {107 size = MaxRows108 }109 // The lines pushed past the edge must be empty.110 first := max(at, size-n)111 edge := colRect(first, size-1)112 if rows {113 edge = rowRect(first, size-1)114 }115 for range s.cells.keysIn(edge) {116 return ErrPushedOff117 }118 s.restructure(rows, formula.Span{At: at, N: n, Size: size})119 return nil120}121122func (s *Sheet) restructure(rows bool, sp formula.Span) {123 cell, rng := formula.AxisMaps(rows, sp)124 last := sp.At + abs(sp.N) - 1125 focus := colRect(sp.At, min(last, MaxCols-1))126 if rows {127 focus = rowRect(sp.At, min(last, MaxRows-1))128 }129 verb, noun := "insert", "column"130 if sp.N < 0 {131 verb = "delete"132 }133 if rows {134 noun = "row"135 }136 label := fmt.Sprintf("%s %d %s", verb, abs(sp.N), noun)137 if abs(sp.N) > 1 {138 label += "s"139 }140 s.change(label, focus, func() {141 s.recordShift()142 s.shiftRegions(rows, sp) // first, so remap doesn't take cells moving where regions were for theirs143 s.remap(label, focus, cell, rng)144 s.remapNames(rng)145 s.shiftCharts(cell, rng)146 if rows {147 s.shiftHeights(sp)148 } else {149 s.shiftWidths(sp)150 }151 s.shiftLines(rows, sp)152 s.shiftView(rows, sp)153 s.shiftPivots(rows, sp)154 s.shiftRules(rows, sp)155 })156}157158// shiftWidths moves column widths along with inserted or deleted columns.159func (s *Sheet) shiftWidths(sp formula.Span) {160 next := map[int]int{}161 for c, w := range s.widths {162 if to, ok := sp.Point(c); ok {163 next[to] = w164 }165 }166 for c := range s.widths {167 if _, ok := next[c]; !ok {168 s.setWidth(c, 0)169 }170 }171 for c, w := range next {172 if s.widths[c] != w {173 s.setWidth(c, w)174 }175 }176}177178// Clip is a copied range: snapshots of its cells, and of the formatting179// they show, taken at copy time.180type Clip struct {181 Range Rect // the range copied182 Src Rect // Range trimmed to its cells when whole lines183 cells map[Addr]*Cell // by offset from Src.From184 formats clipFormats185 merges []Rect // the merges wholly inside Range, by offset186}187188// Copy snapshots the cells in r, and what formatting they show, for189// pasting. Whole columns or rows are trimmed to the cells they hold,190// keeping their line formats.191func (s *Sheet) Copy(r Rect) *Clip {192 c := &Clip{Range: r, Src: s.trimLines(r), cells: map[Addr]*Cell{}, formats: s.copyFormats(r), merges: s.mergesWithin(r)}193 for _, a := range s.cellsIn(r) {194 c.cells[Addr{Col: a.Col - r.From.Col, Row: a.Row - r.From.Row}] = s.cells.get(a).plain()195 }196 return c197}198199// trimLines trims whole columns of r to the rows holding cells, and200// whole rows to the columns holding cells: no point copying a million201// blanks.202func (s *Sheet) trimLines(r Rect) Rect {203 if !r.AllRows() && !r.AllCols() {204 return r205 }206 held, ok := s.cells.bounds(r)207 if !ok {208 held.To = r.From209 }210 if r.AllRows() {211 r.To.Row = max(held.To.Row, r.From.Row)212 }213 if r.AllCols() {214 r.To.Col = max(held.To.Col, r.From.Col)215 }216 return r217}218219// Size returns the clip's width and height in cells.220func (c *Clip) Size() (cols, rows int) {221 return c.Src.To.Col - c.Src.From.Col + 1, c.Src.To.Row - c.Src.From.Row + 1222}223224// TextIn returns the clip's values as General shows them in loc, row by225// row, for the system clipboard. Blank rows and columns past the last226// value are left off, so copying whole columns gives their data; a block227// of more than MaxCells cells, blanks between values included, is too228// big for text, and TextIn returns nil.229func (c *Clip) TextIn(loc *locale.Locale) [][]string {230 cols, rows := 0, 0231 for off, cell := range c.cells {232 if !cell.Blank() {233 cols, rows = max(cols, off.Col+1), max(rows, off.Row+1)234 }235 }236 if cols*rows > MaxCells() {237 return nil238 }239 out := make([][]string, rows)240 for r := range out {241 out[r] = make([]string, cols)242 for col := range out[r] {243 if cell := c.cells[Addr{Col: col, Row: r}]; cell != nil {244 out[r][col] = textIn(cell.Value, loc)245 }246 }247 }248 return out249}250251// Paste writes the clip into dst and returns the range written. Formulas252// shift their relative references by the distance pasted, as in Sheets;253// with values set, only the computed values are pasted, keeping the254// destination's formatting. Otherwise each cell shows the formatting its255// source showed (see clipfmt.go). Like Sheets, a destination that is an256// exact multiple of the clip's size is tiled; otherwise the clip is257// pasted once at dst's top-left corner.258func (s *Sheet) Paste(c *Clip, dst Rect, values bool) (Rect, error) {259 p, err := c.layout(dst)260 if err != nil {261 return Rect{}, err262 }263 if len(c.cells)*p.across*p.down > MaxCells() {264 return Rect{}, ErrFillTooBig265 }266 dst = p.dst267 label := "paste into " + dst.String()268 if values {269 label = "paste values into " + dst.String()270 }271 // Blank parts of the clip clear what they land on, and each of its272 // cells is placed in every tile: the cost is the cells written and273 // cleared, not dst's area.274 s.change(label, dst, func() {275 for _, a := range s.cellsIn(dst) {276 if c.cells[p.off(a)] == nil {277 s.place(a, nil)278 }279 }280 for off, cell := range c.cells {281 from := Addr{Col: c.Src.From.Col + off.Col, Row: c.Src.From.Row + off.Row}282 for tr := range p.down {283 for tc := range p.across {284 to := Addr{Col: dst.From.Col + tc*p.tw + off.Col, Row: dst.From.Row + tr*p.th + off.Row}285 s.pasteCell(to, cell, to.Col-from.Col, to.Row-from.Row, values)286 }287 }288 }289 if !values {290 s.pasteFormats(&c.formats, p)291 s.pasteMerges(c.merges, p)292 }293 })294 return dst, nil295}296297func (s *Sheet) pasteCell(a Addr, c *Cell, dc, dr int, values bool) {298 switch {299 case c == nil:300 if s.cells.has(a) {301 s.place(a, nil)302 }303 case values:304 s.put(a, valueInput(c.Value))305 default:306 s.place(a, c.rewritten(formula.Shift(dc, dr)).clone())307 }308}309310// fillCell pastes c at a, dc and dr away from where it was typed, as311// Ctrl+Enter fills: each cell keeps its own note.312func (s *Sheet) fillCell(a Addr, c *Cell, dc, dr int) {313 if c == nil {314 s.pasteCell(a, nil, dc, dr, false)315 return316 }317 nc := c.rewritten(formula.Shift(dc, dr)).clone()318 nc.Note = ""319 if old := s.cells.get(a); old != nil {320 nc.Note = old.Note321 }322 s.place(a, nc)323}324325// valueInput is the entry that stores v as a constant. Text that would326// read as a number, boolean or formula gets a leading ' to stay text.327func valueInput(v Value) string {328 switch v.Kind {329 case Number:330 return strconv.FormatFloat(v.Num, 'f', -1, 64)331 case Text:332 if n, _, _ := classify(v.Str); n != nil || (len(v.Str) > 0 && v.Str[0] == '\'') {333 return "'" + v.Str334 }335 }336 return v.String()337}338339// FillDown copies the top row of r into the rest of it, adjusting340// references (Ctrl+D). A single-row range fills from the row above. When341// the top rows of r start a series and the rest is blank (1, 2 and then342// empty cells), it continues the series instead, as dragging the fill343// handle would.344func (s *Sheet) FillDown(r Rect) (Rect, error) {345 if src, ok := s.seriesStart(r, true); ok {346 return s.FillSeries(src, r)347 }348 src := Rect{From: r.From, To: Addr{Col: r.To.Col, Row: r.From.Row}}349 if r.From.Row == r.To.Row {350 if r.From.Row == 0 {351 return r, nil352 }353 src.From.Row, src.To.Row = r.From.Row-1, r.From.Row-1354 } else {355 r.From.Row++356 }357 var err error358 s.change("fill down "+r.String(), r, func() { r, err = s.Paste(s.Copy(src), r, false) })359 return r, err360}361362// FillRight copies the left column of r into the rest of it (Ctrl+R). A363// single-column range fills from the column to the left. Like FillDown,364// it continues a series started in the leftmost columns.365func (s *Sheet) FillRight(r Rect) (Rect, error) {366 if src, ok := s.seriesStart(r, false); ok {367 return s.FillSeries(src, r)368 }369 src := Rect{From: r.From, To: Addr{Col: r.From.Col, Row: r.To.Row}}370 if r.From.Col == r.To.Col {371 if r.From.Col == 0 {372 return r, nil373 }374 src.From.Col, src.To.Col = r.From.Col-1, r.From.Col-1375 } else {376 r.From.Col++377 }378 var err error379 s.change("fill right "+r.String(), r, func() { r, err = s.Paste(s.Copy(src), r, false) })380 return r, err381}382383// FillEntry stores input in every cell of r as if it had been typed at384// origin and copied to each cell, adjusting references (Ctrl+Enter).385func (s *Sheet) FillEntry(r Rect, origin Addr, input string) error {386 w, h := r.To.Col-r.From.Col+1, r.To.Row-r.From.Row+1387 if w*h > MaxCells() {388 return ErrFillTooBig389 }390 if _, _, err := classify(input); err != nil {391 return err392 }393 return s.Batch(Change{Label: "fill " + r.String(), Focus: r}, func() error {394 if err := s.put(origin, input); err != nil {395 return err396 }397 c := s.cells.get(origin).clone()398 for row := r.From.Row; row <= r.To.Row; row++ {399 for col := r.From.Col; col <= r.To.Col; col++ {400 if a := (Addr{Col: col, Row: row}); a != origin {401 s.fillCell(a, c, a.Col-origin.Col, a.Row-origin.Row)402 }403 }404 }405 return nil406 })407}408409// seriesStart finds the leading rows (down) or columns of r that hold410// something, when there are at least two of them and everything after411// them in r is blank: the start of a series to fill.412func (s *Sheet) seriesStart(r Rect, down bool) (Rect, bool) {413 line := func(a Addr) int {414 if down {415 return a.Row - r.From.Row416 }417 return a.Col - r.From.Col418 }419 used := map[int]bool{}420 for _, a := range s.cellsIn(r) {421 if s.cells.filledAt(a) {422 used[line(a)] = true423 }424 }425 n := 0426 for used[n] {427 n++428 }429 if n < 2 || len(used) != n {430 return Rect{}, false431 }432 src := r433 if down {434 src.To.Row = r.From.Row + n - 1435 } else {436 src.To.Col = r.From.Col + n - 1437 }438 return src, src != r439}440441func abs(n int) int { return max(n, -n) }