1package fileio23import (4 "flag"5 "fmt"6 "os"7 "strconv"8 "strings"9 "testing"1011 "src.rest/cat/012/internal/sheet"12)1314// The Sheets corpus (testdata/sheets_corpus.tsv) holds formulas whose15// results 012 matches with Google Sheets on purpose, each with 012's16// result and Sheets' as checked in Sheets. TestSheetsCorpus holds 012 to17// its recorded results and asks every difference from Sheets for a18// reason. The corpus reaches Sheets as an XLSX workbook written by 012's19// own exporter (sheetscorpus_xlsx_test.go), and Sheets' answers come back20// as a CSV download of its corpus sheet.21var (22 updateCorpus = flag.Bool("update-corpus", false, "record 012's current results in testdata/sheets_corpus.tsv")23 corpusXLSX = flag.String("corpus-xlsx", "", "write the Sheets corpus as an XLSX workbook to this path, to import into Sheets")24 corpusFrom = flag.String("update-from", "", "record Sheets' results from a CSV download of the imported corpus sheet")25)2627const (28 corpusPath = "testdata/sheets_corpus.tsv"29 unconfirmed = "unconfirmed" // Sheets' result, until checked in Sheets30 onPurpose = "on purpose:" // starts the note of a deliberate difference31)3233// corpusFixture is the Data sheet the corpus formulas read, typed as a34// user would: the oracle's fixture, with a text number, an error, a35// FALSE and a #N/A in column J.36//37// A B C D E F G H I J38// 1 10 north 1 Apple 1.5 9/26/2026 1 -1000 30 '339// 2 20 south 2 Banana 2.5 2026-01-31 2 300 20 =1/040// 3 30 north 3 Cherry 3.5 14:30 2 400 10 FALSE41// 4 apple east 4 date x 2/29/2024 3 500 =NA()42// 5 north 5 Apple 9 12/31/2025 543// 6 ="" 6 TRUE44// 7 -5 Ab*d 745var corpusFixture = map[string]string{46 "A1": "10", "A2": "20", "A3": "30", "A4": "apple", "A7": "-5",47 "B1": "north", "B2": "south", "B3": "north", "B4": "east", "B5": "north", "B6": `=""`, "B7": "Ab*d",48 "C1": "1", "C2": "2", "C3": "3", "C4": "4", "C5": "5", "C6": "6", "C7": "7",49 "D1": "Apple", "D2": "Banana", "D3": "Cherry", "D4": "date", "D5": "Apple",50 "E1": "1.5", "E2": "2.5", "E3": "3.5", "E4": "x", "E5": "9", "E6": "TRUE",51 "F1": "9/26/2026", "F2": "2026-01-31", "F3": "14:30", "F4": "2/29/2024", "F5": "12/31/2025",52 "G1": "1", "G2": "2", "G3": "2", "G4": "3",53 "H1": "-1000", "H2": "300", "H3": "400", "H4": "500",54 "I1": "30", "I2": "20", "I3": "10",55 "J1": "'3", "J2": "=1/0", "J3": "FALSE", "J4": "=NA()",56}5758// The corpus sheet's columns: each formula is in C, and D and E read its59// result as text and its kind with formulas both 012 and Sheets compute,60// so both answer in the same terms.61const (62 colSection = iota63 colFormula64 colResult65 colText66 colKind67 colOurs68 colCheck69 colNote70)7172var corpusHeaders = []string{"section", "formula", "result", "as text", "kind", "012 result", "check", "note"}7374// corpusLine is a line of the corpus file: a formula row, or a comment,75// section heading or blank line kept as it is.76type corpusLine struct {77 text string78 section string79 formula string80 ours string // 012's recorded result81 sheets string // Sheets' result, or unconfirmed82 note string83}8485func readCorpus(t *testing.T) []*corpusLine {86 t.Helper()87 data, err := os.ReadFile(corpusPath)88 if err != nil {89 t.Fatal(err)90 }91 var out []*corpusLine92 section := ""93 for _, text := range strings.Split(strings.TrimSuffix(string(data), "\n"), "\n") {94 if !strings.HasPrefix(text, "=") {95 if h, ok := strings.CutPrefix(text, "## "); ok {96 section = h97 }98 out = append(out, &corpusLine{text: text})99 continue100 }101 f := append(strings.Split(text, "\t"), "", "", "")102 out = append(out, &corpusLine{section: section, formula: f[0], ours: f[1], sheets: f[2], note: f[3]})103 }104 return out105}106107func writeCorpus(t *testing.T, lines []*corpusLine) {108 t.Helper()109 var b strings.Builder110 for _, l := range lines {111 if l.formula == "" {112 b.WriteString(l.text + "\n")113 continue114 }115 if l.sheets == "" {116 l.sheets = unconfirmed117 }118 b.WriteString(strings.TrimRight(strings.Join([]string{l.formula, l.ours, l.sheets, l.note}, "\t"), "\t") + "\n")119 }120 if err := os.WriteFile(corpusPath, []byte(b.String()), 0o644); err != nil {121 t.Fatal(err)122 }123}124125// corpusRows are the formula rows of lines, in order.126func corpusRows(lines []*corpusLine) []*corpusLine {127 var rows []*corpusLine128 for _, l := range lines {129 if l.formula != "" {130 rows = append(rows, l)131 }132 }133 return rows134}135136// corpusBook builds the workbook the corpus is computed in: the Corpus137// sheet, a row per formula under a header row, and the Data sheet.138func corpusBook(t *testing.T, rows []*corpusLine) *sheet.Sheet {139 t.Helper()140 s := sheet.New()141 book := s.Book()142 if err := book.RenameSheet(s, "Corpus"); err != nil {143 t.Fatal(err)144 }145 data, err := book.AddSheet("Data", 1)146 if err != nil {147 t.Fatal(err)148 }149 for a, in := range corpusFixture {150 at := addr(t, a)151 setAt(t, data, at.Col, at.Row, in)152 }153 for c, h := range corpusHeaders {154 setAt(t, s, c, 0, "'"+h)155 }156 for i, r := range rows {157 n := strconv.Itoa(i + 2)158 setAt(t, s, colSection, i+1, r.section)159 setAt(t, s, colFormula, i+1, "'"+r.formula)160 setAt(t, s, colResult, i+1, r.formula)161 setAt(t, s, colText, i+1, `=C`+n+`&""`)162 setAt(t, s, colKind, i+1, kindFormula("C"+n))163 }164 for c, w := range []int{22, 48, 14, 24, 8, 24, 8, 30} {165 s.SetColWidth(c, w)166 }167 return s168}169170// kindFormula names the kind of the value in ref, in words both 012 and171// Sheets compute alike.172func kindFormula(ref string) string {173 return fmt.Sprintf(`=IF(ISERROR(%[1]s), "error", IF(ISNUMBER(%[1]s), "number", IF(ISTEXT(%[1]s), "text", `+174 `IF(ISLOGICAL(%[1]s), "logical", IF(ISBLANK(%[1]s), "blank", "other")))))`, ref)175}176177// setAt types in into the cell at col and row of s.178func setAt(t *testing.T, s *sheet.Sheet, col, row int, in string) {179 t.Helper()180 a := sheet.Addr{Col: col, Row: row}181 if err := s.Set(a, in); err != nil {182 t.Errorf("%s %q: %v", a, in, err)183 }184}185186// corpusResult reads what 012 computed for the formula on row i of the187// corpus sheet, in the corpus file's terms.188func corpusResult(s *sheet.Sheet, i int) string {189 text := s.Value(sheet.Addr{Col: colText, Row: i + 1})190 kind := s.Value(sheet.Addr{Col: colKind, Row: i + 1})191 return encodeResult(text.String(), kind.String())192}193194// encodeResult writes a result, given as text and its kind, as the195// corpus file does: text quoted, (blank) for an empty cell, and196// numbers, booleans and errors as they read.197func encodeResult(text, kind string) string {198 switch kind {199 case "number", "logical", "error":200 return text201 case "text":202 return strconv.Quote(text)203 case "blank":204 return "(blank)"205 }206 return "(" + kind + ") " + text207}208209func TestSheetsCorpus(t *testing.T) {210 lines := readCorpus(t)211 rows := corpusRows(lines)212 s := corpusBook(t, rows)213 if *corpusFrom != "" {214 foldSheets(t, rows, *corpusFrom)215 }216 for i, r := range rows {217 got := corpusResult(s, i)218 if *updateCorpus {219 r.ours = got220 }221 checkCorpusRow(t, r, got)222 }223 if *updateCorpus || *corpusFrom != "" {224 writeCorpus(t, lines)225 }226 if *corpusXLSX != "" {227 exportCorpus(t, s, rows, *corpusXLSX)228 }229}230231// checkCorpusRow holds 012 to its recorded result, and a difference232// from Sheets to a note saying why.233func checkCorpusRow(t *testing.T, r *corpusLine, got string) {234 t.Helper()235 if got != r.ours {236 t.Errorf("%s = %s, recorded %s (go test ./internal/fileio -run SheetsCorpus -update-corpus records it)", r.formula, got, r.ours)237 }238 if r.sheets != "" && r.sheets != unconfirmed && r.sheets != r.ours && !strings.HasPrefix(r.note, onPurpose) {239 t.Errorf("%s = %s in 012 and %s in Sheets: make 012 agree, or say why in a note starting %q", r.formula, r.ours, r.sheets, onPurpose)240 }241}