Python 操作 Excel——openpyxl 与 pandas 完全指南

第6章：Python 操作 Excel——openpyxl 与 pandas 实战

Excel 是职场里使用最广泛的数据载体，也是 Python 自动化最高价值的战场之一。100 个分店的销售数据分散在 100 个 Excel 文件里？手动合并要一天，Python 脚本 3 分钟搞定。格式化月报要改几十个单元格样式？代码写一次，永久复用。本章系统讲解 Python 操作 Excel 的四大工具——openpyxl、pandas、xlwings，以及它们的最佳组合方式，最后用一个完整的销售数据自动化管道项目收尾。

库选择指南：用哪个库做什么

Python 生态里有多个操作 Excel 的库，各有擅长场景。先搞清楚选哪个，避免走弯路。

决策树：

• 需要数据分析、合并、统计？ - 需要漂亮的格式化输出报告？ - 需要和正在运行的 Excel 程序交互？ - 需要读取老版 .xls 文件？

安装命令： pip install openpyxl pandas xlwings openpyxl pandas 读写 Excel 依赖 openpyxl（.xlsx）或 xlrd（.xls），安装 pandas 时建议同时安装 openpyxl。

openpyxl 精讲

读写工作表与单元格操作

openpyxl 基础读写操作

import openpyxl
from pathlib import Path

# ---- 创建新工作簿 ----
wb = openpyxl.Workbook()
ws = wb.active          # 获取默认 Sheet
ws.title = "销售数据"    # 重命名

# 写入单元格（两种方式等价）
ws["A1"] = "产品名称"
ws.cell(row=1, column=2, value="销售额")  # row/column 从 1 开始

# 写入一整行数据
ws.append(["笔记本电脑", 15000, "2024-03-01"])
ws.append(["无线鼠标",    299,   "2024-03-01"])

# 保存
wb.save("sales.xlsx")

# ---- 读取已有工作簿 ----
wb = openpyxl.load_workbook("sales.xlsx")

# 获取工作表
ws = wb.active                    # 活动 Sheet
ws = wb["销售数据"]                # 按名称获取
print(wb.sheetnames)              # 所有 Sheet 名称列表

# 读取单元格
print(ws["A1"].value)             # "产品名称"
print(ws.cell(row=2, column=1).value)  # "笔记本电脑"

# 遍历所有有数据的行
for row in ws.iter_rows(min_row=2, values_only=True):
    # values_only=True 直接返回值，而非 Cell 对象
    product, amount, date = row
    print(f"{product}: {amount}")

# 读取区域（返回 Cell 对象的二维元组）
for row in ws["A1:C5"]:
    for cell in row:
        print(cell.value, end="\t")
    print()

# 工作表尺寸
print(f"最大行：{ws.max_row}  最大列：{ws.max_column}")

样式设置：字体、颜色、边框、对齐

openpyxl 样式系统

import openpyxl
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment, numbers

wb = openpyxl.Workbook()
ws = wb.active
ws.title = "月度报告"

# ---- 字体样式 ----
title_font = Font(
    name="微软雅黑",    # 字体名称
    size=14,           # 字号
    bold=True,         # 加粗
    color="FFFFFF",    # 白色（十六进制，不含 #）
)
ws["A1"].font = title_font

# ---- 背景填充 ----
header_fill = PatternFill(
    fill_type="solid",
    fgColor="4472C4"   # 蓝色背景
)
ws["A1"].fill = header_fill

# ---- 边框 ----
thin = Side(style="thin")
thick = Side(style="medium")
border = Border(left=thin, right=thin, top=thick, bottom=thick)
ws["A1"].border = border

# ---- 对齐 ----
ws["A1"].alignment = Alignment(
    horizontal="center",   # 水平居中
    vertical="center",     # 垂直居中
    wrap_text=True         # 自动换行
)

# ---- 数字格式 ----
ws["B2"].number_format = '#,##0.00'    # 千分位 + 两位小数：1,234.56
ws["C2"].number_format = '0.00%'       # 百分比：12.34%
ws["D2"].number_format = 'yyyy-mm-dd'  # 日期格式

