Repository Public
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