def test_one_frame(self):
df = pd.DataFrame(
[[1.0, 2.0], [3.0, 4.0]], columns=["c1", "c2"], index=["r1", "r2"]
)
wb = render_template(
"template_one_frame.xlsx",
"output.xlsx",
df=df.reset_index(),
title="MyTitle",
)
for i in range(2):
sheet = wb.sheets[i]
self.assertEqual(sheet["A1"].value, "MyTitle")
self.assertEqual(sheet["A3"].value, "PART ONE")
self.assertEqual(sheet["A8"].value, "PART TWO")
if i == 0:
assert_frame_equal(
sheet["A4"].options(pd.DataFrame, expand="table").value,
df.reset_index().set_index("index"),
)
assert_frame_equal(
sheet["A9"].options(pd.DataFrame, expand="table").value,
df.reset_index().set_index("index"),
)
elif i == 1:
df_table1 = sheet["A4"].options(pd.DataFrame, expand="table").value
df_table1.index.name = None
df_table2 = sheet["A9"].options(pd.DataFrame, expand="table").value
df_table2.index.name = None
assert_frame_equal(df_table1, df)
assert_frame_equal(df_table2, df)
self.assertEqual(sheet["A3"].color, (0, 176, 240))
self.assertEqual(sheet["A8"].color, (0, 176, 240))
def test_update_links_true(self):
wb = render_template(
"template_with_links.xlsx",
"output.xlsx",
book_settings={"update_links": True},
**data,
)
self.assertEqual(wb.sheets[0]["M1"].value, "Updated Text for update_links")
def test_pic_filters(self):
wb = render_template("template1.xlsx", "output.xlsx", **data)
self.assertEqual(wb.sheets["pic_filters"].pictures[0].width, 397)
self.assertEqual(wb.sheets["pic_filters"].pictures[0].height, 139)
self.assertEqual(wb.sheets["pic_filters"].pictures[1].width, 120)
self.assertEqual(int(wb.sheets["pic_filters"].pictures[1].height), 42)
self.assertEqual(int(wb.sheets["pic_filters"].pictures[2].width), 371)
self.assertEqual(wb.sheets["pic_filters"].pictures[2].height, 130)
self.assertEqual(int(wb.sheets["pic_filters"].pictures[3].width), 476)
self.assertEqual(int(wb.sheets["pic_filters"].pictures[3].height), 166)
def test_app_instance(self):
app = xw.App()
wb = render_template(
"template_with_links.xlsx",
"output.xlsx",
app=app,
book_settings={"update_links": False},
**data,
)
self.assertEqual(wb.sheets[0]["M1"].value, "Text for update_links")
wb.app.quit()
def test_df_filters_in_frames(self):
wb = render_template("df_filter_frame.xlsx", "output.xlsx", **data)
self.assertEqual(
wb.sheets["Sheet1"]["A1:E10"].value, wb.sheets["expected"]["A1:E10"].value
)
self.assertEqual(
wb.sheets["Sheet1"]["A3"].color, wb.sheets["expected"]["A3"].color
)
self.assertEqual(
wb.sheets["Sheet1"]["A4:A5"].color, wb.sheets["expected"]["A4:A5"].color
)
self.assertIsNone(wb.sheets["Sheet1"]["A6"].color)
self.assertEqual(
wb.sheets["Sheet1"]["A7"].color, wb.sheets["expected"]["A7"].color
)
self.assertEqual(
wb.sheets["Sheet1"]["A8"].color, wb.sheets["expected"]["A8"].color
)
self.assertIsNone(wb.sheets["Sheet1"]["A7"].color)
self.assertIsNone(wb.sheets["Sheet1"]["A10"].color)
def setUpClass(cls):
cls.wb = render_template("template1.xlsx", "output.xlsx", **data)
def test_fontcolor_filter(self):
wb = render_template("template1.xlsx", "output.xlsx", **data)
self.assertEqual(wb.sheets["Sheet1"]["Q1"].font.color, (255, 255, 255))
def test_datetime_filters(self):
wb = render_template("template1.xlsx", "output.xlsx", **data)
self.assertEqual(wb.sheets["dt"]["A1:A7"].value, wb.sheets["dt"]["E1:E7"].value)
self.assertEqual(wb.sheets["dt"].shapes["Rectangle 1"].text, "December 1, 2010")
def test_df_filters(self):
wb = render_template("template1.xlsx", "output.xlsx", **data)
self.assertEqual(
wb.sheets["df_filters"]["A1:E140"].value,
wb.sheets["df_filters"]["G1:K140"].value,
)
def test_two_frames(self):
df1 = pd.DataFrame(
[[1.0, 2.0, 3.0], [4.0, 5.0, 6.0], [7.0, 8.0, 9.0]],
columns=["c1", "c2", "c3"],
index=["r1", "r2", "r3"],
)
df1.index.name = "df1"
df3 = pd.DataFrame(
[
[1.0, 2.0, 3.0],
[4.0, 5.0, 6.0],
[7.0, 8.0, 9.0],
[10.0, 11.0, 12.0],
[13.0, 14.0, 15.0],
],
columns=["c1", "c2", "c3"],
index=["r1", "r2", "r3", "r4", "r5"],
)
df3.index.name = "df3"
text = "abcd"
pic = Image(os.path.abspath("xlwings.jpg"))
data = dict(
df1=df1.reset_index(),
df2="df2 dummy",
df3=df3.reset_index(),
text=text,
pic=pic,
)
wb = render_template("template_two_frames.xlsx", "output.xlsx", **data)
sheet = wb.sheets[0]
# values
assert_frame_equal(sheet["A1"].options(pd.DataFrame, expand="table").value, df3)
self.assertEqual(sheet["A8"].value, "df2 dummy")
self.assertEqual(sheet["C10"].value, "abcd")
assert_frame_equal(
sheet["A12"].options(pd.DataFrame, expand="table").value, df1
)
assert_frame_equal(
sheet["A17"].options(pd.DataFrame, expand="table").value, df3
)
assert_frame_equal(
sheet["A24"].options(pd.DataFrame, expand="table").value, df3
)
assert_frame_equal(
sheet["A31"].options(pd.DataFrame, expand="table").value, df3
)
assert_frame_equal(sheet["F1"].options(pd.DataFrame, expand="table").value, df1)
self.assertEqual(sheet["G6"].value, "abcd")
assert_frame_equal(sheet["F8"].options(pd.DataFrame, expand="table").value, df3)
assert_frame_equal(
sheet["F15"].options(pd.DataFrame, expand="table").value, df1
)
assert_frame_equal(
sheet["F27"].options(pd.DataFrame, expand="table").value, df1
)
self.assertEqual(sheet["F32"].value, "df2 dummy")
assert_frame_equal(
sheet["F34"].options(pd.DataFrame, expand="table").value, df3
)
# colors
self.assertEqual(sheet["A2:D6"].color, (221, 235, 247))
self.assertEqual(sheet["A13:D15"].color, (221, 235, 247))
self.assertEqual(sheet["A18:D22"].color, (221, 235, 247))
self.assertEqual(sheet["A25:D29"].color, (221, 235, 247))
self.assertEqual(sheet["A32:D36"].color, (221, 235, 247))
self.assertEqual(sheet["F2:I4"].color, (221, 235, 247))
self.assertEqual(sheet["F9:I13"].color, (221, 235, 247))
self.assertEqual(sheet["F16:I18"].color, (221, 235, 247))
self.assertEqual(sheet["F28:I30"].color, (221, 235, 247))
self.assertEqual(sheet["F35:I39"].color, (221, 235, 247))
# borders
# TODO: pending Border implementation in xlwings
if sys.platform.startswith("darwin"):
from appscript import k as kw
for cell in [
"A4",
"A14",
"D20",
"A28",
"D36",
"F4",
"H10",
"G17",
"G28",
"I36",
]:
self.assertEqual(
sheet[cell]
.api.get_border(which_border=kw.edge_top)
.properties()
.get(kw.line_style),
kw.continuous,
)
self.assertEqual(
sheet[cell]
.api.get_border(which_border=kw.edge_bottom)
.properties()
.get(kw.line_style),
kw.continuous,
)
else:
pass
