你的任务是什么？
│
├─ 操作 Excel 本身（改格式、生成图表、做透视表、做交互窗体）
│   └─ 你在意格式的精确控制吗？
│       ├─ 是 → VBA（或 Python + VBA 混合）
│       └─ 否 → Python（openpyxl）
│
├─ 处理数据（清洗、合并、计算、分析）
│   └─ 数据量多大？
│       ├─ < 10 万行，且是一次性 → Excel 手工/公式也许够
│       ├─ 任意量，需要重复执行 → Python（pandas）
│       └─ 在 Excel 内完成，且要给用户一个"按钮" → VBA
│
├─ 跨系统（连数据库、调 API、读写文件、发邮件）
│   └─ Python
│
├─ 统计分析（回归、检验、时间序列、机器学习）
│   └─ Python
│
└─ 给不懂技术的同事用
    ├─ 他只会 Excel → 做成带按钮的 Excel 文件（VBA）或独立的 exe
    └─ 能用浏览器 → 做成 Web 应用（Python + Streamlit/Gradio）

Python 负责"重活"：
  - 从数据库取数
  - 清洗、合并、计算（pandas）
  - 生成分析结果

Excel/VBA 负责"交付"：
  - 接收 Python 输出的数据
  - 用 Excel 的格式、透视表、图表呈现
  - 给用户熟悉的界面和交互

[数据库/ERP/MES] 
      ↓ Python（定时运行）
[中间数据 CSV/Excel]
      ↓ Excel（Power Query 连接）
[报表/看板]

Sub Macro1()
    Range("A1:D10").Select
    Selection.Borders.LineStyle = xlContinuous
    Range("A1:D1").Select
    Selection.Font.Bold = True
End Sub

' 优化后（去掉 Select）
Sub FormatTable()
    With Range("A1:D10")
        .Borders.LineStyle = xlContinuous
    End With
    Range("A1:D1").Font.Bold = True
End Sub

Application（Excel 应用）
  └── Workbook（工作簿）
        └── Worksheet（工作表）
              └── Range（单元格区域）
                    ├── Cells(row, col)
                    ├── Value（值）
                    ├── Formula（公式）
                    ├── Interior.Color（背景色）
                    └── Font（字体）

' 引用单元格的几种方式
Range("A1")              ' A1 单元格
Range("A1:B10")          ' 区域
Cells(1, 1)              ' 第1行第1列 = A1
Cells(1, "A")            ' 同上
Range("A1", "B10")       ' 起止区域
[A1]                     ' 简写

' 常用操作
Range("A1").Value = 100              ' 写值
x = Range("A1").Value                ' 读值
Range("A1").Formula = "=SUM(B1:B10)" ' 写公式
Range("A1:D1").Font.Bold = True      ' 加粗
Range("A1:D1").Interior.Color = RGB(200, 200, 200)  ' 背景色
Range("A:A").ColumnWidth = 12        ' 列宽
Range("A1").NumberFormat = "0.00%"   ' 数字格式
Range("A1").ClearContents            ' 清除内容
Range("A1").EntireRow.Delete         ' 删除整行

' 找到最后一行（极其常用）
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column

Sub Demo()
    ' 变量声明
    Dim ws As Worksheet
    Dim lastRow As Long, i As Long
    Dim total As Double
    Dim name As String
    
    Set ws = ThisWorkbook.Sheets("数据表")   ' 对象赋值要用 Set
    
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' For 循环
    total = 0
    For i = 2 To lastRow          ' 从第2行到最后一行（跳过表头）
        total = total + ws.Cells(i, 3).Value
    Next i
    
    ' 判断
    For i = 2 To lastRow
        If ws.Cells(i, 3).Value > 1000 Then
            ws.Cells(i, 4).Value = "高"
        ElseIf ws.Cells(i, 3).Value > 500 Then
            ws.Cells(i, 4).Value = "中"
        Else
            ws.Cells(i, 4).Value = "低"
        End If
    Next i
    
    ' For Each 循环
    Dim cell As Range
    For Each cell In ws.Range("A2:A100")
        If IsEmpty(cell) Then cell.Interior.Color = vbYellow
    Next cell
    
    ' Do While
    i = 2
    Do While ws.Cells(i, 1).Value <> ""
        i = i + 1
    Loop
End Sub

' 慢：逐格读写
For i = 1 To 10000
    ws.Cells(i, 2).Value = ws.Cells(i, 1).Value * 2
