#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""生成刘子研 2025 vs 2026 同期对比 Excel（含图表）"""
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.chart import BarChart, LineChart, Reference
from openpyxl.utils import get_column_letter

# ============ 数据（已验证） ============
months = ['1月', '2月', '3月', '4月', '5月', '6月', '7月', '8月']
y2025 = [315952, 1978538, 327022, 196585, 324724, 295295, 583615, 382607]
y2026 = [689207, 478007, 2146608, 382420, 295484, 1086089, 422317, 199064]

# 客户 1-8月累计（元）
cust2025 = {
    '珠海丽珠试剂股份有限公司': 2040163, '苏州天隆生物科技有限公司': 678700,
    '西安佰奥莱博生物科技有限公司': 594545, '苏州华益美生物科技有限公司': 359960,
    '西安华伟科技有限公司': 333000, '杭州丹威生物科技有限公司': 193328,
    '宁波海尔施基因科技股份有限公司': 78360, '复星诊断科技（上海）有限公司': 65220,
    '陆军军医大学第一附属医院': 24360, '宁波海壹基因科技有限公司': 15246,
    '浙江东方基因生物制品股份有限公司': 8544, '杭州迪安生物技术有限公司': 5846,
    '西安安瑞科生物科技有限公司': 2800, '基灵（西安）生物科技有限公司': 2666,
    '艾康生物技术（杭州）有限公司': 1600,
}
cust2026 = {
    '珠海丽珠试剂股份有限公司': 2388360, '西安华伟科技有限公司': 558000,
    '复星诊断科技（上海）有限公司': 495075, '苏州华益美生物科技有限公司': 485000,
    '杭州丹威生物科技有限公司': 461519, '西安佰奥莱博生物科技有限公司': 437240,
    '苏州天隆生物科技有限公司': 367910, '杭州迪安生物技术有限公司': 325855,
    '深圳市新产业生物医学工程股份有限公司': 90220, '宁波海尔施基因科技股份有限公司': 41308,
    '基灵（西安）生物科技有限公司': 18242, '宁波海壹基因科技有限公司': 12474,
    '珠海丽禾医疗诊断产品有限公司': 8894, '西安安瑞科生物科技有限公司': 5600,
    '上海基灵生物科技有限公司': 3500,
}

# 2026 客户×月明细（元）
cust2026_monthly = {
    '珠海丽珠试剂股份有限公司': [65970, 0, 1673716, 197308, 0, 426758, 7358, 17250],
    '西安华伟科技有限公司': [362000, 0, 196000, 0, 0, 0, 0, 0],
    '复星诊断科技（上海）有限公司': [0, 176384, 34650, 0, 120684, 20800, 134757, 7800],
    '苏州华益美生物科技有限公司': [0, 214500, 0, 0, 0, 270500, 0, 0],
    '杭州丹威生物科技有限公司': [113062, 34866, 10074, 27160, 12372, 87081, 145240, 31664],
    '西安佰奥莱博生物科技有限公司': [80170, 33500, 17000, 35070, 77350, 104550, 89600, 0],
    '苏州天隆生物科技有限公司': [0, 11500, 3000, 84450, 7400, 145000, 30160, 86400],
    '杭州迪安生物技术有限公司': [36320, 3000, 174000, 5591, 45000, 27144, 0, 34800],
    '深圳市新产业生物医学工程股份有限公司': [30040, 0, 0, 23300, 15080, 0, 13400, 8400],
    '宁波海尔施基因科技股份有限公司': [945, 1890, 28575, 5166, 3724, 756, 252, 0],
    '基灵（西安）生物科技有限公司': [0, 1666, 0, 2975, 0, 0, 850, 12750],
    '宁波海壹基因科技有限公司': [0, 0, 0, 0, 12474, 0, 0, 0],
    '珠海丽禾医疗诊断产品有限公司': [0, 0, 8894, 0, 0, 0, 0, 0],
    '西安安瑞科生物科技有限公司': [700, 700, 700, 1400, 1400, 0, 700, 0],
    '上海基灵生物科技有限公司': [0, 0, 0, 0, 0, 3500, 0, 0],
}

