SPB Git

Repository Public

generate_function_fixtures.py 30.9 KB 479 lines python

1#!/usr/bin/env python32"""Generates Fixtures/function_references.json for the FunctionsText tests.34Sources of truth (independent of the Swift implementation):5  * "lo"  — the formula recalculated by headless LibreOffice;6  * "py"  — a Python reference (datetime / dateutil / re / str) for functions LibreOffice lacks, and as a7            cross-check of LibreOffice for the date functions (the generator aborts when the two disagree);8  * "excel" — LibreOffice deviates from Excel's documented behaviour: the Excel value is stored, the9            LibreOffice value is kept in "lo" with a "note" explaining the deviation.1011Usage: python3 generate_function_fixtures.py   (needs openpyxl, python-dateutil, /opt/homebrew/bin/soffice)12"""13import datetime as dt14import json15import os16import re17import sys1819from dateutil.relativedelta import relativedelta2021sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))22from lo_recalc import recalc  # noqa: E4022324CASES = []   # (function, formula, python_expected or None)25PYONLY = []  # (function, formula, expected)  — no LibreOffice implementation262728def q(s):29    return '"' + s.replace('"', '""') + '"'303132def add(fn, *formulas):33    for f in formulas:34        CASES.append((fn, f, None))353637def addpy(fn, formula, expected):38    CASES.append((fn, formula, expected))394041# ---------------------------------------------------------------- text42STR = ["abc", "Hello World", "", "héllo wörld", "a,b,c", "The quick brown fox", "  padded  text ", "ABC-123", "x"]43for s in STR:44    for n in (None, 0, 1, 3, 10):45        tail = "" if n is None else f",{n}"46        add("LEFT", f"LEFT({q(s)}{tail})")47        add("RIGHT", f"RIGHT({q(s)}{tail})")48        add("LEFTB", f"LEFTB({q(s)}{tail})")49        add("RIGHTB", f"RIGHTB({q(s)}{tail})")50    for st, n in [(1, 1), (2, 3), (1, 100), (4, 0), (10, 2), (3, 2)]:51        add("MID", f"MID({q(s)},{st},{n})")52        add("MIDB", f"MIDB({q(s)},{st},{n})")53    add("LEN", f"LEN({q(s)})")54    add("LENB", f"LENB({q(s)})")55    add("LOWER", f"LOWER({q(s)})")56    add("UPPER", f"UPPER({q(s)})")57    add("PROPER", f"PROPER({q(s)})")58    add("TRIM", f"TRIM({q(s)})")59    add("CLEAN", f"CLEAN({q(s)})")60    add("T", f"T({q(s)})")61add("LEFT", 'LEFT("abc",-1)', 'LEFT("abc",1.9)', "LEFT(12345,2)", "LEFT(TRUE,2)", "LEFT(1/4,3)")62add("RIGHT", 'RIGHT("abc",-1)', 'RIGHT("abc",2.9)', "RIGHT(12345,2)", "RIGHT(FALSE,3)")63add("MID", 'MID("abc",0,1)', 'MID("abc",1,-1)', "MID(12345,2,3)", 'MID("abc",1.9,1.9)', 'MID("abc",-1,1)')64add("LEN", "LEN(123.45)", "LEN(TRUE)", "LEN(1/3)", "LEN(-5)", "LEN(1E+20)")65add("PROPER", 'PROPER("o\'neil mc-donald 2nd 3D")', 'PROPER("JOHN SMITH")', 'PROPER("ça va élan")', 'PROPER("hello_world foo.bar")', "PROPER(TRUE)")66add("UPPER", "UPPER(TRUE)", "UPPER(1.5)")67add("LOWER", "LOWER(TRUE)", 'LOWER("ÀÉÎ")')68add("TRIM", 'TRIM("  a   b  ")', 'TRIM("a")', 'TRIM("   ")', 'TRIM(" a"&CHAR(9)&" b ")', "TRIM(5)", 'TRIM("a  b   c    d")')69add("CLEAN", 'CLEAN("a"&CHAR(7)&"b"&CHAR(31)&"c")', 'CLEAN(CHAR(10)&"x"&CHAR(13))', 'CLEAN("a"&CHAR(1)&CHAR(2)&"b")', 'LEN(CLEAN("a"&CHAR(127)&"b"))')70add("REPT", 'REPT("ab",3)', 'REPT("x",0)', 'REPT("ab",2.9)', 'REPT("x",-1)', 'REPT("",5)', "REPT(1,3)", 'LEN(REPT("abc",1000))')71add("EXACT", 'EXACT("a","A")', 'EXACT("abc","abc")', 'EXACT("","")', "EXACT(1,1)", 'EXACT(1,"1")', 'EXACT("a ","a")', "EXACT(TRUE,\"TRUE\")")72add("FIND", 'FIND("b","abcabc")', 'FIND("b","abcabc",3)', 'FIND("B","abc")', 'FIND("","abc")', 'FIND("","abc",2)', 'FIND("c","abc",4)',73    'FIND("abc","ab")', 'FIND("a","abc",0)', 'FIND("o","Hello World",6)', 'FIND("World","Hello World")', 'FIND("?","a?b")', 'FIND("*","a*b")',74    "FIND(3,12345)", 'FIND("c","abc",3)', 'FIND("ö","héllo wörld")', 'FIND("a","")', 'FIND("b","abcabc",2.9)')75add("SEARCH", 'SEARCH("B","abc")', 'SEARCH("b?d","abcd abd")', 'SEARCH("b*d","abcd")', 'SEARCH("~?","what?")', 'SEARCH("~*","a*b")',76    'SEARCH("o","Hello World",6)', 'SEARCH("z","abc")', 'SEARCH("","abc")', 'SEARCH("?","abc",2)', 'SEARCH("a*c","xxabxxc")',77    'SEARCH("World","hello world")', 'SEARCH("c","abc",4)', 'SEARCH("a","abc",0)', 'SEARCH("?c","abc")', 'SEARCH("*","abc")',78    'SEARCH("A","banana",3)', 'SEARCH("n?n","banana")', 'SEARCH("~~","a~b")', "SEARCH(3,12345)", 'SEARCH("É","héllo")')79add("SUBSTITUTE", 'SUBSTITUTE("aaa","a","b")', 'SUBSTITUTE("aaa","a","b",2)', 'SUBSTITUTE("abcabc","bc","X")', 'SUBSTITUTE("abc","","x")',80    'SUBSTITUTE("abc","d","x")', 'SUBSTITUTE("aaa","a","b",4)', 'SUBSTITUTE("aaa","a","b",0)', 'SUBSTITUTE("Hello","l","L",1)',81    'SUBSTITUTE("a.b.c",".","")', 'SUBSTITUTE("abc","B","x")', 'SUBSTITUTE("aaaa","aa","b")', 'SUBSTITUTE("aaaa","aa","b",2)',82    "SUBSTITUTE(12345,3,\"x\")", 'SUBSTITUTE("abc","abc","")', 'SUBSTITUTE("a b c"," ","  ")', 'SUBSTITUTE("aaa","a","b",1.9)', 'SUBSTITUTE("","a","b")')83add("REPLACE", 'REPLACE("abcdef",2,3,"XY")', 'REPLACE("abc",1,0,"x")', 'REPLACE("abc",5,1,"x")', 'REPLACE("abc",0,1,"x")',84    'REPLACE("abc",1,-1,"x")', 'REPLACE("abc",2,100,"")', 'REPLACE("abc",4,0,"d")', "REPLACE(12345,2,2,\"x\")", 'REPLACE("abc",1,3,"")',85    'REPLACE("abc",2.9,1.9,"Z")', 'REPLACE("",1,0,"x")')86add("CHAR", "CHAR(65)", "CHAR(97)", "CHAR(32)", "LEN(CHAR(10))", "CHAR(126)", "CHAR(0)", "CHAR(256)", "CHAR(65.9)", "CHAR(-1)", "CODE(CHAR(9))")87add("CODE", 'CODE("A")', 'CODE("abc")', 'CODE("")', 'CODE(" ")', "CODE(5)", "CODE(TRUE)", 'CODE("~")')88add("UNICHAR", "UNICHAR(65)", "UNICHAR(960)", "UNICHAR(8364)", "UNICHAR(128512)", "UNICHAR(0)", "UNICHAR(233)", "LEN(UNICHAR(128512))", "UNICHAR(-5)")89add("UNICODE", 'UNICODE("A")', 'UNICODE("π")', 'UNICODE("€")', 'UNICODE("😀")', 'UNICODE("")', 'UNICODE("é")', 'UNICODE("abc")')90add("T", "T(5)", "T(TRUE)", "T(1/0)")91add("CONCAT", 'CONCAT("a","b","c")', 'CONCAT("a",1,TRUE)', 'CONCAT({"a","b";"c","d"})', 'CONCAT("x",{1,2,3})', 'CONCAT("")', "CONCAT(1.5,2.5)", 'CONCAT("a",1/0)')92add("CONCATENATE", 'CONCATENATE("a","b",1)', 'CONCATENATE("x")', 'CONCATENATE("a",TRUE,"")', "CONCATENATE(1/3)")93add("TEXTJOIN", 'TEXTJOIN(",",TRUE,"a","","b")', 'TEXTJOIN(",",FALSE,"a","","b")', 'TEXTJOIN("-",TRUE,{"a","b";"c","d"})', 'TEXTJOIN("",TRUE,"a","b")',94    'TEXTJOIN(", ",TRUE,1,2,3)', 'TEXTJOIN({"-","+"},TRUE,"a","b","c","d")', 'TEXTJOIN(",",TRUE,"")', 'TEXTJOIN(",",TRUE,{"a","";"","d"})',95    'TEXTJOIN(",",FALSE,{"a","";"","d"})', 'TEXTJOIN(";",1,"x",TRUE,2.5)', 'TEXTJOIN(",",TRUE,"a",1/0)')96add("TEXT", 'TEXT(1234.567,"#,##0.00")', 'TEXT(0.25,"0%")', 'TEXT(45000,"yyyy-mm-dd")', 'TEXT(45000.5,"h:mm AM/PM")', 'TEXT("abc","0.00")',97    'TEXT(-5,"0;(0)")', 'TEXT(1234.5,"0.00E+00")', 'TEXT(0.5,"# ?/?")', 'TEXT("3/15/2023","yyyy-mm-dd")', 'TEXT("12:30","h:mm:ss")',98    'TEXT(TRUE,"0")', 'TEXT(1/3,"General")', 'TEXT(12,"000")', 'TEXT(45000,"dddd, mmmm d, yyyy")', 'TEXT(0.285,"0.00")', 'TEXT(1.005,"0.00")',99    'TEXT(1234567,"#,##0,")', 'TEXT(5,"0.0 \\x")', 'TEXT(45000.75,"m/d/yyyy h:mm")', 'TEXT(1.5,"[h]:mm")', 'TEXT("1,234.5","0")', 'TEXT(-1234.5,"$#,##0.00")',100    'TEXT(0,"0.00;-0.00;\\z")', 'TEXT(45351,"ddd d mmm yy")', 'TEXT(1234.5,"General")', 'TEXT(-2.5,"General")')101add("VALUE", 'VALUE("123")', 'VALUE(" 12.5 ")', 'VALUE("1,234.5")', 'VALUE("$5")', 'VALUE("50%")', 'VALUE("1e3")', 'VALUE("-7")', 'VALUE("(5)")',102    'VALUE("abc")', 'VALUE("")', 'VALUE("3/15/2023")', 'VALUE("2023-03-15")', 'VALUE("12:00")', 'VALUE("1:30 PM")', 'VALUE("March 15, 2023")',103    'VALUE("15-Mar-2023")', 'VALUE("2023-03-15 18:00")', 'VALUE("1.2.3")', 'VALUE("12:30:45")', "VALUE(5)", 'VALUE("1.5E-3")', 'VALUE("+3")',104    'VALUE("12 abc")', 'VALUE("0.5")', 'VALUE(".5")', 'VALUE("5.")')105add("NUMBERVALUE", 'NUMBERVALUE("1.234,5",",",".")', 'NUMBERVALUE("1 234,5",","," ")', 'NUMBERVALUE("50%",".",",")', 'NUMBERVALUE("5%%",".",",")', 'NUMBERVALUE("",".",",")',106    'NUMBERVALUE("1,5",",",".")', 'NUMBERVALUE("1,234.5",".",",")', 'NUMBERVALUE("1.234,5",",",",")', 'NUMBERVALUE("abc",".",",")', 'NUMBERVALUE(" 12 3 ",".",",")',107    'NUMBERVALUE("3,5,2",",",".")', 'NUMBERVALUE("1.2.3,5",",",".")', 'NUMBERVALUE("-1,5",",",".")', 'NUMBERVALUE("1,5.2",",",".")', 'NUMBERVALUE("2.5",".",",")',108    'NUMBERVALUE("1e3",".",",")', 'NUMBERVALUE("12,5%",",",".")')109add("FIXED", "FIXED(1234.567)", "FIXED(1234.567,1)", "FIXED(1234.567,-2)", "FIXED(1234.567,0)", "FIXED(1234.567,2,TRUE)", "FIXED(-1234.567,1)",110    "FIXED(0.285,2)", "FIXED(1.005,2)", "FIXED(0,2)", "FIXED(1234567.891,3,FALSE)", "FIXED(999.995,2)", "FIXED(1234.567,-1,TRUE)", "FIXED(44.332)",111    "FIXED(1234.5,1.9)", "FIXED(-0.5,0)", "FIXED(2.5,0)", "FIXED(1234567,-3)", "FIXED(0.5,5)", "FIXED(-1234567.891,2,1)")112add("DOLLAR", "DOLLAR(1234.567)", "DOLLAR(1234.567,1)", "DOLLAR(-1234.567)", "DOLLAR(0.5,0)", "DOLLAR(1234.567,-2)", "DOLLAR(0)", "DOLLAR(99.999)",113    "DOLLAR(1234567.891,3)", "DOLLAR(-0.5,0)", "DOLLAR(12,0)")114for name in ("TEXTBEFORE", "TEXTAFTER"):115    add(name, *[f"{name}({a})" for a in [116        '"a-b-c","-"', '"a-b-c","-",2', '"a-b-c","-",-1', '"a-b-c","-",-2', '"a-b-c","x"', '"a-b-c","x",1,0,1', '"a-b-c","-",3,0,1',117        '"a-b-c","-",3', '"Hello World","WORLD",1,1', '"Hello World","WORLD",1,0', '"a-b-c","x",,,,"none"', '"a-b,c",{"-",","},2',118        '"a-b-c","-",0', '"a--b","--"', '"abc","c"', '"abc","a"', '" a b "," ",2', '"a-b-c","-",-3,0,1', '"a-b-c","-",-3', '"one two three"," "',119        '"one two three"," ",2', '"one two three"," ",-1', '"k=v=w","="', '"k=v=w","=",-1', '"abcABCabc","B",1,1', '"abcABCabc","B",2,1',120        '"abcABCabc","B",1,0', '12345,3', '"a-b-c","-",4,0,1', '"a-b-c","-",1,0,1', '"a-b-c","x",-1,0,1']])121SPLITS = [('"a,b;c,d",",",";"', 2, 2), ('"a,b,,c",","', 1, 4), ('"a,b,,c",",",,TRUE', 1, 3), ('"a,b;c",",",";"', 2, 2), ('"a,b;c",",",";",FALSE,0,"x"', 2, 2),122          ('"aXbxc","x",,,1', 1, 3), ('"aXbxc","x"', 1, 2), ('"a,b;c",{",",";"}', 1, 3), ('"a;b",,";"', 2, 1), ('"hello"," "', 1, 1),123          ('"a--b-c","-",,TRUE', 1, 3), ('"1 2 3;4 5 6"," ",";"', 2, 3), ('"a==b=c","=="', 1, 2), ('"x,y|z",",","|",,,""', 2, 2)]124for args, rows, cols in SPLITS:125    add("TEXTSPLIT", f"ROWS(TEXTSPLIT({args}))", f"COLUMNS(TEXTSPLIT({args}))")126    for r in range(1, rows + 1):127        for c in range(1, cols + 1):128            add("TEXTSPLIT", f"INDEX(TEXTSPLIT({args}),{r},{c})")129add("TEXTSPLIT", 'TEXTSPLIT("abc","")', 'TEXTSPLIT("only",",")')130add("ENCODEURL", 'ENCODEURL("a b")', 'ENCODEURL("abc-_XYZ019")', 'ENCODEURL("100%")', 'ENCODEURL("a/b?c=d&e=f")', 'ENCODEURL("é€")',131    'ENCODEURL("http://contoso.sharepoint.com/Finance/Profit and Loss Statement.xlsx")', 'ENCODEURL("a+b=c:d;e,f@g")')132133# ---------------------------------------------------------------- dates134EPOCH = dt.date(1899, 12, 30)135136137def d2s(d):138    return (d - EPOCH).days139140141def s2d(s):142    return EPOCH + dt.timedelta(days=int(s))143144145D = [426, 731, 1462, 25569, 36526, 39448, 40179, 43100, 43889, 43890, 43891, 44196, 44197, 44255, 44561, 44562, 44926, 44927, 45000, 45291,146     45292, 45351, 45352, 45657, 45658, 46022, 50000, 73050, 109205]147148149def py_weekday(s, t):150    wd = s2d(s).isoweekday() % 7 + 1   # 1 = Sunday151    if t == 3:152        return (wd + 5) % 7153    start = {1: 1, 17: 1, 2: 2, 11: 2, 12: 3, 13: 4, 14: 5, 15: 6, 16: 7}[t]154    return (wd - start + 7) % 7 + 1155156157def py_weeknum(s, t):158    d = s2d(s)159    if t == 21:160        return d.isocalendar()[1]161    start = {1: 1, 17: 1, 2: 2, 11: 2, 12: 3, 13: 4, 14: 5, 15: 6, 16: 7}[t]162    jan1 = dt.date(d.year, 1, 1)163    offset = ((jan1.isoweekday() % 7 + 1) - start + 7) % 7164    return ((d - jan1).days + offset) // 7 + 1165166167for s in D:168    d = s2d(s)169    addpy("YEAR", f"YEAR({s})", d.year)170    addpy("MONTH", f"MONTH({s})", d.month)171    addpy("DAY", f"DAY({s})", d.day)172    addpy("ISOWEEKNUM", f"ISOWEEKNUM({s})", d.isocalendar()[1])173    addpy("WEEKDAY", f"WEEKDAY({s})", py_weekday(s, 1))174    for t in (1, 2, 3, 11, 12, 13, 14, 15, 16, 17):175        addpy("WEEKDAY", f"WEEKDAY({s},{t})", py_weekday(s, t))176    addpy("WEEKNUM", f"WEEKNUM({s})", py_weeknum(s, 1))177    for t in (1, 2, 11, 12, 13, 14, 15, 16, 17, 21):178        addpy("WEEKNUM", f"WEEKNUM({s},{t})", py_weeknum(s, t))179for y in range(2018, 2028):180    for d in [dt.date(y, 1, k) for k in range(1, 8)] + [dt.date(y, 12, k) for k in range(25, 32)]:181        s = d2s(d)182        addpy("ISOWEEKNUM", f"ISOWEEKNUM({s})", d.isocalendar()[1])183        for t in (1, 2, 21):184            addpy("WEEKNUM", f"WEEKNUM({s},{t})", py_weeknum(s, t))185add("WEEKDAY", "WEEKDAY(45000,4)", "WEEKDAY(45000,0)", "WEEKDAY(-1)", "WEEKDAY(45000.9)", 'WEEKDAY("3/15/2023")', "WEEKDAY(45000,18)")186add("WEEKNUM", "WEEKNUM(45000,3)", "WEEKNUM(45000,20)", "WEEKNUM(-1)", 'WEEKNUM("2023-03-15")', "WEEKNUM(45000.99,2)")187add("YEAR", "YEAR(45000.99)", "YEAR(-1)", 'YEAR("3/15/2023")', 'YEAR("abc")', "YEAR(2958465)", "YEAR(2958466)")188add("MONTH", "MONTH(45000.99)", 'MONTH("15-Mar-2023")', "MONTH(-5)")189add("DAY", "DAY(45000.99)", 'DAY("2023-03-15")', "DAY(-5)")190TIMES = [0, 0.25, 0.5, 0.75, 0.999, 0.1234, 45000.6789, 0.0008, 0.3334, 0.99998, 0.0417, 12.5103, 0.7391, 44927.2535, 1]191for t in TIMES:192    secs = (t - int(t)) * 86400193    assert abs(secs - round(secs)) < 0.4, t194    total = round(secs) % 86400195    addpy("HOUR", f"HOUR({t!r})", total // 3600)196    addpy("MINUTE", f"MINUTE({t!r})", total // 60 % 60)197    addpy("SECOND", f"SECOND({t!r})", total % 60)198add("HOUR", 'HOUR("1:30 PM")', 'HOUR("13:45:10")', "HOUR(-0.5)", 'HOUR("abc")')199add("MINUTE", 'MINUTE("1:30 PM")', 'MINUTE("13:45:10")', "MINUTE(-0.5)")200add("SECOND", 'SECOND("13:45:10")', "SECOND(-0.5)")201add("DATE", "DATE(2023,3,15)", "DATE(2023,14,35)", "DATE(2023,0,0)", "DATE(2023,-3,-5)", "DATE(99,1,1)", "DATE(1900,3,1)", "DATE(2024,2,29)",202    "DATE(2023,2,29)", "DATE(9999,12,31)", "DATE(10000,1,1)", "DATE(2023,1.9,1.9)", "DATE(-1,1,1)", "DATE(2023,12,31)", "DATE(2023,25,1)",203    "DATE(2023,1,366)", "DATE(2000,2,29)", "DATE(2100,2,29)", "DATE(1950,6,15)", "DATE(2023,3,0)", "DATE(2023,-11,1)", "DATE(1900,12,31)", "DATE(0,3,1)")204add("TIME", "TIME(12,30,0)", "TIME(25,61,61)", "TIME(0,0,0)", "TIME(23,59,59)", "TIME(1,-30,0)", "TIME(-1,0,0)", "TIME(12.9,30.9,10.9)", "TIME(0,0,86399)",205    "TIME(24,0,0)", "TIME(6,0,0)", "TIME(0,90,0)", "TIME(0,0,3661)", "TIME(32768,0,0)", "TIME(0,-1,0)", "TIME(1,0,-3600)")206add("DATEVALUE", *[f"DATEVALUE({q(s)})" for s in [207    "3/15/2023", "15-Mar-2023", "March 15, 2023", "2023-03-15", "2023/03/15", "15 March 2023", "Mar 15 2023", "3/15/23", "3/15/2023 13:30",208    "1/1/30", "1/1/29", "abc", "13/45/2023", "2/30/2023", "Mar 2023", "3-15-2023", "2/29/2024", "2/29/2023", "12/31/9999", "1 Jan 2000",209    "Jan 1, 2000", "2000-01-01 23:59", "September 5, 2021", "5-Sep-21", "Sept 5, 2021", ""]], "DATEVALUE(45000)")210add("TIMEVALUE", *[f"TIMEVALUE({q(s)})" for s in [211    "12:00", "1:30 PM", "13:30:45", "12:00 AM", "3/15/2023 6:00", "11:59:59 PM", "abc", "0:00", "12:00 PM", "6:15:30 AM", "23:59", "2023-03-15 18:45",212    "1:00:00.5", "25:00", ""]])213ENDS = [dt.date(2023, 1, 31), dt.date(2024, 1, 31), dt.date(2023, 3, 31), dt.date(2023, 5, 31), dt.date(2024, 2, 29), dt.date(2023, 2, 28),214        dt.date(2023, 12, 15), dt.date(2020, 2, 29), dt.date(2023, 8, 30), dt.date(1999, 12, 31)]215for d in ENDS + [s2d(s) for s in D[3:12]]:216    s = d2s(d)217    for m in (-25, -13, -12, -1, 0, 1, 2, 11, 12, 25):218        addpy("EDATE", f"EDATE({s},{m})", d2s(d + relativedelta(months=m)))219        addpy("EOMONTH", f"EOMONTH({s},{m})", d2s(d + relativedelta(months=m) + relativedelta(day=31)))220add("EDATE", "EDATE(45000,1.9)", "EDATE(45000,-1.9)", "EDATE(45000.7,1)", 'EDATE("1/31/2023",1)', "EDATE(-1,1)", "EDATE(45000,1000000)", "EDATE(100,-12)")221add("EOMONTH", "EOMONTH(45000,1.9)", "EOMONTH(45000,-1.9)", "EOMONTH(45000.7,0)", 'EOMONTH("2/1/2024",0)', "EOMONTH(-1,1)", "EOMONTH(100,-12)")222for a, b in [(45000, 44000), (44000, 45000), (45000, 45000), (61, 73050), (45292, 45657)]:223    addpy("DAYS", f"DAYS({a},{b})", a - b)224add("DAYS", 'DAYS("3/15/2023","1/1/2023")', 'DAYS("2023-12-31",45000)', 'DAYS("abc",1)')225L = [dt.date(2023, 1, 15), dt.date(2023, 1, 30), dt.date(2023, 1, 31), dt.date(2023, 2, 28), dt.date(2024, 2, 28), dt.date(2024, 2, 29),226     dt.date(2023, 3, 31), dt.date(2023, 4, 30), dt.date(2023, 12, 31), dt.date(2024, 1, 1), dt.date(2025, 6, 15), dt.date(2023, 8, 31),227     dt.date(2020, 2, 29), dt.date(2019, 7, 4)]228for a in L:229    for b in L:230        sa, sb = d2s(a), d2s(b)231        add("DAYS360", f"DAYS360({sa},{sb})", f"DAYS360({sa},{sb},TRUE)")232        if sa <= sb:233            for unit in ("Y", "M", "D", "MD", "YM", "YD"):234                add("DATEDIF", f'DATEDIF({sa},{sb},"{unit}")')235            add("YEARFRAC", f"YEARFRAC({sa},{sb})")236            for basis in range(5):237                add("YEARFRAC", f"YEARFRAC({sa},{sb},{basis})")238add("DAYS360", "DAYS360(45000.9,45100.1)", 'DAYS360("1/31/2023","3/1/2023")', "DAYS360(45000,45100,FALSE)", "DAYS360(-1,5)")239add("DATEDIF", 'DATEDIF(45000,44000,"D")', 'DATEDIF(44000,45000,"X")', 'DATEDIF(44000,45000,"y")', 'DATEDIF(44000.9,45000.1,"d")',240    'DATEDIF("1/1/2020","3/15/2023","Y")', 'DATEDIF(37043,37483,"YD")', 'DATEDIF(37043,37483,"MD")', 'DATEDIF(37043,37483,"YM")')241add("YEARFRAC", "YEARFRAC(45100,45000,1)", "YEARFRAC(45000,45100,5)", "YEARFRAC(45000,45100,-1)", "YEARFRAC(45000.9,45100.2,3)",242    'YEARFRAC("1/1/2023","7/1/2023",1)', "YEARFRAC(40179,45000,1)", "YEARFRAC(36526,45657,1)", "YEARFRAC(45000,45000)", "YEARFRAC(45351,45716,1)",243    "YEARFRAC(44985,45351,1)", "YEARFRAC(43890,44255,1)", "YEARFRAC(43889,44255,1)", "YEARFRAC(43890,44256,1)", "YEARFRAC(45000,45100,1.9)")244245HOL = "{45005,45006,45010,45047,45050,45100}"246WEEKENDS = [1, 2, 3, 4, 5, 6, 7, 11, 12, 13, 14, 15, 16, 17, '"0000011"', '"1000001"', '"0101010"', '"0000000"', '"1111110"']247248249def py_networkdays(a, b, holidays=()):250    lo, hi = min(a, b), max(a, b)251    n = sum(1 for s in range(lo, hi + 1) if s2d(s).isoweekday() < 6 and s not in holidays)252    return n if a <= b else -n253254255def py_workday(a, n, holidays=()):256    s, step, left = a, (1 if n > 0 else -1), abs(n)257    while left > 0:258        s += step259        if s2d(s).isoweekday() < 6 and s not in holidays:260            left -= 1261    return s262263264HOLSET = (45005, 45006, 45010, 45047, 45050, 45100)265for a, b in [(45000, 45100), (45100, 45000), (45003, 45004), (45003, 45003), (45000, 45000), (44927, 45291), (45000, 45006), (45005, 45005),266             (43831, 45657), (45002, 45011), (45004, 45005), (61, 1000)]:267    addpy("NETWORKDAYS", f"NETWORKDAYS({a},{b})", py_networkdays(a, b))268    addpy("NETWORKDAYS", f"NETWORKDAYS({a},{b},{HOL})", py_networkdays(a, b, HOLSET))269    for w in WEEKENDS:270        add("NETWORKDAYS.INTL", f"NETWORKDAYS.INTL({a},{b},{w})")271    add("NETWORKDAYS.INTL", f"NETWORKDAYS.INTL({a},{b})", f"NETWORKDAYS.INTL({a},{b},1,{HOL})", f'NETWORKDAYS.INTL({a},{b},"0010001",{HOL})',272        f"NETWORKDAYS.INTL({a},{b},11,{HOL})")273add("NETWORKDAYS", "NETWORKDAYS(45000.9,45100.2)", 'NETWORKDAYS("3/1/2023","3/31/2023")', "NETWORKDAYS(45000,45100,45005)",274    'NETWORKDAYS(45000,45100,{"3/20/2023","3/21/2023"})', "NETWORKDAYS(-1,5)", 'NETWORKDAYS(45000,45100,"abc")')275add("NETWORKDAYS.INTL", "NETWORKDAYS.INTL(45000,45100,8)", "NETWORKDAYS.INTL(45000,45100,0)", "NETWORKDAYS.INTL(45000,45100,18)",276    'NETWORKDAYS.INTL(45000,45100,"1111111")', 'NETWORKDAYS.INTL(45000,45100,"000001")', 'NETWORKDAYS.INTL(45000,45100,"00000x1")',277    'NETWORKDAYS.INTL(45000,45100,"00000111")')278for a in (45000, 45002, 45003, 45004, 45291, 44926, 43890):279    for n in (-260, -30, -5, -1, 0, 1, 2, 5, 10, 100, 260, 1000):280        addpy("WORKDAY", f"WORKDAY({a},{n})", py_workday(a, n))281        addpy("WORKDAY", f"WORKDAY({a},{n},{HOL})", py_workday(a, n, HOLSET))282    for n in (-30, -1, 1, 7, 45):283        for w in WEEKENDS[:-2] + ['"1111110"']:284            add("WORKDAY.INTL", f"WORKDAY.INTL({a},{n},{w})")285        add("WORKDAY.INTL", f"WORKDAY.INTL({a},{n})", f"WORKDAY.INTL({a},{n},1,{HOL})", f'WORKDAY.INTL({a},{n},"0010001",{HOL})', f"WORKDAY.INTL({a},{n},16,{HOL})")286add("WORKDAY", "WORKDAY(45000.9,1.9)", "WORKDAY(45000,-1.9)", 'WORKDAY("3/15/2023",5)', "WORKDAY(45000,5,45005)", "WORKDAY(-1,5)", "WORKDAY(10,-100)",287    'WORKDAY(45000,5,{"3/20/2023"})')288add("WORKDAY.INTL", 'WORKDAY.INTL(45000,5,"1111111")', "WORKDAY.INTL(45000,5,8)", "WORKDAY.INTL(45000,5,0)", 'WORKDAY.INTL(45000,0,"1111111")',289    'WORKDAY.INTL(45000,5,"abc")', "WORKDAY.INTL(45000,0,1)")290291# ---------------------------------------------------------------- python-only references292293294def general(x):295    if isinstance(x, bool):296        return "TRUE" if x else "FALSE"297    if isinstance(x, (int, float)):298        r = "%.15g" % x299        return r300    return x301302303def strict(x):304    return '"' + x.replace('"', '""') + '"' if isinstance(x, str) else general(x)305306307def lit(x):308    if isinstance(x, bool):309        return "TRUE" if x else "FALSE"310    return q(x) if isinstance(x, str) else repr(x)311312313for v in [123, "abc", True, 1.5, 'a"b', "", -2.25, 1e20, "x y", False, 0.1]:314    PYONLY.append(("VALUETOTEXT", f"VALUETOTEXT({lit(v)})", general(v).replace("e+", "E+") if not isinstance(v, str) else v))315    PYONLY.append(("VALUETOTEXT", f"VALUETOTEXT({lit(v)},0)", general(v).replace("e+", "E+") if not isinstance(v, str) else v))316    PYONLY.append(("VALUETOTEXT", f"VALUETOTEXT({lit(v)},1)", strict(v).replace("e+", "E+")))317ARRAYS = [[[1, 2], [3, "a"]], [[True, "x y"]], [[5]], [["a"], ["b"], ["c"]], [[1.5, -2, "q\"t"], ["", False, 0]]]318for arr in ARRAYS:319    const = "{" + ";".join(",".join(lit(x) for x in row) for row in arr) + "}"320    flat = [x for row in arr for x in row]321    PYONLY.append(("ARRAYTOTEXT", f"ARRAYTOTEXT({const})", ", ".join(general(x) for x in flat)))322    PYONLY.append(("ARRAYTOTEXT", f"ARRAYTOTEXT({const},0)", ", ".join(general(x) for x in flat)))323    PYONLY.append(("ARRAYTOTEXT", f"ARRAYTOTEXT({const},1)", "{" + ";".join(",".join(strict(x) for x in row) for row in arr) + "}"))324RTEXT = ["abc123", "Hello World", "555-1234", "the quick brown fox", "", "colour", "ABC", "aabbc", "2023-03-15 and 2024-12-01", "a1b22c333"]325RPAT = ["[0-9]+", "^abc", "c$", "a.c", r"\d{3}-\d{4}", "colou?r", "^$", "[A-Z][a-z]+", r"\bfox\b", "(a|b)+c", r"(\d{4})-(\d{2})-(\d{2})", "o", r"\s+", "[aeiou]"]326for t in RTEXT:327    for p in RPAT:328        for case in (None, 0, 1):329            flags = re.IGNORECASE if case == 1 else 0330            tail = "" if case is None else f",{case}"331            PYONLY.append(("REGEXTEST", f"REGEXTEST({q(t)},{q(p)}{tail})", re.search(p, t, flags) is not None))332        m = re.search(p, t)333        PYONLY.append(("REGEXEXTRACT", f"REGEXEXTRACT({q(t)},{q(p)})", m.group(0) if m else "#N/A"))334        allm = [x.group(0) for x in re.finditer(p, t)]335        if p != "^$" and all(allm):336            if allm:337                PYONLY.append(("REGEXEXTRACT", f"ROWS(REGEXEXTRACT({q(t)},{q(p)},1))", len(allm)))338            else:339                PYONLY.append(("REGEXEXTRACT", f"REGEXEXTRACT({q(t)},{q(p)},1)", "#N/A"))340            for k, x in enumerate(allm[:4], start=1):341                PYONLY.append(("REGEXEXTRACT", f"INDEX(REGEXEXTRACT({q(t)},{q(p)},1),{k})", x))342            for rep in ("X", "<$0>", ""):343                pyrep = rep.replace("$0", r"\g<0>")344                PYONLY.append(("REGEXREPLACE", f"REGEXREPLACE({q(t)},{q(p)},{q(rep)})", re.sub(p, pyrep, t)))345                for occ in (1, 2, -1):346                    ms = list(re.finditer(p, t))347                    idx = occ - 1 if occ > 0 else len(ms) + occ348                    if 0 <= idx < len(ms):349                        mm = ms[idx]350                        expect = t[:mm.start()] + mm.expand(pyrep) + t[mm.end():]351                    else:352                        expect = t353                    PYONLY.append(("REGEXREPLACE", f"REGEXREPLACE({q(t)},{q(p)},{q(rep)},{occ})", expect))354        if m and m.groups():355            PYONLY.append(("REGEXEXTRACT", f"COLUMNS(REGEXEXTRACT({q(t)},{q(p)},2))", len(m.groups())))356            for k, g in enumerate(m.groups(), start=1):357                PYONLY.append(("REGEXEXTRACT", f"INDEX(REGEXEXTRACT({q(t)},{q(p)},2),1,{k})", g or ""))358PYONLY += [359    ("REGEXTEST", 'REGEXTEST("abc","[")', "#VALUE!"), ("REGEXEXTRACT", 'REGEXEXTRACT("abc","(")', "#VALUE!"),360    ("REGEXREPLACE", 'REGEXREPLACE("abc","[","x")', "#VALUE!"), ("REGEXTEST", 'REGEXTEST("ABC","abc",1)', True),361    ("REGEXREPLACE", 'REGEXREPLACE("John Smith","(\\w+) (\\w+)","$2, $1")', "Smith, John"),362    ("REGEXREPLACE", 'REGEXREPLACE("ABC abc","b","_",0,1)', "A_C a_c"), ("REGEXEXTRACT", 'REGEXEXTRACT("ABC","b",0,1)', "B"),363]364365# ---------------------------------------------------------------- Excel overrides of LibreOffice results366# formula -> (type, Excel value, note)367BOOL = 'Excel converts booleans to the text TRUE/FALSE; LibreOffice converts them to 1/0'368EMPTY = 'Excel documents that an empty find_text matches at start_num; LibreOffice returns #VALUE!'369DEC = 'Excel rounds the decimal value half away from zero (0.285 -> 0.29); LibreOffice rounds the binary double'370EXCEL = {371    'TEXT(TRUE,"0")': ("s", "TRUE", "Excel does not format booleans; LibreOffice coerces TRUE to 1"),372    'TEXT(1/3,"General")': ("s", "0.333333333", "Excel's General shows at most 11 characters in TEXT; LibreOffice shows 15 digits"),373    'LEN(CLEAN("a"&CHAR(127)&"b"))': ("n", 3.0, "Excel's CLEAN removes only codes 0-31; LibreOffice also removes 127"),374    'ENCODEURL("http://contoso.sharepoint.com/Finance/Profit and Loss Statement.xlsx")':375        ("s", "http%3A%2F%2Fcontoso.sharepoint.com%2FFinance%2FProfit%20and%20Loss%20Statement.xlsx",376         "Microsoft's documented example: Excel keeps '.', LibreOffice encodes it as %2E"),377    'DATE(0,3,1)': ("n", 61.0, "Excel adds 1900 to years below 1900"),378    'LEFT(TRUE,2)': ("s", "TR", BOOL), 'RIGHT(FALSE,3)': ("s", "LSE", BOOL), 'LEN(TRUE)': ("n", 4.0, BOOL), 'PROPER(TRUE)': ("s", "True", BOOL),379    'UPPER(TRUE)': ("s", "TRUE", BOOL), 'LOWER(TRUE)': ("s", "true", BOOL), 'EXACT(TRUE,"TRUE")': ("b", True, BOOL), 'CODE(TRUE)': ("n", 84.0, BOOL),380    'CONCAT("a",1,TRUE)': ("s", "a1TRUE", BOOL), 'CONCATENATE("a",TRUE,"")': ("s", "aTRUE", BOOL), 'TEXTJOIN(";",1,"x",TRUE,2.5)': ("s", "x;TRUE;2.5", BOOL),381    'LEN(1E+20)': ("n", 5.0, 'Excel writes 1E+20 (5 characters); LibreOffice writes 1E+020'),382    'FIND("","abc")': ("n", 1.0, EMPTY), 'FIND("","abc",2)': ("n", 2.0, EMPTY), 'SEARCH("","abc")': ("n", 1.0, EMPTY),383    'CHAR(0)': ("e", "#VALUE!", "Excel: CHAR accepts 1-255"), 'CODE("")': ("e", "#VALUE!", "Excel: CODE of empty text is #VALUE!"),384    'UNICHAR(0)': ("e", "#VALUE!", "Excel: UNICHAR(0) is #VALUE!"),385    'LEN(UNICHAR(128512))': ("n", 2.0, "Excel counts UTF-16 code units (an emoji is 2); LibreOffice counts code points"),386    'NUMBERVALUE("",".",",")': ("n", 0.0, "Excel documents NUMBERVALUE(\"\") = 0"),387    'FIXED(0.285,2)': ("s", "0.29", DEC), 'FIXED(1.005,2)': ("s", "1.01", DEC),388    'TEXTBEFORE("a-b-c","-",0)': ("e", "#VALUE!", "Excel documents #VALUE! for instance_num = 0"),389    'TEXTAFTER("a-b-c","-",0)': ("e", "#VALUE!", "Excel documents #VALUE! for instance_num = 0"),390    'TEXTSPLIT("abc","")': ("e", "#VALUE!", "Excel rejects an empty delimiter"),391}392393394NUM = "Excel documents #NUM! for this invalid argument; LibreOffice reports it as #VALUE! (Err:502)"395NEG = "Excel has no dates before serial 0 or after 31 Dec 9999 (#NUM!); LibreOffice computes with them"396for f in ["WEEKDAY(45000,4)", "WEEKDAY(45000,0)", "WEEKDAY(45000,18)", "WEEKNUM(45000,3)", "WEEKNUM(45000,20)", "DATE(-1,1,1)", "TIME(-1,0,0)",397          "TIME(0,-1,0)", "EDATE(45000,1000000)", 'DATEDIF(45000,44000,"D")', 'DATEDIF(44000,45000,"X")', "YEARFRAC(45000,45100,5)",398          "YEARFRAC(45000,45100,-1)", "NETWORKDAYS.INTL(45000,45100,8)", "NETWORKDAYS.INTL(45000,45100,0)", "NETWORKDAYS.INTL(45000,45100,18)",399          "WORKDAY.INTL(45000,5,8)", "WORKDAY.INTL(45000,5,0)"]:400    EXCEL[f] = ("e", "#NUM!", NUM)401for f in ["WEEKDAY(-1)", "WEEKNUM(-1)", "YEAR(-1)", "YEAR(2958466)", "MONTH(-5)", "DAY(-5)", "HOUR(-0.5)", "MINUTE(-0.5)", "SECOND(-0.5)",402          "DATE(10000,1,1)", "EDATE(-1,1)", "EDATE(100,-12)", "EOMONTH(-1,1)", "EOMONTH(100,-12)", "DAYS360(-1,5)", "NETWORKDAYS(-1,5)",403          "WORKDAY(-1,5)", "WORKDAY(10,-100)"]:404    EXCEL[f] = ("e", "#NUM!", NEG)405EXCEL["TIME(12.9,30.9,10.9)"] = ("n", (12 * 3600 + 30 * 60 + 10) / 86400, "Excel truncates hour, minute and second to integers; LibreOffice keeps the fractions")406EXCEL["TIME(0,0,86399)"] = ("e", "#NUM!", "Excel limits each TIME argument to 32767")407EXCEL["TIME(32768,0,0)"] = ("e", "#NUM!", "Excel limits each TIME argument to 32767")408for text, serial in [("2023/03/15", 45000), ("15 March 2023", 45000), ("3-15-2023", 45000), ("1 Jan 2000", 36526)]:409    EXCEL[f"DATEVALUE({q(text)})"] = ("n", float(serial), "Excel accepts this date text; LibreOffice's en-US patterns do not")410EXCEL['DATEVALUE("1/1/30")'] = ("n", 10959.0, "Excel's two-digit year pivot is 2029 (30 -> 1930); LibreOffice's is 2029+1")411412413def yearfrac_us(sa, sb):414    """Excel's 30/360 US YEARFRAC (D. A. Wheeler, 'YEARFRAC Incompatibilities between Excel 2007 and OOXML')."""415    a, b = s2d(min(sa, sb)), s2d(max(sa, sb))416417    def feb_end(d):418        return d.month == 2 and (d + dt.timedelta(days=1)).month == 3419    d1, d2 = a.day, b.day420    if feb_end(a) and feb_end(b):421        d2 = 30422    if feb_end(a):423        d1 = 30424    if d2 == 31 and d1 >= 30:425        d2 = 30426    if d1 == 31:427        d1 = 30428    return ((b.year - a.year) * 360 + (b.month - a.month) * 30 + (d2 - d1)) / 360429430431def main():432    lo = recalc([f for _, f, _ in CASES])433    out = []434    problems = []435    for (fn, f, py), (t, v) in zip(CASES, lo):436        entry = {"fn": fn, "f": f, "t": t, "v": v, "src": "lo"}437        if py is not None:438            entry["src"] = "lo+py"439            if fn == "WEEKNUM" and t == "n" and v == 1 and py >= 53:440                # Excel numbers the last days of December 53/54; LibreOffice wraps them into week 1 of the next year441                entry.update({"lo": v, "lot": t, "v": float(py), "src": "excel",442                              "note": "Excel's WEEKNUM never wraps to week 1 in December (Python reference); LibreOffice does"})443            elif t != "n" or abs(v - py) > 1e-9:444                problems.append(f"{f}: LibreOffice {v!r} != Python {py!r}")445        m = re.fullmatch(r"YEARFRAC\((\d+),(\d+)(,0)?\)", f)446        if m and t == "n":447            want = yearfrac_us(int(m.group(1)), int(m.group(2)))448            if abs(want - v) > 1e-9:449                entry.update({"lo": v, "lot": t, "v": want, "src": "excel",450                              "note": "Excel's US 30/360 treats a start date on the last day of February as day 30 (Python reference); LibreOffice does not"})451        if f in EXCEL:452            et, ev, note = EXCEL[f]453            entry.update({"lo": v, "lot": t, "t": et, "v": ev, "src": "excel", "note": note})454        out.append(entry)455    for fn, f, expected in PYONLY:456        if isinstance(expected, bool):457            t = "b"458        elif isinstance(expected, (int, float)):459            t, expected = "n", float(expected)460        elif isinstance(expected, str) and expected.startswith("#"):461            t = "e"462        else:463            t = "s"464        out.append({"fn": fn, "f": f, "t": t, "v": expected, "src": "py"})465    if problems:466        print("\n".join(problems[:50]))467        sys.exit(f"{len(problems)} LibreOffice/Python disagreements")468    dest = os.path.join(os.path.dirname(os.path.abspath(__file__)), "..", "Fixtures", "function_references.json")469    with open(dest, "w", encoding="utf-8") as fh:470        fh.write("[\n" + ",\n".join(json.dumps(x, ensure_ascii=False) for x in out) + "\n]\n")471    by = {}472    for e in out:473        by[e["src"]] = by.get(e["src"], 0) + 1474    print(f"{len(out)} cases {by} -> {os.path.normpath(dest)}")475476477if __name__ == "__main__":478    main()479
Files main
Menu

Theme