# ---- 批量设置表头样式（实用函数）----
def style_header_row(ws, row: int, bg_color: str = "4472C4", font_color: str = "FFFFFF"):
    """将指定行设置为蓝底白字的表头样式"""
    for cell in ws[row]:
        if cell.value is None:
            continue
        cell.font = Font(name="微软雅黑", bold=True, color=font_color, size=11)
        cell.fill = PatternFill(fill_type="solid", fgColor=bg_color)
        cell.alignment = Alignment(horizontal="center", vertical="center")
        thin = Side(style="thin")
        cell.border = Border(left=thin, right=thin, top=thin, bottom=thin)

# ---- 调整列宽（自适应内容） ----
def auto_width(ws, extra: int = 4):
    """自动调整每列宽度为内容最大长度 + extra"""
    for col in ws.columns:
        max_len = 0
        col_letter = col[0].column_letter
        for cell in col:
            if cell.value:
                # 中文字符宽度约为英文的 2 倍
                text = str(cell.value)
                length = sum(2 if ord(c) > 127 else 1 for c in text)
                max_len = max(max_len, length)
        ws.column_dimensions[col_letter].width = max_len + extra

wb.save("styled_report.xlsx")

合并单元格与插入图片

合并单元格 / 插入图片

from openpyxl.drawing.image import Image as XLImage

# 合并单元格（标题横跨 A1:E1）
ws.merge_cells("A1:E1")
ws["A1"] = "2024年第一季度销售报告"
ws["A1"].alignment = Alignment(horizontal="center", vertical="center")

# 取消合并
# ws.unmerge_cells("A1:E1")

# 设置行高
ws.row_dimensions[1].height = 36   # 像素单位

# 插入图片（如公司 logo）
img = XLImage("logo.png")
img.width = 120   # 像素
img.height = 50
ws.add_image(img, "F1")  # 锚定到 F1 单元格左上角

# 写入公式（openpyxl 写入公式字符串，不计算结果）
ws["B10"] = "=SUM(B2:B9)"   # Excel 打开时会自动计算
ws["C10"] = "=AVERAGE(C2:C9)"

**重要：openpyxl 不计算公式。**当你用 openpyxl 写入 ="=SUM(B2:B9)" 时，Python 不会执行计算，保存的是公式字符串本身。用户在 Excel 中打开文件时，Excel 才会计算。如果你需要在 Python 里读取公式的计算结果，改用 data_only=True 参数打开文件，但这要求文件上次被 Excel 保存时已经计算过。

完整案例：生成格式化的月度报告

generate_monthly_report.py

"""生成格式化的月度销售报告（含表头样式、数字格式、汇总行）"""
import openpyxl
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
from openpyxl.utils import get_column_letter
import datetime

SALES_DATA = [
    ("华北区", "笔记本电脑", 45, 6750000),
    ("华北区", "无线鼠标",   120, 35880),
    ("华南区", "笔记本电脑", 32, 4800000),
    ("华南区", "显示器",     78, 1716000),
    ("华东区", "笔记本电脑", 67, 10050000),
    ("华东区", "键盘",       200, 199800),
]

HEADERS = ["区域", "产品", "销量（台）", "销售额（元）", "平均单价（元）"]

def make_border(style="thin"):
    s = Side(style=style)
    return Border(left=s, right=s, top=s, bottom=s)