# ============ 样式 ============
HDR_FILL = PatternFill('solid', fgColor='1a5276')
HDR_FONT = Font(bold=True, color='FFFFFF', size=11)
TITLE_FONT = Font(bold=True, size=14, color='1a5276')
Y25_FILL = PatternFill('solid', fgColor='d6e4f0')
Y26_FILL = PatternFill('solid', fgColor='fdebd0')
THIN = Side(style='thin', color='b0b0b0')
BORDER = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
CENTER = Alignment(horizontal='center', vertical='center')

wb = Workbook()

# ============ Sheet1 月度对比 ============
ws = wb.active
ws.title = '月度同期对比'
ws['A1'] = '刘子研（大客户部）月度销售额同期对比 · 2025 vs 2026'
ws['A1'].font = TITLE_FONT
ws.merge_cells('A1:D1')
ws['A2'] = '口径：2026=本人+黄明月/张立娅(离职交接)+刘新元代管23家大客户；2025=「大客户1」区域（同盘子）'
ws['A2'].font = Font(size=9, color='666666')
ws.merge_cells('A2:D2')

hdr = ['月份', '2025年(元)', '2026年(元)', '同比%']
for j, h in enumerate(hdr, 1):
    c = ws.cell(row=4, column=j, value=h)
    c.fill = HDR_FILL; c.font = HDR_FONT; c.alignment = CENTER; c.border = BORDER

for i, m in enumerate(months):
    r = 5 + i
    a, b = y2025[i], y2026[i]
    yoy = (b - a) / a if a else 0
    ws.cell(row=r, column=1, value=m).alignment = CENTER
    ws.cell(row=r, column=2, value=a).number_format = '#,##0'
    ws.cell(row=r, column=3, value=b).number_format = '#,##0'
    ws.cell(row=r, column=4, value=yoy).number_format = '0.0%'
    ws.cell(row=r, column=2).fill = Y25_FILL
    ws.cell(row=r, column=3).fill = Y26_FILL
    for j in range(1, 5):
        ws.cell(row=r, column=j).border = BORDER

# 合计行
r = 13
ws.cell(row=r, column=1, value='1-8月累计').font = Font(bold=True)
ws.cell(row=r, column=2, value=sum(y2025)).number_format = '#,##0'
ws.cell(row=r, column=3, value=sum(y2026)).number_format = '#,##0'
ws.cell(row=r, column=4, value=(sum(y2026)-sum(y2025))/sum(y2025)).number_format = '0.0%'
for j in range(1, 5):
    ws.cell(row=r, column=j).border = BORDER
    ws.cell(row=r, column=j).font = Font(bold=True)

ws.column_dimensions['A'].width = 10
ws.column_dimensions['B'].width = 16
ws.column_dimensions['C'].width = 16
ws.column_dimensions['D'].width = 10

# 柱状图（2025 vs 2026）
bar = BarChart()
bar.type = 'col'
bar.grouping = 'clustered'
bar.title = '月度销售额对比（元）'
bar.y_axis.title = '销售额(元)'
bar.x_axis.title = '月份'
data = Reference(ws, min_col=2, max_col=3, min_row=4, max_row=12)
cats = Reference(ws, min_col=1, min_row=5, max_row=12)
bar.add_data(data, titles_from_data=True)
bar.set_categories(cats)
bar.width = 18
bar.height = 10
ws.add_chart(bar, 'F4')

# 折线图（同比%）
line = LineChart()
line.title = '同比增速'
line.y_axis.title = '同比%'
line.x_axis.title = '月份'
data2 = Reference(ws, min_col=4, min_row=4, max_row=12)
line.add_data(data2, titles_from_data=True)
line.set_categories(cats)
line.width = 18
line.height = 9
ws.add_chart(line, 'F19')

