79 lines
2.8 KiB
Python
79 lines
2.8 KiB
Python
# excel_export.py
|
||
import csv
|
||
from io import StringIO, BytesIO
|
||
|
||
|
||
def _to_csv(headers, rows):
|
||
"""生成 UTF-8 BOM 编码的 CSV，兼容 VSCode 和 Excel"""
|
||
buf = StringIO()
|
||
writer = csv.writer(buf)
|
||
writer.writerow(headers)
|
||
writer.writerows(rows)
|
||
|
||
output = BytesIO()
|
||
output.write(b'\xef\xbb\xbf') # UTF-8 BOM
|
||
output.write(buf.getvalue().encode('utf-8'))
|
||
output.seek(0)
|
||
return output
|
||
|
||
|
||
def export_users_to_excel(users):
|
||
headers = ["ID", "用户名", "邮箱", "手机号", "角色", "状态", "完成考试数", "注册时间"]
|
||
rows = []
|
||
for u in users:
|
||
rows.append([
|
||
u.id, u.name, u.email or "", u.phone or "", u.role,
|
||
"已封禁" if u.is_banned else "正常", u.completed_exams,
|
||
u.created_at.strftime("%Y-%m-%d %H:%M:%S") if u.created_at else ""
|
||
])
|
||
return _to_csv(headers, rows)
|
||
|
||
|
||
def export_exams_to_excel(exams):
|
||
headers = ["ID", "考试名称", "创建者ID", "总分", "时长(分钟)", "状态", "提交数", "创建时间"]
|
||
rows = []
|
||
for e in exams:
|
||
rows.append([
|
||
e.id, e.title, e.creator_id, e.total_score, e.duration, e.status,
|
||
len(e.submissions) if e.submissions else 0,
|
||
e.created_at.strftime("%Y-%m-%d %H:%M:%S") if e.created_at else ""
|
||
])
|
||
return _to_csv(headers, rows)
|
||
|
||
|
||
def export_submissions_to_excel(submissions):
|
||
headers = ["ID", "考试ID", "考试名称", "用户ID", "用户名", "得分", "总分", "状态", "提交时间"]
|
||
rows = []
|
||
for s in submissions:
|
||
rows.append([
|
||
s.id, s.exam_id, s.exam.title if s.exam else "", s.user_id,
|
||
s.user.name if s.user else "", s.score if s.score is not None else "",
|
||
s.total_score, s.status,
|
||
s.submitted_at.strftime("%Y-%m-%d %H:%M:%S") if s.submitted_at else ""
|
||
])
|
||
return _to_csv(headers, rows)
|
||
|
||
|
||
def export_contests_to_excel(contests):
|
||
headers = ["ID", "杯赛名称", "主办方", "开始日期", "结束日期", "状态", "参与人数", "创建时间"]
|
||
rows = []
|
||
for c in contests:
|
||
rows.append([
|
||
c.id, c.name, c.organizer or "", c.start_date or "", c.end_date or "",
|
||
c.status, c.participants,
|
||
c.created_at.strftime("%Y-%m-%d %H:%M:%S") if c.created_at else ""
|
||
])
|
||
return _to_csv(headers, rows)
|
||
|
||
|
||
def export_posts_to_excel(posts):
|
||
headers = ["ID", "标题", "作者", "分类", "浏览数", "回复数", "点赞数", "创建时间"]
|
||
rows = []
|
||
for p in posts:
|
||
rows.append([
|
||
p.id, p.title, p.author.name if p.author else "", p.category or "",
|
||
p.views, p.reply_count, p.like_count,
|
||
p.created_at.strftime("%Y-%m-%d %H:%M:%S") if p.created_at else ""
|
||
])
|
||
return _to_csv(headers, rows)
|