1package headless23import (4 "fmt"5 "strings"67 "src.rest/cat/012/internal/sheet"8)910// Operation is one change of an Apply: what the MCP apply_operations11// tool takes, each the same engine call a menu command makes. Op says12// which, and the other fields are its arguments:13//14// set Ref (one cell) and Input or Value, and Format, as Set takes an Entry15// write_table Rows under a header at Ref, formatted as WriteTable formats them16// clear Ref: the range's contents, keeping formats and notes17// insert_rows before Ref's first row, Count rows (Ref's rows when 0)18// delete_rows Ref's rows19// insert_columns before Ref's first column, Count columns20// delete_columns Ref's columns21// add_sheet Name, after the last sheet22// rename_sheet Ref (a sheet) to Name23// delete_sheet Ref (a sheet)24// define_name Name for the range Ref25// sort Ref's rows by Keys, the first row left in place with Header26type Operation struct {27 Op string `json:"op" jsonschema:"set, write_table, clear, insert_rows, delete_rows, insert_columns, delete_columns, add_sheet, rename_sheet, delete_sheet, define_name or sort"`28 Ref string `json:"ref,omitempty" jsonschema:"the cell, range or sheet the operation acts on, as formulas write it: B7, Q3!A1:C9, 'Q3 plan'!A:A, Q3; for write_table its top-left cell"`29 Input string `json:"input,omitempty" jsonschema:"for set: what to type, as a person types it in en-US form (=SUM(A1:A6), $3.50, 12%, 2026-09-29); empty clears the cell"`30 Value Value `json:"value,omitempty" jsonschema:"for set, instead of input: the value with its type, as write_cells takes it: {\"currency\": 3.5}, {\"date\": \"2026-09-29\"}, \"00123\""`31 Format string `json:"format,omitempty" jsonschema:"for set: a number format code for the cell, or alone for every cell of ref: $#,##0.00, 0.0%, yyyy-mm-dd"`32 Columns []string `json:"columns,omitempty" jsonschema:"for write_table: the columns' names in order"`33 Rows []Row `json:"rows,omitempty" jsonschema:"for write_table: the rows, each a record of typed values or a list of them in columns' order, as write_table takes them"`34 Formats map[string]string `json:"formats,omitempty" jsonschema:"for write_table: number format codes by column name"`35 Count int `json:"count,omitempty" jsonschema:"for insert_rows and insert_columns: how many; 0 inserts as many as ref spans"`36 Name string `json:"name,omitempty" jsonschema:"for add_sheet and rename_sheet the sheet's name, for define_name the range's"`37 Keys []SortKey `json:"keys,omitempty" jsonschema:"for sort: the columns to sort by, first first"`38 Header bool `json:"header,omitempty" jsonschema:"for sort: the range's first row is a header and stays in place"`39}4041// SortKey is a column to sort by: a letter (B) or, with a header, the42// header's text.43type SortKey struct {44 Column string `json:"column" jsonschema:"a column letter (B) or the header's text"`45 Descending bool `json:"descending,omitempty"`46}4748// Apply makes ops as one change, in order, stopping at the first that49// fails with an error naming it; the workbook should then be thrown50// away rather than saved, as after Set.51func Apply(w *sheet.Workbook, ops []Operation, o SetOptions) (warnings []string, err error) {52 err = w.Batch(sheet.Change{Label: "apply operations", Sheet: w.Sheet(w.Active())}, func() error {53 for i, op := range ops {54 warn, err := applyOne(w, op, o)55 if err != nil {56 return fmt.Errorf("operation %d (%s): %w", i+1, op.Op, err)57 }58 if warn != "" {59 warnings = append(warnings, warn)60 }61 }62 return nil63 })64 return warnings, err65}6667func applyOne(w *sheet.Workbook, op Operation, o SetOptions) (string, error) {68 switch op.Op {69 case "set":70 return setOne(w, Entry{Ref: op.Ref, Input: op.Input, Value: op.Value, Format: op.Format}, o)71 case "write_table":72 warnings, err := writeTable(w, TableSpec{At: op.Ref, Columns: op.Columns, Rows: op.Rows, Formats: op.Formats}, o)73 return strings.Join(warnings, "; "), err74 case "add_sheet":75 _, err := w.AddSheet(op.Name, w.Len())76 return "", err77 case "define_name":78 t, err := Resolve(w, op.Ref)79 if err != nil {80 return "", err81 }82 return "", w.DefineName(op.Name, t.Sheet, targetRect(t))83 case "rename_sheet", "delete_sheet":84 s := w.Lookup(strings.TrimSuffix(op.Ref, "!"))85 if s == nil {86 return "", noSheet(w, op.Ref)87 }88 if op.Op == "rename_sheet" {89 return "", w.RenameSheet(s, op.Name)90 }91 return "", w.DeleteSheet(s)92 case "sort":93 return "", Sort(w, op.Ref, op.Keys, op.Header)94 }95 return "", rangeOp(w, op, o)96}9798// rangeOp makes the operations on a range: clearing it, and inserting99// and deleting its rows or columns.100func rangeOp(w *sheet.Workbook, op Operation, o SetOptions) error {101 t, err := Resolve(w, op.Ref)102 if err != nil {103 return err104 }105 s, r := t.Sheet, targetRect(t)106 rows, cols := r.To.Row-r.From.Row+1, r.To.Col-r.From.Col+1107 if op.Count > 0 {108 rows, cols = op.Count, op.Count109 }110 switch op.Op {111 case "clear":112 if p, ok := s.Protecting(r); ok && !o.Force {113 return fmt.Errorf("%s is protected (%s): force clears it anyway", t, p.Label())114 }115 s.EraseRange(r)116 return nil117 case "insert_rows":118 return s.InsertRows(r.From.Row, rows)119 case "delete_rows":120 s.DeleteRows(r.From.Row, r.To.Row-r.From.Row+1)121 return nil122 case "insert_columns":123 return s.InsertCols(r.From.Col, cols)124 case "delete_columns":125 s.DeleteCols(r.From.Col, r.To.Col-r.From.Col+1)126 return nil127 }128 return fmt.Errorf("no operation %q: set, write_table, clear, insert_rows, delete_rows, insert_columns, delete_columns, add_sheet, rename_sheet, delete_sheet, define_name or sort", op.Op)129}130131// targetRect is the target's range: a whole sheet's is A1 to its last132// cell with contents.133func targetRect(t Target) sheet.Rect {134 if t.Whole {135 r, _ := t.Sheet.UsedRange()136 return r137 }138 return t.Range139}140141// Sort sorts the rows of the range ref names by keys, as Data > Sort142// range does, leaving its first row in place when header is set.143func Sort(w *sheet.Workbook, ref string, keys []SortKey, header bool) error {144 t, err := Resolve(w, ref)145 if err != nil {146 return err147 }148 r := targetRect(t)149 if len(keys) == 0 {150 return fmt.Errorf("name a column to sort %s by", t)151 }152 var sk []sheet.SortKey153 for _, k := range keys {154 col, err := columnOf(t.Sheet, r, k.Column, header)155 if err != nil {156 return err157 }158 sk = append(sk, sheet.SortKey{Col: col, Desc: k.Descending})159 }160 if header {161 r.From.Row++162 }163 if len(t.Sheet.MergesIn(r)) > 0 {164 return fmt.Errorf("%s has merged cells, which can't be sorted", t)165 }166 t.Sheet.SortRange(r, sk)167 return nil168}169170// columnOf finds a column of r by its letter, or with a header row by171// the header's text, ignoring case.172func columnOf(s *sheet.Sheet, r sheet.Rect, name string, header bool) (int, error) {173 if header {174 for col := r.From.Col; col <= r.To.Col; col++ {175 if v := s.Value(sheet.Addr{Col: col, Row: r.From.Row}); v.Kind == sheet.Text && strings.EqualFold(v.Str, name) {176 return col, nil177 }178 }179 }180 if a, ok := sheet.ParseAddr(strings.ToUpper(name) + "1"); ok && a.Row == 0 && a.Col >= r.From.Col && a.Col <= r.To.Col {181 return a.Col, nil182 }183 return 0, fmt.Errorf("%q isn't a column of %s: name it by its letter%s", name, r, headerHint(header))184}185186func headerHint(header bool) string {187 if header {188 return " or its header"189 }190 return ""191}