# ============ Sheet2 客户对比 ============
ws2 = wb.create_sheet('客户同期对比')
ws2['A1'] = '刘子研客户盘子 · 1-8月累计同期对比（元）'
ws2['A1'].font = TITLE_FONT
ws2.merge_cells('A1:D1')

allc = sorted(set(cust2025) | set(cust2026), key=lambda c: -(cust2025.get(c,0)+cust2026.get(c,0)))
hdr2 = ['客户', '2025年(元)', '2026年(元)', '同比%']
for j, h in enumerate(hdr2, 1):
    c = ws2.cell(row=3, column=j, value=h)
    c.fill = HDR_FILL; c.font = HDR_FONT; c.alignment = CENTER; c.border = BORDER

for i, cname in enumerate(allc):
    r = 4 + i
    a = cust2025.get(cname, 0)
    b = cust2026.get(cname, 0)
    yoy = (b - a) / a if a else None
    ws2.cell(row=r, column=1, value=cname)
    ws2.cell(row=r, column=2, value=a).number_format = '#,##0'
    ws2.cell(row=r, column=3, value=b).number_format = '#,##0'
    if yoy is None:
        ws2.cell(row=r, column=4, value='新增')
    else:
        ws2.cell(row=r, column=4, value=yoy).number_format = '0.0%'
    ws2.cell(row=r, column=2).fill = Y25_FILL
    ws2.cell(row=r, column=3).fill = Y26_FILL
    for j in range(1, 5):
        ws2.cell(row=r, column=j).border = BORDER

n = len(allc)
ws2.column_dimensions['A'].width = 40
ws2.column_dimensions['B'].width = 15
ws2.column_dimensions['C'].width = 15
ws2.column_dimensions['D'].width = 10

bar2 = BarChart()
bar2.type = 'col'
bar2.grouping = 'clustered'
bar2.title = '客户 1-8月累计对比（元）'
data = Reference(ws2, min_col=2, max_col=3, min_row=3, max_row=3+n)
cats = Reference(ws2, min_col=1, min_row=4, max_row=3+n)
bar2.add_data(data, titles_from_data=True)
bar2.set_categories(cats)
bar2.width = 24
bar2.height = 14
ws2.add_chart(bar2, 'F3')

# ============ Sheet3 2026客户月度明细 ============
ws3 = wb.create_sheet('客户月度明细-2026')
ws3['A1'] = '刘子研 2026年 各客户月度明细（元）'
ws3['A1'].font = TITLE_FONT
ws3.merge_cells('A1:I1')

hdr3 = ['客户'] + months + ['1-8月合计']
for j, h in enumerate(hdr3, 1):
    c = ws3.cell(row=3, column=j, value=h)
    c.fill = HDR_FILL; c.font = HDR_FONT; c.alignment = CENTER; c.border = BORDER

sorted2026 = sorted(cust2026_monthly.keys(), key=lambda c: -sum(cust2026_monthly[c]))
for i, cname in enumerate(sorted2026):
    r = 4 + i
    ws3.cell(row=r, column=1, value=cname)
    row = cust2026_monthly[cname]
    for j, v in enumerate(row):
        ws3.cell(row=r, column=2+j, value=v).number_format = '#,##0'
    ws3.cell(row=r, column=10, value=sum(row)).number_format = '#,##0'
    ws3.cell(row=r, column=10).font = Font(bold=True)
    for j in range(1, 11):
        ws3.cell(row=r, column=j).border = BORDER

ws3.column_dimensions['A'].width = 40
for j in range(1, 10):
    ws3.column_dimensions[get_column_letter(1+j)].width = 12

out = '/Users/liuxinyuan/Desktop/Hermes输出-工作类/刘子研_2025vs2026同期对比.xlsx'
wb.save(out)
print('已生成:', out)