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 create_baking_excel(self): data_frame = self.database_controller.to_data_frame() data_collection = DataCollection() data_collection.create(self.is_calibration, data_frame, self.fbg_names, self.main_queue) column_ordering = [DATE_TIME_HEADER, DELTA_TIME_HEADER, TEMPERATURE_HEADER] add_times(data_frame, data_collection) add_wavelength_power_columns(column_ordering, data_frame) add_baking_duplicate_columns(data_frame, data_collection) column_ordering.extend([DELTA_TIME_HEADER1, DELTA_TEMPERATURE_HEADER1]) self.add_delta_wavelengths(data_frame, data_collection, column_ordering) column_ordering.extend([DELTA_TIME_HEADER2, DELTA_TEMPERATURE_HEADER2]) self.add_delta_powers(data_frame, data_collection, column_ordering) column_ordering.extend([DELTA_TEMPERATURE_HEADER3, MEAN_DELTA_WAVELENGTH_HEADER, MEAN_DELTA_POWER_HEADER]) data_frame[MEAN_DELTA_WAVELENGTH_HEADER] = data_collection.mean_delta_wavelengths_pm data_frame[MEAN_DELTA_POWER_HEADER] = data_collection.mean_delta_powers data_frame = data_frame[column_ordering] baking_coefficients = self._create_baking_coefficients(data_frame) trend_line_indexes = self._create_trend_lines(data_frame, baking_coefficients) sensitivity_indexes = self._create_sensitivity_lines(data_frame, trend_line_indexes) style_frame = self.create_style_frame(data_frame) style_frame.apply_column_style(cols_to_style=MEAN_DELTA_WAVELENGTH_HEADER, styler_obj=Styler(font_color=utils.colors.red)) style_frame.apply_column_style(cols_to_style=[DATE_TIME_HEADER], styler_obj=Styler(number_format=utils.number_formats.date_time_with_seconds)) delta_temperature_headers = [col for col in data_frame.columns.values if DELTA_TEMPERATURE_HEADER in col] style_frame.apply_column_style(cols_to_style=delta_temperature_headers, styler_obj=Styler(font_color=utils.colors.red)) parameters = BakingGraphParameters(len(data_frame.index), trend_line_indexes, sensitivity_indexes) self.show_excel([style_frame], ["Baking Data"], parameters, self._graph_bake_results, baking_coefficients=baking_coefficients)
def save_adjust_xlsx(df, file='test.xlsx', width=60): '''save and re-adjust excel format''' df = df.reset_index(drop='index').fillna('') StyleFrame.A_FACTOR = 5 StyleFrame.P_FACTOR = 1.2 sf = StyleFrame(df, Styler(wrap_text=False, shrink_to_fit=True, font_size=12)) if ('add_index' in df.columns.tolist()): sf.apply_style_by_indexes( indexes_to_style=sf[sf['add_index'] == 'new'], styler_obj=Styler(bg_color='yellow'), overwrite_default_style=False) sf.apply_column_style(cols_to_style=['当事人', '诉讼代理人', '地址'], width=width, styler_obj=Styler(wrap_text=False, shrink_to_fit=True)) else: sf.set_column_width_dict( col_width_dict={('当事人', '诉讼代理人', '地址'): width}) if len(df): sf.to_excel(file, best_fit=sf.data_df.columns.difference( ['当事人', '诉讼代理人', '地址']).tolist()).save() else: sf.to_excel(file).save() print_log('>>> 保存文件 => 文件名 \'%s\' => 数据保存成功...' % (file)) return df
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 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 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 setUpClass(cls): cls.cli = CommandLineInterface(TEST_JSON_FILE, TEST_FILENAME) cls.sheet_1_col_a_style = Styler( bg_color=utils.colors.blue, font_color=utils.colors.yellow).create_style() cls.sheet_1_col_a_cell_2_style = Styler( bold=True, font=utils.fonts.arial, font_size=30, font_color=utils.colors.green, border_type=utils.borders.double).create_style() cls.sheet_1_col_b_cell_4_style = Styler(bold=True, font=utils.fonts.arial, font_size=16).create_style()
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 get_dividends(ticker, writer, quarters_back): stock = yf.Ticker(ticker) div_df = stock.actions if (len(div_df) == 0 or (div_df.loc[div_df.index[0], 'Dividends'] == 0 and len(div_df) == 1)): return del div_df['Stock Splits'] quarters_back_calc = len(div_df.index) - quarters_back kept_div_df = div_df.drop(div_df.index[list(range(quarters_back_calc))]) kept_div_df.insert( loc=0, column='Ex-Dates', value=[datetime.strftime(d, "%Y-%m-%d") for d in kept_div_df.index]) sf_div = StyleFrame(kept_div_df) sf_div.apply_column_style( cols_to_style='Ex-Dates', styler_obj=Styler(number_format=utils.number_formats.date)) col_list_div = list(kept_div_df.columns) sf_div.to_excel(excel_writer=writer, sheet_name=ticker + ' Dividends', best_fit=col_list_div)
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().name for i in range(2, len(self.sf))))
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 _load_sheet(self, sheet): sheet_name = sheet['sheet_name'] default_cell_style = sheet.get('default_styles', {}).get('cells') data = defaultdict(list) for col in sheet['columns']: col_name = col['col_name'] col_width = col.get('width') if col_width: self.col_names_to_width[sheet_name][col_name] = col_width for cell in col['cells']: provided_style = cell.get('style') or col.get( 'style') or default_cell_style or {} unrecognized_styler_kwargs = set( provided_style.keys()) - styler_kwargs if unrecognized_styler_kwargs: raise TypeError( 'Styler dict {} contains unexpected argument: {}.\n' 'Expected arguments: {}'.format( provided_style, unrecognized_styler_kwargs, styler_kwargs)) else: data[col_name].append( Container(cell['value'], Styler(**(provided_style)).create_style())) sf = StyleFrame(pd.DataFrame(data=data)) self._apply_headers_style(sf, sheet) self._apply_cols_and_rows_dimensions(sf, sheet) sf.to_excel(excel_writer=self.excel_writer, sheet_name=sheet_name, **sheet.get('extra_features', {})) setattr(self, '{}_sf'.format(sheet_name), sf)
def apply_highlights_pol_rows(self, sf, df): # create masks based on row polarities pol_sets = df.PossiblePols.apply(lambda x: set(x.keys())) pp_mask = pol_sets.apply(lambda x: True if 'Pp' in x else False) pn_mask = pol_sets.apply(lambda x: True if 'Pn' in x else False) nc_mask = pol_sets.apply(lambda x: True if 'Nc' in x else False) np_mask = pol_sets.apply(lambda x: True if 'Np' in x else False) # list all cases and its colour code list_cases = [] list_cases.append( tuple([sf[(pp_mask & pn_mask & nc_mask & np_mask)], 'a80000'])) list_cases.append( tuple([sf[(pp_mask & pn_mask & (nc_mask ^ np_mask))], 'ff0000'])) list_cases.append( tuple([sf[(pp_mask & pn_mask & ~(nc_mask | np_mask))], 'ff6400'])) list_cases.append( tuple([sf[((pp_mask ^ pn_mask) & nc_mask & np_mask)], 'ff9200'])) list_cases.append( tuple([sf[(pp_mask & ~pn_mask & (nc_mask ^ np_mask))], 'ffc800'])) list_cases.append( tuple([sf[(~pp_mask & pn_mask & (nc_mask ^ np_mask))], 'e3b200'])) list_cases.append( tuple([sf[~pp_mask & ~pn_mask & nc_mask & np_mask], 'fffe00'])) list_cases.append(tuple([sf[sf.PolScore == 0], 'b3ff00'])) # apply for case in list_cases: sf.apply_style_by_indexes(indexes_to_style=case[0], styler_obj=Styler(bg_color=case[1]))
def get_prices(ticker, writer, start_date, end_date): df = yf.download(ticker, start=start_date[:10], end=end_date[:10]) i = 0 for val in df['Close']: df.at[df.index[i], 'Close'] = int(val * 100) / 100 i += 1 df.insert(loc=0, column='Date', value=[datetime.strftime(d, "%Y-%m-%d") for d in df.index]) del df['Open'] del df['High'] del df['Adj Close'] del df['Low'] del df['Volume'] sf = StyleFrame(df) sf.apply_column_style( cols_to_style='Date', styler_obj=Styler(number_format=utils.number_formats.date)) col_list = list(df.columns) sf.to_excel(excel_writer=writer, sheet_name=ticker + ' Price', best_fit=col_list)
def test_from_openpyxl_style(self): styler_obj = Styler(bg_color=utils.colors.yellow, bold=True, font=utils.fonts.david, font_size=16, font_color=utils.colors.blue, number_format=utils.number_formats.date, protection=True, underline=utils.underline.double, border_type=utils.borders.double, horizontal_alignment=utils.horizontal_alignments.center, vertical_alignment=utils.vertical_alignments.bottom, wrap_text=False, shrink_to_fit=True, fill_pattern_type=utils.fill_pattern_types.gray0625, indent=1) self.assertEqual(styler_obj, Styler.from_openpyxl_style(styler_obj.to_openpyxl_style(), []))
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 create_calibration_excel(self): data_frame = self.database_controller.to_data_frame() data_collection = DataCollection() data_collection.create(self.is_calibration, data_frame, self.fbg_names, self.main_queue) real_point_data_frame = data_frame[data_frame[REAL_POINT_HEADER] == "True"] cycles = list(set(real_point_data_frame[CYCLE_HEADER])) cycles.sort() del real_point_data_frame[REAL_POINT_HEADER] del real_point_data_frame[DATE_TIME_HEADER] full_column_ordering = [DATE_TIME_HEADER, DELTA_TIME_HEADER, TEMPERATURE_HEADER] add_times(data_frame, data_collection) add_wavelength_power_columns(full_column_ordering, data_frame) full_column_ordering.extend([CYCLE_HEADER, REAL_POINT_HEADER]) data_frame = data_frame[full_column_ordering] container = CalibrationExcelContainer(real_point_data_frame, cycles) calibration_data_frame = container.get_data_frame() calibration_style_frame = self.create_style_frame(calibration_data_frame) full_style_frame = self.create_style_frame(data_frame) delta_temperature_headers = [col for col in data_frame.columns.values if DELTA_TEMPERATURE_HEADER in col] full_style_frame.apply_column_style(cols_to_style=delta_temperature_headers, styler_obj=Styler(font_color=utils.colors.red)) full_style_frame.apply_column_style(cols_to_style=[DATE_TIME_HEADER], styler_obj= Styler(number_format=utils.number_formats.date_time_with_seconds)) first_column = "Temperature (K) Cycle {}".format(container.cycles[0]) temperatures = calibration_data_frame[first_column].values parameters = CalibrationGraphParameters(len(calibration_data_frame.index), temperatures, container.cycles, container.mean_wavelength_indexes, container.deviation_wavelength_indexes, container.mean_power_indexes, container.deviation_power_indexes, container.sensitivity_wavelength_indexes, container.sensitivity_power_indexes, container.master_temperature_column, container.mean_temperature_column) coefficients = [container.wavelength_mean_coefficients, container.power_mean_coefficients] self.show_excel([calibration_style_frame, full_style_frame], ["Cal", "Full Cal"], parameters, self._graph_calibration_results, coefficients)
def setUpClass(cls): cls.yellow_1 = Styler(bg_color='yellow') cls.yellow_2 = Styler(bg_color='yellow') cls.blue = Styler(bg_color='blue') cls.bold = Styler(bold=True) cls.underline = Styler(underline='single') cls.yellow_bold_underline = Styler(bg_color='yellow', bold=True, underline='single')
def _format_table(self, df): sort_criteria = self.SETTINGS['sort_data']['incomes_table'] excel_format = df[sort_criteria].apply( lambda value: f"{value.date().strftime(r'%d/%m/%Y')}" if f"{type(value)}" == "<class 'pandas._libs.tslibs.timestamps.Timestamp'>" else value) df[sort_criteria] = excel_format sf = StyleFrame(df) sf.apply_style_by_indexes(indexes_to_style=sf[sf['TITULO'] != ''],styler_obj=Styler(font_size=10)) sf.apply_style_by_indexes(indexes_to_style=sf[sf['TURMA'] == 'TURMA'],styler_obj=Styler(bold=False, bg_color=utils.colors.grey, border_type=utils.borders.thin, font_size=10, wrap_text=False, shrink_to_fit=False)) sf.apply_style_by_indexes(indexes_to_style=sf[sf['SACADO'] == 'TOTAL'],styler_obj=Styler(font_size=10, bold=True)) sf.apply_headers_style(styler_obj=Styler(bold=False, bg_color=utils.colors.grey, border_type=utils.borders.thin, font_size=10, wrap_text=False, shrink_to_fit=False)) col_width = { 'TURMA': 32, 'TITULO': 8, 'VENCIMENTO': 12, 'SACADO': 34, 'VAL_COMISSAO': 12, 'VAL_RECEBIDO':13.2, 'VAL_PARCELA': 12, 'VAL_PRODUCAO': 12, 'VAL_MULTA': 12, 'VAL_DESCONTO': 12, 'VAL_ADESAO': 12, 'DATA_CREDITO':12, } sf.set_column_width_dict(col_width) return sf
def apply_highlights_mut_rows(self, sf, df): # create masks based on row mutations colour_base = 127 for index, row in df.iterrows(): muts_dict = row.PossibleMuts rgb = tuple([ colour_base+muts_dict['Sil']*(255-colour_base) if 'Sil' in muts_dict else colour_base, colour_base+muts_dict['Mis']*(255-colour_base)*5 if 'Mis' in muts_dict else colour_base, colour_base+muts_dict['Non']*(255-colour_base)*5 if 'Non' in muts_dict else colour_base ]) sf.apply_style_by_indexes( indexes_to_style=[index], styler_obj=Styler(bg_color=self.rgb2hex(*rgb)) )
def combine_csvs(self, delete_batches: bool = False, to_excel: bool = True): """Combines batch csv output files into the final csv file and optionally an excel file Args: delete_batches (bool): Whether the batch csv files should be deleted after stitching them to the combined csv file. Defaults to False (not deleting the batch csv files) to_excel (bool): Whether the csv file should also be saved as an excel file. Defaults to True (creating the Excel file) """ logger = sip.MapsXmlParser.create_logger(self.log_file_path) items = os.listdir(self.csv_base_path) stitched_csvs = [] for name in items: if name.endswith('_stitched.csv'): stitched_csvs.append(name) stitched_csvs.sort() annotation_tiles = {} for csv in stitched_csvs: current_tiles = sip.MapsXmlParser.load_annotations_from_csv(self.base_header, self.csv_base_path / csv) for key in current_tiles: annotation_tiles[key] = current_tiles[key] csv_output_path = self.csv_base_path / (self.project_name + '_fused' + '.csv') sip.MapsXmlParser.save_annotation_tiles_to_csv(annotation_tiles, self.base_header, csv_output_path) # Delete all the batch files if the option is set for it if delete_batches: for name in items: os.remove(self.csv_base_path / name) if to_excel: logger.info('Saving the annotations csv as an excel file') data_frame = pd.read_csv(csv_output_path) excel_output_path = self.csv_base_path / (self.project_name + '_fused' + '.xlsx') # Create a list of headers whose column should be expanded to fit the content fitting_headers = list(data_frame.columns.values) non_fitting_headers = ['img_path', 'surrounding_tile_names', 'surrounding_tile_exists'] for header in non_fitting_headers: if header in fitting_headers: fitting_headers.remove(header) no_wrap_text_style = Styler(wrap_text=False, shrink_to_fit=False) excel_writer = StyleFrame.ExcelWriter(excel_output_path) styled_df = StyleFrame(data_frame, styler_obj=no_wrap_text_style) styled_df.to_excel(excel_writer, 'MapsAnnotations', index=False, columns_and_rows_to_freeze='A1', best_fit=fitting_headers) excel_writer.save()
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
def _handle_export_button_click(self): utils.create_missing() path = QtWidgets.QFileDialog.getSaveFileName( self, "Сохранить", os.path.join( utils.get_exports_path(), utils.get_file_name_for_export(self.company_id, self.period)), "Файл Excel (*.xlsx)") self.export_button.setEnabled(False) if not path: return elif os.path.exists(path[0]): os.remove(path[0]) headers = parsing.table_columns with StyleFrame.ExcelWriter(path[0]) as writer: export_data = [[float(cell) for cell in row[1:]] for row in self.data] export_string_codes = [int(row[0]) for row in self.data] df = DataFrame(data=export_data, columns=headers, index=export_string_codes) df.index.name = "Код строки" sf = StyleFrame(df) sf.apply_headers_style(Styler(bold=True, font_size=14)) sf.to_excel(writer, sheet_name='Полная таблица', index=True, best_fit=headers) writer.save() self.export_button.setEnabled(True) result = QtWidgets.QMessageBox.question( self, "Успех!", "Данные успешно экспортированы!\nОткрыть файл?", buttons=QtWidgets.QMessageBox.Yes | QtWidgets.QMessageBox.No) if result == QtWidgets.QMessageBox.Yes: command = 'start' if Runtime.is_windows else 'xdg-open' subprocess.Popen([command, os.path.normpath(path[0])], shell=True)
def as_xlsx_file(self): df = pd.DataFrame( data=self.days, columns=['number', 'start_hour', 'end_hour', 'amount']) df.loc[len(df)] = [''] * 4 # Empty row. total_working_days = self.total_working_days() total_working_days_row = ['סה"כ ימים', total_working_days, '', ''] df.loc[len(df)] = total_working_days_row total_working_hours = self.total_working_hours() total_working_hours_row = ['סה"כ שעות', total_working_hours, '', ''] df.loc[len(df)] = total_working_hours_row total_driving_km_row = ['נסיעות', self.driving_in_km, 'ק"מ', ''] df.loc[len(df)] = total_driving_km_row user_info = [self.user.first_name, self.user.last_name, '', ''] df.loc[len(df)] = user_info df.columns = ['יום', 'שעת התחלה', 'שעת סיום', 'מספר גנים'] sf = StyleFrame(df) sf.set_column_width_dict({ ('שעת התחלה', 'שעת סיום'): 16, ('יום', 'מספר גנים'): 13 }) sf.apply_style_by_indexes(indexes_to_style=sf.index[-4:-1], styler_obj=Styler(bold=True), cols_to_style='יום') output = BytesIO() ew = StyleFrame.ExcelWriter(output) sf.to_excel( ew, right_to_left=True, row_to_add_filters=0, ).save() return output
def _load_sheet(self, sheet): sheet_name = sheet['sheet_name'] default_cell_style = sheet.get('default_styles', {}).get('cells') data = defaultdict(list) for col in sheet['columns']: col_name = col['col_name'] col_width = col.get('width') if col_width: self.col_names_to_width[sheet_name][col_name] = col_width for cell in col['cells']: data[col_name].append( Container( cell['value'], Styler( **(cell.get('style') or col.get('style') or default_cell_style or {})).create_style())) sf = StyleFrame(pd.DataFrame(data=data)) self._apply_headers_style(sf, sheet) self._apply_cols_and_rows_dimensions(sf, sheet) sf.to_excel(excel_writer=self.excel_writer, sheet_name=sheet_name, **sheet.get('extra_features', {})) setattr(self, '{}_sf'.format(sheet_name), sf)
def parse_page (driver, links): driver.execute_script("window.open('');") tabs = driver.window_handles driver.switch_to.window(tabs[1]) datalist = [] for link in links: proc_info = [] proc_info.append(link) if 'regNumber=' in link: proc_info.append(link.split("regNumber=")[1]) driver.get(link) try: wait = WebDriverWait(driver, 10) wait.until(EC.presence_of_all_elements_located((By.TAG_NAME, 'td'))) finally: print('No exception') elements = driver.find_elements_by_tag_name('td') for el in elements: has_protocols = True ###### получаем URL ПРОТОКОЛА if ('ПРОТОКОЛ' in el.text): el.click() try: wait = WebDriverWait(driver, 10) wait.until(EC.presence_of_element_located((By.CLASS_NAME, 'toolTipMenu'))) except TimeoutException: has_protocols = False finally: print('No exception') if (has_protocols == True): hover_link = driver.find_elements_by_class_name('toolTipMenu')[0] hover = ActionChains(driver).move_to_element(hover_link) try: hover.perform() elements = driver.find_elements_by_tag_name('li') for el in elements: if ('Печат' in el.text): a = el.get_attribute('onclick') url_prorocol = "{}{}".format(main_url, a.split("\'")[1]) print(url_prorocol) proc_info.append(url_prorocol.split("=")[1]) except WebDriverException: print ("Hover exception") break ###### Получаем Общие сведения о закупке try: wait = WebDriverWait(driver, 10) wait.until(EC.presence_of_all_elements_located((By.TAG_NAME, 'td'))) finally: print('No exception') td_menu = driver.find_elements_by_tag_name('td') for tds in td_menu: if ('ОБЩАЯ' in tds.text): tds.click() try: wait = WebDriverWait(driver, 10) wait.until(EC.presence_of_all_elements_located((By.TAG_NAME, 'td'))) finally: print('No exception') td_inside = driver.find_elements_by_tag_name('td') previous_element = '' for tds_in in td_inside: if ('ИНН' in previous_element): print('ИНН:' + tds_in.text) proc_info.append(tds_in.text) if ('Наименование организации' in previous_element): print('Наименование организации:' + tds_in.text) proc_info.append(tds_in.text) if ('Дата размещения текущей редакции извещения' in previous_element): print('Дата размещения текущей редакции извещения:' + tds_in.text) proc_info.append(tds_in.text) if ('Наименование закупки' in previous_element): print('Наименование закупки:' + tds_in.text) proc_info.append(tds_in.text) previous_element = tds_in.text break ###### Получаем ДОКУМЕНТЫ """ try: wait = WebDriverWait(driver, 10) wait.until(EC.presence_of_all_elements_located((By.TAG_NAME, 'td'))) finally: print('No exception') td_menu = driver.find_elements_by_tag_name('td') for tds in td_menu: if ('ДОКУМЕНТЫ' in tds.text): tds.click() try: wait = WebDriverWait(driver, 10) wait.until(EC.presence_of_all_elements_located((By.TAG_NAME, 'td'))) finally: print('No exception') elem = driver.find_elements_by_xpath("//a[@class='epz_aware']") temp_str = '' for epz_aware in elem: temp_str = temp_str + "<a href=\"" + epz_aware.get_property('href') + "\">"+epz_aware.text+"</a>" proc_info.append([temp_str]) break """ datalist.append(proc_info) my_list = pd.DataFrame(datalist) output_filename = 'links.xlsx' st = Styler(wrap_text=True, shrink_to_fit=True) StyleFrame(my_list, styler_obj=st) writer = StyleFrame.ExcelWriter(output_filename) my_list.to_excel(writer, 'Исходные(текущие)', index=False) writer.save() driver.close()
def _apply_headers_style(self, sf, sheet): default_headers_style = sheet.get('default_styles', {}).get('headers') if default_headers_style: sf.apply_headers_style(styler_obj=Styler(**default_headers_style))
def test_add(self): self.assertEqual(self.yellow_1 + self.bold + self.underline, self.yellow_bold_underline) self.assertEqual(self.yellow_2.bold, False) self.assertEqual(self.yellow_2 + Styler(), self.yellow_1)
if valid_issue == False: print ('Issue not assigned to anyone!') continue if 'status' in issue.raw['fields'] and not (issue.raw['fields']['status'] is None): if 'statusCategory' in issue.raw['fields']['status'] and not (issue.raw['fields']['status']['statusCategory'] is None): if 'name' in issue.raw['fields']['status']['statusCategory'] and not (issue.raw['fields']['status']['statusCategory']['name'] is None): print ('Current status=' + issue.raw['fields']['status']['statusCategory']['name']) state = issue.raw['fields']['status']['statusCategory']['name'] if state != 'Done': new_dic[assignee][2] += 1 if (create_date > start_date and create_date < end_date): print ('This issue created in this time') new_dic[assignee][0] += 1 if (update_date > start_date) and (update_date < end_date) and (state == 'Done'): print ('This issue completed in this time') new_dic[assignee][1] += 1 df = pandas.DataFrame(new_dic) df.index = ['Tasks Created', 'Tasks Completed', 'Tasks pending'] print(df.T) #export output to excel sheet writer = StyleFrame.ExcelWriter("taskreport.xlsx") sf=StyleFrame(df.T) sf.apply_column_style(cols_to_style=df.T.columns, styler_obj=Styler(bg_color=utils.colors.white, bold=True, font=utils.fonts.arial,font_size=8),style_header=True) sf.apply_headers_style(styler_obj=Styler(bg_color=utils.colors.blue, bold=True, font_size=8, font_color=utils.colors.white,number_format=utils.number_formats.general, protection=False)) sf.to_excel(writer, sheet_name='Sheet1', index=True) writer.save()
def group_by_color( filepath, row_height, column_width, include_sources, output_name, ): from StyleFrame import StyleFrame, Styler, utils import numpy as np import pandas as pd if not filepath.endswith(".xlsx"): filepath += ".xlsx" sf = StyleFrame.read_excel(filepath, read_style=True) color_groupings = {} for c_index, c in enumerate(sf.columns): if "Cues" in c.value: for value in sf[c]: if type(value.value) is not str and np.isnan(value.value): continue if value.style.bg_color + "_value" not in color_groupings: color_groupings[value.style.bg_color + "_value"] = [] if include_sources and ( value.style.bg_color + "_source" not in color_groupings ): color_groupings[value.style.bg_color + "_source"] = [] color_groupings[value.style.bg_color + "_value"].append( value.value ) if include_sources: color_groupings[value.style.bg_color + "_source"].append( sf.iloc[0,c_index - 1].value ) # colors of our stuff for k,length in zip(color_groupings.keys(), map(len, color_groupings.values())): print( "color", k + ":", length, "entries") # pad data so it is all the same size maxlen = max(map(len, color_groupings.values())) for k,v in color_groupings.items(): color_groupings[k] = v + ['']*(maxlen - len(v)) print("old length:", len(v), "|| new length:", len(color_groupings[k])) # add padded data to df, print 'er out new_df = pd.DataFrame(color_groupings) new_df defaults = {'font': utils.fonts.calibri, 'font_size': 9} new_sf = StyleFrame(new_df, styler_obj=Styler(**defaults)) for c in new_sf.columns: # color to use color = c.value.strip("_source").strip("_value") # apply style to ALL values (incl headers) new_sf.apply_column_style( cols_to_style=[c.value], styler_obj=Styler(bold=True, font_size=10, bg_color=color, font_color=utils.colors.white), style_header=True, ) # revert style for non-headers new_sf.apply_column_style( cols_to_style=[c.value], styler_obj=Styler(**defaults), style_header=False ) # row height all_rows = new_sf.row_indexes new_sf.set_row_height_dict( row_height_dict={ all_rows[0]: 24, all_rows[1:]: row_height } ) # col width all_cols = tuple(map(lambda x: x.value, new_sf.columns)) new_sf.set_column_width_dict( col_width_dict={ all_cols: column_width } ) # save to excel file if not output_name.endswith(".xlsx"): output_name += ".xlsx" new_sf.to_excel(output_name).save() return 0