def make_report(output_path: str) -> None:
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "月度销售报告"

    # --- 大标题行 ---
    ws.merge_cells("A1:E1")
    title_cell = ws["A1"]
    month_str = datetime.date.today().strftime("%Y年%m月")
    title_cell.value = f"{month_str} 销售报告"
    title_cell.font = Font(name="微软雅黑", size=16, bold=True, color="FFFFFF")
    title_cell.fill = PatternFill(fill_type="solid", fgColor="2F5496")
    title_cell.alignment = Alignment(horizontal="center", vertical="center")
    ws.row_dimensions[1].height = 40

    # --- 表头行 ---
    for col, header in enumerate(HEADERS, start=1):
        cell = ws.cell(row=2, column=col, value=header)
        cell.font = Font(name="微软雅黑", bold=True, color="FFFFFF", size=11)
        cell.fill = PatternFill(fill_type="solid", fgColor="4472C4")
        cell.alignment = Alignment(horizontal="center", vertical="center")
        cell.border = make_border()
    ws.row_dimensions[2].height = 24

    # --- 数据行 ---
    for row_idx, (region, product, qty, amount) in enumerate(SALES_DATA, start=3):
        avg_price = amount / qty if qty else 0
        row_data = [region, product, qty, amount, avg_price]
        for col_idx, value in enumerate(row_data, start=1):
            cell = ws.cell(row=row_idx, column=col_idx, value=value)
            cell.border = make_border()
            cell.alignment = Alignment(vertical="center")
            # 数字列格式
            if col_idx == 3:
                cell.number_format = '#,##0'
            if col_idx in (4, 5):
                cell.number_format = '#,##0.00'
        # 隔行背景
        if row_idx % 2 == 0:
            for col_idx in range(1, 6):
                ws.cell(row=row_idx, column=col_idx).fill = PatternFill(
                    fill_type="solid", fgColor="DCE6F1"
                )

    # --- 汇总行 ---
    summary_row = len(SALES_DATA) + 3
    ws.cell(row=summary_row, column=1, value="合计").font = Font(bold=True)
    # 写入 SUM 公式
    for col in [3, 4]:
        col_letter = get_column_letter(col)
        cell = ws.cell(
            row=summary_row, column=col,
            value=f"=SUM({col_letter}3:{col_letter}{summary_row - 1})"
        )
        cell.font = Font(bold=True)
        cell.number_format = '#,##0' if col == 3 else '#,##0.00'
        cell.fill = PatternFill(fill_type="solid", fgColor="D9E1F2")
        cell.border = make_border("medium")

    # --- 列宽 ---
    col_widths = [10, 16, 14, 18, 18]
    for i, width in enumerate(col_widths, start=1):
        ws.column_dimensions[get_column_letter(i)].width = width

    # --- 冻结首行 ---
    ws.freeze_panes = "A3"

    wb.save(output_path)
    print(f"报告已生成：{output_path}")

make_report("monthly_sales_report.xlsx")

pandas 数据处理

read_excel / to_excel 参数详解

pandas 读写 Excel 核心参数

import pandas as pd

# ---- 读取 Excel ----
# 基础读取
df = pd.read_excel("data.xlsx")

# 常用参数
df = pd.read_excel(
    "data.xlsx",
    sheet_name="Sheet1",      # 指定 Sheet 名或索引（0-based）；None 读取所有 Sheet 返回 dict
    header=0,                 # 第几行作为列名（0-based），None 表示无列名
    skiprows=2,               # 跳过前 2 行
    usecols="A:D",            # 只读 A-D 列，也可以写 [0,1,3] 或 ["产品","金额"]
    nrows=100,                # 只读前 100 行（提升性能）
    dtype={"订单号": str},     # 强制列类型（防止数字单号被转为 float）
    na_values=["N/A", "-"],   # 自定义 NA 值
    parse_dates=["日期"],      # 将指定列解析为 datetime
)

# 读取所有 Sheet
all_sheets = pd.read_excel("data.xlsx", sheet_name=None)  # 返回 {sheet_name: DataFrame}
for name, df in all_sheets.items():
    print(f"Sheet: {name}  行数: {len(df)}")

# ---- 写入 Excel ----
df.to_excel(
    "output.xlsx",
    sheet_name="结果",
    index=False,              # 不写行号索引（几乎总是要设为 False）
    startrow=1,               # 从第 2 行开始写（留第 1 行给标题）
)

多文件合并

合并文件夹内所有 Excel 文件

import pandas as pd
from pathlib import Path

def merge_excel_files(folder: Path, glob: str = "*.xlsx",
                      sheet_name: int | str = 0) -> pd.DataFrame:
    """
    合并 folder 下所有匹配 glob 的 Excel 文件。
    自动添加来源文件名列，便于溯源。
    """
    dfs = []
    files = sorted(folder.glob(glob))
    print(f"找到 {len(files)} 个文件，开始合并...")

    for f in files:
        try:
            df = pd.read_excel(f, sheet_name=sheet_name)
            df["_来源文件"] = f.name   # 添加来源标记
            dfs.append(df)
            print(f"  已读取：{f.name}  ({len(df)} 行)")
        except Exception as e:
            print(f"  [跳过] {f.name}: {e}")

    if not dfs:
        raise ValueError("没有成功读取任何文件")

    merged = pd.concat(dfs, ignore_index=True)
    print(f"\n合并完成：共 {len(merged)} 行，{len(merged.columns)} 列")
    return merged