Next i

' 快：一次性读入数组，处理，一次性写回
Dim arr As Variant
arr = ws.Range("A1:A10000").Value    ' 一次性读入（二维数组）

Dim i As Long
For i = 1 To UBound(arr, 1)
    arr(i, 1) = arr(i, 1) * 2
Next i

ws.Range("B1:B10000").Value = arr    ' 一次性写回

Sub FastMacro()
    Application.ScreenUpdating = False   ' 关闭屏幕刷新（最重要）
    Application.Calculation = xlCalculationManual  ' 手动计算
    Application.EnableEvents = False     ' 关闭事件触发
    Application.DisplayAlerts = False    ' 关闭警告提示
    
    ' ... 你的代码 ...
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.DisplayAlerts = True
End Sub

Sub SafeMacro()
    On Error GoTo CleanUp
    Application.ScreenUpdating = False
    
    ' ... 代码 ...
    
CleanUp:
    Application.ScreenUpdating = True
    If Err.Number <> 0 Then MsgBox "出错: " & Err.Description
End Sub

' 放在工作表代码页（如 Sheet1）
Private Sub Worksheet_Change(ByVal Target As Range)
    ' 当 A 列变化时，自动记录修改时间到 B 列
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        Application.EnableEvents = False
        Cells(Target.Row, 2).Value = Now
        Application.EnableEvents = True
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' 选中单元格时执行
End Sub

' 放在 ThisWorkbook 代码页
Private Sub Workbook_Open()
    ' 打开文件时执行（如自动刷新数据）
    MsgBox "欢迎使用生产报表模板"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' 关闭前执行（如自动保存备份）
End Sub

' 按钮点击事件
Private Sub cmdRun_Click()
    Dim startDate As Date
    startDate = CDate(txtStartDate.Value)
    
    If Not IsDate(txtStartDate.Value) Then
        MsgBox "请输入有效日期", vbExclamation
        Exit Sub
    End If
    
    Call GenerateReport(startDate)
    MsgBox "报表生成完成", vbInformation
    Unload Me
End Sub

