1package fileio23import (4 "context"5 "fmt"6 "math"7 "strconv"8 "strings"910 "src.rest/cat/012/internal/locale"11 "src.rest/cat/012/internal/sheet"12 "src.rest/cat/012/internal/telemetry"13)1415// builder fills a new workbook for an importer, one sheet at a time (s16// is the sheet being filled). It keeps text as text (an imported "12%"17// string must not turn into a number, nor "=A1" into a formula), falls18// back to a formula's cached value when the formula can't be translated,19// and counts what didn't fit so the notes can say.20type builder struct {21 s *sheet.Sheet22 loc *locale.Locale // what entries are typed in (nil for en-US)2324 rowsOut, colsOut int // how far past the sheet's edges the data went25 values int // formulas kept as their values26 valueExample string2728 // The cell budget (max-cells): rows are kept whole while their cells29 // fit, and none after the first that doesn't.30 maxCells int31 cells int // cells stored32 row int // the row being filled33 rowCells []sheet.Addr // its cells stored so far34 full bool // the budget ran out at row fullRow35 fullRow int36 lastRow int // the last row the data reached37}3839// newBuilder starts a workbook keeping at most maxCells cells; 0 means40// the max-cells setting, and a negative number no budget (the format's41// own limits apply). The workbook's recalculation nests in the span ctx42// carries: the import's.43func newBuilder(ctx context.Context, maxCells int) *builder {44 if maxCells == 0 {45 maxCells = sheet.MaxCells()46 }47 s := sheet.New()48 s.Book().SetTrace(telemetry.NewTrace(telemetry.ParentFrom(ctx)))49 return &builder{s: s, maxCells: maxCells}50}5152// fits reports whether a is inside the worksheet and the budget,53// recording by how much data overflowed when it isn't.54func (b *builder) fits(a sheet.Addr) bool {55 b.lastRow = max(b.lastRow, a.Row)56 if a.Row >= sheet.MaxRows {57 b.rowsOut = max(b.rowsOut, a.Row-sheet.MaxRows+1)58 }59 if a.Col >= sheet.MaxCols {60 b.colsOut = max(b.colsOut, a.Col-sheet.MaxCols+1)61 }62 return a.Valid() && !b.full63}6465// nextSheet starts filling another sheet, whose rows start again at 1.66func (b *builder) nextSheet(s *sheet.Sheet) {67 b.s, b.row, b.rowCells = s, 0, b.rowCells[:0]68}6970// take counts a cell about to be stored against the budget, reporting71// false when it doesn't fit: then the row it's on is taken out again, so72// the sheet ends with whole rows.73func (b *builder) take(a sheet.Addr) bool {74 if b.maxCells < 0 {75 return true76 }77 if a.Row != b.row {78 b.row, b.rowCells = a.Row, b.rowCells[:0]79 }80 if b.cells < b.maxCells {81 b.cells++82 b.rowCells = append(b.rowCells, a)83 return true84 }85 for _, c := range b.rowCells {86 b.s.Unload(c)87 }88 b.cells -= len(b.rowCells)89 b.rowCells = b.rowCells[:0]90 b.full, b.fullRow = true, a.Row91 return false92}9394// isFull reports whether the budget ran out, so an importer can stop95// reading rows.96func (b *builder) isFull() bool { return b.full }9798// flatten replaces line breaks and tabs, which a cell can't show, with99// spaces.100func flatten(s string) string {101 if !strings.ContainsAny(s, "\r\n\t") {102 return s103 }104 s = strings.ReplaceAll(s, "\r\n", " ")105 return strings.Map(func(r rune) rune {106 switch r {107 case '\r', '\n', '\t':108 return ' '109 }110 return r111 }, s)112}113114// textInput is the entry that shows s as text: s itself, or s behind a115// ' when typing it would make a number, a boolean or a formula.116func textInput(s string) string {117 s = flatten(s)118 if s == "" {119 return ""120 }121 if strings.HasPrefix(s, "'") || sheet.IsFormulaEntry(s) {122 return "'" + s123 }124 if _, _, ok := sheet.ParseValue(s); ok {125 return "'" + s126 }127 switch strings.ToUpper(strings.TrimSpace(s)) {128 case "TRUE", "FALSE":129 return "'" + s130 }131 return s132}133134// entryInput is s as if typed into Sheets in loc, for text formats:135// numbers, dates, currency and percentages become values with formats.136// Formulas stay text: a data file shouldn't run formulas (or ask JEV137// questions) just by being opened.138func entryInput(s string, loc *locale.Locale) string {139 s = flatten(s)140 if strings.HasPrefix(s, "'") || sheet.IsFormulaEntry(s) {141 return "'" + s142 }143 if in := sheet.CanonicalEntry(s, loc); !sheet.IsFormulaEntry(in) {144 return in145 }146 return "'" + s // a formula in loc's syntax147}148149// numInput writes v so it parses back to the same number.150func numInput(v float64) string {151 if a := math.Abs(v); a == 0 || (a >= 1e-6 && a < 1e21) {152 return strconv.FormatFloat(v, 'f', -1, 64)153 }154 return strconv.FormatFloat(v, 'E', -1, 64)155}156157func (b *builder) put(a sheet.Addr, input string, f sheet.Format, st sheet.Style) {158 if input == "" && f.IsZero() && st.IsZero() || !b.fits(a) || !b.take(a) {159 return160 }161 if err := b.s.Load(a, input, f, st); err != nil {162 // Only formulas fail; keep what was written as text.163 b.s.Load(a, "'"+input, f, st)164 }165}166167// text stores s as text.168func (b *builder) text(a sheet.Addr, s string, f sheet.Format, st sheet.Style) {169 b.put(a, textInput(s), f, st)170}171172// entry stores s as if typed in the builder's locale (see entryInput).173func (b *builder) entry(a sheet.Addr, s string) {174 b.put(a, entryInput(s, b.loc), sheet.Format{}, sheet.Style{})175}176177// number stores v with format f. Plain text formats keep the digits.178func (b *builder) number(a sheet.Addr, v float64, f sheet.Format, st sheet.Style) {179 if math.IsNaN(v) || math.IsInf(v, 0) {180 b.put(a, "'#NUM!", f, st)181 return182 }183 b.put(a, numInput(v), f, st)184}185186func (b *builder) boolean(a sheet.Addr, v bool, st sheet.Style) {187 b.put(a, strings.ToUpper(strconv.FormatBool(v)), sheet.Format{}, st)188}189190// formula stores a formula in 012's syntax (with the leading =), or,191// when it doesn't parse, the value it had in the file, stored by keep.192// It reports whether the formula was stored.193func (b *builder) formula(a sheet.Addr, text string, f sheet.Format, st sheet.Style, keep func()) bool {194 if !b.fits(a) {195 return false196 }197 if _, err := sheet.Parse(text); err == nil && b.take(a) {198 if b.s.Load(a, text, f, st) == nil {199 return true200 }201 b.cells--202 b.rowCells = b.rowCells[:len(b.rowCells)-1]203 }204 b.kept(a, text, keep)205 return false206}207208// kept stores a formula's value with keep, counting it for the notes.209func (b *builder) kept(a sheet.Addr, text string, keep func()) {210 if !b.fits(a) {211 return212 }213 keep()214 b.values++215 if b.valueExample == "" {216 b.valueExample = strings.TrimSpace(a.String() + " " + text)217 }218}219220// finish recalculates the workbook and returns the sheet being filled221// with notes on what was lost.222func (b *builder) finish(notes []string) (*sheet.Sheet, []string) {223 b.s.RecalcAll()224 if b.values > 0 {225 notes = append(notes, fmt.Sprintf("%s kept as values, e.g. %s", count(b.values, "formula", "formulas"), b.valueExample))226 }227 switch {228 case b.full:229 notes = append(notes, fmt.Sprintf("only the first %s rows fit in max-cells (%s cells); %s left out",230 thousands(b.fullRow), thousands(b.maxCells), count(max(b.lastRow, b.fullRow)-b.fullRow+1+b.rowsOut, "row", "rows")))231 case b.rowsOut > 0:232 notes = append(notes, fmt.Sprintf("only the first %s rows fit; %s left out", thousands(sheet.MaxRows), count(b.rowsOut, "row", "rows")))233 }234 if b.colsOut > 0 {235 notes = append(notes, fmt.Sprintf("only columns A to %s fit; %s left out", sheet.ColName(sheet.MaxCols-1), count(b.colsOut, "column", "columns")))236 }237 return b.s, notes238}239240// count writes n with a singular or plural noun: "1 row", "1,200 rows".241func count(n int, one, many string) string {242 if n == 1 {243 return "1 " + one244 }245 return thousands(n) + " " + many246}247248// thousands writes n with thousands separators.249func thousands(n int) string {250 s := strconv.Itoa(n)251 if n < 0 {252 return "-" + thousands(-n)253 }254 for i := len(s) - 3; i > 0; i -= 3 {255 s = s[:i] + "," + s[i:]256 }257 return s258}