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ブック２シートに出力
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'],