# 使用示例：合并 sales/ 目录下所有 Excel
sales_dir = Path("./sales")
merged_df = merge_excel_files(sales_dir)
merged_df.to_excel("merged_sales.xlsx", index=False)

数据清洗：缺失值、重复值、类型转换

pandas 数据清洗常用操作

import pandas as pd
import numpy as np

df = pd.read_excel("raw_data.xlsx")

# ---- 查看数据质量 ----
print(df.info())                         # 列名、数据类型、非空计数
print(df.isnull().sum())                 # 每列空值数量
print(df.duplicated().sum())             # 完全重复行数量
print(df.describe())                     # 数值列统计摘要

# ---- 处理缺失值 ----
df.dropna(subset=["订单ID", "销售额"])   # 删除关键列为空的行
df["数量"].fillna(0, inplace=True)       # 用 0 填充数量的空值
df["备注"].fillna("无", inplace=True)    # 文本列填充"无"
df["金额"].interpolate(inplace=True)     # 用插值法填充（时序数据常用）

# ---- 处理重复值 ----
df.drop_duplicates(inplace=True)                          # 删除完全重复行
df.drop_duplicates(subset=["订单ID"], keep="last",        # 按订单ID去重，保留最新
                   inplace=True)

# ---- 类型转换 ----
df["日期"] = pd.to_datetime(df["日期"], format="%Y/%m/%d", errors="coerce")
df["金额"] = pd.to_numeric(df["金额"], errors="coerce")   # errors='coerce' 把非数字转为 NaN
df["区域"] = df["区域"].astype("category")                # 分类类型节省内存

# ---- 字符串清洗 ----
df["产品名称"] = df["产品名称"].str.strip()               # 去首尾空格
df["区域"] = df["区域"].str.replace("区", "", regex=False)
df["订单ID"] = df["订单ID"].str.upper()

# ---- 筛选与条件处理 ----
df = df[df["金额"] > 0]                                   # 过滤无效数据
df["等级"] = pd.cut(df["金额"],
                    bins=[0, 1000, 5000, float("inf")],
                    labels=["普通", "重要", "战略"])       # 按金额分级

print(f"清洗后：{len(df)} 行")

透视表

pandas pivot_table 多维汇总

import pandas as pd

df = pd.read_excel("sales.xlsx")

# 基础透视表：按区域和产品汇总销售额
pivot = df.pivot_table(
    values="销售额",          # 汇总的数值列
    index="区域",             # 行（可以是列表：多层索引）
    columns="产品类别",        # 列（可选）
    aggfunc="sum",            # 聚合函数：sum / mean / count / max / min
    fill_value=0,             # 空值填充
    margins=True,             # 显示合计行/列
    margins_name="合计",
)

print(pivot)

# 多指标透视
pivot_multi = df.pivot_table(
    values=["销售额", "数量"],
    index=["区域", "销售员"],
    aggfunc={"销售额": "sum", "数量": "sum"},
)

# 重置索引（将层级索引变为普通列）
pivot_reset = pivot_multi.reset_index()
pivot_reset.to_excel("pivot_output.xlsx", index=False)

完整案例：100 个 Excel 文件合并汇总

merge_100_excel.py

"""
merge_100_excel.py
将文件夹内所有门店 Excel 文件合并，清洗，生成透视汇总表。
"""
import pandas as pd
from pathlib import Path

def load_and_clean(filepath: Path) -> pd.DataFrame:
    """读取单个文件并做基础清洗"""
    df = pd.read_excel(filepath, dtype={"订单编号": str})
    df["_门店"] = filepath.stem                          # 用文件名（去后缀）作门店标识
    df.columns = df.columns.str.strip()                  # 去列名空格
    df.dropna(subset=["订单编号", "销售额"], inplace=True)
    df["销售额"] = pd.to_numeric(df["销售额"], errors="coerce")
    df["日期"] = pd.to_datetime(df["日期"], errors="coerce")
    df.dropna(subset=["销售额", "日期"], inplace=True)
    df = df[df["销售额"] > 0]
    return df