' ① 遍历文件夹里的所有 Excel 文件并合并
Sub MergeFiles()
    Dim fso As Object, folder As Object, file As Object
    Dim wb As Workbook, ws As Worksheet
    Dim targetRow As Long
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set folder = fso.GetFolder("D:\数据\")
    
    targetRow = 2
    For Each file In folder.Files
        If LCase(fso.GetExtensionName(file.Name)) = "xlsx" Then
            Set wb = Workbooks.Open(file.Path)
            Set ws = wb.Sheets(1)
            
            ' 复制数据
            ws.Range("A2:D100").Copy
            ThisWorkbook.Sheets("汇总").Cells(targetRow, 1).PasteSpecial xlPasteValues
            targetRow = targetRow + 99
            
            wb.Close False
        End If
    Next file
End Sub

' ② 拆分工作表为多个文件（按某列的值）
Sub SplitByColumn()
    Dim ws As Worksheet, dict As Object
    Dim lastRow As Long, i As Long, key As String
    
    Set ws = ThisWorkbook.Sheets("数据")
    Set dict = CreateObject("Scripting.Dictionary")
    
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' 按 A 列分组
    For i = 2 To lastRow
        key = ws.Cells(i, 1).Value
        If Not dict.Exists(key) Then dict.Add key, New Collection
        dict(key).Add i
    Next i
    
    ' 为每个 key 创建新表
    Dim k As Variant, newWs As Worksheet, r As Variant, outRow As Long
    For Each k In dict.Keys
        Set newWs = ThisWorkbook.Sheets.Add
        newWs.Name = Left(CStr(k), 30)
        ws.Rows(1).Copy newWs.Rows(1)   ' 复制表头
        outRow = 2
        For Each r In dict(k)
            ws.Rows(r).Copy newWs.Rows(outRow)
            outRow = outRow + 1
        Next r
    Next k
End Sub

' ③ 自动生成图表
Sub MakeChart()
    Dim cht As Chart
    Set cht = ThisWorkbook.Charts.Add
    With cht
        .SetSourceData Source:=Range("数据!A1:B20")
        .ChartType = xlLine
        .HasTitle = True
        .ChartTitle.Text = "产量趋势"
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Text = "日期"
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Text = "产量"
    End With
End Sub

' ④ 导出为 PDF
Sub ExportPDF()
    ThisWorkbook.Sheets("报表").ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:="D:\输出\生产周报_" & Format(Date, "yyyymmdd") & ".pdf", _
        Quality:=xlQualityStandard
End Sub

' ⑤ 发送邮件（Outlook）
Sub SendMail()
    Dim olApp As Object, olMail As Object
    Set olApp = CreateObject("Outlook.Application")
    Set olMail = olApp.CreateItem(0)
    
    With olMail
        .To = "manager@company.com"
        .CC = "team@company.com"
        .Subject = "生产周报 - " & Format(Date, "yyyy-mm-dd")
        .Body = "请查收附件。"
        .Attachments.Add "D:\输出\生产周报.pdf"
        .Send    ' 或 .Display 先预览
    End With
End Sub

conda create -n ie python=3.11
conda activate ie
conda install pandas openpyxl numpy matplotlib
# 或
pip install pandas openpyxl numpy matplotlib

import pandas as pd

# 读取
df = pd.read_excel("生产数据.xlsx", sheet_name="报工")
df = pd.read_csv("数据.csv", encoding="utf-8-sig")   # 中文 CSV 注意编码
df = pd.read_sql("SELECT * FROM mes_report", conn)

# 查看
df.head()          # 前 5 行
df.tail(10)        # 后 10 行
df.info()          # 列类型、非空数
df.describe()      # 数值列的统计摘要
df.shape           # (行数, 列数)
df.columns         # 列名
df.dtypes          # 数据类型

# 选择
df["产量"]                      # 一列（Series）
df[["产量", "良品数"]]           # 多列
df.loc[0:10, ["产量", "日期"]]   # 按标签选择
df.iloc[0:10, 0:3]              # 按位置选择

# 筛选
df[df["产量"] > 1000]
df[(df["产线"] == "A线") & (df["产量"] > 1000)]
df[df["产品"].isin(["A001", "A002"])]
df[df["备注"].isna()]                    # 空值
df[~df["备注"].isna()]                   # 非空
df[df["工单号"].str.contains("WO2026")]  # 包含

# 新增/修改列
df["良率"] = df["良品数"] / (df["良品数"] + df["不良数"])
df["日期"] = pd.to_datetime(df["日期"])
df["月份"] = df["日期"].dt.month
df["是否延期"] = df["实际完工"] > df["计划完工"]

# 分组聚合
df.groupby("产线")["产量"].sum()
df.groupby(["产线", "月份"])["产量"].agg(["sum", "mean", "count"])
df.groupby("产线").agg({
    "产量": "sum",
    "不良数": "sum",
    "工时": "mean"
})

# 透视表
pd.pivot_table(df, values="产量", index="产线", columns="月份", aggfunc="sum")

# 排序
df.sort_values("产量", ascending=False)
df.sort_values(["产线", "日期"])

# 合并
pd.merge(df1, df2, on="工单号", how="left")    # 类似 SQL JOIN
pd.concat([df1, df2, df3])                      # 纵向拼接

# 处理缺失
df.dropna()                          # 删除含空值的行
df.dropna(subset=["产量"])            # 只删除"产量"为空的行
df.fillna(0)                         # 填充为 0
df["产量"] = df["产量"].fillna(df["产量"].mean())   # 均值填充

# 去重
df.drop_duplicates()
df.drop_duplicates(subset=["工单号"], keep="last")

# 类型转换
df["产量"] = df["产量"].astype(int)
df["日期"] = pd.to_datetime(df["日期"], errors="coerce")  # 转换失败的变 NaT

# 重命名列
df.rename(columns={"旧名": "新名"})

# 应用函数
df["等级"] = df["产量"].apply(lambda x: "高" if x > 1000 else "低")
df["良率"] = df.apply(lambda row: row["良品"] / row["总数"], axis=1)

# 写出
df.to_excel("输出.xlsx", index=False)
df.to_csv("输出.csv", index=False, encoding="utf-8-sig")

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.chart import LineChart, BarChart, Reference

# 新建工作簿
wb = Workbook()
ws = wb.active
ws.title = "周报"

# 写数据（从 DataFrame）
df = pd.read_excel("源数据.xlsx")
for r in dataframe_to_rows(df, index=False, header=True):
    ws.append(r)

# 样式
header_font = Font(bold=True, color="FFFFFF", size=11)
header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
thin_border = Border(
    left=Side(style="thin"), right=Side(style="thin"),
    top=Side(style="thin"), bottom=Side(style="thin")
)

# 应用表头样式
for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal="center", vertical="center")

