def toExcelSheet( self, sheet ):
''' Write the contents of the grid to an xlwt excel sheet. '''
titleStyle = xlwt.XFStyle()
titleStyle.font.bold = True
titleStyle.font.height += titleStyle.font.height / 2
rowTop = 0
if self.title:
for line in self.title.split('\n'):
sheet.write(rowTop, 0, line, titleStyle)
rowTop += 1
rowTop += 1
sheetFit = FitSheetWrapper( sheet )
# Write the colnames and data.
rowMax = 0
for col, c in enumerate(self.colnames):
isSpeed = (c == _('Speed'))
if isSpeed and self.data[col]:
c = self.colnames[col] = self.data[col][0].split()[1]
headerStyle = xlwt.XFStyle()
headerStyle.borders.bottom = xlwt.Borders.MEDIUM
headerStyle.font.bold = True
headerStyle.alignment.horz = xlwt.Alignment.HORZ_LEFT if col in self.leftJustifyCols \
else xlwt.Alignment.HORZ_RIGHT
headerStyle.alignment.wrap = xlwt.Alignment.WRAP_AT_RIGHT
style = xlwt.XFStyle()
style.alignment.horz = xlwt.Alignment.HORZ_LEFT if col in self.leftJustifyCols \
else xlwt.Alignment.HORZ_RIGHT
sheetFit.write( rowTop, col, c, headerStyle, bold=True )
for row, v in enumerate(self.data[col]):
if isSpeed and v:
v = '{}'.format(v).split()[0]
rowCur = rowTop + 1 + row
if rowCur > rowMax:
rowMax = rowCur
sheetFit.write( rowCur, col, v, style )
if isSpeed:
self.colnames[col] = _('Speed')
if self.footer:
style = xlwt.XFStyle()
style.alignment.horz = xlwt.Alignment.HORZ_LEFT
rowMax += 2
for line in self.footer.split('\n'):
sheet.write( rowMax, 0, line.strip(), style )
rowMax += 1
# Add branding at the bottom of the sheet.
style = xlwt.XFStyle()
style.alignment.horz = xlwt.Alignment.HORZ_LEFT
sheet.write( rowMax + 2, 0, brandText, style )
def toExcelSheet( self, sheet ):
''' Write the contents of the grid to an xlwt excel sheet. '''
titleStyle = xlwt.XFStyle()
titleStyle.font.bold = True
titleStyle.font.height += titleStyle.font.height / 2
rowTop = 0
if self.title:
for line in self.title.split('\n'):
sheet.write(rowTop, 0, line, titleStyle)
rowTop += 1
rowTop += 1
sheetFit = FitSheetWrapper( sheet )
# Write the colnames and data.
headerStyleLeft = xlwt.XFStyle()
headerStyleLeft.borders.bottom = xlwt.Borders.MEDIUM
headerStyleLeft.font.bold = True
headerStyleLeft.alignment.horz = xlwt.Alignment.HORZ_LEFT
headerStyleLeft.alignment.wrap = xlwt.Alignment.WRAP_AT_RIGHT
headerStyleRight = xlwt.XFStyle()
headerStyleRight.borders.bottom = xlwt.Borders.MEDIUM
headerStyleRight.font.bold = True
headerStyleRight.alignment.horz = xlwt.Alignment.HORZ_RIGHT
headerStyleRight.alignment.wrap = xlwt.Alignment.WRAP_AT_RIGHT
styleLeft = xlwt.XFStyle()
styleLeft.alignment.horz = xlwt.Alignment.HORZ_LEFT
styleLeft.alignment.wrap = True
styleLeft.alignment.vert = xlwt.Alignment.VERT_TOP
styleRight = xlwt.XFStyle()
styleRight.alignment.horz = xlwt.Alignment.HORZ_RIGHT
styleRight.alignment.wrap = True
styleRight.alignment.vert = xlwt.Alignment.VERT_TOP
rowMax = 0
for col, c in enumerate(self.colnames):
sheetFit.write( rowTop, col, c, headerStyleLeft if col in self.leftJustifyCols else headerStyleRight, bold=True )
style = styleLeft if col in self.leftJustifyCols else styleRight
for row, v in enumerate(self.data[col]):
rowCur = rowTop + 1 + row
if rowCur > rowMax:
rowMax = rowCur
sheetFit.write( rowCur, col, v, style )
# Add branding at the bottom of the sheet.
style = xlwt.XFStyle()
style.alignment.horz = xlwt.Alignment.HORZ_LEFT
sheet.write( rowMax + 2, 0, brandText, style )
def UCIExport( sheet, cat ):
race = Model.race
if not race:
return
SyncExcelLink( race )
sheetFit = FitSheetWrapper( sheet )
titleStyle = xlwt.XFStyle()
titleStyle.font.bold = True
leftAlignStyle = xlwt.XFStyle()
rightAlignStyle = xlwt.XFStyle()
rightAlignStyle.alignment.horz = xlwt.Alignment.HORZ_RIGHT
results = GetResults( cat, True )
def toInt( n ):
try:
return int(n.split()[0])
except:
return n
row = 0
for col, field in enumerate(UCIFields):
sheetFit.write( row, col, field, titleStyle, bold=True )
row += 1
for rr in results:
try:
finishTime = formatTimeGap(rr.lastTime - rr.raceTimes[0]) if rr.status == Model.Rider.Finisher else ''
except Exception as e:
finishTime = ''
gap = getattr(rr, 'gap', '')
if reHighPrecision.match(gap):
gap = gap[:-4] + '"'
for col, field in enumerate(UCIFields):
{
'Pos': lambda : sheetFit.write( row, col, toInt(rr.pos), rightAlignStyle ),
'Nr.': lambda : sheetFit.write( row, col, rr.num, rightAlignStyle ),
'Name': lambda : sheetFit.write( row, col, rr.full_name(), leftAlignStyle ),
'Team': lambda : sheetFit.write( row, col, getattr(rr, 'Team', ''), leftAlignStyle ),
'UCI Code': lambda : sheetFit.write( row, col, getattr(rr, 'UCICode', ''), leftAlignStyle ),
'Time': lambda : sheetFit.write( row, col, finishTime, rightAlignStyle ),
'Gap': lambda : sheetFit.write( row, col, gap, rightAlignStyle ),
}[field]()
row += 1
def onExcel( self, event ):
results = self.getResults()
if not results:
return
fileName = Utils.getFileName() or 'test.cmn'
xlFName = os.path.splitext(fileName)[0] + '-Unmatched-RFID-Tags.xls'
dlg = wx.DirDialog( self, u'{} "{}"'.format(_('Folder to write'), os.path.basename(xlFName)),
style=wx.DD_DEFAULT_STYLE, defaultPath=os.path.dirname(xlFName) )
ret = dlg.ShowModal()
dName = dlg.GetPath()
dlg.Destroy()
if ret != wx.ID_OK:
return
xlFName = os.path.join( dName, os.path.basename(xlFName) )
wb = xlwt.Workbook()
sheetCur = wb.add_sheet( 'Unmatched RFID Tags' )
headerStyle = xlwt.XFStyle()
headerStyle.borders.bottom = xlwt.Borders.MEDIUM
headerStyle.font.bold = True
sheetFit = FitSheetWrapper( sheetCur )
sheetFit.write( 0, 0, _('Tag'), headerStyle, bold=True )
for col, t in enumerate(results[0][1], 1):
sheetFit.write( 0, col, u'{} {}'.format(_('Time'), col), headerStyle, bold=True )
for rowTop, (tag, times) in enumerate(self.getResults(), 1):
sheetFit.write( rowTop, 0, tag )
for col, t in enumerate(times, 1):
sheetFit.write( rowTop, col,
Utils.formatTime(
t, extraPrecision=True, forceHours=True, twoDigitHours=True,
)
)
try:
wb.save( xlFName )
Utils.LaunchApplication( xlFName )
Utils.MessageOK(self, u'{}:\n\n {}'.format(_('Excel file written to'), xlFName), _('Excel Write'))
except IOError:
Utils.MessageOK(self,
u'{} "{}"\n\n{}\n{}'.format(
_('Cannot write'), xlFName,
_('Check if this spreadsheet is already open.'),
_('If so, close it, and try again.')
),
_('Excel File Error'), iconMask=wx.ICON_ERROR )
def onExcel( self, event ):
results = self.getResults()
if not results:
return
fileName = Utils.getFileName() or 'test.cmn'
xlFName = os.path.splitext(fileName)[0] + '-Unmatched-RFID-Tags.xls'
dlg = wx.DirDialog( self, u'{} "{}"'.format(_('Folder to write'), os.path.basename(xlFName)),
style=wx.DD_DEFAULT_STYLE, defaultPath=os.path.dirname(xlFName) )
ret = dlg.ShowModal()
dName = dlg.GetPath()
dlg.Destroy()
if ret != wx.ID_OK:
return
xlFName = os.path.join( dName, os.path.basename(xlFName) )
wb = xlwt.Workbook()
sheetCur = wb.add_sheet( 'Unmatched RFID Tags' )
headerStyle = xlwt.XFStyle()
headerStyle.borders.bottom = xlwt.Borders.MEDIUM
headerStyle.font.bold = True
sheetFit = FitSheetWrapper( sheetCur )
sheetFit.write( 0, 0, _('Tag'), headerStyle, bold=True )
for col, t in enumerate(results[0][1], 1):
sheetFit.write( 0, col, u'{} {}'.format(_('Time'), col), headerStyle, bold=True )
for rowTop, (tag, times) in enumerate(self.getResults(), 1):
sheetFit.write( rowTop, 0, tag )
for col, t in enumerate(times, 1):
sheetFit.write( rowTop, col,
Utils.formatTime(
t, extraPrecision=True, forceHours=True, twoDigitHours=True,
)
)
try:
wb.save( xlFName )
webbrowser.open( xlFName, new = 2, autoraise = True )
Utils.MessageOK(self, u'{}:\n\n {}'.format(_('Excel file written to'), xlFName), _('Excel Write'))
except IOError:
Utils.MessageOK(self,
u'{} "{}"\n\n{}\n{}'.format(
_('Cannot write'), xlFName,
_('Check if this spreadsheet is already open.'),
_('If so, close it, and try again.')
),
_('Excel File Error'), iconMask=wx.ICON_ERROR )
def toExcelSheet(self, sheet):
''' Write the contents of the grid to an xlwt excel sheet. '''
titleStyle = xlwt.XFStyle()
titleStyle.font.bold = True
titleStyle.font.height += titleStyle.font.height // 2
rowTop = 0
if self.title:
for line in self.title.split('\n'):
sheet.write(rowTop, 0, line, titleStyle)
rowTop += 1
rowTop += 1
sheetFit = FitSheetWrapper(sheet)
# Write the colnames and data.
headerStyleLeft = xlwt.XFStyle()
headerStyleLeft.borders.bottom = xlwt.Borders.MEDIUM
headerStyleLeft.font.bold = True
headerStyleLeft.alignment.horz = xlwt.Alignment.HORZ_LEFT
headerStyleLeft.alignment.wrap = xlwt.Alignment.WRAP_AT_RIGHT
headerStyleRight = xlwt.XFStyle()
headerStyleRight.borders.bottom = xlwt.Borders.MEDIUM
headerStyleRight.font.bold = True
headerStyleRight.alignment.horz = xlwt.Alignment.HORZ_RIGHT
headerStyleRight.alignment.wrap = xlwt.Alignment.WRAP_AT_RIGHT
styleLeft = xlwt.XFStyle()
styleLeft.alignment.horz = xlwt.Alignment.HORZ_LEFT
styleLeft.alignment.wrap = True
styleLeft.alignment.vert = xlwt.Alignment.VERT_TOP
styleRight = xlwt.XFStyle()
styleRight.alignment.horz = xlwt.Alignment.HORZ_RIGHT
styleRight.alignment.wrap = True
styleRight.alignment.vert = xlwt.Alignment.VERT_TOP
rowMax = 0
for col, c in enumerate(self.colnames):
sheetFit.write(rowTop,
col,
c,
headerStyleLeft if col in self.leftJustifyCols else
headerStyleRight,
bold=True)
style = styleLeft if col in self.leftJustifyCols else styleRight
for row, v in enumerate(self.data[col]):
rowCur = rowTop + 1 + row
if rowCur > rowMax:
rowMax = rowCur
sheetFit.write(rowCur, col, v, style, bold=True)
# Add branding at the bottom of the sheet.
style = xlwt.XFStyle()
style.alignment.horz = xlwt.Alignment.HORZ_LEFT
sheet.write(rowMax + 2, 0, brandText, style)
def UCIExport(sheet, cat):
race = Model.race
if not race:
return
sheetFit = FitSheetWrapper(sheet)
titleStyle = xlwt.XFStyle()
titleStyle.font.bold = True
leftAlignStyle = xlwt.XFStyle()
rightAlignStyle = xlwt.XFStyle()
rightAlignStyle.alignment.horz = xlwt.Alignment.HORZ_RIGHT
results = GetResults(cat, True)
def toInt(n):
try:
return int(n)
except:
return n
row = 0
for col, field in enumerate(UCIFields):
sheetFit.write(row, col, field, titleStyle, bold=True)
row += 1
for rr in results:
for col, field in enumerate(UCIFields):
{
"Pos": lambda: sheetFit.write(row, col, toInt(rr.pos), rightAlignStyle),
"Nr.": lambda: sheetFit.write(row, col, rr.num, rightAlignStyle),
"Name": lambda: sheetFit.write(row, col, rr.full_name(), leftAlignStyle),
"UCI Code": lambda: sheetFit.write(row, col, getattr(rr, "License", ""), leftAlignStyle),
"Team": lambda: sheetFit.write(row, col, getattr(rr, "Team", ""), leftAlignStyle),
}[field]()
row += 1
def MakeExampleExcel():
random.seed( 0xed )
common_first_names = [unicode(n,'utf-8') for n in 'Léopold Grégoire Aurélien Rémi Léandre Thibault Kylian Nathan Lucas Enzo Léo Louis Hugo Gabriel Ethan Mathis Jules Raphaël Arthur Théo Noah Timeo Matheo Clément Maxime Yanis Maël'.split()]
common_last_names = [unicode(n,'utf-8') for n in 'Tisserand Lavergne Guignard Parmentier Evrard Leclerc Martin Bernard Dubois Petit Durand Leroy Moreau Simon Laurent Lefevre Roux Fournier Dupont'.split()]
teams = [unicode(n,'utf-8') for n in 'Pirates of the Pavement,Coastbusters,Tour de Friends,Pesky Peddlers,Spoke & Mirrors'.split(',')]
fname_excel = os.path.join( Utils.getHomeDir(), 'StageRaceGC_Test_Input.xlsx' )
wb = xlsxwriter.Workbook( fname_excel )
bold_format = wb.add_format( {'bold': True} )
time_format = wb.add_format( {'num_format': 'hh:mm:ss'} )
high_precision_time_format = wb.add_format( {'num_format': 'hh:mm:ss.000'} )
ws = wb.add_worksheet('Registration')
fit_sheet = FitSheetWrapper( ws )
fields = ['bib', 'first_name', 'last_name', 'uci_id', 'license', 'team']
row = 0
for c, field in enumerate(fields):
fit_sheet.write( row, c, Utils.fieldToHeader(field), bold_format )
riders = 25
team_size = riders // len(teams)
bibs = []
for i in xrange(riders):
row += 1
bibs.append((i//team_size+1)*10 + (i%team_size))
fit_sheet.write( row, 0, bibs[i] )
fit_sheet.write( row, 1, common_first_names[i%len(common_first_names)] )
fit_sheet.write( row, 2, common_last_names[i%len(common_last_names)] )
fit_sheet.write( row, 3, get_uci_id() )
fit_sheet.write( row, 4, get_license() )
fit_sheet.write( row, 5, teams[i//team_size] )
stageCount = 5
for stage in xrange(stageCount):
isTT = (stage == 3-1)
if isTT:
tf = high_precision_time_format
race_time = 60*60
ws = wb.add_worksheet('Stage {}-ITT'.format(stage+1))
else:
tf = time_format
race_time = 4*60*60
ws = wb.add_worksheet('Stage {}-RR'.format(stage+1))
fit_sheet = FitSheetWrapper( ws )
fields = ['bib', 'time', 'place', 'penalty', 'bonus', 'kom 1 1C', 'kom 2 HC', 'sprint 1', 'stage sprint']
if isTT:
fields = fields[:5]
row = 0
for c, field in enumerate(fields):
fit_sheet.write( row, c, Utils.fieldToHeader(field), bold_format )
bibAB = []
for i, (bib, t) in enumerate(sorted( ((bib, random.normalvariate(race_time-bib/4.0, 5*60)) for bib in bibs), key=operator.itemgetter(1) )):
row += 1
fit_sheet.write( row, 0, bib )
fit_sheet.write( row, 1, t/(24.0*60.0*60.0), tf )
if stage in (4-1, 5-1) and i == len(bibs)-1:
bibAB.append( bib )
fit_sheet.write( row, 2, 'AB' )
for b in bibAB:
bibs.remove( b )
if not isTT:
for c in xrange(5,9):
positions = [x for x in xrange(len(bibs))]
random.shuffle( positions )
if fields[c] == 'stage sprint':
points = stage_points[stage%len(stage_points)]
positions.sort()
elif fields[c] == 'sprint 1':
points = [6,4,2]
elif fields[c] == 'kom 2 HC':
points = kom_by_category[0]
elif fields[c] == 'kom 1 1C':
points = kom_by_category[1]
for point, pos in zip(points, positions):
fit_sheet.write( pos+1, c, point )
wb.close()
return fname_excel
def MakeExampleExcel( include_uci_points=True, include_national_points=True, include_previous_result=True ):
Year = datetime.date.today().year
YearAdjust = Year - 2017
fname = os.path.join(Utils.getImageFolder(), 'IndividualRanking.xlsx')
reader = GetExcelReader( fname )
uci_points = Source( fname, 'Individual' )
uci_points.read( reader )
for r in uci_points.results:
r.age += YearAdjust
r.license = random_license()
uci_sample = random.sample( uci_points.results, 20 )
common_first_names = u'Léopold Grégoire Aurélien Rémi Léandre Thibault Kylian Nathan Lucas Enzo Léo Louis Hugo Gabriel Ethan Mathis Jules Raphaël Arthur Théo Noah Timeo Matheo Clément Maxime Yanis Maël'.split()
common_last_names = u'Tisserand Lavergne Guignard Parmentier Evrard Leclerc Martin Bernard Dubois Petit Durand Leroy Moreau Simon Laurent Lefevre Roux Fournier Dupont'.split()
other_sample = []
for i in range(20):
other_sample.append( Result(
first_name=common_first_names[i%len(common_first_names)],
last_name=common_last_names[i%len(common_last_names)],
uci_id=random_uci_id(),
license=random_license(),
)
)
registration = list(uci_sample) + other_sample
bibs = list(range(100,200))
random.shuffle( bibs )
for i, r in enumerate(registration):
r.bib = bibs[i]
fname_excel = os.path.join( Utils.getHomeDir(), 'CS_Test_Input.xlsx' )
wb = xlsxwriter.Workbook( fname_excel )
ws = wb.add_worksheet('Registration')
fit_sheet = FitSheetWrapper( ws )
fields = ['bib', 'first_name', 'last_name', 'uci_id', 'license']
for c, field in enumerate(fields):
fit_sheet.write( 0, c, make_title(field) )
for r, result in enumerate(registration):
for c, field in enumerate(fields):
fit_sheet.write( r+1, c, getattr(result, field) )
if include_uci_points:
ws = wb.add_worksheet('Individual')
fit_sheet = FitSheetWrapper( ws )
for c, header in enumerate(['Rank', 'UCI ID', 'Name', 'Team Code', 'Age', 'Points']):
fit_sheet.write( 0, c, header )
for r, result in enumerate(uci_points.results):
row = r + 1
fit_sheet.write( row, 0, u'{}'.format(row) )
fit_sheet.write( row, 1, result.uci_id if result.uci_id else u'' )
fit_sheet.write( row, 2, u'{} {}'.format(result.last_name.upper(), result.first_name) )
fit_sheet.write( row, 3, result.team_code if result.team_code else u'' )
fit_sheet.write( row, 4, result.age )
fit_sheet.write( row, 5, result.points )
eligible_for_points = other_sample + [rr for rr in uci_points.results if rr.nation_code == 'FRA']
if include_national_points:
ws = wb.add_worksheet('National Points')
fit_sheet = FitSheetWrapper( ws )
for c, header in enumerate(['First Name', 'Last Name', 'License', 'Points']):
fit_sheet.write( 0, c, header )
for r, result in enumerate(random.sample(eligible_for_points, min(len(eligible_for_points),35))):
row = r + 1
fit_sheet.write( row, 0, result.first_name )
fit_sheet.write( row, 1, result.last_name )
fit_sheet.write( row, 2, result.license )
fit_sheet.write( row, 3, random.randint(1, 200) )
if include_previous_result:
ws = wb.add_worksheet( '{} Result'.format(Year-1) )
fit_sheet = FitSheetWrapper( ws )
for c, header in enumerate(['Pos', 'First Name', 'Last Name', 'UCI ID', 'License']):
fit_sheet.write( 0, c, header )
for r, result in enumerate(random.sample(eligible_for_points, min(len(eligible_for_points),35))):
row = r + 1
fit_sheet.write( row, 0, row )
fit_sheet.write( row, 1, result.first_name )
fit_sheet.write( row, 2, result.last_name )
fit_sheet.write( row, 3, result.uci_id )
fit_sheet.write( row, 4, result.license )
wb.close()
return fname_excel
def CallupResultsToExcel( fname_excel, registration_headers, callup_headers, callup_results, is_callup=True, top_riders=999999, exclude_unranked=False ):
callup_results = callup_results[:top_riders]
if exclude_unranked:
callup_results = [r for r in callup_results if any(r[k] for k in range(len(registration_headers), len(callup_headers)))]
if not is_callup:
callup_results = reversed( callup_results )
wb = xlsxwriter.Workbook( fname_excel )
ws = wb.add_worksheet('Callups' if is_callup is True else 'Seeding')
# Set print prefereces.
ws.set_landscape()
ws.hide_gridlines( 0 )
ws.fit_to_pages( 1, 1 )
fit_sheet = FitSheetWrapper( ws )
bold_format = wb.add_format( {'bold': True} )
date_format = wb.add_format( {'num_format': 'yyyy/mm/dd'} )
rowNum = 0
last_name_col = None
uci_id_col = None
ignore_headers = set(['age'])
for col, v in enumerate(callup_headers):
if v == 'last_name':
last_name_col = col
elif v == 'uci_id':
uci_id_col = col
header_col = {}
col_cur = 1 # Add one columne room for the Order column.
for v in callup_headers:
if v in ignore_headers:
continue
header_col[v] = col_cur
col_cur += 1
fit_sheet.write( rowNum, 0, 'Order', bold_format, bold=True )
for v in callup_headers:
if v in ignore_headers:
continue
fit_sheet.write( rowNum, header_col[v], make_title(v), bold_format, bold=True )
rowNum += 1
for row in callup_results:
fit_sheet.write( rowNum, 0, rowNum )
for c, value in enumerate(row):
if callup_headers[c] in ignore_headers:
continue
try:
v = value.get_value()
findResult = value
except AttributeError:
v = value
findResult = None
col = header_col[callup_headers[c]]
if isinstance(v, datetime.date):
fit_sheet.write( rowNum, col, v, date_format )
else:
if c == last_name_col:
v = u'{}'.format(v).upper()
elif c == uci_id_col:
v = Model.format_uci_id( u'{}'.format(v) )
fit_sheet.write( rowNum, col, v )
if findResult and findResult.get_status() != findResult.NoMatch:
ws.write_comment( rowNum, col, findResult.get_message(), {'width': 200, 'height': 200} )
rowNum += 1
wb.close()
def menuExportFinalClassificationToExcel( self, event ):
self.commit()
pageTitle = 'Final Classification'
if not self.fileName or len(self.fileName) < 4:
Utils.MessageOK(self, 'You must Save before you can Export to Excel', 'Excel Write')
return
model = Model.model
competition = model.competition
pageTitle = Utils.RemoveDisallowedFilenameChars( pageTitle.replace('/', '_') )
xlFName = self.fileName[:-4] + '-' + pageTitle + ' Export.xls'
dlg = wx.DirDialog( self, 'Folder to write "%s"' % os.path.basename(xlFName),
style=wx.DD_DEFAULT_STYLE, defaultPath=os.path.dirname(xlFName) )
ret = dlg.ShowModal()
dName = dlg.GetPath()
dlg.Destroy()
if ret != wx.ID_OK:
return
xlFName = os.path.join( dName, os.path.basename(xlFName) )
title = self.getTitle()
wb = xlwt.Workbook()
sheetName = 'Sprint Final Classification'
sheetName = re.sub('[+!#$%&+~`".:;|\\/?*\[\] ]+', ' ', sheetName)[:31]
sheetCur = wb.add_sheet( sheetName )
sheetFit = FitSheetWrapper( sheetCur )
headerNames = [u'Pos', u'Bib', u'LastName', u'FirstName', u'Team', u'License', u'Category']
leftJustifyCols = { h for h in headerNames if h not in {u'Pos', u'Bib'} }
leftStyle = xlwt.XFStyle()
leftStyle.alignment.horz = xlwt.Alignment.HORZ_LEFT
rightStyle = xlwt.XFStyle()
rightStyle.alignment.horz = xlwt.Alignment.HORZ_RIGHT
leftHeaderStyle = xlwt.XFStyle()
leftHeaderStyle.borders.bottom = xlwt.Borders.MEDIUM
leftHeaderStyle.font.bold = True
leftHeaderStyle.alignment.horz = xlwt.Alignment.HORZ_LEFT
leftHeaderStyle.alignment.wrap = xlwt.Alignment.WRAP_AT_RIGHT
rightHeaderStyle = xlwt.XFStyle()
rightHeaderStyle.borders.bottom = xlwt.Borders.MEDIUM
rightHeaderStyle.font.bold = True
rightHeaderStyle.alignment.horz = xlwt.Alignment.HORZ_RIGHT
rightHeaderStyle.alignment.wrap = xlwt.Alignment.WRAP_AT_RIGHT
rowTop = 0
results, dnfs, dqs = competition.getResults()
for col, c in enumerate(headerNames):
sheetFit.write( rowTop, col, c, leftHeaderStyle if c in leftJustifyCols else rightHeaderStyle, bold=True )
rowTop += 1
for row, r in enumerate(results):
if r:
for col, value in enumerate([row+1, r.bib if r.bib else u'', r.last_name.upper(), r.first_name, r.team, r.license, model.category]):
sheetFit.write( rowTop, col, value, leftStyle if headerNames[col] in leftJustifyCols else rightStyle )
rowTop += 1
for r in dnfs:
for col, value in enumerate([u'DNF', r.bib if r.bib else u'', r.last_name.upper(), r.first_name, r.team, r.license, model.category]):
sheetFit.write( rowTop, col, value, leftStyle if headerNames[col] in leftJustifyCols else rightStyle )
rowTop += 1
for r in dqs:
for col, value in enumerate([u'DQ', r.bib if r.bib else u'', r.last_name.upper(), r.first_name, r.team, r.license, model.category]):
sheetFit.write( rowTop, col, value, leftStyle if headerNames[col] in leftJustifyCols else rightStyle )
rowTop += 1
for r in model.getDNQs():
for col, value in enumerate([u'DQ', r.bib if r.bib else u'', r.last_name.upper(), r.first_name, r.team, r.license, model.category]):
sheetFit.write( rowTop, col, value, leftStyle if headerNames[col] in leftJustifyCols else rightStyle )
rowTop += 1
try:
wb.save( xlFName )
webbrowser.open( xlFName, new = 2, autoraise = True )
Utils.MessageOK(self, 'Excel file written to:\n\n %s' % xlFName, 'Excel Export')
except IOError:
Utils.MessageOK(self,
'Cannot write "%s".\n\nCheck if this spreadsheet is open.\nIf so, close it, and try again.' % xlFName,
'Excel File Error', iconMask=wx.ICON_ERROR )
def toExcelSheet(self, sheet):
""" Write the contents of the grid to an xlwt excel sheet. """
titleStyle = xlwt.XFStyle()
titleStyle.font.bold = True
titleStyle.font.height += titleStyle.font.height / 2
headerStyleAlignLeft = xlwt.XFStyle()
headerStyleAlignLeft.borders.bottom = xlwt.Borders.MEDIUM
headerStyleAlignLeft.font.bold = True
headerStyleAlignLeft.alignment.horz = xlwt.Alignment.HORZ_LEFT
headerStyleAlignLeft.alignment.wrap = xlwt.Alignment.WRAP_AT_RIGHT
headerStyleAlignRight = xlwt.XFStyle()
headerStyleAlignRight.borders.bottom = xlwt.Borders.MEDIUM
headerStyleAlignRight.font.bold = True
headerStyleAlignRight.alignment.horz = xlwt.Alignment.HORZ_RIGHT
headerStyleAlignRight.alignment.wrap = xlwt.Alignment.WRAP_AT_RIGHT
styleAlignLeft = xlwt.XFStyle()
styleAlignLeft.alignment.horz = xlwt.Alignment.HORZ_LEFT
styleAlignRight = xlwt.XFStyle()
styleAlignRight.alignment.horz = xlwt.Alignment.HORZ_RIGHT
rowTop = 0
if self.title:
for line in self.title.split("\n"):
sheet.write(rowTop, 0, line, titleStyle)
rowTop += 1
rowTop += 1
sheetFit = FitSheetWrapper(sheet)
# Write the colnames and data.
rowMax = 0
for col, c in enumerate(self.colnames):
isSpeed = c == _("Speed")
if isSpeed and self.data[col]:
try:
c = self.colnames[col] = self.data[col][0].split()[1]
except IndexError:
c = self.colnames[col] = ""
headerStyle = headerStyleAlignLeft if col in self.leftJustifyCols else headerStyleAlignRight
style = styleAlignLeft if col in self.leftJustifyCols else styleAlignRight
sheetFit.write(rowTop, col, c, headerStyle, bold=True)
for row, v in enumerate(self.data[col]):
if isSpeed and v:
v = (u"{}".format(v).split() or [""])[0]
if v == u'"':
v += u" "
rowCur = rowTop + 1 + row
if rowCur > rowMax:
rowMax = rowCur
sheetFit.write(rowCur, col, v, style)
if isSpeed:
self.colnames[col] = _("Speed")
if self.footer:
rowMax += 2
for line in self.footer.split("\n"):
sheet.write(rowMax, 0, line.strip(), styleAlignLeft)
rowMax += 1
# Add branding at the bottom of the sheet.
sheet.write(rowMax + 2, 0, self.brandText, styleAlignLeft)
def writeKOMGC(ws):
fit_sheet = FitSheetWrapper(ws)
riderFields = set(model.registration.getFieldsInUse())
headers = (['place', 'bib', 'last_name', 'first_name', 'team'] +
(['uci_id'] if 'uci_id' in riderFields else []) +
(['license'] if 'license' in riderFields else []) + [
'KOM Total', 'HC Wins', 'C1 Wins', 'C2 Wins', 'C3 Wins',
'C4 Wins', 'GC'
])
rowNum = 0
for c, h in enumerate(headers):
fit_sheet.write(rowNum, c, Utils.fieldToHeader(h), bold_format)
rowNum += 1
for place, r in enumerate(model.kom_gc, 1):
try:
rider = model.registration.bibToRider[r[-1]]
except KeyError:
continue
col = 0
fit_sheet.write(rowNum, col, unicode(place))
col += 1
fit_sheet.write(rowNum, col, unicode(rider.bib))
col += 1
fit_sheet.write(rowNum, col,
unicode(rider.last_name).upper())
col += 1
fit_sheet.write(rowNum, col, unicode(rider.first_name))
col += 1
fit_sheet.write(rowNum, col, unicode(rider.team))
col += 1
if 'uci_id' in riderFields:
fit_sheet.write(rowNum, col, unicode(rider.uci_id))
col += 1
if 'license' in riderFields:
fit_sheet.write(rowNum, col, unicode(rider.license))
col += 1
for v in r[:-1]:
if v:
fit_sheet.write(rowNum, col, v)
col += 1
rowNum += 1
def writeTeamClass( ws, stage ): fit_sheet = FitSheetWrapper( ws ) headers = ['Place', 'Team', 'Gap', 'Combined Times', 'Combined Places', 'Best Rider GC'] rowNum = 0 for c, h in enumerate(headers): fit_sheet.write( rowNum, c, h, bold_format ) rowNum +=1 for place, tc in enumerate(stage.team_classification, 1): col = 0 fit_sheet.write( rowNum, col, place ); col += 1 fit_sheet.write( rowNum, col, tc.team ); col += 1 fit_sheet.write( rowNum, col, tc.gap / (24.0*60.0*60.0), time_format ) col += 1 fit_sheet.write( rowNum, col, tc.sum_best_top_times.value / (24.0*60.0*60.0), time_format ) # ws.write_comment( rowNum, col, formatContext(tc.sum_best_top_times.context), comment_style ) col += 1 fit_sheet.write( rowNum, col, tc.sum_best_top_places.value ) # ws.write_comment( rowNum, col, formatContext(tc.sum_best_top_places.context), comment_style ) col += 1 fit_sheet.write( rowNum, col, tc.best_place.value ) # ws.write_comment( rowNum, col, formatContext(tc.best_place.context), comment_style ) col += 1 rowNum +=1
def onExportToExcel( self, event ):
model = SeriesModel.model
if Utils.mainWin:
if not Utils.mainWin.fileName:
Utils.MessageOK( self, 'You must save your Series to a file first.', 'Save Series' )
return
self.raceResults = model.extractAllRaceResults()
categoryNames = sorted( set(rr.categoryName for rr in self.raceResults) )
if not categoryNames:
return
pointsForRank = { r.fileName: r.pointStructure for r in model.races }
wb = xlwt.Workbook()
for categoryName in categoryNames:
results, races = GetModelInfo.GetCategoryResults( categoryName, self.raceResults, pointsForRank, model.numPlacesTieBreaker )
results = [rr for rr in results if rr[3] > 0]
headerNames = HeaderNames + [r[1] for r in races]
ws = wb.add_sheet( re.sub('[:\\/?*\[\]]', ' ', categoryName) )
wsFit = FitSheetWrapper( ws )
fnt = xlwt.Font()
fnt.name = 'Arial'
fnt.bold = True
fnt.height = int(fnt.height * 1.5)
headerStyle = xlwt.XFStyle()
headerStyle.font = fnt
rowCur = 0
ws.write_merge( rowCur, rowCur, 0, 8, model.name, headerStyle )
rowCur += 2
colCur = 0
ws.write_merge( rowCur, rowCur, colCur, colCur + 4, categoryName, xlwt.easyxf(
"font: name Arial, bold on;"
) );
rowCur += 2
for c, headerName in enumerate(headerNames):
wsFit.write( rowCur, c, headerName, labelStyle, bold = True )
rowCur += 1
for pos, (lastName, firstName, license, points, racePoints) in enumerate(results):
wsFit.write( rowCur, 0, pos+1, numberStyle )
wsFit.write( rowCur, 1, lastName, textStyle )
wsFit.write( rowCur, 2, firstName, textStyle )
wsFit.write( rowCur, 3, license, textStyle )
wsFit.write( rowCur, 4, points, numberStyle )
for q, (rPoints, rRank) in enumerate(racePoints):
wsFit.write( rowCur, 5 + q, '{} ({})'.format(rPoints, Utils.ordinal(rRank)) if rPoints else '', centerStyle )
rowCur += 1
# Add branding at the bottom of the sheet.
style = xlwt.XFStyle()
style.alignment.horz = xlwt.Alignment.HORZ_LEFT
ws.write( rowCur + 2, 0, brandText, style )
if Utils.mainWin:
xlfileName = os.path.splitext(Utils.mainWin.fileName)[0] + '.xls'
else:
xlfileName = 'ResultsTest.xls'
try:
wb.save( xlfileName )
webbrowser.open( xlfileName, new = 2, autoraise = True )
Utils.MessageOK(self, 'Excel file written to:\n\n %s' % xlfileName, 'Excel Write')
except IOError:
Utils.MessageOK(self,
'Cannot write "%s".\n\nCheck if this spreadsheet is open.\nIf so, close it, and try again.' % xlfileName,
'Excel File Error', iconMask=wx.ICON_ERROR )
def writeKOMGC( ws ): fit_sheet = FitSheetWrapper( ws ) riderFields = set( model.registration.getFieldsInUse() ) headers = ( ['place', 'bib', 'last_name', 'first_name', 'team'] + (['uci_id'] if 'uci_id' in riderFields else []) + (['license'] if 'license' in riderFields else []) + ['KOM Total', 'HC Wins', 'C1 Wins', 'C2 Wins', 'C3 Wins', 'C4 Wins', 'GC'] ) rowNum = 0 for c, h in enumerate(headers): fit_sheet.write( rowNum, c, Utils.fieldToHeader(h), bold_format ) rowNum +=1 for place, r in enumerate(model.kom_gc, 1): try: rider = model.registration.bibToRider[r[-1]] except KeyError: continue col = 0 fit_sheet.write( rowNum, col, unicode(place) ); col += 1 fit_sheet.write( rowNum, col, unicode(rider.bib) ); col += 1 fit_sheet.write( rowNum, col, unicode(rider.last_name).upper()); col += 1 fit_sheet.write( rowNum, col, unicode(rider.first_name) ); col += 1 fit_sheet.write( rowNum, col, unicode(rider.team) ); col += 1 if 'uci_id' in riderFields: fit_sheet.write( rowNum, col, unicode(rider.uci_id) ); col += 1 if 'license' in riderFields: fit_sheet.write( rowNum, col, unicode(rider.license) ); col += 1 for v in r[:-1]: if v: fit_sheet.write( rowNum, col, v ) col += 1 rowNum +=1
def UCIExport(sheet, cat):
race = Model.race
if not race:
return
SyncExcelLink(race)
sheetFit = FitSheetWrapper(sheet)
titleStyle = xlwt.XFStyle()
titleStyle.font.bold = True
leftAlignStyle = xlwt.XFStyle()
rightAlignStyle = xlwt.XFStyle()
rightAlignStyle.alignment.horz = xlwt.Alignment.HORZ_RIGHT
results = GetResults(cat)
def toInt(n):
try:
return int(n.split()[0])
except:
return n
row = 0
for col, field in enumerate(UCIFields):
sheetFit.write(row, col, field, titleStyle, bold=True)
row += 1
for rr in results:
try:
finishTime = formatTimeGap(
rr.lastTime -
rr.raceTimes[0]) if rr.status == Model.Rider.Finisher else ''
except Exception:
finishTime = ''
gap = getattr(rr, 'gap', '')
if reHighPrecision.match(gap):
gap = gap[:-4] + '"'
for col, field in enumerate(UCIFields):
{
'Pos':
lambda: sheetFit.write(row, col, toInt(rr.pos), rightAlignStyle
),
'Nr.':
lambda: sheetFit.write(row, col, rr.num, rightAlignStyle),
'Name':
lambda: sheetFit.write(row, col, rr.full_name(), leftAlignStyle
),
'Team':
lambda: sheetFit.write(row, col, getattr(rr, 'Team', ''),
leftAlignStyle),
'UCI Code':
lambda: sheetFit.write(row, col, getattr(rr, 'UCICode', ''),
leftAlignStyle),
'Time':
lambda: sheetFit.write(row, col, finishTime, rightAlignStyle),
'Gap':
lambda: sheetFit.write(row, col, gap, rightAlignStyle),
}[field]()
row += 1
def onPublishToExcel(self, event):
model = SeriesModel.model
scoreByPoints = model.scoreByPoints
scoreByTime = model.scoreByTime
scoreByPercent = model.scoreByPercent
scoreByTrueSkill = model.scoreByTrueSkill
HeaderNames = getHeaderNames()
if Utils.mainWin:
if not Utils.mainWin.fileName:
Utils.MessageOK(self,
'You must save your Series to a file first.',
'Save Series')
return
self.raceResults = model.extractAllRaceResults(False)
categoryNames = model.getCategoryNamesSortedTeamPublish()
if not categoryNames:
return
pointsForRank = {
r.getFileName(): r.pointStructure
for r in model.races
}
wb = xlwt.Workbook()
for categoryName in categoryNames:
results, races = GetModelInfo.GetCategoryResultsTeam(
categoryName,
self.raceResults,
pointsForRank,
useMostEventsCompleted=model.useMostEventsCompleted,
numPlacesTieBreaker=model.numPlacesTieBreaker,
)
results = [rr for rr in results if rr[1] > 0]
headerNames = HeaderNames + [r[1] for r in races]
ws = wb.add_sheet(re.sub('[:\\/?*\[\]]', ' ', categoryName))
wsFit = FitSheetWrapper(ws)
fnt = xlwt.Font()
fnt.name = 'Arial'
fnt.bold = True
fnt.height = int(fnt.height * 1.5)
headerStyle = xlwt.XFStyle()
headerStyle.font = fnt
rowCur = 0
ws.write_merge(rowCur, rowCur, 0, 8, model.name, headerStyle)
rowCur += 1
if model.organizer:
ws.write_merge(rowCur, rowCur, 0, 8,
u'by {}'.format(model.organizer), headerStyle)
rowCur += 1
rowCur += 1
colCur = 0
ws.write_merge(rowCur, rowCur, colCur, colCur + 4, categoryName,
xlwt.easyxf("font: name Arial, bold on;"))
rowCur += 2
for c, headerName in enumerate(headerNames):
wsFit.write(rowCur, c, headerName, labelStyle, bold=True)
rowCur += 1
for pos, (team, points, gap, rrs) in enumerate(results):
wsFit.write(rowCur, 0, pos + 1, numberStyle)
wsFit.write(rowCur, 1, team, textStyle)
wsFit.write(rowCur, 2, points, numberStyle)
wsFit.write(rowCur, 3, gap, numberStyle)
for q, rt in enumerate(rrs):
wsFit.write(rowCur, 4 + q,
formatTeamResults(scoreByPoints, rt),
centerStyle)
rowCur += 1
# Add branding at the bottom of the sheet.
style = xlwt.XFStyle()
style.alignment.horz = xlwt.Alignment.HORZ_LEFT
ws.write(rowCur + 2, 0, brandText, style)
if Utils.mainWin:
xlfileName = os.path.splitext(
Utils.mainWin.fileName)[0] + 'Team.xls'
else:
xlfileName = 'ResultsTestTeam.xls'
try:
wb.save(xlfileName)
webbrowser.open(xlfileName, new=2, autoraise=True)
Utils.MessageOK(
self, 'Excel file written to:\n\n {}'.format(xlfileName),
'Excel Write')
self.callPostPublishCmd(xlfileName)
except IOError:
Utils.MessageOK(
self,
'Cannot write "{}".\n\nCheck if this spreadsheet is open.\nIf so, close it, and try again.'
.format(xlfileName),
'Excel File Error',
iconMask=wx.ICON_ERROR)
def writeTeamClass(ws, stage):
fit_sheet = FitSheetWrapper(ws)
headers = [
'Place', 'Team', 'Gap', 'Combined Times', 'Combined Places',
'Best Rider GC'
]
rowNum = 0
for c, h in enumerate(headers):
fit_sheet.write(rowNum, c, h, bold_format)
rowNum += 1
for place, tc in enumerate(stage.team_classification, 1):
col = 0
fit_sheet.write(rowNum, col, place)
col += 1
fit_sheet.write(rowNum, col, tc.team)
col += 1
fit_sheet.write(rowNum, col, tc.gap / (24.0 * 60.0 * 60.0),
time_format)
col += 1
fit_sheet.write(rowNum, col,
tc.sum_best_top_times.value / (24.0 * 60.0 * 60.0),
time_format)
# ws.write_comment( rowNum, col, formatContext(tc.sum_best_top_times.context), comment_style )
col += 1
fit_sheet.write(rowNum, col, tc.sum_best_top_places.value)
# ws.write_comment( rowNum, col, formatContext(tc.sum_best_top_places.context), comment_style )
col += 1
fit_sheet.write(rowNum, col, tc.best_place.value)
# ws.write_comment( rowNum, col, formatContext(tc.best_place.context), comment_style )
col += 1
rowNum += 1
def CallupResultsToExcel( fname_excel, registration_headers, callup_headers, callup_results, is_callup=True, top_riders=999999, exclude_unranked=False ):
callup_results = callup_results[:top_riders]
if exclude_unranked:
callup_results = [r for r in callup_results if any(r[k] for k in range(len(registration_headers), len(callup_headers)))]
if not is_callup:
callup_results = reversed( callup_results )
wb = xlsxwriter.Workbook( fname_excel )
ws = wb.add_worksheet('Callups' if is_callup is True else 'Seeding')
fit_sheet = FitSheetWrapper( ws )
bold_format = wb.add_format( {'bold': True} )
date_format = wb.add_format( {'num_format': 'yyyy/mm/dd'} )
rowNum = 0
last_name_col = None
uci_id_col = None
ignore_headers = set(['age'])
for col, v in enumerate(callup_headers):
if v == 'last_name':
last_name_col = col
elif v == 'uci_id':
uci_id_col = col
header_col = {}
col_cur = 0
for v in callup_headers:
if v in ignore_headers:
continue
header_col[v] = col_cur
col_cur += 1
for v in callup_headers:
if v in ignore_headers:
continue
fit_sheet.write( rowNum, header_col[v], make_title(v), bold_format, bold=True )
rowNum += 1
for row in callup_results:
for c, value in enumerate(row):
if callup_headers[c] in ignore_headers:
continue
try:
v = value.get_value()
findResult = value
except AttributeError:
v = value
findResult = None
col = header_col[callup_headers[c]]
if isinstance(v, datetime.date):
fit_sheet.write( rowNum, col, v, date_format )
else:
if c == last_name_col:
v = u'{}'.format(v).upper()
elif c == uci_id_col:
v = Model.format_uci_id( u'{}'.format(v) )
fit_sheet.write( rowNum, col, v )
if findResult and findResult.get_status() != findResult.NoMatch:
ws.write_comment( rowNum, col, findResult.get_message(), {'width': 200, 'height': 200} )
rowNum += 1
wb.close()
def writeIC(ws, stage):
fit_sheet = FitSheetWrapper(ws)
ic_fields = ['gap'] + list(
Model.IndividualClassification._fields[1:-1])
riderFields = set(model.registration.getFieldsInUse())
headers = (['Place', 'Bib', 'Last Name', 'First Name', 'Team'] +
(['UCI ID'] if 'uci_id' in riderFields else []) +
(['License'] if 'license' in riderFields else []) +
[Utils.fieldToHeader(h) for h in ic_fields])
rowNum = 0
for c, h in enumerate(headers):
fit_sheet.write(rowNum, c, h, bold_format)
rowNum += 1
for place, r in enumerate(stage.individual_gc, 1):
try:
rider = model.registration.bibToRider[r.bib]
except KeyError:
continue
col = 0
if r.retired_stage > 0:
fit_sheet.write(rowNum, col, 'AB')
col += 1
else:
fit_sheet.write(rowNum, col, place)
col += 1
fit_sheet.write(rowNum, col, r.bib)
col += 1
fit_sheet.write(rowNum, col, rider.last_name.upper())
col += 1
fit_sheet.write(rowNum, col, rider.first_name)
col += 1
fit_sheet.write(rowNum, col, rider.team)
col += 1
if 'uci_id' in riderFields:
fit_sheet.write(rowNum, col, rider.uci_id)
col += 1
if 'license' in riderFields:
fit_sheet.write(rowNum, col, rider.license)
col += 1
if r.retired_stage == 0:
fit_sheet.write(rowNum, col, r.gap / (24.0 * 60.0 * 60.0),
time_format)
col += 1
fit_sheet.write(
rowNum, col, r.total_time_with_bonus_plus_penalty /
(24.0 * 60.0 * 60.0), time_format)
col += 1
fit_sheet.write(
rowNum, col,
r.total_time_with_bonus_plus_penalty_plus_second_fraction /
(24.0 * 60.0 * 60.0), high_precision_time_format)
col += 1
fit_sheet.write(rowNum, col, r.last_stage_place)
col += 1
rowNum += 1
def toExcelSheet(self, sheet):
''' Write the contents of the grid to an xlwt excel sheet. '''
titleStyle = xlwt.XFStyle()
titleStyle.font.bold = True
titleStyle.font.height += titleStyle.font.height / 2
headerStyleAlignLeft = xlwt.XFStyle()
headerStyleAlignLeft.borders.bottom = xlwt.Borders.MEDIUM
headerStyleAlignLeft.font.bold = True
headerStyleAlignLeft.alignment.horz = xlwt.Alignment.HORZ_LEFT
headerStyleAlignLeft.alignment.wrap = xlwt.Alignment.WRAP_AT_RIGHT
headerStyleAlignRight = xlwt.XFStyle()
headerStyleAlignRight.borders.bottom = xlwt.Borders.MEDIUM
headerStyleAlignRight.font.bold = True
headerStyleAlignRight.alignment.horz = xlwt.Alignment.HORZ_RIGHT
headerStyleAlignRight.alignment.wrap = xlwt.Alignment.WRAP_AT_RIGHT
styleAlignLeft = xlwt.XFStyle()
styleAlignLeft.alignment.horz = xlwt.Alignment.HORZ_LEFT
styleAlignRight = xlwt.XFStyle()
styleAlignRight.alignment.horz = xlwt.Alignment.HORZ_RIGHT
rowTop = 0
if self.title:
for line in self.title.split('\n'):
sheet.write(rowTop, 0, line, titleStyle)
rowTop += 1
rowTop += 1
sheetFit = FitSheetWrapper(sheet)
# Write the colnames and data.
rowMax = 0
for col, c in enumerate(self.colnames):
isSpeed = (c == _('Speed'))
if isSpeed and self.data[col]:
try:
c = self.colnames[col] = self.data[col][0].split()[1]
except IndexError:
c = self.colnames[col] = ''
headerStyle = headerStyleAlignLeft if col in self.leftJustifyCols else headerStyleAlignRight
style = styleAlignLeft if col in self.leftJustifyCols else styleAlignRight
sheetFit.write(rowTop, col, c, headerStyle, bold=True)
for row, v in enumerate(self.data[col]):
if isSpeed and v:
v = (u'{}'.format(v).split() or [''])[0]
if v == u'"':
v += u' '
rowCur = rowTop + 1 + row
if rowCur > rowMax:
rowMax = rowCur
sheetFit.write(rowCur, col, v, style)
if isSpeed:
self.colnames[col] = _('Speed')
if self.footer:
rowMax += 2
for line in self.footer.split('\n'):
sheet.write(rowMax, 0, line.strip(), styleAlignLeft)
rowMax += 1
# Add branding at the bottom of the sheet.
sheet.write(rowMax + 2, 0, self.brandText, styleAlignLeft)
def writeIC( ws, stage ): fit_sheet = FitSheetWrapper( ws ) ic_fields = ['gap'] + list(Model.IndividualClassification._fields[1:-1]) riderFields = set( model.registration.getFieldsInUse() ) headers = ( ['Place', 'Bib', 'Last Name', 'First Name', 'Team'] + (['UCI ID'] if 'uci_id' in riderFields else []) + (['License'] if 'license' in riderFields else []) + [Utils.fieldToHeader(h) for h in ic_fields] ) rowNum = 0 for c, h in enumerate(headers): fit_sheet.write( rowNum, c, h, bold_format ) rowNum +=1 for place, r in enumerate(stage.individual_gc, 1): try: rider = model.registration.bibToRider[r.bib] except KeyError: continue col = 0 if r.retired_stage > 0: fit_sheet.write( rowNum, col, 'AB' ); col += 1 else: fit_sheet.write( rowNum, col, place ); col += 1 fit_sheet.write( rowNum, col, r.bib ); col += 1 fit_sheet.write( rowNum, col, rider.last_name.upper() ); col += 1 fit_sheet.write( rowNum, col, rider.first_name ); col += 1 fit_sheet.write( rowNum, col, rider.team ); col += 1 if 'uci_id' in riderFields: fit_sheet.write( rowNum, col, rider.uci_id ); col += 1 if 'license' in riderFields: fit_sheet.write( rowNum, col, rider.license ); col += 1 if r.retired_stage == 0: fit_sheet.write( rowNum, col, r.gap / (24.0*60.0*60.0), time_format ); col += 1 fit_sheet.write( rowNum, col, r.total_time_with_bonus_plus_penalty / (24.0*60.0*60.0), time_format ); col += 1 fit_sheet.write( rowNum, col, r.total_time_with_bonus_plus_penalty_plus_second_fraction / (24.0*60.0*60.0), high_precision_time_format ); col += 1 fit_sheet.write( rowNum, col, r.last_stage_place ); col += 1 rowNum +=1
def USACExport( workbook, sheet ):
race = Model.race
if not race:
return
SyncExcelLink( race )
raceDiscipline = getattr( race, 'discipline', 'Cyclo-cross' )
# Correct for USAC's picky naming.
if 'cyclo' in raceDiscipline.lower():
raceDiscipline = 'Cyclo-cross'
elif 'road' in raceDiscipline.lower():
raceDiscipline = 'Road Race'
sheetFit = FitSheetWrapper( sheet )
titleStyle = xlwt.easyxf('font: bold on')
leftAlignStyle = xlwt.easyxf()
rightAlignStyle = xlwt.easyxf('align: horiz right')
catDetails = dict( (cd['name'], cd) for cd in GetCategoryDetails() )
hasDistance = None
maxLaps = 0
publishCategories = race.getCategories( startWaveOnly = False, uploadOnly = True )
for cat in publishCategories:
results = GetResults( cat )
if not results:
continue
cd = catDetails[cat.fullname]
if cd.get('raceDistance', None):
hasDistance = True
maxLaps = max( maxLaps, max(rr.laps for rr in results) )
if maxLaps == 1 or maxLaps > 99:
maxLaps = 0
lapTimeStartCol = (2 if hasDistance else 0) + lenUSACFields
year, month, day = race.date.split( '-' )
raceDate = datetime.date( year = int(year), month = int(month), day = int(day) ).strftime( '%m/%d/%Y' )
row = 0
for cat in publishCategories:
results = GetResults( cat )
if not results:
continue
raceGender = getattr(cat, 'gender', 'Open')
if raceGender == 'Open':
raceGender = 'All'
cd = catDetails[cat.fullname]
raceDistance = cd.get('raceDistance', '')
raceDistanceType = cd.get('distanceUnit', '')
for rr in results:
if row == 0:
for col, field in enumerate(USACFields):
sheetFit.write( row, col, field, titleStyle, bold=True )
if hasDistance:
sheetFit.write( row, lenUSACFields , 'Race Distance', titleStyle, bold=True )
sheetFit.write( row, lenUSACFields+1, 'Race Distance Type', titleStyle, bold=True )
for i in six.moves.range(maxLaps):
sheetFit.write( row, lapTimeStartCol + i, 'Rider Lap {}'.format(i + 1), titleStyle, bold=True )
row += 1
try:
finishTime = formatTimeGap(rr.lastTime - rr.raceTimes[0]) if rr.status == Model.Rider.Finisher else ''
except Exception as e:
finishTime = ''
for col, field in enumerate(USACFields):
{
'Race Date': lambda : sheet.write( row, col, raceDate, rightAlignStyle ),
'Race Gender': lambda : sheetFit.write( row, col, raceGender, leftAlignStyle ),
'Race Discipline': lambda : sheetFit.write( row, col, raceDiscipline, leftAlignStyle ),
'Race Category': lambda : sheetFit.write( row, col, cat.name, leftAlignStyle ),
'Rider Bib #': lambda : sheetFit.write( row, col, rr.num, rightAlignStyle ),
'Rider Last Name': lambda : sheetFit.write( row, col, getattr(rr, 'LastName', ''), leftAlignStyle ),
'Rider First Name': lambda : sheetFit.write( row, col, getattr(rr, 'FirstName', ''), leftAlignStyle ),
'Rider Team': lambda : sheetFit.write( row, col, getattr(rr, 'Team', ''), leftAlignStyle ),
'Rider License #': lambda : sheetFit.write( row, col, getattr(rr, 'License', ''), leftAlignStyle ),
'Rider Place': lambda : sheetFit.write( row, col, 'DNP' if rr.pos in {'NP', 'OTL', 'PUL'} else toInt(rr.pos), rightAlignStyle ),
'Rider Time': lambda : sheetFit.write( row, col, finishTime, rightAlignStyle ),
}[field]()
if hasDistance:
sheetFit.write( row, lenUSACFields , raceDistance, rightAlignStyle )
sheetFit.write( row, lenUSACFields+1, raceDistanceType, rightAlignStyle )
if maxLaps:
for i, lapTime in enumerate(rr.lapTimes):
sheetFit.write( row, lapTimeStartCol + i, formatTimeGap(lapTime), rightAlignStyle )
row += 1
def writeTeamGC(ws):
fit_sheet = FitSheetWrapper(ws)
headers = (['Place', 'Team', 'Gap', 'Combined Time'] + [
'# {}s'.format(Utils.ordinal(i + 1))
for i in xrange(len(model.all_teams))
] + ['Best Rider GC'])
rowNum = 0
for c, h in enumerate(headers):
fit_sheet.write(rowNum, c, h, bold_format)
rowNum += 1
leaderTime = None
for place, tgc in enumerate(model.team_gc, 1):
col = 0
fit_sheet.write(rowNum, col, place)
col += 1
fit_sheet.write(rowNum, col, tgc[-1])
col += 1
timeCur = tgc[0].value
if leaderTime is None:
leaderTime = timeCur
gap = timeCur - leaderTime
fit_sheet.write(rowNum, col, gap / (24.0 * 60.0 * 60.0),
time_format)
col += 1
fit_sheet.write(rowNum, col, timeCur / (24.0 * 60.0 * 60.0),
time_format)
# ws.write_comment( rowNum, col, formatContextList(tgc[0].context), wide_comment_style )
col += 1
for i in xrange(1, len(tgc) - 2):
if tgc[i].value:
fit_sheet.write(rowNum, col, tgc[i].value)
# ws.write_comment( rowNum, col, u'\n'.join(tgc[i].context), narrow_comment_style )
col += 1
fit_sheet.write(rowNum, col, tgc[-2].value)
# ws.write_comment( rowNum, col, formatContext(tgc[-2].context), comment_style )
col += 1
rowNum += 1
for team in model.unranked_teams:
col = 0
fit_sheet.write(rowNum, col, 'DNF')
col += 1
fit_sheet.write(rowNum, col, team)
col += 1
rowNum += 1
def USACExport( sheet ):
race = Model.race
if not race:
return
raceDiscipline = getattr( race, 'discipline', 'Cyclo-cross' )
if raceDiscipline.lower() in 'cyclocross':
raceDiscipline = 'Cyclo-cross'
sheetFit = FitSheetWrapper( sheet )
titleStyle = xlwt.XFStyle()
titleStyle.font.bold = True
leftAlignStyle = xlwt.XFStyle()
rightAlignStyle = xlwt.XFStyle()
rightAlignStyle.alignment.horz = xlwt.Alignment.HORZ_RIGHT
catDetails = dict( (cd['name'], cd) for cd in GetCategoryDetails() )
hasDistance = None
maxLaps = 0
publishCategories = race.getCategories( startWaveOnly = False, uploadOnly = True )
for cat in publishCategories:
results = GetResults( cat, True )
if not results:
continue
cd = catDetails[cat.fullname]
if cd.get('raceDistance', None):
hasDistance = True
for rr in results:
maxLaps = max( maxLaps, rr.laps )
if maxLaps == 1 or maxLaps > 99:
maxLaps = 0
lapTimeStartCol = (2 if hasDistance else 0) + lenUSACFields
def toInt( n ):
try:
return int(n)
except:
return n
year, month, day = race.date.split( '-' )
raceDate = datetime.date( year = int(year), month = int(month), day = int(day) ).strftime( '%m/%d/%Y' )
row = 0
for cat in publishCategories:
results = GetResults( cat, True )
if not results:
continue
raceGender = getattr(cat, 'gender', 'Open')
if raceGender == 'Open':
raceGender = 'All'
cd = catDetails[cat.fullname]
raceDistance = cd.get('raceDistance', '')
raceDistanceType = cd.get('distanceUnit', '')
for rr in results:
if row == 0:
for col, field in enumerate(USACFields):
sheetFit.write( row, col, field, titleStyle, bold=True )
if hasDistance:
sheetFit.write( row, lenUSACFields , 'Race Distance', titleStyle, bold=True )
sheetFit.write( row, lenUSACFields+1, 'Race Distance Type', titleStyle, bold=True )
for i in xrange(maxLaps):
sheetFit.write( row, lapTimeStartCol + i, 'Rider Lap {}'.format(i + 1), titleStyle, bold=True )
row += 1
for col, field in enumerate(USACFields):
{
'Race Date': lambda : sheet.write( row, col, raceDate, rightAlignStyle ),
'Race Gender': lambda : sheetFit.write( row, col, raceGender, leftAlignStyle ),
'Race Discipline': lambda : sheetFit.write( row, col, raceDiscipline, leftAlignStyle ),
'Race Category': lambda : sheetFit.write( row, col, cat.name, leftAlignStyle ),
'Rider Bib #': lambda : sheetFit.write( row, col, rr.num, rightAlignStyle ),
'Rider Last Name': lambda : sheetFit.write( row, col, getattr(rr, 'LastName', ''), leftAlignStyle ),
'Rider First Name': lambda : sheetFit.write( row, col, getattr(rr, 'FirstName', ''), leftAlignStyle ),
'Rider Team': lambda : sheetFit.write( row, col, getattr(rr, 'Team', ''), leftAlignStyle ),
'Rider License #': lambda : sheetFit.write( row, col, getattr(rr, 'License', ''), leftAlignStyle ),
'Rider Place': lambda : sheetFit.write( row, col, 'DNP' if rr.pos in {'NP', 'OTL', 'PUL'} else toInt(rr.pos), rightAlignStyle ),
'Rider Time': lambda : sheetFit.write( row, col, Utils.formatTime(rr.lastTime) if rr.lastTime else '', rightAlignStyle ),
}[field]()
if hasDistance:
sheetFit.write( row, lenUSACFields , raceDistance, rightAlignStyle )
sheetFit.write( row, lenUSACFields+1, raceDistanceType, rightAlignStyle )
if maxLaps:
for i, lapTime in enumerate(rr.lapTimes):
sheetFit.write( row, lapTimeStartCol + i, Utils.formatTime(lapTime), rightAlignStyle )
row += 1
def USACExport(workbook, sheet):
race = Model.race
if not race:
return
SyncExcelLink(race)
raceDiscipline = getattr(race, 'discipline', 'Cyclo-cross')
# Correct for USAC's picky naming.
if 'cyclo' in raceDiscipline.lower():
raceDiscipline = 'Cyclo-cross'
elif 'road' in raceDiscipline.lower():
raceDiscipline = 'Road Race'
sheetFit = FitSheetWrapper(sheet)
titleStyle = xlwt.easyxf('font: bold on')
leftAlignStyle = xlwt.easyxf()
rightAlignStyle = xlwt.easyxf('align: horiz right')
catDetails = dict((cd['name'], cd) for cd in GetCategoryDetails())
hasDistance = None
maxLaps = 0
publishCategories = race.getCategories(startWaveOnly=False,
uploadOnly=True)
for cat in publishCategories:
results = GetResults(cat)
if not results:
continue
cd = catDetails[cat.fullname]
if cd.get('raceDistance', None):
hasDistance = True
maxLaps = max(maxLaps, max(rr.laps for rr in results))
if maxLaps == 1 or maxLaps > 99:
maxLaps = 0
lapTimeStartCol = (2 if hasDistance else 0) + lenUSACFields
year, month, day = race.date.split('-')
raceDate = datetime.date(year=int(year), month=int(month),
day=int(day)).strftime('%m/%d/%Y')
row = 0
for cat in publishCategories:
results = GetResults(cat)
if not results:
continue
raceGender = getattr(cat, 'gender', 'Open')
if raceGender == 'Open':
raceGender = 'All'
cd = catDetails[cat.fullname]
raceDistance = cd.get('raceDistance', '')
raceDistanceType = cd.get('distanceUnit', '')
for rr in results:
if row == 0:
for col, field in enumerate(USACFields):
sheetFit.write(row, col, field, titleStyle, bold=True)
if hasDistance:
sheetFit.write(row,
lenUSACFields,
'Race Distance',
titleStyle,
bold=True)
sheetFit.write(row,
lenUSACFields + 1,
'Race Distance Type',
titleStyle,
bold=True)
for i in range(maxLaps):
sheetFit.write(row,
lapTimeStartCol + i,
'Rider Lap {}'.format(i + 1),
titleStyle,
bold=True)
row += 1
try:
finishTime = formatTimeGap(
rr.lastTime - rr.raceTimes[0]
) if rr.status == Model.Rider.Finisher else ''
except Exception as e:
finishTime = ''
for col, field in enumerate(USACFields):
{
'Race Date':
lambda: sheet.write(row, col, raceDate, rightAlignStyle),
'Race Gender':
lambda: sheetFit.write(row, col, raceGender, leftAlignStyle
),
'Race Discipline':
lambda: sheetFit.write(row, col, raceDiscipline,
leftAlignStyle),
'Race Category':
lambda: sheetFit.write(row, col, cat.name, leftAlignStyle),
'Rider Bib #':
lambda: sheetFit.write(row, col, rr.num, rightAlignStyle),
'Rider Last Name':
lambda: sheetFit.write(row, col, getattr(
rr, 'LastName', ''), leftAlignStyle),
'Rider First Name':
lambda: sheetFit.write(row, col,
getattr(rr, 'FirstName', ''),
leftAlignStyle),
'Rider Team':
lambda: sheetFit.write(row, col, getattr(rr, 'Team', ''),
leftAlignStyle),
'Rider License #':
lambda: sheetFit.write(row, col, getattr(
rr, 'License', ''), leftAlignStyle),
'Rider Place':
lambda: sheetFit.write(
row, col, 'DNP' if rr.pos in {'NP', 'OTL', 'PUL'} else
toInt(rr.pos), rightAlignStyle),
'Rider Time':
lambda: sheetFit.write(row, col, finishTime,
rightAlignStyle),
}[field]()
if hasDistance:
sheetFit.write(row, lenUSACFields, raceDistance,
rightAlignStyle)
sheetFit.write(row, lenUSACFields + 1, raceDistanceType,
rightAlignStyle)
if maxLaps:
for i, lapTime in enumerate(rr.lapTimes):
sheetFit.write(row, lapTimeStartCol + i,
formatTimeGap(lapTime), rightAlignStyle)
row += 1
def onPublishToExcel( self, event ):
model = SeriesModel.model
scoreByPoints = model.scoreByPoints
scoreByTime = model.scoreByTime
scoreByPercent = model.scoreByPercent
scoreByTrueSkill = model.scoreByTrueSkill
HeaderNames = getHeaderNames()
if Utils.mainWin:
if not Utils.mainWin.fileName:
Utils.MessageOK( self, 'You must save your Series to a file first.', 'Save Series' )
return
self.raceResults = model.extractAllRaceResults( adjustForUpgrades=False, isIndividual=False )
categoryNames = model.getCategoryNamesSortedTeamPublish()
if not categoryNames:
return
pointsForRank = { r.getFileName(): r.pointStructure for r in model.races }
teamPointsForRank = { r.getFileName(): r.teamPointStructure for r in model.races }
wb = xlwt.Workbook()
for categoryName in categoryNames:
results, races = GetModelInfo.GetCategoryResultsTeam(
categoryName,
self.raceResults,
pointsForRank,
teamPointsForRank,
useMostEventsCompleted=model.useMostEventsCompleted,
numPlacesTieBreaker=model.numPlacesTieBreaker,
)
results = [rr for rr in results if rr[1] > 0]
headerNames = HeaderNames + [r[1] for r in races]
ws = wb.add_sheet( re.sub('[:\\/?*\[\]]', ' ', categoryName) )
wsFit = FitSheetWrapper( ws )
fnt = xlwt.Font()
fnt.name = 'Arial'
fnt.bold = True
fnt.height = int(fnt.height * 1.5)
headerStyle = xlwt.XFStyle()
headerStyle.font = fnt
rowCur = 0
ws.write_merge( rowCur, rowCur, 0, 8, model.name, headerStyle )
rowCur += 1
if model.organizer:
ws.write_merge( rowCur, rowCur, 0, 8, u'by {}'.format(model.organizer), headerStyle )
rowCur += 1
rowCur += 1
colCur = 0
ws.write_merge( rowCur, rowCur, colCur, colCur + 4, categoryName, xlwt.easyxf(
"font: name Arial, bold on;"
) );
rowCur += 2
for c, headerName in enumerate(headerNames):
wsFit.write( rowCur, c, headerName, labelStyle, bold = True )
rowCur += 1
for pos, (team, points, gap, rrs) in enumerate(results):
wsFit.write( rowCur, 0, pos+1, numberStyle )
wsFit.write( rowCur, 1, team, textStyle )
wsFit.write( rowCur, 2, points, numberStyle )
wsFit.write( rowCur, 3, gap, numberStyle )
for q, rt in enumerate(rrs):
wsFit.write( rowCur, 4 + q, formatTeamResults(scoreByPoints, rt), centerStyle )
rowCur += 1
# Add branding at the bottom of the sheet.
style = xlwt.XFStyle()
style.alignment.horz = xlwt.Alignment.HORZ_LEFT
ws.write( rowCur + 2, 0, brandText, style )
if Utils.mainWin:
xlfileName = os.path.splitext(Utils.mainWin.fileName)[0] + 'Team.xls'
else:
xlfileName = 'ResultsTestTeam.xls'
try:
wb.save( xlfileName )
webbrowser.open( xlfileName, new = 2, autoraise = True )
Utils.MessageOK(self, 'Excel file written to:\n\n {}'.format(xlfileName), 'Excel Write')
self.callPostPublishCmd( xlfileName )
except IOError:
Utils.MessageOK(self,
'Cannot write "{}".\n\nCheck if this spreadsheet is open.\nIf so, close it, and try again.'.format(xlfileName),
'Excel File Error', iconMask=wx.ICON_ERROR )
def writeTeamGC( ws ):
fit_sheet = FitSheetWrapper( ws )
headers = (
['Place', 'Team', 'Gap', 'Combined Time'] +
['# {}s'.format(Utils.ordinal(i+1)) for i in xrange(len(model.all_teams))] +
['Best Rider GC']
)
rowNum = 0
for c, h in enumerate(headers):
fit_sheet.write( rowNum, c, h, bold_format )
rowNum +=1
leaderTime = None
for place, tgc in enumerate(model.team_gc, 1):
col = 0
fit_sheet.write( rowNum, col, place ); col += 1
fit_sheet.write( rowNum, col, tgc[-1] ); col += 1
timeCur = tgc[0].value
if leaderTime is None:
leaderTime = timeCur
gap = timeCur - leaderTime
fit_sheet.write( rowNum, col, gap / (24.0*60.0*60.0), time_format )
col += 1
fit_sheet.write( rowNum, col, timeCur / (24.0*60.0*60.0), time_format )
# ws.write_comment( rowNum, col, formatContextList(tgc[0].context), wide_comment_style )
col += 1
for i in xrange(1, len(tgc)-2):
if tgc[i].value:
fit_sheet.write( rowNum, col, tgc[i].value )
# ws.write_comment( rowNum, col, u'\n'.join(tgc[i].context), narrow_comment_style )
col += 1
fit_sheet.write( rowNum, col, tgc[-2].value )
# ws.write_comment( rowNum, col, formatContext(tgc[-2].context), comment_style )
col += 1
rowNum +=1
for team in model.unranked_teams:
col = 0
fit_sheet.write( rowNum, col, 'DNF' ); col += 1
fit_sheet.write( rowNum, col, team ); col += 1
rowNum +=1