def run(source_dir: str, output_file: str) -> None:
    source = Path(source_dir)
    files = sorted(source.glob("*.xlsx"))
    print(f"发现 {len(files)} 个门店文件")

    # 合并
    all_dfs = []
    for f in files:
        try:
            all_dfs.append(load_and_clean(f))
        except Exception as e:
            print(f"  [跳过] {f.name}: {e}")

    merged = pd.concat(all_dfs, ignore_index=True)
    print(f"合并后总行数：{len(merged)}")

    # 透视汇总
    pivot = merged.pivot_table(
        values="销售额",
        index="_门店",
        columns=pd.Grouper(key="日期", freq="ME"),  # 按月汇总
        aggfunc="sum",
        fill_value=0,
        margins=True,
        margins_name="汇总",
    )
    pivot.columns = [c.strftime("%Y-%m") if hasattr(c, "strftime") else c
                     for c in pivot.columns]

    # 输出
    with pd.ExcelWriter(output_file, engine="openpyxl") as writer:
        merged.to_excel(writer, sheet_name="明细数据", index=False)
        pivot.to_excel(writer, sheet_name="透视汇总")

    print(f"输出文件：{output_file}")

run("./stores", "sales_summary.xlsx")

pandas + openpyxl 组合：数据处理 + 精美输出

pandas 擅长计算，openpyxl 擅长美化。两者组合通过 ExcelWriter 实现无缝衔接。

ExcelWriter 多 Sheet 输出 + openpyxl 后处理样式

import pandas as pd
import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

def apply_table_style(ws, header_row: int = 1,
                       header_color: str = "4472C4",
                       alt_row_color: str = "DCE6F1") -> None:
    """对 worksheet 应用统一的表格样式"""
    thin = Side(style="thin")
    border = Border(left=thin, right=thin, top=thin, bottom=thin)

    for row_idx, row in enumerate(ws.iter_rows(), start=1):
        for cell in row:
            cell.border = border
            if row_idx == header_row:
                cell.font = Font(bold=True, color="FFFFFF")
                cell.fill = PatternFill(fill_type="solid", fgColor=header_color)
                cell.alignment = Alignment(horizontal="center", vertical="center")
            elif row_idx % 2 == 0:
                cell.fill = PatternFill(fill_type="solid", fgColor=alt_row_color)

    # 自动列宽
    for col in ws.columns:
        max_len = max((len(str(cell.value or "")) for cell in col), default=8)
        ws.column_dimensions[col[0].column_letter].width = min(max_len + 4, 40)

def generate_styled_report(df_detail: pd.DataFrame,
                            df_pivot: pd.DataFrame,
                            output_path: str) -> None:
    """用 pandas 写数据，用 openpyxl 做样式后处理"""
    with pd.ExcelWriter(output_path, engine="openpyxl") as writer:
        df_detail.to_excel(writer, sheet_name="明细", index=False)
        df_pivot.to_excel(writer, sheet_name="透视汇总")

        # 拿到 openpyxl workbook 对象，做样式后处理
        wb = writer.book
        for sheet_name in ["明细", "透视汇总"]:
            ws = wb[sheet_name]
            apply_table_style(ws)

    print(f"格式化报告已生成：{output_path}")

# 示例调用
import pandas as pd
df = pd.read_excel("merged_sales.xlsx")
pivot = df.pivot_table(values="销售额", index="区域", aggfunc="sum")
generate_styled_report(df, pivot, "formatted_report.xlsx")

xlwings：操作已打开的 Excel

xlwings 通过 Windows COM 接口与 Excel 程序直接交互，可以操作正在运行的 Excel 实例，调用 VBA 宏，读取公式计算结果。

**平台限制：**xlwings 需要本机安装 Microsoft Excel，主要支持 Windows（macOS 有限支持，功能受限）。在 Linux 服务器上无法使用。适合在 Windows 桌面端需要与用户正在使用的 Excel 交互的场景。

xlwings 基础操作

import xlwings as xw  # pip install xlwings

# ---- 连接已打开的 Excel ----
app = xw.App(visible=True)          # 启动 Excel（visible=False 后台运行）
wb = app.books.open("sales.xlsx")   # 打开文件
ws = wb.sheets["Sheet1"]

