wb = Workbook() ws = wb.active redFill = PatternFill(start_color='EE1111', end_color='EE1111',fill_type='solid') ws.conditional_formatting.add('A1:A10',ColorScaleRule(start_type='min', start_color='AA0000', end_type='max', end_color='00AA00')) 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')) ws.conditional_formatting.add('C2:C10',CellIsRule(operator='lessThan', formula=['C$1'], stopIfTrue=True, fill=redFill)) ws.conditional_formatting.add('D2:D10',CellIsRule(operator='between', formula=['1','5'], stopIfTrue=True, fill=redFill)) ws.conditional_formatting.add('E1:E10',FormulaRule(formula=['ISBLANK(E1)'], stopIfTrue=True, fill=redFill)) myFont = Font() myBorder = Border() ws.conditional_formatting.add('E1:E10',FormulaRule(formula=['E1=0'], font=myFont, border=myBorder, fill=redFill)) red_text = Font(color="9C0006") red_fill = PatternFill(bgColor="FFC7CE")
def conditional_formatting(ws, language_dict): def get_entire_column(index): return index + '1:' + index + '1048576' red_color = 'ffc7ce' green_color = 'c2efcf' yellow_color = 'ffeba2' red_fill = PatternFill(start_color=red_color, end_color=red_color, fill_type='solid') green_fill = PatternFill(start_color=green_color, end_color=green_color, fill_type='solid') yellow_fill = PatternFill(start_color=yellow_color, end_color=yellow_color, fill_type='solid') ws.conditional_formatting.add( get_entire_column(column_index[language_dict['difficulty']]), CellIsRule(operator='equal', formula=['"' + language_dict['level'][1] + '"'], stopIfTrue=False, fill=green_fill)) ws.conditional_formatting.add( get_entire_column(column_index[language_dict['difficulty']]), CellIsRule(operator='equal', formula=['"' + language_dict['level'][2] + '"'], stopIfTrue=False, fill=yellow_fill)) ws.conditional_formatting.add( get_entire_column(column_index[language_dict['difficulty']]), CellIsRule(operator='equal', formula=['"' + language_dict['level'][3] + '"'], stopIfTrue=False, fill=red_fill)) ws.conditional_formatting.add( get_entire_column(column_index[language_dict['paid_only']]), CellIsRule(operator='equal', formula=['"' + language_dict['bool'][False] + '"'], stopIfTrue=False, fill=green_fill)) ws.conditional_formatting.add( get_entire_column(column_index[language_dict['paid_only']]), CellIsRule(operator='equal', formula=['"' + language_dict['bool'][True] + '"'], stopIfTrue=False, fill=red_fill)) ws.conditional_formatting.add( get_entire_column(column_index[language_dict['status']]), CellIsRule(operator='equal', formula=['"' + language_dict['bool'][False] + '"'], stopIfTrue=False, fill=red_fill)) ws.conditional_formatting.add( get_entire_column(column_index[language_dict['status']]), CellIsRule(operator='equal', formula=['"' + language_dict['bool'][True] + '"'], stopIfTrue=False, fill=green_fill)) ws.conditional_formatting.add( get_entire_column(column_index[language_dict['acceptance']]), DataBarRule(start_type='percentile', start_value=0, end_type='percentile', end_value=100, color="FF638EC6", showValue='None'))
def write_score_sheet(ws, db, chart_set, config, scores): latest_filtered_mix = get_latest_filtered_mix(db, config.mix_ids) fver = db.newest_version_from_mix(latest_filtered_mix) charts = list(chart_set) charts.sort(key=lambda cid: db.chart_sort_key(cid, fver, down=config.down)) mixes = config.mix_ids if len(mixes) == 1: mixes = [] headers = [ "CID", # A "Title", # B "Cut", # C "Mode", # D "Difficulty", # E ] if config.pad: col_pad = len(headers) + 1 headers += [ "Passed (Pad)", # F "Grade (Pad)", # G "Miss (Pad)", # H "Comment (Pad)", # I ] if config.keyboard: col_kbd = len(headers) + 1 headers += [ "Passed (Kbd)", # F J "Grade (Kbd)", # G K "Miss (Kbd)", # H L "Comment (Kbd)" # I M ] col_mix = len(headers) + 1 headers += [db.mixes[m].title for m in mixes] # F J N col_hist = len(headers) + 1 headers += ["History"] bold = Font(bold=True) gray = PatternFill("solid", fgColor="EEEEEE") dgray = PatternFill("solid", fgColor="CCCCCC") border_cols = [5] if config.pad or config.keyboard: border_cols += [9] if config.pad and config.keyboard: border_cols += [13] if scores: scores_left = set(scores) for i in range(len(headers)): right = None c = ws.cell(row=1, column=i+1, value=headers[i]) c.font = bold c.fill = dgray c.border = Border(bottom=Side(style="thick"), right=right) for i, cid in enumerate(charts): ws.cell(row=i+2, column=1, value=cid).fill = dgray ws.cell(row=i+2, column=2, value="=VLOOKUP(A%d, 'Data (Complete)'!A1:O9999, 4, FALSE)" % (i+2)).fill = gray ws.cell(row=i+2, column=3, value="=VLOOKUP(A%d, 'Data (Complete)'!A1:O9999, 5, FALSE)" % (i+2)).fill = gray ws.cell(row=i+2, column=4, value="=VLOOKUP(A%d, 'Data (Complete)'!A1:O9999, 6, FALSE)" % (i+2)).fill = gray ws.cell(row=i+2, column=5, value="=VLOOKUP(A%d, 'Data (Complete)'!A1:O9999, 7, FALSE)" % (i+2)).fill = gray for j, mid in enumerate(mixes): ws.cell(row=i+2, column=col_mix+j, value="NY"[db.chart_in_mix(cid, mid)]).fill = gray ws.cell(row=i+2, column=col_mix+len(mixes), value=db.chart_rating_sequence_str(cid, changes_only=True)).fill = gray if scores: if config.pad: key = (cid, True) if key in scores: s = scores[key] ws.cell(row=i+2, column=col_pad+0, value=s.passed) ws.cell(row=i+2, column=col_pad+1, value=s.grade) ws.cell(row=i+2, column=col_pad+2, value=s.miss) ws.cell(row=i+2, column=col_pad+3, value=s.comment) scores_left.remove(key) if config.keyboard: key = (cid, False) if key in scores: s = scores[key] ws.cell(row=i+2, column=col_kbd+0, value=s.passed) ws.cell(row=i+2, column=col_kbd+1, value=s.grade) ws.cell(row=i+2, column=col_kbd+2, value=s.miss) ws.cell(row=i+2, column=col_kbd+3, value=s.comment) scores_left.remove(key) for c in range(len(headers)): for r in range(len(charts)): right = None if c+1 == len(headers): right = Side(style="thin") if c+1 in border_cols: right = Side(style="thick") border = Border(bottom=Side(style="thin", color="777777"), right=right) ws.cell(row=r+2, column=c+1).border = border ws.column_dimensions['A'].width = 5 ws.column_dimensions['B'].width = 30 ws.column_dimensions['C'].width = 9 ws.column_dimensions['D'].width = 9 ws.column_dimensions['E'].width = 3 c = 6 if config.pad: ws.column_dimensions[gcl(c+0)].width = 4 ws.column_dimensions[gcl(c+1)].width = 4 ws.column_dimensions[gcl(c+2)].width = 4 ws.column_dimensions[gcl(c+3)].width = 20 c += 4 if config.keyboard: ws.column_dimensions[gcl(c+0)].width = 4 ws.column_dimensions[gcl(c+1)].width = 4 ws.column_dimensions[gcl(c+2)].width = 4 ws.column_dimensions[gcl(c+3)].width = 20 c += 4 for i in range(len(mixes)): ws.column_dimensions[gcl(c+i)].width = 2 c += len(mixes) ws.column_dimensions[gcl(c)].width = 10 #24 green = PatternFill("solid", bgColor="44FF44") red = PatternFill("solid", bgColor="FF4444") grade_f = PatternFill("solid", bgColor="555555") grade_d = PatternFill("solid", bgColor="666666") grade_c = PatternFill("solid", bgColor="777777") grade_b = PatternFill("solid", bgColor="888888") grade_a = PatternFill("solid", bgColor="AAAAAA") grade_s = PatternFill("solid", bgColor="DD88FF") grade_ss = PatternFill("solid", bgColor="FFEE00") grade_sss = PatternFill("solid", bgColor="44FF44") if config.pad or config.keyboard: ws.conditional_formatting.add('F2:F9999', CellIsRule(operator='equal', formula=['"Y"'], fill=green)) ws.conditional_formatting.add('F2:F9999', CellIsRule(operator='equal', formula=['"N"'], fill=red)) if config.pad and config.keyboard: ws.conditional_formatting.add('J2:J9999', CellIsRule(operator='equal', formula=['"Y"'], fill=green)) ws.conditional_formatting.add('J2:J9999', CellIsRule(operator='equal', formula=['"N"'], fill=red)) if config.pad or config.keyboard: ws.conditional_formatting.add('G2:G9999', CellIsRule(operator='equal', formula=['"SSS"'], fill=grade_sss)) ws.conditional_formatting.add('G2:G9999', CellIsRule(operator='equal', formula=['"SS"'], fill=grade_ss)) ws.conditional_formatting.add('G2:G9999', CellIsRule(operator='equal', formula=['"S"'], fill=grade_s)) ws.conditional_formatting.add('G2:G9999', CellIsRule(operator='equal', formula=['"A"'], fill=grade_a)) ws.conditional_formatting.add('G2:G9999', CellIsRule(operator='equal', formula=['"B"'], fill=grade_b)) ws.conditional_formatting.add('G2:G9999', CellIsRule(operator='equal', formula=['"C"'], fill=grade_c)) ws.conditional_formatting.add('G2:G9999', CellIsRule(operator='equal', formula=['"D"'], fill=grade_d)) ws.conditional_formatting.add('G2:G9999', CellIsRule(operator='equal', formula=['"F"'], fill=grade_f)) if config.pad and config.keyboard: ws.conditional_formatting.add('K2:K9999', CellIsRule(operator='equal', formula=['"SSS"'], fill=grade_sss)) ws.conditional_formatting.add('K2:K9999', CellIsRule(operator='equal', formula=['"SS"'], fill=grade_ss)) ws.conditional_formatting.add('K2:K9999', CellIsRule(operator='equal', formula=['"S"'], fill=grade_s)) ws.conditional_formatting.add('K2:K9999', CellIsRule(operator='equal', formula=['"A"'], fill=grade_a)) ws.conditional_formatting.add('K2:K9999', CellIsRule(operator='equal', formula=['"B"'], fill=grade_b)) ws.conditional_formatting.add('K2:K9999', CellIsRule(operator='equal', formula=['"C"'], fill=grade_c)) ws.conditional_formatting.add('K2:K9999', CellIsRule(operator='equal', formula=['"D"'], fill=grade_d)) ws.conditional_formatting.add('K2:K9999', CellIsRule(operator='equal', formula=['"F"'], fill=grade_f)) ws.freeze_panes = 'C2' if scores: lver = db.latest_version() for s in scores_left: title = None rstr = None sid = db.chart_song(scores[s].cid) if sid: title = db.song_title(sid, lver) if title == None: title = "[Unknown Title]" rating = db.chart_rating(scores[s].cid, lver) if rating: rstr = db.rating_str(rating) if rstr == None or rstr == "???": rstr = "[Unknown Rating]" etype = "*keyboard*" if s[1]: etype = "*pad*" print("WARNING: New sheet does not contain a %s entry for CID=%d: %s %s" % (etype, scores[s].cid, title, rstr))
def MainPerformance(): try: print('************ BEGIN ************') print() print('Step 1: Processing Template and Raw Data') try: try: config = configparser.ConfigParser() config.read('Settings.ini') rawdata_path=config.get('source', 'rawdata') if rawdata_path=="": rawdata_path=None except: rawdata_path=None #1.import template wb_file_name = 'Report\AG_Performance_Template.xlsx' wb_file = Library.getXlsxFile(wb_file_name, []) print(' -Loading file: %s ' % wb_file, end="") #wb = load_workbook(filename = wb_file_name, data_only=True) wb = load_workbook(filename = wb_file) for checksheet in wb.sheetnames: if checksheet!="Performance": wb.remove(wb[checksheet]) wb_sheet = wb["Performance"] ReportDate = wb_sheet.cell(column=1, row=1).value ReportDateLast = ReportDate - datetime.timedelta(days=1) ReportDateSimpleArr = str(ReportDate).split(' ',1)[0].split('-',2) ReportDateSimple = ReportDateSimpleArr[1]+ReportDateSimpleArr[2] ReportDateStr = ReportDateSimpleArr[0]+"/"+ReportDateSimpleArr[1]+"/"+ReportDateSimpleArr[2] ReportDateLastSimpleArr = str(ReportDateLast).split(' ',1)[0].split('-',2) ReportDateLastSimple = ReportDateLastSimpleArr[1]+ReportDateLastSimpleArr[2] #wb_sheet = wb[wb.sheetnames[0]] print(' => Completed') #2.import data source print(' -Loading file:', end="") table_summary_file=None if rawdata_path!=None: try: table_summary_file_name = rawdata_path + '\**\*客服人員群組總結*.xls' table_summary_file = Library.getCsvFile(table_summary_file_name, [[1,1, ReportDateStr]]) except Exception as e: table_summary_file=None if table_summary_file==None: table_summary_file_name = 'Report\RAWDATA\**\*客服人員群組總結*.xls' table_summary_file = Library.getCsvFile(table_summary_file_name, [[1,1, ReportDateStr]]) if table_summary_file!=None: table_summary = csv.reader(open(table_summary_file, 'r'), delimiter='\t') #table_summary = getCsvFile('前日匯報-客服人員群組總結*.xls', ReportDateStr) table_summary_list = list(table_summary) if table_summary!=None else None table_summary_rows = sum(1 for row in table_summary) if table_summary_list!=None else 0 print(' %s => Completed' % table_summary_file) else: print(' Finding "%s\" => Failed' % table_summary_file_name) #2.import data source print(' -Loading file:', end="") #table_loginout = csv.reader(open('Report\RAWDATA\前日匯報-客服人員登出登入.xls', 'r'), delimiter='\t') table_loginout_file=None if rawdata_path!=None: try: table_loginout_file_name = rawdata_path + '\**\*客服人員登出登入*.xls' table_loginout_file = Library.getCsvFile(table_loginout_file_name, [[1,1, ReportDateStr]]) except Exception as e: table_loginout_file=None if table_loginout_file==None: table_loginout_file_name = 'Report\RAWDATA\**\*客服人員登出登入*.xls' table_loginout_file = Library.getCsvFile(table_loginout_file_name, [[1,1, ReportDateStr]]) if table_loginout_file!=None: table_loginout = csv.reader(open(table_loginout_file, 'r'), delimiter='\t') table_loginout_list = list(table_loginout) if table_loginout!=None else None table_loginout_rows = sum(1 for row in table_loginout) if table_loginout_list!=None else 0 print(' %s => Completed' % table_loginout_file) else: #print(' %s => Failed, cannot found date including report date' % table_loginout_file_name) print(' Finding "%s\" => Failed' % table_loginout_file_name) #2.import data source print(' -Loading file:', end="") table_mail_file_name = 'Report\RAWDATA\**\*MAIL*.xlsx' table_mail_file = Library.getXlsxFile(table_mail_file_name, [['Str', 2, 'Closed'], ['DateStr', 4, ReportDateStr]]) #table_mail = load_workbook(filename = 'Report\RAWDATA\MAIL.xlsx') if table_mail_file!=None: table_mail = load_workbook(filename = table_mail_file) table_mail_sheet = table_mail[table_mail.sheetnames[0]] print(' %s => Completed' % table_mail_file) else: print(' Finding "%s\" => Failed' % table_mail_file_name) #2.import data source print(' -Loading file:', end="") table_cts_file_name = '*Report\RAWDATA\**\*CTS*.xlsx' table_cts_file = Library.getXlsxFile(table_cts_file_name, [['DateStr', 3, ReportDateStr]]) #table_cts_file = Library.getCtsFileName(table_cts_file_name, ReportDateStr) if table_cts_file!=None: table_cts = load_workbook(filename = table_cts_file) table_cts_sheet = table_cts[table_cts.sheetnames[0]] print(' %s => Completed' % table_cts_file) else: print(' Finding "%s\" => Failed' % table_cts_file_name) #3.Processing Report print() print('Step 2: Processing report calculation') TotalACD = 0 TotalACW = 0 TotalLogin = 0 if wb_sheet.max_row>1: wb_sheet.cell(column=4, row=3).value = 0 for rows in range(1, wb_sheet.max_row+1): LoginID = wb_sheet.cell(column=4, row=rows).value LoginName = wb_sheet.cell(column=2, row=rows).value if table_summary_file!=None: mySummaryRow = Library.getRow(table_summary_list, LoginID) #Process Summary Table if mySummaryRow!=None: print(' -Loading LoginID = %s' % LoginID, end="") #print(mySummaryRow) #Start ACD Process... TotalACD += int(mySummaryRow[10]) wb_sheet.cell(column=13, row=rows).value = datetime.timedelta(seconds=int(mySummaryRow[10])) #ACD wb_sheet.cell(column=13, row=3).value = datetime.timedelta(seconds=TotalACD) #Total ACD #End ACD Process... #Start ACW Process... TotalACW += int(mySummaryRow[11]) wb_sheet.cell(column=14, row=rows).value = datetime.timedelta(seconds=int(mySummaryRow[11])) #ACW wb_sheet.cell(column=14, row=3).value = datetime.timedelta(seconds=TotalACW) #Total ACW #End ACW Process... #Start TotalLogin Process... TotalLogin += int(mySummaryRow[16]) wb_sheet.cell(column=12, row=rows).value = datetime.timedelta(seconds=int(mySummaryRow[16])) #TotalLogin wb_sheet.cell(column=12, row=3).value = datetime.timedelta(seconds=TotalLogin) #Total TotalLogin #End TotalLogin Process... #Start ACH Process... wb_sheet.cell(column=28, row=rows).value = datetime.timedelta(seconds=int(float(mySummaryRow[2])+float(mySummaryRow[3]))) #ACH #End TotalLogin Process... #Process Loginout Table if table_loginout_file!=None: myLoginoutRow = Library.getRow(table_loginout_list, LoginID) if myLoginoutRow!=None: #print(myLoginoutRow) LoginSec = Library.getSec(myLoginoutRow[3]) LogoutSec = Library.getSec(myLoginoutRow[5]) wb_sheet.cell(column=10, row=rows).value = str(datetime.timedelta(seconds=LoginSec))[-5:] #Login wb_sheet.cell(column=11, row=rows).value = str(datetime.timedelta(seconds=LogoutSec))[-5:] #Logout try: wb_sheet.cell(column=5, row=rows).value = int((LogoutSec-LoginSec)/60) except: wb_sheet.cell(column=5, row=rows).value = "" wb_sheet.cell(column=20, row=rows).value = wb_sheet.cell(column=5, row=rows).value #wb_sheet.cell(column=10, row=rows).value = datetime.timedelta(seconds=)) #Login #wb_sheet.cell(column=11, row=rows).value = datetime.timedelta(seconds=)) #Logout else: myRole = wb_sheet.cell(column=1, row=rows).value if myRole=='AG' or myRole=='SA': wb_sheet.cell(column=5, row=rows).value = "休" wb_sheet.cell(column=20, row=rows).value = "休" #Process Mail Table TotalPaperCounter = 0 if rows>3 and table_mail_file!=None: myMailCounter = Library.getMailCount(table_mail_sheet, LoginName, ReportDateStr) if myMailCounter!=None and myMailCounter>0: wb_sheet.cell(column=17, row=rows).value = myMailCounter TotalPaperCounter += myMailCounter #Process Cts Table if rows>3 and table_cts_file!=None: myFacebookCounter = Library.getCtsCount(table_cts_sheet, LoginName, ReportDateStr, 'Facebook') if myFacebookCounter!=None and myFacebookCounter>0: wb_sheet.cell(column=18, row=rows).value = myFacebookCounter TotalPaperCounter += myFacebookCounter myTelCounter = Library.getCtsCount(table_cts_sheet, LoginName, ReportDateStr, '電話') if myTelCounter!=None and myTelCounter>0: wb_sheet.cell(column=15, row=rows).value = myTelCounter TotalPaperCounter += myTelCounter myOutboundCounter = Library.getCtsCount(table_cts_sheet, LoginName, ReportDateStr, 'Outbound') if myOutboundCounter!=None and myOutboundCounter>0: wb_sheet.cell(column=16, row=rows).value = myOutboundCounter TotalPaperCounter += myOutboundCounter if wb_sheet.cell(column=5, row=rows).value == "休" and TotalPaperCounter>0: wb_sheet.cell(column=5, row=rows).value = "" wb_sheet.cell(column=20, row=rows).value = "" if str(wb_sheet.cell(column=5, row=rows).value).isdigit(): wb_sheet.cell(column=4, row=3).value += 1 if table_summary_file!=None and mySummaryRow!=None: print(' => Completed') #改數值格式後不用計算 #if TotalLogin>0 and TotalACD>0: # wb_sheet.cell(column=21, row=3).value = TotalACD/TotalLogin # Total TotalACD/TotalLogin #if TotalLogin>0 and TotalACW>0: # wb_sheet.cell(column=23, row=3).value = (TotalACD+TotalACW)/TotalLogin # Total TotalACD/TotalLogin print() print('Step 3: Generating Report') #Generate wb_sheet.title = ReportDateSimple Performance_FilePathName = "Report\OPPO_Agent_Performance%s.xlsx" % ReportDateSimple Performance_FilePathNameLast = "Report\OPPO_Agent_Performance%s.xlsx" % ReportDateLastSimple print(' -Creating Report to the %s' % Performance_FilePathName, end="") gray_font = styles.Font(color='00A0A0A0') if not os.path.isfile(Performance_FilePathName) and os.path.isfile(Performance_FilePathNameLast) and ReportDateSimple[-2:]!='01': copyfile(Performance_FilePathNameLast, Performance_FilePathName) if os.path.isfile(Performance_FilePathName): wb_Copy = load_workbook(filename = Performance_FilePathName) if ReportDateSimple in wb_Copy.sheetnames: wb_Copy_Sheet = wb_Copy[ReportDateSimple] else: wb_Copy_Sheet = wb_Copy.copy_worksheet(wb_Copy[wb_Copy.sheetnames[0]]) wb_Copy_Sheet.title = ReportDateSimple wb_Copy_Sheet = Library.copyWorksheet(wb_sheet, wb_Copy_Sheet) wb_Copy_Sheet.conditional_formatting.add('A1:AB100', CellIsRule(operator='equal', formula=['0'], stopIfTrue=True, font=gray_font)) wb_Copy.active = len(wb_Copy.sheetnames)-1 wb_Copy.save(Performance_FilePathName) wb_Copy.close() else: wb_sheet.conditional_formatting.add('A1:AB100', CellIsRule(operator='equal', formula=['0'], stopIfTrue=True, font=gray_font)) wb.save(Performance_FilePathName) wb.close() print(' => Completed') #print(str(datetime.timedelta(seconds=134))) #print("12305"[-2:]) #print('0'.isdigit()) #test='Yvonne Peng彭伊楺' #print(re.sub(r'[\x00-\x7f]',r' ',test).strip()) #print(ReportDateLastSimple) #print(ReportDateStr) #except Exception as e: # print(e) except: print(' -Loading file: => Failed ') #except Exception as e: # print(e) except: print("Error!! Close all excel files and try again.") finally: print() print('************ END ************')
TX = "=" + tn + "!" + "F25" TY = "=" + tn + "!" + "Y16" SN.cell(row=2 + D, column=2).value = TT #Average Rocket Hatch SN.cell(row=2 + D, column=3).value = TU #Average Rocket Cargo SN.cell(row=2 + D, column=4).value = TV #Average Cargo Ship Hatch SN.cell(row=2 + D, column=5).value = TW #Average Cargo Ship Cargo SN.cell(row=2 + D, column=6).value = TX #Average Climb level SN.cell(row=2 + D, column=7).value = TY #Average Point Contribution #SN.cell(row = 2+D , column = 3).number_format = '#,##0.00' D += 1 SN.conditional_formatting.add( 'B2:F75', CellIsRule(operator='greaterThan', formula=[2.96], stopIfTrue=True, fill=greenFill)) SN.conditional_formatting.add( 'B2:F75', CellIsRule(operator='between', formula=[1.96, 2.95], stopIfTrue=True, fill=yellowFill)) SN.conditional_formatting.add( 'B2:F75', CellIsRule(operator='between', formula=[.0001, 1.95], stopIfTrue=True, fill=redFill)) Trnmt.save('./Spreadsheets/Tournament.xlsx')
def format_xls(spreadsheet): """ Take spreadsheet output from FMUT functions and apply formatting """ wb = openpyxl.load_workbook(spreadsheet) #Delete blank sheets for sheet_name in wb.sheetnames: if re.match(r'Sheet\d', sheet_name): sheet2remove = wb[sheet_name] wb.remove_sheet(sheet2remove) wb.active = 0 #Define some fill styles for use below whiteFill = PatternFill(start_color='ffffff', end_color='ffffff', fill_type='solid') yellowHighlight = PatternFill(start_color='f7ff23', end_color='f7ff23', fill_type='solid') for sheet_name in wb.sheetnames: sheet = wb[sheet_name] #Test summary sheet if sheet_name == 'test summary': sheet.column_dimensions['A'].width = 40 sheet.column_dimensions['B'].width = 200 for cell in sheet['B']: cell.alignment = Alignment(horizontal='left') #Format critical values for row in range(1, sheet.max_row + 1): if 'critical value' in sheet['A' + str(row)].value: sheet['B' + str(row)].number_format = '0.00' #cluster_summary sheet if sheet_name == 'cluster summary': #Set column dimensions for i in range(1, sheet.max_column + 1): if i % 3 == 1: sheet.column_dimensions[get_column_letter(i)].width = 19 elif i % 3 == 2: sheet.column_dimensions[get_column_letter(i)].width = 10 elif i % 3 == 0: sheet.column_dimensions[get_column_letter(i)].width = 5 #Freeze header for easier viewing sheet.freeze_panes = sheet['A2'] #Set header style for cell in sheet[1]: cell.font = Font(sz=13, bold=True) #Format decimal places of stats for row in range(2, sheet.max_row + 1): if any(cell.value == 'cluster mass' for cell in sheet[str(row)]): for cell in sheet[row]: cell.number_format = '0.00' elif any(cell.value == 'p-value' for cell in sheet[str(row)]): for cell in sheet[row]: cell.number_format = '0.000' #Left align everything for easier viewing for col in range(2, sheet.max_column + 1): if col % 3 == 2: for row in range(3, sheet.max_row + 1): sheet.cell(row=row, column=col).alignment = Alignment( horizontal='left') #Bold and highlight significant clusters for row in range(1, sheet.max_row + 1): for cell in sheet[row]: if cell.value == 'p-value' and cell.offset( row=0, column=1).value <= .05: cell.offset(row=0, column=1).fill = yellowHighlight for r in range(cell.row - 2, cell.row + 7): for c in range( column_index_from_string(cell.column), column_index_from_string(cell.column) + 2): sheet.cell(row=r, column=c).font = Font(bold=True) #Some general stuff for remaining sheets if sheet_name.endswith('clust_IDs') or sheet_name.endswith( 'pvals') or sheet_name.endswith('_obs'): #Make headers bold for cell in sheet[1]: cell.font = Font(bold=True) cell.number_format = '0' for cell in sheet['A']: cell.font = Font(bold=True) #Freeze headers for easier viewing sheet.freeze_panes = sheet['B2'] #Format cluster ID sheets if sheet_name.endswith('clust_IDs'): #Reduce column width for i in range(1, sheet.max_column + 1): sheet.column_dimensions[get_column_letter(i)].width = 4 #Apply a different color to each cluster max_cell = get_column_letter(sheet.max_column) + str(sheet.max_row) clust_colors = ('36ec41', '003fbb', 'b4b500', 'c777ff', '00f7b8', '2f0067', '537e00', 'ff6dbf', '8befff', 'ff5227', '0171aa', 'a92900', '00727b', 'b60070', 'faffd1', '540042', 'a96500', 'e2caff', 'ffa177', '6a001f') for c in range(100): color_idx = c % 20 clust = c + 1 clustFill = PatternFill(start_color=clust_colors[color_idx], end_color=clust_colors[color_idx], fill_type='solid') sheet.conditional_formatting.add( 'B2:' + max_cell, CellIsRule(operator='equal', formula=[clust], stopIfTrue=True, fill=clustFill)) #Reduce font size and clear locations not included in cluster for row in range(2, sheet.max_row + 1): for cell in sheet[row]: if column_index_from_string(cell.column) > 1: cell.font = Font(sz=10) if not cell.value: cell.value = None #Format t-obs F-obs sheets elif sheet_name.endswith('_obs'): #Reduce column width for i in range(1, sheet.max_column + 1): sheet.column_dimensions[get_column_letter(i)].width = 6 #Format numbers and apply conditional formatting max_cell = get_column_letter(sheet.max_column) + str(sheet.max_row) data_range = 'B2:' + max_cell for row in range(2, sheet.max_row + 1): for cell in sheet[row]: if cell.column != 'A': cell.number_format = '0.00' cell.font = Font(sz=10) if sheet_name.endswith('F_obs'): sheet.conditional_formatting.add( data_range, ColorScaleRule(start_type='num', start_value=1, start_color='ffffff', end_type='max', end_color='448452')) elif sheet_name.endswith('t_obs'): sheet.conditional_formatting.add( data_range, ColorScaleRule(start_type='min', start_color='0047ba', mid_type='num', mid_value=0, mid_color='ffffff', end_type='max', end_color='c63535')) #Format p-value sheets elif sheet_name.endswith('_pvals'): #Reduce column width for i in range(2, sheet.max_column + 1): sheet.column_dimensions[get_column_letter(i)].width = 6 #Format numbers and apply conditional formatting max_cell = get_column_letter(sheet.max_column) + str(sheet.max_row) data_range = 'B2:' + max_cell for row in range(2, sheet.max_row + 1): for cell in sheet[row]: if cell.column != 'A': cell.number_format = '0.000' cell.font = Font(sz=10) sheet.conditional_formatting.add( data_range, CellIsRule(operator='greaterThan', formula=[0.05], stopIfTrue=True, fill=whiteFill)) sheet.conditional_formatting.add( data_range, ColorScaleRule(start_type='num', start_value=0, start_color='4e875b', end_type='num', end_value=0.05, end_color='c9ffd5')) #Save wb.save(spreadsheet)
outname = input('outputName?') #各データを1ブック2シートに出力 with pd.ExcelWriter(outname) as writer: tf.to_excel(writer, sheet_name='TrueFalse') td.to_excel(writer, sheet_name='TestData') result.to_excel(writer, sheet_name='EditorData') #FALSEを強調表示する条件付き書式設定 wb = px.load_workbook(outname) ws = wb['TrueFalse'] ws.conditional_formatting.add( 'A1:AZ100', CellIsRule(operator='equal', formula=['FALSE'], fill=PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid'))) white = px.styles.PatternFill(patternType='solid', fgColor='000000', bgColor='000000') ws['A1'].fill = white ws.conditional_formatting.add( 'A1:AZ100', CellIsRule(operator='equal', formula=[''], fill=PatternFill(start_color='000000', end_color='000000', fill_type='solid'))) wb.save(outname)
continue else: gainDict[sheet.title] = [sheet[iRange].value, direction, sheet[bRange].value] sheet[jRange].value = slTarget - float(close) if sheet[jRange].value < 0: result = 'Stop Loss' if sheet.title in lossDict: continue else: lossDict[sheet.title] = [sheet[jRange].value, direction, sheet[bRange].value] iMax, jMax = 'I' + str(maxRow), 'J' + str(maxRow) sheet.conditional_formatting.add('I2:' + str(iMax), CellIsRule(operator='greaterThan', formula=[('0')], stopIfTrue=True, fill=darkGreenFill)) sheet.conditional_formatting.add('I2:' + str(iMax), CellIsRule(operator='between', formula=[('(0)'), ('(H$3 - H$6) * .5')], stopIfTrue=True, fill=greenFill)) sheet.conditional_formatting.add('I2:' + str(iMax), CellIsRule(operator='between', formula=[('(H$3 - H$6) * .5'), ('H$3 - H$6')], stopIfTrue=True, fill=ltGreenFill)) sheet.conditional_formatting.add('J2:' + str(jMax), CellIsRule(operator='lessThan',
ws2.cell(row=1 + S, column=1).value = int(mn) ws2.cell(row=1 + S, column=2).value = tm ws2.cell(row=1 + S, column=3).value = int(R1) ws2.cell(row=1 + S, column=4).value = int(R2) ws2.cell(row=1 + S, column=5).value = int(R3) ws2.cell(row=1 + S, column=6).value = int(B1) ws2.cell(row=1 + S, column=7).value = int(B2) ws2.cell(row=1 + S, column=8).value = int(B3) ws2.cell( row=1 + S, column=2).number_format = 'HH:MM' #Restrict to display time only S += 1 SN = Tnmt.get_sheet_by_name(str(TEAM) + ' Schedule') SN.conditional_formatting.add( 'C2:H14', CellIsRule(operator='equal', formula=[TEAM], stopIfTrue=True, fill=greenFill)) SM = Tnmt.get_sheet_by_name('Match Schedule') SM.conditional_formatting.add( 'C2:H130', CellIsRule(operator='equal', formula=[TEAM], stopIfTrue=True, fill=greenFill)) Tnmt.save('/EagleScout/Python Scripts/Match_Schedule/Match_Schdule.xlsx')
ws.cell(row=1, column=12).value = 'INDICE' ws.cell(row=1, column=12).font = Font(color=colors.BLUE) ws.cell(row=1, column=12).hyperlink = (link) elif f[:-4] == 'top_Hua_VoLTE': link = '#INDICE!A1' ws.cell(row=1, column=22).value = 'INDICE' ws.cell(row=1, column=22).font = Font(color=colors.BLUE) ws.cell(row=1, column=22).hyperlink = (link) ws.sheet_view.zoomScale = 75 ws = wb['eri3gdia_top_rssi'] ws.conditional_formatting.add( 'E1:E300', CellIsRule(operator='greaterThan', formula=['-90'], stopIfTrue=True, fill=redFill)) ws = wb['eri4gdia_top_interf'] ws.conditional_formatting.add( 'E1:E300', CellIsRule(operator='greaterThan', formula=['-100'], stopIfTrue=True, fill=redFill)) ws = wb['hua4gdia_top_interf'] ws.conditional_formatting.add( 'E1:E300', CellIsRule(operator='greaterThan', formula=['-100'],