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