1package sheet23import (4 "bufio"5 "encoding/json"6 "fmt"7 "io"8 "maps"9 "slices"10 "strconv"11 "strings"12)1314// FileExt is the extension of the native worksheet format.15const FileExt = ".012"1617// fileVersion is the newest version Write produces and Read accepts.18// Version 1 files (cells as plain strings, no formatting) still load.19// Version 3 adds named ranges, frozen panes and a filter; a sheet using20// none of them is written as version 2, so earlier builds can open it.21// Version 4 holds several sheets; a workbook of one sheet whose formulas22// name no sheet is still written as version 2 or 3, its sheet's name in23// a "name" field that earlier builds ignore.24// Version 5 is version 4 with pivot tables; a workbook without one is25// still written as version 4. Version 6 is version 5 with tables26// (tablefile.go), written only for a workbook with one. Version 7 is27// version 6 with linked sources (sourcefile.go), written only for a28// workbook linking one.29const fileVersion = 73031// The file is JSON with one entry per cell, keyed by address. A cell32// without formatting is just its input, as in version 1; a formatted cell33// is an object, so diffs stay one line per cell:34//35// "B2": "Rent",36// "C2": {"input": "1450", "format": "currency", "decimals": 2, "bold": true}37//38// Named ranges map each name to its range, or to "#REF!" once its cells39// were deleted: "names": {"Sales": "B2:B20"}. In version 4 the sheets are40// a list, each with its name and the fields a version 3 file has at the41// top, and ranges of names say their sheet: {"Sales": "Q3!B2:B20"}.42//43// "sheets": [44// {45// "name": "Q3",46// "cells": {47// "A1": "Rent",48type fileFormat struct {49 Version int `json:"version"`50 Names map[string]string `json:"names,omitempty"`51 Active int `json:"active,omitempty"` // index of the sheet shown52 // Arithmetic is "decimal" for decimal arithmetic, which needs no53 // version bump: earlier builds ignore it and compute in binary, as54 // Sheets would. It is for the whole workbook, so it stays at the top.55 Arithmetic string `json:"arithmetic,omitempty"`56 // Locale is the tag of the workbook's locale ("de-DE"), when it57 // names one. It only changes how entries are typed and shown (cells58 // are stored in en-US's form), so it needs no version bump either.59 Locale string `json:"locale,omitempty"`60 // Macros and where they were made need no version bump either:61 // earlier builds ignore them, and the sheets read the same.62 MacroOrigin string `json:"macroOrigin,omitempty"`63 Macros []fileMacro `json:"macros,omitempty"`64 fileSheet // versions 1 to 3: the only sheet65 Sheets []fileSheet `json:"sheets,omitempty"` // version 466}6768// fileSheet is one sheet of a file.69type fileSheet struct {70 Name string `json:"name,omitempty"`71 Hidden bool `json:"hidden,omitempty"` // version 4, ignored by older builds72 Widths map[string]int `json:"widths,omitempty"`73 // Heights need no version: earlier builds ignore them and show each74 // row one line tall.75 Heights map[string]int `json:"heights,omitempty"`76 Lines map[string]json.RawMessage `json:"lines,omitempty"` // column and row formats, see linefile.go77 fileView78 Cells map[string]json.RawMessage `json:"cells"`79 Charts []fileChart `json:"charts,omitempty"`80 Pivot *filePivot `json:"pivot,omitempty"` // version 581 // Rules need no version: see rulefile.go.82 CondFormats []fileCondFormat `json:"conditionalFormats,omitempty"`83 Validations []fileValidation `json:"validations,omitempty"`84 // Regions need no version: see regionfile.go.85 Regions []fileRegion `json:"regions,omitempty"`86 // Nor do notebook tabs: see notebookfile.go.87 Tab string `json:"tab,omitempty"`88 Reactive bool `json:"reactive,omitempty"`89 NotebookCells []fileNotebookCell `json:"notebookCells,omitempty"`90}9192// Write saves the workbook the sheet belongs to; see Workbook.Write.93func (s *Sheet) Write(w io.Writer) error { return s.wb.Write(w) }9495// Write saves the workbook as JSON, storing each cell's input as typed96// and its formatting. Cells go one per line in row-major order so diffs97// read naturally. It streams: cells are written as they are read from98// the store, through a buffer of its own, so writing costs about nothing99// on top of the workbook whatever its size.100func (w *Workbook) Write(out io.Writer) error {101 b := bufio.NewWriterSize(out, 64<<10)102 if w.single() {103 s := w.sheets[0]104 version := 2105 if v := s.view; len(w.names) > 0 || v.frozenRows > 0 || v.frozenCols > 0 || v.filter != nil {106 version = 3107 }108 fmt.Fprintf(b, "{\n \"version\": %d,\n", version)109 if s.name != "Sheet1" {110 fmt.Fprintf(b, " \"name\": %s,\n", jsonString(s.name))111 }112 if err := s.writeBody(b, " ", w.headLines()); err != nil {113 return err114 }115 b.WriteString("\n}\n")116 } else {117 version := 4118 switch {119 case w.hasSources():120 version = 7121 case w.hasTables():122 version = 6123 case w.hasPivots():124 version = 5125 }126 fmt.Fprintf(b, "{\n \"version\": %d,\n", version)127 if head := w.headLines(); head != "" {128 b.WriteString(" " + head + ",\n")129 }130 if w.Active() > 0 {131 fmt.Fprintf(b, " \"active\": %d,\n", w.Active())132 }133 b.WriteString(` "sheets": [`)134 for i, s := range w.sheets {135 if i > 0 {136 b.WriteByte(',')137 }138 fmt.Fprintf(b, "\n {\n \"name\": %s,\n", jsonString(s.name))139 if s.tabHidden {140 b.WriteString(" \"hidden\": true,\n")141 }142 if err := s.writeBody(b, " ", ""); err != nil {143 return err144 }145 b.WriteString("\n }")146 }147 b.WriteString("\n ]\n}\n")148 }149 return b.Flush()150}151152// single reports whether the workbook fits the single-sheet format of153// versions 2 and 3: one sheet, not a notebook, no pivot table nor table,154// and no formula naming a sheet.155func (w *Workbook) single() bool {156 return len(w.sheets) == 1 && len(w.crossUsers) == 0 && !w.hasPivots() && !w.hasNotebook() && !w.hasTables() && !w.hasSources()157}158159func jsonString(s string) string {160 raw, _ := json.Marshal(s)161 return string(raw)162}163164// headLines are the workbook's fields before the sheets: the named ranges165// and the settings, separated as the lines of the file.166func (w *Workbook) headLines() string {167 var lines []string168 if names := w.namesLine(); names != "" {169 lines = append(lines, names)170 }171 if w.decimal {172 lines = append(lines, `"arithmetic": "decimal"`)173 }174 if w.locale != nil {175 lines = append(lines, `"locale": `+jsonString(w.locale.Tag))176 }177 if w.macroOrigin != "" && (len(w.macros) > 0 || len(w.allRegions()) > 0 || w.hasNotebook()) {178 lines = append(lines, `"macroOrigin": `+jsonString(w.macroOrigin))179 }180 if len(w.macros) > 0 {181 lines = append(lines, w.macrosLines())182 }183 return strings.Join(lines, ",\n ")184}185186// namesLine is the "names" field, or "" without names.187func (w *Workbook) namesLine() string {188 names := w.Names()189 if len(names) == 0 {190 return ""191 }192 var b strings.Builder193 b.WriteString(`"names": {`)194 for i, n := range names {195 if i > 0 {196 b.WriteString(", ")197 }198 fmt.Fprintf(&b, "%q: %s", n.Name, jsonString(n.Ref()))199 }200 b.WriteString("}")201 return b.String()202}203204// writeBody writes a sheet's fields, each line starting with indent, the205// names line (if any) after the widths as version 3 has it, and no206// newline after the last field.207func (s *Sheet) writeBody(b *bufio.Writer, indent, names string) error {208 if len(s.widths) > 0 {209 b.WriteString(indent + `"widths": {`)210 for i, c := range slices.Sorted(maps.Keys(s.widths)) {211 if i > 0 {212 b.WriteString(", ")213 }214 fmt.Fprintf(b, "%q: %d", ColName(c), s.widths[c])215 }216 b.WriteString("},\n")217 }218 s.writeHeights(b, indent)219 if err := s.writeLines(b, indent); err != nil {220 return err221 }222 if names != "" {223 b.WriteString(indent + names + ",\n")224 }225 if err := s.writeView(b, indent); err != nil {226 return err227 }228 b.WriteString(indent + `"cells": {`)229 var line []byte230 n := 0231 for a := range s.cells.keysIn(Rect{To: Addr{Col: MaxCols - 1, Row: MaxRows - 1}}) {232 line = append(append(line[:0], '\n'), indent...)233 line = appendAddr(append(line, ' ', ' ', '"'), a)234 line = append(line, '"', ':', ' ')235 var ok bool236 var err error237 line, ok, err = s.cells.appendSaved(line, a)238 if err != nil {239 return err240 }241 if !ok {242 continue243 }244 if n > 0 {245 b.WriteByte(',')246 }247 n++248 if _, err := b.Write(line); err != nil {249 return err250 }251 }252 if n > 0 {253 b.WriteString("\n" + indent)254 }255 b.WriteString("}")256 return s.writeObjects(b, indent)257}258259// writeObjects writes the fields after the cells: the charts, one per260// line, the pivot table's definition, and the rules.261func (s *Sheet) writeObjects(b *bufio.Writer, indent string) error {262 if len(s.charts) > 0 {263 b.WriteString(",\n" + indent + `"charts": [`)264 for i, c := range s.charts {265 raw, err := encodeChart(c)266 if err != nil {267 return err268 }269 if i > 0 {270 b.WriteByte(',')271 }272 fmt.Fprintf(b, "\n%s %s", indent, raw)273 }274 b.WriteString("\n" + indent + "]")275 }276 if p := s.pivot.def; p != nil {277 raw, err := encodePivot(p)278 if err != nil {279 return err280 }281 fmt.Fprintf(b, ",\n%s\"pivot\": %s", indent, raw)282 }283 s.writeRules(b, indent)284 if err := s.writeRegionDefs(b, indent); err != nil {285 return err286 }287 return s.writeNotebook(b, indent)288}289290// Read loads a file written by Write, of this or an earlier version, and291// returns the sheet that was shown when it was saved.292func Read(r io.Reader) (*Sheet, error) { return ReadTraced(r, nil) }293294// ReadTraced is Read giving the workbook trace (see SetTrace) before it295// recalculates, so that recalculation nests in the caller's span.296func ReadTraced(r io.Reader, trace any) (*Sheet, error) {297 w, err := readBook(r, trace)298 if err != nil {299 return nil, err300 }301 return w.Sheet(w.Active()), nil302}303304// ReadBook loads a workbook written by Write, of this or an earlier305// version.306func ReadBook(r io.Reader) (*Workbook, error) { return readBook(r, nil) }307308// readNames defines the named ranges of a file. A range without a sheet309// is on the first sheet, as in single-sheet files.310func (w *Workbook) readNames(names map[string]string) error {311 for name, ref := range names {312 if err := ValidName(name); err != nil {313 return fmt.Errorf("name %q: %w", name, err)314 }315 if _, dup := w.LookupName(name); dup {316 return fmt.Errorf("name %q is defined twice", name)317 }318 n := Name{Name: name, Sheet: w.sheets[0], Lost: ref == "#REF!"}319 if !n.Lost {320 sheet, rest := SplitSheet(ref)321 if sheet != "" {322 if n.Sheet = w.Lookup(sheet); n.Sheet == nil {323 return fmt.Errorf("name %q: no sheet %q", name, sheet)324 }325 }326 r, ok := ParseRange(rest)327 if !ok {328 return fmt.Errorf("name %q: invalid range %q", name, ref)329 }330 n.Range = r331 }332 w.putName(nameKey(name), &n)333 }334 return nil335}336337// read fills a sheet, its cells read (fileread.go), from the rest of338// its part of a file, adding to old the command regions to convert.339func (s *Sheet) read(f fileSheet, old *[]oldRegion) error {340 s.tabHidden = f.Hidden341 for name, width := range f.Widths {342 c, ok := ParseCol(name)343 if !ok {344 return fmt.Errorf("invalid column %q", name)345 }346 s.setWidth(c, width)347 }348 for name, h := range f.Heights {349 row, err := strconv.Atoi(name)350 if err != nil || row < 1 || row > MaxRows {351 return fmt.Errorf("invalid row %q", name)352 }353 s.setHeight(row-1, h)354 }355 if err := s.readLines(f.Lines); err != nil {356 return err357 }358 for i, fc := range f.Charts {359 c, err := decodeChart(fc)360 if err != nil {361 return fmt.Errorf("chart %d: %w", i+1, err)362 }363 s.charts = append(s.charts, c)364 }365 if f.Pivot != nil {366 p, err := decodePivot(*f.Pivot)367 if err != nil {368 return err369 }370 s.pivot = pivotState{def: p, stale: true}371 }372 if err := s.readRules(f.CondFormats, f.Validations); err != nil {373 return err374 }375 if err := s.readRegions(f.Regions, old); err != nil {376 return err377 }378 if err := s.readNotebook(f); err != nil {379 return err380 }381 if err := s.checkSourceTab(); err != nil {382 return err383 }384 return s.readView(f.fileView)385}