1package headless23import (4 "fmt"5 "strings"67 "src.rest/cat/012/internal/sheet"8)910// ChartSpec is a chart to add, as Insert > Chart makes one: over Data,11// with the header row, category labels and title guessed as Sheets12// guesses them unless given.13type ChartSpec struct {14 Data string `json:"data" jsonschema:"the range to chart, e.g. Q3!A1:C9"`15 Type string `json:"type,omitempty" jsonschema:"column, bar, line, pie, area or scatter; column when empty"`16 Title string `json:"title,omitempty" jsonschema:"the title; guessed from the series when empty"`17 At string `json:"at,omitempty" jsonschema:"the cell under the chart's top-left corner; right of the data when empty"`18 Width int `json:"width,omitempty" jsonschema:"the width in terminal cells, 20 to 240; 60 when 0"`19 Height int `json:"height,omitempty" jsonschema:"the height in terminal cells, 8 to 120; 18 when 0"`20 ByRow bool `json:"by_row,omitempty" jsonschema:"series run along rows rather than down columns"`21 Stack string `json:"stack,omitempty" jsonschema:"stacked or percent, for column, bar and area charts"`22}2324// ChartMade says which chart AddChart made: its sheet and number, as25// describe lists it.26type ChartMade struct {27 Sheet string `json:"sheet"`28 Number int `json:"number"`29 Title string `json:"title"`30}3132// AddChart adds a chart as spec says.33func AddChart(w *sheet.Workbook, spec ChartSpec) (ChartMade, error) {34 t, err := Resolve(w, spec.Data)35 if err != nil {36 return ChartMade{}, err37 }38 s, r := t.Sheet, targetRect(t)39 c := s.GuessChart(r)40 if spec.Type != "" {41 typ, ok := sheet.ParseChartType(spec.Type)42 if !ok {43 return ChartMade{}, fmt.Errorf("no chart type %q: column, bar, line, pie, area or scatter", spec.Type)44 }45 c.Type = typ46 }47 if spec.Title != "" {48 c.Title = spec.Title49 }50 c.ByRow = spec.ByRow51 if c.Stack, err = parseStack(spec.Stack); err != nil {52 return ChartMade{}, err53 }54 c.W, c.H = orDefault(spec.Width, 60), orDefault(spec.Height, 18)55 c.At = sheet.Addr{Col: min(r.To.Col+2, sheet.MaxCols-1), Row: r.From.Row}56 if spec.At != "" {57 cs, a, err := ResolveCell(w, spec.At)58 if err != nil {59 return ChartMade{}, err60 }61 if cs != s {62 return ChartMade{}, fmt.Errorf("a chart sits on its data's sheet, %s", s.Name())63 }64 c.At = a65 }66 i := s.AddChart(c)67 return ChartMade{Sheet: s.Name(), Number: i + 1, Title: ChartTitle(s.Charts()[i])}, nil68}6970func parseStack(s string) (sheet.ChartStack, error) {71 switch strings.ToLower(s) {72 case "", "none":73 return sheet.StackNone, nil74 case "stacked":75 return sheet.StackNormal, nil76 case "percent":77 return sheet.StackPercent, nil78 }79 return 0, fmt.Errorf("stack %q: stacked or percent", s)80}8182func orDefault(v, d int) int {83 if v <= 0 {84 return d85 }86 return v87}8889// PivotSpec is a pivot table to add, as Insert > Pivot table makes one:90// a new sheet summarizing Source, whose first row names its columns.91type PivotSpec struct {92 Source string `json:"source" jsonschema:"the data, header row included, e.g. Sales!A1:F99"`93 Name string `json:"name,omitempty" jsonschema:"the new sheet's name; Pivot Table N when empty"`94 Rows []string `json:"rows,omitempty" jsonschema:"columns whose values become rows, by header or letter"`95 Columns []string `json:"columns,omitempty" jsonschema:"columns whose values become columns, by header or letter"`96 Values []PivotValue `json:"values" jsonschema:"columns to summarize"`97 Totals bool `json:"totals,omitempty" jsonschema:"add grand totals"`98}99100// PivotValue is a column to summarize, and how.101type PivotValue struct {102 Column string `json:"column" jsonschema:"by header or letter"`103 Summarize string `json:"summarize,omitempty" jsonschema:"sum, counta, count, countunique, average, max or min; sum when empty"`104}105106// AddPivot adds a pivot table's sheet after its source's, returning its107// name.108func AddPivot(w *sheet.Workbook, spec PivotSpec) (string, error) {109 t, err := Resolve(w, spec.Source)110 if err != nil {111 return "", err112 }113 src, r := t.Sheet, targetRect(t)114 p := sheet.Pivot{RowTotals: spec.Totals, ColumnTotals: spec.Totals}115 for _, name := range spec.Rows {116 col, err := columnOf(src, r, name, true)117 if err != nil {118 return "", err119 }120 p.Rows = append(p.Rows, sheet.PivotGroup{Col: col})121 }122 for _, name := range spec.Columns {123 col, err := columnOf(src, r, name, true)124 if err != nil {125 return "", err126 }127 p.Columns = append(p.Columns, sheet.PivotGroup{Col: col})128 }129 if len(spec.Values) == 0 {130 return "", fmt.Errorf("name a column to summarize")131 }132 for _, v := range spec.Values {133 col, err := columnOf(src, r, v.Column, true)134 if err != nil {135 return "", err136 }137 sum := sheet.SumBy138 if v.Summarize != "" {139 var ok bool140 if sum, ok = sheet.ParseSummarize(strings.ToLower(v.Summarize)); !ok {141 return "", fmt.Errorf("summarize %q: sum, counta, count, countunique, average, max or min", v.Summarize)142 }143 }144 p.Values = append(p.Values, sheet.PivotValue{Col: col, Summarize: sum})145 }146 s, err := w.CreatePivot(src, r, spec.Name, p)147 if err != nil {148 return "", err149 }150 return s.Name(), nil151}