Repository Public
1import Foundation2import Testing3@testable import SheetPython45let salesHost: @Sendable () -> FakeHost = {6 FakeHost([7 .init(name: "Data", rows: FakeHost.cells([8 ["Category", "Revenue", "Units", "Date", "Flag"],9 ["A", 10.5, 1, 45292, true], // 2024-01-0110 ["B", 20, 2, 45293.5, false], // 2024-01-02 12:0011 ["A", 30, nil, 45294, true],12 ["C", "#DIV/0!", 4, nil, nil],13 ]), dateColumns: [3]),14 .init(name: "Feuille é", rows: FakeHost.cells([["x", 1], ["y", 2]])),15 ])16}1718@Suite(.serialized) struct OrixAPITests {19 @Test(.enabled(Interp.noPython) { await Interp.any != nil })20 func readsWithoutPandas() async throws {21 let py = try #require(await Interp.bareOrAny)22 let r = try await runPython("""23 import orix, json, datetime24 s = orix.active_sheet()25 rg = s.range("A1:E5")26 v = rg.values27 out = {28 "name": s.name, "shape": list(rg.shape), "address": rg.address,29 "header": v[0], "single": s.range("B2").value, "blank": s.range("C4").value,30 "bool": v[1][4], "err": str(v[4][1]), "err_type": type(v[4][1]).__name__,31 "date": v[1][3].isoformat(), "datetime": v[2][3].isoformat(), "header_date_col": v[0][3],32 "sheets": [x.name for x in orix.sheets()], "sel": repr(orix.selection()), "used": s.used_range().address,33 "other": orix.sheet("Feuille é").range("A1:B2").values, "getitem": s["A2"].value,34 "block": s.range("A2:B3").value,35 }36 try: orix.sheet("Nope")37 except orix.OrixError as e: out["missing"] = str(e)38 print(json.dumps(out))39 """, on: py, host: salesHost())40 #expect(r.succeeded, "\(r.stderr)")41 let o = report(r)42 #expect(o["name"] as? String == "Data")43 #expect(o["shape"] as? [Int] == [5, 5])44 #expect(o["header"] as? [String] == ["Category", "Revenue", "Units", "Date", "Flag"])45 #expect(o["single"] as? Double == 10.5)46 #expect(o["blank"] is NSNull)47 #expect(o["bool"] as? Bool == true)48 #expect(o["err"] as? String == "#DIV/0!")49 #expect(o["err_type"] as? String == "CellError")50 #expect(o["date"] as? String == "2024-01-01T00:00:00")51 #expect(o["datetime"] as? String == "2024-01-02T12:00:00")52 #expect(o["header_date_col"] as? String == "Date")53 #expect(o["sheets"] as? [String] == ["Data", "Feuille é"])54 #expect((o["sel"] as? String)?.contains("Data!A1:B2") == true)55 #expect(o["used"] as? String == "A1:E5")56 #expect((o["missing"] as? String)?.contains("no sheet named 'Nope'") == true)57 #expect(o["getitem"] as? String == "A")58 #expect((o["block"] as? [[Any]])?.count == 2)59 }6061 @Test(.enabled(Interp.noBare) { await Interp.bare != nil })62 func pandasMissingGivesClearError() async throws {63 let py = try #require(await Interp.bare)64 let r = try await runPython("""65 import orix, json66 rg = orix.active_sheet().range("A1:B3")67 out = {"values": rg.values}68 for name in ("to_dataframe", "to_numpy"):69 try: getattr(rg, name)()70 except ImportError as e: out[name] = str(e)71 rg2 = orix.active_sheet().range("H1")72 rg2.write([[1, "a"], [None, True]])73 rg2.write({"k": 1, "j": 2.5})74 print(json.dumps(out))75 """, on: py, host: salesHost())76 #expect(r.succeeded, "\(r.stderr)")77 let o = report(r)78 #expect((o["to_dataframe"] as? String)?.contains("needs pandas") == true)79 #expect((o["to_numpy"] as? String)?.contains("needs numpy") == true)80 #expect((o["values"] as? [[Any]])?.count == 3)81 #expect(r.writes.count == 2)82 #expect(cellsText(r.writes[0].rows) == "1,a;_,T")83 #expect(cellsText(r.writes[1].rows) == "k,1;j,2.5")84 }8586 @Test(.enabled(Interp.noPython) { await Interp.any != nil })87 func writesOfPlainPythonTypes() async throws {88 let py = try #require(await Interp.bareOrAny)89 let r = try await runPython("""90 import orix, datetime, decimal, fractions91 s = orix.active_sheet()92 s.range("A1").write(42) # 0 scalar93 s.range("B2").write([1, 2.5, "x", None, True]) # 1 list -> row94 s.range("B3").write([1, 2, 3], transpose=True) # 2 column95 s.range("C1:D9").write([[1, 2], [3, 4]]) # 3 block, anchor = top-left96 s.range("E1").write((("a", "b"), ("c", "d"))) # 4 tuples97 s.range("F1").write({"x": [1, 2], "y": ["u", "v"]}) # 5 dict of columns98 s.range("G1").write({"alpha": 1, "beta": None}) # 6 dict of scalars99 s.range("H1").write(float("nan")) # 7 NaN -> blank100 s.range("I1").write(float("inf")) # 8 inf -> #NUM!101 s.range("J1").write([datetime.date(2024, 1, 1), datetime.datetime(2024, 1, 2, 12, 0), datetime.time(6, 0), datetime.timedelta(hours=36)]) # 9102 s.range("K1").write([decimal.Decimal("1.25"), fractions.Fraction(1, 4), 2**70, "ünï"]) # 10103 s.range("L1").write_formula("=SUM(B:B)") # 11104 s.range("L2").write_formula([["=A1+1", "=A1*2"]]) # 12105 orix.sheet("Feuille é").range("A1").write("hello") # 13106 s.range("M1").write(orix.CellError("#N/A")) # 14107 s.range("N1").write([datetime.date(1900, 2, 28), datetime.date(1900, 3, 1), datetime.date(1900, 1, 1)]) # 15108 s.range("O1").write([]) # nothing109 for bad in (lambda: s.range("A1").write([[1, 2], [3]]), lambda: s.range("XFD1").write([1, 2]),110 lambda: s.range("A1048576").write([[1], [2]]), lambda: s.range("A1").write([[{"a": 1}]]),111 lambda: s.range("A1").write_formula(5), lambda: s.range("not a ref").write(1)):112 try: bad(); print("NOT RAISED")113 except (ValueError, TypeError) as e: print("raised:", e)114 """, on: py, host: salesHost())115 #expect(r.succeeded, "\(r.stderr)")116 #expect(!r.stdout.contains("NOT RAISED"))117 #expect(r.stdout.components(separatedBy: "raised:").count == 7)118 let w = r.writes119 try #require(w.count == 16)120 #expect(w[0].anchorA1 == "A1" && cellsText(w[0].rows) == "42" && w[0].sheet == "Data")121 #expect(cellsText(w[1].rows) == "1,2.5,x,_,T")122 #expect(cellsText(w[2].rows) == "1;2;3")123 #expect(w[3].anchorA1 == "C1" && cellsText(w[3].rows) == "1,2;3,4")124 #expect(cellsText(w[4].rows) == "a,b;c,d")125 #expect(cellsText(w[5].rows) == "x,y;1,u;2,v")126 #expect(cellsText(w[6].rows) == "alpha,1;beta,_")127 #expect(w[7].rows == [[.null]])128 #expect(w[8].rows == [[.error("#NUM!")]])129 #expect(w[9].rows == [[.number(45292), .number(45293.5), .number(0.25), .number(1.5)]])130 #expect(w[9].formats == [PythonFormatHint(r0: 0, c0: 0, r1: 0, c1: 0, format: "yyyy-mm-dd"), PythonFormatHint(r0: 0, c0: 1, r1: 0, c1: 1, format: "yyyy-mm-dd hh:mm:ss"),131 PythonFormatHint(r0: 0, c0: 2, r1: 0, c1: 2, format: "hh:mm:ss"), PythonFormatHint(r0: 0, c0: 3, r1: 0, c1: 3, format: "[h]:mm:ss")])132 #expect(w[10].rows == [[.number(1.25), .number(0.25), .number(1180591620717411303424), .string("ünï")]])133 #expect(w[11].formulas && w[11].rows == [[.string("=SUM(B:B)")]])134 #expect(w[12].formulas && cellsText(w[12].rows) == "=A1+1,=A1*2")135 #expect(w[13].sheet == "Feuille é")136 #expect(w[14].rows == [[.error("#N/A")]])137 #expect(w[15].rows == [[.number(59), .number(61), .number(1)]], "1900 system with the serial-60 gap")138 }139140 @Test(.enabled(Interp.noPandas) { await Interp.scientific != nil })141 func dataFramesAndArrays() async throws {142 let py = try #require(await Interp.scientific)143 let r = try await runPython("""144 import orix, json, numpy as np, pandas as pd145 s = orix.active_sheet()146 df = s.range("A1:E5").to_dataframe()147 out = {"dtypes": {c: str(t) for c, t in df.dtypes.items()}, "cols": list(df.columns),148 "units_nan": bool(np.isnan(df["Units"][2])), "rev_nan": bool(pd.isna(df["Revenue"][3])),149 "date0": str(df["Date"][0]), "nat": bool(pd.isna(df["Date"][3])),150 "keep": str(s.range("A1:E5").to_dataframe(errors="keep")["Revenue"][3]),151 "nohdr": list(map(str, s.range("A2:B3").to_dataframe(header=False).columns)),152 "indexed": s.range("A1:B5").to_dataframe(index="Category").index.name,153 "np_shape": list(s.range("B2:C3").to_numpy().shape), "np_dtype": str(s.range("B2:C4").to_numpy().dtype),154 "np_nan": bool(np.isnan(s.range("B2:C4").to_numpy()[2, 1])), "np_obj": str(s.range("A2:B3").to_numpy().dtype)}155 result = df.groupby("Category")["Revenue"].sum()156 s.range("G1").write(result) # 0 Series with named index157 s.range("J1").write(df[["Category", "Units"]]) # 1 default index: not written158 s.range("M1").write(df[["Category", "Units"]], index=True) # 2159 s.range("P1").write(df[["Revenue"]], header=False) # 3160 s.range("R1").write(np.array([[1.5, np.nan], [3, 4]])) # 4161 s.range("T1").write(np.arange(3)) # 5 1-D -> row162 s.range("V1").write(np.float64(2.5)) # 6 numpy scalar163 s.range("W1").write(df[["Date", "Flag"]]) # 7 dates + bool/None164 s.range("Y1").write(pd.DataFrame({"a": [1, 2]}, index=pd.MultiIndex.from_tuples([("x", 1), ("y", 2)], names=["k", "n"]))) # 8165 s.range("AC1").write(pd.Series([1.0, None, 3.0])) # 9 unnamed166 s.range("AE1").write(pd.DataFrame({"t": pd.to_datetime(["2024-03-01 08:30", None])})) # 10 NaT + time167 s.range("AG1").write([np.int64(7), np.bool_(True), np.datetime64("2024-01-01"), pd.NA, pd.NaT, pd.Timestamp("2024-01-02")]) # 11168 s.range("AJ1").write(pd.DataFrame({"i": pd.array([1, None], dtype="Int64"), "c": pd.Categorical(["u", "v"]), "s": pd.array(["p", None], dtype="string")})) # 12169 print(json.dumps(out))170 """, on: py, host: salesHost())171 #expect(r.succeeded, "\(r.stderr)")172 let o = report(r)173 let dtypes = try #require(o["dtypes"] as? [String: String])174 #expect(dtypes["Revenue"] == "float64" && dtypes["Units"] == "float64")175 #expect(dtypes["Date"]?.hasPrefix("datetime64") == true)176 #expect(o["units_nan"] as? Bool == true && o["rev_nan"] as? Bool == true && o["nat"] as? Bool == true)177 #expect(o["date0"] as? String == "2024-01-01 00:00:00")178 #expect(o["keep"] as? String == "#DIV/0!")179 #expect(o["nohdr"] as? [String] == ["0", "1"])180 #expect(o["indexed"] as? String == "Category")181 #expect(o["np_shape"] as? [Int] == [2, 2] && o["np_dtype"] as? String == "float64" && o["np_nan"] as? Bool == true && o["np_obj"] as? String == "object")182 let w = r.writes183 try #require(w.count == 13)184 #expect(cellsText(w[0].rows) == "Category,Revenue;A,40.5;B,20;C,0")185 #expect(cellsText(w[1].rows) == "Category,Units;A,1;B,2;A,_;C,4")186 #expect(cellsText(w[2].rows) == "_,Category,Units;0,A,1;1,B,2;2,A,_;3,C,4")187 #expect(cellsText(w[3].rows) == "10.5;20;30;_")188 #expect(cellsText(w[4].rows) == "1.5,_;3,4")189 #expect(cellsText(w[5].rows) == "0,1,2")190 #expect(w[6].rows == [[.number(2.5)]])191 #expect(cellsText(w[7].rows) == "Date,Flag;45292,T;45293.5,F;45294,T;_,_")192 #expect(w[7].formats == [PythonFormatHint(r0: 1, c0: 0, r1: 4, c1: 0, format: "yyyy-mm-dd hh:mm:ss")])193 #expect(cellsText(w[8].rows) == "k,n,a;x,1,1;y,2,2")194 #expect(cellsText(w[9].rows) == "_,value;0,1;1,_;2,3")195 if case .number(let serial) = w[10].rows[1][0] { #expect(abs(serial - (45352 + 8.5 / 24)) < 1e-9) } else { Issue.record("expected a serial number") }196 #expect(w[10].rows[2] == [.null])197 #expect(cellsText(w[11].rows) == "7,T,45292,_,_,45293")198 #expect(cellsText(w[12].rows) == "i,c,s;1,u,p;_,v,_")199 }200201 @Test(.enabled("polars is not installed: test skipped") { await Interp.all.contains { $0.has("polars") } })202 func polarsExchange() async throws {203 let py = try #require(await Interp.all.first { $0.has("polars") })204 let r = try await runPython("""205 import orix, polars as pl, datetime206 s = orix.active_sheet()207 df = s.range("A1:C4").to_polars()208 print(df.schema)209 s.range("H1").write(pl.DataFrame({"a": [1, None], "b": ["x", "y"], "d": [datetime.date(2024, 1, 1), None]}))210 s.range("L1").write(df.group_by("Category").agg(pl.col("Revenue").sum()).sort("Category"))211 orix.show(df, name="polars frame")212 """, on: py, host: salesHost())213 #expect(r.succeeded, "\(r.stderr)")214 #expect(cellsText(r.writes[0].rows) == "a,b,d;1,x,45292;_,y,_")215 #expect(r.writes[0].formats == [PythonFormatHint(r0: 1, c0: 2, r1: 1, c1: 2, format: "yyyy-mm-dd")])216 #expect(cellsText(r.writes[1].rows) == "Category,Revenue;A,40.5;B,20")217 #expect(r.tables.first?.name == "polars frame" && r.tables.first?.kind == "polars" && r.tables.first?.rowCount == 3)218 }219220 @Test(.enabled(Interp.noPython) { await Interp.any != nil })221 func dateSerialQuirk() async throws {222 let py = try #require(await Interp.any)223 let host = FakeHost([.init(name: "D", rows: FakeHost.cells([[1], [59], [60], [61], [45292.75], ["text"], [nil]]), dateColumns: [0])])224 let r = try await runPython("""225 import orix, json, datetime226 v = [r[0] for r in orix.active_sheet().range("A1:A7").values]227 print(json.dumps([x.isoformat() if isinstance(x, datetime.datetime) else x for x in v]))228 print(json.dumps([orix.to_serial(x) if isinstance(x, datetime.datetime) else None for x in v]))229 """, on: py, host: host)230 #expect(r.succeeded, "\(r.stderr)")231 let lines = r.stdout.split(separator: "\n").map(String.init)232 #expect(json(lines[0]) as? [String?] == ["1900-01-01T00:00:00", "1900-02-28T00:00:00", "1900-02-28T00:00:00", "1900-03-01T00:00:00", "2024-01-01T18:00:00", "text", nil])233 #expect(json(lines[1]) as? [Double?] == [1, 59, 59, 61, 45292.75, nil, nil], "round trip (60 is not representable)")234 }235236 @Test(.enabled(Interp.noPandas) { await Interp.scientific != nil })237 func figuresAndTablePreviews() async throws {238 let py = try #require(await Interp.scientific)239 try await runPython("""240 import orix, numpy as np, pandas as pd, matplotlib.pyplot as plt241 df = pd.DataFrame({"x": np.arange(500), "y": np.arange(500) * 1.5, "s": ["é"] * 500})242 orix.show(df, name="big")243 orix.show(df.set_index("s").head(3))244 orix.show(df["y"])245 orix.show(np.eye(2))246 orix.show([[1, 2], [3, 4]])247 orix.show({"a": [1, 2, 3]})248 orix.show(3.14) # not a table: printed249 fig, ax = plt.subplots(); ax.plot(df.x, df.y); ax.set_title("Shown explicitly")250 orix.show(fig)251 orix.show(fig) # sent once252 plt.figure(); plt.hist(df.y); plt.title("Left open")253 closed = plt.figure(); plt.close(closed)254 """, on: py, inspect: { r in255 #expect(r.succeeded, "\(r.stderr)")256 #expect(r.figures.count == 2)257 #expect(r.figureTitles == ["Shown explicitly", "Left open"])258 for url in r.figures {259 let data = try Data(contentsOf: url)260 #expect(data.prefix(4) == Data([0x89, 0x50, 0x4E, 0x47]) && data.count > 2_000)261 }262 #expect(r.tables.map(\.name) == ["big", "DataFrame", "DataFrame", "ndarray", "list", "dict"])263 let big = r.tables[0]264 #expect(big.rowCount == 500 && big.columnCount == 3 && big.rows.count == 200 && big.truncated)265 #expect(big.columns.map(\.name) == ["x", "y", "s"] && big.columns[0].dtype == "int64" && big.columns[1].dtype == "float64")266 #expect(big.rows[1] == ["1", "1.5", "é"])267 #expect(r.tables[1].columns.first?.dtype == "index" && r.tables[1].rows[0][0] == "é" && !r.tables[1].truncated)268 #expect(r.tables[2].columns.map(\.name) == ["y"])269 #expect(r.tables[3].rows == [["1", "0"], ["0", "1"]])270 #expect(r.stdout.contains("3.14"))271 })272 }273274 @Test(.enabled(Interp.noPandas) { await Interp.scientific != nil })275 func previewRowLimitIsConfigurable() async throws {276 let py = try #require(await Interp.scientific)277 let r = try await runPython("import orix, pandas as pd\norix.show(pd.DataFrame({'a': range(50)}))", on: py, configure: { $0.previewRows = 7 })278 #expect(r.tables.first?.rows.count == 7 && r.tables.first?.rowCount == 50)279 }280281 @Test(.enabled(Interp.noPython) { await Interp.any != nil })282 func unicodeEverywhere() async throws {283 let py = try #require(await Interp.any)284 let host = FakeHost([.init(name: "Données 📊", rows: [[.string("Prénom"), .string("日本語")], [.string("Zoë \"q\" \\ \n\t"), .string("🚀")]])])285 let box = OutputBox()286 let r = try await runPython("""287 import orix, sys288 s = orix.active_sheet()289 v = s.range("A1:B2").values290 print(s.name, v[0][0], v[0][1], v[1][1])291 print("stderr: çà et là — 🚀", file=sys.stderr)292 assert v[1][0] == 'Zoë "q" \\\\ \\n\\t', repr(v[1][0])293 s.range("D1").write([v[1][0], "🚀 ünïcödé"])294 """, on: py, host: host, configure: { $0.scriptName = "analyse été.py" }, output: box)295 #expect(r.succeeded, "\(r.stderr)")296 #expect(r.stdout == "Données 📊 Prénom 日本語 🚀\n")297 #expect(box.stdout == r.stdout && box.stderr == "stderr: çà et là — 🚀\n")298 #expect(r.writes[0].rows == [[.string("Zoë \"q\" \\ \n\t"), .string("🚀 ünïcödé")]])299 #expect(r.writes[0].sheet == "Données 📊")300 }301302 @Test(.enabled(Interp.noPandas) { await Interp.scientific != nil })303 func briefExampleWorksVerbatim() async throws {304 let py = try #require(await Interp.scientific)305 let r = try await runPython("""306 import orix307 sheet = orix.active_sheet()308 data = sheet.range("A1:D100").to_dataframe()309 result = data.groupby("Category")["Revenue"].sum()310 sheet.range("F1").write(result)311 orix.sheet("Data").range("A1").write([[1, 2], [3, 4]]); orix.sheets(); orix.selection(); sheet.used_range(); sheet.range("A1").write_formula("=SUM(B:B)"); orix.show(data)312 """, on: py, host: salesHost())313 #expect(r.succeeded, "\(r.stderr)")314 #expect(r.writes.count == 3 && r.tables.count == 1)315 #expect(cellsText(r.writes[0].rows) == "Category,Revenue;A,40.5;B,20;C,0")316 }317}318