1package headless23import (4 "src.rest/cat/012/internal/notebook"5 "src.rest/cat/012/internal/sheet"6)78// Description is what a workbook holds, for 012 describe and the MCP9// describe tool: enough for agents (or people) to know which10// references to read and write without reading every cell. Its JSON11// form is a stable schema (docs/reference/json.md): fields are only12// added, never renamed or removed.13type Description struct {14 Sheets []SheetDescription `json:"sheets"`15 Names []NameDescription `json:"names"`16}1718// SheetDescription is one tab: a sheet or a notebook.19type SheetDescription struct {20 Name string `json:"name"`21 Kind string `json:"kind"` // "sheet", "notebook", "pivot" or "source"22 Shown bool `json:"shown"`23 Hidden bool `json:"hidden"`24 // Used is the range from A1 to the last cell with contents, "" when25 // the sheet is empty; Rows and Cols are its size.26 Used string `json:"used"`27 Rows int `json:"rows"`28 Cols int `json:"cols"`29 Cells int `json:"cells"`30 Formulas int `json:"formulas"` // -1 on sheets too large to count them31 Errors int `json:"errors"` // formulas showing errors; -1 when not counted32 // Header is the row guessed to name the used range's columns, and33 // Columns its names; 0 and none when no row looks like one.34 Header int `json:"header_row"`35 Columns []string `json:"columns"`36 Frozen [2]int `json:"frozen"` // rows, columns37 Filter string `json:"filter"` // the filtered range, or ""38 Tables []TableDescription `json:"tables"`39 Regions []RegionDescription `json:"regions"`40 Charts []ChartDescription `json:"charts"`41 Pivot *PivotDescription `json:"pivot,omitempty"`42 Notebook []NotebookCell `json:"notebook_cells,omitempty"`43}4445// NameDescription is a named range.46type NameDescription struct {47 Name string `json:"name"`48 Range string `json:"range"` // with its sheet: Q3!B2:B9, or #REF!49}5051// TableDescription is a named table.52type TableDescription struct {53 Name string `json:"name"`54 Range string `json:"range"` // header row included55 Columns []string `json:"columns"`56}5758// RegionDescription is a linked file or a notebook cell's output sent to59// the sheet.60type RegionDescription struct {61 Name string `json:"name"`62 Kind string `json:"kind"` // "output", "linked" or "source"63 Range string `json:"range"`64 File string `json:"file,omitempty"` // a linked region's file65}6667// ChartDescription is a chart: Number is what 012 export --chart and68// the MCP tools call it.69type ChartDescription struct {70 Number int `json:"number"`71 Title string `json:"title"`72 Type string `json:"type"`73 Data string `json:"data"`74 At string `json:"at"`75}7677// PivotDescription is where a pivot table's data comes from.78type PivotDescription struct {79 Source string `json:"source"` // Sheet!A1:D9980}8182// NotebookCell is one cell of a notebook tab.83type NotebookCell struct {84 Number int `json:"number"`85 Kind string `json:"kind"` // "code" or "note"86 Name string `json:"name,omitempty"`87 Source string `json:"source"`88 // State is "ran", "failed" or "not run"; Error says why it failed.89 State string `json:"state,omitempty"`90 Error string `json:"error,omitempty"`91}9293// maxCounted is the most cells Describe counts formulas and errors on.94const maxCounted = 1_000_0009596// Describe describes the workbook as it is.97func Describe(w *sheet.Workbook) Description {98 d := Description{Sheets: []SheetDescription{}, Names: []NameDescription{}}99 for i, s := range w.Sheets() {100 d.Sheets = append(d.Sheets, describeSheet(s, i == w.Active()))101 }102 for _, n := range w.Names() {103 r := "#REF!"104 if !n.Gone() {105 r = sheet.Qualified(n.Sheet.Name(), n.Range)106 }107 d.Names = append(d.Names, NameDescription{Name: n.Name, Range: r})108 }109 return d110}111112func describeSheet(s *sheet.Sheet, shown bool) SheetDescription {113 d := SheetDescription{Name: s.Name(), Kind: "sheet", Shown: shown, Hidden: s.Hidden(), Cells: s.Len(),114 Tables: []TableDescription{}, Regions: []RegionDescription{}, Charts: []ChartDescription{}, Columns: []string{}}115 if used, ok := s.UsedRange(); ok {116 d.Used, d.Rows, d.Cols = used.String(), used.To.Row+1, used.To.Col+1117 d.Header, d.Columns = guessHeader(s, used)118 }119 d.Formulas, d.Errors = countFormulas(s)120 d.Frozen[0], d.Frozen[1] = s.Frozen()121 if f := s.Filter(); f != nil {122 d.Filter = f.Range.String()123 }124 for _, t := range s.Tables() {125 d.Tables = append(d.Tables, TableDescription{Name: t.Name, Range: t.Range.String(), Columns: append([]string{}, t.Cols...)})126 }127 for _, r := range s.Regions() {128 d.Regions = append(d.Regions, describeRegion(s, r))129 }130 for i, c := range s.Charts() {131 d.Charts = append(d.Charts, ChartDescription{Number: i + 1, Title: ChartTitle(c), Type: c.Type.String(),132 Data: c.Data.String(), At: c.At.String()})133 }134 if p, ok := s.Pivot(); ok {135 d.Kind, d.Pivot = "pivot", &PivotDescription{Source: sheet.Qualified(p.Source, p.Range)}136 }137 if s.IsNotebook() {138 d.Kind, d.Notebook = "notebook", describeNotebook(s)139 }140 if info, ok := s.Source(); ok {141 d.Kind, d.Rows, d.Cols = "source", info.Shape.Rows+1, len(info.Shape.Cols)142 d.Header, d.Columns = 1, append([]string{}, info.Shape.Cols...)143 }144 return d145}146147// countFormulas counts the sheet's formulas and those showing errors,148// or returns -1 for both past maxCounted cells.149func countFormulas(s *sheet.Sheet) (formulas, errors int) {150 if s.Len() > maxCounted {151 return -1, -1152 }153 for _, a := range s.Addrs() {154 if c := s.Cell(a); c != nil && c.IsFormula() {155 formulas++156 if s.Value(a).Kind == sheet.Error {157 errors++158 }159 }160 }161 return formulas, errors162}163164func describeRegion(s *sheet.Sheet, r sheet.Region) RegionDescription {165 d := RegionDescription{Name: r.Name, Kind: "output", Range: r.At.String()}166 if rng, ok := s.RegionTable(r.Name); ok {167 d.Range = rng.String()168 }169 if r.Linked() {170 d.Kind, d.File = "linked", r.File.Path171 }172 if r.File.Paged {173 d.Kind, d.File = "source", r.File.Path174 }175 return d176}177178func describeNotebook(s *sheet.Sheet) []NotebookCell {179 out := []NotebookCell{}180 for i, c := range s.NotebookCells() {181 nc := NotebookCell{Number: i + 1, Kind: "code", Name: c.Name(), Source: c.Source}182 if c.Kind != notebook.Code {183 nc.Kind = "note"184 out = append(out, nc)185 continue186 }187 switch o := s.Book().Output(c.ID); {188 case o == nil || o.Unsaved:189 nc.State = "not run"190 case o.Failed():191 nc.State, nc.Error = "failed", o.Err192 default:193 nc.State = "ran"194 }195 out = append(out, nc)196 }197 return out198}199200// headerLook is how many rows below a header guessHeader reads.201const headerLook = 5202203// guessHeader finds the row naming the used range's columns: its first204// row with contents, when every cell there is text and the rows below205// hold something other than text, or the row is bold. It returns the206// row's number (1 for the first) and the names, or 0.207func guessHeader(s *sheet.Sheet, used sheet.Rect) (int, []string) {208 first, ok := s.FilledBounds(used)209 if !ok {210 return 0, []string{}211 }212 row := first.From.Row213 names := make([]string, 0, used.To.Col-used.From.Col+1)214 bold, n := true, 0215 for col := used.From.Col; col <= used.To.Col; col++ {216 a := sheet.Addr{Col: col, Row: row}217 v := s.Value(a)218 switch v.Kind {219 case sheet.Empty:220 names = append(names, "")221 continue222 case sheet.Text:223 default:224 return 0, []string{}225 }226 names = append(names, v.Str)227 bold = bold && s.CellStyle(a).Bold228 n++229 }230 if n == 0 || row == used.To.Row || !bold && !typedBelow(s, used, row) {231 return 0, []string{}232 }233 return row + 1, names234}235236// typedBelow reports whether the rows below row hold a number, a date,237// a boolean or a formula.238func typedBelow(s *sheet.Sheet, used sheet.Rect, row int) bool {239 for r := row + 1; r <= min(row+headerLook, used.To.Row); r++ {240 for col := used.From.Col; col <= used.To.Col; col++ {241 a := sheet.Addr{Col: col, Row: r}242 if k := s.Value(a).Kind; k != sheet.Empty && k != sheet.Text {243 return true244 }245 }246 }247 return false248}