AI+pandas自动出报表:Excel一键变可视化周报

import pandas as pd

df = pd.read_excel("sales_aug_2026.xlsx")df["Revenue"] = df["Units"] * df["UnitPrice"]

by_region = (df.groupby("Region", as_index=False)["Revenue"].sum().sort_values("Revenue", ascending=False))by_product = (df.groupby("Product", as_index=False).agg(Units=("Units","sum"), Revenue=("Revenue","sum")))kpis = {"总营收": int(df["Revenue"].sum()),"总销量": int(df["Units"].sum()),"订单数": len(df),"头部区域": by_region.iloc[0]["Region"],}print(by_region.to_string(index=False))print(kpis)```

```pythonfrom openpyxl import Workbookfrom openpyxl.chart import BarChart, Referencefrom openpyxl.styles import Fontwb = Workbook()ws = wb.activews.title = "Summary"ws.append(["指标","数值"])for k, v in kpis.items():ws.append([k, v])ws["A1"].font = Font(bold=True)

start = ws.max_row + 2ws.cell(start, 1, "区域"); ws.cell(start, 2, "营收")for i, row in by_region.iterrows():ws.cell(start+1+i, 1, row["Region"])ws.cell(start+1+i, 2, int(row["Revenue"]))chart = BarChart()data = Reference(ws, min_col=2, min_row=start,max_row=start+len(by_region))cats = Reference(ws, min_col=1, min_row=start+1,max_row=start+len(by_region))chart.add_data(data, titles_from_data=False)chart.set_categories(cats)chart.title = "各区域营收"ws.add_chart(chart, "D2")wb.save("周报_销售.xlsx")```

```pythonfrom openai import OpenAIimport os, jsonclient = OpenAI()  # 环境变量 OPENAI_API_KEYdef write_summary(kpis: dict, by_region: pd.DataFrame) -> str:brief = {"kpis": kpis,"region_rank": by_region.to_dict("records")}resp = client.chat.completions.create(model="gpt-4o-mini",messages=[{"role":"system","content":"你是商业分析师，基于提供的聚合KPI写3句话执行摘要，""不编造、不出现具体客户名。"},{"role":"user","content": f"数据：{json.dumps(brief, ensure_ascii=False)}"}],temperature=0.3)return resp.choices[0].message.contentprint(write_summary(kpis, by_region))```