# 应用边框
for row in ws.iter_rows(min_row=1, max_row=ws.max_row, max_col=ws.max_column):
    for cell in row:
        cell.border = thin_border

# 列宽
ws.column_dimensions["A"].width = 15
for col in ws.columns:
    max_len = max(len(str(c.value or "")) for c in col)
    ws.column_dimensions[col[0].column_letter].width = min(max_len + 2, 30)

# 数字格式
for cell in ws["D"][1:]:
    cell.number_format = "0.00%"

# 条件格式：低于目标的标红
from openpyxl.formatting.rule import CellIsRule
ws.conditional_formatting.add(
    "D2:D100",
    CellIsRule(operator="lessThan", formula=["0.95"],
               fill=PatternFill(start_color="FFC7CE"))
)

# 冻结首行
ws.freeze_panes = "A2"

# 图表
chart = LineChart()
chart.title = "产量趋势"
chart.y_axis.title = "产量"
chart.x_axis.title = "日期"
data = Reference(ws, min_col=2, min_row=1, max_row=ws.max_row)
cats = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, "H2")

# 保存
wb.save("生产周报.xlsx")

import pandas as pd
import numpy as np

# ① 读入并初查
df = pd.read_excel("原始数据.xlsx")
print(df.shape, df.columns.tolist())
print(df.isna().sum())          # 各列缺失数
print(df.duplicated().sum())    # 重复行数

# ② 清理列名（去空格、统一命名）
df.columns = df.columns.str.strip()
df = df.rename(columns={"产 量": "产量", "日期时间": "日期"})

# ③ 删除无关列
df = df[["工单号", "产线", "产品", "日期", "产量", "不良数", "工时"]]

# ④ 类型转换
df["日期"] = pd.to_datetime(df["日期"], errors="coerce")
df["产量"] = pd.to_numeric(df["产量"], errors="coerce")
df["产线"] = df["产线"].astype(str).str.strip().str.upper()

# ⑤ 处理缺失与异常
df = df.dropna(subset=["产量", "日期"])     # 关键字段缺失就删
df = df[df["产量"] > 0]                     # 删除非正值
df = df[df["产量"] < df["产量"].quantile(0.999)]  # 删除极端值（或用业务规则）

# ⑥ 去重与标准化
df = df.drop_duplicates(subset=["工单号", "日期"], keep="last")
df["产线"] = df["产线"].replace({"A": "A线", "A线 ": "A线"})  # 统一取值

# ⑦ 派生字段与校验
df["良率"] = df["产量"] / (df["产量"] + df["不良数"])
assert df["良率"].between(0, 1).all(), "良率超出 [0,1] 范围！"
print(f"清洗完成：{len(df)} 行")

import pandas as pd
import glob
import os

# 找到所有文件
files = glob.glob(r"D:\MES导出\*.xlsx")
print(f"找到 {len(files)} 个文件")

# 逐个读取并合并
dfs = []
for f in files:
    df = pd.read_excel(f, sheet_name="报工")
    df["来源文件"] = os.path.basename(f)   # 记录来源，便于追溯
    dfs.append(df)
    print(f"  读取 {os.path.basename(f)}: {len(df)} 行")

combined = pd.concat(dfs, ignore_index=True)
print(f"合并后共 {len(combined)} 行")

# 去重（如果文件之间有重叠）
combined = combined.drop_duplicates(subset=["工单号", "报工时间"])

# 输出
combined.to_excel(r"D:\输出\月度报工汇总.xlsx", index=False)

import pandas as pd

# 读入新旧 BOM
old = pd.read_excel("BOM_旧.xlsx")
new = pd.read_excel("BOM_新.xlsx")

# 统一关键字段
for df in (old, new):
    df["物料编码"] = df["物料编码"].astype(str).str.strip()

# 外连接，标记来源
merged = old.merge(new, on="物料编码", how="outer",
                   suffixes=("_旧", "_新"), indicator=True)

# 分类差异
新增 = merged[merged["_merge"] == "right_only"]
删除 = merged[merged["_merge"] == "left_only"]
共有 = merged[merged["_merge"] == "both"].copy()

# 数量变化
共有["数量变化"] = 共有["用量_新"] - 共有["用量_旧"]
数量变化 = 共有[共有["数量变化"] != 0]

