#!/usr/bin/env python3 # 订单汇总与异常检查（Excel 自动化样例） # 依赖：pip install openpyxl # 用法：python excel_summary.py # 默认读取同目录 orders-input.xlsx，生成 orders-result.xlsx from collections import defaultdict, Counter from copy import copy from pathlib import Path from openpyxl import Workbook, load_workbook from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.utils import get_column_letter BASE = Path(__file__).resolve().parent INPUT = BASE / "orders-input.xlsx" OUTPUT = BASE / "orders-result.xlsx" HEADER_FILL = PatternFill("solid", fgColor="EFE7D8") ALERT_FILL = PatternFill("solid", fgColor="F8D7D0") OK_FILL = PatternFill("solid", fgColor="E8F0EA") THIN = Border( left=Side(style="thin", color="E4D8C4"), right=Side(style="thin", color="E4D8C4"), top=Side(style="thin", color="E4D8C4"), bottom=Side(style="thin", color="E4D8C4"), ) TITLE_FONT = Font(bold=True, size=14, color="1C1915") HEAD_FONT = Font(bold=True) def style_header(ws, row, cols): ws.row_dimensions[row].height = 22 for col in range(1, cols + 1): cell = ws.cell(row, col) cell.fill = HEADER_FILL cell.font = HEAD_FONT cell.alignment = Alignment(vertical="center") cell.border = THIN def autosize(ws, min_width=10, max_width=28): for col in ws.columns: letter = get_column_letter(col[0].column) length = 0 for cell in col: val = "" if cell.value is None else str(cell.value) length = max(length, min(max_width, len(val) + 2)) ws.column_dimensions[letter].width = max(min_width, length) def summarize(input_path=INPUT, output_path=OUTPUT): wb_in = load_workbook(input_path) ws_in = wb_in.active rows = list(ws_in.iter_rows(min_row=2, values_only=True)) totals = defaultdict(lambda: {"qty": 0, "amount": 0.0, "orders": 0}) order_ids = [] issues = [] valid_orders = 0 total_amount = 0.0 for idx, row in enumerate(rows, start=2): date, order_id, product, qty, price = (list(row) + [None] * 5)[:5] order_ids.append(order_id) problems = [] if not order_id: problems.append("缺少单号") if not product: problems.append("缺少商品") if qty is None: problems.append("缺少数量") if price is None or price == "": problems.append("空单价") try: qty_n = float(qty) if qty is not None and qty != "" else None except (TypeError, ValueError): qty_n = None problems.append("数量不是数字") try: price_n = float(price) if price is not None and price != "" else None except (TypeError, ValueError): price_n = None problems.append("单价不是数字") if problems: issues.append((date, order_id, product, qty, price, "；".join(problems), idx)) continue amount = qty_n * price_n totals[str(product)]["qty"] += qty_n totals[str(product)]["amount"] += amount totals[str(product)]["orders"] += 1 valid_orders += 1 total_amount += amount id_counts = Counter(i for i in order_ids if i) for oid, count in id_counts.items(): if count > 1: issues.append(("", oid, "", "", "", f"单号重复 {count} 次", "")) wb = Workbook() ws_sum = wb.active ws_sum.title = "汇总" ws_sum["A1"] = "本月订单汇总" ws_sum["A1"].font = TITLE_FONT ws_sum.merge_cells("A1:D1") ws_sum["A2"] = f"有效订单 {valid_orders} 笔；合计金额 {total_amount:.2f} 元；异常 {len(issues)} 条" headers = ["商品", "订单数", "总数量", "总金额"] for col, name in enumerate(headers, 1): ws_sum.cell(4, col, name) style_header(ws_sum, 4, 4) r = 5 for product, data in sorted(totals.items(), key=lambda x: -x[1]["amount"]): ws_sum.cell(r, 1, product) ws_sum.cell(r, 2, data["orders"]) ws_sum.cell(r, 3, data["qty"]) ws_sum.cell(r, 4, round(data["amount"], 2)) for c in range(1, 5): ws_sum.cell(r, c).border = THIN if r % 2 == 0: ws_sum.cell(r, c).fill = OK_FILL r += 1 autosize(ws_sum) ws_issue = wb.create_sheet("异常") ws_issue["A1"] = "需要人工核对的异常" ws_issue["A1"].font = TITLE_FONT ws_issue.merge_cells("A1:F1") issue_headers = ["日期", "单号", "商品", "数量", "单价", "原因"] for col, name in enumerate(issue_headers, 1): ws_issue.cell(3, col, name) style_header(ws_issue, 3, 6) r = 4 for date, order_id, product, qty, price, reason, _line in issues: values = [date, order_id, product, qty, price, reason] for c, val in enumerate(values, 1): cell = ws_issue.cell(r, c, val) cell.fill = ALERT_FILL cell.border = THIN r += 1 if not issues: ws_issue["A4"] = "没有发现异常" autosize(ws_issue, min_width=12) ws_raw = wb.create_sheet("原始明细") raw_headers = ["日期", "单号", "商品", "数量", "单价"] for col, name in enumerate(raw_headers, 1): ws_raw.cell(1, col, name) style_header(ws_raw, 1, 5) for i, row in enumerate(rows, start=2): for c, val in enumerate((list(row) + [None] * 5)[:5], 1): cell = ws_raw.cell(i, c, val) cell.border = THIN autosize(ws_raw) wb.save(output_path) print(f"已生成：{output_path}") print(f"有效订单 {valid_orders} 笔，合计 {total_amount:.2f} 元，异常 {len(issues)} 条") def build_sample_input(path=INPUT): wb = Workbook() ws = wb.active ws.title = "订单" headers = ["日期", "单号", "商品", "数量", "单价"] ws.append(headers) data = [ ("2026-10-03", "A1001", "原味吐司", 12, 8.5), ("2026-10-03", "A1002", "豆沙包", 20, 3), ("2026-10-05", "A1003", "原味吐司", 8, 8.5), ("2026-10-06", "A1004", "奶酪包", 6, 7), ("2026-10-08", "A1002", "豆沙包", 10, 3), # 重复单号 ("2026-10-09", "A1005", "肉松卷", 15, None), # 空单价 ("2026-10-11", "A1006", "奶酪包", 9, 7), ("2026-10-14", "A1007", "原味吐司", 16, 8.5), ("2026-10-18", "A1008", "肉松卷", 7, 12), ("2026-10-21", "A1009", "豆沙包", 30, 3), ("2026-10-24", "A1010", "奶酪包", None, 7), # 缺数量 ("2026-10-27", "A1011", "原味吐司", 11, 8.5), ("2026-10-29", "A1012", "肉松卷", 5, 12), ] for row in data: ws.append(list(row)) for col in range(1, 6): ws.cell(1, col).font = HEAD_FONT ws.cell(1, col).fill = HEADER_FILL for col in range(1, 6): ws.column_dimensions[get_column_letter(col)].width = 14 wb.save(path) if __name__ == "__main__": if not INPUT.exists(): build_sample_input() summarize()