#!/usr/bin/env python3 """三系统月度汇总 — 修正版：排除底部「总计」行""" import openpyxl, json from datetime import datetime from collections import defaultdict fp = '/mnt/c/Users/kingw/Desktop/自助机项目成立至2026年5月31日完全统计表.xlsx' wb_src = openpyxl.load_workbook(fp, data_only=True) ice = defaultdict(lambda: {'total': 0.0, 'count': 0}) bev = defaultdict(lambda: {'total': 0.0, 'count': 0}) sheets_cfg = [ ('2.0系统冰激淋', 2, 3, 10, 'ice'), ('2.0系统 饮料机', 2, 3, 10, 'bev'), ('1.0系统冰激淋', 2, 3, 19, 'ice'), ('1.0系统饮料数据', 1, 2, 7, 'bev'), ('海逸冰激淋', 3, 4, 28, 'ice'), ] for sname, name_col, fm_col, total_col, cat in sheets_cfg: ws = wb_src[sname] for row in range(2, ws.max_row + 1): # 🚫 Skip 总计 summary row name = str(ws.cell(row=row, column=name_col).value or '').strip() if name == '总计' or name == '' or name == 'None': continue for col in range(fm_col, total_col): h = ws.cell(row=1, column=col).value if not isinstance(h, datetime): continue if h < datetime(2025, 1, 1) or h > datetime(2026, 5, 31): continue val = ws.cell(row=row, column=col).value if val and val != '-' and val != 0: try: amt = float(val) except: continue if amt > 0: m = h.strftime('%Y-%m') target = ice if cat == 'ice' else bev target[m]['total'] += amt target[m]['count'] += 1 # Build ordered month series months_17 = [] for y in [2025, 2026]: end = 13 if y == 2025 else 6 for m in range(1, end): months_17.append(f'{y}-{m:02d}') def build_series(data): rows = [] for m in months_17: d = data.get(m, {'total': 0.0, 'count': 0}) rows.append({ 'month': m, 'total': round(d['total'], 2), 'count': d['count'], 'avg': round(d['total'] / d['count'], 2) if d['count'] > 0 else 0 }) return rows ice_s = build_series(ice) bev_s = build_series(bev) comb_s = [] for i, m in enumerate(months_17): t = ice_s[i]['total'] + bev_s[i]['total'] c = ice_s[i]['count'] + bev_s[i]['count'] comb_s.append({'month': m, 'total': t, 'count': c, 'avg': round(t/c, 2) if c > 0 else 0}) # ── Output Excel ── wb = openpyxl.Workbook() hf = openpyxl.styles.PatternFill(start_color='2F5496', end_color='2F5496', fill_type='solid') hfont = openpyxl.styles.Font(bold=True, color='FFFFFF', size=10) bdr = openpyxl.styles.Border( left=openpyxl.styles.Side(style='thin'), right=openpyxl.styles.Side(style='thin'), top=openpyxl.styles.Side(style='thin'), bottom=openpyxl.styles.Side(style='thin')) ca = openpyxl.styles.Alignment(horizontal='center') # Summary sheet ws = wb.active ws.title = '月度汇总' headers = ['月份', '🍦冰激淋销售额', '冰机器数', '冰单机月均', '🥤饮料销售额', '饮机器数', '饮单机月均', '📊合计销售额', '合机器数', '合单机月均'] for c, h in enumerate(headers, 1): cell = ws.cell(row=1, column=c, value=h) cell.font = hfont; cell.fill = hf; cell.alignment = ca; cell.border = bdr for i in range(17): row = i + 2 for c, v in enumerate([ comb_s[i]['month'], ice_s[i]['total'], ice_s[i]['count'], ice_s[i]['avg'], bev_s[i]['total'], bev_s[i]['count'], bev_s[i]['avg'], comb_s[i]['total'], comb_s[i]['count'], comb_s[i]['avg'], ], 1): cell = ws.cell(row=row, column=c, value=v) cell.border = bdr; cell.alignment = ca if c in (2, 5, 8): cell.number_format = '#,##0' elif c in (4, 7, 10): cell.number_format = '#,##0' for col, w in [(1,10),(2,14),(3,10),(4,12),(5,14),(6,10),(7,12),(8,14),(9,10),(10,12)]: ws.column_dimensions[openpyxl.utils.get_column_letter(col)].width = w # Chart sheets from openpyxl.chart import BarChart, LineChart, Reference def chart_sheet(wb, name, series, color, title): ws = wb.create_sheet(title=name) for c, h in enumerate(['月份', '销售额', '机器数', '单机月均'], 1): cell = ws.cell(row=1, column=c, value=h) cell.font = hfont; cell.fill = hf; cell.alignment = ca; cell.border = bdr for i, r in enumerate(series): row = i + 2 ws.cell(row=row, column=1, value=r['month']).border = bdr ws.cell(row=row, column=1).alignment = ca ws.cell(row=row, column=2, value=r['total']).border = bdr ws.cell(row=row, column=2).number_format = '#,##0' ws.cell(row=row, column=3, value=r['count']).border = bdr ws.cell(row=row, column=3).alignment = ca ws.cell(row=row, column=4, value=r['avg']).border = bdr ws.cell(row=row, column=4).number_format = '#,##0' for c, w in [(1,10),(2,14),(3,10),(4,14)]: ws.column_dimensions[openpyxl.utils.get_column_letter(c)].width = w n = len(series) chart = BarChart() chart.type = 'col'; chart.style = 10; chart.title = title chart.y_axis.title = '销售额 (¥)'; chart.width = 26; chart.height = 15 chart.add_data(Reference(ws, min_col=2, min_row=1, max_row=n+1), titles_from_data=True) chart.set_categories(Reference(ws, min_col=1, min_row=2, max_row=n+1)) chart.series[0].graphicalProperties.solidFill = color l1 = LineChart() l1.add_data(Reference(ws, min_col=3, min_row=1, max_row=n+1), titles_from_data=True) l1.y_axis.axId = 200; l1.y_axis.title = '机器数'; l1.y_axis.crosses = 'max' l1.series[0].graphicalProperties.line.solidFill = 'FF0000' l1.series[0].marker.symbol = 'circle'; l1.series[0].marker.size = 5 chart += l1 l2 = LineChart() l2.add_data(Reference(ws, min_col=4, min_row=1, max_row=n+1), titles_from_data=True) l2.y_axis.axId = 300; l2.y_axis.title = '单机月均 (¥)'; l2.y_axis.crosses = 'max' l2.series[0].graphicalProperties.line.solidFill = '70AD47' l2.series[0].marker.symbol = 'diamond'; l2.series[0].marker.size = 5 chart += l2 ws.add_chart(chart, f'A{n+4}') chart_sheet(wb, '🍦冰激淋趋势', ice_s, '4472C4', '冰激淋月度趋势（2025.01-2026.05）') chart_sheet(wb, '🥤饮料趋势', bev_s, 'ED7D31', '饮料月度趋势（2025.01-2026.05）') chart_sheet(wb, '📊合计趋势', comb_s, '7030A0', '全品类合计月度趋势（2025.01-2026.05）') wb.move_sheet('月度汇总', offset=-3) filepath = '/mnt/c/Users/kingw/Desktop/三系统月度销售汇总_2025.01-2026.05.xlsx' wb.save(filepath) ti = sum(r['total'] for r in ice_s) tb = sum(r['total'] for r in bev_s) print(f'✅ {filepath}') print(f'🍦冰激淋: ¥{ti:,.2f} | 🥤饮料: ¥{tb:,.2f} | 📊合计: ¥{ti+tb:,.2f}') print(f'2026-05: 冰¥{ice_s[-1]["total"]:,.0f} + 饮¥{bev_s[-1]["total"]:,.0f} = ¥{comb_s[-1]["total"]:,.0f}') PYEOF