# 输出报告
with pd.ExcelWriter("BOM_差异报告.xlsx") as writer:
    新增.to_excel(writer, sheet_name="新增物料", index=False)
    删除.to_excel(writer, sheet_name="删除物料", index=False)
    数量变化.to_excel(writer, sheet_name="用量变化", index=False)

print(f"新增 {len(新增)} 项，删除 {len(删除)} 项，用量变化 {len(数量变化)} 项")

import pandas as pd
import numpy as np

df = pd.read_excel("工时原始数据.xlsx")

# 记录处理过程
log = {"原始": len(df)}

# 1. 删除测试记录
df = df[~df["工单号"].astype(str).str.contains("TEST|测试|试用", na=False)]
log["删除测试"] = len(df)

# 2. 删除关键字段为空
df = df.dropna(subset=["工时秒", "操作人"])
log["删除空值"] = len(df)

# 3. 删除非正值
df = df[df["工时秒"] > 0]
log["删除非正"] = len(df)

# 4. 用 IQR 方法剔除离群值（按产品-工序分组）
def remove_outliers(group):
    q1 = group["工时秒"].quantile(0.25)
    q3 = group["工时秒"].quantile(0.75)
    iqr = q3 - q1
    lower = q1 - 1.5 * iqr
    upper = q3 + 1.5 * iqr
    return group[(group["工时秒"] >= lower) & (group["工时秒"] <= upper)]

df = df.groupby(["产品编码", "工序"], group_keys=False).apply(remove_outliers)
log["剔除离群"] = len(df)

# 5. 剔除样本量不足的组（<10 条）
counts = df.groupby(["产品编码", "工序"])["工时秒"].transform("count")
df = df[counts >= 10]
log["样本不足"] = len(df)

# 6. 计算标准工时（用中位数更稳健）
std_time = df.groupby(["产品编码", "工序"])["工时秒"].agg([
    ("样本数", "count"),
    ("均值", "mean"),
    ("中位数", "median"),
    ("标准差", "std"),
    ("变异系数", lambda x: x.std() / x.mean())
]).round(2)

# 建议标准工时 = 中位数 + 宽放
std_time["建议标准工时秒"] = (std_time["中位数"] * 1.15).round(1)   # 15% 宽放

# 输出
with pd.ExcelWriter("标准工时分析.xlsx") as writer:
    df.to_excel(writer, sheet_name="清洗后数据", index=False)
    std_time.to_excel(writer, sheet_name="标准工时")
    pd.Series(log).to_excel(writer, sheet_name="清洗日志", header=["剩余行数"])

print(log)

import os
import re
from pathlib import Path

src_dir = Path(r"D:\工艺文件")
dst_dir = Path(r"D:\工艺文件_已整理")
dst_dir.mkdir(exist_ok=True)

# 读入映射表（旧名 -> 新名）
mapping = pd.read_excel("重命名映射表.xlsx")
mapping_dict = dict(zip(mapping["原文件名"], mapping["新文件名"]))

renamed, skipped = 0, 0
for file in src_dir.glob("*.pdf"):
    stem = file.stem   # 不含扩展名的文件名
    
    # 从文件名提取编码（如 "DWG-A001-Rev2" 中的 A001）
    match = re.search(r"([A-Z]{2}\d{4})", stem)
    
    if stem in mapping_dict:
        new_name = mapping_dict[stem] + file.suffix
    elif match:
        code = match.group(1)
        new_name = f"{code}_工艺文件{file.suffix}"
    else:
        print(f"  跳过（无法识别）: {file.name}")
        skipped += 1
        continue
    
    # 处理重名
    target = dst_dir / new_name
    if target.exists():
        target = dst_dir / f"{Path(new_name).stem}_副本{file.suffix}"
    
    file.rename(target)
    renamed += 1

print(f"重命名 {renamed} 个，跳过 {skipped} 个")

import pandas as pd
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.application import MIMEApplication
from datetime import datetime, timedelta
import schedule
import time

def generate_report():
    """生成上周的生产周报"""
    end = datetime.now()
    start = end - timedelta(days=7)
    
    # 取数
    df = pd.read_sql(
        f"SELECT * FROM v_production_daily WHERE stat_date >= '{start:%Y-%m-%d}'",
        conn
    )
    
    # 计算指标
    summary = df.groupby("产线").agg({
        "产量": "sum",
        "不良数": "sum",
        "运行时间": "sum",
        "停机时间": "sum"
    })
    summary["良率"] = summary["产量"] / (summary["产量"] + summary["不良数"])
    summary["OEE"] = ...  # 按公式计算
    
    # 生成 Excel（用模板）
    from openpyxl import load_workbook
    wb = load_workbook("周报模板.xlsx")
    ws = wb["数据"]
    for r in dataframe_to_rows(summary.reset_index(), index=False, header=False):
        ws.append(r)
    
    output = f"生产周报_{end:%Y%m%d}.xlsx"
    wb.save(output)
    
    return output, summary

