def toExcelSheetXLSX( self, formats, sheet ):
''' Write the contents of the grid to an xlwt excel sheet. '''
titleStyle = formats['titleStyle']
headerStyleAlignLeft = formats['headerStyleAlignLeft']
headerStyleAlignRight = formats['headerStyleAlignRight']
styleAlignLeft = formats['styleAlignLeft']
styleAlignRight = formats['styleAlignRight']
rowTop = 0
if self.title:
for line in self.title.split('\n'):
sheet.write(rowTop, 0, line, titleStyle)
rowTop += 1
rowTop += 1
sheetFit = FitSheetWrapperXLSX( 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 toExcelSheetXLSX(self, formats, sheet):
''' Write the contents of the grid to an xlwt excel sheet. '''
titleStyle = formats['titleStyle']
headerStyleAlignLeft = formats['headerStyleAlignLeft']
headerStyleAlignRight = formats['headerStyleAlignRight']
styleAlignLeft = formats['styleAlignLeft']
styleAlignRight = formats['styleAlignRight']
rowTop = 0
if self.title:
for line in self.title.split('\n'):
sheet.write(rowTop, 0, line, titleStyle)
rowTop += 1
rowTop += 1
sheetFit = FitSheetWrapperXLSX(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 JPResultsExport(workbook, sheet):
race = Model.race
if not race:
return
SyncExcelLink(race)
sheetFit = FitSheetWrapperXLSX(sheet)
titleStyle = workbook.add_format({'bold': True})
leftAlignStyle = workbook.add_format()
rightAlignStyle = workbook.add_format({'align': 'right'})
timeStyle = workbook.add_format({
'num_format': 'hh:mm:ss',
'align': 'right'
})
Finisher = Model.Rider.Finisher
row = 0
for cat in race.getCategories(startWaveOnly=False, uploadOnly=True):
results = GetResults(cat)
if not results:
continue
gender = None
for rr in results:
if row == 0:
for col, field in enumerate(JPResultFields):
sheetFit.write(row, col, field, titleStyle, bold=True)
row += 1
try:
finishTime = (
rr.lastTime -
rr.raceTimes[0]) if rr.status == Finisher else None
if race.roadRaceFinishTimes:
finishTime = floor(finishTime)[
0] # Truncate decimal seconds.
finishTime /= (24.0 * 60.0 * 60.0
) # Convert to fraction of a day.
except Exception as e:
finishTime = None
valueStyle = {
'body_number': (rr.num, rightAlignStyle),
'racer_code': (getattr(rr, 'License', None), leftAlignStyle),
'family_name': (getattr(rr, 'LastName', None), leftAlignStyle),
'first_name': (getattr(rr, 'FirstName', None), leftAlignStyle),
'team': (getattr(rr, 'Team', None), leftAlignStyle),
'uci_id': (formatUciId(getattr(rr, 'UCIID', None)) if hasattr(
rr, 'UCIID') else None, leftAlignStyle),
'gender': (gender, leftAlignStyle),
'birth_date': (getattr(rr, 'DateOfBirth',
None), leftAlignStyle),
'entry_status': (getattr(rr, 'EntryStatus',
None), leftAlignStyle),
'rank':
(rr.pos if rr.status == Finisher else None, rightAlignStyle),
'result_status': (getStatusName(rr.status), leftAlignStyle),
'lap':
(rr.laps if rr.status == Finisher else None, rightAlignStyle),
'goal_time':
(finishTime if rr.status == Finisher else None, timeStyle),
'category_code': (cat.name, leftAlignStyle),
}
for col, field in enumerate(JPResultFields):
value, style = valueStyle[field]
if value is not None:
sheetFit.write(row, col, value, style)
row += 1
def VTTAExport( workbook, sheet ):
race = Model.race
if not race:
return
SyncExcelLink( race )
raceDiscipline = getattr( race, 'discipline', 'Cyclo-cross' )
if 'cyclo' in raceDiscipline.lower():
raceDiscipline = 'Cyclo-cross'
elif 'road' in raceDiscipline.lower():
raceDiscipline = 'Road Race'
if race.isTimeTrial:
raceDiscipline = 'Time Trial'
sheetFit = FitSheetWrapperXLSX( sheet )
titleStyle = workbook.add_format({'bold': True})
leftAlignStyle = workbook.add_format()
rightAlignStyle = workbook.add_format({'align': '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) + lenVTTAFields
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(VTTAFields):
sheetFit.write( row, col, field, titleStyle, bold=True )
if hasDistance:
sheetFit.write( row, lenVTTAFields , 'Race Distance', titleStyle, bold=True )
sheetFit.write( row, lenVTTAFields+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(VTTAFields):
{
'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 Age': lambda : sheetFit.write( row, col, getattr(rr, 'Age', ''), rightAlignStyle ),
'Rider City': lambda : sheetFit.write( row, col, getattr(rr, 'City', ''), leftAlignStyle ),
'Rider StateProv': lambda : sheetFit.write( row, col,
getattr(rr, 'StateProv', '') or getattr(rr, 'Prov', '') or getattr(rr, 'State', ''),
leftAlignStyle ),
'Rider Nat.': lambda : sheetFit.write( row, col, getattr(rr, 'Nat.', ''), leftAlignStyle ),
'Rider Team': lambda : sheetFit.write( row, col, getattr(rr, 'Team', ''), leftAlignStyle ),
'Rider License #': lambda : sheetFit.write( row, col, getattr(rr, 'License', ''), leftAlignStyle ),
'Rider UCICode': lambda : sheetFit.write( row, col, getattr(rr, 'UCICode', ''), 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, lenVTTAFields , raceDistance, rightAlignStyle )
sheetFit.write( row, lenVTTAFields+1, raceDistanceType, rightAlignStyle )
if maxLaps:
for i, lapTime in enumerate(rr.lapTimes):
sheetFit.write( row, lapTimeStartCol + i, formatTimeGap(lapTime), rightAlignStyle )
row += 1