| 1 | package xlsx_test |
| 2 | |
| 3 | import ( |
| 4 | "strings" |
| 5 | "testing" |
| 6 | |
| 7 | "github.com/xuri/excelize/v2" |
| 8 | ) |
| 9 | |
| 10 | // oracleCell is the same fixed lookup table calc's corpusEnv |
| 11 | // (corpus_test.go) sets up — A1:A3 hold 10/20/30, B1:B3 their English |
| 12 | // names, C1 deliberately left unset — because VLOOKUP/INDEX/MATCH corpus |
| 13 | // entries reference it by address. Anything a corpus entry needs beyond |
| 14 | // this small table is out of scope for this harness (matching the |
| 15 | // current corpus's own scope: every entry is either self-contained or |
| 16 | // reads only this table). |
| 17 | func newOracleWorkbook() (f *excelize.File, sheet string) { |
| 18 | f = excelize.NewFile() |
| 19 | sheet = f.GetSheetList()[0] |
| 20 | must(f.SetCellFloat(sheet, "A1", 10, -1, 64)) |
| 21 | must(f.SetCellStr(sheet, "B1", "ten")) |
| 22 | must(f.SetCellFloat(sheet, "A2", 20, -1, 64)) |
| 23 | must(f.SetCellStr(sheet, "B2", "twenty")) |
| 24 | must(f.SetCellFloat(sheet, "A3", 30, -1, 64)) |
| 25 | must(f.SetCellStr(sheet, "B3", "thirty")) |
| 26 | // C1 intentionally unset. |
| 27 | return f, sheet |
| 28 | } |
| 29 | |
| 30 | // oracleFormulaCell is where each corpus expression's formula is |
| 31 | // written — outside A1:C3 so it can never collide with the support |
| 32 | // table above. |
| 33 | const oracleFormulaCell = "Z1" |
| 34 | |
| 35 | // oracleKnownErrorTokens are the spreadsheet error strings excelize (and |
| 36 | // our own ErrKind.String, errors.go) can produce as a genuine cell |
| 37 | // value, as opposed to excelize's own diagnostic text for a formula it |
| 38 | // could not evaluate at all. "#CIRCULAR!" is deliberately absent: it is |
| 39 | // this app's own invented token (errors.go's ErrKind.String), not a |
| 40 | // real Excel error string, and never appears in excelize output. |
| 41 | var oracleKnownErrorTokens = map[string]bool{ |
| 42 | "#DIV/0!": true, "#VALUE!": true, "#REF!": true, "#NAME?": true, |
| 43 | "#NUM!": true, "#N/A": true, "#NULL!": true, "#SPILL!": true, |
| 44 | "#CALC!": true, "#GETTING_DATA": true, |
| 45 | } |
| 46 | |
| 47 | // oracleAnswer is one corpus expression's outcome from excelize: |
| 48 | // Available is false when excelize could not produce a real spreadsheet |
| 49 | // answer at all (an unimplemented function, a formula it refuses to |
| 50 | // parse) — see classifyOracleResult's doc comment for the full |
| 51 | // reasoning. Value is only meaningful when Available is true. |
| 52 | type oracleAnswer struct { |
| 53 | Available bool |
| 54 | Value string |
| 55 | Reason string // why Available is false, or empty |
| 56 | } |
| 57 | |
| 58 | // computeOracleAnswer writes expr into a fresh excelize workbook (with |
| 59 | // the same support table calc's corpusEnv provides) and asks excelize to |
| 60 | // evaluate it via CalcCellValue — the independent-implementation half of |
| 61 | // this harness's cross-check. See classifyOracleResult for how its |
| 62 | // (value, error) pair is turned into an oracleAnswer. |
| 63 | func computeOracleAnswer(t *testing.T, expr string) oracleAnswer { |
| 64 | t.Helper() |
| 65 | f, sheet := newOracleWorkbook() |
| 66 | defer func() { |
| 67 | if err := f.Close(); err != nil { |
| 68 | t.Errorf("close oracle workbook for %q: %v", expr, err) |
| 69 | } |
| 70 | }() |
| 71 | |
| 72 | if err := f.SetCellFormula(sheet, oracleFormulaCell, "="+expr); err != nil { |
| 73 | return oracleAnswer{Available: false, Reason: "excelize rejected the formula: " + err.Error()} |
| 74 | } |
| 75 | val, err := f.CalcCellValue(sheet, oracleFormulaCell) |
| 76 | return classifyOracleResult(val, err) |
| 77 | } |
| 78 | |
| 79 | // classifyOracleResult turns CalcCellValue's (value, error) pair into an |
| 80 | // oracleAnswer. excelize is NOT consistent about what it returns for a |
| 81 | // formula error (verified against v2.11.0 directly, not assumed): |
| 82 | // |
| 83 | // - A real spreadsheet error the formula itself produces (division by |
| 84 | // zero, VLOOKUP with no match, ...) comes back as BOTH a non-nil err |
| 85 | // AND a val that IS one of the standard error tokens (e.g. val="#N/A", |
| 86 | // err="VLOOKUP no result found" — err's own text is not the token). |
| 87 | // That is a genuine answer, not an unavailable oracle: val is used. |
| 88 | // - A function excelize has not implemented ALSO comes back with a val |
| 89 | // that happens to look like a token (val="#VALUE!") but err's text |
| 90 | // contains "not support" — checked first, specifically so this case |
| 91 | // is never mistaken for the first one just because val looks legit. |
| 92 | // - A formula excelize's parser rejects outright comes back with |
| 93 | // val="" and err="formula not valid". |
| 94 | // |
| 95 | // Anything else (an err whose text is not a recognized shape) is treated |
| 96 | // as unavailable too, conservatively: better to under-claim oracle |
| 97 | // coverage than to silently accept a value from a state this harness |
| 98 | // does not actually understand. |
| 99 | func classifyOracleResult(val string, err error) oracleAnswer { |
| 100 | if err == nil { |
| 101 | return oracleAnswer{Available: true, Value: val} |
| 102 | } |
| 103 | msg := err.Error() |
| 104 | if strings.Contains(msg, "not support") { |
| 105 | return oracleAnswer{Available: false, Reason: "unimplemented in excelize: " + msg} |
| 106 | } |
| 107 | if msg == "formula not valid" { |
| 108 | return oracleAnswer{Available: false, Reason: "excelize could not parse the formula: " + msg} |
| 109 | } |
| 110 | if oracleKnownErrorTokens[msg] { |
| 111 | return oracleAnswer{Available: true, Value: msg} |
| 112 | } |
| 113 | if oracleKnownErrorTokens[val] { |
| 114 | return oracleAnswer{Available: true, Value: val} |
| 115 | } |
| 116 | return oracleAnswer{Available: false, Reason: "unrecognized excelize error: " + msg} |
| 117 | } |
| 118 | |
| 119 | func must(err error) { |
| 120 | if err != nil { |
| 121 | panic(err) |
| 122 | } |
| 123 | } |
| 124 | |
amadan