def _insert_compare_string_results(string_results, ws, row_height): """ Insert string result into a worksheet Parameters ---------- string_results: dict result to insert ws: Excel worksheet instance row_height: float Height of the row """ # construct thick border thin = Side(border_style="thin", color="000000") border = Border(top=thin, left=thin, right=thin, bottom=thin) row_heights = {} # loop and output result for result in string_results: column = result['column'] if not 'result_df' in result.keys(): ws.append([column, result['error_msg']]) for col in ['A', 'B']: ws['%s%d' %(col, ws.max_row)].style = 'Bad' ws.append(['']) continue result_df = result['result_df'][0][['feature', 'value', 'graph']] value_counts_df = result['result_df'][1] head_row = _insert_df(result_df, ws) # if there is value counts result if len(value_counts_df) > 0: value_counts_df = value_counts_df.rename(columns={'value': 'top 10 values', 'count_x': 'count_1', 'count_y': 'count_2'}) databar_head = _insert_df(value_counts_df, ws, header=True, head_style='60 % - Accent5') for row_idx in range(databar_head, databar_head+value_counts_df.shape[0]+1): row_heights[row_idx] = 25 # add conditional formatting: data bar first = FormatObject(type='num', val=0) second = FormatObject(type='num', val=np.max([value_counts_df['count_1'].max(), value_counts_df['count_2'].max()])) data_bar1 = DataBar(cfvo=[first, second], color=TABLE1_DARK.replace('#', ''), showValue=True, minLength=None, maxLength=None) data_bar2 = DataBar(cfvo=[first, second], color=TABLE2_DARK.replace('#', ''), showValue=True, minLength=None, maxLength=None) # assign the data bar to a rule rule1 = Rule(type='dataBar', dataBar=data_bar1) ws.conditional_formatting.add('B%d:B%d' %(databar_head+1, databar_head+len(value_counts_df)), rule1) rule2 = Rule(type='dataBar', dataBar=data_bar2) ws.conditional_formatting.add('C%d:C%d' %(databar_head+1, databar_head+len(value_counts_df)), rule2) # draw the thick outline border _style_range(ws, 'A%d:C%d'%(head_row, databar_head+len(value_counts_df)), border=border) else: _style_range(ws, 'A%d:C%d'%(head_row, head_row+result_df.shape[0]-1), border=border) # add gap ws.append(['']) _adjust_ws(ws, row_height=row_height, row_heights=row_heights, adjust_type='str')
def highlight_excel(): workbook = load_workbook(filename=excel_file) sheet = workbook.active row_count = sheet.max_row dimensions = sheet.dimensions sheet.auto_filter.ref = dimensions red_background = PatternFill(bgColor='ff4d4d') yellow_background = PatternFill(bgColor='ffff80') green_background = PatternFill(bgColor='ccffcc') diff_style1 = DifferentialStyle(fill=red_background) diff_style2 = DifferentialStyle(fill=yellow_background) diff_style3 = DifferentialStyle(fill=green_background) rule3 = Rule(type="expression", dxf=diff_style3) rule3.formula = ["$D1>=7"] sheet.conditional_formatting.add('A1:C' + str(row_count), rule3) sheet.conditional_formatting.add('E1:J' + str(row_count), rule3) rule1 = Rule(type="expression", dxf=diff_style1) rule1.formula = ["$D1<5"] sheet.conditional_formatting.add('A1:C' + str(row_count), rule1) sheet.conditional_formatting.add('E1:J' + str(row_count), rule1) rule2 = Rule(type="expression", dxf=diff_style2) rule2.formula = (["$D1<7"]) sheet.conditional_formatting.add('A1:C' + str(row_count), rule2) sheet.conditional_formatting.add('E1:J' + str(row_count), rule2) data = pd.read_csv(csv_collection) start_value = min(data['SCORE']) end_value = max(data['SCORE']) data_bar_rule = DataBarRule(start_type="num", start_value=start_value, end_type="num", end_value=end_value, color='53ff4d') sheet.conditional_formatting.add('D2:D' + str(row_count), data_bar_rule) red_text = Font(color='a70000') green_text = Font(color='00bb00') for element in range(2, row_count + 1): sheet['F' + str(element)].number_format = numbers.FORMAT_CURRENCY_USD sheet['G' + str(element)].number_format = numbers.FORMAT_CURRENCY_USD sheet['H' + str(element)].number_format = numbers.FORMAT_CURRENCY_USD cell_value = sheet.cell(element, 8).value if int(cell_value) < 0: sheet.cell(element, 8).font = red_text else: sheet.cell(element, 8).font = green_text workbook.save(excel_file)
def create_xls(args, final_acl): print('Creating the spreadsheet...') filename = os.path.join(args[4], args[5] + ".xlsx") # Create workbook with the one defaut sheet and rename it wb = Workbook() ws1 = wb.active ws1.title = "ACL Report" # Add the headers, set font, colour and column width (from header dictionary) for col, head in zip(range(1,len(header) + 1), header.items()): ws1['{}1'.format(get_column_letter(col))] = head[0] # get_column_letter converts number to letter ws1['{}1'.format(get_column_letter(col))].fill = PatternFill(bgColor=colors.Color("00DCDCDC")) ws1['{}1'.format(get_column_letter(col))].font = Font(bold=True, size=14) ws1.column_dimensions[get_column_letter(col)].width = head[1] # Add the ACE entries. The columns holding numbers are changed to integrars for ace in final_acl: ace[1] = int(ace[1]) ace[8] = int(ace[8]) ws1.append(ace) # Add a key at start with info on the colourised rows for ACEs with frequent hitcnts ws1.insert_rows(1) ws1.insert_rows(2) keys = {'A1': 'Key:', 'B1':'Hit in last 1 day', 'E1':'Hit in last 7 days', 'G1':'Hit in last 30 days', 'I1':'Inactive'} colour = {'B1':'E6B0AA', 'E1':'A9CCE3', 'G1':'F5CBA7', 'I1':'D4EFDF'} for cell, val in keys.items(): ws1[cell] = val ws1['A1'].font = Font(bold=True) for cell, col in colour.items(): ws1[cell].fill = PatternFill(start_color=col, end_color=col, fill_type='solid') ws1.freeze_panes = ws1['A4'] # Freezes the top row (A1) so remains when scrolling ws1.auto_filter.ref = 'A3:L4' # Adds dropdown to headers to the headers # Colours used for columns dependant on the last hit data (J column). Formula is a standard XL formula style_grn = DifferentialStyle(fill=PatternFill(bgColor=colors.Color("00D4EFDF"))) rule_inactive = Rule(type="expression",formula=['=$L1="inactive"'], dxf=style_grn) style_red = DifferentialStyle(fill=PatternFill(bgColor=colors.Color("00E6B0AA"))) rule_1day = Rule(type="expression",formula=["=AND(TODAY()-$J1>=0,TODAY()-$J1<=1)"], dxf=style_red) style_blu = DifferentialStyle(fill=PatternFill(bgColor=colors.Color("00A9CCE3"))) rule_7day = Rule(type="expression", formula=["=AND(TODAY()-$J1>=0,TODAY()-$J1<=7)"], dxf=style_blu) style_org = DifferentialStyle(fill=PatternFill(bgColor=colors.Color("00F5CBA7"))) rule_30day = Rule(type="expression", formula=["=AND(TODAY()-$J1>=0,TODAY()-$J1<=30)"], dxf=style_org) # Apply the rules to workbook and save it for rule in [rule_inactive, rule_1day, rule_7day, rule_30day]: ws1.conditional_formatting.add(ws1.dimensions, rule) wb.save(filename) print('File {} has been created'.format(filename))
def test_conditional_formatting_customRule(worksheet_with_cf, write_conditional_formatting): ws = worksheet_with_cf from openpyxl.formatting.rule import Rule ws.conditional_formatting.add( 'C1:C10', Rule(**{ 'type': 'expression', 'formula': ['ISBLANK(C1)'], 'stopIfTrue': '1' })) cfs = write_conditional_formatting(ws) xml = b"" for cf in cfs: xml += tostring(cf) diff = compare_xml( xml, """ <conditionalFormatting sqref="C1:C10"> <cfRule type="expression" stopIfTrue="1" priority="1"> <formula>ISBLANK(C1)</formula> </cfRule> </conditionalFormatting> """) assert diff is None, diff
def addrulesBW(ws): # rule pour les dates inférieures diff = DifferentialStyle(font=Font(bold=True)) rule5 = Rule(type="expression", dxf=diff, formula=['AND(NOT(ISBLANK($D2)),($D2<TODAY()))']) ws.conditional_formatting.add("D2:D466", rule5)
def example_pyxl(): first = FormatObject(type='percent', val=0) second = FormatObject(type='percent', val=33) third = FormatObject(type='percent', val=67) iconset = IconSet(iconSet='3TrafficLights1', cfvo=[first, second, third], showValue=None, percent=None, reverse=None) # assign the icon set to a rule rule = Rule(type='iconSet', iconSet=iconset) wb = Workbook() dest_filename = 'empty_book.xlsx' ws1 = wb.active ws1.title = "range names" for row in range(1, 40): ws1.append(range(600)) ws2 = wb.create_sheet(title="Pi") ws2['F5'] = 3.14 ws3 = wb.create_sheet(title="Data") for row in range(10, 20): for col in range(27, 54): _ = ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col))) test = f'{get_column_letter(col)}{col}' print(f'Получаем первую ячейку заголовка: {test}') print(ws3['AA10'].value) ws1.conditional_formatting.add(f'{test}', rule) wb.save(filename = dest_filename)
def calc_profit(): # loading excel file to read and write to wb = openpyxl.load_workbook(filename="product_list.xlsx") sheet = wb.active # Creating and styling Profit Column Heading sheet.cell(row=1, column=8).value = "Profit" sheet.cell(row=1, column=8).font = Font(bold=True, name="Helvetica", size=15) sheet.cell(row=1, column=8).fill = PatternFill(patternType="solid", fgColor="00e5ee") sheet.cell(row=1, column=8).alignment = Alignment(horizontal="center") # Calculating Profit row_index=2 for cell in sheet["H"][1:]: cell.value = f"=G{row_index}-F{row_index}" row_index = row_index + 1 cell.alignment = Alignment(horizontal="center") # Adding Conditional Formatting on entire row yellow_background = PatternFill(bgColor="e9ee9e") diff_style = DifferentialStyle(fill=yellow_background) rule = Rule(type="expression", dxf=diff_style) rule.formula = ["$H1<0"] sheet.conditional_formatting.add(f"A1:H{sheet.max_column}", rule) # Same formula as but for a cell in a column # sheet.conditional_formatting.add(f'H2:H{sheet.max_column}', CellIsRule(operator='lessThan', formula=['0'], fill=yellow_background)) wb.save("product_list.xlsx")
def test_conditional_formatting_customRule(self): worksheet = self.ws worksheet.conditional_formatting.add( 'C1:C10', Rule( **{ 'type': 'expression', 'formula': ['ISBLANK(C1)'], 'stopIfTrue': '1', })) cfs = write_conditional_formatting(worksheet) xml = b"" for cf in cfs: xml += tostring(cf) diff = compare_xml( xml, """ <conditionalFormatting sqref="C1:C10"> <cfRule type="expression" stopIfTrue="1" priority="1"> <formula>ISBLANK(C1)</formula> </cfRule> </conditionalFormatting> """) assert diff is None, diff
def format_ws(ws): header = DifferentialStyle(font=Font(bold=True)) r0 = Rule(type="expression", dxf=header) last_row = ws.max_row last_column = get_column_letter(ws.max_column) ws.conditional_formatting.add(f"A1:{last_column}1", r0) create_table(ws, ws.title, f"A1:{last_column}{last_row}") alignment_wrapper(ws)
def _insert_string_results(string_results, ws, row_height): """ Insert result of a string type column into a worksheet Parameters ---------- string_results: dict The result dictionary ws: Excel worksheet instance row_height: float Height of the rows """ # construct thin border thin = Side(border_style="thin", color="000000") border = Border(top=thin, left=thin, right=thin, bottom=thin) # loop and output result for result in string_results: column = result['column'] if 'result_df' not in result.keys(): ws.append([column, result['error_msg']]) for col in ['A', 'B']: ws['%s%d' %(col, ws.max_row)].style = 'Bad' ws.append(['']) continue result_df = result['result_df'][0] value_counts_df = result['result_df'][1] head_row = _insert_df(result_df, ws) # if there is value counts result if len(value_counts_df) > 0: value_counts_df = value_counts_df.rename(columns={column: 'top 10 values'}) databar_head = _insert_df(value_counts_df, ws, header=True, head_style='60 % - Accent5') # add conditional formatting: data bar first = FormatObject(type='num', val=0) second = FormatObject(type='num', val=value_counts_df['count'].max()) data_bar = DataBar(cfvo=[first, second], color=DIS_LINE.replace('#', ''), showValue=True, minLength=None, maxLength=None) # assign the data bar to a rule rule = Rule(type='dataBar', dataBar=data_bar) ws.conditional_formatting.add('B%d:B%d' %(databar_head+1, databar_head+len(value_counts_df)), rule) # draw the thick outline border _style_range(ws, 'A%d:B%d'%(head_row, databar_head+len(value_counts_df)), border=border) else: _style_range(ws, 'A%d:B%d'%(head_row, head_row+result_df.shape[0]-1), border=border) # add gap ws.append(['']) # adjust the worksheet _adjust_ws(ws=ws, row_height=row_height)
def cf_blanks(ws, first_cell, last_cell): # Conditional formatting to highlight blanks as light gray blankFill = PatternFill(start_color='EEEEEE', end_color='EEEEEE', fill_type='solid') dxf = DifferentialStyle(fill=blankFill) blank = Rule(type="expression", formula=["ISBLANK({0})".format(first_cell)], dxf=dxf, stopIfTrue=True) ws.conditional_formatting.add('{0}:{1}'.format(first_cell, last_cell), blank)
def cf_mismatches(ws, first_cell, last_cell): # Conditional formatting to highlight mismatches redFill = PatternFill(start_color='EE6666', end_color='EE6666', fill_type='solid') dxf = DifferentialStyle(fill=redFill) rule = Rule(type="expression", formula=["ISNUMBER(SEARCH(\"|\", {0}))".format(first_cell)], dxf=dxf, stopIfTrue=True) ws.conditional_formatting.add('{0}:{1}'.format(first_cell, last_cell), rule)
def addrulesCouleur(ws, longueur): #rule pour la liste terminé gbackground = PatternFill(start_color='00CC00', end_color='00CC00', fill_type='solid') diff = DifferentialStyle(fill=gbackground) rule4 = Rule(type="expression", dxf=diff) rule4.formula = ['$A2="Terminé !"'] ws.conditional_formatting.add("A2:F" + str(longueur + 1), rule4) #rule pour les dates inférieures redbackground = PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid') diff = DifferentialStyle(fill=redbackground) #['AND(NOT(ISEMPTY($D1));($D1<TODAY()))'] rule5 = Rule(type="expression", dxf=diff, formula=['AND(NOT(ISBLANK($D2)),($D2<TODAY()))']) ws.conditional_formatting.add("D2:D466", rule5) #rule pour la liste en cours ybackground = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid') diff = DifferentialStyle(fill=ybackground) rule1 = Rule(type="expression", dxf=diff, formula=['$A2="En cours"']) ws.conditional_formatting.add("A2:F" + str(longueur + 1), rule1) #rule pour la liste bolqué obackground = PatternFill(start_color='FF9933', end_color='FF9933', fill_type='solid') diff = DifferentialStyle(fill=obackground) rule2 = Rule(type="expression", dxf=diff, formula=['$A2="Bloqué"']) ws.conditional_formatting.add("A2:F" + str(longueur + 1), rule2) #rule pour la liste presque fini lgbackground = PatternFill(start_color='99FF33', end_color='99FF33', fill_type='solid') diff = DifferentialStyle(fill=lgbackground) rule3 = Rule(type="expression", dxf=diff, formula=['$A2="Presque fini"']) ws.conditional_formatting.add("A2:F" + str(longueur + 1), rule3)
def cf_highlight_good_row(ws, first_cell, last_cell, start_row, end_row): # Conditional formatting to highlight rows with no mismatches greenFill = PatternFill(start_color='66EE66', end_color='66EE66', fill_type='solid') dxf = DifferentialStyle(fill=greenFill) rule = Rule(type="expression", formula=[ "COUNT(SEARCH(\"|\",${0}:${1}))<1".format( start_row, end_row) ], dxf=dxf, stopIfTrue=True) ws.conditional_formatting.add('{0}:{1}'.format(first_cell, last_cell), rule)
def add_color_conditioning(self, sheet, address_range): """ This will create and apply a rule for color conditioning the heatmap sheet """ mylog.debug('Begin add color conditioning to heatmap sheet for range %s' % address_range) mylog.debug('Setting minimum and maximum ranges, and mid to percentile with value of 50') first = FormatObject(type='min') mid = FormatObject(type='percentile', val=50) last = FormatObject(type='max') colors = [Color('7FA9D3'), Color('FCBF04'), Color('BF0200')] mylog.debug('Colors in range are %s' % colors) color_scale = ColorScale(cfvo=[first, mid, last], color=colors) rule = Rule(type='colorScale', colorScale=color_scale) mylog.debug('Applying conditional formatting to sheet at range %s' % address_range) sheet.conditional_formatting.add(address_range, rule) return True
def openpyxl_rules(self, contrast_text=True): if self.ignore_blanks: rule_ignore_blanks = Rule(type="containsBlanks", stopIfTrue=True) yield rule_ignore_blanks for l_i in self.__legends: interval_color = _COLOR_MAP.get(l_i.color.upper(), l_i.color) color_fill = PatternFill(start_color=interval_color, end_color=interval_color, fill_type='solid') # use a contrasting text colour, like white, against dark coloured fills if contrast_text and l_i.color.upper() in _CONTRAST_MAP: interval_font = Font(color=_CONTRAST_MAP[l_i.color.upper()], bold=True) else: interval_font = Font(bold=True) if l_i.start is None and l_i.end is None: # make everything the same colour rule = ColorScaleRule(start_type='percentile', start_value=0, start_color=interval_color, end_type='percentile', end_value=100, end_color=interval_color, font=interval_font) elif l_i.start is None: rule = CellIsRule(operator='lessThan', formula=[str(l_i.end)], stopIfTrue=True, fill=color_fill, font=interval_font) elif l_i.end is None: rule = CellIsRule(operator='greaterThanOrEqual', formula=[str(l_i.start)], stopIfTrue=True, fill=color_fill, font=interval_font) else: rule = CellIsRule(operator='between', formula=[str(l_i.start), str(l_i.end)], stopIfTrue=True, fill=color_fill, font=interval_font) yield rule
def format_data(writer, current_date): workbook = writer.book sheet = workbook[current_date] #sheet = workbook.active #Fill ("NEW") rule blue_bg = PatternFill(bgColor=Color(indexed=27)) diff_style = DifferentialStyle(fill=blue_bg) rule = Rule(type="expression", dxf=diff_style) rule.formula = ['$D2 = "NEW"'] sheet.conditional_formatting.add("D2:E201", rule) #Arrow rule rule_arrow = IconSetRule('3Arrows', 'num', [-200, 0, 1]) sheet.conditional_formatting.add("D2:E201", rule_arrow) write_further_info(sheet) workbook.save(FILENAME)
def formatting_xls(): wb = Workbook() ws = wb.active # Create fill redFill = PatternFill(start_color='EE1111', end_color='EE1111', fill_type='solid') # Add a two-color scale # Takes colors in excel 'RRGGBB' style. ws.conditional_formatting.add('A1:A10', ColorScaleRule(start_type='min', start_color='AA0000', end_type='max', end_color='00AA00')) # Add a three-color scale ws.conditional_formatting.add('B1:B10', ColorScaleRule(start_type='percentile', start_value=10, start_color='AA0000', mid_type='percentile', mid_value=50, mid_color='0000AA', end_type='percentile', end_value=90, end_color='00AA00')) # Add a conditional formatting based on a cell comparison # addCellIs(range_string, operator, formula, stopIfTrue, wb, font, border, fill) # Format if cell is less than 'formula' ws.conditional_formatting.add('C2:C10', CellIsRule(operator='lessThan', formula=['C$1'], stopIfTrue=True, fill=redFill)) # Format if cell is between 'formula' ws.conditional_formatting.add('D2:D10', CellIsRule(operator='between', formula=['1','5'], stopIfTrue=True, fill=redFill)) # Format using a formula ws.conditional_formatting.add('E1:E10', FormulaRule(formula=['ISBLANK(E1)'], stopIfTrue=True, fill=redFill)) # Aside from the 2-color and 3-color scales, format rules take fonts, borders and fills for styling: myFont = Font() myBorder = Border() ws.conditional_formatting.add('E1:E10', FormulaRule(formula=['E1=0'], font=myFont, border=myBorder, fill=redFill)) # Highlight cells that contain particular text by using a special formula red_text = Font(color="9C0006") red_fill = PatternFill(bgColor="FFC7CE") dxf = DifferentialStyle(font=red_text, fill=red_fill) rule = Rule(type="containsText", operator="containsText", text="highlight", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("highlight",A1)))'] ws.conditional_formatting.add('A1:F40', rule) wb.save("test.xlsx")
def export_excel(_path, _df=None, _wb=None, _sheet_name='sheet1', _letter_fmt=None, _append=False, _frz='B2', _auto_flt=True, _auto_sz=False, _header_height=None, _col_width_=[20,20], _header_fmt=None, _header_rot=0, _zoom=100, _heatmap=0, _is_index=True, _index_name='Index', _header_txtcol='000000', _header_fillcol='d9f2f8', _img=None): import openpyxl as px from openpyxl.utils import get_column_letter time_start = time.perf_counter() if _path==None and _wb==None: print('[Error] Both of _path and _bw are None.') return print('[Exporting Excel file ...] Sheet : "{0}"'.format(_sheet_name)) #------------------------------------------- # 初期設定 #------------------------------------------- # Workbook作成 if _wb == None: if _append: # 既存ファイルにシート追加 try: wb = px.load_workbook(_path) except: _append = False # ファイルが存在しないときは新規作成 if not _append: # 新規ファイル wb = px.Workbook() else: wb = _wb _append = True # Worksheet作成 ws = wb.create_sheet(title=_sheet_name) #------------------------------------------- # DataFrameをWorksheetに書き込み #------------------------------------------- if _df is not None: #----- 作業用にDataFrameをコピー ----- df = _df.copy() # Timestampを文字列に変換(そのままだとエラーになるので) list_timestamp_col = list() # Timestampのセルが存在する列を探して文字列に変換する for col_name, col in df.iteritems(): for item in col: tp = type(item) if tp is pd._libs.tslibs.timestamps.Timestamp: list_timestamp_col.append(col_name) break for col in list_timestamp_col: df[col] = df[col].astype(str) df[col] = df[col].replace('NaT', '') #----- Excelファイル用フォーマットの作成 ----- base_font = '游ゴシック' from openpyxl.styles.fonts import Font from openpyxl.styles import PatternFill font_header_row = Font(name=base_font, b=True, sz=10, color=_header_txtcol) font_header_col = Font(name=base_font, b=True, sz=10, color=_header_txtcol) font_cell = Font(name=base_font, sz=10) align_header_row = px.styles.Alignment(horizontal="center", vertical="center", wrapText=True, textRotation=_header_rot) align_header_col = px.styles.Alignment(horizontal="center", vertical="center", wrapText=True) fill_header_row = PatternFill(patternType='solid', fgColor=_header_fillcol) fill_header_col = PatternFill(patternType='solid', fgColor=_header_fillcol) #----- データ出力 ----- # DataFrameをWorksheetにExport l = df.columns.tolist() if _is_index: l.insert(0, _index_name) # 行のindexを先頭列に追加 ws.append(l) count = 0 for i, row in df.iterrows(): # 一行ずつwsに追加していく l = row.values.tolist() if _is_index: l.insert(0, row.name) # 行のindexを先頭列に追加 ws.append(l) count += 1 print('\r - データコピー {0}/{1}'.format(count, len(df)), end="") print('') #----- Worksheetの書式設定 ----- # ヘッダー行(既定値) for cell in list(ws.rows)[0]: cell.font = font_header_row cell.alignment = align_header_row cell.fill = fill_header_row # ヘッダー行(個別) if _header_fmt != None: list_cell = list(ws.rows)[0] for head, fmt in _header_fmt.items(): try: index = list(df.columns).index(head) if _is_index: index += 1 cell = list_cell[index] except: continue # rotation try: rotation = fmt['rotation'] cell.alignment = px.styles.Alignment(horizontal="center", vertical="center", wrapText=True, textRotation=rotation) except: pass # 文字色 try: text_color = fmt['txtcol'] cell.font = Font(name=base_font, b=True, sz=10, color=text_color) except: pass # 背景色 try: fill_color = fmt['fillcol'] cell.fill = PatternFill(patternType='solid', fgColor=fill_color) except: pass # 列ごとの書式設定用のリスト作成 list_dtxt_pat = list() list_dfill_pat = list() if _header_fmt != None: for head, fmt in _header_fmt.items(): try: index = list(df.columns).index(head) if _is_index: index += 1 except: continue # 文字色 try: text_color = fmt['dtxtcol'] list_dtxt_pat.append([index, Font(name=base_font, sz=10, color=text_color)]) except: pass # 背景色 try: dfill_color = fmt['dfillcol'] list_dfill_pat.append([index, PatternFill(patternType='solid', fgColor=dfill_color)]) except: pass # データ行書式設定 count = 0 for row in ws.iter_rows(min_row=2): # 書式設定 for cell in row: cell.font = font_cell # 列ごとの書式設定で上書き for list_pat in list_dtxt_pat: # 個別設定がある列を順に処理する idx = list_pat[0] row[idx].font = list_pat[1] for list_pat in list_dfill_pat: # 個別設定がある列を順に処理する idx = list_pat[0] row[idx].fill = list_pat[1] # Index列がある場合はIndex用設定 if _is_index: row[0].font = font_header_col # 先頭列のみ太字 row[0].alignment = align_header_col # 先頭列のみセンタリング row[0].fill = fill_header_col # 先頭列の塗りつぶし count += 1 print('\r - 書式設定 {0}/{1}'.format(count, len(df)), end="") print('') #----- セルの文字書式 ----- if type(_letter_fmt) is dict: # _header_fmtがあれば不要だが互換性のために残してある for col in ws.iter_cols(): col_name = col[0].value if col_name in _letter_fmt: num_format = _letter_fmt[col_name] for cell in col: cell.number_format = num_format elif type(_letter_fmt) is str: for col in ws.iter_cols(): for cell in col: cell.number_format = _letter_fmt # 列ごとの個別設定で上書き if _header_fmt != None: list_col = list(_header_fmt.keys()) for col in ws.iter_cols(): col_name = col[0].value if col_name in list_col: # 列書式一覧の辞書にこの列が存在する try: fmt = _header_fmt[col_name] num_format = fmt['dtxtformat'] for cell in col: cell.number_format = num_format except: pass # Worksheetの列幅調整 if _auto_sz: # 自動調整 for col in ws.columns: max_length = 0 column = col[0].column column = get_column_letter(column) # 数字をアルファベットに変換 cols = col if _header_rot!=90 else col[1:] for cell in cols: if len(str(cell.value)) > max_length: max_length = len(str(cell.value)) adjusted_width = (max_length + 2) * 1.1 ws.column_dimensions[column].width = adjusted_width else: for col in ws.columns: column = col[0].column # 列番号を取得 col_letter = get_column_letter(column) # 列番号を列記号に変換 width = _col_width_[0] if column == 1 else _col_width_[1] # 列幅 ws.column_dimensions[col_letter].width = width # 列ごとの個別調整 if _header_fmt != None: list_col = list(ws.columns) for head, fmt in _header_fmt.items(): try: width = fmt['width'] index = list(df.columns).index(head) if _is_index: index += 1 col = list_col[index] column = col[0].column # 列番号を取得 col_letter = get_column_letter(column) # 列番号を列記号に変換 ws.column_dimensions[col_letter].width = width except: pass # Worksheetの行の高さ調整 if _header_height != None: ws.row_dimensions[1].height = _header_height # ヒートマップ from openpyxl.formatting.rule import ColorScale, FormatObject from openpyxl.styles import Color if _heatmap == 1: # 赤 → 白 → 青 first = FormatObject(type='min') last = FormatObject(type='max') # colors match the format objects: colors = [Color('F8696B'), Color('5A8AC6')] # a three color scale would extend the sequences mid = FormatObject(type='percentile', val=50) colors.insert(1, Color('FCFCFF')) cs3 = ColorScale(cfvo=[first, mid, last], color=colors) # create a rule with the color scale from openpyxl.formatting.rule import Rule rule = Rule(type='colorScale', colorScale=cs3) # 対象範囲を示す文字列を作成 rg = 'A2:' + get_column_letter(ws.max_column)+str(ws.max_row) ws.conditional_formatting.add(rg, rule) elif _heatmap == 2: # 白 → 橙 → 赤 first = FormatObject(type='min') last = FormatObject(type='max') # colors match the format objects: colors = [Color('FFFFFF'), Color('F8696B')] # a three color scale would extend the sequences mid = FormatObject(type='percentile', val=50) colors.insert(1, Color('FFEB84')) cs3 = ColorScale(cfvo=[first, mid, last], color=colors) # create a rule with the color scale from openpyxl.formatting.rule import Rule rule = Rule(type='colorScale', colorScale=cs3) # 対象範囲を示す文字列を作成 rg = 'A2:' + get_column_letter(ws.max_column)+str(ws.max_row) ws.conditional_formatting.add(rg, rule) elif _heatmap == 3: # 赤 → 橙 → 白 first = FormatObject(type='min') last = FormatObject(type='max') # colors match the format objects: colors = [Color('F8696B'), Color('FFFFFF')] # a three color scale would extend the sequences mid = FormatObject(type='percentile', val=25) colors.insert(1, Color('FFEB84')) cs3 = ColorScale(cfvo=[first, mid, last], color=colors) # create a rule with the color scale from openpyxl.formatting.rule import Rule rule = Rule(type='colorScale', colorScale=cs3) # 対象範囲を示す文字列を作成 rg = 'A2:' + get_column_letter(ws.max_column)+str(ws.max_row) ws.conditional_formatting.add(rg, rule) # 枠固定 if _frz != None: ws.freeze_panes = _frz # オートフィルタ if _auto_flt: ws.auto_filter.ref = 'A1:' + get_column_letter(ws.max_column)+'1' # 表示倍率 ws.sheet_view.zoomScale = _zoom #------------------------------------------- # Worksheetに画像を挿入 #------------------------------------------- if _img != None: from openpyxl.drawing.image import Image for img in _img: fpath = img[0] # 挿入する画像ファイル anchor = img[1] # 挿入位置 px_img = Image(fpath) px_img.anchor = anchor ws.add_image(px_img) #------------------------------------------- # Excelファイルに書き込み #------------------------------------------- # 最後に不要なシートを削除 if 'Sheet' in wb.sheetnames: wb.remove(wb['Sheet']) print(' - ファイル書き込み...', end='') wb.save(_path) # 画像ファイル削除 if _img != None: for img in _img: is_delete = False # ファイルを削除するか否か if len(img) > 2: is_delete = img[2] if is_delete: # ファイル削除 os.remove(fpath) print ('\n ---> Finished. (処理時間:{0:.3f}[sec])'.format(time.perf_counter() - time_start )) return wb
import os import re from fuzzywuzzy import fuzz from operator import itemgetter, add from openpyxl import worksheet from openpyxl import load_workbook from openpyxl.styles import PatternFill, colors from openpyxl.styles.differential import DifferentialStyle from openpyxl.formatting.rule import Rule import collections full_path = os.path.abspath(os.getcwd()) # file_name = r"C:\Users\willlee\Desktop\Manufacturing Issue\2020\Test Station Part Number.xlsx" red_background = PatternFill(bgColor=colors.BLUE) diff_style = DifferentialStyle(fill=red_background) rule = Rule(type="expression", dxf=diff_style) rule.formula = ["istext($AA2)"] # <---need to change to dynamic def text_splitter(text, regex_expression, take_which_group=1): matches = re.finditer(regex_expression, text, re.MULTILINE) for matchNum, match in enumerate(matches, start=1): print("Match {matchNum} was found at {start}-{end}: {match}".format( matchNum=matchNum, start=match.start(), end=match.end(), match=match.group())) for groupNum in range(0, len(match.groups())):
def create_matrix_tab( worksheet: Worksheet, matrix_rows: list, account_overall_scores: dict, accounts: dict ) -> Worksheet: """ Function to generate the workbook based data already gatered and parsed. Parameters: matrix_rows (list): Direct input for the itemized worksheet. account_overall_scores (dict): Mapping from account id to account overall score accounts (list): List of accounts. Returns: Workbook: The workbook object ready to be saved. """ formatting = MatrixTabFormatting() ### Add data ### # header rows account_header = [] for account in accounts: if 'account_name' in account: account_header.append(account['account_name']) else: account_header.append(account['accountId']) # add header row worksheet.append(formatting.HEADERS + account_header) # add account score rows worksheet.append([formatting.ACCOUNT_SCORE, '', ''] + list(account_overall_scores.values())) # add requirement rows rows = sorted(matrix_rows, key=lambda row: row['description']) # sort by description field for row in rows: worksheet.append([ row['description'], row['requirementId'], row['severity'] ] + row['numFailing']) if all(score == scores_table.NOT_APPLICABLE for score in row['numFailing']): worksheet.row_dimensions[worksheet.max_row].hidden = True # add footer worksheet.append(['']) # empty row worksheet.append([f'Scored Against CSS Version: {formatting.version}']) worksheet.append([f'Report Generated at {datetime.now()} GMT']) ### Apply formatting ### worksheet.title = formatting.TITLE # bold headers for header_cell in worksheet[1][:len(formatting.HEADERS)]: header_cell.font = Font(bold=True, size=11) # vertically align account names for readability for account_name in worksheet[1][len(formatting.HEADERS):]: account_name.alignment = Alignment(text_rotation=45) # word wrap long descriptions for description in worksheet['A']: description.alignment = Alignment(wrap_text=True) # freeze first column and first row worksheet.freeze_panes = formatting.FREEZE # bold overall scores overall_score_row = 2 for grade_cell in worksheet[overall_score_row][:worksheet.max_column]: grade_cell.font = Font(bold=True, size=11) # right align ACCOUNT_SCORE cell worksheet[overall_score_row][1].alignment = Alignment(horizontal='right') # set appropriate font size for row in worksheet.iter_rows(min_row=overall_score_row + 1): for cell in row: cell.font = Font(size=9) # set Description column width worksheet.column_dimensions['A'].width = 80 # set other column widths for col_index in range(len(formatting.HEADERS) + 1, worksheet.max_column + 1): worksheet.column_dimensions[get_column_letter(col_index)].width = 8 # hide requirement id column worksheet.column_dimensions['B'].hidden = True # cell coloring/conditional formatting # format account scores colors_ordered_by_weight = list(reversed(sorted(formatting.severity_formatting.values(), key=lambda severity: severity['weight']))) for account_score in worksheet[overall_score_row][len(formatting.HEADERS):]: try: score = int(account_score.value) except: # pylint: disable=bare-except score = 0 account_score.number_format = '0' # colors in reverse order by weight so first one encountered is correct for color in colors_ordered_by_weight: if score >= color['weight']: account_score.fill = PatternFill(start_color=color['fill'], end_color=color['fill'], fill_type='solid') account_score.font = Font(color=color['font_color'], bold=True) break # add conditional formatting for error scores score_cell_range = '{}3:{}{:d}'.format( get_column_letter(len(formatting.HEADERS) + 1), get_column_letter(worksheet.max_column), len(matrix_rows) + 2 ) score_cell_top_left = '{}3'.format(get_column_letter(len(formatting.HEADERS) + 1)) for error_format in formatting.error_formatting: # convert python string to excel string if isinstance(error_format['value'], str): check_value = formatting.excel_string(error_format['value']) else: check_value = error_format['value'] worksheet.conditional_formatting.add( score_cell_range, Rule( type='expression', formula=[f'{check_value}={score_cell_top_left}'], priority=worksheet.conditional_formatting.max_priority + 1, stopIfTrue=True, dxf=DifferentialStyle( font=Font( color=error_format['font_color'] ), fill=PatternFill( start_color=error_format['fill'], end_color=error_format['fill'], fill_type='solid', ) ) ) ) severity_column_reference = '${}3'.format(get_column_letter(formatting.SEVERITY_COLUMN)) for severity, severity_format in formatting.severity_formatting.items(): # convert python string to excel string check_value = formatting.excel_string(severity) worksheet.conditional_formatting.add( score_cell_range, Rule( type='expression', formula=[f'{check_value}={severity_column_reference}'], priority=worksheet.conditional_formatting.max_priority + 1, stopIfTrue=True, dxf=DifferentialStyle( font=Font( color=severity_format['font_color'] ), fill=PatternFill( start_color=severity_format['fill'], end_color=severity_format['fill'], fill_type='solid', ) ) ) ) return worksheet
def store_annuary(path, **kwargs): wb = openpyxl.Workbook() wb.remove(wb.active) if "characters" in kwargs: print("Storing characters ...") ws_chars = wb.create_sheet(title="Personnages") ws_chars[openpyxl.utils.get_column_letter(2) + "1"] = "Nom" ws_chars[openpyxl.utils.get_column_letter(3) + "1"] = "Classe" ws_chars[openpyxl.utils.get_column_letter(4) + "1"] = "Couleur" ws_chars[openpyxl.utils.get_column_letter(5) + "1"] = "Niveau" i = 2 for character in kwargs["characters"]: ws_chars[openpyxl.utils.get_column_letter(2) + str(i)] = character.name ws_chars[openpyxl.utils.get_column_letter(3) + str(i)] = character.class_ color_cell = openpyxl.utils.get_column_letter(4) + str(i) ws_chars[color_cell] = character.main_trait if character.main_trait == "rouge": ws_chars[color_cell].font = openpyxl.styles.Font( color=openpyxl.styles.colors.RED, bold=True) elif character.main_trait == "bleu": ws_chars[color_cell].font = openpyxl.styles.Font( color=openpyxl.styles.colors.BLUE, bold=True) elif character.main_trait == "jaune": ws_chars[color_cell].font = openpyxl.styles.Font( color=openpyxl.styles.colors.YELLOW, bold=True) ws_chars[openpyxl.utils.get_column_letter(5) + str(i)] = character.level i += 1 if "jobs" in kwargs and "anvils" in kwargs: print("Storing jobs & masteries ...") ws_jobs = wb.create_sheet(title="Métiers") ws_jobs[openpyxl.utils.get_column_letter(2) + "1"] = "Nom" ws_jobs[openpyxl.utils.get_column_letter(3) + "1"] = "Métier" for idx, mastery in enumerate(JobAnvil.accepted_tiers): ws_jobs[openpyxl.utils.get_column_letter(4 + idx) + "1"] = mastery.capitalize() anvils_map = {} for anvil in kwargs["anvils"]: if anvil.id_ not in anvils_map: anvils_map[anvil.id_] = [] anvils_map[anvil.id_].append(anvil) i = 2 for idx, job in enumerate(kwargs["jobs"]): ws_jobs[openpyxl.utils.get_column_letter(2) + str(i + idx)] = job.name ws_jobs[openpyxl.utils.get_column_letter(3) + str(i + idx)] = job.job for ma_idx, mastery in enumerate(JobAnvil.accepted_tiers): for anvil in anvils_map[job.id_]: if anvil.tier == mastery: cell = openpyxl.utils.get_column_letter( 4 + ma_idx) + str(i + idx) ws_jobs[cell] = mastery.capitalize() if anvil.gold or anvil.bronze: if anvil.gold: ft_color = openpyxl.styles.colors.DARKYELLOW bg_color = openpyxl.styles.colors.YELLOW else: ft_color = openpyxl.styles.colors.DARKRED bg_color = openpyxl.styles.colors.RED ws_jobs[cell].font = openpyxl.styles.Font( color=ft_color, bold=True) ws_jobs[cell].fill = openpyxl.styles.PatternFill( start_color=bg_color, end_color=bg_color, fill_type="solid") break if "reputations" in kwargs: print("Storing reputations ...") ws_reps = wb.create_sheet(title="Réputations") for idx, faction in enumerate(Reputation.accepted_factions): ws_reps[openpyxl.utils.get_column_letter(3 + idx) + "1"] = faction.capitalize().replace('_', ' ') chars_map = {} for reputation in kwargs["reputations"]: if reputation.name not in chars_map: chars_map[reputation.name] = [] chars_map[reputation.name].append(reputation) i = 2 idx = 0 for name, reputations in chars_map.items(): ws_reps[openpyxl.utils.get_column_letter(2) + str(i)] = name.capitalize() for idx, faction in enumerate(Reputation.accepted_factions): for reputation in reputations: if reputation.faction == faction: ws_reps[openpyxl.utils.get_column_letter(3 + 1 + idx) + str(i)] = reputation.level.capitalize() break initial_color = (128, 0, 0) final_color = (153, 255, 102) n_steps = len(Reputation.accepted_levels) step = () for i in range(3): step += (int((initial_color[i] + final_color[i]) / n_steps), ) for idx_level, level in enumerate(Reputation.accepted_levels): color = triple_to_rgb( ((idx_level + 1) * step[0], (idx_level + 1) * step[1], (idx_level + 1) * step[2])) print(color) fill = openpyxl.styles.PatternFill(bgColor=color) dxf = DifferentialStyle(fill=fill) rule = Rule(type="containsText", operator="containsText", text=level.capitalize(), dxf=dxf) ws_reps.conditional_formatting.add( "C2:" + openpyxl.utils.get_column_letter(3 + idx) + str(i), rule) try: wb.save(filename=path) print("Register stored !") except Exception: print("Could not save annuary as xlsx")
kiirando_fajl = "balance.xlsx" logging.info('A kiírandó file: ' + kiirando_fajl) arfolyam_file = "arfolyamok.csv" logging.info('A használt árfolyamok file: ' + arfolyam_file) arfolyam = {} egyenleg2 = "" # xml fájl betöltése mydoc = minidom.parse(olvasando_fajl) # Munkalap stílusának beálítása forintra still = NamedStyle(name="Pénzecske") # Ilyen nevű stílus hozzáadása still.number_format = '#,##0 "HUF";-#,##0 "HUF"' # A számformátum beállítása forintra diff_style = DifferentialStyle(fill=PatternFill( bgColor='C6EFCE', fgColor='006100')) # A feltételes formázás beállítása rule = Rule(type="expression", dxf=diff_style) # Feltételes kifejezés megadása rule.formula = ["$B2>0"] # Formula a feltételes formázáshoz # Árfolyam file meglétének ellenőrzése és frissítése ha két óránál régebbi fileido = datetime.fromtimestamp(os.stat( './arfolyamok.csv').st_ctime) # a meglévő file idejének lekérdezése now = datetime.now() max_delay = timedelta(hours=2) try: if now - fileido > max_delay: shutil.copyfile('arfolyamok.csv', 'arfolyamok.old') logging.debug( "Régi a fájl ezért lekérdezem az árfolyamokat: {} ".format( fileido)) c = CurrencyRates() # Aktuális árfolyam lekérdezése arfolyam = c.get_rates('HUF') # átszámítás forint vs. valutákra
def create_spreadsheet(base_channel_report): # Setup Excel file filename = "server_os_report.xlsx" workbook = Workbook() ws1 = workbook.create_sheet("Sheet_A") ws1.title = "Overview Linux OS" ws2 = workbook.create_sheet("Sheet_B") ws2.title = "Data" ws3 = workbook.create_sheet("Sheet_C") ws3.title = "Approver Breakdown" sheet = workbook["Data"] for row in base_channel_report: sheet.append(row) darkyellow_background = PatternFill(bgColor=colors.DARKYELLOW) yellow_background = PatternFill(bgColor=colors.YELLOW) blue_background = PatternFill(bgColor=colors.BLUE) green_background = PatternFill(bgColor=colors.GREEN) diff_style7 = DifferentialStyle(fill=darkyellow_background) rule7 = Rule(type="expression", dxf=diff_style7) rule7.formula = ["$C1=7"] sheet.conditional_formatting.add("A1:E600", rule7) diff_style8 = DifferentialStyle(fill=blue_background) rule8 = Rule(type="expression", dxf=diff_style8) rule8.formula = ["$C1=7"] sheet.conditional_formatting.add("A1:E600", rule8) diff_style6 = DifferentialStyle(fill=yellow_background) rule6 = Rule(type="expression", dxf=diff_style6) rule6.formula = ["$C1=6"] sheet.conditional_formatting.add("A1:E600", rule6) diff_style5 = DifferentialStyle(fill=green_background) rule5 = Rule(type="expression", dxf=diff_style5) rule5.formula = ["$C1=5"] sheet.conditional_formatting.add("A1:E600", rule5) sheet = workbook["Overview Linux OS"] data = [ ['Centos5', '=COUNTIFS(Data!$C$2:$C$600,5, Data!$B$2:$B$600,"Centos")'], ['Centos6', '=COUNTIFS(Data!$C$2:$C$600,6, Data!$B$2:$B$600,"Centos")'], ['Centos7', '=COUNTIFS(Data!$C$2:$C$600,7, Data!$B$2:$B$600,"Centos")'], ['Centos8', '=COUNTIFS(Data!$C$2:$C$600,8, Data!$B$2:$B$600,"Centos")'], ['RedHat5', '=COUNTIFS(Data!$C$2:$C$600,5, Data!$B$2:$B$600,"Redhat")'], ['RedHat6', '=COUNTIFS(Data!$C$2:$C$600,6, Data!$B$2:$B$600,"Redhat")'], ['RedHat7', '=COUNTIFS(Data!$C$2:$C$600,7, Data!$B$2:$B$600,"Redhat")'], ['RedHat8', '=COUNTIFS(Data!$C$2:$C$600,8, Data!$B$2:$B$600,"Redhat")'], ['Unknown', '=COUNTIFS(Data!$C$2:$C$600,0)'] ] for row in data: sheet.append(row) pie = PieChart() labels = Reference(sheet, min_col=1, min_row=2, max_row=9) data = Reference(sheet, min_col=2, min_row=1, max_row=9) pie.add_data(data, titles_from_data=True) pie.set_categories(labels) pie.title = "OS Breakdown" pie.height = 20 pie.width = 40 # Cut the first slice out of the pie slice = DataPoint(idx=0, explosion=20) pie.series[0].data_points = [slice] sheet.add_chart(pie, "A1") std=workbook.get_sheet_by_name('Sheet') workbook.remove_sheet(std) unique_cost_center = set(x for l in base_channel_report for x in l) ws3 = workbook.create_sheet("Sheet_C") ws3.title = "Cost Center Breakdown" sheet = workbook["Cost Centre Breakdown"] data =[] for x in unique_cost_center: countifs = "=COUNTIFS(Data!$H$2:$H$600,%s)" % x data.append([x,countifs]) for row in data: sheet.append(row) pie = PieChart() labels = Reference(sheet, min_col=1, min_row=2, max_row=len(data)) data = Reference(sheet, min_col=2, min_row=1, max_row=len(data)) pie.add_data(data, titles_from_data=True) pie.set_categories(labels) pie.title = "Cost Center Breakdown" pie.height = 20 pie.width = 40 # Cut the first slice out of the pie slice = DataPoint(idx=0, explosion=20) pie.series[0].data_points = [slice] sheet.add_chart(pie, "A1") # save file workbook.save(filename)
datetime.fromtimestamp(random.uniform(from_date, to_date)) for i in range(0, count)) dates.sort() for i in range(0, count): float = random.random() * 1000 date = dates[i] yield DemoObject("Object %d" % (i + 1), "This is a text which\n will wrap by default", random.choice((True, False)), float, float, random.randint(1, 3), date, date, date) bold_true = Rule(type='expression', dxf=DifferentialStyle(font=Font(bold=True)), formula=["$C3"]) class ColumnDemoSheet(TableSheet): table_name = "ColumnDemo" char = CharColumn(header="CharColumn") text = TextColumn(header="TextColumn", freeze=True) boolean = BoolColumn(header="BoolColumn", row_style="Row, integer", conditional_formatting=bold_true) integer = IntColumn(header="IntColumn", group=True) float = FloatColumn(header="FloatColumn", group=True) datetime = DatetimeColumn(header="DatetimeColumn", group=True) date = DateColumn(header="DateColumn")
def _xlsx_create_rpm_details(self, wb, rpm_table): df = self._rename_rpm_detail_columns(rpm_table) ws_rd = wb.create_sheet("RPMs details") ( normal_font, normal_border, normal_fill, ) = self._style.get_rpm_xslx_table_normal() normal_align = Alignment(horizontal="left", vertical="top", wrap_text=True) center_align = Alignment(horizontal="center", vertical="center", wrap_text=True) ( imt_font, imt_border, imt_fill, ) = self._style.get_rpm_xslx_table_important_failed() ( ctc_font, ctc_border, ctc_fill, ) = self._style.get_rpm_xslx_table_critical_failed() ctc_style = self._get_critical_failed_style() imt_style = self._get_important_failed_style() ( header_font, header_border, header_fill, ) = self._style.get_rpm_xslx_table_header() cols = df.columns xlsx_cols = list(string.ascii_lowercase[0:len(cols)]) for i in range(len(xlsx_cols)): cell_no = xlsx_cols[i] + "1" ws_rd[cell_no] = cols[i] ws_rd[cell_no].font = header_font ws_rd[cell_no].fill = header_fill ws_rd[cell_no].border = header_border ws_rd.column_dimensions["J"].hidden = True curr_row_no = 2 supported_col_no = 0 for i in range(len(cols)): if cols[i] == "Driver Flag: Supported": supported_col_no = i break vld_lic = self._style.get_valid_licenses() for i, row in df.iterrows(): rpm_license = row["License"] for col_idx in range(len(cols)): cell_no = xlsx_cols[col_idx] + str(curr_row_no) val = row[cols[col_idx]] ws_rd[cell_no].font = normal_font ws_rd[cell_no].fill = normal_fill ws_rd[cell_no].border = normal_border ws_rd[cell_no].alignment = normal_align if cols[col_idx] == "Symbols Check": val = self._get_sym_check_failed(val) if val == "": val = "All passed!" ws_rd[cell_no] = val ws_rd[cell_no].alignment = center_align else: ws_rd[cell_no] = val elif cols[col_idx] == "Driver Flag: Supported": val = "All passed!" ws_rd[cell_no] = val ws_rd[cell_no].alignment = center_align elif cols[col_idx] == "License": lcs_chk = self._fmt_driver_license_check( rpm_license, row["Driver Licenses"], vld_lic) if lcs_chk == "": if rpm_license == "": ws_rd[cell_no] = "No License" ws_rd[cell_no].font = imt_font ws_rd[cell_no].fill = imt_fill ws_rd[cell_no].border = imt_border else: ws_rd[cell_no] = rpm_license else: ws_rd[cell_no] = lcs_chk ws_rd[cell_no].font = imt_font ws_rd[cell_no].fill = imt_fill ws_rd[cell_no].border = imt_border else: ws_rd[cell_no] = str(val) failed_drivers = self._get_supported_driver_failed( row["Driver Flag: Supported"]) driver_count = len(failed_drivers) if driver_count > 0: # format supported information for sp_idx in range(driver_count): cell_no = xlsx_cols[supported_col_no] + str(curr_row_no + sp_idx) ws_rd[cell_no] = failed_drivers[sp_idx] ws_rd[cell_no].font = ctc_font ws_rd[cell_no].fill = ctc_fill ws_rd[cell_no].border = ctc_border ws_rd[cell_no].alignment = normal_align if driver_count > 1: # need merge cell for col_idx in range(len(cols)): if col_idx != supported_col_no: start_cell_no = xlsx_cols[col_idx] + str(curr_row_no) end_cell_no = xlsx_cols[col_idx] + str(curr_row_no + driver_count - 1) merge_range = start_cell_no + ":" + end_cell_no ws_rd.merge_cells(merge_range) if driver_count == 0: curr_row_no += 1 else: curr_row_no += driver_count records = curr_row_no empty_vendor = Rule(type="expression", dxf=imt_style) empty_vendor.formula = ['$C2 = ""'] ws_rd.conditional_formatting.add(f"C2:C{records}", empty_vendor) sf_rule = Rule(type="expression", dxf=ctc_style) sf_rule.formula = [ '=OR($H2="All passed!", AND(ISNUMBER(FIND(":", $H2)), ISNUMBER(FIND("external", $H2))))' ] ws_rd.conditional_formatting.add(f"H2:H{records}", sf_rule) sig_rule = Rule(type="expression", dxf=ctc_style) sig_rule.formula = ['=AND($D2 <> "", $D2 <> "(none)")'] ws_rd.conditional_formatting.add(f"D2:D{records}", sig_rule) sym_rule = Rule(type="expression", dxf=ctc_style) sym_rule.formula = ['=ISNUMBER(FIND(".ko", $I2))'] ws_rd.conditional_formatting.add(f"I2:I{records}", sym_rule)
def _xlsx_create_vendor_summary(self, wb, rpm_table): ws_vs = wb.create_sheet("vendor summary") sm_table = self._get_summary_table(rpm_table) for row in dataframe_to_rows(sm_table, index=False, header=True): ws_vs.append(row) ( header_font, header_border, header_fill, ) = self._style.get_rpm_xslx_table_header() for cell in ws_vs[1]: cell.font = header_font cell.border = header_border cell.fill = header_fill for row in ws_vs[f"A1:G{len(sm_table.index)+1}"]: for cell in row: cell.border = header_border last_record_row_no = len(sm_table.index) + 1 ( great_font, great_border, great_fill, ) = self._style.get_rpm_xslx_table_great_row() great_row_style = DifferentialStyle( font=great_font, border=great_border, fill=great_fill, ) great_row = Rule(type="expression", dxf=great_row_style) great_row.formula = [ 'AND($A2 <> "", VALUE(LEFT($C2, FIND(" ",$C2)-1))=$B2, VALUE(LEFT($D2, FIND(" ", $D2) - 1)) = $B2, VALUE(LEFT($E2, FIND(" ", $E2) - 1)) = $B2, VALUE(LEFT($F2, FIND(" ", $F2)-1))=$B2, VALUE(LEFT($G2, FIND(" ", $G2) - 1))=0)' ] ws_vs.conditional_formatting.add(f"A2:G{last_record_row_no}", great_row) ipt_style = self._get_important_failed_style() ctc_style = self._get_critical_failed_style() empty_vendor = Rule(type="expression", dxf=ipt_style) empty_vendor.formula = ['$A2 = ""'] ws_vs.conditional_formatting.add(f"A2:A{last_record_row_no}", empty_vendor) supported_failed = Rule(type="expression", dxf=ctc_style) supported_failed.formula = [ 'VALUE(LEFT($C2, FIND(" ", $C2) - 1)) <> $B2' ] ws_vs.conditional_formatting.add(f"C2:C{last_record_row_no}", supported_failed) license_check = Rule(type="expression", dxf=ipt_style) license_check.formula = ['VALUE(LEFT($D2, FIND(" ", $D2) - 1)) <> $B2'] ws_vs.conditional_formatting.add(f"D2:D{last_record_row_no}", license_check) sig_check = Rule(type="expression", dxf=ipt_style) sig_check.formula = ['VALUE(LEFT($E2, FIND(" ", $E2) - 1)) <> $B2'] ws_vs.conditional_formatting.add(f"E2:E{last_record_row_no}", sig_check) wm_check = Rule(type="expression", dxf=ipt_style) wm_check.formula = ['VALUE(LEFT($F2, FIND(" ", $F2) - 1)) <> $B2'] ws_vs.conditional_formatting.add(f"F2:F{last_record_row_no}", wm_check) sym_failed = Rule(type="expression", dxf=ctc_style) sym_failed.formula = ['VALUE(LEFT($G2, FIND(" ", $G2) - 1)) <> 0'] ws_vs.conditional_formatting.add(f"G2:G{last_record_row_no}", sym_failed)
def placing_excel(master_data_one, master_data_two): ''' function that places all information into the summary dashboard sheet :param master_data_one: python dictionary of latest ar data. :param master_data_two: python dictionary of last ar data. :return: populated Excel dashboard. ''' for row_num in range(2, ws.max_row + 1): project_name = ws.cell(row=row_num, column=2).value print(project_name) if project_name in master_data_one.projects: dca_one = master_data_one.data[project_name]['DCA'] try: dca_two = master_data_two.data[project_name]['DCA'] change = up_or_down(dca_one, dca_two) ws.cell(row=row_num, column=4).value = change except KeyError: ws.cell(row=row_num, column=4).value = 'NEW' ws.cell(row=row_num, column=5).value = master_data_one.data[project_name]['DCA'] start_date_one = master_data_one.data[project_name]['Start Date'] ws.cell(row=row_num, column=6).value = start_date_one try: start_date_two = master_data_two.data[project_name][ 'Start Date'] s_date_diff = cal_date_difference(start_date_one, start_date_two) ws.cell(row=row_num, column=7).value = s_date_diff except KeyError: ws.cell(row=row_num, column=7).value = 0 end_date_one = master_data_one.data[project_name]['End Date'] ws.cell(row=row_num, column=8).value = end_date_one try: end_date_two = master_data_two.data[project_name]['End Date'] e_date_diff = cal_date_difference(end_date_one, end_date_two) ws.cell(row=row_num, column=9).value = e_date_diff except KeyError: ws.cell(row=row_num, column=9).value = 0 ws.cell( row=row_num, column=10 ).value = master_data_one.data[project_name]['in year baseline'] ws.cell( row=row_num, column=11 ).value = master_data_one.data[project_name]['in year forecast'] ws.cell( row=row_num, column=12 ).value = master_data_one.data[project_name]['in year variance'] wlc_one = master_data_one.data[project_name]['WLC baseline'] ws.cell(row=row_num, column=13).value = wlc_one try: wlc_two = master_data_two.data[project_name]['WLC baseline'] wlc_diff = wlc_one - wlc_two ws.cell(row=row_num, column=14).value = wlc_diff except KeyError: ws.cell(row=row_num, column=14).value = 0 except TypeError: ws.cell(row=row_num, column=14).value = 'Check wlc value/data' for row_num in range(2, ws.max_row + 1): project_name = ws.cell(row=row_num, column=2).value if project_name in master_data_two.data: ws.cell(row=row_num, column=3).value = master_data_two[project_name]['DCA'] # Highlight cells that contain RAG text, with background and text the same colour. column E. ag_text = Font(color="00a5b700") ag_fill = PatternFill(bgColor="00a5b700") dxf = DifferentialStyle(font=ag_text, fill=ag_fill) rule = Rule(type="containsText", operator="containsText", text="Amber/Green", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("Amber/Green",e1)))'] ws.conditional_formatting.add('e1:e100', rule) ar_text = Font(color="00f97b31") ar_fill = PatternFill(bgColor="00f97b31") dxf = DifferentialStyle(font=ar_text, fill=ar_fill) rule = Rule(type="containsText", operator="containsText", text="Amber/Red", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("Amber/Red",e1)))'] ws.conditional_formatting.add('e1:e100', rule) red_text = Font(color="00fc2525") red_fill = PatternFill(bgColor="00fc2525") dxf = DifferentialStyle(font=red_text, fill=red_fill) rule = Rule(type="containsText", operator="containsText", text="Red", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("Red",E1)))'] ws.conditional_formatting.add('E1:E100', rule) green_text = Font(color="0017960c") green_fill = PatternFill(bgColor="0017960c") dxf = DifferentialStyle(font=green_text, fill=green_fill) rule = Rule(type="containsText", operator="containsText", text="Green", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("Green",e1)))'] ws.conditional_formatting.add('E1:E100', rule) amber_text = Font(color="00fce553") amber_fill = PatternFill(bgColor="00fce553") dxf = DifferentialStyle(font=amber_text, fill=amber_fill) rule = Rule(type="containsText", operator="containsText", text="Amber", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("Amber",e1)))'] ws.conditional_formatting.add('e1:e100', rule) # Highlight cells that contain RAG text, with background and black text columns G to L. ag_text = Font(color="000000") ag_fill = PatternFill(bgColor="00a5b700") dxf = DifferentialStyle(font=ag_text, fill=ag_fill) rule = Rule(type="containsText", operator="containsText", text="Amber/Green", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("Amber/Green",G1)))'] ws.conditional_formatting.add('G1:L100', rule) ar_text = Font(color="000000") ar_fill = PatternFill(bgColor="00f97b31") dxf = DifferentialStyle(font=ar_text, fill=ar_fill) rule = Rule(type="containsText", operator="containsText", text="Amber/Red", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("Amber/Red",G1)))'] ws.conditional_formatting.add('G1:L100', rule) red_text = Font(color="000000") red_fill = PatternFill(bgColor="00fc2525") dxf = DifferentialStyle(font=red_text, fill=red_fill) rule = Rule(type="containsText", operator="containsText", text="Red", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("Red",G1)))'] ws.conditional_formatting.add('G1:L100', rule) green_text = Font(color="000000") green_fill = PatternFill(bgColor="0017960c") dxf = DifferentialStyle(font=green_text, fill=green_fill) rule = Rule(type="containsText", operator="containsText", text="Green", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("Green",G1)))'] ws.conditional_formatting.add('G1:L100', rule) amber_text = Font(color="000000") amber_fill = PatternFill(bgColor="00fce553") dxf = DifferentialStyle(font=amber_text, fill=amber_fill) rule = Rule(type="containsText", operator="containsText", text="Amber", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("Amber",G1)))'] ws.conditional_formatting.add('G1:L100', rule) # highlighting new projects red_text = Font(color="00fc2525") white_fill = PatternFill(bgColor="000000") dxf = DifferentialStyle(font=red_text, fill=white_fill) rule = Rule(type="containsText", operator="containsText", text="NEW", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("NEW",F1)))'] ws.conditional_formatting.add('D1:D100', rule) # assign the icon set to a rule first = FormatObject(type='num', val=-1) second = FormatObject(type='num', val=0) third = FormatObject(type='num', val=1) iconset = IconSet(iconSet='3Arrows', cfvo=[first, second, third], percent=None, reverse=None) rule = Rule(type='iconSet', iconSet=iconset) ws.conditional_formatting.add('D1:D100', rule) return wb
def placing_excel(dict_one, dict_two): for row_num in range(2, ws.max_row + 1): project_name = ws.cell(row=row_num, column=3).value print(project_name) if project_name in dict_one: ws.cell(row=row_num, column=4).value = dict_one[project_name]['Total Forecast'] ws.cell(row=row_num, column=6).value = dict_one[project_name]['Change'] ws.cell(row=row_num, column=7).value = convert_rag_text(dict_one[project_name]['Departmental DCA']) ws.cell(row=row_num, column=8).value = convert_rag_text(dict_one[project_name]['GMPP - IPA DCA last quarter']) ws.cell(row=row_num, column=9).value = convert_bc_stage_text(dict_one[project_name]['BICC approval point']) ws.cell(row=row_num, column=10).value = dict_one[project_name]['Start of Operation'] ws.cell(row=row_num, column=11).value = dict_one[project_name]['Project End Date'] ws.cell(row=row_num, column=12).value = convert_rag_text(dict_one[project_name]['SRO Finance confidence']) ws.cell(row=row_num, column=13).value = dict_one[project_name]['Last time at BICC'] ws.cell(row=row_num, column=14).value = dict_one[project_name]['Next at BICC'] for row_num in range(2, ws.max_row + 1): project_name = ws.cell(row=row_num, column=3).value if project_name in dict_two: ws.cell(row=row_num, column=5).value = convert_rag_text(dict_two[project_name]['Departmental DCA']) # Highlight cells that contain RAG text, with background and text the same colour. column E. ag_text = Font(color="00a5b700") ag_fill = PatternFill(bgColor="00a5b700") dxf = DifferentialStyle(font=ag_text, fill=ag_fill) rule = Rule(type="containsText", operator="containsText", text="A/G", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("A/G",e1)))'] ws.conditional_formatting.add('e1:e100', rule) ar_text = Font(color="00f97b31") ar_fill = PatternFill(bgColor="00f97b31") dxf = DifferentialStyle(font=ar_text, fill=ar_fill) rule = Rule(type="containsText", operator="containsText", text="A/R", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("A/R",e1)))'] ws.conditional_formatting.add('e1:e100', rule) red_text = Font(color="00fc2525") red_fill = PatternFill(bgColor="00fc2525") dxf = DifferentialStyle(font=red_text, fill=red_fill) rule = Rule(type="containsText", operator="containsText", text="R", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("R",E1)))'] ws.conditional_formatting.add('E1:E100', rule) green_text = Font(color="0017960c") green_fill = PatternFill(bgColor="0017960c") dxf = DifferentialStyle(font=green_text, fill=green_fill) rule = Rule(type="containsText", operator="containsText", text="G", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("G",e1)))'] ws.conditional_formatting.add('E1:E100', rule) amber_text = Font(color="00fce553") amber_fill = PatternFill(bgColor="00fce553") dxf = DifferentialStyle(font=amber_text, fill=amber_fill) rule = Rule(type="containsText", operator="containsText", text="A", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("A",e1)))'] ws.conditional_formatting.add('e1:e100', rule) # highlight cells in column g ag_text = Font(color="000000") ag_fill = PatternFill(bgColor="00a5b700") dxf = DifferentialStyle(font=ag_text, fill=ag_fill) rule = Rule(type="containsText", operator="containsText", text="A/G", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("A/G",g1)))'] ws.conditional_formatting.add('g1:g100', rule) ar_text = Font(color="000000") ar_fill = PatternFill(bgColor="00f97b31") dxf = DifferentialStyle(font=ar_text, fill=ar_fill) rule = Rule(type="containsText", operator="containsText", text="A/R", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("A/R",g1)))'] ws.conditional_formatting.add('g1:g100', rule) red_text = Font(color="000000") red_fill = PatternFill(bgColor="00fc2525") dxf = DifferentialStyle(font=red_text, fill=red_fill) rule = Rule(type="containsText", operator="containsText", text="R", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("R",g1)))'] ws.conditional_formatting.add('g1:g100', rule) green_text = Font(color="000000") green_fill = PatternFill(bgColor="0017960c") dxf = DifferentialStyle(font=green_text, fill=green_fill) rule = Rule(type="containsText", operator="containsText", text="G", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("G",g1)))'] ws.conditional_formatting.add('g1:g100', rule) amber_text = Font(color="000000") amber_fill = PatternFill(bgColor="00fce553") dxf = DifferentialStyle(font=amber_text, fill=amber_fill) rule = Rule(type="containsText", operator="containsText", text="A", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("A",g1)))'] ws.conditional_formatting.add('g1:g100', rule) # highlight cells in column H ag_text = Font(color="000000") ag_fill = PatternFill(bgColor="00a5b700") dxf = DifferentialStyle(font=ag_text, fill=ag_fill) rule = Rule(type="containsText", operator="containsText", text="A/G", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("A/G",h1)))'] ws.conditional_formatting.add('h1:h100', rule) ar_text = Font(color="000000") ar_fill = PatternFill(bgColor="00f97b31") dxf = DifferentialStyle(font=ar_text, fill=ar_fill) rule = Rule(type="containsText", operator="containsText", text="A/R", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("A/R",h1)))'] ws.conditional_formatting.add('h1:h100', rule) red_text = Font(color="000000") red_fill = PatternFill(bgColor="00fc2525") dxf = DifferentialStyle(font=red_text, fill=red_fill) rule = Rule(type="containsText", operator="containsText", text="R", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("R",h1)))'] ws.conditional_formatting.add('h1:h100', rule) green_text = Font(color="000000") green_fill = PatternFill(bgColor="0017960c") dxf = DifferentialStyle(font=green_text, fill=green_fill) rule = Rule(type="containsText", operator="containsText", text="G", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("G",h1)))'] ws.conditional_formatting.add('h1:h100', rule) amber_text = Font(color="000000") amber_fill = PatternFill(bgColor="00fce553") dxf = DifferentialStyle(font=amber_text, fill=amber_fill) rule = Rule(type="containsText", operator="containsText", text="A", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("A",h1)))'] ws.conditional_formatting.add('h1:h100', rule) # highlight cells in column H ag_text = Font(color="000000") ag_fill = PatternFill(bgColor="00a5b700") dxf = DifferentialStyle(font=ag_text, fill=ag_fill) rule = Rule(type="containsText", operator="containsText", text="A/G", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("A/G",l1)))'] ws.conditional_formatting.add('l1:l100', rule) ar_text = Font(color="000000") ar_fill = PatternFill(bgColor="00f97b31") dxf = DifferentialStyle(font=ar_text, fill=ar_fill) rule = Rule(type="containsText", operator="containsText", text="A/R", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("A/R",l1)))'] ws.conditional_formatting.add('l1:l100', rule) red_text = Font(color="000000") red_fill = PatternFill(bgColor="00fc2525") dxf = DifferentialStyle(font=red_text, fill=red_fill) rule = Rule(type="containsText", operator="containsText", text="R", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("R",l1)))'] ws.conditional_formatting.add('l1:l100', rule) green_text = Font(color="000000") green_fill = PatternFill(bgColor="0017960c") dxf = DifferentialStyle(font=green_text, fill=green_fill) rule = Rule(type="containsText", operator="containsText", text="G", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("G",l1)))'] ws.conditional_formatting.add('l1:l100', rule) amber_text = Font(color="000000") amber_fill = PatternFill(bgColor="00fce553") dxf = DifferentialStyle(font=amber_text, fill=amber_fill) rule = Rule(type="containsText", operator="containsText", text="A", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("A",l1)))'] ws.conditional_formatting.add('l1:l100', rule) # Highlight cells that contain RAG text, with background and black text columns G to L. # ag_text = Font(color="000000") # ag_fill = PatternFill(bgColor="00a5b700") # dxf = DifferentialStyle(font=ag_text, fill=ag_fill) # rule = Rule(type="uniqueValues", operator="equal", text="A/G", dxf=dxf) # rule.formula = ['NOT(ISERROR(SEARCH("A/G",G1)))'] # ws.conditional_formatting.add('G1:L100', rule) # # ar_text = Font(color="000000") # ar_fill = PatternFill(bgColor="00f97b31") # dxf = DifferentialStyle(font=ar_text, fill=ar_fill) # rule = Rule(type="uniqueValues", operator="equal", text="A/R", dxf=dxf) # rule.formula = ['NOT(ISERROR(SEARCH("A/R",G1)))'] # ws.conditional_formatting.add('G1:L100', rule) # # red_text = Font(color="000000") # red_fill = PatternFill(bgColor="00fc2525") # dxf = DifferentialStyle(font=red_text, fill=red_fill) # rule = Rule(type="uniqueValues", operator="equal", text="R", dxf=dxf) # rule.formula = ['NOT(ISERROR(SEARCH("R",G1)))'] # ws.conditional_formatting.add('G1:L100', rule) # # green_text = Font(color="000000") # green_fill = PatternFill(bgColor="0017960c") # dxf = DifferentialStyle(font=green_text, fill=green_fill) # rule = Rule(type="uniqueValues", operator="equal", text="G", dxf=dxf) # rule.formula = ['NOT(ISERROR(SEARCH("Green",G1)))'] # ws.conditional_formatting.add('G1:L100', rule) # # amber_text = Font(color="000000") # amber_fill = PatternFill(bgColor="00fce553") # dxf = DifferentialStyle(font=amber_text, fill=amber_fill) # rule = Rule(type="uniqueValues", operator="equal", text="A", dxf=dxf) # rule.formula = ['NOT(ISERROR(SEARCH("A",G1)))'] # ws.conditional_formatting.add('G1:L100', rule) # highlighting new projects red_text = Font(color="00fc2525") white_fill = PatternFill(bgColor="000000") dxf = DifferentialStyle(font=red_text, fill=white_fill) rule = Rule(type="uniqueValues", operator="equal", text="NEW", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("NEW",F1)))'] ws.conditional_formatting.add('F1:F100', rule) # assign the icon set to a rule first = FormatObject(type='num', val=-1) second = FormatObject(type='num', val=0) third = FormatObject(type='num', val=1) iconset = IconSet(iconSet='3Arrows', cfvo=[first, second, third], percent=None, reverse=None) rule = Rule(type='iconSet', iconSet=iconset) ws.conditional_formatting.add('F1:F100', rule) # change text in last at next at BICC column for row_num in range(2, ws.max_row + 1): if ws.cell(row=row_num, column=13).value == '-2 weeks': ws.cell(row=row_num, column=13).value = 'Last BICC' if ws.cell(row=row_num, column=13).value == '2 weeks': ws.cell(row=row_num, column=13).value = 'Next BICC' if ws.cell(row=row_num, column=13).value == 'Today': ws.cell(row=row_num, column=13).value = 'This BICC' if ws.cell(row=row_num, column=14).value == '-2 weeks': ws.cell(row=row_num, column=14).value = 'Last BICC' if ws.cell(row=row_num, column=14).value == '2 weeks': ws.cell(row=row_num, column=14).value = 'Next BICC' if ws.cell(row=row_num, column=14).value == 'Today': ws.cell(row=row_num, column=14).value = 'This BICC' # highlight text in bold ft = Font(bold=True) for row_num in range(2, ws.max_row + 1): lis = ['This week', 'Next week', 'Last week', 'Two weeks', 'Two weeks ago', 'This mth', 'Last mth', 'Next mth', '2 mths', '3 mths', '-2 mths', '-3 mths', '-2 weeks', 'Today', 'Last BICC', 'Next BICC', 'This BICC', 'Later this mth'] if ws.cell(row=row_num, column=10).value in lis: ws.cell(row=row_num, column=10).font = ft if ws.cell(row=row_num, column=11).value in lis: ws.cell(row=row_num, column=11).font = ft if ws.cell(row=row_num, column=13).value in lis: ws.cell(row=row_num, column=13).font = ft if ws.cell(row=row_num, column=14).value in lis: ws.cell(row=row_num, column=14).font = ft return wb
def submit(self): ## Create workbook and sheets global g_w wb = openpyxl.Workbook() sheet0 = wb.create_sheet(index=0, title='Read_Me') sheet1 = wb.create_sheet(index=1, title='2019_2020') ## Create Read me sheet sheet0[ 'B2'].value = 'Hey all. This excel file is the result of a python script' sheet0['B4'].value = 'The script uses the FPL API and json data to import your history from the ' \ 'website and then exports it to this file ' sheet0[ 'B6'].value = 'See more info at my site https://www.fezfiles.com/fpl-data-fetcher. Report bugs, contact at [email protected] or Twitter: https://twitter.com/fez9o' sheet0[ 'B8'].value = 'be aware that the code is raw and a lof of improvements can be made.' sheet0[ 'B10'].value = 'Your data is in the next sheet. Change sheet tabs below or hold "CTRL+PgDown"' ## Import history JSON data url1 = 'https://fantasy.premierleague.com/api/entry/{}/history/'.format( self.fpl_prompt.get()) url2 = 'https://fantasy.premierleague.com/api/bootstrap-static/' json_history = requests.get(url1).json() json_live = requests.get(url2).json() num_of_gw = len(json_history['current']) participants = json_live['total_players'] ## Import league data and team name from new url due to change in FPL api since 2019-2020 Season url9 = 'https://fantasy.premierleague.com/api/entry/{}/'.format( self.fpl_prompt.get()) json_info = requests.get(url9).json() team_name = json_info['name'] ## Import gameweek history and insert data in sheet header1 = [ 'GW', 'GP', 'GW AVG', 'GW HS', 'PB', 'TM', 'TC', 'GR', 'PGR', 'OP', 'OR', 'POR', 'Position', 'TV' ] headerrow = 1 for key in range(14): sheet1.cell(row=headerrow, column=key + 3).value = str(header1[key]) o_r1 = [ ] # To make a list of overall rank for inserting change in rank symbols for each in json_history['current']: g_w = each['event'] points = each['points'] p_b = each['points_on_bench'] t_m = each['event_transfers'] t_c = each['event_transfers_cost'] g_w_r = each['rank'] o_r = each['overall_rank'] t_v = each['value'] o_r1.append( o_r) # This is for creating rank symbols in the excel sheet o_p = each['total_points'] p_g_r = 100 - (((participants - g_w_r) / participants) * 100) p_o_r = 100 - (((participants - o_r) / participants) * 100) p_o_s = 1 # placeholder which will be replaced later down the code history_list = [ g_w, points, p_b, t_m, t_c, g_w_r, p_g_r, o_p, o_r, p_o_r, p_o_s, t_v / 10 ] for rownum in range(g_w + 1, g_w + 2): sheet1.cell(row=rownum, column=3).value = g_w for rownum in range(g_w + 1, g_w + 2): sheet1.cell(row=rownum, column=4).value = points for rownum in range(g_w + 1, g_w + 2): for key in range(2, 12): sheet1.cell(row=rownum, column=key + 5).value = history_list[key] ## Appending overall rank changes to a list so it becomes easier to make symbols in the table p_o_s_1 = [] p_o_s_1.append(0) for count in range(1, g_w): if o_r1[count - 1] > o_r1[count]: p_o_s = 1 elif o_r1[count - 1] == o_r1[count]: p_o_s = 0 else: p_o_s = -1 p_o_s_1.append(p_o_s) for each in json_history['current']: g_w = each['event'] sheet1.cell(row=g_w + 1, column=15).value = p_o_s_1[g_w - 1] ## Import gameweek average points and highest points for each in json_live['events']: g_w = each['id'] h_p = each['highest_score'] a_v_g = each['average_entry_score'] for rownum in range(g_w + 1, g_w + 2): sheet1.cell(row=rownum, column=5).value = a_v_g for rownum in range(g_w + 1, g_w + 2): sheet1.cell(row=rownum, column=6).value = h_p ## Import all football players' data in premier league player_d = {} for each in json_live['elements']: pl_position = each['element_type'] pl_id = each['id'] pl_name = each['web_name'] player_d[pl_id] = pl_name tot_player = len(player_d) ## Select data for Chip usage and enter in GW history as highlights wildcardfill = PatternFill(start_color='ffff0000', end_color='ffff0000', fill_type='solid') freehitfill = PatternFill(start_color='ffff00ff', end_color='ffff00ff', fill_type='solid') bboostfill = PatternFill(start_color='ffffa500', end_color='ffffa500', fill_type='solid') triplecapfill = PatternFill(start_color='ff0099ff', end_color='ff0099ff', fill_type='solid') gwh_col = range(3, 17) for each in json_history['chips']: chipgw = each['event'] chip = each['name'] while chip == 'wildcard': for key in gwh_col: wc = sheet1.cell(row=chipgw + 1, column=key) wc.fill = wildcardfill break while chip == 'bboost': for key in gwh_col: wc = sheet1.cell(row=chipgw + 1, column=key) wc.fill = bboostfill break while chip == 'freehit': for key in gwh_col: wc = sheet1.cell(row=chipgw + 1, column=key) wc.fill = freehitfill break while chip == '3xc': for key in gwh_col: wc = sheet1.cell(row=chipgw + 1, column=key) wc.fill = triplecapfill break ## Import weekly team player data and points gwteamheaderow = 50 #41+9 gwtitle = 3 for gw in range(1, 48): #39+9 sheet1.cell(row=gwteamheaderow, column=gwtitle).value = str('GW {}'.format(gw)) sheet1.cell(row=gwteamheaderow, column=gwtitle + 1).value = str('P {}'.format(gw)) gwtitle = gwtitle + 2 capfill = PatternFill(start_color='ff15dd43', end_color='ff15dd43', fill_type='solid') vcapfill = PatternFill(start_color='ff00FFDA', end_color='ff00FFDA', fill_type='solid') benchfill = PatternFill(start_color='ffBA6B12', end_color='ffBA6B12', fill_type='solid') for rownum in range(62, 66): # 62 to 66 for colnum in range(3, 97): # 79+18 to 97 bench = sheet1.cell(row=rownum, column=colnum) bench.fill = benchfill gwteamcol = ( (39 + 9) * 2 - int(num_of_gw * 2) ) - 1 # To accomodate people who started late. Don't ask how I came up with this formula. capfont = Font(underline='single') # gwteamcol = 1 for each in json_history['current']: g_w = each['event'] url3 = 'https://fantasy.premierleague.com/api/entry/{}/event/{}/picks/'.format( self.fpl_prompt.get(), g_w) json_pick = requests.get(url3).json() gwteamcol = gwteamcol + 2 gwteamrow = 42 + 9 url4 = 'https://fantasy.premierleague.com/api/event/{}/live/'.format( g_w) json_points = requests.get(url4).json() total_players = len(json_points['elements']) for each1 in json_pick['picks']: player_id = each1['element'] player_idnew = int(player_id) captain = each1['is_captain'] vicecapt = each1['is_vice_captain'] multiplier = each1['multiplier'] pl_name = player_d[player_id] plist = {player_id: pl_name} # Assigning points to selected players for each2 in json_points['elements']: pointsvid = each2['id'] for t1 in range(0, total_players): if player_idnew == json_points['elements'][t1]['id']: pl_points = json_points['elements'][t1]['stats'][ 'total_points'] sheet1.cell(row=gwteamrow, column=gwteamcol + 1).value = pl_points if multiplier == 2: sheet1.cell(row=gwteamrow, column=gwteamcol + 1).value = pl_points * 2 elif multiplier == 3: sheet1.cell(row=gwteamrow, column=gwteamcol + 1).value = pl_points * 3 break # Assigning captaincy and vice-captaincy for values in plist.values(): sheet1.cell(row=gwteamrow, column=gwteamcol).value = values if captain == True: capf = sheet1.cell(row=gwteamrow, column=gwteamcol) capf.fill = capfill capf.font = capfont if vicecapt == True: vcapf = sheet1.cell(row=gwteamrow, column=gwteamcol) vcapf.fill = vcapfill gwteamrow = gwteamrow + 1 ## Import classic league history sheet1.merge_cells('BR1:BS1') # League Rank header sheet1['BR1'].value = 'League Rank History' clrow = 2 num_of_leagues = len(json_info['leagues']['classic']) clheader = ['League Name', 'Rank'] for leaguecolumn in range(2): sheet1.cell(row=clrow, column=leaguecolumn + 70).value = str( clheader[leaguecolumn]) for each in json_info['leagues']['classic']: leaguename = each['name'] leagueposition = each['entry_rank'] league_data = [leaguename, leagueposition] clrow = clrow + 1 for clcol in range(2): sheet1.cell(row=clrow, column=clcol + 70).value = league_data[clcol] ## Import Cup History sheet1.merge_cells('BV1:BZ1') # Cup History Header sheet1['BV1'].value = 'FPL Cup History' url8 = 'https://fantasy.premierleague.com/api/entry/{}/cup/'.format( self.fpl_prompt.get()) json_cup = requests.get(url8).json() num_of_cups = len(json_cup['cup_matches']) cuplist = ['GW', 'Team Name 1', 'Points 1', 'Team Name 2', 'Points 2 '] cuprow = 2 for col in range(5): sheet1.cell(row=cuprow, column=col + 74).value = str(cuplist[col]) if num_of_cups > 0: for each in json_cup['cup_matches']: cupgw = each['event'] entry_1 = each['entry_1_name'] entry_2 = each['entry_2_name'] entrypoints_1 = each['entry_1_points'] entrypoints_2 = each['entry_2_points'] cup_data = [ cupgw, entry_1, entrypoints_1, entry_2, entrypoints_2 ] cuprow = cuprow + 1 for colnum in range(5): sheet1.cell(row=cuprow, column=colnum + 74).value = cup_data[colnum] else: sheet1.cell(row=3, column=74).value = "Failed to qualify for the cup" ## Import h2h details sheet1.merge_cells('BN1:BO1') # H2H Team Header sheet1['BN1'].value = 'H2H History' num_of_h2h = len(json_info['leagues']['h2h']) h2h_header = ['H2H League', 'Rank'] h2hrow = 2 for h2hcol in range(2): sheet1.cell(row=h2hrow, column=h2hcol + 66).value = str(h2h_header[h2hcol]) if num_of_h2h > 0: for each in json_history['leagues']['h2h']: h2hname = each['name'] h2hrank = each['entry_rank'] h2h_data = [h2hname, h2hrank] h2hrow = h2hrow + 1 for colnum in range(2): sheet1.cell(row=h2hrow, column=colnum + 66).value = h2h_data[colnum] else: sheet1.cell(row=3, column=66).value = "No H2H leagues entered." \ \ ## Import Gameweek Transfer history sheet1.merge_cells('CV1:CZ1') sheet1['CV1'].value = 'Transfer History' transferheader = [ 'GW', 'Transfer In', 'Value In ', 'Transfer Out', 'Value Out' ] transferhrow = 2 for tkey in range(5): sheet1.cell(row=transferhrow, column=tkey + 100).value = str( transferheader[tkey]) #82+18 url5 = 'https://fantasy.premierleague.com/api/entry/{}/transfers/'.format( self.fpl_prompt.get()) json_transfer = requests.get(url5).json() gwtransferrow = 2 gwtransfercol = 82 + 18 num_of_t = len(json_transfer) if num_of_t == 0: sheet1.cell(row=gwtransferrow + 1, column=gwtransfercol).value = "No Transfers Made" else: for each in json_transfer: transferin = each['element_in'] transferout = each['element_out'] incost = each['element_in_cost'] outcost = each['element_out_cost'] transfergw = each['event'] t_in_name = player_d.get(transferin, 0) t_out_name = player_d.get(transferout, 0) trans_data = [ transfergw, t_in_name, incost / 10, t_out_name, outcost / 10 ] gwtransferrow = gwtransferrow + 1 for colnum in range(5): sheet1.cell(row=gwtransferrow, column=colnum + gwtransfercol).value = trans_data[colnum] ## Import Overall Dream Team Data sheet1.merge_cells('BI1:BJ1') # Dream Team Header sheet1['BI1'].value = str('Overall Dream Team') overalldtheader = ['Player Name', 'Total Points'] for odtcol in range(2): sheet1.cell(row=2, column=odtcol + 61).value = str( overalldtheader[odtcol]) url6 = 'https://fantasy.premierleague.com/api/dream-team/' json_dreamteam = requests.get(url6).json() dtrow = 2 for each in json_dreamteam['team']: dtpoints = each['points'] dtplayer = each['element'] dt_name = player_d.get(dtplayer, 0) dt_data = [dt_name, dtpoints] dtrow = dtrow + 1 for colnum in range(2): sheet1.cell(row=dtrow, column=colnum + 61).value = dt_data[colnum] ## Import Weekly Dream Team Data dtteamheaderrow = 59 + 9 dttitle = 3 for dt in range(1, 39 + 9): sheet1.cell(row=dtteamheaderrow, column=dttitle).value = str('GW {}'.format(dt)) sheet1.cell(row=dtteamheaderrow, column=dttitle + 1).value = str('P {}'.format(dt)) dttitle = dttitle + 2 dtteamcol = 1 for each in range( 1, 39 + 9): ## CHANGE THIS BACK TO 39 TO ACOMMODATE ALL GAMEWEEKS url7 = 'https://fantasy.premierleague.com/api/dream-team/{}/'.format( each) json_weeklydt = requests.get(url7).json() # print(json_weeklydt) dtteamrow = 69 #60+9 dtteamcol = dtteamcol + 2 for each1 in json_weeklydt['team']: dtpl_id = each1['element'] dt_points = each1['points'] pl_name = player_d[dtpl_id] dtlist = {pl_name: dt_points} for values in dtlist.values(): sheet1.cell(row=dtteamrow, column=dtteamcol + 1).value = values for values2 in dtlist.keys(): sheet1.cell(row=dtteamrow, column=dtteamcol).value = values2 dtteamrow = dtteamrow + 1 ## Creating Legend legendlist = [ 'Legend', 'Wildcard', 'Benchboost', 'Triple Captain', 'Free Hit', 'Captain', 'Vice Captain', 'Bench' ] legendrow = 5 for lkey in range(8): sheet1.cell(row=lkey + legendrow, column=1).value = legendlist[lkey] lewc = sheet1.cell(row=legendrow + 1, column=1) lewc.fill = wildcardfill lebb = sheet1.cell(row=legendrow + 2, column=1) lebb.fill = bboostfill letc = sheet1.cell(row=legendrow + 3, column=1) letc.fill = triplecapfill lefh = sheet1.cell(row=legendrow + 4, column=1) lefh.fill = freehitfill leca = sheet1.cell(row=legendrow + 5, column=1) leca.fill = capfill levca = sheet1.cell(row=legendrow + 6, column=1) levca.fill = vcapfill leben = sheet1.cell(row=legendrow + 7, column=1) leben.fill = benchfill ## Creating Team name and FPL ID sheet1['A2'].value = 'FPL ID: {}'.format(self.fpl_prompt.get()) sheet1['A1'].value = 'Team: {}'.format(team_name) sheet1['A3'].value = 'Players: {}'.format(participants) ## Cell Styling headerfont = Font(bold=True) alignment = Alignment(horizontal='center') for key in range(74, 79): # FPL CUP 'GW/Team Name/Points/Team Name/Points' row2 = sheet1.cell(row=2, column=key) row2.font = headerfont row2.alignment = alignment for row in range(1, num_of_cups + 3): # FPL CUP History details for col in range(74, 79): cup1 = sheet1.cell(row=row, column=col) cup1.alignment = alignment cup2 = sheet1.cell(row=1, column=74) # 'FPL CUP History' cup2.font = headerfont cup2.alignment = alignment for key in range(3, 17): # 'GW/GP/GW AVG/GW HS/PB/......' row1 = sheet1.cell(row=1, column=key) row1.font = headerfont row1.alignment = alignment for key in range(3, 97): # GW Teams 'GW1/P1/GW2/P2.....' 79+18, 41+9 row41 = sheet1.cell(row=50, column=key) row41.font = headerfont row41.alignment = alignment for key1 in range(2, 49): # GW history full table 40+9 for key2 in range(3, 10): set1 = sheet1.cell(row=key1, column=key2) set1.alignment = alignment for key1 in range(51, 66): # Team history full table 42+9, 57+9, 79+18 for key2 in range(3, 97): set2 = sheet1.cell(row=key1, column=key2) set2.alignment = alignment for key in range(2, 49): # GW history table value format 40+9 col1 = sheet1.cell(row=key, column=11) # Percentile GW Rank col1.number_format = '0.00000' col1.alignment = alignment col2 = sheet1.cell(row=key, column=14) # Percentile Overall Rank col2.number_format = '0.00000' col2.alignment = alignment col3 = sheet1.cell(row=key, column=16) # Team Value col3.number_format = '0.0' col3.alignment = alignment col4 = sheet1.cell(row=key, column=12) # Overall Points col4.alignment = alignment col5 = sheet1.cell(row=key, column=10) # Gameweek Rank col5.number_format = '#,##0' col5.alignment = alignment col6 = sheet1.cell(row=key, column=13) # Overall Rank col6.number_format = '#,##0' col6.alignment = alignment col4 = sheet1.cell(row=key, column=15) # Position col4.alignment = alignment for key1 in range(1, 3 + num_of_t): # Transfer history table 82+18, 88+18 for key2 in range(100, 106): set3 = sheet1.cell(row=key1, column=key2) set3.alignment = alignment for key in range(100, 106): set4 = sheet1.cell(row=1, column=key) set4.alignment = alignment set4.font = headerfont for key in range(100, 106): # Transfer history 'GW/Transfer in/Value....' row1 = sheet1.cell(row=1, column=key) row1.font = headerfont for key1 in range(2, num_of_t + 1): # Transfer table value In 84+18 col7 = sheet1.cell(row=key1, column=102) col7.number_format = '0.0' for key1 in range(2, num_of_t + 1): # Transfer table value Out 86+18 col7 = sheet1.cell(row=key1, column=104) col7.number_format = '0.0' for col8 in range(70, 73): # League Rank table lrh = sheet1.cell(row=1, column=col8) lrh.font = headerfont for row8 in range(2, num_of_leagues + 3): lr = sheet1.cell(row=row8, column=col8) lr.alignment = alignment lr1 = sheet1.cell(row=1, column=70) # League Rank title lr1.font = headerfont lr1.alignment = alignment for col9 in range( 3, 97): # Gameweek Dream Team full table and title 79+18, 59+9 gwdt = sheet1.cell(row=68, column=col9) gwdt.font = headerfont for row9 in range(68, 80): # 59+9, 71+9 gwdtt = sheet1.cell(row=row9, column=col9) gwdtt.alignment = alignment for col10 in range(61, 63): # Overall dream team table and titles odt = sheet1.cell(row=1, column=col10) odt.font = headerfont odt.alignment = alignment for col10 in range(61, 63): for row10 in range(2, 14): odtt = sheet1.cell(row=row10, column=col10) odtt.alignment = alignment for col10 in range(61, 63): odt = sheet1.cell(row=2, column=col10) odt.font = headerfont for col11 in range(66, 68): # Head2Head table and titles h2ht = sheet1.cell(row=1, column=col11) h2ht.font = headerfont h2ht.alignment = alignment for col11 in range(66, 68): for row11 in range(2, num_of_h2h + 3): h2htt = sheet1.cell(row=row11, column=col11) h2htt.alignment = alignment for col11 in range(66, 68): h2ht = sheet1.cell(row=2, column=col11) h2ht.font = headerfont ## Creating Position symbols for GW history p_o_s using p_o_s_1 first = FormatObject(type='num', val=-1) second = FormatObject(type='num', val=0) third = FormatObject(type='num', val=1) iconset = IconSet(iconSet='3Arrows', cfvo=[first, second, third], showValue=None, percent=None, reverse=None) rule = Rule(type='iconSet', iconSet=iconset) sheet1.conditional_formatting.add('O2:O48', cfRule=rule) #39+9 ## Creating Tables table1 = Table(displayName='GWH', ref='C1:P48') # GW History 39+9 style1 = TableStyleInfo(name="TableStyleMedium11", showRowStripes=True) table1.tableStyleInfo = style1 sheet1.add_table(table1) table2 = Table(displayName='GWT', ref='C50:CR65') # Team History 41+9, BZ to CR 56+ 9 style2 = TableStyleInfo(name="TableStyleLight15", showRowStripes=True) table2.tableStyleInfo = style2 sheet1.add_table(table2) if num_of_t > 0: num_of_trow = num_of_t else: num_of_trow = 1 table3 = Table(displayName='TH', ref='CV2:CZ{}'.format(num_of_trow + 2)) # Transfer History style3 = TableStyleInfo(name="TableStyleMedium12", showRowStripes=True) table3.tableStyleInfo = style3 sheet1.add_table(table3) if num_of_cups > 0: cup_table_row = 2 else: cup_table_row = 3 table4 = Table(displayName='CH', ref='BV2:BZ{}'.format(num_of_cups + cup_table_row)) # FPL Cup history style4 = TableStyleInfo(name="TableStyleMedium13", showRowStripes=True) table4.tableStyleInfo = style4 sheet1.add_table(table4) table5 = Table(displayName='CLR', ref='BR2:BS{}'.format(num_of_leagues + 2)) # Classic League Rank style5 = TableStyleInfo(name="TableStyleMedium10", showRowStripes=True) table5.tableStyleInfo = style5 sheet1.add_table(table5) if num_of_h2h > 0: h2h_table_row = 2 else: h2h_table_row = 3 table6 = Table(displayName='HTOH', ref='BN2:BO{}'.format(num_of_h2h + h2h_table_row)) # H2H Rank style6 = TableStyleInfo(name="TableStyleMedium11", showRowStripes=True) table6.tableStyleInfo = style6 sheet1.add_table(table6) table7 = Table(displayName='ODT', ref='BI2:BJ13') # Overall Dream Team style7 = TableStyleInfo(name="TableStyleMedium7", showRowStripes=True) table7.tableStyleInfo = style7 sheet1.add_table(table7) table8 = Table(displayName='GWDT', ref='C68:CR79') # GW Dream Team 59+9, 70+9 style8 = TableStyleInfo(name="TableStyleLight17", showRowStripes=True) table8.tableStyleInfo = style8 sheet1.add_table(table8) ## Creating Chart chart1 = LineChart() chart1.title = 'Gameweek Points / Average Points / Points Benched / Highest GW Score' data1 = Reference(sheet1, min_col=4, max_col=7, min_row=1, max_row=48) # 39+9 chart1.height = 20 chart1.width = 60 chart1.add_data(data1, titles_from_data=True) sheet1.add_chart(chart1, "T2") ## Save workbook wb.save("FPL.Data.19-20.{}.xlsx".format(self.fpl_prompt.get())) ## Clear text box and show success dialog item_path = str( path.realpath("FPL.Data.19-20.{}.xlxs".format( self.fpl_prompt.get()))) messagebox.showinfo(title="Success", message="Data for \'{}\' imported successfully.\n" "File saved in {}".format(team_name, item_path)) self.clear()