SPB Git

Repository Public

gen_lookup.py 9.2 KB 166 lines python

1"""Reference cases for the lookup & reference functions, computed by LibreOffice Calc.23Only inputs on which LibreOffice and Excel are known to agree are generated here (approximate4matches run on sorted data); Excel-specific behaviour is covered by hand-written Swift tests.5"""6from lo_harness import Fixture78fx = Fixture("lookup_lo", "VLOOKUP, HLOOKUP, LOOKUP, MATCH, XMATCH, XLOOKUP, ADDRESS, AREAS, HYPERLINK, TRANSPOSE — LibreOffice 26.8 results")910asc = [1, 3, 3, 5, 8, 8, 8, 10, 12, 15, 20, 25]11labels = ["one", "three-a", "three-b", "five", "eight-a", "eight-b", "eight-c", "ten", "twelve", "fifteen", "twenty", "twenty-five"]12amounts = [10.5, 30, 31, 50.25, 80, 81, 82, 100, 120, 150, 200, 250]13unsorted = [8, 2, 15, 4, 23, 16, 42, 4, 1, 9, 8, 30]14names = ["apple", "Banana", "cherry", "apple pie", "a?c", "x*y", "abc", "Cherry", "date", "fig", "grape", "kiwi~"]15sorted_text = ["alpha", "bravo", "charlie", "delta", "echo", "foxtrot", "golf", "hotel", "india", "juliet", "kilo", "lima"]16desc = [50, 40, 40, 30, 25, 20, 20, 20, 10, 5, 2, 1]17mixed = [5, "five", True, None, "=NA()", 7, "Seven", False, 0, "", 12, "TEXT"]18fx.column("A", asc); fx.column("B", labels); fx.column("C", amounts)19fx.column("D", unsorted); fx.column("E", names); fx.column("F", sorted_text)20fx.column("G", desc); fx.column("H", mixed); fx.column("I", [i * 1.5 for i in range(1, 13)])21# horizontal table K1:R3 (sorted first row)22fx.row(1, [10, 20, 30, 40, 50, 60, 70, 80], 11)23fx.row(2, ["ten", "twenty", "thirty", "forty", "fifty", "sixty", "seventy", "eighty"], 11)24fx.row(3, [1.1, 2.2, 3.3, 4.4, 5.5, 6.6, 7.7, 8.8], 11)2526num_keys = [-1, 0, 0.5, 1, 2, 3, 4, 5, 8, 9, 10, 11, 12, 19.99, 20, 25, 26, 1000]27q = lambda s: '"%s"' % s2829# --- VLOOKUP -------------------------------------------------------------------------------30for k in num_keys:31    for col in (2, 3):32        fx.case("=VLOOKUP(%s,A1:C12,%d)" % (k, col))33        fx.case("=VLOOKUP(%s,A1:C12,%d,FALSE)" % (k, col))34    fx.case("=VLOOKUP(%s,A1:C12,2,TRUE)" % k)35for k in [8, 4, 23, 42, 1, 30, 99, 2.5]:36    fx.case("=VLOOKUP(%s,D1:F12,2,FALSE)" % k)37    fx.case("=VLOOKUP(%s,D1:F12,3,0)" % k)38for k in ["alpha", "ALPHA", "bravo", "c", "charlie", "delta1", "lima", "zulu", "a", "Hotel", "golf "]:39    fx.case("=VLOOKUP(%s,F1:G12,2,TRUE)" % q(k))40    fx.case("=VLOOKUP(%s,F1:G12,2,FALSE)" % q(k))41for pat in ["app*", "*pie", "?anana", "a?c", "a~?c", "x~*y", "x*y", "*e", "ch*y", "CHERRY", "*", "?", "???", "kiwi~~", "d*te", "*rr*", "zzz*"]:42    fx.case("=VLOOKUP(%s,E1:F12,2,FALSE)" % q(pat))43    fx.case("=MATCH(%s,E1:E12,0)" % q(pat))44fx.case("=VLOOKUP(5,A:C,2,FALSE)"); fx.case("=VLOOKUP(13,A:C,3)"); fx.case("=VLOOKUP(\"date\",E:F,2,FALSE)")45fx.case("=VLOOKUP(5,A1:C12,4,FALSE)"); fx.case("=VLOOKUP(5,A1:C12,0,FALSE)"); fx.case("=VLOOKUP(5,A1:C12,1)")46fx.case("=VLOOKUP(TRUE,H1:I12,2,FALSE)"); fx.case("=VLOOKUP(FALSE,H1:I12,2,FALSE)")47fx.case("=VLOOKUP(7,H1:I12,2,FALSE)"); fx.case("=VLOOKUP(\"seven\",H1:I12,2,FALSE)"); fx.case("=VLOOKUP(12,H1:I12,2,FALSE)")48fx.case("=VLOOKUP(\"text\",H1:I12,2,FALSE)"); fx.case("=VLOOKUP(5,{1,\"a\";5,\"b\";9,\"c\"},2,FALSE)"); fx.case("=VLOOKUP(6,{1,\"a\";5,\"b\";9,\"c\"},2)")4950# --- HLOOKUP -------------------------------------------------------------------------------51for k in [5, 10, 15, 20, 45, 80, 85, 30]:52    for row in (2, 3):53        fx.case("=HLOOKUP(%s,K1:R3,%d)" % (k, row))54        fx.case("=HLOOKUP(%s,K1:R3,%d,FALSE)" % (k, row))55fx.case("=HLOOKUP(\"t*\",K2:R3,2,FALSE)"); fx.case("=HLOOKUP(\"FIFTY\",K2:R3,2,FALSE)"); fx.case("=HLOOKUP(30,K1:R3,4)")56fx.case("=HLOOKUP(2,{1,2,3;\"a\",\"b\",\"c\"},2,FALSE)")5758# --- LOOKUP --------------------------------------------------------------------------------59for k in num_keys:60    fx.case("=LOOKUP(%s,A1:A12,B1:B12)" % k)61    fx.case("=LOOKUP(%s,A1:A12)" % k)62    fx.case("=LOOKUP(%s,A1:C12)" % k)63for k in [5, 10, 35, 80, 99]:64    fx.case("=LOOKUP(%s,K1:R1,K2:R2)" % k)65    fx.case("=LOOKUP(%s,K1:R3)" % k)66    fx.case("=LOOKUP(%s,K1:R1,B1:B8)" % k)67for k in ["delta", "dz", "a", "zz", "KILO"]:68    fx.case("=LOOKUP(%s,F1:F12,A1:A12)" % q(k))69fx.case("=LOOKUP(2,{1,2,3},{\"a\",\"b\",\"c\"})"); fx.case("=LOOKUP(2.5,{1,2,3;4,5,6})"); fx.case("=LOOKUP(2.5,{1,10;2,20;3,30})")7071# --- MATCH ---------------------------------------------------------------------------------72for k in num_keys:73    fx.case("=MATCH(%s,A1:A12,1)" % k)74    fx.case("=MATCH(%s,A1:A12)" % k)75    fx.case("=MATCH(%s,A1:A12,0)" % k)76for k in [60, 50, 45, 40, 30, 22, 20, 10, 3, 1, 0, 25]:77    fx.case("=MATCH(%s,G1:G12,-1)" % k)78    fx.case("=MATCH(%s,G1:G12,0)" % k)79for k in [8, 4, 42, 30, 1, 100]:80    fx.case("=MATCH(%s,D1:D12,0)" % k)81for k in ["alpha", "Echo", "e", "lima", "m", "a"]:82    fx.case("=MATCH(%s,F1:F12,1)" % q(k))83    fx.case("=MATCH(%s,F1:F12,0)" % q(k))84for k in [10, 35, 80, 5]:85    fx.case("=MATCH(%s,K1:R1,1)" % k)86    fx.case("=MATCH(%s,K1:R1,0)" % k)87fx.case("=MATCH(5,A1:B12,0)"); fx.case("=MATCH(TRUE,H1:H12,0)"); fx.case("=MATCH(\"seven\",H1:H12,0)")88fx.case("=MATCH(2,{1,2,3},0)"); fx.case("=MATCH(\"b\",{\"a\";\"b\";\"c\"},0)"); fx.case("=MATCH(5,A:A,0)"); fx.case("=MATCH(11,A:A)")8990# --- XMATCH --------------------------------------------------------------------------------91for k in [0, 1, 3, 4, 8, 9, 25, 26]:92    for mm in (0, -1, 1):93        for sm in (1, -1, 2):94            fx.case("=XMATCH(%s,A1:A12,%d,%d)" % (k, mm, sm))95for k in [60, 50, 41, 40, 20, 3, 1, 0]:96    for mm in (0, -1, 1):97        fx.case("=XMATCH(%s,G1:G12,%d,-2)" % (k, mm))98for k in [8, 4, 3, 5, 16, 20, 42, 50, 0, 1]:99    for mm in (0, -1, 1):100        for sm in (1, -1):101            fx.case("=XMATCH(%s,D1:D12,%d,%d)" % (k, mm, sm))102for pat in ["app*", "*y", "?anana", "a~?c", "cherry", "*e", "zz*"]:103    fx.case("=XMATCH(%s,E1:E12,2)" % q(pat))104    fx.case("=XMATCH(%s,E1:E12,2,-1)" % q(pat))105    fx.case("=XMATCH(%s,E1:E12,0)" % q(pat))106fx.case("=XMATCH(8,A1:A12)"); fx.case("=XMATCH(\"CHERRY\",E1:E12)"); fx.case("=XMATCH(40,K1:R1)"); fx.case("=XMATCH(45,K1:R1,-1)"); fx.case("=XMATCH(45,K1:R1,1)")107for k in ["delta", "dz", "a", "zz"]:108    for mm in (-1, 1):109        fx.case("=XMATCH(%s,F1:F12,%d)" % (q(k), mm))110        fx.case("=XMATCH(%s,F1:F12,%d,2)" % (q(k), mm))111112# --- XLOOKUP -------------------------------------------------------------------------------113for k in [0, 1, 3, 4, 8, 9, 25, 26]:114    fx.case("=XLOOKUP(%s,A1:A12,B1:B12)" % k)115    fx.case("=XLOOKUP(%s,A1:A12,B1:B12,\"none\")" % k)116    for mm in (-1, 1):117        for sm in (1, -1, 2):118            fx.case("=XLOOKUP(%s,A1:A12,C1:C12,-999,%d,%d)" % (k, mm, sm))119    fx.case("=XLOOKUP(%s,A1:A12,B1:B12,\"none\",0,-1)" % k)120for k in [8, 4, 3, 16, 42, 50, 0]:121    for mm in (0, -1, 1):122        for sm in (1, -1):123            fx.case("=XLOOKUP(%s,D1:D12,E1:E12,\"nf\",%d,%d)" % (k, mm, sm))124for k in [60, 41, 40, 20, 3, 0]:125    for mm in (0, -1, 1):126        fx.case("=XLOOKUP(%s,G1:G12,F1:F12,\"nf\",%d,-2)" % (k, mm))127for pat in ["app*", "*pie", "a~?c", "x~*y", "CH*", "zz*"]:128    fx.case("=XLOOKUP(%s,E1:E12,D1:D12,,2)" % q(pat))129    fx.case("=XLOOKUP(%s,E1:E12,D1:D12,0,2,-1)" % q(pat))130for k in [5, 12, 13]:131    fx.case("=XLOOKUP(%s,A1:A12,B1:C12)" % k)                       # multi-column return spills132    fx.case("=XLOOKUP(%s,A1:A12,A1:C12,\"missing\")" % k)133for k in [30, 55, 80]:134    fx.case("=XLOOKUP(%s,K1:R1,K2:R2)" % k)135    fx.case("=XLOOKUP(%s,K1:R1,K2:R3)" % k)                         # horizontal lookup returns a column136    fx.case("=XLOOKUP(%s,K1:R1,K3:R3,\"nf\",-1)" % k)137fx.case("=XLOOKUP(5,A:A,B:B)"); fx.case("=XLOOKUP(\"fig\",E:E,D:D)"); fx.case("=XLOOKUP(5,A1:A12,B1:B11)"); fx.case("=XLOOKUP(5,A1:B12,C1:C12)")138fx.case("=SUM(XLOOKUP(3,A1:A12,C1:C12):XLOOKUP(10,A1:A12,C1:C12))")139fx.case("=SUM(XLOOKUP(5,A1:A12,C1:C12,,0,-1):C12)")140fx.case("=XLOOKUP(2,{1,2,3},{\"a\",\"b\",\"c\"})"); fx.case("=XLOOKUP(\"b\",{\"a\";\"b\";\"c\"},{1;2;3})")141142# --- array lookup values (function-level lifting) ------------------------------------------143fx.case("=VLOOKUP({3;10;11;99},A1:C12,2,FALSE)", shape=(4, 1))144fx.case("=VLOOKUP({3,10,11},A1:C12,3)", shape=(1, 3))145fx.case("=MATCH({1;8;9;25},A1:A12,0)", shape=(4, 1))146fx.case("=MATCH({1;8;9;25},A1:A12,1)", shape=(4, 1))147fx.case("=XMATCH({8;4;7},D1:D12)", shape=(3, 1))148fx.case("=XLOOKUP({8;4;7},D1:D12,E1:E12,\"nf\")", shape=(3, 1))149fx.case("=VLOOKUP(5,A1:C12,{2,3},FALSE)", shape=(1, 2))150151# --- ADDRESS / AREAS / HYPERLINK / TRANSPOSE ---------------------------------------------152for row, col in [(1, 1), (2, 3), (10, 26), (100, 27), (1048576, 16384), (5, 702), (7, 703)]:153    for ab in (1, 2, 3, 4):154        fx.case("=ADDRESS(%d,%d,%d)" % (row, col, ab))155        if row < 1048576:      # LibreOffice rejects relative R1C1 offsets at the sheet limits156            fx.case("=ADDRESS(%d,%d,%d,FALSE)" % (row, col, ab))157    fx.case("=ADDRESS(%d,%d)" % (row, col))158fx.case("=ADDRESS(2,3,1,TRUE,\"Sheet2\")"); fx.case("=ADDRESS(2,3,1,TRUE,\"My Sheet\")"); fx.case("=ADDRESS(2,3,4,FALSE,\"Data\")")159fx.case("=ADDRESS(2,3,1,FALSE,\"My Sheet\")"); fx.case("=ADDRESS(0,3)"); fx.case("=ADDRESS(3,0)"); fx.case("=ADDRESS(1,1,5)")160fx.case("=AREAS(A1:B2)"); fx.case("=AREAS(A1)"); fx.case("=AREAS(A:C)")161# HYPERLINK is not generated: LibreOffice returns a two-cell array {name; url}; see the hand-written tests.162fx.case("=TRANSPOSE(A1:C4)"); fx.case("=TRANSPOSE(K1:R3)"); fx.case("=TRANSPOSE({1,2,3})"); fx.case("=TRANSPOSE({1;2;3})"); fx.case("=TRANSPOSE(A1)")163fx.case("=TRANSPOSE(H1:H8)")164165fx.write()166
Files main
Menu

Theme