def test_write_excel__raises_error_on_invalid_style_spec(
tmp_path, err_msg_match, style_spec):
# Make a table
t = Table(name="foo")
t["place"] = ["home", "work", "beach", "wonderland"]
t.add_column("distance", list(range(3)) + [float("nan")], "km")
# Invalid font size
with pytest.raises(ValueError, match=err_msg_match):
write_excel([t],
tmp_path / "foo_invalid_style.xlsx",
styles=style_spec)
def test__table_to_csv():
# Make a table with content of various units
t = Table(name="foo")
t["place"] = ["home", "work", "beach", "wonderland"]
t.add_column("distance", list(range(3)) + [float("nan")], "km")
t.add_column(
"ETA",
pd.to_datetime([
"2020-08-04 08:00", "2020-08-04 09:00", "2020-08-04 17:00", pd.NaT
]),
"datetime",
)
t.add_column("is_hot", [True, False, True, False], "onoff")
# Write table to stream
with io.StringIO() as out:
_table_to_csv(t, out, ";", "-")
# Assert stream content is as expected
assert out.getvalue() == dedent("""\
**foo;
all
place;distance;ETA;is_hot
text;km;datetime;onoff
home;0.0;2020-08-04 08:00:00;1
work;1.0;2020-08-04 09:00:00;0
beach;2.0;2020-08-04 17:00:00;1
wonderland;-;-;0
""")
def test__table_to_csv__writes_transposed_table():
# Make a TRANSPOSED table with content of various units
t = Table(name="foo")
t["place"] = ["home", "work", "beach", "wonderland"]
t.add_column("distance", list(range(3)) + [float("nan")], "km")
t.add_column(
"ETA",
pd.to_datetime(["2020-08-04 08:00", "2020-08-04 09:00", "2020-08-04 17:00", pd.NaT]),
"datetime",
)
t.add_column("is_hot", [True, False, True, False], "onoff")
t.metadata.transposed = True # <<<< aha
# Write transposed table to stream
with io.StringIO() as out:
_table_to_csv(t, out, ";", "-")
# Stream content is as expected
assert out.getvalue() == dedent(
"""\
**foo*;
all
place;text;home;work;beach;wonderland
distance;km;0.0;1.0;2.0;-
ETA;datetime;2020-08-04 08:00:00;2020-08-04 09:00:00;2020-08-04 17:00:00;-
is_hot;onoff;1;0;1;0
"""
)
def test_write_csv__writes_to_file(tmp_path):
# Make a table
t2 = Table(
pd.DataFrame({
"number": [1, 6, 42],
"spelling": ["one", "six", "forty-two"]
}),
name="bar",
units=["-", "text"],
)
# Write to file
out_path = tmp_path / "write_csv_to_file.csv"
write_csv(t2, out_path)
# Now check file contents
assert out_path.read_text() == dedent("""\
**bar;
all
number;spelling
-;text
1;one
6;six
42;forty-two
""")
# Teardown
out_path.unlink()
def test_write_csv__leaves_stream_open_if_caller_passes_stream():
# Make a table
t2 = Table(
pd.DataFrame({
"number": [1, 6, 42],
"spelling": ["one", "six", "forty-two"]
}),
name="bar",
units=["-", "text"],
)
# Check write_csv single table and leave stream open for business
with io.StringIO() as out:
write_csv(t2, out)
out.write("Fin\n")
assert out.getvalue() == dedent("""\
**bar;
all
number;spelling
-;text
1;one
6;six
42;forty-two
Fin
""")
def test_write_csv__with_format_specs():
# Make a table
t2 = Table(
pd.DataFrame({
"numbers": [1, 6, 42],
"same_numbers": [1, 6, 42],
"others": [1, 123.456, 42]
}),
name="bar",
units=["-", "-", "-"],
)
# Give formats to some columns, leave some without formats
t2.column_metadata["same_numbers"].display_format = ColumnFormat(2)
t2.column_metadata["others"].display_format = ColumnFormat("14.3e")
with io.StringIO() as out:
write_csv(t2, out)
assert out.getvalue() == dedent("""\
**bar;
all
numbers;same_numbers;others
-;-;-
1;1.00; 1.000e+00
6;6.00; 1.235e+02
42;42.00; 4.200e+01
""")
def test_write_excel__transposed_table_units_and_values_are_centered_by_default(
tmp_path):
# Make a table
t = Table(name="foo")
t["place"] = ["home", "work", "beach", "wonderland"]
t.add_column("distance", list(range(3)) + [float("nan")], "km")
t.metadata.transposed = True
nc = len(t.column_names)
nr = len(t.df)
# DEFAULT ALIGNMENT: CENTER
# Write tables to workbook with default styles, save, and re-load
out_path = tmp_path / "foo_custom_style.xlsx"
write_excel([t], out_path, styles=True) # <<< Default style
wb = openpyxl.load_workbook(out_path)
ws = wb.active
# column units and values are centered
assert [ws.cell(2 + c, 2).alignment.horizontal
for c in range(1, nc + 1)] == ["center"] * nc
assert [[
ws.cell(2 + c, 2 + r).alignment.horizontal for c in range(1, nc + 1)
] for r in range(1, nr + 1)] == [["center"] * nc] * nr
# DEFAULT ALIGNMENT NOT APPLIED when custom alignment is specified
# Write tables to workbook with custom alignment styles, save, and re-load
out_path = tmp_path / "foo_custom_style.xlsx"
left = {"alignment": {"horizontal": "left"}}
write_excel([t], out_path, styles={
"units": left,
"values": left
}) # << Custom alignment
wb = openpyxl.load_workbook(out_path)
ws = wb.active
# column units and values are not centered
assert [ws.cell(2 + c, 2).alignment.horizontal
for c in range(1, nc + 1)] == ["left"] * nc
assert [[
ws.cell(2 + c, 2 + r).alignment.horizontal for c in range(1, nc + 1)
] for r in range(1, nr + 1)] == [["left"] * nc] * nr
def make_table(cells: CellGrid, origin: Optional[TableOriginCSV] = None, **kwargs) -> Table:
"""Parses cell grid into a pdtable-style Table block object."""
json_precursor = make_table_json_precursor(cells, origin=origin, **kwargs)
return Table(
frame.make_table_dataframe(
pd.DataFrame(json_precursor["columns"]),
units=json_precursor["units"],
table_metadata=TableMetadata(
name=json_precursor["name"],
destinations=set(json_precursor["destinations"].keys()),
origin=json_precursor["origin"],
),
)
)
def test_make_table__with_backslashes():
cells = [[cell.strip() for cell in line.split(";")] for line in dedent(r"""
**input_files_derived;
all;
file_bytes;file_date;file_name;has_table;
-;text;text;onoff;
15373;20190516T104445;PISA_Library\results\check_Soil_Plastic_ULS1-PISA_C1.csv;1;
15326;20190516T104445;PISA_Library\results\check_Soil_Plastic_ULS1-PISA_C2.csv;1;
""").strip().split("\n")]
t = make_table(cells).df
assert t.file_bytes[0] == 15373
tt = Table(t)
assert tt.name == "input_files_derived"
assert set(tt.metadata.destinations) == {"all"}
assert tt.units == ["-", "text", "text", "onoff"]
def test_make_table__parses_onoff_column():
cells = [[cell.strip() for cell in line.split(";")] for line in dedent(r"""
**input_files_derived;
all;
file_bytes;file_date;has_table;
-;text;onoff;
15373;a;0;
15326;b;1;
""").strip().split("\n")]
table_df = make_table(cells).df
assert table_df.file_bytes[0] == 15373
assert not table_df.has_table[0]
assert table_df.has_table[1]
tt = Table(table_df)
assert tt.name == "input_files_derived"
assert set(tt.metadata.destinations) == {"all"}
assert tt.units == ["-", "text", "onoff"]
def test__append_table_to_openpyxl_worksheet():
# Make a table with content of various units
t = Table(name="foo")
t["place"] = ["home", "work", "beach", "wonderland"]
t.add_column("distance", list(range(3)) + [float("nan")], "km")
t.add_column(
"ETA",
pd.to_datetime([
"2020-08-04 08:00", "2020-08-04 09:00", "2020-08-04 17:00", pd.NaT
]),
"datetime",
)
t.add_column("is_hot", [True, False, True, False], "onoff")
wb = openpyxl.Workbook()
ws = wb.active
# Act
_append_table_to_openpyxl_worksheet(t, ws, sep_lines=1)
# Assert worksheet looks as expected:
# table header by row
assert ws["A1"].value == "**foo"
assert ws["A2"].value == "all"
assert [ws.cell(3, c).value
for c in range(1, 5)] == ["place", "distance", "ETA", "is_hot"]
assert [ws.cell(4, c).value
for c in range(1, 5)] == ["text", "km", "datetime", "onoff"]
# table data by column
assert [ws.cell(r, 1).value
for r in range(5, 9)] == ["home", "work", "beach", "wonderland"]
assert [ws.cell(r, 2).value for r in range(5, 9)] == [0, 1, 2, "-"]
assert [ws.cell(r, 3).value for r in range(5, 9)] == list(
pd.to_datetime([
"2020-08-04 08:00", "2020-08-04 09:00", "2020-08-04 17:00"
])) + ["-"]
assert [ws.cell(r, 4).value for r in range(5, 9)] == [1, 0, 1, 0]
def test__write_csv__uses_altered_default_csv_separator(monkeypatch):
# Change the default CSV separator
# Using monkeypatch in lieu of just 'tables.CSV_SEP = ","'
# so that this alteration of the default is only visible in this test.
monkeypatch.setattr(pdtable, "CSV_SEP", ",")
# Make a table with content of various units
t = Table(
pd.DataFrame(
{
"place": ["home", "work", "beach", "wonderland"],
"distance": list(range(3)) + [float("nan")],
"ETA": pd.to_datetime(
["2020-08-04 08:00", "2020-08-04 09:00", "2020-08-04 17:00", pd.NaT]
),
"is_hot": [True, False, True, False],
}
),
name="foo",
units=["text", "km", "datetime", "onoff"],
)
# Write table to stream
with io.StringIO() as out:
write_csv(t, out)
# Assert stream content is as expected
assert out.getvalue() == dedent(
"""\
**foo,
all
place,distance,ETA,is_hot
text,km,datetime,onoff
home,0.0,2020-08-04 08:00:00,1
work,1.0,2020-08-04 09:00:00,0
beach,2.0,2020-08-04 17:00:00,1
wonderland,-,-,0
"""
)
def test_write_excel(tmp_path):
# Make a couple of tables
t = Table(name="foo")
t["place"] = ["home", "work", "beach", "wonderland"]
t.add_column("distance", list(range(3)) + [float("nan")], "km")
t.add_column(
"ETA",
pd.to_datetime([
"2020-08-04 08:00", "2020-08-04 09:00", "2020-08-04 17:00", pd.NaT
]),
"datetime",
)
t.add_column("is_hot", [True, False, True, False], "onoff")
# This one is transposed
t2 = Table(name="bar")
t2.add_column("digit", [1, 6, 42], "-")
t2.add_column("spelling", ["one", "six", "forty-two"], "text")
t2.metadata.transposed = True
# Write tables to workbook, save, and re-load
out_path = tmp_path / "foo.xlsx"
write_excel([t, t2], out_path)
wb = openpyxl.load_workbook(out_path)
ws = wb.active
# First table is written as expected
# - table header by row
assert ws["A1"].value == "**foo"
assert ws["A2"].value == "all"
assert [ws.cell(3, c).value
for c in range(1, 5)] == ["place", "distance", "ETA", "is_hot"]
assert [ws.cell(4, c).value
for c in range(1, 5)] == ["text", "km", "datetime", "onoff"]
# - table data by column
assert [ws.cell(r, 1).value
for r in range(5, 9)] == ["home", "work", "beach", "wonderland"]
assert [ws.cell(r, 2).value for r in range(5, 9)] == [0, 1, 2, "-"]
for r, d in zip(
range(5, 8),
pd.to_datetime(
["2020-08-04 08:00", "2020-08-04 09:00", "2020-08-04 17:00"])):
# workaround openpyxl bug: https://foss.heptapod.net/openpyxl/openpyxl/-/issues/1493
# openpyxl adds a spurious microsecond to some datetimes.
assert abs(ws.cell(r, 3).value -
d) <= datetime.timedelta(microseconds=1)
assert ws.cell(8, 3).value == "-"
assert [ws.cell(r, 4).value for r in range(5, 9)] == [1, 0, 1, 0]
# Second table is there as well
assert ws["A10"].value == "**bar*"
assert ws["A11"].value == "all"
# column headers (transposed)
assert [ws.cell(r, 1).value
for r in range(12, 14)] == ["digit", "spelling"]
assert [ws.cell(r, 2).value for r in range(12, 14)] == ["-", "text"]
# column values (transposed)
assert [ws.cell(12, c).value for c in range(3, 6)] == [1, 6, 42]
assert [ws.cell(13, c).value
for c in range(3, 6)] == ["one", "six", "forty-two"]
# Teardown
out_path.unlink()
def test_write_excel__multiple_sheets(tmp_path):
"""write_excel() can write tables to multiple sheets in a workbook"""
# Make a couple of tables
t = Table(name="foo")
t["place"] = ["home", "work", "beach", "wonderland"]
t.add_column("distance", list(range(3)) + [float("nan")], "km")
t.add_column(
"ETA",
pd.to_datetime([
"2020-08-04 08:00", "2020-08-04 09:00", "2020-08-04 17:00", pd.NaT
]),
"datetime",
)
t.add_column("is_hot", [True, False, True, False], "onoff")
t2 = Table(name="bar")
t2.add_column("digit", [1, 6, 42], "-")
t2.add_column("spelling", ["one", "six", "forty-two"], "text")
# Write tables to workbook, save, and re-load
out_path = tmp_path / "foo.xlsx"
write_excel({"sheet_one": [t, t2], "sheet_two": t2}, out_path)
wb = openpyxl.load_workbook(out_path)
# Workbook has the expected sheets
assert len(wb.worksheets) == 2
assert wb.sheetnames == ["sheet_one", "sheet_two"]
# First sheet contains the expected tables
assert wb.worksheets[0]["A1"].value == "**foo"
assert wb.worksheets[0]["A10"].value == "**bar"
# Second sheet contains the expected tables
assert wb.worksheets[1]["A1"].value == "**bar"
# Table details are tested elsewhere.
# Teardown
out_path.unlink()
def test_write_excel__sep_lines(tmp_path):
# Make a couple of tables
t = Table(name="foo")
t["place"] = ["home", "work", "beach", "wonderland"]
t.add_column("distance", list(range(3)) + [float("nan")], "km")
t.add_column(
"ETA",
pd.to_datetime([
"2020-08-04 08:00", "2020-08-04 09:00", "2020-08-04 17:00", pd.NaT
]),
"datetime",
)
t.add_column("is_hot", [True, False, True, False], "onoff")
# This one is transposed
t2 = Table(name="bar")
t2.add_column("digit", [1, 6, 42], "-")
t2.add_column("spelling", ["one", "six", "forty-two"], "text")
t2.metadata.transposed = True
# This one is also transposed
t3 = Table(name="bas")
t3.add_column("digit", [1, 6, 42], "-")
t3.add_column("spelling", ["one", "six", "forty-two"], "text")
t3.metadata.transposed = True
# Write tables to workbook, save, and re-load
out_path = tmp_path / "foo.xlsx"
write_excel([t, t2, t3], out_path, sep_lines=2)
wb = openpyxl.load_workbook(out_path)
ws = wb.active
# Tables start on the expected rows
assert ws["A1"].value == "**foo"
assert ws["A11"].value == "**bar*"
assert ws["A17"].value == "**bas*"
def test_write_excel__custom_style(tmp_path):
# Make a table
t = Table(name="foo")
t["place"] = ["home", "work", "beach", "wonderland"]
t.add_column("distance", list(range(3)) + [float("nan")], "km")
nc = len(t.column_names)
nr = len(t.df)
# Make a style specification as a JSON-like data structure
style_spec = {
"table_name": {
"font": {
"name": "Times New Roman",
"size": 24,
"color": "FF0000", # RGB hex color code
"bold": True,
},
"fill": {
"color":
"00AAAAAA", # leading 'aa' transparency values are accepted (but unused)
},
},
"destinations": {
"font": {
"italic": True,
"color": "0000FF",
},
"fill": {
"color": "888888",
},
},
"column_names": {
"font": {
"color": "444400",
"bold": True,
},
"fill": {
"color": "777777",
},
},
"units": {
"font": {
"color": "440044",
}, # --------------------- fill unspecified, leave untouched
},
"values": {
"alignment": {
"horizontal": "left",
},
"fill": {
"color": "EEEEEE",
}, # --------------------- font unspecified, leave untouched
},
}
# Write tables to workbook, save, and re-load
out_path = tmp_path / "foo_custom_style.xlsx"
write_excel([t], out_path, styles=style_spec)
wb = openpyxl.load_workbook(out_path)
ws = wb.active
# Check table formatting
# table name
assert ws["A1"].fill.fill_type == "solid"
assert ws["A1"].fill.start_color.value == "00AAAAAA"
assert ws["A1"].font.size == 24
assert ws["A1"].font.color.value == "00FF0000"
assert ws["A1"].font.name == "Times New Roman"
assert ws["A1"].font.bold is True
# destinations
assert ws["A2"].fill.fill_type == "solid"
assert ws["A2"].fill.start_color.value == "00888888"
assert ws["A2"].font.color.value == "000000FF"
assert ws["A2"].font.bold is False
assert ws["A2"].font.italic is True
# column names
assert [ws.cell(3, c).fill.fill_type
for c in range(1, nc + 1)] == ["solid"] * nc
assert [ws.cell(3, c).fill.start_color.value
for c in range(1, nc + 1)] == ["00777777"] * nc
assert [ws.cell(3, c).font.color.value
for c in range(1, nc + 1)] == ["00444400"] * nc
assert [ws.cell(3, c).font.bold for c in range(1, nc + 1)] == [True] * nc
# column units
assert [ws.cell(4, c).fill.fill_type
for c in range(1, nc + 1)] == [None] * nc # left as default
assert [ws.cell(4, c).font.color.value
for c in range(1, nc + 1)] == ["00440044"] * nc
assert [ws.cell(4, c).font.bold for c in range(1, nc + 1)] == [False] * nc
assert [ws.cell(4, c).alignment.horizontal
for c in range(1, nc + 1)] == [None] * nc
# values
assert [[ws.cell(4 + r, c).fill.fill_type for c in range(1, nc + 1)]
for r in range(1, nr + 1)] == [["solid"] * nc] * nr
assert [[
ws.cell(4 + r, c).fill.start_color.value for c in range(1, nc + 1)
] for r in range(1, nr + 1)] == [["00EEEEEE"] * nc] * nr
assert [[ws.cell(4 + r, c).alignment.horizontal for c in range(1, nc + 1)]
for r in range(1, nr + 1)] == [["left"] * nc] * nr
def test_write_csv__writes_two_tables():
# Make a couple of tables
t = Table(name="foo")
t["place"] = ["home", "work", "beach", "wonderland"]
t.add_column("distance", list(range(3)) + [float("nan")], "km")
t.add_column(
"ETA",
pd.to_datetime([
"2020-08-04 08:00", "2020-08-04 09:00", "2020-08-04 17:00", pd.NaT
]),
"datetime",
)
t.add_column("is_hot", [True, False, True, False], "onoff")
t2 = Table(name="bar")
t2.add_column("number", [1, 6, 42], "-")
t2.add_column("spelling", ["one", "six", "forty-two"], "text")
# Write tables to stream
with io.StringIO() as out:
write_csv([t, t2], out)
# Assert stream content is as expected
assert out.getvalue() == dedent("""\
**foo;
all
place;distance;ETA;is_hot
text;km;datetime;onoff
home;0.0;2020-08-04 08:00:00;1
work;1.0;2020-08-04 09:00:00;0
beach;2.0;2020-08-04 17:00:00;1
wonderland;-;-;0
**bar;
all
number;spelling
-;text
1;one
6;six
42;forty-two
""")
