1package sheet23import (4 "math"5 "regexp"6 "strconv"7 "strings"89 "src.rest/cat/012/internal/formula"10 "src.rest/cat/012/internal/numfmt"11)1213// Fill series: dragging the fill handle in Sheets continues what the14// selected cells start. 1, 2 goes on 3, 4; 2, 4 goes on 6, 8 (with more15// cells, along their linear trend); dates go on by day, or by month when16// they fall on the same day of the month; Jan, Feb goes on Mar and Mon,17// Tue goes on Wed; "Item 1" goes on "Item 2". A single number, and18// anything else (text, formulas), is copied, formulas adjusting their19// references as when pasted.2021// FillSeries extends src over dst, which contains it and reaches past it22// along one axis: down or up, right or left. Each column (or row) of src23// is continued on its own. It returns the range filled, as one undo step.24func (s *Sheet) FillSeries(src, dst Rect) (Rect, error) {25 w, h := dst.To.Col-dst.From.Col+1, dst.To.Row-dst.From.Row+126 if w*h > MaxCells() {27 return Rect{}, ErrFillTooBig28 }29 vertical := dst.From.Col == src.From.Col && dst.To.Col == src.To.Col30 if !vertical && (dst.From.Row != src.From.Row || dst.To.Row != src.To.Row) || dst == src {31 return src, nil32 }33 var lanes []fillLane34 lane := func(from, step Addr, n int, to Addr, m int) {35 var seq []Addr36 for i := range n {37 seq = append(seq, Addr{Col: from.Col + step.Col*i, Row: from.Row + step.Row*i})38 }39 lanes = append(lanes, fillLane{seq, to, step, m})40 }41 n := src.To.Row - src.From.Row + 142 if !vertical {43 n = src.To.Col - src.From.Col + 144 }45 switch {46 case vertical && dst.To.Row > src.To.Row:47 for c := src.From.Col; c <= src.To.Col; c++ {48 lane(Addr{Col: c, Row: src.From.Row}, Addr{Row: 1}, n, Addr{Col: c, Row: src.To.Row + 1}, dst.To.Row-src.To.Row)49 }50 case vertical:51 for c := src.From.Col; c <= src.To.Col; c++ {52 lane(Addr{Col: c, Row: src.To.Row}, Addr{Row: -1}, n, Addr{Col: c, Row: src.From.Row - 1}, src.From.Row-dst.From.Row)53 }54 case dst.To.Col > src.To.Col:55 for r := src.From.Row; r <= src.To.Row; r++ {56 lane(Addr{Col: src.From.Col, Row: r}, Addr{Col: 1}, n, Addr{Col: src.To.Col + 1, Row: r}, dst.To.Col-src.To.Col)57 }58 default:59 for r := src.From.Row; r <= src.To.Row; r++ {60 lane(Addr{Col: src.To.Col, Row: r}, Addr{Col: -1}, n, Addr{Col: src.From.Col - 1, Row: r}, src.From.Col-dst.From.Col)61 }62 }63 s.change("fill "+dst.String(), dst, func() {64 for _, l := range lanes {65 s.fill(l)66 }67 })68 return dst, nil69}7071// fillLane is one column (or row) of a fill: the source cells in fill72// order, and n targets from to on by step.73type fillLane struct {74 seq []Addr75 to, step Addr76 n int77}7879// fill continues the cells of a lane into its targets. A lane of blank80// cells only clears what its targets hold.81func (s *Sheet) fill(l fillLane) {82 seq := l.seq83 cells := make([]*Cell, len(seq))84 blank := true85 for i, a := range seq {86 cells[i] = s.cells.get(a)87 blank = blank && cells[i] == nil88 }89 if blank {90 last := Addr{Col: l.to.Col + l.step.Col*(l.n-1), Row: l.to.Row + l.step.Row*(l.n-1)}91 for _, a := range s.cellsIn(NewRect(l.to, last)) {92 s.place(a, nil)93 }94 return95 }96 next := detectSeries(cells)97 for i := range l.n {98 to := Addr{Col: l.to.Col + l.step.Col*i, Row: l.to.Row + l.step.Row*i}99 k := i % len(seq)100 c := cells[k]101 switch {102 case next != nil:103 nc, err := newCell(next(len(seq)+i), c.Format, c.Style, false)104 if err == nil {105 s.place(to, nc)106 continue107 }108 fallthrough109 case c != nil:110 s.pasteCell(to, c, to.Col-seq[k].Col, to.Row-seq[k].Row, false)111 case s.cells.has(to):112 s.place(to, nil)113 }114 }115}116117// detectSeries returns the entry for position i of the series cells118// start, or nil if they should be copied instead.119func detectSeries(cells []*Cell) func(i int) string {120 for _, c := range cells {121 if c.Blank() || c.IsFormula() {122 return nil123 }124 }125 if f := numberSeries(cells); f != nil {126 return f127 }128 if f := nameSeries(cells); f != nil {129 return f130 }131 return textNumberSeries(cells)132}133134// numberSeries continues numbers along their linear trend (a least135// squares fit, which for evenly spaced numbers is exact), and dates by136// day or by month. A single number is copied; a single date goes on by137// a day.138func numberSeries(cells []*Cell) func(int) string {139 vals := make([]float64, len(cells))140 for i, c := range cells {141 if _, ok := c.expr.(formula.Num); !ok || c.Value.Kind != Number {142 return nil143 }144 vals[i] = c.Value.Num145 }146 f := cells[0].Format147 date := f.Kind == FmtDate || f.Kind == FmtDateTime148 k := len(vals)149 input := func(i int, v float64) string { return numberInput(v, cells[i%k].Format) }150 if k == 1 {151 if !date {152 return nil153 }154 return func(i int) string { return input(i, vals[0]+float64(i)) }155 }156 if date {157 if step, ok := monthStep(vals); ok {158 y, m, d := numfmt.Civil(int64(vals[0]))159 return func(i int) string {160 y2, m2 := numfmt.AddMonths(y, m, step*i)161 day := min(d, numfmt.DaysIn(y2, m2))162 return input(i, numfmt.DateSerial(y2, m2, day)+vals[0]-math.Floor(vals[0]))163 }164 }165 }166 var mi, mv float64167 for i, v := range vals {168 mi += float64(i)169 mv += v170 }171 mi, mv = mi/float64(k), mv/float64(k)172 var num, den float64173 for i, v := range vals {174 num += (float64(i) - mi) * (v - mv)175 den += (float64(i) - mi) * (float64(i) - mi)176 }177 b := num / den178 a := mv - b*mi179 return func(i int) string { return input(i, a+b*float64(i)) }180}181182// monthStep reports whether dates fall on the same day of evenly spaced183// months, and the spacing.184func monthStep(vals []float64) (int, bool) {185 months := func(v float64) (int, int) {186 y, m, d := numfmt.Civil(int64(math.Floor(v)))187 return y*12 + m - 1, d188 }189 m0, d0 := months(vals[0])190 m1, _ := months(vals[1])191 step := m1 - m0192 if step == 0 {193 return 0, false194 }195 for i, v := range vals {196 m, d := months(v)197 if d != d0 || m != m0+step*i {198 return 0, false199 }200 }201 return step, true202}203204// numberInput is the entry for v in format f: as the format shows it205// when that reads back exactly ($1,300.00, 10/2/2026), else plain.206func numberInput(v float64, f Format) string {207 // Fifteen significant digits drop float noise (0.1+0.2).208 v, _ = strconv.ParseFloat(strconv.FormatFloat(v, 'g', 15, 64), 64)209 if f.Kind != FmtAuto && f.Kind != FmtText {210 t := FormatText(Value{Kind: Number, Num: v}, f)211 if back, _, ok := ParseValue(t); ok && back == v {212 return t213 }214 }215 return strconv.FormatFloat(v, 'f', -1, 64)216}217218// A list of names that fills in a cycle: month and day names, long or219// short.220type nameList struct {221 long []string222 short int // letters in the short form223}224225var nameLists = []nameList{{numfmt.MonthNames[:], 3}, {numfmt.DayNames[:], 3}}226227// nameAt finds a month or day name: which list, the index, whether it is228// long, and whether it could be either (May).229func nameAt(w string) (list, idx int, long, either, ok bool) {230 lw := strings.ToLower(w)231 for li, l := range nameLists {232 for i, n := range l.long {233 n = strings.ToLower(n)234 full, short := lw == n, lw == n[:l.short]235 if full || short {236 return li, i, full, full && short, true237 }238 }239 }240 return 0, 0, false, false, false241}242243// nameSeries continues month or day names in steps, keeping the case and244// length of the first: Jan, Feb goes on Mar; MONDAY goes on TUESDAY.245func nameSeries(cells []*Cell) func(int) string {246 var list, step, first int247 long, known := true, false248 idx := make([]int, len(cells))249 for i, c := range cells {250 if c.Value.Kind != Text {251 return nil252 }253 l, n, lg, either, ok := nameAt(c.Value.Str)254 if !ok || i > 0 && l != list {255 return nil256 }257 if !either {258 if known && lg != long {259 return nil260 }261 long, known = lg, true262 }263 list, idx[i] = l, n264 }265 size := len(nameLists[list].long)266 first = idx[0]267 step = 1268 if len(idx) > 1 {269 step = ((idx[1]-idx[0])%size + size) % size270 for i := 1; i < len(idx); i++ {271 if ((idx[i]-idx[i-1])%size+size)%size != step {272 return nil273 }274 }275 }276 sample := cells[0].Value.Str277 return func(i int) string {278 name := nameLists[list].long[((first+step*i)%size+size)%size]279 if !long {280 name = name[:nameLists[list].short]281 }282 switch {283 case sample == strings.ToUpper(sample):284 name = strings.ToUpper(name)285 case sample == strings.ToLower(sample):286 name = strings.ToLower(name)287 }288 return name289 }290}291292var trailingNumber = regexp.MustCompile(`^(.*?)(\d+)$`)293294// textNumberSeries continues text ending in a number: Item 1, Item 2 goes295// on Item 3, keeping zero padding (Q01, Q02, Q03). A single one counts up296// by one.297func textNumberSeries(cells []*Cell) func(int) string {298 var prefix, quote string299 width := 0300 nums := make([]int, len(cells))301 for i, c := range cells {302 if c.Value.Kind != Text || c.expr != nil {303 return nil304 }305 m := trailingNumber.FindStringSubmatch(c.Value.Str)306 if m == nil || i > 0 && m[1] != prefix {307 return nil308 }309 n, err := strconv.Atoi(m[2])310 if err != nil {311 return nil312 }313 prefix, nums[i] = m[1], n314 if strings.HasPrefix(m[2], "0") {315 width = len(m[2])316 }317 if strings.HasPrefix(c.Input, "'") {318 quote = "'"319 }320 }321 step := 1322 if len(nums) > 1 {323 step = nums[1] - nums[0]324 for i := 2; i < len(nums); i++ {325 if nums[i]-nums[i-1] != step {326 return nil327 }328 }329 }330 return func(i int) string {331 n := nums[0] + step*i332 digits := strconv.Itoa(max(n, -n))333 if len(digits) < width {334 digits = strings.Repeat("0", width-len(digits)) + digits335 }336 return quote + prefix + digits337 }338}