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 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 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 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 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 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 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 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 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 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 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 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()