1// Package oracle checks 012's formula engine against excelize's2// calculation engine: the same data and formulas go into both, and the3// results must agree. It is a separate module so the main binary never4// depends on excelize. Run it with make oracle.5package oracle67import (8 "math"9 "strconv"10 "strings"11 "testing"1213 "github.com/xuri/excelize/v2"1415 "src.rest/cat/012/internal/sheet"16)1718// fixture is the data both engines read, typed as a user would.19//20// A B C D E F G H I21// 1 10 north 1 Apple 1.5 9/26/2026 1 -1000 3022// 2 20 south 2 Banana 2.5 2026-01-31 2 300 2023// 3 30 north 3 Cherry 3.5 14:30 2 400 1024// 4 apple east 4 date x 2/29/2024 3 50025// 5 north 5 Apple 9 12/31/2025 526// 6 "" 6 TRUE27// 7 -5 Ab*d 728var fixture = map[string]string{29 "A1": "10", "A2": "20", "A3": "30", "A4": "apple", "A7": "-5",30 "B1": "north", "B2": "south", "B3": "north", "B4": "east", "B5": "north", "B6": `=""`, "B7": "Ab*d",31 "C1": "1", "C2": "2", "C3": "3", "C4": "4", "C5": "5", "C6": "6", "C7": "7",32 "D1": "Apple", "D2": "Banana", "D3": "Cherry", "D4": "date", "D5": "Apple",33 "E1": "1.5", "E2": "2.5", "E3": "3.5", "E4": "x", "E5": "9", "E6": "TRUE",34 "F1": "9/26/2026", "F2": "2026-01-31", "F3": "14:30", "F4": "2/29/2024", "F5": "12/31/2025",35 "G1": "1", "G2": "2", "G3": "2", "G4": "3",36 "H1": "-1000", "H2": "300", "H3": "400", "H4": "500",37 "I1": "30", "I2": "20", "I3": "10",38}3940// formulas are compared between the engines. They use syntax both41// accept; toExcel covers the differences.42var formulas = []string{43 // Math44 `=SUM(A1:A7)`, `=SUM(A4)`, `=SUMIF(B1:B5, "north", C1:C5)`, `=SUMIF(A1:A7, ">15")`,45 `=SUMIF(A1:A7, "<>10")`, `=SUMIF(B1:B5, "n*", C1)`, `=SUMIF(C1:C7, 3)`, `=SUMIF(C1:C7, ">=" & C5)`,46 `=SUMIFS(C1:C5, B1:B5, "north", A1:A5, ">10")`, `=SUMPRODUCT(A1:A3, C1:C3)`, `=SUMPRODUCT(A1:A4, C1:C4)`,47 `=PRODUCT(C1:C5)`, `=PRODUCT(2, "3")`, `=POWER(2, 10)`, `=POWER(0, -1)`,48 `=ROUND(1.005, 2)`, `=ROUND(-2.5)`, `=ROUND(1234.5, -2)`, `=ROUND(2.675, 2)`,49 `=ROUNDUP(2.3, 1)`, `=ROUNDUP(-2.31, 1)`, `=ROUNDUP(1234, -2)`, `=ROUNDDOWN(2.39, 1)`, `=ROUNDDOWN(-2.39, 0)`,50 `=TRUNC(-7.9)`, `=CEILING(2.1, 1)`, `=CEILING(7, 5)`, `=CEILING(0.3, 0.1)`, `=CEILING(-2.5, 1)`,51 `=CEILING(-2.5, -1)`, `=CEILING(2.5, -1)`, `=FLOOR(2.9, 1)`, `=FLOOR(7, 5)`, `=FLOOR(-2.5, 1)`, `=FLOOR(-2.5, -1)`,52 `=SIGN(-3)`, `=SIGN(0)`, `=EXP(1)`, `=LN(EXP(2))`, `=LN(0)`, `=LOG(8, 2)`, `=LOG(1000)`, `=LOG(-1)`,53 `=LOG10(0.01)`, `=QUOTIENT(-7, 2)`, `=QUOTIENT(1, 0)`, `=RANDBETWEEN(3, 3)`, `=MOD(-7, 3)`, `=INT(-1.5)`,54 `=ABS(-2)`, `=SQRT(16)`, `=SQRT(-1)`, `=10%`, `=A1*50%`, `=-2^2`, `=2^-1`, `=SUM(C1:C2, E6)`,55 // Statistics56 `=AVERAGE(A1:A7)`, `=COUNT(A1:A7)`, `=COUNTA(A1:A7)`, `=MIN(A1:A7)`, `=MAX(A1:A7)`,57 `=AVERAGEIF(B1:B5, "north", C1:C5)`, `=AVERAGEIF(A1:A7, ">0")`, `=AVERAGEIF(A1:A7, ">100")`,58 `=AVERAGEIFS(C1:C5, B1:B5, "north", C1:C5, ">1")`, `=COUNTIF(B1:B7, "north")`, `=COUNTIF(B1:B7, "NORTH")`,59 `=COUNTIF(B1:B7, "?o*")`, `=COUNTIF(B1:B7, "Ab~*d")`, `=COUNTIF(A1:A7, "<>10")`, `=COUNTIF(A1:A7, "")`,60 `=COUNTIF(A1:A7, "<>")`, `=COUNTIF(E1:E7, TRUE)`, `=COUNTIF(A1:A7, ">=10")`, `=COUNTIF(B1:B7, ">m")`,61 `=COUNTIFS(B1:B5, "north", C1:C5, "<5")`, `=COUNTBLANK(A1:A7)`, `=COUNTBLANK(B1:B7)`,62 `=MEDIAN(A1:A7)`, `=MEDIAN(C1:C5)`, `=MODE(G1:G4)`, `=MODE(C1:C5)`, `=STDEV(A1:A7)`, `=STDEVP(A1:A7)`,63 `=VAR(A1:A7)`, `=VARP(A1:A7)`, `=STDEV(5)`, `=LARGE(A1:A7, 1)`, `=LARGE(A1:A7, 4)`, `=LARGE(A1:A7, 5)`,64 `=SMALL(C1:C7, 2)`, `=RANK(20, A1:A7)`, `=RANK(20, A1:A7, 1)`, `=RANK(2, G1:G4)`, `=RANK(99, A1:A7)`,65 `=RANK.EQ(20, A1:A7)`, `=MODE.SNGL(G1:G4)`, `=PERCENTILE(A1:A7, 0.25)`, `=PERCENTILE.INC(A1:A7, 0.9)`,66 `=PERCENTILE(A1:A7, 1.5)`, `=PERCENTILE.EXC(C1:C5, 0.25)`, `=PERCENTILE.EXC(C1:C5, 0.5)`,67 `=QUARTILE(C1:C5, 1)`, `=QUARTILE(A1:A7, 3)`, `=QUARTILE.INC(C1:C5, 4)`, `=QUARTILE.EXC(C1:C5, 3)`,68 `=QUARTILE.EXC(C1:C5, 0)`,69 // Text70 `=CONCATENATE("a", 1, TRUE)`, `=CONCAT(A1, "x")`, `=TEXTJOIN(", ", TRUE, A3:A5)`, `=TEXTJOIN("-", FALSE, A3:A5)`,71 `=LEFT("hello", 2)`, `=LEFT("hello")`, `=LEFT("hi", 10)`, `=LEFT("hi", -1)`, `=RIGHT("hello", 3)`,72 `=MID("spreadsheet", 7, 5)`, `=MID("abc", 5, 2)`, `=MID("abc", 0, 2)`, `=LEN("hello")`, `=LEN(A1)`,73 `=UPPER("abc")`, `=LOWER("ABC")`, `=PROPER("hello wORLD it's")`, `=TRIM(" a b ")`,74 `=SUBSTITUTE("a-b-c", "-", "+")`, `=SUBSTITUTE("a-b-c", "-", "+", 2)`, `=SUBSTITUTE("a-b-c", "-", "+", 5)`,75 `=REPLACE("abcdef", 2, 3, "X")`, `=REPLACE("abc", 10, 1, "X")`, `=FIND("b", "abcb")`, `=FIND("b", "abcb", 3)`,76 `=FIND("B", "abc")`, `=SEARCH("B", "abc")`, `=SEARCH("b?d", "abcbxd")`, `=SEARCH("z", "abc")`,77 `=TEXT(1234.567, "$#,##0.00")`, `=TEXT(0.256, "0.0%")`, `=TEXT(F1, "yyyy-mm-dd")`, `=TEXT(F1, "dddd")`,78 `=TEXT(F3, "h:mm AM/PM")`, `=TEXT(1234.5, "0.00E+00")`, `=TEXT(-1234.5, "#,##0.00;(#,##0.00)")`,79 `=TEXT(F1, "mmm d, yyyy")`, `=TEXT(1.5, "[h]:mm")`, `=TEXT(5, "000")`,80 `=VALUE("12%")`, `=VALUE("abc")`, `=VALUE(A1)`, `=REPT("ab", 3)`, `=REPT("ab", -1)`,81 `=EXACT("a", "A")`, `=EXACT("a", "a")`, `=A1&"x"`, `=1/3&""`,82 // Logic83 `=IF(A1>5, "big", "small")`, `=IF(A1>50, 1)`, `=IFERROR(1/0, 0)`, `=AND(A1>5, A2<10)`, `=OR(E6, FALSE)`, `=NOT(A1=10)`,84 `=IFS(A1>20, "big", A1>5, "medium")`, `=IFS(A1>20, "big")`, `=SWITCH(B2, "north", 1, "south", 2)`,85 `=SWITCH(B4, "north", 1, "south", 2, 0)`, `=SWITCH(B4, "north", 1)`, `=XOR(TRUE, FALSE)`, `=XOR(TRUE, TRUE)`,86 `=XOR(A1:A3)`, `=IFNA(NA(), "none")`, `=IFNA(1/0, 0)`, `=ISBLANK(A5)`, `=ISBLANK(B6)`, `=ISNUMBER(A1)`,87 `=ISNUMBER(A4)`, `=ISTEXT(A4)`, `=ISLOGICAL(E6)`, `=ISERROR(1/0)`, `=ISERR(NA())`, `=ISNA(NA())`, `=ISNA(1/0)`,88 // Lookup89 `=VLOOKUP("banana", D1:E3, 2, FALSE)`, `=VLOOKUP("B*", D1:E3, 2, FALSE)`, `=VLOOKUP("kiwi", D1:E3, 2, FALSE)`,90 `=VLOOKUP("Apple", D1:E5, 2, FALSE)`, `=VLOOKUP("Blueberry", D1:E3, 2)`, `=VLOOKUP("Aardvark", D1:E3, 2, TRUE)`,91 `=VLOOKUP(2.5, C1:D5, 2)`, `=VLOOKUP("Apple", D1:E3, 3, FALSE)`, `=HLOOKUP(1.5, C1:E2, 2, FALSE)`,92 `=HLOOKUP("Apple", D1:E2, 2, FALSE)`, `=MATCH("Cherry", D1:D3, 0)`, `=MATCH(3.2, C1:C7)`, `=MATCH(0, C1:C7)`,93 `=MATCH(25, I1:I3, -1)`, `=MATCH(10, I1:I3, -1)`, `=MATCH(35, I1:I3, -1)`, `=INDEX(D1:E3, 2, 2)`,94 `=INDEX(D1:D3, 3)`, `=INDEX(C1:E1, 2)`, `=INDEX(D1:E3, 4, 1)`, `=XLOOKUP("Cherry", D1:D3, E1:E3)`,95 `=XLOOKUP("kiwi", D1:D3, E1:E3)`, `=XLOOKUP("kiwi", D1:D3, E1:E3, "none")`, `=XLOOKUP("Apple", D1:D5, E1:E5, , 0, -1)`,96 `=XLOOKUP(3.2, C1:C7, D1:D7, , -1)`, `=XLOOKUP(3.2, C1:C7, D1:D7, , 1)`, `=XLOOKUP("Ch*", D1:D3, E1:E3, , 2)`,97 `=CHOOSE(2, "a", "b", "c")`, `=CHOOSE(2.9, "a", "b", "c")`, `=CHOOSE(4, "a", "b", "c")`, `=ROWS(A1:C7)`, `=COLUMNS(A1:C7)`,98 // Dates99 `=DATE(2026, 9, 26)`, `=DATE(2026, 14, 1)`, `=DATE(2026, 1, 0)`, `=DATE(26, 1, 1)`, `=DATE(-1, 1, 1)`,100 `=TIME(14, 30, 0)`, `=TIME(25, 0, 0)`, `=YEAR(F1)`, `=MONTH(F1)`, `=DAY(F1)`, `=YEAR("2026-09-26")`,101 `=WEEKDAY(F1)`, `=WEEKDAY(F1, 2)`, `=WEEKDAY(F1, 3)`, `=HOUR(F3)`, `=MINUTE(F3)`, `=SECOND(TIME(1, 2, 3))`,102 `=EDATE(F2, 1)`, `=EDATE(F1, -12)`, `=EOMONTH(F1, 0)`, `=EOMONTH(F4, 12)`, `=DATEDIF(F4, F1, "Y")`,103 `=DATEDIF(F4, F1, "M")`, `=DATEDIF(F4, F1, "D")`, `=DATEDIF(F4, F1, "YM")`, `=DATEDIF(F5, F1, "MD")`,104 `=DATEDIF(F5, F1, "YD")`, `=DATEDIF(F2, F5, "D")`, `=DAYS(F1, F2)`, `=DAYS("2026-01-02", "2026-01-01")`,105 `=NETWORKDAYS(F5, F2)`, `=NETWORKDAYS(F2, F5)`, `=NETWORKDAYS(F5, F2, F5)`, `=DATEVALUE("9/26/2026")`,106 `=DATEVALUE("hello")`, `=TIMEVALUE("2:30 PM")`, `=F1+1`, `="2026-09-27"-F1`,107 // Finance108 `=PMT(0.05/12, 360, 200000)`, `=PMT(0, 10, 1000)`, `=PV(0.05/12, 360, -1000)`, `=PV(0, 10, -100)`,109 `=FV(0.05, 10, -100)`, `=FV(0.05, 10, -100, 0, 1)`, `=FV(0, 10, -100, -500)`, `=NPER(0.01, -1000, 10000)`,110 `=NPER(0, -100, 1000)`, `=NPER(0.01, 100, 10000)`, `=RATE(360, -1073.6432460242795, 200000)`,111 `=RATE(48, -200, 8000)`, `=NPV(0.1, H1:H4)`, `=IRR(H1:H4)`, `=IRR(C1:C5)`,112}113114// skipped are formulas where Google Sheets, which 012 follows, and115// Excel disagree, or where excelize departs from Excel. Each says why.116var skipped = map[string]string{117 // Sheets and Excel disagree; 012 follows Sheets.118 `=ROUND(-2.5)`: "Sheets makes ROUND's places optional; Excel requires them",119 `=MID("abc", 0, 2)`: "Sheets gives #NUM! for a start before the text; Excel gives #VALUE!",120 `=INDEX(D1:E3, 4, 1)`: "Sheets gives #NUM! for a row past the range; Excel gives #REF!",121 `=CHOOSE(4, "a", "b", "c")`: "Sheets gives #NUM! for an index past the choices; Excel gives #VALUE!",122123 // excelize departs from Excel (and Sheets) here.124 `=PERCENTILE(A1:A7, 1.5)`: "excelize gives #N/A for a percentile past 1; Excel and Sheets give #NUM!",125 `=SUM(C1:C2, E6)`: "excelize counts TRUE in a referenced cell as 1; Excel and Sheets ignore it",126 `=SUMIF(B1:B5, "n*", C1)`: "excelize doesn't stretch a smaller sum range from its corner as Excel does",127 `=SUMPRODUCT(A1:A4, C1:C4)`: "excelize gives #VALUE! for text; Excel and Sheets treat it as 0",128 `=CEILING(2.5, -1)`: "excelize gives #VALUE!; Excel and Sheets give #NUM!",129 `=LN(0)`: "excelize gives -INF; Excel and Sheets give #NUM!",130 `=COUNTIF(A1:A7, "")`: "excelize doesn't count blank cells for an empty criterion",131 `=COUNTIF(A1:A7, "<>10")`: "excelize doesn't count blank cells as not equal",132 `=COUNTBLANK(B1:B7)`: "excelize doesn't count empty text from a formula as blank",133 `=TRIM(" a b ")`: "excelize doesn't collapse inner runs of spaces",134 `=1/3&""`: "excelize joins 16 significant digits; Excel and Sheets show 15",135 `=IF(A1>50, 1)`: "excelize gives empty text; Excel and Sheets give FALSE",136 `=IFNA(1/0, 0)`: "excelize turns other errors into #VALUE!; Excel and Sheets pass them through",137 `=VLOOKUP("Apple", D1:E3, 3, FALSE)`: "excelize gives #N/A for an index past the range; Excel and Sheets give #REF!",138 `=INDEX(C1:E1, 2)`: "excelize doesn't count along a single row",139 `=XLOOKUP("Apple", D1:D5, E1:E5, , 0, -1)`: "excelize misreads an omitted argument",140 `=XLOOKUP(3.2, C1:C7, D1:D7, , -1)`: "excelize misreads an omitted argument",141 `=XLOOKUP(3.2, C1:C7, D1:D7, , 1)`: "excelize misreads an omitted argument",142 `=XLOOKUP("Ch*", D1:D3, E1:E3, , 2)`: "excelize misreads an omitted argument",143 `=CHOOSE(2.9, "a", "b", "c")`: "excelize doesn't truncate the index",144 `=DATE(26, 1, 1)`: "excelize doesn't add 1900 to years below 1900",145 `=DATE(-1, 1, 1)`: "excelize accepts negative years; Excel and Sheets give #NUM!",146 `=TIME(25, 0, 0)`: "excelize doesn't wrap past midnight",147 `="2026-09-27"-F1`: "excelize doesn't read date text in arithmetic",148 `=NPV(0.1, H1:H4)`: "excelize reads only the first cell of a range",149}150151// toExcel translates a 012 formula to Excel syntax: 1-2-3 style @SUM152// and A1..B2 ranges become SUM and A1:B2, and the leading = goes.153func toExcel(f string) string {154 f = strings.TrimPrefix(f, "=")155 var b strings.Builder156 inStr := false157 for i := 0; i < len(f); i++ {158 c := f[i]159 switch {160 case c == '"':161 inStr = !inStr162 case inStr:163 case c == '@':164 continue165 case c == '.' && i+1 < len(f) && f[i+1] == '.':166 b.WriteByte(':')167 i++168 continue169 }170 b.WriteByte(c)171 }172 return b.String()173}174175func TestToExcel(t *testing.T) {176 for in, want := range map[string]string{177 "=@SUM(A1..B2)": "SUM(A1:B2)",178 `=IF(A1="@x..y",1)`: `IF(A1="@x..y",1)`,179 "=A1*2": "A1*2",180 "=XLOOKUP(1,A:A,B:B)": "XLOOKUP(1,A:A,B:B)",181 } {182 if got := toExcel(in); got != want {183 t.Errorf("toExcel(%q) = %q, want %q", in, got, want)184 }185 }186}187188// build loads the fixture into 012, then copies the values 012189// computed into an excelize workbook, so both start from the same data.190func build(t *testing.T) (*sheet.Sheet, *excelize.File) {191 t.Helper()192 s := sheet.New()193 for a, in := range fixture {194 if err := s.Set(addr(a), in); err != nil {195 t.Fatalf("%s: %v", a, err)196 }197 }198 x := excelize.NewFile()199 for a, in := range fixture {200 var err error201 switch v := s.Value(addr(a)); {202 case sheet.IsFormulaEntry(in):203 err = x.SetCellFormula("Sheet1", a, toExcel(in))204 case v.Kind == sheet.Number:205 err = x.SetCellValue("Sheet1", a, v.Num)206 case v.Kind == sheet.Bool:207 err = x.SetCellValue("Sheet1", a, v.Num != 0)208 default:209 err = x.SetCellValue("Sheet1", a, v.Str)210 }211 if err != nil {212 t.Fatal(err)213 }214 }215 return s, x216}217218func addr(s string) sheet.Addr {219 a, ok := sheet.ParseAddr(s)220 if !ok {221 panic(s)222 }223 return a224}225226func TestAgainstExcelize(t *testing.T) {227 s, x := build(t)228 const cell = "Z1"229 matched := 0230 for _, f := range formulas {231 if why, ok := skipped[f]; ok {232 t.Logf("skip %s: %s", f, why)233 continue234 }235 if err := s.Set(addr(cell), f); err != nil {236 t.Errorf("012 rejects %s: %v", f, err)237 continue238 }239 ours := s.Value(addr(cell))240 if err := x.SetCellFormula("Sheet1", cell, toExcel(f)); err != nil {241 t.Fatal(err)242 }243 theirs, err := x.CalcCellValue("Sheet1", cell, excelize.Options{RawCellValue: true})244 if err != nil && !strings.HasPrefix(theirs, "#") {245 theirs = err.Error()246 }247 if agree(ours, theirs) {248 matched++249 continue250 }251 t.Errorf("%s: 012 %s, excelize %q", f, show(ours), theirs)252 }253 t.Logf("%d of %d formulas match excelize, %d skipped", matched, len(formulas)-len(skipped), len(skipped))254}255256// agree compares a 012 value with excelize's text result: numbers to257// about 12 significant digits, booleans as TRUE/FALSE, errors by code.258func agree(v sheet.Value, x string) bool {259 switch v.Kind {260 case sheet.Number:261 n, err := strconv.ParseFloat(x, 64)262 if err != nil {263 return false264 }265 return math.Abs(n-v.Num) <= 1e-9*math.Max(1, math.Abs(v.Num))266 case sheet.Bool:267 return strings.EqualFold(x, v.String())268 case sheet.Empty:269 return x == "" || x == "0"270 }271 return x == v.Str272}273274func show(v sheet.Value) string {275 switch v.Kind {276 case sheet.Text:277 return strconv.Quote(v.Str)278 case sheet.Empty:279 return "blank"280 }281 return v.String()282}