def test_read_excel_template_with_use_df_boundaries(self): template_sf = StyleFrame(obj={ 'a': ['col_a_row_1', 'col_a_row_2'], 'b': ['col_b_row_1', 'col_b_row_2'] }, styler_obj=self.styler_obj_1) template_sf.to_excel(TEST_FILENAME).save() df = pd.DataFrame(data={ 'A': [1], }, columns=['A']) sf_from_template = StyleFrame.read_excel_as_template( path=TEST_FILENAME, df=df, use_df_boundaries=True, read_comments=True) self.assertListEqual([col.value for col in sf_from_template.columns], ['A']) self.assertEqual(len(df), len(sf_from_template)) expected_cell_style = template_sf['a'][0].style actual_cell_style = sf_from_template['A'][0].style self.assertEqual( actual_cell_style, expected_cell_style, 'Different styles in template cell with style {template_style}' '\nand actual cell with style {actual_cell_style}'.format( template_style=expected_cell_style, actual_cell_style=actual_cell_style)) self.assertEqual(sf_from_template['A'][0].value, 1)
def get_standard_matrix(): filepath = self.story_logic_sheet sf = StyleFrame.read_excel(filepath, read_style=True, use_openpyxl_styles=False) def only_cells_with_red_text(cell): if cell.style.bg_color in {utils.colors.red, 'FFFF0000'}: return 120 sf_2 = StyleFrame(sf.applymap(only_cells_with_red_text)) #print(qualifying_dict) sf_2.to_excel().save() df = pd.read_excel(curdir + '/output.xlsx') df = df.iloc[:self.row_lim, 1] #print(df) standard_matrix = df.values return standard_matrix
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 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)
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 write_tax_report_xlsx(xlsx_file, csv_file, tax_report_df): report_template = StyleFrame.read_excel_as_template(xlsx_file, tax_report_df, sheet_name='자료', engine="openpyxl") report_p2_info = StyleFrame.read_excel(xlsx_file, sheet_name='작성안내', read_style=True, engine="openpyxl") report_p3_tax_code = StyleFrame.read_excel(xlsx_file, sheet_name='양도물건종류별 세율코드', read_style=True, engine="openpyxl") report_p4_stock_type = StyleFrame.read_excel(xlsx_file, sheet_name='양도물건종류별 주식등종류', read_style=True, engine="openpyxl") report_template.apply_column_style( cols_to_style=['양도일자', '취득일자'], styler_obj=Styler(number_format=utils.number_formats.date)) report_template.apply_column_style( cols_to_style=['양도가액', '취득가액', '필요경비'], styler_obj=Styler( number_format=utils.number_formats.thousands_comma_sep)) # 양도세 시트 저장 report_filename = os.path.join(REPORT_FOLDER, f'Treport_{csv_file.split(".")[0]}.xlsx') # report_filename = f'Treport_{csv_file.split(".")[0]}.xlsx' report_writer = StyleFrame.ExcelWriter(report_filename) report_template.to_excel(excel_writer=report_writer, sheet_name='자료') report_p2_info.to_excel(excel_writer=report_writer, sheet_name='작성안내') report_p3_tax_code.to_excel(excel_writer=report_writer, sheet_name='양도물건종류별 세율코드') report_p4_stock_type.to_excel(excel_writer=report_writer, sheet_name='양도물건종류별 주식등종류') # 양도세 신고서 파일 저장 report_writer.save() # 양도세 신고서 파일명 변환 return report_filename
def test_read_excel_columns_width(self): self.sf.set_column_width(columns='a', width=25) self.sf.set_column_width(columns='b', width=15) self.export_and_get_default_sheet(save=True) sf_from_excel = StyleFrame.read_excel(TEST_FILENAME, read_style=True) # Assert the number of rows with height is the length of our data plus 1 for headers row self.assertEqual(len(sf_from_excel._columns_width), len(self.sf.columns)) self.assertEqual(sf_from_excel._columns_width['a'], 25) self.assertEqual(sf_from_excel._columns_width['b'], 15)
def test_read_excel_template_boundaries_with_less_rows_and_columns_than_df( self): template_sf = StyleFrame(obj={ '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'] }, styler_obj=self.styler_obj_1) template_sf.index[0].style = self.styler_obj_2 template_sf.to_excel(TEST_FILENAME, index=True).save() df = pd.DataFrame(data={ 'A': [1, 2, 3, 4], 'B': [3, 2, 1, 4], 'C': [-1, -2, -3, -4], }, columns=['A', 'B', 'C']) sf_from_template = StyleFrame.read_excel_as_template( path=TEST_FILENAME, df=df, use_df_boundaries=False, index_col=0, read_comments=True) for template_rows, sf_rows in zip( template_sf.data_df.itertuples(), sf_from_template.data_df.itertuples()): for template_cell, actual_cell in zip(template_rows, sf_rows): self.assertEqual( template_cell.style, actual_cell.style, 'Different styles in template cell {template_cell} with style {template_style}' '\nand actual cell {actual_cell} with style {actual_cell_style}' .format(template_cell=template_cell, template_style=template_cell.style, actual_cell=actual_cell, actual_cell_style=actual_cell.style)) # Assert values are equals to df and not to the original values from template assert_frame_equal(sf_from_template.data_df, df, check_index_type=False, check_dtype=False, check_column_type=False)
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_read_excel_rows_height(self): self.sf.set_row_height(rows=1, height=25) self.sf.set_row_height(rows=2, height=15) self.export_and_get_default_sheet(save=True) sf_from_excel = StyleFrame.read_excel(TEST_FILENAME, read_style=True) # Assert the number of rows with height is the length of our data plus 1 for headers row self.assertEqual(len(sf_from_excel._rows_height), len(self.sf) + 1) self.assertEqual(sf_from_excel._rows_height[1], 25) self.assertEqual(sf_from_excel._rows_height[2], 15) self.assertEqual(sf_from_excel._rows_height[3], None) self.assertEqual(sf_from_excel._rows_height[4], None)
def test_read_excel_template_boundaries_with_more_rows_and_columns_than_df( self): template_sf = StyleFrame(obj={ 'a': ['col_a_row_1', 'col_a_row_2'], 'b': ['col_b_row_1', 'col_b_row_2'] }, styler_obj=self.styler_obj_1) template_sf.to_excel(TEST_FILENAME).save() df = pd.DataFrame(data={ 'A': [1], }, columns=['A']) sf_from_template = StyleFrame.read_excel_as_template( path=TEST_FILENAME, df=df, use_df_boundaries=False, read_comments=True) # Since template is larger than the df and use_df_boundaries is false, 'b' column shouldn't change # and be left from the original template self.assertListEqual([col.value for col in sf_from_template.columns], ['A', 'b']) self.assertEqual( template_sf['a'][0].style, sf_from_template['A'][0].style, 'Different styles in template cell with style {template_style}' '\nand actual cell with style {actual_cell_style}'.format( template_style=template_sf['a'][0].style, actual_cell_style=sf_from_template['A'][0].style)) self.assertEqual(sf_from_template['A'][0].value, 1) # Assert extra column equals self.assertListEqual(list(sf_from_template['b']), list(template_sf['b'])) # Assert extra row exists and equals self.assertListEqual(list(sf_from_template.iloc[1]), list(template_sf.iloc[1]))
def test_read_excel_with_style_header_arg_none(self): self.sf = StyleFrame({0: ['A1', 'A2', 'A3', 'A4', 'A5']}) self.sf.apply_style_by_indexes( self.sf[self.sf.data_df[0].isin(('A2', 'A5'))], Styler(bold=True)) self.export_and_get_default_sheet(save=True) sf_from_excel = StyleFrame.read_excel(TEST_FILENAME, read_style=True, header=None) # making sure content is the same self.assertTrue( all( list(self.sf[col]) == list(sf_from_excel[col])[1:] for col in self.sf.columns)) rows_in_excel = list(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[1:], rows_in_self) for excel_cell, self_cell in zip(row_in_excel[1:], row_in_self[1:])))
def create_template(array_valid_element, root_last, sheet_name): template = [] collection = '' for element in array_valid_element: if len(element.attrib) == 1 and 'ref' not in element.attrib: collection = element.attrib.get('name', '') else: if element.tag.endswith('element') and 'ref' not in element.attrib: template.append([ element.get('name', ''), root_last + '.' + collection + '.', collection, 'ELEMENT' ]) elif 'ref' not in element.attrib: if element.attrib.get('name') in [ 'DATA_ASOF_TSTMP', 'LAST_ASOF_TSTMP' ]: template.append([ element.get('name', ''), root_last + '/', collection, 'ATTRIBUTE' ]) else: template.append([ element.get('name', ''), root_last + '.' + collection + '/', collection, 'ATTRIBUTE' ]) df = pd.DataFrame(template, columns=['mdrm', 'xml_path', 'collection', 'type']) excel_writer = StyleFrame.ExcelWriter('XML_Template.xlsx') sf = StyleFrame(df).\ apply_column_style(['mdrm', 'xml_path', 'collection', 'type'], styler_obj=Styler(font_size=11, font_color=None, horizontal_alignment=utils.horizontal_alignments.left, wrap_text=False, shrink_to_fit=False)) sf.set_column_width_dict({ 'mdrm': define_length_column(df['mdrm']) + 8, 'xml_path': define_length_column(df['xml_path']) + 10, 'collection': define_length_column(df['collection']) + 10, 'type': define_length_column(df['type']) + 5 }) sf.to_excel(excel_writer=excel_writer, sheet_name=sheet_name) excel_writer.save()
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 extract_PDF(read_path, write_path): files = [f for f in os.listdir(read_path) if isfile(join(read_path, f))] importlib.reload(pfs) for file in files: # Process PDFs and read them into dataframe results = pfs.process_PDF(read_path + file) # Save processed results to local drive so they can be easily retrieved without # having to re-perform processing (as this takes a long time) results.to_pickle(write_path + file.replace('pdf', 'pkl')) results = pd.read_pickle(write_path + file.replace('pdf', 'pkl')) # Reformat values and delete unnecessary attributes results['value'] = results['value'].apply(lambda x: float( x.replace('-', '0').replace(')', '').replace('(', '-'))) results = results[['label', 'statement', 'year', 'unit', 'value']] results['year'] = results['year'].astype("int64") results.drop_duplicates(['label', 'year'], keep='last', inplace=True) # Unstack dataframe results = results.groupby(['label', 'year', 'unit', 'statement'], sort=False)['value'].sum().unstack('year') # Split dataframe into balance sheet and income statement balance_sheet = results.query("statement == 'Balance sheet'") income_statement = results.query("statement == 'Income statement'") for i in (balance_sheet, income_statement): i.reset_index(inplace=True) i.drop(['statement'], axis=1, inplace=True) # Export to Excel writer = StyleFrame.ExcelWriter(write_path + file.replace('pdf', 'xlsx')) balance_sheet.to_excel(excel_writer=writer, sheet_name='Balance sheet') income_statement.to_excel(excel_writer=writer, sheet_name='Income statement') writer.save()
def change_coloring(df, path): sf = StyleFrame(df) sf.apply_column_style(cols_to_style=sf.columns, styler_obj=Styler(font_size=11, font=utils.fonts.calibri)) sf.apply_headers_style( styler_obj=Styler(bold=True, font_size=12, font=utils.fonts.calibri)) sf.apply_style_by_indexes(indexes_to_style=sf[sf['Pre Freezing'] > 6], cols_to_style=['Pre Freezing'], styler_obj=Styler(bg_color=utils.colors.blue, font_size=11, font_color=utils.colors.white)) sf.apply_style_by_indexes(indexes_to_style=sf[sf['Duration'] >= 1], cols_to_style=['Duration'], styler_obj=Styler(bg_color=utils.colors.yellow, font_size=11)) ew = StyleFrame.ExcelWriter(path) sf.to_excel(ew) ew.save()
print(sortex_df,"...") print("Sorted By Date") #-------------- sortex_df.columns = sortex_df.columns.str.replace('.csv', '') sorted_df.columns = sorted_df.columns.str.replace('.csv', '') print("Replaced String") sortex_df = StyleFrame(sortex_df, Styler(shrink_to_fit=False, wrap_text=False)) sortex_df.set_column_width(sortex_df.columns,12) writer = StyleFrame.ExcelWriter('{0}.xlsx'.format(key)) sorted_df = StyleFrame(sorted_df, Styler(shrink_to_fit=False, wrap_text=False)) sorted_df.set_column_width(sorted_df.columns,12) print("Stylesheet Created") sortex_df.to_excel(writer,'Merging By Date',index=False) sorted_df.to_excel(writer,'Treasury Spread',index=False) writer.save()
def test_style_accessor(self): sf = StyleFrame({'a': list(range(10))}) sf.apply_style_by_indexes( sf[sf['a'] % 2 == 0], styler_obj=Styler(bold=True, bg_color=utils.colors.yellow), complement_style=Styler(bold=False, font=utils.fonts.calibri)) control_sf = StyleFrame({'a': list(range(0, 10, 2))}) test_sf = StyleFrame( sf.loc[sf['a'].style.font == utils.fonts.arial].reset_index( drop=True)) assert_frame_equal(control_sf.data_df, test_sf.data_df) control_sf = StyleFrame({'a': list(range(0, 10, 2))}) test_sf = StyleFrame( sf.loc[sf['a'].style.bg_color == utils.colors.yellow].reset_index( drop=True)) assert_frame_equal(control_sf.data_df, test_sf.data_df) control_sf = StyleFrame({'a': list(range(0, 10, 2))}) test_sf = StyleFrame( sf.loc[(sf['a'].style.bg_color == utils.colors.yellow) & sf['a'].style.font].reset_index(drop=True)) assert_frame_equal(control_sf.data_df, test_sf.data_df) control_sf = StyleFrame({'a': list(range(1, 10, 2))}) test_sf = StyleFrame( sf.loc[sf['a'].style.font == utils.fonts.calibri].reset_index( drop=True)) assert_frame_equal(control_sf.data_df, test_sf.data_df) control_sf = StyleFrame({'a': list(range(1, 10, 2))}) test_sf = StyleFrame( sf.loc[~sf['a'].style.bold].reset_index(drop=True)) assert_frame_equal(control_sf.data_df, test_sf.data_df) control_sf = StyleFrame({'a': list(range(1, 10, 2))}) test_sf = StyleFrame( sf.loc[~sf['a'].style.bold & (sf['a'].style.font == utils.fonts.calibri)].reset_index( drop=True)) assert_frame_equal(control_sf.data_df, test_sf.data_df)
def main(): response_sheet_1 = "data/sheet_1_resp.xlsx" key_sheet_1 = "data/sheet_1_key.xlsx" response_sheet_2 = "data/sheet_2_resp.xlsx" key_sheet_2 = "data/sheet_2_key.xlsx" response_data_1, key_data_1 = readData(response_sheet_1, key_sheet_1) response_data_2, key_data_2 = readData(response_sheet_2, key_sheet_2) response_data_1 = response_data_1.sort_values('OMR No.', ascending=True, na_position='first') response_data_1 = response_data_1.drop('Timestamp', axis=1) response_data_2 = response_data_2.sort_values('OMR No.', ascending=True, na_position='first') response_data_2 = response_data_2.drop( ['Timestamp', 'NAME OF THE STUDENT'], axis=1) number_colums_sheet_1 = len(response_data_1) number_colums_sheet_2 = len(response_data_2) # print(type(key_data_1)) # print(type(key_data_2)) # #TEST COL Sheet 2 sheet2_data = response_data_2.iloc[0] unanswered_phy_sheet2, unanswered_chem_sheet2, unanswered_math_sheet2, total_unanswered_sheet2 = StudentNullAnalysis_sheet2( sheet2_data) QuestionsAnalysis_sheet2(sheet2_data, key_data_2, unanswered_phy_sheet2, unanswered_chem_sheet2, unanswered_math_sheet2, total_unanswered_sheet2) #TEST Col rowData = response_data_1.iloc[1] unanswered_phy, unanswered_chem, unanswered_math, total_unanswered = StudentNullAnalysis_sheet1( rowData) studentName, physics_total, chem_total, math_total, total = QuestionsAnalysis_sheet1( rowData, key_data_1, unanswered_phy, unanswered_chem, unanswered_math, total_unanswered) # print(studentName) result_sheet_1 = [] result_sheet_2 = [] for i in range(number_colums_sheet_1): rowData = response_data_1.iloc[i] unanswered_phy, unanswered_chem, unanswered_math, total_unanswered = StudentNullAnalysis_sheet1( rowData) studentName, physics_total, chem_total, math_total, total = QuestionsAnalysis_sheet1( rowData, key_data_1, unanswered_phy, unanswered_chem, unanswered_math, total_unanswered) result_sheet_1.append([ studentName, physics_total, chem_total, math_total, total, unanswered_phy, unanswered_chem, unanswered_math, total_unanswered ]) resultdf = pd.DataFrame(result_sheet_1, columns=[ 'Student Name', 'Physics Total', 'Chemistry Total', 'Mathematics Total', 'Total', 'Unanswered Physics', 'Unanswered Chemistry', 'Unanswered Math', 'Total Unanswered Questions' ]) for i in range(number_colums_sheet_2): sheet2_data = response_data_2.iloc[i] unanswered_phy_sheet2, unanswered_chem_sheet2, unanswered_math_sheet2, total_unanswered_sheet2 = StudentNullAnalysis_sheet2( sheet2_data) studentName_2, physics_total_2, chem_total_2, math_total_2, total_2 = QuestionsAnalysis_sheet2( sheet2_data, key_data_2, unanswered_phy_sheet2, unanswered_chem_sheet2, unanswered_math_sheet2, total_unanswered_sheet2) result_sheet_2.append([ studentName_2, physics_total_2, chem_total_2, math_total_2, total_2, unanswered_phy_sheet2, unanswered_chem_sheet2, unanswered_math_sheet2, total_unanswered_sheet2 ]) resultdf_2 = pd.DataFrame(result_sheet_2, columns=[ 'Student Name', 'Physics Total', 'Chemistry Total', 'Mathematics Total', 'Total', 'Unanswered Physics', 'Unanswered Chemistry', 'Unanswered Math', 'Total Unanswered Questions' ]) print(resultdf) print(resultdf_2) resultdf.to_csv(index=False) resultdf.to_excel("Result_sheet1.xlsx", sheet_name='Result_sheet1', engine='openpyxl', index=False) resultdf_2.to_csv(index=False) resultdf_2.to_excel("Result_sheet2.xlsx", sheet_name='Result_sheet2', engine='openpyxl', index=False) excel_writer = StyleFrame.ExcelWriter('Result_sheet1.xlsx') sf = StyleFrame(resultdf) styler = Styler(font_size=15, vertical_alignment='distributed') sf.apply_headers_style(styler, style_index_header=True) sf.set_column_width('Student Name', 40) sf.set_column_width( ['Physics Total', 'Chemistry Total', 'Mathematics Total', 'Total'], 20) sf.set_column_width([ 'Unanswered Physics', 'Unanswered Chemistry', 'Unanswered Math', 'Total Unanswered Questions' ], 30) sf.to_excel(excel_writer=excel_writer, columns_and_rows_to_freeze='B2') excel_writer.save() excel_writer = StyleFrame.ExcelWriter('Result_sheet2.xlsx') sf = StyleFrame(resultdf_2) styler = Styler(font_size=15, vertical_alignment='distributed') sf.apply_headers_style(styler, style_index_header=True) sf.set_column_width('Student Name', 40) sf.set_column_width( ['Physics Total', 'Chemistry Total', 'Mathematics Total', 'Total'], 20) sf.set_column_width([ 'Unanswered Physics', 'Unanswered Chemistry', 'Unanswered Math', 'Total Unanswered Questions' ], 30) sf.to_excel(excel_writer=excel_writer) excel_writer.save()
# print("Inside Loop") # print(x , i) #Process xf = bonds[i].sub(bonds[x]) xf = xf[~xf.index.duplicated()] xf = xf.fillna(value="na") newBonds.append(xf) parent_newBonds.append(newBonds) count += 1 #print(parent_newBonds) resulx, sortex_df = extractingTreasuryYeilds(nbonds) writer = StyleFrame.ExcelWriter('{0}.xlsx'.format(key)) writerRenamed = StyleFrame.ExcelWriter('{0}.xlsx'.format(key)) if key in shortlisted_countries: shortlist_sortex_df = shortlistedYeild(resulx) writerShorlisted = StyleFrame.ExcelWriter('{0}.xlsx'.format(key)) writerShortlistedRenamed = StyleFrame.ExcelWriter( '{0}.xlsx'.format(key)) mod_date_sortex_df, price_resulx, date_sortex_df = quarterlyInsightsYeild( resulx, resulx, 0) imputed_yeild_df = ImputedYeild(mod_date_sortex_df) writerImputed = StyleFrame.ExcelWriter('{0}.xlsx'.format(key)) writerQuarterly = StyleFrame.ExcelWriter('{0}.xlsx'.format(key))
def reports(request): if request.method == POST_METHOD: print(request.POST) query_dict_str = str(request.POST) if "Vendor" in query_dict_str: query_dict_str = query_dict_str.replace("Vendor", "VendorName") query_dict_str = query_dict_str.replace('<QueryDict:', '').replace('>', '') query_dict_str = query_dict_str.replace('\\n', '') filter_dict = eval(query_dict_str) # Reading master file start_time = time.time() EmployeeMaster = read_main_file() t1 = time.time() - start_time print("Emp Read") print(t1) # Reading Frequency value start_time = time.time() frequency = return_frequency(filter_dict) print(time.time() - start_time) # Preparing Date list start_time = time.time() date_value_list = return_date_list(frequency, filter_dict) print(time.time() - start_time) # Reading Report Type start_time = time.time() report_type = return_report_type(filter_dict) print(time.time() - start_time) # Returning filtered dataframe based on dimensions start_time = time.time() filered_df = filtered_dataframe(EmployeeMaster, filter_dict) print(time.time() - start_time) # Returning final table final_dataframe = return_final_table(filered_df, date_value_list, report_type, frequency) # Dataframe post processing final_dataframe["VendorName"] = final_dataframe[ "VendorName"].str.upper().str.title() final_dataframe.style.set_properties(subset=["VendorName"], **{'text-align': 'left'}) collist = final_dataframe.columns.tolist() if report_type != "Attrition rate": # if frequency == "MTD": final_dataframe[collist[1:]] = final_dataframe[collist[1:]].astype( int) if frequency == "MTD" or frequency == "Quarterly YTD" or frequency == "Annually": if report_type != "Net Addition": df_dict = {} from datetime import datetime for col in collist[1:]: date_object = datetime.strptime(col, '%Y-%m-%d') df_dict[col] = date_object.strftime('%b %y') for col in final_dataframe.columns: for name in df_dict: if name == col: final_dataframe = final_dataframe.rename( columns={col: df_dict[name]}) final_dataframe = final_dataframe.append( final_dataframe.sum(numeric_only=True), ignore_index=True) final_dataframe['VendorName'] = final_dataframe[ 'VendorName'].fillna('Total') else: is_NaN = final_dataframe.isnull() row_has_NaN = is_NaN.any(axis=1) final_dataframe = final_dataframe[row_has_NaN] final_dataframe['VendorName'] = final_dataframe[ 'VendorName'].replace(np.nan, "Net Addition") final_dataframe.rename(columns={'VendorName': "Report Type"}, inplace=True) df_dict = {} from datetime import datetime for col in collist[1:]: date_object = datetime.strptime(col, '%Y-%m-%d') df_dict[col] = date_object.strftime('%b %y') for col in final_dataframe.columns: for name in df_dict: if name == col: final_dataframe = final_dataframe.rename( columns={col: df_dict[name]}) end_time = time.time() print("------------------------") print("Time Taken by the engine:") print(end_time - start_time) print("------------------------") # print(final_dataframe[:4]) default_style = Styler(font=utils.fonts.calibri, font_size=11, fill_pattern_type=None, border_type=None) sf = StyleFrame(final_dataframe, styler_obj=default_style) sf.apply_column_style( cols_to_style=list(final_dataframe.columns)[:1], styler_obj=Styler( horizontal_alignment=utils.horizontal_alignments.left, font=utils.fonts.calibri, font_size=11, border_type=None, fill_pattern_type=None, bg_color='#808080'), style_header=True) header_style = Styler( bold=True, bg_color='#808080', horizontal_alignment=utils.horizontal_alignments.left, font_color=utils.colors.white, font=utils.fonts.calibri, font_size=11, border_type=None) sf.apply_headers_style(styler_obj=header_style) sf.set_column_width(columns=list(final_dataframe.columns)[:1], width=45) df_file = sf.to_excel('static/df_to_excel/final_output.xlsx', index=False).save() return render(request, TABLE_HTML, { 'data_frame': final_dataframe, 'df_file': df_file }) field_list = [ EMPLOYEES, VENDORS, STATES, LOCATIONS, GENDERS, TEAMS, FUNCTIONS, REPORT_TYPES, FREQUENCIES, DIMENSIONS, CITIES, SUB_TEAMS, REGIONS, CTC_SLABS, EXIT_TYPES, AGES, EMP_TYPES, TENURES, ENTITIES ] active_fields = fetch_active_fields2(field_list) return render(request, REPORT_HTML, active_fields)
# Write to Excel file SCTID_Columns = excel_file.filter(regex=f"SCTID.*").columns all_list = SCTID_Columns.to_list( ) + Semantic_Tag_Columns + FSN_Columns.to_list() excel_file = excel_file.drop(columns=all_list) col_list = excel_file.columns.tolist() excel_writer = pd.ExcelWriter(f"Output/{file_name}") # sf = StyleFrame(excel_file) # sf.apply_headers_style(Styler(bold=False, horizontal_alignment='left')) excel_file.to_excel(excel_writer=excel_writer, index=False, sheet_name="Variables") file_df.pop("Variables") for file_name in file_df: sf = StyleFrame(file_df[file_name]) col_list = file_df[file_name].columns.tolist() # sf.apply_headers_style(Styler(bold=False, horizontal_alignment='left')) file_df[file_name].to_excel(excel_writer=excel_writer, index=False, sheet_name=file_name) excel_writer.save() # CSV + YAML elif mode == 2: _, _, files = next(walk("Files")) excel_dfs = [] for file_name in files: _, file_extension = os.path.splitext(f"Files/{file_name}") if file_extension != ".xlsx": break excel_df = edit_excel(f"Files/{file_name}")
def func01(): sheet_range = [21, 22] df_origin = pd.DataFrame() for num in sheet_range: excel_sheet_name = re.sub('00', str(num), 'day00') df_origin = df_origin.append(pd.read_excel("data/vocabulary.xlsx", sheet_name=excel_sheet_name, header=None), ignore_index=True) shuffled_df = df_origin.iloc[np.random.permutation(df_origin.index)].reset_index(drop=True) shuffled_df_len = len(shuffled_df.index) df_origin = pd.DataFrame(index=[1, 2, 3, 4]) df_quiz = pd.DataFrame(index=[1, 2, 3, 4]) for num in range(0, shuffled_df_len, 2): df_origin.loc[int(num/2), 1] = shuffled_df.loc[num, 0] df_origin.loc[int(num/2), 2] = shuffled_df.loc[num, 1] df_origin.loc[int(num/2), 3] = shuffled_df.loc[num+1, 0] df_origin.loc[int(num/2), 4] = shuffled_df.loc[num+1, 1] seed = random.choice(['VOCA', 'MEANING']) if seed == 'VOCA': df_quiz.loc[int(num/2), 1] = shuffled_df.loc[num, 0] df_quiz.loc[int(num/2), 2] = '' elif seed == 'MEANING': df_quiz.loc[int(num/2), 1] = '' df_quiz.loc[int(num/2), 2] = shuffled_df.loc[num, 1] seed = random.choice(['VOCA', 'MEANING']) if seed == 'VOCA': df_quiz.loc[int(num/2), 3] = shuffled_df.loc[num+1, 0] df_quiz.loc[int(num/2), 4] = '' elif seed == 'MEANING': df_quiz.loc[int(num/2), 3] = '' df_quiz.loc[int(num/2), 4] = shuffled_df.loc[num+1, 1] excel_writer = StyleFrame.ExcelWriter('data/vocabulary_quiz.xlsx') sf_origin = StyleFrame(df_origin) sf_quiz = StyleFrame(df_quiz) # sf.set_row_height(rows=[n for n in range(1, math.ceil(shuffled_df_len/2)+1)], height=) sf_origin.set_column_width(columns=['A', 'C'], width=16.0) sf_origin.set_column_width(columns=['B', 'D'], width=40.0) sf_quiz.set_column_width(columns=['A', 'C'], width=16.0) sf_quiz.set_column_width(columns=['B', 'D'], width=40.0) sf_origin.to_excel(excel_writer, header=False, index=False, sheet_name='origin') sf_quiz.to_excel(excel_writer, header=False, index=False, sheet_name='quiz') excel_writer.save()
def formatexcel(df1,temp): df1.iloc[1:,-1:]=temp.iloc[1:,-1:] sf = StyleFrame(df1) for col_name in df1.columns[:-2]: #### Apply Red colour to invalid cells sf.apply_style_by_indexes( sf[temp[col_name]==1],cols_to_style=col_name, styler_obj=Styler(bg_color='red',font_size=8, number_format=utils.number_formats.general_float, wrap_text=False)) #### Apply Green colour to valid cells sf.apply_style_by_indexes( sf[temp[col_name]==0],cols_to_style=col_name, styler_obj=Styler(bg_color="#228B22",font_size=8, number_format=utils.number_formats.general_float, wrap_text=False)) #### Apply Yellow colour to Operator cells sf.apply_style_by_indexes( sf[temp[col_name]==0.0505],cols_to_style=col_name, styler_obj=Styler(bg_color="yellow",font_size=8, wrap_text=False)) header_style = Styler(bg_color="#c4c4ff",text_rotation=90, font_size=8,wrap_text=False) sf.apply_headers_style(styler_obj=header_style) sf.set_column_width(columns=sf.columns, width=10) sf.to_excel("Output "+str(int(time.time()))+".xlsx").save() return True
weight = roundArr(weight, 4) weightedGradeCalc = roundArr(weightedGradeCalc, 4) #set up the excel dataframe df = pd.DataFrame({ 'Gradebook Item': names, 'Grade': marks, 'Max Points': maxMark, 'Weight (out of 100%)': weight, 'Weighted Grade (out of 100%)': weightedGradeCalc }) #define default style default_style = Styler(font_size=12) #create a new StyleFrame with excel dataframe using default style sf[i] = StyleFrame(df, styler_obj=default_style) #apply header style header_style = Styler(bg_color=utils.colors.black, font_color=utils.colors.white, bold=True, font_size=18, shrink_to_fit=False) sf[i].apply_headers_style(styler_obj=header_style) #apply style for categories. There is a different style for the headings (they are justified to the left) categoryStyleName = Styler( bold=True, font_size=14, bg_color=utils.colors.grey, horizontal_alignment=utils.horizontal_alignments.left)
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.0, underline=utils.underline.single, horizontal_alignment=utils.horizontal_alignments.left, vertical_alignment=utils.vertical_alignments.center, comment_author='Author 1', comment_text='styler_obj_1 comment') cls.styler_obj_2 = Styler(bg_color=utils.colors.yellow, comment_author='Author 2', 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_openpyxl_objects_and_save(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:]))) sf_from_excel.to_excel(TEST_FILENAME).save() 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_read_excel_with_style_header_arg_none(self): self.sf = StyleFrame({0: ['A1', 'A2', 'A3', 'A4', 'A5']}) self.sf.apply_style_by_indexes( self.sf[self.sf.data_df[0].isin(('A2', 'A5'))], Styler(bold=True)) self.export_and_get_default_sheet(save=True) sf_from_excel = StyleFrame.read_excel(TEST_FILENAME, read_style=True, header=None) # making sure content is the same self.assertTrue( all( list(self.sf[col]) == list(sf_from_excel[col])[1:] for col in self.sf.columns)) rows_in_excel = list(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[1:], rows_in_self) for excel_cell, self_cell in zip(row_in_excel[1:], row_in_self[1:]))) def test_read_excel_rows_height(self): self.sf.set_row_height(rows=1, height=25) self.sf.set_row_height(rows=2, height=15) self.export_and_get_default_sheet(save=True) sf_from_excel = StyleFrame.read_excel(TEST_FILENAME, read_style=True) # Assert the number of rows with height is the length of our data plus 1 for headers row self.assertEqual(len(sf_from_excel._rows_height), len(self.sf) + 1) self.assertEqual(sf_from_excel._rows_height[1], 25) self.assertEqual(sf_from_excel._rows_height[2], 15) self.assertEqual(sf_from_excel._rows_height[3], None) self.assertEqual(sf_from_excel._rows_height[4], None) def test_read_excel_columns_width(self): self.sf.set_column_width(columns='a', width=25) self.sf.set_column_width(columns='b', width=15) self.export_and_get_default_sheet(save=True) sf_from_excel = StyleFrame.read_excel(TEST_FILENAME, read_style=True) # Assert the number of rows with height is the length of our data plus 1 for headers row self.assertEqual(len(sf_from_excel._columns_width), len(self.sf.columns)) self.assertEqual(sf_from_excel._columns_width['a'], 25) self.assertEqual(sf_from_excel._columns_width['b'], 15) def test_read_excel_template_equal_boundaries(self): template_sf = StyleFrame(obj={ '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'] }, styler_obj=self.styler_obj_1) template_sf.index[0].style = self.styler_obj_2 template_sf.to_excel(TEST_FILENAME, index=True).save() df = pd.DataFrame(data={ 'A': [1, 2, 3], 'B': [3, 2, 1] }, columns=['A', 'B']) sf_from_template = StyleFrame.read_excel_as_template( path=TEST_FILENAME, df=df, use_df_boundaries=False, index_col=0, read_comments=True) for template_rows, sf_rows in zip( template_sf.data_df.itertuples(), sf_from_template.data_df.itertuples()): for template_cell, actual_cell in zip(template_rows, sf_rows): self.assertEqual( template_cell.style, actual_cell.style, 'Different styles in template cell {template_cell} with style {template_style}' '\nand actual cell {actual_cell} with style {actual_cell_style}' .format(template_cell=template_cell, template_style=template_cell.style, actual_cell=actual_cell, actual_cell_style=actual_cell.style)) # Assert values are equals to df and not to the original values from template assert_frame_equal(sf_from_template.data_df, df, check_index_type=False, check_dtype=False, check_column_type=False) def test_read_excel_template_boundaries_with_more_rows_and_columns_than_df( self): template_sf = StyleFrame(obj={ 'a': ['col_a_row_1', 'col_a_row_2'], 'b': ['col_b_row_1', 'col_b_row_2'] }, styler_obj=self.styler_obj_1) template_sf.to_excel(TEST_FILENAME).save() df = pd.DataFrame(data={ 'A': [1], }, columns=['A']) sf_from_template = StyleFrame.read_excel_as_template( path=TEST_FILENAME, df=df, use_df_boundaries=False, read_comments=True) # Since template is larger than the df and use_df_boundaries is false, 'b' column shouldn't change # and be left from the original template self.assertListEqual([col.value for col in sf_from_template.columns], ['A', 'b']) self.assertEqual( template_sf['a'][0].style, sf_from_template['A'][0].style, 'Different styles in template cell with style {template_style}' '\nand actual cell with style {actual_cell_style}'.format( template_style=template_sf['a'][0].style, actual_cell_style=sf_from_template['A'][0].style)) self.assertEqual(sf_from_template['A'][0].value, 1) # Assert extra column equals self.assertListEqual(list(sf_from_template['b']), list(template_sf['b'])) # Assert extra row exists and equals self.assertListEqual(list(sf_from_template.iloc[1]), list(template_sf.iloc[1])) def test_read_excel_template_boundaries_with_less_rows_and_columns_than_df( self): template_sf = StyleFrame(obj={ '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'] }, styler_obj=self.styler_obj_1) template_sf.index[0].style = self.styler_obj_2 template_sf.to_excel(TEST_FILENAME, index=True).save() df = pd.DataFrame(data={ 'A': [1, 2, 3, 4], 'B': [3, 2, 1, 4], 'C': [-1, -2, -3, -4], }, columns=['A', 'B', 'C']) sf_from_template = StyleFrame.read_excel_as_template( path=TEST_FILENAME, df=df, use_df_boundaries=False, index_col=0, read_comments=True) for template_rows, sf_rows in zip( template_sf.data_df.itertuples(), sf_from_template.data_df.itertuples()): for template_cell, actual_cell in zip(template_rows, sf_rows): self.assertEqual( template_cell.style, actual_cell.style, 'Different styles in template cell {template_cell} with style {template_style}' '\nand actual cell {actual_cell} with style {actual_cell_style}' .format(template_cell=template_cell, template_style=template_cell.style, actual_cell=actual_cell, actual_cell_style=actual_cell.style)) # Assert values are equals to df and not to the original values from template assert_frame_equal(sf_from_template.data_df, df, check_index_type=False, check_dtype=False, check_column_type=False) def test_read_excel_template_with_use_df_boundaries(self): template_sf = StyleFrame(obj={ 'a': ['col_a_row_1', 'col_a_row_2'], 'b': ['col_b_row_1', 'col_b_row_2'] }, styler_obj=self.styler_obj_1) template_sf.to_excel(TEST_FILENAME).save() df = pd.DataFrame(data={ 'A': [1], }, columns=['A']) sf_from_template = StyleFrame.read_excel_as_template( path=TEST_FILENAME, df=df, use_df_boundaries=True, read_comments=True) self.assertListEqual([col.value for col in sf_from_template.columns], ['A']) self.assertEqual(len(df), len(sf_from_template)) expected_cell_style = template_sf['a'][0].style actual_cell_style = sf_from_template['A'][0].style self.assertEqual( actual_cell_style, expected_cell_style, 'Different styles in template cell with style {template_style}' '\nand actual cell with style {actual_cell_style}'.format( template_style=expected_cell_style, actual_cell_style=actual_cell_style)) self.assertEqual(sf_from_template['A'][0].value, 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) def test_columns_setter(self): self.sf.columns = ['c', 'd'] self.assertTrue( all(isinstance(col, Container) for col in self.sf.columns)) self.assertEqual([col.value for col in self.sf.columns], ['c', 'd'])
df1 = pd.read_excel("file2.xlsx") print(df1) print(df1.columns) frames = [df0, df1] # when in production, replace frames for create_list... function master_df = pd.concat(frames, ignore_index=True) master_df.sort_values(by='Id', inplace=True) # end of master_df print(master_df) print(master_df.columns) # creating sf dataFrame for color formating sf = StyleFrame(master_df) cols_to_style = [cols for cols in sf.columns] # applying color for student with 1 sf.apply_style_by_indexes(indexes_to_style=sf[(sf['note'] <= 1)], cols_to_style=cols_to_style, styler_obj=Styler(bg_color='green', bold=True, font_size=10)) # applying color for student with 2 sf.apply_style_by_indexes(indexes_to_style=sf[(sf['note'] > 1) & (sf['note'] < 3)], cols_to_style=cols_to_style, styler_obj=Styler(bg_color='yellow', bold=True,
def test_init_styleframe(self): self.assertIsInstance(StyleFrame(StyleFrame({'a': [1, 2, 3]})), StyleFrame) with self.assertRaises(TypeError): StyleFrame({}, styler_obj=1)
komentar = fix['komentar'] komentar.append(data['komentar']) fix['komentar'] = komentar prediksi = fix['ulasan_prediksi'] prediksi.append(data['hasil'].lower()) fix['ulasan_prediksi'] = prediksi aktual = fix['ulasan_aktual'] aktual.append(ratePakar[i]) fix['ulasan_aktual'] = aktual outputFile = 'output/compare-output.xlsx' df = pd.DataFrame(fix) sf = StyleFrame(df) for col_name in df.columns: sf.apply_style_by_indexes( indexes_to_style=sf[(sf['ulasan_prediksi'] == 'positif') & (sf['ulasan_aktual'] == 'positif')], cols_to_style=['ulasan_prediksi', 'ulasan_aktual'], styler_obj=Styler(bg_color=utils.colors.green)) sf.apply_style_by_indexes( indexes_to_style=sf[(sf['ulasan_prediksi'] == 'positif') & (sf['ulasan_aktual'] == 'negatif')], cols_to_style=['ulasan_prediksi', 'ulasan_aktual'], styler_obj=Styler(bg_color=utils.colors.blue)) sf.apply_style_by_indexes( indexes_to_style=sf[(sf['ulasan_prediksi'] == 'negatif') & (sf['ulasan_aktual'] == 'positif')], cols_to_style=['ulasan_prediksi', 'ulasan_aktual'],
def change_coloring(df, path): sf = StyleFrame(df) sf.apply_column_style(cols_to_style=sf.columns, styler_obj=Styler(font_size=11, font=utils.fonts.calibri)) sf.apply_headers_style(styler_obj=Styler(bold=True, font_size=12, font=utils.fonts.calibri)) sf.apply_style_by_indexes(indexes_to_style=sf[sf['Pre Hidden'] > 6], cols_to_style=['Pre Hidden'], styler_obj=Styler(bg_color=utils.colors.blue, font_size=11, font_color=utils.colors.white)) sf.apply_style_by_indexes(indexes_to_style=sf[sf['Pre Center'] > 6], cols_to_style=['Pre Center'], styler_obj=Styler(bg_color=utils.colors.blue, font_size=11, font_color=utils.colors.white)) sf.apply_style_by_indexes(indexes_to_style=sf[sf['Pre Approach'] > 6], cols_to_style=['Pre Approach'], styler_obj=Styler(bg_color=utils.colors.blue, font_size=11, font_color=utils.colors.white)) sf.apply_style_by_indexes(indexes_to_style=sf[sf['Pre Hidden'] > 6], cols_to_style=['Hidden Start Time'], styler_obj=Styler(bg_color=utils.colors.yellow, font_size=11)) sf.apply_style_by_indexes(indexes_to_style=sf[sf['Pre Center'] > 6], cols_to_style=['Center Start Time'], styler_obj=Styler(bg_color=utils.colors.yellow, font_size=11)) sf.apply_style_by_indexes(indexes_to_style=sf[sf['Pre Approach'] > 6], cols_to_style=['Approach Start Time'], styler_obj=Styler(bg_color=utils.colors.yellow, font_size=11)) ew = StyleFrame.ExcelWriter(path) sf.to_excel(ew) ew.save()