def send_report(attachment, summary):
    """发送邮件"""
    msg = MIMEMultipart()
    msg["From"] = "ie@company.com"
    msg["To"] = "manager@company.com; team@company.com"
    msg["Subject"] = f"生产周报 - {datetime.now():%Y-%m-%d}"
    
    # 正文（把关键指标写进正文，不用打开附件就知道概况）
    body = f"""
    <html><body>
    <h3>上周生产概况</h3>
    <table border="1" cellpadding="5">
    <tr><th>产线</th><th>产量</th><th>良率</th><th>OEE</th></tr>
    {"".join(f"<tr><td>{idx}</td><td>{row['产量']:.0f}</td>"
             f"<td>{row['良率']:.2%}</td><td>{row['OEE']:.1%}</td></tr>"
             for idx, row in summary.iterrows())}
    </table>
    <p>详见附件。</p>
    </body></html>
    """
    msg.attach(MIMEText(body, "html", "utf-8"))
    
    # 附件
    with open(attachment, "rb") as f:
        part = MIMEApplication(f.read())
        part.add_header("Content-Disposition", "attachment", 
                       filename=attachment)
        msg.attach(part)
    
    # 发送
    with smtplib.SMTP("smtp.company.com", 25) as server:
        server.login("ie@company.com", "password")
        server.send_message(msg)
    print(f"已发送: {attachment}")

def job():
    print(f"[{datetime.now()}] 开始生成周报...")
    try:
        attachment, summary = generate_report()
        send_report(attachment, summary)
        print("完成")
    except Exception as e:
        print(f"出错: {e}")
        # 出错时也要发通知
        # send_error_notification(str(e))

# 定时任务：每周一早上 8:00
schedule.every().monday.at("08:00").do(job)

# 或者直接用操作系统的定时任务（更可靠）
# Windows: 任务计划程序
# Linux: cron

print("定时任务已启动，等待触发...")
while True:
    schedule.run_pending()
    time.sleep(60)

[数据源]
   ↓ Python 脚本（定时运行）
[中间数据.csv / 数据.xlsx]  ← 放在共享盘
   ↓ Excel 用 Power Query 连接（自动刷新）
[报表/看板.xlsx]  ← 用户在 Excel 里看、切片、透视

Sub RunPython()
    Dim pythonPath As String, scriptPath As String
    Dim shellCmd As String
    
    pythonPath = "C:\Users\xxx\miniconda3\envs\ie\python.exe"
    scriptPath = "D:\scripts\generate_report.py"
    
    shellCmd = """" & pythonPath & """ """ & scriptPath & """"
    
    ' 同步执行，等待完成
    Dim wsh As Object
    Set wsh = CreateObject("WScript.Shell")
    Dim exitCode As Integer
    exitCode = wsh.Run(shellCmd, 1, True)   ' 1=显示窗口, True=等待
    
    If exitCode = 0 Then
        ' 刷新 Excel 数据连接
        ThisWorkbook.RefreshAll
        MsgBox "报表已更新", vbInformation
    Else
        MsgBox "Python 脚本执行失败，错误码: " & exitCode, vbCritical
    End If
End Sub

import xlwings as xw

# 连接已打开的 Excel，或打开新实例
wb = xw.Book("报表.xlsx")
# 或 app = xw.App(visible=True); wb = app.books.open(...)

# 读写单元格
sheet = wb.sheets["数据"]
sheet.range("A1").value = "产量"
sheet.range("A2").value = [[1, 2], [3, 4]]   # 写二维数组

# 读入 DataFrame
df = sheet.range("A1").expand().options(pd.DataFrame).value

# 写 DataFrame
sheet.range("A1").value = df

# 调用 Excel 公式
sheet.range("D2").formula = "=B2/C2"

# 运行 VBA 宏
macro = wb.macro("GenerateChart")
macro()

# 保存
wb.save()

年度耗时 = 单次耗时 × 年频次
开发投入 = 学习 + 编写 + 调试
回收期 = 开发投入 / 年度耗时