def write2xl(self, ew, title_list, data_list, sheet_name): logging.debug('method: write2xl') logging.debug(title_list) df = pd.DataFrame(columns=title_list, data=data_list) defaults = {'font': utils.fonts.aharoni, 'font_size': 14} list_sf = StyleFrame(df, styler_obj=Styler(**defaults)) # Style the headers of the table header_style = Styler(bold=True, font_size=18) list_sf.apply_headers_style(styler_obj=header_style) # Change the columns width and the rows height list_sf.set_column_width(columns=list_sf.columns, width=30) list_sf.set_row_height(rows=list_sf.row_indexes, height=25) # list_sf.apply_column_style(cols_to_style=title_list) list_sf.to_excel( excel_writer=ew, sheet_name=sheet_name, # Add filters in row 0 to each column. row_to_add_filters=0, # Freeze the columns before column 'A' (=None) and rows above '2' (=1), # columns_and_rows_to_freeze='A2' ).save()
def style_df(df): # Create StyleFrame object that wrap our DataFrame and assign default style. defaults = {'font': utils.fonts.aharoni, 'font_size': 11} sf = StyleFrame(df, styler_obj=Styler(**defaults)) # Style the headers of the table header_style = Styler(bold=True, font_size=11) sf.apply_headers_style(styler_obj=header_style) # Change the columns width and the rows height sf.set_column_width(columns=sf.columns, width=20) sf.set_row_height(rows=sf.row_indexes, height=25) return sf
def convert_uber(filename): df = pd.read_csv(filename) df = df.fillna(0) drivers = set(df['Driver Name']) out_data = [] for driver in drivers: driver_calc = {} driver_data = df[df['Driver Name'] == driver] name = driver_data['Driver Name'].values[0] total = driver_data["Fare"].values.sum() to_parking = total * PARK_PERCENTAGE_UBER total_payment = driver_data["Total Payment"].values.sum() to_driver = total_payment - to_parking driver_calc['name'] = ' '.join(name.split()[:2]) driver_calc['total'] = round(total, 2) driver_calc['total_payment'] = round(total_payment, 2) driver_calc['to_driver'] = round(to_driver, 2) driver_calc['to_parking'] = round(to_parking, 2) out_data.append(driver_calc) driver_out_data = { 'name': [d['name'] for d in out_data], 'total': [d['total'] for d in out_data], 'total_payment': [d['total_payment'] for d in out_data], 'to_driver': [d['to_driver'] for d in out_data], 'to_parking': [d['to_parking'] for d in out_data], } for key in driver_out_data.keys(): if key != 'name': driver_out_data[key].append(round(sum(driver_out_data[key]), 2)) else: driver_out_data[key].append(None) df_out = pd.DataFrame(driver_out_data) sf_out = StyleFrame(df_out) sf_out.set_column_width(columns=sf_out.columns, width=30) out_name, _ = os.path.splitext(filename) excel_writer = StyleFrame.ExcelWriter('%s-out.xlsx' % (out_name)) sf_out.to_excel( excel_writer=excel_writer, header=True, columns=['name', 'total', 'total_payment', 'to_driver', 'to_parking']) excel_writer.save()
def create_style_frame(self, data_frame: pd.DataFrame): defaults = {'font_size': 14} style_frame = StyleFrame(data_frame, styler_obj=Styler(**defaults, shrink_to_fit=False, wrap_text=False)) header_style = Styler(bold=True, font_size=18) style_frame.set_column_width(columns=style_frame.columns, width=35) style_frame.apply_headers_style(styler_obj=header_style) for fbg_name, hex_color in zip(self.fbg_names, HEX_COLORS): style_frame.apply_column_style(cols_to_style=[col for col in data_frame.columns.values if fbg_name in col], styler_obj=Styler(bg_color=hex_color)) temperature_headers = [col for col in data_frame.columns.values if "Temperature" in col] style_frame.apply_column_style(cols_to_style=temperature_headers, styler_obj=Styler(font_color=utils.colors.red)) return style_frame
def style_df_all(df): # Create StyleFrame object that wrap our DataFrame and assign default style. defaults = {'font': utils.fonts.aharoni, 'font_size': 10} sf = StyleFrame(df, styler_obj=Styler(**defaults)) # Style the headers of the table header_style = Styler(bold=True, font_size=11) sf.apply_headers_style(styler_obj=header_style) # Change the columns width and the rows height sf.set_column_width(columns=sf.columns, width=12) sf.set_column_width( columns=['transDate', 'description', 'remark', 'amountMoney'], width=20) sf.set_column_width(columns=["shoppingsheetId", "billId", "relationId"], width=30) sf.set_row_height(rows=sf.row_indexes, height=25) # Set the background color to red where the test marked as 'failed' valid_style = Styler(bg_color=utils.colors.red, font_color=utils.colors.white, **defaults) invalid_style = Styler(bg_color=utils.colors.green, font_color=utils.colors.black, **defaults) # sf.apply_style_by_indexes(indexes_to_style=sf[sf['是否有效流水'] == 0], # cols_to_style='amountMoney', # styler_obj=valid_style) sf.apply_style_by_indexes(indexes_to_style=sf[sf['是否有效流水'] == 1], cols_to_style='amountMoney', styler_obj=invalid_style) # 单元格左对齐 col_style = Styler(horizontal_alignment=utils.horizontal_alignments.left, font_size=10) # sf.set_column_width(columns=["remark"], width=80) # sf.apply_column_style(cols_to_style=["remark", 'description', 'nameOnOppositeCard'], # styler_obj=col_style) return sf
class StyleFrameTest(unittest.TestCase): @classmethod def setUpClass(cls): cls.ew = StyleFrame.ExcelWriter("test.xlsx") cls.style_kwargs = dict( bg_color=utils.colors.blue, bold=True, font="Impact", font_color=utils.colors.yellow, font_size=20, underline=utils.underline.single, ) cls.styler_obj = Styler(**cls.style_kwargs) cls.openpy_style_obj = cls.styler_obj.create_style() def setUp(self): self.sf = StyleFrame({"a": [1, 2, 3], "b": [1, 2, 3]}) self.apply_column_style = partial(self.sf.apply_column_style, **self.style_kwargs) self.apply_style_by_indexes = partial(self.sf.apply_style_by_indexes, **self.style_kwargs) self.apply_headers_style = partial(self.sf.apply_headers_style, **self.style_kwargs) def export_and_get_default_sheet(self): self.sf.to_excel( excel_writer=self.ew, right_to_left=True, columns_to_hide=self.sf.columns[0], row_to_add_filters=0, columns_and_rows_to_freeze="A2", allow_protection=True, ) return self.ew.book.get_sheet_by_name("Sheet1") def test_init_styler_obj(self): self.sf = StyleFrame({"a": [1, 2, 3], "b": [1, 2, 3]}, styler_obj=self.styler_obj) self.assertTrue(all(self.sf.ix[index, "a"].style == self.openpy_style_obj for index in self.sf.index)) sheet = self.export_and_get_default_sheet() self.assertTrue( all( sheet.cell(row=i, column=j).style == self.openpy_style_obj for i in range(2, len(self.sf)) for j in range(1, len(self.sf.columns)) ) ) def test_init_dataframe(self): self.assertIsInstance(StyleFrame(pd.DataFrame({"a": [1, 2, 3], "b": [1, 2, 3]})), StyleFrame) self.assertIsInstance(StyleFrame(pd.DataFrame()), StyleFrame) def test_init_styleframe(self): self.assertIsInstance(StyleFrame(StyleFrame({"a": [1, 2, 3]})), StyleFrame) def test_len(self): self.assertEqual(len(self.sf), len(self.sf.data_df)) self.assertEqual(len(self.sf), 3) def test_apply_column_style(self): self.apply_column_style(cols_to_style=["a"]) self.assertTrue( all( [ self.sf.ix[index, "a"].style == self.openpy_style_obj and self.sf.ix[index, "b"].style != self.openpy_style_obj for index in self.sf.index ] ) ) sheet = self.export_and_get_default_sheet() # range starts from 2 since we don't want to check the header's style self.assertTrue(all(sheet.cell(row=i, column=1).style == self.openpy_style_obj for i in range(2, len(self.sf)))) def test_apply_style_by_indexes_single_col(self): self.apply_style_by_indexes(self.sf[self.sf["a"] == 2], cols_to_style=["a"]) self.assertTrue( all( [ self.sf.ix[index, "a"].style == self.openpy_style_obj for index in self.sf.index if self.sf.ix[index, "a"] == 2 ] ) ) sheet = self.export_and_get_default_sheet() self.assertTrue( all( sheet.cell(row=i, column=1).style == self.openpy_style_obj for i in range(1, len(self.sf)) if sheet.cell(row=i, column=1).value == 2 ) ) def test_apply_style_by_indexes_all_cols(self): self.apply_style_by_indexes(self.sf[self.sf["a"] == 2]) self.assertTrue( all( [ self.sf.ix[index, "a"].style == self.openpy_style_obj for index in self.sf.index if self.sf.ix[index, "a"] == 2 ] ) ) sheet = self.export_and_get_default_sheet() self.assertTrue( all( sheet.cell(row=i, column=j).style == self.openpy_style_obj for i in range(1, len(self.sf)) for j in range(1, len(self.sf.columns)) if sheet.cell(row=i, column=1).value == 2 ) ) def test_apply_headers_style(self): self.apply_headers_style() self.assertEqual(self.sf.columns[0].style, self.openpy_style_obj) sheet = self.export_and_get_default_sheet() self.assertEqual(sheet.cell(row=1, column=1).style, self.openpy_style_obj) def test_apply_column_style_styler_obj(self): self.sf.apply_column_style(cols_to_style=["a"], styler_obj=self.styler_obj) self.assertTrue( all( [ self.sf.ix[index, "a"].style == self.openpy_style_obj and self.sf.ix[index, "b"].style != self.openpy_style_obj for index in self.sf.index ] ) ) sheet = self.export_and_get_default_sheet() # range starts from 2 since we don't want to check the header's style self.assertTrue(all(sheet.cell(row=i, column=1).style == self.openpy_style_obj for i in range(2, len(self.sf)))) def test_apply_style_by_indexes_single_col_styler_obj(self): self.sf.apply_style_by_indexes(self.sf[self.sf["a"] == 2], cols_to_style=["a"], styler_obj=self.styler_obj) self.assertTrue( all( self.sf.ix[index, "a"].style == self.openpy_style_obj for index in self.sf.index if self.sf.ix[index, "a"] == 2 ) ) sheet = self.export_and_get_default_sheet() self.assertTrue( all( sheet.cell(row=i, column=1).style == self.openpy_style_obj for i in range(1, len(self.sf)) if sheet.cell(row=i, column=1).value == 2 ) ) def test_apply_style_by_indexes_all_cols_styler_obj(self): self.sf.apply_style_by_indexes(self.sf[self.sf["a"] == 2], styler_obj=self.styler_obj) self.assertTrue( all( [ self.sf.ix[index, "a"].style == self.openpy_style_obj for index in self.sf.index if self.sf.ix[index, "a"] == 2 ] ) ) sheet = self.export_and_get_default_sheet() self.assertTrue( all( sheet.cell(row=i, column=j).style == self.openpy_style_obj for i in range(1, len(self.sf)) for j in range(1, len(self.sf.columns)) if sheet.cell(row=i, column=1).value == 2 ) ) def test_apply_headers_style_styler_obj(self): self.sf.apply_headers_style(styler_obj=self.styler_obj) self.assertEqual(self.sf.columns[0].style, self.openpy_style_obj) sheet = self.export_and_get_default_sheet() self.assertEqual(sheet.cell(row=1, column=1).style, self.openpy_style_obj) def test_set_column_width(self): self.sf.set_column_width(columns=["a"], width=20) self.assertEqual(self.sf._columns_width["a"], 20) sheet = self.export_and_get_default_sheet() self.assertEqual(sheet.column_dimensions["A"].width, 20) def test_set_column_width_dict(self): width_dict = {"a": 20, "b": 30} self.sf.set_column_width_dict(width_dict) self.assertEqual(self.sf._columns_width, width_dict) sheet = self.export_and_get_default_sheet() self.assertTrue(all(sheet.column_dimensions[col.upper()].width == width_dict[col] for col in width_dict)) def test_set_row_height(self): self.sf.set_row_height(rows=[1], height=20) self.assertEqual(self.sf._rows_height[1], 20) sheet = self.export_and_get_default_sheet() self.assertEqual(sheet.row_dimensions[1].height, 20) def test_set_row_height_dict(self): height_dict = {1: 20, 2: 30} self.sf.set_row_height_dict(height_dict) self.assertEqual(self.sf._rows_height, height_dict) sheet = self.export_and_get_default_sheet() self.assertTrue(all(sheet.row_dimensions[row].height == height_dict[row] for row in height_dict)) def test_rename(self): original_columns_name = list(self.sf.columns) names_dict = {"a": "A", "b": "B"} # testing rename with inplace = True self.sf.rename(columns=names_dict, inplace=True) self.assertTrue(all(new_col_name in self.sf.columns for new_col_name in names_dict.values())) new_columns_name = list(self.sf.columns) # check that the columns order did not change after renaming self.assertTrue( all( original_columns_name.index(old_col_name) == new_columns_name.index(new_col_name) for old_col_name, new_col_name in names_dict.items() ) ) # using the old name should raise a KeyError with self.assertRaises(KeyError): # noinspection PyStatementEffect self.sf["a"] # testing rename with inplace = False names_dict = {v: k for k, v in names_dict.items()} new_sf = self.sf.rename(columns=names_dict, inplace=False) self.assertTrue(all(new_col_name in new_sf.columns for new_col_name in names_dict.values())) # using the old name should raise a KeyError with self.assertRaises(KeyError): # noinspection PyStatementEffect new_sf["A"]
class StyleFrameTest(unittest.TestCase): @classmethod def setUpClass(cls): cls.ew = StyleFrame.ExcelWriter(TEST_FILENAME) cls.styler_obj_1 = Styler( bg_color=utils.colors.blue, bold=True, font='Impact', font_color=utils.colors.yellow, font_size=20, underline=utils.underline.single, horizontal_alignment=utils.horizontal_alignments.left, vertical_alignment=utils.vertical_alignments.center) cls.styler_obj_2 = Styler(bg_color=utils.colors.yellow) cls.openpy_style_obj_1 = cls.styler_obj_1.create_style() cls.openpy_style_obj_2 = cls.styler_obj_2.create_style() def setUp(self): self.sf = StyleFrame({'a': [1, 2, 3], 'b': [1, 2, 3]}) self.apply_column_style = partial(self.sf.apply_column_style, styler_obj=self.styler_obj_1, width=10) self.apply_style_by_indexes = partial(self.sf.apply_style_by_indexes, styler_obj=self.styler_obj_1, height=10) self.apply_headers_style = partial(self.sf.apply_headers_style, styler_obj=self.styler_obj_1) @classmethod def tearDownClass(cls): try: os.remove(TEST_FILENAME) except OSError as ex: print(ex) def export_and_get_default_sheet(self, save=False): self.sf.to_excel(excel_writer=self.ew, right_to_left=True, columns_to_hide=self.sf.columns[0], row_to_add_filters=0, columns_and_rows_to_freeze='A2', allow_protection=True) if save: self.ew.save() return self.ew.book.get_sheet_by_name('Sheet1') def test_init_styler_obj(self): self.sf = StyleFrame({ 'a': [1, 2, 3], 'b': [1, 2, 3] }, styler_obj=self.styler_obj_1) self.assertTrue( all(self.sf.loc[index, 'a'].style == self.openpy_style_obj_1 for index in self.sf.index)) sheet = self.export_and_get_default_sheet() self.assertTrue( all( sheet.cell(row=i, column=j).style == self.openpy_style_obj_1 for i in range(2, len(self.sf)) for j in range(1, len(self.sf.columns)))) with self.assertRaises(TypeError): StyleFrame({}, styler_obj=1) def test_init_dataframe(self): self.assertIsInstance( StyleFrame(pd.DataFrame({ 'a': [1, 2, 3], 'b': [1, 2, 3] })), StyleFrame) self.assertIsInstance(StyleFrame(pd.DataFrame()), StyleFrame) def test_init_styleframe(self): self.assertIsInstance(StyleFrame(StyleFrame({'a': [1, 2, 3]})), StyleFrame) with self.assertRaises(TypeError): StyleFrame({}, styler_obj=1) def test_len(self): self.assertEqual(len(self.sf), len(self.sf.data_df)) self.assertEqual(len(self.sf), 3) def test_str(self): self.assertEqual(str(self.sf), str(self.sf.data_df)) def test__get_item__(self): self.assertEqual(self.sf['a'].tolist(), self.sf.data_df['a'].tolist()) self.assertTrue(self.sf.data_df[['a', 'b']].equals(self.sf[['a', 'b']].data_df)) def test__getattr__(self): self.assertEqual(self.sf.fillna, self.sf.data_df.fillna) with self.assertRaises(AttributeError): self.sf.non_exisiting_method() def test_apply_column_style(self): # testing some edge cases with self.assertRaises(TypeError): self.sf.apply_column_style(cols_to_style='a', styler_obj=0) with self.assertRaises(KeyError): self.sf.apply_column_style(cols_to_style='non_existing_col', styler_obj=Styler()) # actual tests self.apply_column_style(cols_to_style=['a']) self.assertTrue( all([ self.sf.loc[index, 'a'].style == self.openpy_style_obj_1 and self.sf.loc[index, 'b'].style != self.openpy_style_obj_1 for index in self.sf.index ])) sheet = self.export_and_get_default_sheet() self.assertEqual(sheet.column_dimensions['A'].width, 10) # range starts from 2 since we don't want to check the header's style self.assertTrue( all( sheet.cell(row=i, column=1).style == self.openpy_style_obj_1 for i in range(2, len(self.sf)))) def test_apply_style_by_indexes_single_col(self): with self.assertRaises(TypeError): self.sf.apply_style_by_indexes(indexes_to_style=0, styler_obj=0) self.apply_style_by_indexes(self.sf[self.sf['a'] == 2], cols_to_style=['a']) self.assertTrue( all(self.sf.loc[index, 'a'].style == self.openpy_style_obj_1 for index in self.sf.index if self.sf.loc[index, 'a'] == 2)) sheet = self.export_and_get_default_sheet() self.assertTrue( all( sheet.cell(row=i, column=1).style == self.openpy_style_obj_1 for i in range(1, len(self.sf)) if sheet.cell(row=i, column=1).value == 2)) self.assertEqual(sheet.row_dimensions[3].height, 10) def test_apply_style_by_indexes_all_cols(self): self.apply_style_by_indexes(self.sf[self.sf['a'] == 2]) self.assertTrue( all(self.sf.loc[index, 'a'].style == self.openpy_style_obj_1 for index in self.sf.index if self.sf.loc[index, 'a'] == 2)) sheet = self.export_and_get_default_sheet() self.assertTrue( all( sheet.cell(row=i, column=j).style == self.openpy_style_obj_1 for i in range(1, len(self.sf)) for j in range(1, len(self.sf.columns)) if sheet.cell(row=i, column=1).value == 2)) def test_apply_style_by_indexes_with_single_index(self): self.apply_style_by_indexes(self.sf.index[0]) self.assertTrue( all(self.sf.iloc[0, self.sf.columns.get_loc(col)].style == self.openpy_style_obj_1 for col in self.sf.columns)) sheet = self.export_and_get_default_sheet() # row=2 since sheet start from row 1 and the headers are row 1 self.assertTrue( all( sheet.cell(row=2, column=col).style == self.openpy_style_obj_1 for col in range(1, len(self.sf.columns)))) def test_apply_style_by_indexes_all_cols_with_multiple_indexes(self): self.apply_style_by_indexes([1, 2]) self.assertTrue( all(self.sf.iloc[index, self.sf.columns.get_loc(col)].style == self.openpy_style_obj_1 for index in [1, 2] for col in self.sf.columns)) sheet = self.export_and_get_default_sheet() self.assertTrue( all( sheet.cell(row=i, column=j).style == self.openpy_style_obj_1 for i in [3, 4] # sheet start from row 1 and headers are row 1 for j in range(1, len(self.sf.columns)))) def test_apply_headers_style(self): self.apply_headers_style() self.assertEqual(self.sf.columns[0].style, self.openpy_style_obj_1) sheet = self.export_and_get_default_sheet() self.assertEqual( sheet.cell(row=1, column=1).style, self.openpy_style_obj_1) def test_set_column_width(self): # testing some edge cases with self.assertRaises(TypeError): self.sf.set_column_width(columns='a', width='a') with self.assertRaises(ValueError): self.sf.set_column_width(columns='a', width=-1) # actual tests self.sf.set_column_width(columns=['a'], width=20) self.assertEqual(self.sf._columns_width['a'], 20) sheet = self.export_and_get_default_sheet() self.assertEqual(sheet.column_dimensions['A'].width, 20) def test_set_column_width_dict(self): with self.assertRaises(TypeError): self.sf.set_column_width_dict(None) width_dict = {'a': 20, 'b': 30} self.sf.set_column_width_dict(width_dict) self.assertEqual(self.sf._columns_width, width_dict) sheet = self.export_and_get_default_sheet() self.assertTrue( all(sheet.column_dimensions[col.upper()].width == width_dict[col] for col in width_dict)) def test_set_row_height(self): # testing some edge cases with self.assertRaises(TypeError): self.sf.set_row_height(rows=[1], height='a') with self.assertRaises(ValueError): self.sf.set_row_height(rows=[1], height=-1) with self.assertRaises(ValueError): self.sf.set_row_height(rows=['a'], height=-1) # actual tests self.sf.set_row_height(rows=[1], height=20) self.assertEqual(self.sf._rows_height[1], 20) sheet = self.export_and_get_default_sheet() self.assertEqual(sheet.row_dimensions[1].height, 20) def test_set_row_height_dict(self): with self.assertRaises(TypeError): self.sf.set_row_height_dict(None) height_dict = {1: 20, 2: 30} self.sf.set_row_height_dict(height_dict) self.assertEqual(self.sf._rows_height, height_dict) sheet = self.export_and_get_default_sheet() self.assertTrue( all(sheet.row_dimensions[row].height == height_dict[row] for row in height_dict)) def test_rename(self): with self.assertRaises(TypeError): self.sf.rename(columns=None) original_columns_name = list(self.sf.columns) names_dict = {'a': 'A', 'b': 'B'} # testing rename with inplace = True self.sf.rename(columns=names_dict, inplace=True) self.assertTrue( all(new_col_name in self.sf.columns for new_col_name in names_dict.values())) new_columns_name = list(self.sf.columns) # check that the columns order did not change after renaming self.assertTrue( all( original_columns_name.index(old_col_name) == new_columns_name.index(new_col_name) for old_col_name, new_col_name in names_dict.items())) # using the old name should raise a KeyError with self.assertRaises(KeyError): # noinspection PyStatementEffect self.sf['a'] # testing rename with inplace = False names_dict = {v: k for k, v in names_dict.items()} new_sf = self.sf.rename(columns=names_dict, inplace=False) self.assertTrue( all(new_col_name in new_sf.columns for new_col_name in names_dict.values())) # using the old name should raise a KeyError with self.assertRaises(KeyError): # noinspection PyStatementEffect new_sf['A'] def test_read_excel_no_style(self): self.apply_headers_style() self.export_and_get_default_sheet(save=True) sf_from_excel = StyleFrame.read_excel(TEST_FILENAME) # making sure content is the same self.assertTrue( all( list(self.sf[col]) == list(sf_from_excel[col]) for col in self.sf.columns)) def test_read_excel_style(self): self.apply_headers_style() self.export_and_get_default_sheet(save=True) sf_from_excel = StyleFrame.read_excel(TEST_FILENAME, read_style=True) # making sure content is the same self.assertTrue( all( list(self.sf[col]) == list(sf_from_excel[col]) for col in self.sf.columns)) rows_in_excel = sf_from_excel.data_df.itertuples() rows_in_self = self.sf.data_df.itertuples() # making sure styles are the same self.assertTrue( all(excel_cell.value == self_cell.value for row_in_excel, row_in_self in zip(rows_in_excel, rows_in_self) for excel_cell, self_cell in zip(row_in_excel, row_in_self))) def test_row_indexes(self): self.assertEqual(self.sf.row_indexes, (1, 2, 3, 4)) def test_style_alternate_rows(self): styles = [self.styler_obj_1, self.styler_obj_2] openpy_styles = [self.openpy_style_obj_1, self.openpy_style_obj_2] self.sf.style_alternate_rows(styles) self.assertTrue( all(self.sf.iloc[index.value, 0].style == styles[index % len(styles)].create_style() for index in self.sf.index)) sheet = self.export_and_get_default_sheet() # sheet start from row 1 and headers are row 1, so need to add 2 when iterating self.assertTrue( all( sheet.cell(row=i.value + 2, column=1).style == openpy_styles[i % len(styles)] for i in self.sf.index))
def convert_gett(filename): #все данные из экселя df = pd.read_excel(filename) #уникальные индексы водителей drivers = set(df['ID водителя']) out_data = [] driver_sum = { 'name': 'ИТОГО', 'plan': 0, 'cash': 0, 'driver_salary': 0, 'parking_salary': 0, 'drives': 0, 'tips': 0, 'toll_roads': 0, 'cancels': 0, } for driver in drivers: #данные по водителю driver_data = df[df['ID водителя'] == driver] #отменённые заказы cancels = driver_data['Тип оплаты'].str.contains('Отмененн') #состоявшиеся заказы driver_data_good = driver_data[~cancels] #не состоявшиеся заказы driver_data_cancels = driver_data[cancels] data_good_count = driver_data_good.shape[0] #XX #если прошедших заказов нет (driver_data_good.shape[0] равно 0) if not data_good_count: continue #ФИО name = driver_data['Имя водителя'].values[0] #план по состоявшимся заказам to_drv_total = driver_data['Тариф для водителя всего'].values.sum() #I #план по состоявшимся заказам без чаевых to_drv_without_tips = driver_data_good[ 'Тариф для водителя без чаевых'].values.sum() #O #наличка от клиента from_psgr = driver_data_good['Получено от клиента'].values.sum() #H #чаевые от клиента tips = driver_data_good['Чаевые для водителя'].values.sum() #K #платные дороги toll_roads_array = driver_data_good[ 'Стоимость парковки / платной дороги'].values toll_roads = sum([x for x in toll_roads_array if x >= 0]) #U #отменённые cancels_money = driver_data_cancels[ 'Тариф для водителя всего'].values.sum() #комиссия парка to_parking = (to_drv_total + toll_roads) * PARK_PERCENTAGE_GETT #PP #комиссия gett to_gett = (to_drv_without_tips - toll_roads) * PERCENTAGE_GETT #PG #выдать водителю to_drv_perc = to_drv_total - from_psgr - to_parking - to_gett #SS driver_calc = {} driver_calc['name'] = ' '.join(name.split()[:2]) driver_calc['plan'] = round(to_drv_total, 2) driver_calc['cash'] = round(from_psgr, 2) driver_calc['driver_salary'] = round(to_drv_perc, 2) driver_calc['parking_salary'] = round(to_parking, 2) driver_calc['drives'] = driver_data.shape[0] driver_calc['tips'] = round(tips, 2) driver_calc['toll_roads'] = round(toll_roads, 2) driver_calc['cancels'] = round(cancels_money, 2) out_data.append(driver_calc) driver_sum['plan'] += driver_calc['plan'] driver_sum['cash'] += driver_calc['cash'] driver_sum['driver_salary'] += driver_calc['driver_salary'] driver_sum['parking_salary'] += driver_calc['parking_salary'] driver_sum['drives'] += driver_calc['drives'] driver_sum['tips'] += driver_calc['tips'] driver_sum['toll_roads'] += driver_calc['toll_roads'] driver_sum['cancels'] += driver_calc['cancels'] out_data.append(driver_sum) driver_out_data = { COLLS_NAME['name']: [d['name'] for d in out_data], COLLS_NAME['plan']: [d['plan'] for d in out_data], COLLS_NAME['cash']: [d['cash'] for d in out_data], COLLS_NAME['driver_salary']: [d['driver_salary'] for d in out_data], COLLS_NAME['parking_salary']: [d['parking_salary'] for d in out_data], COLLS_NAME['drives']: [d['drives'] for d in out_data], COLLS_NAME['tips']: [d['tips'] for d in out_data], COLLS_NAME['toll_roads']: [d['toll_roads'] for d in out_data], COLLS_NAME['cancels']: [d['cancels'] for d in out_data], } """ for key in driver_out_data.keys(): if key != 'name': driver_out_data[key].append(round(sum(driver_out_data[key]), 2)) else: driver_out_data[key].append(None) """ df_out = pd.DataFrame(driver_out_data) sf_out = StyleFrame(df_out) sf_out.set_column_width(columns=sf_out.columns, width=20) out_name, ext = os.path.splitext(filename) excel_writer = StyleFrame.ExcelWriter('%s-out%s' % (out_name, ext)) sf_out.to_excel(excel_writer=excel_writer, header=True, columns=[ COLLS_NAME['name'], COLLS_NAME['plan'], COLLS_NAME['cash'], COLLS_NAME['driver_salary'], COLLS_NAME['parking_salary'], COLLS_NAME['drives'], COLLS_NAME['tips'], COLLS_NAME['toll_roads'], COLLS_NAME['cancels'] ]) try: excel_writer.save() except Exception as e: print('Permission denied for output file') print('Error message:\r\n' + str(e)) finally: pass
class StyleFrameTest(unittest.TestCase): @classmethod def setUpClass(cls): cls.default_styler_obj = Styler(wrap_text=False) cls.styler_obj_1 = Styler(bg_color=utils.colors.blue, bold=True, font='Impact', font_color=utils.colors.yellow, font_size=20, underline=utils.underline.single, horizontal_alignment=utils.horizontal_alignments.left, vertical_alignment=utils.vertical_alignments.center, comment_text='styler_obj_1 comment') cls.styler_obj_2 = Styler(bg_color=utils.colors.yellow, comment_text='styler_obj_2 comment') cls.openpy_style_obj_1 = cls.styler_obj_1.to_openpyxl_style()._style cls.openpy_style_obj_2 = cls.styler_obj_2.to_openpyxl_style()._style def setUp(self): self.ew = StyleFrame.ExcelWriter(TEST_FILENAME) self.sf = StyleFrame({'a': ['col_a_row_1', 'col_a_row_2', 'col_a_row_3'], 'b': ['col_b_row_1', 'col_b_row_2', 'col_b_row_3']}, self.default_styler_obj) self.apply_column_style = partial(self.sf.apply_column_style, styler_obj=self.styler_obj_1, width=10) self.apply_style_by_indexes = partial(self.sf.apply_style_by_indexes, styler_obj=self.styler_obj_1, height=10) self.apply_headers_style = partial(self.sf.apply_headers_style, styler_obj=self.styler_obj_1) @classmethod def tearDownClass(cls): try: os.remove(TEST_FILENAME) except OSError as ex: print(ex) def export_and_get_default_sheet(self, save=False): self.sf.to_excel(excel_writer=self.ew, right_to_left=True, columns_to_hide=self.sf.columns[0], row_to_add_filters=0, columns_and_rows_to_freeze='A2', allow_protection=True) if save: self.ew.save() return self.ew.sheets['Sheet1'] def get_cf_rules(self, sheet): conditional_formatting = sheet.conditional_formatting try: return conditional_formatting.cf_rules except AttributeError: return conditional_formatting def test_init_styler_obj(self): self.sf = StyleFrame({'a': [1, 2, 3], 'b': [1, 2, 3]}, styler_obj=self.styler_obj_1) self.assertTrue(all(self.sf.at[index, 'a'].style.to_openpyxl_style()._style == self.openpy_style_obj_1 for index in self.sf.index)) sheet = self.export_and_get_default_sheet() self.assertTrue(all(sheet.cell(row=i, column=j)._style == self.openpy_style_obj_1 for i in range(2, len(self.sf)) for j in range(1, len(self.sf.columns)))) with self.assertRaises(TypeError): StyleFrame({}, styler_obj=1) def test_init_dataframe(self): self.assertIsInstance(StyleFrame(pd.DataFrame({'a': [1, 2, 3], 'b': [1, 2, 3]})), StyleFrame) self.assertIsInstance(StyleFrame(pd.DataFrame()), StyleFrame) def test_init_styleframe(self): self.assertIsInstance(StyleFrame(StyleFrame({'a': [1, 2, 3]})), StyleFrame) with self.assertRaises(TypeError): StyleFrame({}, styler_obj=1) def test_len(self): self.assertEqual(len(self.sf), len(self.sf.data_df)) self.assertEqual(len(self.sf), 3) def test_str(self): self.assertEqual(str(self.sf), str(self.sf.data_df)) def test__getitem__(self): self.assertEqual(self.sf['a'].tolist(), self.sf.data_df['a'].tolist()) self.assertTrue(self.sf.data_df[['a', 'b']].equals(self.sf[['a', 'b']].data_df)) def test__setitem__(self): self.sf['a'] = range(3) self.sf['b'] = range(3, 6) self.sf['c'] = 5 self.sf['d'] = self.sf['a'] + self.sf['b'] self.sf['e'] = self.sf['a'] + 5 self.assertTrue(all(self.sf.applymap(lambda x: isinstance(x, Container)).all())) def test__getattr__(self): self.assertEqual(self.sf.fillna, self.sf.data_df.fillna) self.assertTrue(self.sf['a'].equals(self.sf.a)) with self.assertRaises(AttributeError): self.sf.non_exisiting_method() def test_apply_column_style(self): # testing some edge cases with self.assertRaises(TypeError): # noinspection PyTypeChecker self.sf.apply_column_style(cols_to_style='a', styler_obj=0) with self.assertRaises(KeyError): self.sf.apply_column_style(cols_to_style='non_existing_col', styler_obj=Styler()) # actual tests self.apply_column_style(cols_to_style=['a']) self.assertTrue(all([self.sf.at[index, 'a'].style.to_openpyxl_style()._style == self.openpy_style_obj_1 and self.sf.at[index, 'b'].style.to_openpyxl_style()._style != self.openpy_style_obj_1 for index in self.sf.index])) sheet = self.export_and_get_default_sheet() self.assertEqual(sheet.column_dimensions['A'].width, 10) # range starts from 2 since we don't want to check the header's style self.assertTrue(all(sheet.cell(row=i, column=1)._style == self.openpy_style_obj_1 for i in range(2, len(self.sf)))) def test_apply_column_style_no_override_default_style(self): # testing some edge cases with self.assertRaises(TypeError): # noinspection PyTypeChecker self.sf.apply_column_style(cols_to_style='a', styler_obj=0) with self.assertRaises(KeyError): self.sf.apply_column_style(cols_to_style='non_existing_col', styler_obj=Styler()) # actual tests self.apply_column_style(cols_to_style=['a'], overwrite_default_style=False) self.assertTrue(all([self.sf.at[index, 'a'].style == Styler.combine(self.default_styler_obj, self.styler_obj_1) and self.sf.at[index, 'b'].style == self.default_styler_obj for index in self.sf.index])) sheet = self.export_and_get_default_sheet() self.assertEqual(sheet.column_dimensions['A'].width, 10) # range starts from 2 since we don't want to check the header's style self.assertTrue(all(sheet.cell(row=i, column=1)._style == Styler.combine(self.default_styler_obj, self.styler_obj_1).to_openpyxl_style()._style for i in range(2, len(self.sf)))) def test_apply_style_by_indexes_single_col(self): with self.assertRaises(TypeError): # noinspection PyTypeChecker self.sf.apply_style_by_indexes(indexes_to_style=0, styler_obj=0) self.apply_style_by_indexes(self.sf[self.sf['a'] == 'col_a_row_2'], cols_to_style=['a']) self.assertTrue(all(self.sf.at[index, 'a'].style.to_openpyxl_style()._style == self.openpy_style_obj_1 for index in self.sf.index if self.sf.at[index, 'a'] == 'col_a_row_2')) sheet = self.export_and_get_default_sheet() self.assertTrue(all(sheet.cell(row=i, column=1)._style == self.openpy_style_obj_1 for i in range(1, len(self.sf)) if sheet.cell(row=i, column=1).value == 2)) self.assertEqual(sheet.row_dimensions[3].height, 10) def test_apply_style_by_indexes_all_cols(self): self.apply_style_by_indexes(self.sf[self.sf['a'] == 2]) self.assertTrue(all(self.sf.at[index, 'a'].style.to_openpyxl_style()._style == self.openpy_style_obj_1 for index in self.sf.index if self.sf.at[index, 'a'] == 2)) sheet = self.export_and_get_default_sheet() self.assertTrue(all(sheet.cell(row=i, column=j)._style == self.openpy_style_obj_1 for i in range(1, len(self.sf)) for j in range(1, len(self.sf.columns)) if sheet.cell(row=i, column=1).value == 2)) def test_apply_style_by_indexes_complement_style(self): self.apply_style_by_indexes(self.sf[self.sf['a'] == 'col_a_row_1'], complement_style=self.styler_obj_2) self.assertTrue(all(self.sf.at[index, 'a'].style.to_openpyxl_style()._style == self.openpy_style_obj_1 for index in self.sf.index if self.sf.at[index, 'a'] == 'col_a_row_1')) self.assertTrue(all(self.sf.at[index, 'a'].style.to_openpyxl_style()._style == self.openpy_style_obj_2 for index in self.sf.index if self.sf.at[index, 'a'] != 'col_a_row_1')) def test_apply_style_by_indexes_with_single_index(self): self.apply_style_by_indexes(self.sf.index[0]) self.assertTrue(all(self.sf.iloc[0, self.sf.columns.get_loc(col)].style.to_openpyxl_style()._style == self.openpy_style_obj_1 for col in self.sf.columns)) sheet = self.export_and_get_default_sheet() # row=2 since sheet start from row 1 and the headers are row 1 self.assertTrue(all(sheet.cell(row=2, column=col)._style == self.openpy_style_obj_1 for col in range(1, len(self.sf.columns)))) def test_apply_style_by_indexes_all_cols_with_multiple_indexes(self): self.apply_style_by_indexes([1, 2]) self.assertTrue(all(self.sf.iloc[index, self.sf.columns.get_loc(col)].style.to_openpyxl_style()._style == self.openpy_style_obj_1 for index in [1, 2] for col in self.sf.columns)) sheet = self.export_and_get_default_sheet() self.assertTrue(all(sheet.cell(row=i, column=j)._style == self.openpy_style_obj_1 for i in [3, 4] # sheet start from row 1 and headers are row 1 for j in range(1, len(self.sf.columns)))) def test_apply_headers_style(self): self.apply_headers_style() self.assertEqual(self.sf.columns[0].style.to_openpyxl_style()._style, self.openpy_style_obj_1) sheet = self.export_and_get_default_sheet() self.assertEqual(sheet.cell(row=1, column=1)._style, self.openpy_style_obj_1) def test_set_column_width(self): # testing some edge cases with self.assertRaises(TypeError): # noinspection PyTypeChecker self.sf.set_column_width(columns='a', width='a') with self.assertRaises(ValueError): # noinspection PyTypeChecker self.sf.set_column_width(columns='a', width=-1) # actual tests self.sf.set_column_width(columns=['a'], width=20) self.assertEqual(self.sf._columns_width['a'], 20) sheet = self.export_and_get_default_sheet() self.assertEqual(sheet.column_dimensions['A'].width, 20) def test_set_column_width_dict(self): with self.assertRaises(TypeError): # noinspection PyTypeChecker self.sf.set_column_width_dict(None) width_dict = {'a': 20, 'b': 30} self.sf.set_column_width_dict(width_dict) self.assertEqual(self.sf._columns_width, width_dict) sheet = self.export_and_get_default_sheet() self.assertTrue(all(sheet.column_dimensions[col.upper()].width == width_dict[col] for col in width_dict)) def test_set_row_height(self): # testing some edge cases with self.assertRaises(TypeError): self.sf.set_row_height(rows=[1], height='a') with self.assertRaises(ValueError): self.sf.set_row_height(rows=[1], height=-1) with self.assertRaises(ValueError): self.sf.set_row_height(rows=['a'], height=-1) # actual tests self.sf.set_row_height(rows=[1], height=20) self.assertEqual(self.sf._rows_height[1], 20) sheet = self.export_and_get_default_sheet() self.assertEqual(sheet.row_dimensions[1].height, 20) def test_set_row_height_dict(self): with self.assertRaises(TypeError): # noinspection PyTypeChecker self.sf.set_row_height_dict(None) height_dict = {1: 20, 2: 30} self.sf.set_row_height_dict(height_dict) self.assertEqual(self.sf._rows_height, height_dict) sheet = self.export_and_get_default_sheet() self.assertTrue(all(sheet.row_dimensions[row].height == height_dict[row] for row in height_dict)) def test_rename(self): with self.assertRaises(TypeError): # noinspection PyTypeChecker self.sf.rename(columns=None) original_columns_name = list(self.sf.columns) names_dict = {'a': 'A', 'b': 'B'} # testing rename with inplace = True self.sf.rename(columns=names_dict, inplace=True) self.assertTrue(all(new_col_name in self.sf.columns for new_col_name in names_dict.values())) new_columns_name = list(self.sf.columns) # check that the columns order did not change after renaming self.assertTrue(all(original_columns_name.index(old_col_name) == new_columns_name.index(new_col_name) for old_col_name, new_col_name in names_dict.items())) # using the old name should raise a KeyError with self.assertRaises(KeyError): # noinspection PyStatementEffect self.sf['a'] # testing rename with inplace = False names_dict = {v: k for k, v in names_dict.items()} new_sf = self.sf.rename(columns=names_dict, inplace=False) self.assertTrue(all(new_col_name in new_sf.columns for new_col_name in names_dict.values())) # using the old name should raise a KeyError with self.assertRaises(KeyError): # noinspection PyStatementEffect new_sf['A'] def test_read_excel_no_style(self): self.export_and_get_default_sheet(save=True) sf_from_excel = StyleFrame.read_excel(TEST_FILENAME) # making sure content is the same self.assertTrue(all(list(self.sf[col]) == list(sf_from_excel[col]) for col in self.sf.columns)) def test_read_excel_with_string_sheet_name(self): self.export_and_get_default_sheet(save=True) sf_from_excel = StyleFrame.read_excel(TEST_FILENAME, read_style=True, sheet_name='Sheet1', use_openpyxl_styles=True) # making sure content is the same self.assertTrue(all(list(self.sf[col]) == list(sf_from_excel[col]) for col in self.sf.columns)) rows_in_excel = sf_from_excel.data_df.itertuples() rows_in_self = self.sf.data_df.itertuples() # making sure styles are the same self.assertTrue(all(self_cell.style == Styler.from_openpyxl_style(excel_cell.style, []) for row_in_excel, row_in_self in zip(rows_in_excel, rows_in_self) for excel_cell, self_cell in zip(row_in_excel[1:], row_in_self[1:]))) def test_read_excel_with_style_openpyxl_objects(self): self.export_and_get_default_sheet(save=True) sf_from_excel = StyleFrame.read_excel(TEST_FILENAME, read_style=True, use_openpyxl_styles=True) # making sure content is the same self.assertTrue(all(list(self.sf[col]) == list(sf_from_excel[col]) for col in self.sf.columns)) rows_in_excel = sf_from_excel.data_df.itertuples() rows_in_self = self.sf.data_df.itertuples() # making sure styles are the same self.assertTrue(all(self_cell.style == Styler.from_openpyxl_style(excel_cell.style, []) for row_in_excel, row_in_self in zip(rows_in_excel, rows_in_self) for excel_cell, self_cell in zip(row_in_excel[1:], row_in_self[1:]))) def test_read_excel_with_style_styler_objects(self): self.export_and_get_default_sheet(save=True) sf_from_excel = StyleFrame.read_excel(TEST_FILENAME, read_style=True) # making sure content is the same self.assertTrue(all(list(self.sf[col]) == list(sf_from_excel[col]) for col in self.sf.columns)) rows_in_excel = sf_from_excel.data_df.itertuples() rows_in_self = self.sf.data_df.itertuples() # making sure styles are the same self.assertTrue(all(excel_cell.style == self_cell.style for row_in_excel, row_in_self in zip(rows_in_excel, rows_in_self) for excel_cell, self_cell in zip(row_in_excel[1:], row_in_self[1:]))) def test_read_excel_with_style_comments_openpyxl_objects(self): self.export_and_get_default_sheet(save=True) sf_from_excel = StyleFrame.read_excel(TEST_FILENAME, read_style=True, read_comments=True, use_openpyxl_styles=True) # making sure content is the same self.assertTrue(all(list(self.sf[col]) == list(sf_from_excel[col]) for col in self.sf.columns)) rows_in_excel = sf_from_excel.data_df.itertuples() rows_in_self = self.sf.data_df.itertuples() # making sure styles are the same self.assertTrue(all(self_cell.style == Styler.from_openpyxl_style(excel_cell.style, []) for row_in_excel, row_in_self in zip(rows_in_excel, rows_in_self) for excel_cell, self_cell in zip(row_in_excel[1:], row_in_self[1:]))) def test_read_excel_with_style_comments_styler_objects(self): self.export_and_get_default_sheet(save=True) sf_from_excel = StyleFrame.read_excel(TEST_FILENAME, read_style=True, read_comments=True) # making sure content is the same self.assertTrue(all(list(self.sf[col]) == list(sf_from_excel[col]) for col in self.sf.columns)) rows_in_excel = sf_from_excel.data_df.itertuples() rows_in_self = self.sf.data_df.itertuples() # making sure styles are the same self.assertTrue(all(excel_cell.style == self_cell.style for row_in_excel, row_in_self in zip(rows_in_excel, rows_in_self) for excel_cell, self_cell in zip(row_in_excel[1:], row_in_self[1:]))) def test_row_indexes(self): self.assertEqual(self.sf.row_indexes, (1, 2, 3, 4)) def test_style_alternate_rows(self): styles = [self.styler_obj_1, self.styler_obj_2] openpy_styles = [self.openpy_style_obj_1, self.openpy_style_obj_2] self.sf.style_alternate_rows(styles) self.assertTrue(all(self.sf.iloc[index.value, 0].style.to_openpyxl_style() == styles[index.value % len(styles)].to_openpyxl_style() for index in self.sf.index)) sheet = self.export_and_get_default_sheet() # sheet start from row 1 and headers are row 1, so need to add 2 when iterating self.assertTrue(all(sheet.cell(row=i.value + 2, column=1)._style == openpy_styles[i.value % len(styles)] for i in self.sf.index)) def test_add_color_scale_conditional_formatting_start_end(self): self.sf.add_color_scale_conditional_formatting(start_type=utils.conditional_formatting_types.percentile, start_value=0, start_color=utils.colors.red, end_type=utils.conditional_formatting_types.percentile, end_value=100, end_color=utils.colors.green) sheet = self.export_and_get_default_sheet(save=True) cf_rules = self.get_cf_rules(sheet=sheet) rules_dict = cf_rules['A1:B4'] self.assertEqual(rules_dict[0].type, 'colorScale') self.assertEqual(rules_dict[0].colorScale.color[0].rgb, utils.colors.red) self.assertEqual(rules_dict[0].colorScale.color[1].rgb, utils.colors.green) self.assertEqual(rules_dict[0].colorScale.cfvo[0].type, utils.conditional_formatting_types.percentile) self.assertEqual(rules_dict[0].colorScale.cfvo[0].val, 0.0) self.assertEqual(rules_dict[0].colorScale.cfvo[1].type, utils.conditional_formatting_types.percentile) self.assertEqual(rules_dict[0].colorScale.cfvo[1].val, 100.0) def test_add_color_scale_conditional_formatting_start_mid_end(self): self.sf.add_color_scale_conditional_formatting(start_type=utils.conditional_formatting_types.percentile, start_value=0, start_color=utils.colors.red, mid_type=utils.conditional_formatting_types.percentile, mid_value=50, mid_color=utils.colors.yellow, end_type=utils.conditional_formatting_types.percentile, end_value=100, end_color=utils.colors.green) sheet = self.export_and_get_default_sheet(save=True) cf_rules = self.get_cf_rules(sheet=sheet) rules_dict = cf_rules['A1:B4'] self.assertEqual(rules_dict[0].type, 'colorScale') self.assertEqual(rules_dict[0].colorScale.color[0].rgb, utils.colors.red) self.assertEqual(rules_dict[0].colorScale.color[1].rgb, utils.colors.yellow) self.assertEqual(rules_dict[0].colorScale.color[2].rgb, utils.colors.green) self.assertEqual(rules_dict[0].colorScale.cfvo[0].type, utils.conditional_formatting_types.percentile) self.assertEqual(rules_dict[0].colorScale.cfvo[0].val, 0.0) self.assertEqual(rules_dict[0].colorScale.cfvo[1].type, utils.conditional_formatting_types.percentile) self.assertEqual(rules_dict[0].colorScale.cfvo[1].val, 50.0) self.assertEqual(rules_dict[0].colorScale.cfvo[2].type, utils.conditional_formatting_types.percentile) self.assertEqual(rules_dict[0].colorScale.cfvo[2].val, 100.0)
print( 'Прервано! Отсутствуют данные по коэффициенту неготовности в БД Сибири' ) raise SystemExit df_c_eur['TARGET_DATE'] = df_c_eur['TARGET_DATE'].astype('str') df_c_sib['TARGET_DATE'] = df_c_sib['TARGET_DATE'].astype('str') # Сохраняем отчеты и форматируем их style = Styler(font_size=10, horizontal_alignment=utils.horizontal_alignments.right) columns = df_c_avg.axes[1] df_c_avg = StyleFrame(df_c_avg) excel_writer = StyleFrame.ExcelWriter(path_c_avg) for s in columns: df_c_avg.set_column_width(s, 7 + str(s).__len__()) df_c_avg.apply_column_style(cols_to_style=columns, styler_obj=style, style_header=True) df_c_avg.to_excel(excel_writer=excel_writer, sheet_name='Среднее значение коэффициента', index=False) excel_writer.save() excel_writer.close() columns = df_c_eur.axes[1] df_c_eur = StyleFrame(df_c_eur) df_c_sib = StyleFrame(df_c_sib) excel_writer = StyleFrame.ExcelWriter(path_c) for s in columns: df_c_eur.set_column_width(s, 12 + 0.5 * str(s).__len__())
df_fact_PCHITAZN['ДАТА'] = df_fact_PCHITAZN['ДАТА'].astype('str') conn_eur.close() conn_sib.close() df_Four_x_stor_INT = df_Four_x_stor_INT_eur.append(df_Four_x_stor_INT_sib) # Для PCHITAZN можно этот вариант форматирования массива и записи в xlsx style = Styler(font_size=10, horizontal_alignment=utils.horizontal_alignments.right) columns = df_fact_PCHITAZN.axes[1] PCHITAZN = StyleFrame(df_fact_PCHITAZN) excel_writer = StyleFrame.ExcelWriter(path_PCHITAZN) for s in columns: PCHITAZN.set_column_width(s, 9 + str(s).__len__()) PCHITAZN.apply_column_style(cols_to_style=columns, styler_obj=style, style_header=True) PCHITAZN.to_excel(excel_writer=excel_writer, index=False) excel_writer.save() excel_writer.close() # Для склееного файла почему-то не катит предыдущий вариант форматирования массива данных, поэтому делаем так -> # Экспортируем выгруженные данные в xlsx df_Four_x_stor_INT.to_excel(path_inter, index=False) # Создаем шаблоны стилей border = Border(left=Side(border_style='thin', color='FF000000'),
class StyleFrameTest(unittest.TestCase): @classmethod def setUpClass(cls): cls.ew = StyleFrame.ExcelWriter('test.xlsx') cls.style_kwargs = dict(bg_color=utils.colors.blue, bold=True, font='Impact', font_color=utils.colors.yellow, font_size=20, underline=utils.underline.single) cls.styler_obj = Styler(**cls.style_kwargs) cls.openpy_style_obj = cls.styler_obj.create_style() def setUp(self): self.sf = StyleFrame({'a': [1, 2, 3], 'b': [1, 2, 3]}) self.apply_column_style = partial(self.sf.apply_column_style, **self.style_kwargs) self.apply_style_by_indexes = partial(self.sf.apply_style_by_indexes, **self.style_kwargs) self.apply_headers_style = partial(self.sf.apply_headers_style, **self.style_kwargs) def export_and_get_default_sheet(self): self.sf.to_excel(excel_writer=self.ew, right_to_left=True, columns_to_hide=self.sf.columns[0], row_to_add_filters=0, columns_and_rows_to_freeze='A2', allow_protection=True) return self.ew.book.get_sheet_by_name('Sheet1') def test_init_styler_obj(self): self.sf = StyleFrame({ 'a': [1, 2, 3], 'b': [1, 2, 3] }, styler_obj=self.styler_obj) self.assertTrue( all(self.sf.ix[index, 'a'].style == self.openpy_style_obj for index in self.sf.index)) sheet = self.export_and_get_default_sheet() self.assertTrue( all( sheet.cell(row=i, column=j).style == self.openpy_style_obj for i in range(2, len(self.sf)) for j in range(1, len(self.sf.columns)))) def test_init_dataframe(self): self.assertIsInstance( StyleFrame(pd.DataFrame({ 'a': [1, 2, 3], 'b': [1, 2, 3] })), StyleFrame) self.assertIsInstance(StyleFrame(pd.DataFrame()), StyleFrame) def test_init_styleframe(self): self.assertIsInstance(StyleFrame(StyleFrame({'a': [1, 2, 3]})), StyleFrame) def test_len(self): self.assertEqual(len(self.sf), len(self.sf.data_df)) self.assertEqual(len(self.sf), 3) def test_apply_column_style(self): self.apply_column_style(cols_to_style=['a']) self.assertTrue( all([ self.sf.ix[index, 'a'].style == self.openpy_style_obj and self.sf.ix[index, 'b'].style != self.openpy_style_obj for index in self.sf.index ])) sheet = self.export_and_get_default_sheet() # range starts from 2 since we don't want to check the header's style self.assertTrue( all( sheet.cell(row=i, column=1).style == self.openpy_style_obj for i in range(2, len(self.sf)))) def test_apply_style_by_indexes_single_col(self): self.apply_style_by_indexes(self.sf[self.sf['a'] == 2], cols_to_style=['a']) self.assertTrue( all([ self.sf.ix[index, 'a'].style == self.openpy_style_obj for index in self.sf.index if self.sf.ix[index, 'a'] == 2 ])) sheet = self.export_and_get_default_sheet() self.assertTrue( all( sheet.cell(row=i, column=1).style == self.openpy_style_obj for i in range(1, len(self.sf)) if sheet.cell(row=i, column=1).value == 2)) def test_apply_style_by_indexes_all_cols(self): self.apply_style_by_indexes(self.sf[self.sf['a'] == 2]) self.assertTrue( all([ self.sf.ix[index, 'a'].style == self.openpy_style_obj for index in self.sf.index if self.sf.ix[index, 'a'] == 2 ])) sheet = self.export_and_get_default_sheet() self.assertTrue( all( sheet.cell(row=i, column=j).style == self.openpy_style_obj for i in range(1, len(self.sf)) for j in range(1, len(self.sf.columns)) if sheet.cell(row=i, column=1).value == 2)) def test_apply_headers_style(self): self.apply_headers_style() self.assertEqual(self.sf.columns[0].style, self.openpy_style_obj) sheet = self.export_and_get_default_sheet() self.assertEqual( sheet.cell(row=1, column=1).style, self.openpy_style_obj) def test_apply_column_style_styler_obj(self): self.sf.apply_column_style(cols_to_style=['a'], styler_obj=self.styler_obj) self.assertTrue( all([ self.sf.ix[index, 'a'].style == self.openpy_style_obj and self.sf.ix[index, 'b'].style != self.openpy_style_obj for index in self.sf.index ])) sheet = self.export_and_get_default_sheet() # range starts from 2 since we don't want to check the header's style self.assertTrue( all( sheet.cell(row=i, column=1).style == self.openpy_style_obj for i in range(2, len(self.sf)))) def test_apply_style_by_indexes_single_col_styler_obj(self): self.sf.apply_style_by_indexes(self.sf[self.sf['a'] == 2], cols_to_style=['a'], styler_obj=self.styler_obj) self.assertTrue( all(self.sf.ix[index, 'a'].style == self.openpy_style_obj for index in self.sf.index if self.sf.ix[index, 'a'] == 2)) sheet = self.export_and_get_default_sheet() self.assertTrue( all( sheet.cell(row=i, column=1).style == self.openpy_style_obj for i in range(1, len(self.sf)) if sheet.cell(row=i, column=1).value == 2)) def test_apply_style_by_indexes_all_cols_styler_obj(self): self.sf.apply_style_by_indexes(self.sf[self.sf['a'] == 2], styler_obj=self.styler_obj) self.assertTrue( all([ self.sf.ix[index, 'a'].style == self.openpy_style_obj for index in self.sf.index if self.sf.ix[index, 'a'] == 2 ])) sheet = self.export_and_get_default_sheet() self.assertTrue( all( sheet.cell(row=i, column=j).style == self.openpy_style_obj for i in range(1, len(self.sf)) for j in range(1, len(self.sf.columns)) if sheet.cell(row=i, column=1).value == 2)) def test_apply_headers_style_styler_obj(self): self.sf.apply_headers_style(styler_obj=self.styler_obj) self.assertEqual(self.sf.columns[0].style, self.openpy_style_obj) sheet = self.export_and_get_default_sheet() self.assertEqual( sheet.cell(row=1, column=1).style, self.openpy_style_obj) def test_set_column_width(self): self.sf.set_column_width(columns=['a'], width=20) self.assertEqual(self.sf._columns_width['a'], 20) sheet = self.export_and_get_default_sheet() self.assertEqual(sheet.column_dimensions['A'].width, 20) def test_set_column_width_dict(self): width_dict = {'a': 20, 'b': 30} self.sf.set_column_width_dict(width_dict) self.assertEqual(self.sf._columns_width, width_dict) sheet = self.export_and_get_default_sheet() self.assertTrue( all(sheet.column_dimensions[col.upper()].width == width_dict[col] for col in width_dict)) def test_set_row_height(self): self.sf.set_row_height(rows=[1], height=20) self.assertEqual(self.sf._rows_height[1], 20) sheet = self.export_and_get_default_sheet() self.assertEqual(sheet.row_dimensions[1].height, 20) def test_set_row_height_dict(self): height_dict = {1: 20, 2: 30} self.sf.set_row_height_dict(height_dict) self.assertEqual(self.sf._rows_height, height_dict) sheet = self.export_and_get_default_sheet() self.assertTrue( all(sheet.row_dimensions[row].height == height_dict[row] for row in height_dict)) def test_rename(self): original_columns_name = list(self.sf.columns) names_dict = {'a': 'A', 'b': 'B'} # testing rename with inplace = True self.sf.rename(columns=names_dict, inplace=True) self.assertTrue( all(new_col_name in self.sf.columns for new_col_name in names_dict.values())) new_columns_name = list(self.sf.columns) # check that the columns order did not change after renaming self.assertTrue( all( original_columns_name.index(old_col_name) == new_columns_name.index(new_col_name) for old_col_name, new_col_name in names_dict.items())) # using the old name should raise a KeyError with self.assertRaises(KeyError): # noinspection PyStatementEffect self.sf['a'] # testing rename with inplace = False names_dict = {v: k for k, v in names_dict.items()} new_sf = self.sf.rename(columns=names_dict, inplace=False) self.assertTrue( all(new_col_name in new_sf.columns for new_col_name in names_dict.values())) # using the old name should raise a KeyError with self.assertRaises(KeyError): # noinspection PyStatementEffect new_sf['A']