def test_read_write_excel__round_trip_with_styles(tmp_path):
"""Round-trip reading and writing and re-reading preserves tables"""
from pdtable import TableBundle, read_excel
bundle = TableBundle(read_excel("pdtable/test/io/input/foo.xlsx"))
out_path = tmp_path / "foo_styled.xlsx"
# Doesn't crash on write
write_excel(bundle, out_path, styles=True)
# Re-read bundle is same as first one
bundle2 = TableBundle(read_excel(out_path))
for t, t2 in zip(bundle, bundle2):
assert t.equals(t2)
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_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")
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([t, t2], out_path)
wb = openpyxl.load_workbook(out_path)
ws = wb.active
# Assert loaded 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, "-"]
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]
# Teardown
out_path.unlink()
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 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_excel__style(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, styles=True)
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"]
# Check table formatting
assert ws["A1"].fill.fill_type == "solid"
assert ws["A1"].fill.start_color.value == "00D9D9D9"
assert ws["A1"].font.color.value == "001F4E78"
assert ws["A1"].font.bold is True
assert ws["A2"].fill.fill_type == "solid"
assert ws["A2"].fill.start_color.value == "00D9D9D9"
assert ws["A2"].font.color.value == "00808080"
assert ws["A2"].font.bold is True
assert [ws.cell(3, c).fill.fill_type for c in range(1, 5)] == ["solid"] * 4
assert [ws.cell(3, c).fill.start_color.value
for c in range(1, 5)] == ["00F2F2F2"] * 4
assert [ws.cell(3, c).font.bold for c in range(1, 5)] == [True] * 4
assert [ws.cell(4, c).fill.fill_type for c in range(1, 5)] == ["solid"] * 4
assert [ws.cell(4, c).fill.start_color.value
for c in range(1, 5)] == ["00F2F2F2"] * 4
assert [ws.cell(4, c).font.bold for c in range(1, 5)] == [False] * 4
# - table data by column
assert [ws.cell(r, 1).value
for r in range(5, 9)] == ["home", "work", "beach", "wonderland"]
assert [ws.cell(r, 1).fill.fill_type for r in range(5, 9)] == [None] * 4
assert [ws.cell(r, 1).font.bold for r in range(5, 9)] == [False] * 4
assert [ws.cell(r, 2).value for r in range(5, 9)] == [0, 1, 2, "-"]
assert [ws.cell(r, 2).fill.fill_type for r in range(5, 9)] == [None] * 4
assert [ws.cell(r, 2).font.bold for r in range(5, 9)] == [False] * 4
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(r, 3).fill.fill_type is None
assert ws.cell(r, 3).font.bold is False
assert ws.cell(8, 3).value == "-"
assert [ws.cell(r, 4).value for r in range(5, 9)] == [1, 0, 1, 0]
assert [ws.cell(r, 4).fill.fill_type for r in range(5, 9)] == [None] * 4
assert [ws.cell(r, 4).font.bold for r in range(5, 9)] == [False] * 4
# Second table is there as well
assert ws["A10"].value == "**bar*"
assert ws["A10"].fill.fill_type == "solid"
assert ws["A10"].fill.start_color.value == "00D9D9D9"
assert ws["A10"].font.color.value == "001F4E78"
assert ws["A10"].font.bold is True
assert ws["A11"].value == "all"
assert ws["A11"].fill.fill_type == "solid"
assert ws["A11"].fill.start_color.value == "00D9D9D9"
assert ws["A11"].font.color.value == "00808080"
assert ws["A11"].font.bold is True
# column headers (transposed)
assert [ws.cell(r, 1).value
for r in range(12, 14)] == ["digit", "spelling"]
assert [ws.cell(r, 1).fill.fill_type
for r in range(12, 14)] == ["solid"] * 2
assert [ws.cell(r, 1).fill.start_color.value
for r in range(12, 14)] == ["00F2F2F2"] * 2
assert [ws.cell(r, 1).font.bold for r in range(12, 14)] == [True] * 2
assert [ws.cell(r, 2).value for r in range(12, 14)] == ["-", "text"]
assert [ws.cell(r, 2).fill.fill_type
for r in range(12, 14)] == ["solid"] * 2
assert [ws.cell(r, 2).fill.start_color.value
for r in range(12, 14)] == ["00F2F2F2"] * 2
assert [ws.cell(r, 2).font.bold for r in range(12, 14)] == [False] * 2
# column values (transposed)
assert [ws.cell(12, c).value for c in range(3, 6)] == [1, 6, 42]
assert [ws.cell(12, c).fill.fill_type for c in range(3, 6)] == [None] * 3
assert [ws.cell(12, c).font.bold for c in range(3, 6)] == [False] * 3
assert [ws.cell(13, c).value
for c in range(3, 6)] == ["one", "six", "forty-two"]
assert [ws.cell(13, c).fill.fill_type for c in range(3, 6)] == [None] * 3
assert [ws.cell(13, c).font.bold for c in range(3, 6)] == [False] * 3
# Third table is there as well
assert ws["A15"].value == "**bas*"
assert ws["A15"].fill.fill_type == "solid"
assert ws["A15"].fill.start_color.value == "00D9D9D9"
assert ws["A15"].font.color.value == "001F4E78"
assert ws["A15"].font.bold is True
assert ws["A16"].value == "all"
assert ws["A16"].fill.fill_type == "solid"
assert ws["A16"].fill.start_color.value == "00D9D9D9"
assert ws["A16"].font.color.value == "00808080"
assert ws["A16"].font.bold is True
# Teardown
out_path.unlink()
