1package sheet23import (4 "math"5 "slices"6 "strconv"7 "strings"8)910// Charts float over the grid like Sheets' charts: each draws a range of11// cells, sits with its top-left corner on a cell, and has a size in12// terminal cells. They belong to the sheet, so they're saved with it,13// follow inserted and deleted rows and columns, and every change to them14// is an undo step.1516// ChartType is how a chart draws its series. Adding one takes a constant17// here and its name in chartTypeNames; the chart editor, the file format18// and ChartTypes follow from the table, and internal/chart draws it.19type ChartType int2021const (22 ChartColumn ChartType = iota // vertical bars, one group per category23 ChartBar // horizontal bars24 ChartLine // one line per series25 ChartPie // the first series as slices of a whole26 ChartArea // lines filled down to the axis27 ChartScatter // points at X, Y: the first series is X28)2930// chartTypeNames names every type, as files store it, in the order the31// chart editor offers them.32var chartTypeNames = [...]string{ChartColumn: "column", ChartBar: "bar", ChartLine: "line", ChartPie: "pie",33 ChartArea: "area", ChartScatter: "scatter"}3435// ChartTypes lists the types in the order the chart editor offers them.36var ChartTypes = func() []ChartType {37 out := make([]ChartType, len(chartTypeNames))38 for i := range out {39 out[i] = ChartType(i)40 }41 return out42}()4344func (t ChartType) String() string {45 if t < 0 || int(t) >= len(chartTypeNames) {46 return chartTypeNames[0]47 }48 return chartTypeNames[t]49}5051// Title is the type's name as the chart editor shows it, e.g. "Column".52func (t ChartType) Title() string {53 s := t.String()54 return strings.ToUpper(s[:1]) + s[1:]55}5657// ParseChartType parses a type name as String writes it.58func ParseChartType(s string) (ChartType, bool) {59 i := slices.Index(chartTypeNames[:], strings.ToLower(s))60 return ChartType(max(i, 0)), i >= 061}6263// Chart is a chart floating over the grid.64type Chart struct {65 Type ChartType66 Data Rect67 // ByRow puts each series in a row instead of a column, like Sheets'68 // "Switch rows / columns".69 ByRow bool70 // Header takes series names from the first row of the data (the first71 // column when ByRow).72 Header bool73 // Labels takes category labels from the first column of the data (the74 // first row when ByRow).75 Labels bool76 Title string77 At Addr // the cell under the top-left corner78 W, H int // size in terminal cells79 // ChartOptions are the stacking, axis and legend settings of Sheets'80 // Customize tab; see chartopts.go.81 ChartOptions82}8384// Chart size limits, in terminal cells.85const (86 MinChartW, MinChartH = 20, 887 MaxChartW, MaxChartH = 240, 12088)8990// ChartSeries is one named run of values. Blank and non-numeric cells are91// NaN, drawn as gaps.92type ChartSeries struct {93 Name string94 Values []float6495}9697// ChartData is what a chart draws: category labels and series of the same98// length, and the number format of the values, for axis labels.99type ChartData struct {100 Categories []string101 Series []ChartSeries102 Format Format103}104105// maxChartPoints caps the categories a chart reads, so a chart over whole106// columns stays quick to draw, and maxChartSeries its series (the columns107// 1-2-3's grid had), for one over whole rows.108const (109 maxChartPoints = 500110 maxChartSeries = 256111)112113// ChartData reads a chart's cells. Series names and labels are the cells'114// text as displayed.115func (s *Sheet) ChartData(c Chart) ChartData {116 r := s.clipToUsed(c.Data)117 // Lay the range out as rows of cells with series going down columns,118 // transposing when series run along rows.119 at := func(i, j int) Addr { return Addr{Col: r.From.Col + j, Row: r.From.Row + i} }120 n, m := r.To.Row-r.From.Row+1, r.To.Col-r.From.Col+1121 if c.ByRow {122 at = func(i, j int) Addr { return Addr{Col: r.From.Col + i, Row: r.From.Row + j} }123 n, m = m, n124 }125 first, col0 := 0, 0126 if c.Header && n > 1 {127 first = 1128 }129 if c.Labels && m > 1 {130 col0 = 1131 }132 n, m = min(n, first+maxChartPoints), min(m, col0+maxChartSeries)133 var d ChartData134 for i := first; i < n; i++ {135 label := strconv.Itoa(i - first + 1)136 if col0 == 1 {137 label = s.displayText(at(i, 0))138 }139 d.Categories = append(d.Categories, label)140 }141 for j := col0; j < m; j++ {142 sr := ChartSeries{Name: "Series " + strconv.Itoa(j-col0+1)}143 if first == 1 {144 if name := s.displayText(at(0, j)); name != "" {145 sr.Name = name146 }147 }148 for i := first; i < n; i++ {149 a := at(i, j)150 v := s.Value(a)151 switch v.Kind {152 case Number:153 sr.Values = append(sr.Values, v.Num)154 if d.Format.IsZero() {155 d.Format = s.DisplayFormat(a)156 }157 default:158 sr.Values = append(sr.Values, math.NaN())159 }160 }161 d.Series = append(d.Series, sr)162 }163 return d164}165166// displayText is a cell's value as its format shows it, without padding.167func (s *Sheet) displayText(a Addr) string {168 v := s.Value(a)169 if v.Kind == Text || v.Kind == Empty {170 return v.Str171 }172 text, _ := Display(v, s.DisplayFormat(a), 64)173 return strings.TrimSpace(text)174}175176// clipToUsed trims a range to the used part of the sheet, so a chart over177// whole columns reads only the filled rows.178func (s *Sheet) clipToUsed(r Rect) Rect {179 used, ok := s.UsedRange()180 if !ok {181 return Rect{From: r.From, To: r.From}182 }183 r.To.Col = max(min(r.To.Col, used.To.Col), r.From.Col)184 r.To.Row = max(min(r.To.Row, used.To.Row), r.From.Row)185 return r186}187188// Region returns the block of data around a, as Sheets picks the range to189// sort or filter when a single cell is selected: filled cells connected190// to a (diagonals count), and anything touching their bounding box, until191// nothing more touches it. A blank cell with no filled neighbors is a192// region of its own.193func (s *Sheet) Region(a Addr) Rect {194 filled := func(a Addr) bool { return a.Valid() && s.cells.filledAt(a) }195 seen := map[Addr]bool{}196 var queue []Addr197 visit := func(p Addr) {198 if !seen[p] && filled(p) {199 seen[p] = true200 queue = append(queue, p)201 }202 }203 around := func(p Addr) {204 for dr := -1; dr <= 1; dr++ {205 for dc := -1; dc <= 1; dc++ {206 visit(Addr{Col: p.Col + dc, Row: p.Row + dr})207 }208 }209 }210 around(a)211 if len(queue) == 0 {212 return Rect{From: a, To: a}213 }214 r := Rect{From: queue[0], To: queue[0]}215 for len(queue) > 0 {216 for len(queue) > 0 {217 p := queue[0]218 queue = queue[1:]219 r = union(r, Rect{From: p, To: p})220 around(p)221 }222 // Cells touching the bounding box join too.223 for row := r.From.Row - 1; row <= r.To.Row+1; row++ {224 visit(Addr{Col: r.From.Col - 1, Row: row})225 visit(Addr{Col: r.To.Col + 1, Row: row})226 }227 for col := r.From.Col; col <= r.To.Col; col++ {228 visit(Addr{Col: col, Row: r.From.Row - 1})229 visit(Addr{Col: col, Row: r.To.Row + 1})230 }231 }232 return r233}234235// GuessChart sets up a chart over r the way Sheets does: series down236// columns, a header row when the first row is text over numbers, and237// category labels when the first column is text.238func (s *Sheet) GuessChart(r Rect) Chart {239 r = s.clipToUsed(r)240 c := Chart{Type: ChartColumn, Data: r}241 isText := func(a Addr) bool { return s.Value(a).Kind == Text }242 if r.To.Col > r.From.Col {243 labels := false244 s.cells.filled.colScan(r.From.Col, r.From.Row+1, r.To.Row, func(row int) bool {245 labels = isText(Addr{Col: r.From.Col, Row: row})246 return !labels247 })248 c.Labels = labels || r.From.Row == r.To.Row && isText(r.From)249 }250 if r.To.Row > r.From.Row {251 from := r.From.Col252 if c.Labels {253 from++254 }255 texts, nums := 0, 0256 for _, v := range s.cells.valuesIn(Rect{From: Addr{Col: from, Row: r.From.Row}, To: Addr{Col: r.To.Col, Row: r.From.Row}}) {257 switch v.Kind {258 case Text:259 texts++260 case Number, Bool:261 nums++262 }263 }264 c.Header = texts > 0 && nums == 0265 }266 d := s.ChartData(c)267 if len(d.Series) <= 2 && c.Header {268 names := make([]string, len(d.Series))269 for i, sr := range d.Series {270 names[i] = sr.Name271 }272 c.Title = strings.Join(names, " and ")273 }274 return c275}276277// Charts returns the sheet's charts, bottom first.278func (s *Sheet) Charts() []Chart { return slices.Clone(s.charts) }279280// AddChart adds a chart on top of the others and returns its index.281func (s *Sheet) AddChart(c Chart) int {282 s.change("insert chart", c.Data, func() {283 s.recordCharts()284 s.charts = append(s.charts, c.clamped())285 })286 return len(s.charts) - 1287}288289// SetChart replaces chart i, as one undo step described by label, e.g.290// "move chart".291func (s *Sheet) SetChart(i int, c Chart, label string) {292 if i < 0 || i >= len(s.charts) || s.charts[i] == c.clamped() {293 return294 }295 s.change(label, c.Data, func() {296 s.recordCharts()297 s.charts[i] = c.clamped()298 })299}300301// DeleteChart removes chart i.302func (s *Sheet) DeleteChart(i int) {303 if i < 0 || i >= len(s.charts) {304 return305 }306 s.change("delete chart", s.charts[i].Data, func() {307 s.recordCharts()308 s.charts = slices.Delete(slices.Clone(s.charts), i, i+1)309 })310}311312// clamped keeps a chart's size within limits and its corner on the sheet.313func (c Chart) clamped() Chart {314 c.W = clampInt(c.W, MinChartW, MaxChartW)315 c.H = clampInt(c.H, MinChartH, MaxChartH)316 c.At.Col = clampInt(c.At.Col, 0, MaxCols-1)317 c.At.Row = clampInt(c.At.Row, 0, MaxRows-1)318 return c319}320321// shiftCharts moves charts with inserted or deleted rows or columns: the322// data range grows or shrinks as a formula's range would, and the corner323// moves with its cell. A chart whose data is deleted entirely goes too.324func (s *Sheet) shiftCharts(cell func(Addr) (Addr, bool), rng func(Rect) (Rect, bool)) {325 if len(s.charts) == 0 {326 return327 }328 s.recordCharts()329 var next []Chart330 for _, c := range s.charts {331 data, ok := rng(c.Data)332 if !ok {333 continue334 }335 c.Data = data336 if at, ok := cell(c.At); ok {337 c.At = at338 }339 next = append(next, c.clamped())340 }341 s.charts = next342}