1package fileio23import (4 "context"5 "encoding/csv"6 "os"7 "slices"8 "strconv"9 "strings"10 "testing"1112 "src.rest/cat/012/internal/sheet"13)1415// sheetsOnlyNote marks a corpus row whose formula 012's exporter saves as16// its value, because Excel has nothing the same (formula.go): in Sheets17// the formula has to be typed into C.18const sheetsOnlyNote = "saved as a value: type the formula in B into C"1920// exportCorpus writes the corpus workbook s as an XLSX file at name with21// 012's exporter, for importing into Sheets. Sheets recalculates C, D22// and E on import; F keeps 012's results, and G compares the two.23func exportCorpus(t *testing.T, s *sheet.Sheet, rows []*corpusLine, name string) {24 t.Helper()25 for i, r := range rows {26 n := strconv.Itoa(i + 2)27 setAt(t, s, colOurs, i+1, "'"+corpusResult(s, i))28 setAt(t, s, colCheck, i+1, checkFormula(n))29 note := r.note30 if !excelKeeps(r.formula) {31 setAt(t, s, colResult, i+1, "")32 note = sheetsOnlyNote33 }34 setAt(t, s, colNote, i+1, note)35 }36 res, err := Export(context.Background(), name, XLSX, SnapBook(s), ExportOptions{})37 if err != nil {38 t.Fatal(err)39 }40 for _, n := range res.Notes {41 t.Log(n)42 }43 t.Logf("wrote %d formulas to %s", len(rows), name)44}4546// checkFormula compares, on row n, the result in D and E with 012's in47// F, writing it as encodeResult does. Errors only say both are errors;48// the error in C and the one in F are compared by eye.49func checkFormula(n string) string {50 d, e, f := "D"+n, "E"+n, "F"+n51 enc := `IF(` + e + `="text", """" & ` + d + ` & """", IF(` + e + `="blank", "(blank)", ` + d + ` & ""))`52 return `=IFERROR(IF(EXACT(` + enc + `, ` + f + `), "same", "DIFFERS"), IF(LEFT(` + f + `, 1)="#", "both errors", "DIFFERS"))`53}5455// excelKeeps reports whether 012's exporter keeps formula as a formula.56func excelKeeps(formula string) bool {57 _, ok := toExcelFormula(formula, nil)58 return ok59}6061// foldSheets records Sheets' results from name, a CSV download of the62// corpus sheet after importing the XLSX workbook into Sheets. Rows left63// out of the download, formulas saved as values that weren't typed in,64// and functions the import didn't recognize stay as they are.65func foldSheets(t *testing.T, rows []*corpusLine, name string) {66 t.Helper()67 f, err := os.Open(name)68 if err != nil {69 t.Fatal(err)70 }71 defer f.Close()72 recs, err := csv.NewReader(f).ReadAll()73 if err != nil {74 t.Fatal(err)75 }76 if len(recs) == 0 || !slices.Equal(recs[0][:colOurs], corpusHeaders[:colOurs]) {77 t.Fatalf("%s: expected a CSV download of the corpus sheet, with headers %q", name, corpusHeaders[:colOurs])78 }79 sheets := map[string]string{}80 for _, rec := range recs[1:] {81 if len(rec) > colKind {82 text := rec[colText]83 if rec[colKind] == "number" {84 // Sheets writes a number a hair off a whole one, such as85 // 100*1.1, as "110." when it rounds to 15 digits.86 text = strings.TrimSuffix(text, ".")87 }88 sheets[rec[colFormula]] = encodeResult(text, rec[colKind])89 }90 }91 skipped := 092 for _, r := range rows {93 got, ok := sheets[r.formula]94 // #NAME? where 012 knows every name means the import didn't95 // recognize a function (Sheets reads Excel's _xlfn._xlws.FILTER96 // so): the formula wasn't computed.97 if !ok || (got == "(blank)" && !excelKeeps(r.formula)) || (got == "#NAME?" && r.ours != got) {98 skipped++99 continue100 }101 r.sheets = got102 }103 if skipped > 0 {104 t.Logf("%d formulas have no result from Sheets in %s", skipped, name)105 }106}