# ---- 读写单元格 ----
ws["A1"].value = "标题"
value = ws["B2"].value               # 读取（公式会返回计算结果！）

# ---- 批量读写（Range 操作）----
# 写入二维数据（行列列表）
ws["A1"].value = [["产品", "数量", "金额"],
                  ["笔记本", 5, 7500],
                  ["鼠标",   20, 598]]

# 读取区域为 DataFrame
df = ws["A1:C10"].options(pd.DataFrame, header=1, index=False).value

# ---- 调用 VBA 宏 ----
wb.macro("Module1.RefreshAllData")()   # 调用名为 RefreshAllData 的 VBA 宏

# ---- 触发 Excel 重新计算 ----
wb.app.calculate()

# ---- 读取公式计算结果 ----
# xlwings 直接返回已计算的值，无需 data_only 参数
result = ws["D10"].value   # 如果 D10 是 =SUM(D2:D9)，返回数值而不是公式字符串

# ---- 保存关闭 ----
wb.save()
wb.close()
app.quit()

适合 xlwings 的场景

• 需要读取公式计算结果：openpyxl 的 data_only 只能读取上次 Excel 保存的缓存值，xlwings 能触发 Excel 实时计算
• 调用 VBA 宏：老系统里有大量 VBA 业务逻辑，Python 触发宏执行
• 操作用户正在使用的 Excel：Python 作为"副驾驶"，在不关闭 Excel 的情况下自动填充数据
• Windows 桌面自动化工作流：与其他 Office 程序联动

实战项目：销售数据自动化处理管道

整合本章技术，实现一个完整的销售数据处理管道：**多门店 Excel 合并

sales_pipeline.py — 完整可运行的约 110 行代码

"""
sales_pipeline.py
销售数据自动化处理管道：
  1. 读取 ./stores/ 目录下所有门店 Excel
  2. 合并并清洗数据
  3. 生成月度透视汇总
  4. 输出格式化报告（含样式）

依赖：pip install pandas openpyxl
用法：python sales_pipeline.py
"""
import sys
import datetime
import pandas as pd
import openpyxl
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
from openpyxl.utils import get_column_letter
from pathlib import Path

# ==================== 配置 ====================
STORES_DIR = Path("./stores")          # 门店数据目录
OUTPUT_FILE = Path("sales_report.xlsx")
REQUIRED_COLS = {"订单编号", "日期", "产品", "数量", "单价", "销售额"}

# ==================== 数据加载 & 清洗 ====================

