async def sheet_raise_dtl(title: str, sheet: Worksheet): sheet.title = title field_title = [ ('序号', 6), # 5.38 ('发布批次', 18), # 17.38 ('发布人', 7.5), # 6.88 ('发布日期', 12), # 11.38 ('发布时间', 12), # 11.38 ('数量', 7), # 6.38 ('复核人', 7.5), # 6.88 ] row = IncrementCtrl(1) set_large_title(sheet, row.now, 1, len(field_title), title) set_export_time(sheet, row.next, 1, len(field_title)) set_field_title(sheet, row.next, field_title) index = IncrementCtrl(0) ticket_batch: TicketBatch async for ticket_batch in TicketBatch.find({}).sort([('raise_time', pymongo.DESCENDING)]): raiser = await User.find_one({'_id': ticket_batch['raiser']}) raiser_init = await UserInit.find_one_by_user(raiser) checker = await User.find_one({'_id': ticket_batch['checker']}) checker_init = await UserInit.find_one_by_user(checker) set_array_content(sheet, row.next, [ index.next, # 序号 ticket_batch.json_id, # 发布批次 raiser_init['real_name'], # 发布人 ticket_batch['raise_time'].date(), # 发布日期 ticket_batch['raise_time'].time(), # 发布时间 ticket_batch['raise_count'], # 数量 checker_init['real_name'] if checker_init else '-', # 复核人 ])
def create_all_cards(sheet: Worksheet, index: ScryfallDataIndex) -> None: """Create all cards sheet from card_db.""" sheet.title = "All Cards" sheet.append(ALL_CARDS_SHEET_HEADER) for name in sorted(index.name_to_cards): row = [name, get_references(index, name)] sheet.append(row)
async def sheet_ticket_dtl(title: str, sheet: Worksheet, batch: str): sheet.title = title field_title = [ ('序号', 6), # 5.38 ('票券编号', 24), # 23.38 ('发布批次', 18), # 17.38 ('发布日期', 12), # 11.38 ('发布时间', 12), # 11.38 ('票券状态', 12), # 11.38 ] row = IncrementCtrl(0) set_large_title(sheet, row.next, 1, len(field_title), title) set_export_time(sheet, row.next, 1, len(field_title)) set_field_title(sheet, row.next, field_title) index = IncrementCtrl(0) async for ticket in Ticket.find({'batch': batch}): set_array_content(sheet, row.next, [ index.next, # 序号 ticket.json_id[:20], # 票券编号 batch, # 发布批次 ticket['raise_time'].date(), # 发布日期 ticket['raise_time'].time(), # 发布时间 SheetMaker.state_map.get(ticket.get('state'), '-'), # 票券状态 ])
def populate(self, ws: Worksheet, cg: ConnectGroup): ws.title = cg.name self.create_column_headers(ws) for person in sorted( cg.members, key=lambda p: p.personal_attributes[PERSONAL_ATTRIBUTE_NAME] ): ws.append(self.person_as_row_values(person))
def create_tab(worksheet: Worksheet, rows: list, formatting: Formatter): """ Function to specifically create the Non Compliant Resource worksheet. Modified passed in workbook. Parameters: worksheet (Worksheet): The worksheet to modify. rows (list): Data for the rows of each item formatting (object): Object containing various formatting information """ # add header worksheet.append(formatting.get_header_names()) # sort data, since adding a sort to the filter has no effect until excel sorts it sort_header = ([h for h in formatting.get_headers() if h.sort] + [None])[0] if sort_header: rows = list(sorted(rows, key=sort_header.get_value)) for row in rows: worksheet.append(formatting.format_resource(row)) # no footer worksheet.title = formatting.title worksheet.freeze_panes = formatting.freeze # add filtering capability if formatting.excel_filter: worksheet.auto_filter.ref = 'A1:{}{:d}'.format( get_column_letter(len(formatting.get_header_names())), len(rows) + 1 # one header row + number of rows ) # set column widths for idx, header in enumerate(formatting.get_headers()): worksheet.column_dimensions[get_column_letter(idx + 1)].width = header.width # set column conditional formatting for idx, header in enumerate(formatting.get_headers()): if header.conditional_formatting: column_range = '{0}2:{0}{1:d}'.format( get_column_letter(idx + 1), # excel is 0 based indexing len(rows) + 1 # number of rows + 1 header row ) worksheet.conditional_formatting.add(column_range, header.conditional_formatting) # bold header row for header_cell in worksheet[1]: header_cell.font = Font(bold=True) return worksheet
async def sheet_day_dtl(title: str, sheet: Worksheet, date_list: List[str]): """ 运动券使用统计日明细报表 :param title: :param sheet: :param date_list: :return: """ sheet.title = title field_title = [ ('序号', 5.5), # 4.88 ('部门', 13), # 12.38 ('姓名', 9), # 8.38 ('项目', 9), # 8.38 ('票券编号', 23), # 22.38 ('使用日期', 12), # 11.38 ('使用时间', 12), # 11.38 ('检票员', 9), # 8.38 ] row = IncrementCtrl(0) set_large_title(sheet, row.next, 1, len(field_title), title) # set_large_title(sheet, row.next, 1, len(field_title), date) set_export_time(sheet, row.next, 1, len(field_title)) set_field_title(sheet, row.next, field_title) index = IncrementCtrl(0) for date in date_list: async for ticket in Ticket.find({ 'state': 'verified', 'expiry_date': {'$gte': date, '$lte': date} }).sort('expiry_date'): user_init = await UserInit.find_one_by_user(await User.find_one({'_id': ticket['purchaser']})) checker_init = await UserInit.find_one_by_user(await User.find_one({'_id': ticket['checker']})) set_array_content(sheet, row.next, [ index.next, # 序号 (user_init or {}).get('department', '-'), # 部门 (user_init or {}).get('real_name', '-'), # 姓名 SheetMaker.sport_map.get(ticket.get('class'), '-'), # 项目 ticket.json_id[:20], # 票券编号 ticket['check_time'].date(), # 使用日期 ticket['check_time'].time(), # 使用时间 (checker_init or {}).get('real_name', '-'), # 检票人员 ])
def create_all_sets(sheet: Worksheet, index: ScryfallDataIndex) -> None: """Create all sets sheet from card_db.""" sheet.title = "All Sets" sheet.append(ALL_SETS_SHEET_HEADER) sheet.append(ALL_SETS_SHEET_TOTALS) for card_set in sorted(index.setcode_to_set.values(), key=lambda cset: cset.released_at): setcode = card_set.code.upper() row = [ setcode, card_set.name, card_set.released_at, card_set.block, card_set.set_type.value, len(index.setcode_to_cards[card_set.code]), f"=COUNTIF('{setcode}'!A:A,\">0\")", f"=COUNTIF('{setcode}'!A:A,\">=4\")", f"=SUM('{setcode}'!A:A)", ] sheet.append(row)
def create_set_sheet(sheet: Worksheet, collection: MagicCollection, setcode: str) -> None: """Populate sheet with card information from a given set.""" index = collection.oracle.index sheet.append(SET_SHEET_HEADER) sheet.title = setcode.upper() for card in index.setcode_to_cards[setcode]: rownum = ROW_OFFSET + index.id_to_setindex[card.id] row: List[Optional[Any]] = [ HAVE_TMPL.format(rownum=rownum), card.name, str(card.id), card.collector_number, card.artist, ] card_counts = collection.counts.get(card.id, {}) for count_type in counts.CountType: row.append(card_counts.get(count_type)) row.append(get_references(index, card.name, exclude_sets={setcode})) sheet.append(row)
async def sheet_day_check(title: str, sheet: Worksheet, start: str, end: str): sheet.title = title field_title = [ ('统计日期', 21), ('票券总量', 10), ('未使用量', 10), ('已使用量', 10), ] row = IncrementCtrl(1) set_large_title(sheet, row.now, 1, len(field_title), title) set_export_time(sheet, row.next, 1, len(field_title)) set_field_title(sheet, row.next, field_title) async for ticket_check in TicketCheck.find({ 'checked_date': {'$gte': start, '$lte': end} }).sort('checked_date'): set_array_content(sheet, row.next, [ ticket_check.get('checked_date'), # 统计日期 ticket_check.get('all'), # 票券总量 ticket_check.get('default'), # 未使用量 ticket_check.get('verified'), # 已使用量 ])
def fill_excel_sheet(self, worksheet: Worksheet, csv_list: list, name: str = None, title: str = None, description: str = None) -> None: """ This method adds an additional sheet to the given workbook :return: """ start_row = 1 name = name if name is not None else self._name title = title if title is not None else self.title description = description if description is not None else self.description worksheet.title = name if description: csv_list.insert(0, []) csv_list.insert(0, [description]) start_row += 2 if title: csv_list.insert(0, []) csv_list.insert(0, [title]) start_row += 2 for row in csv_list: try: worksheet.append(row) except IllegalCharacterError: print("ignoring row due to illegal character: {}".format(row), file=sys.stderr) except ValueError: raise ValueError("cannot add row to sheet '{}': {}".format( self._name, row)) dimension = worksheet.calculate_dimension() dimension = "A{}:{}".format(start_row, dimension.split(":")[-1]) table = Table(displayName=self._name.replace(" ", ""), ref=dimension) style = TableStyleInfo(name="TableStyleLight8") table.tableStyleInfo = style worksheet.add_table(table)
def create_matrix_tab( worksheet: Worksheet, matrix_rows: list, account_overall_scores: dict, accounts: dict ) -> Worksheet: """ Function to generate the workbook based data already gatered and parsed. Parameters: matrix_rows (list): Direct input for the itemized worksheet. account_overall_scores (dict): Mapping from account id to account overall score accounts (list): List of accounts. Returns: Workbook: The workbook object ready to be saved. """ formatting = MatrixTabFormatting() ### Add data ### # header rows account_header = [] for account in accounts: if 'account_name' in account: account_header.append(account['account_name']) else: account_header.append(account['accountId']) # add header row worksheet.append(formatting.HEADERS + account_header) # add account score rows worksheet.append([formatting.ACCOUNT_SCORE, '', ''] + list(account_overall_scores.values())) # add requirement rows rows = sorted(matrix_rows, key=lambda row: row['description']) # sort by description field for row in rows: worksheet.append([ row['description'], row['requirementId'], row['severity'] ] + row['numFailing']) if all(score == scores_table.NOT_APPLICABLE for score in row['numFailing']): worksheet.row_dimensions[worksheet.max_row].hidden = True # add footer worksheet.append(['']) # empty row worksheet.append([f'Scored Against CSS Version: {formatting.version}']) worksheet.append([f'Report Generated at {datetime.now()} GMT']) ### Apply formatting ### worksheet.title = formatting.TITLE # bold headers for header_cell in worksheet[1][:len(formatting.HEADERS)]: header_cell.font = Font(bold=True, size=11) # vertically align account names for readability for account_name in worksheet[1][len(formatting.HEADERS):]: account_name.alignment = Alignment(text_rotation=45) # word wrap long descriptions for description in worksheet['A']: description.alignment = Alignment(wrap_text=True) # freeze first column and first row worksheet.freeze_panes = formatting.FREEZE # bold overall scores overall_score_row = 2 for grade_cell in worksheet[overall_score_row][:worksheet.max_column]: grade_cell.font = Font(bold=True, size=11) # right align ACCOUNT_SCORE cell worksheet[overall_score_row][1].alignment = Alignment(horizontal='right') # set appropriate font size for row in worksheet.iter_rows(min_row=overall_score_row + 1): for cell in row: cell.font = Font(size=9) # set Description column width worksheet.column_dimensions['A'].width = 80 # set other column widths for col_index in range(len(formatting.HEADERS) + 1, worksheet.max_column + 1): worksheet.column_dimensions[get_column_letter(col_index)].width = 8 # hide requirement id column worksheet.column_dimensions['B'].hidden = True # cell coloring/conditional formatting # format account scores colors_ordered_by_weight = list(reversed(sorted(formatting.severity_formatting.values(), key=lambda severity: severity['weight']))) for account_score in worksheet[overall_score_row][len(formatting.HEADERS):]: try: score = int(account_score.value) except: # pylint: disable=bare-except score = 0 account_score.number_format = '0' # colors in reverse order by weight so first one encountered is correct for color in colors_ordered_by_weight: if score >= color['weight']: account_score.fill = PatternFill(start_color=color['fill'], end_color=color['fill'], fill_type='solid') account_score.font = Font(color=color['font_color'], bold=True) break # add conditional formatting for error scores score_cell_range = '{}3:{}{:d}'.format( get_column_letter(len(formatting.HEADERS) + 1), get_column_letter(worksheet.max_column), len(matrix_rows) + 2 ) score_cell_top_left = '{}3'.format(get_column_letter(len(formatting.HEADERS) + 1)) for error_format in formatting.error_formatting: # convert python string to excel string if isinstance(error_format['value'], str): check_value = formatting.excel_string(error_format['value']) else: check_value = error_format['value'] worksheet.conditional_formatting.add( score_cell_range, Rule( type='expression', formula=[f'{check_value}={score_cell_top_left}'], priority=worksheet.conditional_formatting.max_priority + 1, stopIfTrue=True, dxf=DifferentialStyle( font=Font( color=error_format['font_color'] ), fill=PatternFill( start_color=error_format['fill'], end_color=error_format['fill'], fill_type='solid', ) ) ) ) severity_column_reference = '${}3'.format(get_column_letter(formatting.SEVERITY_COLUMN)) for severity, severity_format in formatting.severity_formatting.items(): # convert python string to excel string check_value = formatting.excel_string(severity) worksheet.conditional_formatting.add( score_cell_range, Rule( type='expression', formula=[f'{check_value}={severity_column_reference}'], priority=worksheet.conditional_formatting.max_priority + 1, stopIfTrue=True, dxf=DifferentialStyle( font=Font( color=severity_format['font_color'] ), fill=PatternFill( start_color=severity_format['fill'], end_color=severity_format['fill'], fill_type='solid', ) ) ) ) return worksheet
def setSheetParams(ws: Worksheet): ws.title = xml_name ws.column_dimensions['A'].width = 10 ws.column_dimensions['B'].width = 30 ws.column_dimensions['C'].width = 60 ws.column_dimensions['D'].width = 60
async def sheet_day_count(title: str, sheet: Worksheet, start: str, end: str): """ 运动券使用统计日报表 :param title: :param sheet: :param start: :param end: :return: """ async def get_data(): _sport_days = {} _sport_total = dict(zip(SheetMaker.sport_map.keys(), [0] * len(SheetMaker.sport_map))) datetime_start = datetime.strptime(start, '%Y-%m-%d') datetime_end = datetime.strptime(end, '%Y-%m-%d') for i in range((datetime_end - datetime_start).days + 1): datetime_now = datetime_start + timedelta(days=i) date_now = datetime_now.strftime('%Y-%m-%d') _sport_days[date_now] = dict((zip(SheetMaker.sport_map.keys(), [0] * len(SheetMaker.sport_map)))) cursor = Ticket.find({ 'state': 'verified', 'expiry_date': {'$gte': start, '$lte': end} }).sort('expiry_date') async for ticket in cursor: date_now = ticket.get('expiry_date', '-') _sport_days[date_now][ticket.get('class')] += 1 _sport_total[ticket.get('class')] += 1 return _sport_days, _sport_total sport_days, sport_total = await get_data() sheet.title = title field_title = [ ('日期', 12), *[(x, 8) for x in SheetMaker.sport_map.values()], ('合计', 8), ('备注', 8), ] row = IncrementCtrl(0) set_large_title(sheet, row.next, 1, len(field_title), title) set_export_time(sheet, row.next, 1, len(field_title)) set_field_title(sheet, row.next, field_title) for (sport_day, sports) in sport_days.items(): break_need = bool(datetime.now().strftime('%Y-%m-%d') == sport_day) # 今日计算完就停止 sport = [count for sport, count in sports.items() if sport != 'expired'] note, pattern = ('今日结束后数据可能会发生变动', style_body_warn) if break_need else ('-', style_body) set_array_content(sheet, row.next, [ sport_day, # 日期 *sport, # 运动项目 sum(sport), # 合计 note, # 备注 ], pattern=pattern) if break_need: break # 写入合计 sport = [count for sport, count in sport_total.items()] set_array_content(sheet, row.next, [ '合计', # 合计 *sport, # 运动项目 sum(sport), # 合计 None, # 备注 ])
async def sheet_month_count(title: str, sheet: Worksheet, start: str, end: str): """ 运动券使用统计月报表 :param title: :param sheet: :param start: :param end: :return: """ async def get_data(): """ 整理数据 :return: """ datetime_start = datetime.strptime(start, '%Y-%m') datetime_end = datetime.strptime(end, '%Y-%m') month_list = [] month_now = datetime(datetime_start.year, datetime_start.month, 15) month_list.append(month_now) while month_now < datetime(datetime_end.year, datetime_end.month, 15): month_now += timedelta(days=30) month_now += timedelta(days=15 - month_now.day) month_list.append(month_now) _sport_months = [] _sport_all = {'sports': dict(zip(SheetMaker.sport_map.keys(), [0] * len(SheetMaker.sport_map)))} for _month in month_list: month_start = date_month_start(_month) month_end = date_month_end(_month) if datetime.now() < month_start: break month_start_str = month_start.strftime('%Y-%m-%d') month_end_str = month_end.strftime('%Y-%m-%d') month_str = _month.strftime('%Y-%m') _sport_month = {'month': month_str, 'start': month_start_str, 'end': month_end_str} if month_str not in _sport_months: _sport_month['sports'] = dict(zip(SheetMaker.sport_map.keys(), [0] * len(SheetMaker.sport_map))) if datetime.now() <= month_end: _sport_month['un-end'] = True async for ticket in Ticket.find({ 'expiry_date': {'$gte': month_start_str, '$lte': month_end_str} }).sort('expiry_date'): if ticket.get('state') == 'verified': _sport_month['sports'][ticket.get('class')] += 1 _sport_all['sports'][ticket.get('class')] += 1 _sport_months.append(_sport_month) return _sport_months, _sport_all sport_months, sport_all = await get_data() sheet.title = title field_title = [ ('年度', 5), ('月份', 5), *[(x, 7) for x in SheetMaker.sport_map.values()], ('合计', 7), ('开始日期', 11), ('结束日期', 11), ('备注', 8), ] row = IncrementCtrl(0) set_large_title(sheet, row.next, 1, len(field_title), title) set_export_time(sheet, row.next, 1, len(field_title)) set_field_title(sheet, row.next, field_title) for sport_month in sport_months: break_need = bool(sport_month.get('un-end')) # 当前月计算完就停止 year, month = sport_month['month'].split('-') sport = [ count for sport, count in sport_month['sports'].items() if sport != 'expired' and sport != 'un-end' ] note, pattern = ('本月结束后数据可能会发生变动', style_body_warn) if break_need else ('-', style_body) set_array_content(sheet, row.next, [ year, # 年度 month, # 月份 *sport, # 运动项目 sum(sport), # 合计 sport_month['start'], # 开始日期 sport_month['end'], # 结束日期 note, # 备注 ], pattern=pattern) if break_need: break # 写入合计 sport = [count for sport, count in sport_all['sports'].items()] set_array_content(sheet, row.next, [ '合计', # 合计 None, # 合计(被合并的单元格) *sport, # 运动项目 sum(sport), # 合计 '-', # 开始日期 '-', # 结束日期 '-', # 备注 ]) set_merge_content(sheet, row.now, 1, 2, '合计')
def create_ncr_tab(worksheet: Worksheet, ncr_data: list): """ Function to specifically create the Non Compliant Resource worksheet. Modified passed in workbook. Parameters: worksheet (Worksheet): The worksheet to modify. ncr_data (list): The ncr data to be dropped into the worksheet. """ formatting = NcrTabFormatting() # add header worksheet.append(formatting.get_header_names()) # sort data, since adding a sort to the filter has no effect until excel sorts it sort_header = ([h for h in formatting.headers if h.get('sort')] + [None])[0] if sort_header: ncrs = sorted(ncr_data, key=lambda ncr: get_value(sort_header, ncr)) else: ncrs = ncr_data for ncr in ncrs: if ncr.get('isHidden'): # We've marked this one for hiding continue worksheet.append(formatting.format_resource(ncr)) # no footer worksheet.title = formatting.TITLE worksheet.freeze_panes = formatting.FREEZE # starting_column = ord('A') + scorecard.NCR_STARTING_COLUMN for idx, header in enumerate(formatting.headers): worksheet.column_dimensions[get_column_letter(idx + 1)].width = header['width'] # bold header row for header_cell in worksheet[1]: header_cell.font = Font(bold=True) starting_column = 'A' # add filtering capability worksheet.auto_filter.ref = 'A1:{}{:d}'.format( get_column_letter(worksheet.max_column), worksheet.max_row ) italics_max_row = max(worksheet.max_row, 3) # add conditional formatting for resource rows with a valid exclusion (italics) cell_range = '{}2:{}{:d}'.format(starting_column, get_column_letter(worksheet.max_column), italics_max_row) exclusion_valid_column = get_column_letter(formatting.get_exclusion_applied_header_index() + 1) worksheet.conditional_formatting.add( cell_range, FormulaRule( formula=['AND(${0}2=TRUE, NOT(ISBLANK(${0}2)))'.format(exclusion_valid_column)], font=formatting.VALID_EXCLUSION_FONT ) ) # add conditional formatting for resource rows with an expired exclusion (italic red text) worksheet.conditional_formatting.add( cell_range, FormulaRule( formula=['AND(${0}2=FALSE, NOT(ISBLANK(${0}2)))'.format(exclusion_valid_column)], font=formatting.INVALID_EXCLUSION_FONT, ) ) return worksheet