129 lines
5.4 KiB
Python
129 lines
5.4 KiB
Python
#!/usr/bin/env python3
|
||
"""导出退款用户行课分析 Excel"""
|
||
import openpyxl
|
||
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill
|
||
from openpyxl.utils import get_column_letter
|
||
from datetime import date
|
||
|
||
wb = openpyxl.Workbook()
|
||
|
||
# ===== Sheet 1: 用户概览 =====
|
||
ws1 = wb.active
|
||
ws1.title = "用户概览"
|
||
|
||
header_font = Font(name="微软雅黑", bold=True, size=11, color="FFFFFF")
|
||
header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
|
||
cell_font = Font(name="微软雅黑", size=10)
|
||
center_align = Alignment(horizontal="center", vertical="center", wrap_text=True)
|
||
left_align = Alignment(horizontal="left", vertical="center", wrap_text=True)
|
||
thin_border = Border(
|
||
left=Side(style="thin"), right=Side(style="thin"),
|
||
top=Side(style="thin"), bottom=Side(style="thin")
|
||
)
|
||
|
||
headers1 = ["序号", "手机号", "Account ID", "购买商品", "购买金额", "购买日期", "购买渠道", "退款状态", "角色名", "角色年龄", "课程状态", "已加班主任"]
|
||
col_widths1 = [6, 16, 12, 18, 12, 14, 28, 10, 10, 10, 16, 12]
|
||
|
||
for col_idx, (h, w) in enumerate(zip(headers1, col_widths1), 1):
|
||
cell = ws1.cell(row=1, column=col_idx, value=h)
|
||
cell.font = header_font
|
||
cell.fill = header_fill
|
||
cell.alignment = center_align
|
||
cell.border = thin_border
|
||
ws1.column_dimensions[get_column_letter(col_idx)].width = w
|
||
|
||
data1 = [
|
||
[1, "18814300616", 28288, "L1+L2联报", "¥3,598", "2026-05-23", "老狼聊育儿(达人直播)", "已退款", "Wing", "18岁(2008-02-29)", "正常", "是"],
|
||
[2, "18604787938", 28269, "L2年包", "¥1,999", "2026-05-23", "老狼聊育儿(达人直播)", "已退款", "无角色", "-", "已删除(5/23)", "是"],
|
||
[3, "13728635257", 28261, "L2年包", "¥1,999", "2026-05-23", "老狼聊育儿(达人直播)", "已退款", "Solomon", "12岁(2013-06-13)", "正常", "是"],
|
||
[4, "15387076394", 28293, "L1+L2联报", "¥3,598", "2026-05-23", "老狼聊育儿(达人直播)", "已退款", "无角色", "-", "已删除(5/28)", "是"],
|
||
]
|
||
|
||
for row_idx, row_data in enumerate(data1, 2):
|
||
for col_idx, val in enumerate(row_data, 1):
|
||
cell = ws1.cell(row=row_idx, column=col_idx, value=val)
|
||
cell.font = cell_font
|
||
cell.alignment = center_align
|
||
cell.border = thin_border
|
||
|
||
ws1.row_dimensions[1].height = 28
|
||
for r in range(2, 6):
|
||
ws1.row_dimensions[r].height = 22
|
||
|
||
# ===== Sheet 2: 行课明细 =====
|
||
ws2 = wb.create_sheet("行课明细")
|
||
|
||
headers2 = ["序号", "手机号", "Account ID", "角色名", "课程等级", "完成课时", "完课时间", "耗时(分钟)", "备注"]
|
||
col_widths2 = [6, 16, 12, 10, 10, 22, 22, 12, 20]
|
||
|
||
for col_idx, (h, w) in enumerate(zip(headers2, col_widths2), 1):
|
||
cell = ws2.cell(row=1, column=col_idx, value=h)
|
||
cell.font = header_font
|
||
cell.fill = header_fill
|
||
cell.alignment = center_align
|
||
cell.border = thin_border
|
||
ws2.column_dimensions[get_column_letter(col_idx)].width = w
|
||
|
||
data2 = [
|
||
[1, "18814300616", 28288, "Wing", "L1", "S0 U00 L01", "2026-05-23 21:10", 18.5, "L1+L2联报→先看L1"],
|
||
[2, "18814300616", 28288, "Wing", "L1", "S0 U00 L02", "2026-05-23 21:43", 16.8, ""],
|
||
[3, "18604787938", 28269, "-", "L2", "无", "-", "-", "课程已删除，无行课"],
|
||
[4, "13728635257", 28261, "Solomon", "L2", "无", "-", "-", "0课时完成"],
|
||
[5, "15387076394", 28293, "-", "L1+L2", "无", "-", "-", "课程已删除，无行课"],
|
||
]
|
||
|
||
for row_idx, row_data in enumerate(data2, 2):
|
||
for col_idx, val in enumerate(row_data, 1):
|
||
cell = ws2.cell(row=row_idx, column=col_idx, value=val)
|
||
cell.font = cell_font
|
||
cell.alignment = center_align
|
||
cell.border = thin_border
|
||
|
||
ws2.row_dimensions[1].height = 28
|
||
for r in range(2, 7):
|
||
ws2.row_dimensions[r].height = 22
|
||
|
||
# ===== Sheet 3: 汇总 =====
|
||
ws3 = wb.create_sheet("汇总分析")
|
||
|
||
summary_data = [
|
||
["指标", "数值"],
|
||
["分析用户数", 4],
|
||
["全部已退款", "4/4 (100%)"],
|
||
["已加班主任", "4/4 (100%)"],
|
||
["有行课记录", "1/4 (25%)"],
|
||
["行课用户完成课时数", "2课时 (L1 S0 U00 L01-L02)"],
|
||
["行课用户平均完课时长", "17.6分钟"],
|
||
["购买渠道", "老狼聊育儿(达人直播)"],
|
||
["购买日期", "2026-05-23"],
|
||
["", ""],
|
||
["结论", "4人均为5月23日同一渠道购买，全部已退款且全部已加班主任。仅1人有行课(完成2课时)，行课率25%，整体行课参与度很低。"],
|
||
]
|
||
|
||
for row_idx, row_data in enumerate(summary_data, 1):
|
||
for col_idx, val in enumerate(row_data, 1):
|
||
cell = ws3.cell(row=row_idx, column=col_idx, value=val)
|
||
if row_idx == 1:
|
||
cell.font = header_font
|
||
cell.fill = header_fill
|
||
cell.alignment = center_align
|
||
else:
|
||
cell.font = cell_font
|
||
cell.alignment = left_align if col_idx == 2 else center_align
|
||
cell.border = thin_border
|
||
|
||
ws3.column_dimensions['A'].width = 22
|
||
ws3.column_dimensions['B'].width = 50
|
||
ws3.row_dimensions[1].height = 28
|
||
for r in range(2, len(summary_data) + 1):
|
||
ws3.row_dimensions[r].height = 22
|
||
|
||
# 合并结论行
|
||
ws3.merge_cells(start_row=11, start_column=1, end_row=11, end_column=2)
|
||
ws3.cell(row=11, column=1).alignment = Alignment(horizontal="left", vertical="center", wrap_text=True)
|
||
ws3.row_dimensions[11].height = 40
|
||
|
||
output_path = "/root/.openclaw/workspace/output/退款用户行课分析_20260602.xlsx"
|
||
wb.save(output_path)
|
||
print(f"Excel saved: {output_path}")
|