def load_store(filepath: Path) -> pd.DataFrame | None:
    """加载单个门店文件，返回清洗后的 DataFrame；失败返回 None"""
    try:
        df = pd.read_excel(filepath, dtype={"订单编号": str})
    except Exception as e:
        print(f"  [ERROR] 读取失败 {filepath.name}: {e}")
        return None

    df.columns = df.columns.str.strip()
    missing = REQUIRED_COLS - set(df.columns)
    if missing:
        print(f"  [SKIP]  {filepath.name} 缺少列：{missing}")
        return None

    df["_门店"] = filepath.stem
    df["日期"] = pd.to_datetime(df["日期"], errors="coerce")
    df["销售额"] = pd.to_numeric(df["销售额"], errors="coerce")
    df["数量"] = pd.to_numeric(df["数量"], errors="coerce").fillna(0).astype(int)

    before = len(df)
    df.dropna(subset=["订单编号", "日期", "销售额"], inplace=True)
    df = df[df["销售额"] > 0]
    df.drop_duplicates(subset=["订单编号"], keep="last", inplace=True)
    after = len(df)

    print(f"  [OK]    {filepath.name}: {before} 行     return df

def load_all(stores_dir: Path) -> pd.DataFrame:
    """加载目录下所有门店文件"""
    files = sorted(stores_dir.glob("*.xlsx"))
    if not files:
        sys.exit(f"错误：{stores_dir} 目录下没有找到 .xlsx 文件")

    print(f"发现 {len(files)} 个门店文件：\n")
    dfs = [df for f in files if (df := load_store(f)) is not None]
    if not dfs:
        sys.exit("所有文件加载失败，请检查数据格式。")

    merged = pd.concat(dfs, ignore_index=True)
    print(f"\n合并完成：{len(merged)} 行有效数据")
    return merged

# ==================== 数据分析 ====================

def build_pivot(df: pd.DataFrame) -> pd.DataFrame:
    """构建月度 × 门店的销售额透视表"""
    df["年月"] = df["日期"].dt.to_period("M").astype(str)
    pivot = df.pivot_table(
        values="销售额",
        index="_门店",
        columns="年月",
        aggfunc="sum",
        fill_value=0,
        margins=True,
        margins_name="合计",
    )
    return pivot

def top_products(df: pd.DataFrame, n: int = 10) -> pd.DataFrame:
    """按销售额统计 Top N 产品"""
    return (
        df.groupby("产品")["销售额"]
        .sum()
        .sort_values(ascending=False)
        .head(n)
        .reset_index()
        .rename(columns={"销售额": "总销售额"})
    )

# ==================== 报告输出 ====================

def style_ws(ws) -> None:
    """为 worksheet 应用统一样式"""
    thin = Side(style="thin")
    border = Border(left=thin, right=thin, top=thin, bottom=thin)
    header_fill = PatternFill(fill_type="solid", fgColor="4472C4")
    alt_fill = PatternFill(fill_type="solid", fgColor="DCE6F1")

    for row_idx, row in enumerate(ws.iter_rows(), start=1):
        for cell in row:
            cell.border = border
            if row_idx == 1:
                cell.font = Font(bold=True, color="FFFFFF", size=11)
                cell.fill = header_fill
                cell.alignment = Alignment(horizontal="center", vertical="center")
            elif row_idx % 2 == 0:
                cell.fill = alt_fill

    for col in ws.columns:
        max_len = max((len(str(c.value or "")) for c in col), default=6)
        ws.column_dimensions[col[0].column_letter].width = min(max_len + 4, 35)

def write_report(df: pd.DataFrame, pivot: pd.DataFrame,
                 top: pd.DataFrame, output_path: Path) -> None:
    """写出带样式的多 Sheet Excel 报告"""
    with pd.ExcelWriter(output_path, engine="openpyxl") as writer:
        df.drop(columns=["_门店"], errors="ignore").to_excel(
            writer, sheet_name="全量明细", index=False)
        pivot.to_excel(writer, sheet_name="月度透视")
        top.to_excel(writer, sheet_name="Top产品", index=False)

        wb = writer.book
        for sheet_name in ["全量明细", "月度透视", "Top产品"]:
            style_ws(wb[sheet_name])

        # 封面 Sheet
        cover = wb.create_sheet("封面", 0)
        cover.sheet_view.showGridLines = False
        cover.merge_cells("B2:F2")
        cover["B2"] = "销售数据自动化报告"
        cover["B2"].font = Font(size=20, bold=True, color="2F5496")
        cover["B2"].alignment = Alignment(horizontal="center")
        cover.merge_cells("B3:F3")
        cover["B3"] = f"生成时间：{datetime.datetime.now().strftime('%Y-%m-%d %H:%M')}"
        cover["B3"].font = Font(size=11, color="666666")
        cover["B3"].alignment = Alignment(horizontal="center")
        cover.row_dimensions[2].height = 40

    print(f"\n报告已生成：{output_path.resolve()}")

# ==================== 主流程 ====================

def main() -> None:
    print("=" * 50)
    print("销售数据自动化处理管道")
    print("=" * 50 + "\n")

    df = load_all(STORES_DIR)
    pivot = build_pivot(df)
    top = top_products(df, n=10)

    # 打印关键统计
    print(f"\n总销售额：{df['销售额'].sum():,.2f} 元")
    print(f"订单数量：{len(df)} 条")
    print(f"门店数量：{df['_门店'].nunique()} 家")
    print(f"数据时间跨度：{df['日期'].min().date()} ~ {df['日期'].max().date()}")

    write_report(df, pivot, top, OUTPUT_FILE)

if __name__ == "__main__":
    main()

**本章小结：**openpyxl 负责格式化输出，pandas 负责数据计算，两者通过 ExcelWriter 无缝组合。xlwings 在需要与运行中的 Excel 交互时才登场。掌握这三件套，Excel 相关的自动化需求基本全部覆盖。

上一章

下一章
第7章：Python 操作 Word