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