def _read_data(self):
""" Read the actual data to an np array
"""
for _, v in self.sets.items():
# pre-alloc data
data = []
for i in range(v.n_wells):
data.append(np.zeros((v.inner_x, v.inner_y)))
n_rows = v.inner_x * v.inner_y
for iRow in range(v.row, v.row+n_rows):
# np starts at 0, PlateReader at 1
inner_x = self.ws[gcl(v.col)+str(iRow)].value -1
inner_y = self.ws[gcl(v.col+1)+str(iRow)].value -1
idx_outer = 0
for iCol in range(v.col+2, self.ws.max_column+1):
cell = self.ws[gcl(iCol)+str(iRow)]
try:
val = int(cell.value)
except (TypeError, ValueError):
val = 0
# we have to swap x and y for numpy !!!
data[idx_outer][(inner_y, inner_x)] = val
idx_outer += 1
v.data = data
def adjust_column_widths(ws, cols, rows): for c in cols: width = 0 for r in rows: val = ws.cell(row=r, column=c).value if val == None: continue width = max(width, len(str(val))) ws.column_dimensions[gcl(c)].width = width + 1
def _get_sets(self):
""" Search the worksheet for data set and store locations
"""
# parse b column for start of read
sets = []
colB = self.ws['B'] # we know its in "B"
# find all data starts
for cell in colB:
if cell and "X Read #" in str(cell.value):
sets.append([cell.row, cell.column])
for _set in sets:
cell = self.ws[gcl(_set[1]-1)+str(_set[0]-2)]
data = DataSet()
data.row = _set[0]+1
data.col = _set[1]
data.col_ = gcl(_set[1])
self.sets[cell.value] = data
def format_all_names():
if printing:
print("Opening...")
# Uses sys.argv to pass in arguments
args = sys.argv[1:]
filename = args[0]
cols = args[1:]
wb = openpyxl.load_workbook(filename)
sheet = wb.worksheets[0]
first = 2
last = sheet.max_row + 1
for col in cols:
for row in range(first, last):
name = str(sheet[col + str(row)].value).title()
formatted = formatting_name(name, row)
if saving:
sheet[gcl(number_from_column(col) + 1) +
str(row)].value = formatted['appellation']
sheet[gcl(number_from_column(col) + 2) +
str(row)].value = formatted['first_name']
sheet[gcl(number_from_column(col) + 3) +
str(row)].value = formatted['middle_name']
sheet[gcl(number_from_column(col) + 4) +
str(row)].value = formatted['last_name']
if printing:
print("Processed " + str(last - first) + " rows...")
if printing and saving:
print("Saving...")
wb.save("splitNames.xlsx")
pygame.init()
pygame.mixer.music.load('/home/andrefisch/python/evan/note.mp3')
pygame.mixer.music.play()
time.sleep(5)
pygame.mixer.music.stop()
def _get_inner_dimensions(self):
""" Get the dimensions of the innter matrix
"""
for _, v in self.sets.items():
n_cols = []
n_rows = []
idx = v.row
idy = v.col
while idx < self.ws.max_row:
idy_rows = gcl(idy)
val_x = self.ws[idy_rows+str(idx)].value
idy_cols = gcl(idy+1)
val_y = self.ws[idy_cols+str(idx)].value
if not val_x:
break
n_rows.append(val_x)
n_cols.append(val_y)
idx += 1
v.inner_x = max(n_rows)
v.inner_y = max(n_cols)
def countryRegions():
# Uses sys.argv to pass in arguments
args = sys.argv[1:]
fileName = args[0]
cols = args[1:]
dicty = impDi('../../regions.txt')
# Open an existing excel file
wb = openpyxl.load_workbook(fileName)
sheet = wb.worksheets[0]
#################
# DO STUFF HERE #
#################
first = 2
last = sheet.max_row + 1
changes = 0
for col in cols:
for row in range(first, last):
if sheet[col + str(row)].value:
info = str(sheet[col + str(row)].value).strip().title()
if info in dicty:
changes = changes + 1
region = dicty[info]
sheet[gcl(number_from_column(col) + 1) +
str(row)].value = region
print(col + str(row) + ":", info, '->', region)
print("Processed " + str((last - first) * len(cols)) + " rows...")
print("Changed " + str(changes) + " values...")
# add the word 'formatted' and save the new file where the original is
newName = 'regions'
index = fileName[::-1].find('/')
end = fileName[-index - 1:]
fileName = fileName[:-index - 1] + newName + end[0].capitalize() + end[1:]
print("Saving " + fileName)
wb.save(fileName)
# LMK when the script is done
pygame.init()
pygame.mixer.music.load('/home/andrefisch/python/evan/note.mp3')
pygame.mixer.music.play()
time.sleep(5)
pygame.mixer.music.stop()
def _get_outer_dimensions(self):
""" Get the number of wells, layout of the plate
"""
for _, v in self.sets.items():
# we stored the data start, not the header start!
row = str(v.row -1)
# we skip X Read/Y Read
col = v.col + 2
labels = []
while col <= self.ws.max_column:
col_name = gcl(col)
cell = self.ws[col_name+row]
labels.append(cell.value)
col += 1
# process header for outer dimension
uniq = set([d[0] for d in labels])
v.labels = labels
v.n_wells = len(labels)
v.outer_x = len(uniq)
v.outer_y = int(len(labels)/len(uniq))
def add_template(xl_ws):
# Text and formulas
xl_ws['A1'] = 'Result'
xl_ws['B1'] = 'Team'
xl_ws['C1'] = 'K/A/D'
xl_ws['D1'] = 'K/R'
xl_ws['E1'] = 'K/D'
xl_ws['F1'] = 'HS rate'
xl_ws['G1'] = 'Score'
xl_ws['H1'] = 'Map'
xl_ws['I1'] = 'Date'
xl_ws['J1'] = 'ELO diff.'
xl_ws['K1'] = 'ELO aft. match'
xl_ws['N2'] = 'Total games'
xl_ws['N3'] = '=COUNTA(A:A)-1'
xl_ws['O2'] = 'Win rate'
xl_ws['O3'] = '=COUNTIF(A:A,"Win")/N3'
xl_ws['P2'] = 'Average K/D'
xl_ws['P3'] = '=AVERAGE(E:E)'
xl_ws['Q2'] = 'Av. HS rate'
xl_ws['Q3'] = '=AVERAGE(F:F)'
xl_ws['N5'] = 'Map'
xl_ws['N6'] = 'Map %'
xl_ws['N7'] = 'Win rate %'
xl_ws['O5'] = 'de_mirage'
xl_ws['P5'] = 'de_inferno'
xl_ws['Q5'] = 'de_dust2'
xl_ws['R5'] = 'de_overpass'
xl_ws['S5'] = 'de_nuke'
xl_ws['T5'] = 'de_vertigo'
xl_ws['U5'] = 'de_train'
for i in range(15, 22):
col_letter = gcl(i)
xl_ws.cell(row=6, column=i).value = f'=COUNTIF(H:H,{col_letter}5)/N3'
xl_ws.cell(row=7, column=i).value = f'=IF(COUNTIF(H:H,{col_letter}5)=0,0,' \
f'COUNTIFS(H:H,"="&{col_letter}5,A:A,"=Win")/COUNTIF(H:H,{col_letter}5))'
# Conditional formatting
win_rule = Rule(
type="containsText",
operator="containsText",
text="Win",
dxf=DifferentialStyle(
fill=PatternFill(patternType='solid', bgColor=win)))
win_rule.formula = ['NOT(ISERROR(SEARCH("Win",A1)))']
xl_ws.conditional_formatting.add('A1:A1048576', win_rule)
lose_rule = Rule(
type="containsText",
operator="containsText",
text="Lose",
dxf=DifferentialStyle(
fill=PatternFill(patternType='solid', bgColor=lose)))
lose_rule.formula = ['NOT(ISERROR(SEARCH("Lose",A1)))']
xl_ws.conditional_formatting.add('A1:A1048576', lose_rule)
colour_scale_rule = ColorScaleRule(start_type='min',
start_color=red,
mid_type='percentile',
mid_color=yellow,
end_type='max',
end_color=green)
xl_ws.conditional_formatting.add('E1:E1048576', colour_scale_rule)
xl_ws.conditional_formatting.add('F1:F1048576', colour_scale_rule)
def apply_styles(xl_ws):
# Static formatting
last_row = calc_last_row(xl_ws)
for i in range(1, 23):
if i == 4 or i == 5 or i == 6:
xl_ws.column_dimensions[gcl(i)].width = 8.86
elif i == 2 or i == 9:
xl_ws.column_dimensions[gcl(i)].width = 16
else:
xl_ws.column_dimensions[gcl(i)].width = 12.14
for i in range(1, last_row):
xl_ws.row_dimensions[i].height = 18.75
for row in xl_ws[f'A2:K{last_row - 1}']:
for c in row:
c.alignment = Alignment(horizontal='center',
vertical='center',
shrink_to_fit=True)
c.border = Border(right=Side(border_style='thin'))
c.font = Font(size=12)
for row in xl_ws['A1:K1']:
for c in row:
c.font = Font(size=14, bold=True)
c.alignment = Alignment(horizontal='center',
vertical='center',
shrink_to_fit=True)
c.border = Border(bottom=Side(border_style='thick'),
right=Side(border_style='thin'))
c.fill = PatternFill('solid', fgColor=purple)
all_borders = Border(top=Side(border_style='thin'),
bottom=Side(border_style='thin'),
left=Side(border_style='thin'),
right=Side(border_style='thin'))
for row in xl_ws['N2:Q3']:
for c in row:
c.alignment = Alignment(horizontal='center',
vertical='center',
shrink_to_fit=True)
c.font = Font(size=14)
c.border = all_borders
for row in xl_ws['N5:U7']:
for c in row:
c.alignment = Alignment(horizontal='center',
vertical='center',
shrink_to_fit=True)
c.font = Font(size=14)
c.border = all_borders
for row in xl_ws['O6:U7']:
for c in row:
c.font = Font(size=14, color=maroon)
c.number_format = '0%'
for i in range(14, 18):
xl_ws[f'{gcl(i)}2'].fill = PatternFill('solid', fgColor=purple)
for i in range(5, 8):
xl_ws[f'N{i}'].fill = PatternFill('solid', fgColor=purple)
xl_ws['O3'].number_format = '0.00%'
xl_ws['P3'].number_format = '0.00'
xl_ws['Q3'].number_format = '0.0%'
for i in range(1, last_row):
xl_ws[f'D{i}'].number_format = '0.00'
xl_ws[f'E{i}'].number_format = '0.00'
xl_ws[f'F{i}'].number_format = '0%'
xl_ws[f'J{i}'].number_format = '+#;-#'
for i in range(15, 22):
col_letter = gcl(i)
c = xl_ws[f'{col_letter}5']
c.fill = PatternFill('solid', fgColor=map_colors[c.value])
if i == 16:
c.font = Font(size=14, color=white)
for row in xl_ws.iter_rows(min_row=2,
min_col=8,
max_col=8,
max_row=last_row - 1):
for c in row:
c.fill = PatternFill(patternType='solid',
fgColor=map_colors[c.value])
if c.value == 'de_inferno':
c.font = Font(size=12, color=white)
def write_data_sheet(ws, db, chart_set, config): if chart_set == None: chart_set = set(db.charts) if config == None: config = config_all(db) fmix = get_latest_filtered_mix(db, config.mix_ids) fver = db.newest_version_from_mix(fmix) charts = list(chart_set) charts.sort(key=lambda cid: db.chart_sort_key(cid, fver, down=config.down)) headers = [ "CID", # A "SID", # B "GID", # C "Title", # D "Cut", # E "Mode", # F "Difficulty", # G "First Seen", # H "Last Seen", # I "BPM", # J "Category", # K "Stepmaker", # L ] headers += config.mixes headers += [ "Labels", "Card", "Comment" ] m = len(config.mixes) for i in range(len(headers)): ws.cell(row=1, column=i+1, value=headers[i]).font = Font(bold=True) for i, cid in enumerate(charts): sid = db.charts[cid].songId game_id = db.song_game_id(sid, fver) if game_id == None: game_id = "" first_seen = last_seen = "???" vid = db.chart_introduced(cid) if vid != None: first_seen = db.version_title(vid) vid = db.chart_last_seen(cid) if vid != None: last_seen = db.version_title(vid) ws.cell(row=i+2, column=1, value=cid) ws.cell(row=i+2, column=2, value=sid) ws.cell(row=i+2, column=3, value=game_id) ws.cell(row=i+2, column=4, value=db.song_title(sid, fver)) ws.cell(row=i+2, column=5, value=db.song_cut_str(sid)) ws.cell(row=i+2, column=6, value=db.chart_mode_str(cid, fver)) ws.cell(row=i+2, column=7, value=db.chart_difficulty_str(cid, fver)) ws.cell(row=i+2, column=8, value=first_seen) ws.cell(row=i+2, column=9, value=last_seen) ws.cell(row=i+2, column=10, value=db.song_bpm_str(sid, fver)) ws.cell(row=i+2, column=11, value=db.song_category(sid, fver)) ws.cell(row=i+2, column=12, value=str(db.chart_stepmaker(cid))) for j, mid in enumerate(config.mix_ids): ws.cell(row=i+2, column=13+j, value="NY"[db.chart_in_mix(cid, mid)]) ws.cell(row=i+2, column=13+m, value=",".join(db.chart_labels(cid, fver))) ws.cell(row=i+2, column=14+m, value=db.song_card(sid, fver)) ws.cell(row=i+2, column=15+m, value=db.song_comment(sid, fver)) ws.column_dimensions['A'].width = 5 ws.column_dimensions['B'].width = 5 ws.column_dimensions['C'].width = 5 ws.column_dimensions['D'].width = 36 ws.column_dimensions['E'].width = 9 ws.column_dimensions['F'].width = 9 ws.column_dimensions['G'].width = 3 ws.column_dimensions['H'].width = 17 ws.column_dimensions['I'].width = 17 ws.column_dimensions['J'].width = 8 ws.column_dimensions['K'].width = 14 ws.column_dimensions['L'].width = 31 for i in range(m): ws.column_dimensions[gcl(13+i)].width = 2 ws.column_dimensions[gcl(13+m)].width = 39 ws.column_dimensions[gcl(14+m)].width = 48 ws.column_dimensions[gcl(15+m)].width = 120 ws.freeze_panes = 'A2'
def write_summary_sheet(ws, db, chart_set, config, mixId, pad):
table_names = ["Single + Single Performance", "Double + Double Performance", "Half-Double", "Routine", "Co-Op"]
table_colors = ["ff2211", "11dd22", "cc0066", "23a98d", "f2c219"]
table_headers = ["Passed", "Failed", "Unplayed", "SSS", "SS", "S", "A", "B", "C", "D", "F", "Low Miss", "High Miss", "Avg Miss"]
gray = PatternFill("solid", fgColor="eeece1")
def difficulty_sort_key(d):
if d == None:
return 99
if config.down:
return -d
return d
def difficulty_name(d):
if d == None:
return "??"
return "%02d" % d
table = []
for cid in chart_set:
ver = db.chart_version_in_mix(cid, mixId)
mode_title = db.chart_mode_str(cid, ver)
if mode_title == None: continue
mode_title = mode_title.lower()
table_num = -1
if mode_title.startswith("single"):
table_num = 0
elif mode_title.startswith("double"):
table_num = 1
elif mode_title.startswith("half"):
table_num = 2
elif mode_title.startswith("routine"):
table_num = 3
elif mode_title.startswith("co"):
table_num = 4
if table_num == -1: continue
difficulty = db.chart_difficulty(cid, ver)
table.append((table_num, difficulty_sort_key(difficulty), difficulty, cid))
table.sort()
table.append((None, None, None, None))
ws.cell(row=1, column=17, value="CID")
ws.cell(row=1, column=18, value="T")
ws.cell(row=1, column=19, value="D")
ws.cell(row=1, column=20, value="Pass")
ws.cell(row=1, column=21, value="Grade")
ws.cell(row=1, column=22, value="Miss")
last_mode = last_diff = -1
start_row = end_row = -1
main_row = 1
score_col = 6
if not pad and config.pad:
score_col = 10
for r, (mode, _, diff, cid) in enumerate(table):
# Add an entry to the hidden lookup table
if mode != None:
ws.cell(row=r+2, column=17, value=cid)
ws.cell(row=r+2, column=18, value=mode)
ws.cell(row=r+2, column=19, value=difficulty_name(diff))
ws.cell(row=r+2, column=20, value='=IF(VLOOKUP(Q%d, Scores!$A$2:$M$9999, %d, FALSE)="", "", UPPER(VLOOKUP(Q%d, Scores!$A$2:$M$9999, %d, FALSE)))' % (r+2, score_col+0, r+2, score_col+0))
ws.cell(row=r+2, column=21, value='=IF(VLOOKUP(Q%d, Scores!$A$2:$M$9999, %d, FALSE)="", "", UPPER(VLOOKUP(Q%d, Scores!$A$2:$M$9999, %d, FALSE)))' % (r+2, score_col+1, r+2, score_col+1))
ws.cell(row=r+2, column=22, value='=IF(VLOOKUP(Q%d, Scores!$A$2:$M$9999, %d, FALSE)="", "", VLOOKUP(Q%d, Scores!$A$2:$M$9999, %d, FALSE))' % (r+2, score_col+2, r+2, score_col+2))
# If there is a previous row...
if last_mode != -1:
# Finish writing the previous row
if last_diff != diff or last_mode != mode:
end_row = r+1
ws.cell(row=main_row-1, column=2, value='=COUNTIF(T%d:T%d, "Y")' % (start_row, end_row))
ws.cell(row=main_row-1, column=3, value='=COUNTIF(T%d:T%d, "N")' % (start_row, end_row))
ws.cell(row=main_row-1, column=4, value='=(%d-%d+1) - B%d - C%d' % (end_row, start_row, main_row-1, main_row-1))
ws.cell(row=main_row-1, column=5, value='=IF(COUNTIF(U%d:U%d, "SSS")<>0,COUNTIF(U%d:U%d, "SSS"),"")' % (start_row, end_row, start_row, end_row))
ws.cell(row=main_row-1, column=6, value='=IF(COUNTIF(U%d:U%d, "SS")<>0,COUNTIF(U%d:U%d, "SS"),"")' % (start_row, end_row, start_row, end_row))
ws.cell(row=main_row-1, column=7, value='=IF(COUNTIF(U%d:U%d, "S")<>0,COUNTIF(U%d:U%d, "S"),"")' % (start_row, end_row, start_row, end_row))
ws.cell(row=main_row-1, column=8, value='=IF(COUNTIF(U%d:U%d, "A")<>0,COUNTIF(U%d:U%d, "A"),"")' % (start_row, end_row, start_row, end_row))
ws.cell(row=main_row-1, column=9, value='=IF(COUNTIF(U%d:U%d, "B")<>0,COUNTIF(U%d:U%d, "B"),"")' % (start_row, end_row, start_row, end_row))
ws.cell(row=main_row-1, column=10, value='=IF(COUNTIF(U%d:U%d, "C")<>0,COUNTIF(U%d:U%d, "C"),"")' % (start_row, end_row, start_row, end_row))
ws.cell(row=main_row-1, column=11, value='=IF(COUNTIF(U%d:U%d, "D")<>0,COUNTIF(U%d:U%d, "D"),"")' % (start_row, end_row, start_row, end_row))
ws.cell(row=main_row-1, column=12, value='=IF(COUNTIF(U%d:U%d, "F")<>0,COUNTIF(U%d:U%d, "F"),"")' % (start_row, end_row, start_row, end_row))
ws.cell(row=main_row-1, column=13, value='=IF(SUMPRODUCT(--(V%d:V%d<>""))=0,"",MIN(V%d:V%d))' % (start_row, end_row, start_row, end_row))
ws.cell(row=main_row-1, column=14, value='=IF(SUMPRODUCT(--(V%d:V%d<>""))=0,"",MAX(V%d:V%d))' % (start_row, end_row, start_row, end_row))
ws.cell(row=main_row-1, column=15, value='=IF(SUMPRODUCT(--(V%d:V%d<>""))=0,"",AVERAGE(V%d:V%d))' % (start_row, end_row, start_row, end_row))
# Finish the table
if last_mode != mode:
for i in range(15):
add_border(ws, main_row-1, i+1, bottom=Side(style="medium"))
main_row += 1
# If this is a new entry...
if mode != None:
# Draw the header of a new table
if last_mode != mode:
ws.merge_cells("A%d:O%d" % (main_row, main_row))
ws.cell(row=main_row, column=1, value=table_names[mode])
ws.cell(row=main_row, column=1).alignment = Alignment(horizontal='center')
ws.cell(row=main_row, column=1).font = Font(bold=True)
ws.cell(row=main_row, column=1).fill = PatternFill("solid", fgColor=table_colors[mode])
for i, header in enumerate(table_headers):
ws.cell(row=main_row+1, column=i+2, value=header)
ws.cell(row=main_row+1, column=i+2).alignment = Alignment(horizontal='center')
ws.cell(row=main_row+1, column=i+2).font = Font(bold=True)
ws.cell(row=main_row+1, column=i+2).fill = gray
for i in range(15):
add_border(ws, main_row, i+1, top=Side(style="medium"))
add_border(ws, main_row+1, i+1, top=Side(style="thin"), bottom=Side(style="thin"))
add_border(ws, main_row, 1, left=Side(style="medium"), right=Side(style="medium"))
add_border(ws, main_row+1, 1, left=Side(style="medium"), right=Side(style="thin"))
add_border(ws, main_row+1, 4, right=Side(style="thin"))
add_border(ws, main_row+1, 12, right=Side(style="thin"))
add_border(ws, main_row, 15, right=Side(style="medium"))
add_border(ws, main_row+1, 15, right=Side(style="medium"))
main_row += 2
# Draw the next row
if last_mode != mode or last_diff != diff:
ws.cell(row=main_row, column=1, value=difficulty_name(diff))
ws.cell(row=main_row, column=1).font = Font(bold=True)
ws.cell(row=main_row, column=1).fill = gray
add_border(ws, main_row, 1, right=Side(style="thin"), left=Side(style="medium"))
add_border(ws, main_row, 4, right=Side(style="thin"))
add_border(ws, main_row, 12, right=Side(style="thin"))
add_border(ws, main_row, 15, right=Side(style="medium"))
for i in range(15):
ws.cell(row=main_row, column=i+1).alignment = Alignment(horizontal='center')
start_row = r+2
main_row += 1
(last_mode, last_diff) = (mode, diff)
widths = [3, 7, 7, 9, 4, 4, 4, 4, 4, 4, 4, 4, 9, 10, 9, 8, 5, 2, 3, 5, 6, 5]
for i, w in enumerate(widths):
ws.column_dimensions[gcl(i+1)].width = w
for col in "QRSTUV":
ws.column_dimensions[col].hidden = True
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))
