自学教程 学编程从这里开始

Pandas Excel 文件操作进阶

Pandas 教程

前置说明：本文是《Pandas Excel 文件操作》进阶独立教程，全部功能依赖 xlsxwriter 引擎，openpyxl 不支持本章的冻结窗格、原生公式、下拉验证、条件格式、图表、打印页面设置。
配套数据源：沿用之前 data.xlsx 里的「销售明细」表

环境准备

pip install pandas xlsxwriter
# 升级到最新版，避免API缺失
pip install --upgrade xlsxwriter

import pandas as pd

配套数据（销售明细）

订单号商品名称数量单价销售日期
S001笔记本2352026-01-05
S002钢笔582026-01-05
S003文件夹3122026-01-06
S004笔记本1352026-01-07
# 读取配套数据
df_sale = pd.read_excel("data.xlsx", sheet_name="销售明细", engine="openpyxl")

一、冻结窗格 freeze_panes

冻结窗格用于固定表头、固定左侧编码列，滚动查看报表时不丢失标题。
语法：ws.freeze_panes("单元格位置")

  • "A2"：冻结首行（最常用），A2 开始滚动
  • "B2"：冻结首行 + 首列
  • "C2"：冻结首行 + A、B两列
with pd.ExcelWriter("adv_freeze.xlsx", engine="xlsxwriter") as w:
    df_sale.to_excel(w, sheet_name="销售明细", index=False)
    ws = w.sheets["销售明细"]
    # 冻结首行
    ws.freeze_panes("A2")
    # ws.freeze_panes("B2") # 首行+首列同时冻结

注意：冻结是工作表属性，必须在 to_excel 之后调用。

二、写入 Excel 原生公式 write_formula

Pandas 默认写入的是计算后静态数值；write_formula 写入 Excel 原生公式，用户打开文件可以修改数值、公式自动重算。
常用公式：SUM、PRODUCT、IF、AVERAGE。

df_sale = pd.read_excel("data.xlsx", sheet_name="销售明细", engine="openpyxl")
row_cnt = len(df_sale)

with pd.ExcelWriter("adv_formula.xlsx", engine="xlsxwriter") as w:
    df_sale.to_excel(w, sheet_name="销售明细", index=False)
    ws = w.sheets["销售明细"]
    # E列作为金额列
    ws.write("E1", "金额")
    # 循环写入 =C2*D2 数量 × 单价
    for r in range(2, row_cnt + 2):
        ws.write_formula(f"E{r}", f"=C{r}*D{r}")
    # 合计行，求和E2:E5
    ws.write("D6", "合计")
    ws.write_formula("E6", "=SUM(E2:E5)")
    ws.freeze_panes("A2")

公式写法要点：

  • 单元格引用使用 Excel A1 标记法
  • 不要使用 Python 变量直接填结果，公式字符串原样交给Excel
  • 支持嵌套：=IF(E2>100,"大额","小额")

三、下拉数据验证（Data Validation）【已修复】

给单元格增加下拉选择列表，限制输入内容，防止脏数据。适合商品、分类、状态选择。
参数说明：

  • validate="list"：下拉列表类型
  • source：选项数组
  • error_type="stop"：禁止输入不在列表的值；warning 仅弹窗警告
df_sale = pd.read_excel("data.xlsx", sheet_name="销售明细", engine="openpyxl")

with pd.ExcelWriter("adv_validation.xlsx", engine="xlsxwriter") as w:
    df_sale.to_excel(w, sheet_name="销售明细", index=False)
    ws = w.sheets["销售明细"]
    # 下拉选项
    options = ["笔记本", "钢笔", "文件夹"]
    # ws.data_validation
    ws.data_validation("B2:B100", {
        "validate": "list",
        "source": options,
        "input_title": "商品选择",
        "input_message": "从下拉列表选择商品",
        "error": "商品名称不在可选列表中",
        "error_type": "stop"
    })
    ws.freeze_panes("A2")

四、条件格式 conditional_format

根据单元格数值自动变色、色阶、数据条，高亮异常数据，是报表可视化利器。支持：单元格规则、色阶、数据条、图标集。

示例1：单元格规则，高亮金额大于50的行

df_sale = pd.read_excel("data.xlsx", sheet_name="销售明细", engine="openpyxl")
row_cnt = len(df_sale)

with pd.ExcelWriter("adv_conditional.xlsx", engine="xlsxwriter") as w:
    df_sale.to_excel(w, sheet_name="销售明细", index=False)
    ws = w.sheets["销售明细"]
    # 写入金额公式
    ws.write("E1", "金额")
    for r in range(2, row_cnt + 2):
        ws.write_formula(f"E{r}", f"=C{r}*D{r}")
    ws.freeze_panes("A2")

    # 条件格式：E2:E5 大于50标绿色背景
    ws.conditional_format("E2:E5", {
        "type": "cell",
        "criteria": ">",
        "value": 50,
        "format": w.book.add_format({"bg_color": "#C6EFCE"})
    })

示例2：色阶渐变（颜色刻度）

ws.conditional_format("E2:E5", {
    "type": "color_scale",
    "min_color": "#FFC7CE",
    "max_color": "#C6EFCE"
})

示例3：数据条

ws.conditional_format("E2:E5", {
    "type": "data_bar",
    "bar_color": "#638EC6"
})

五、插入图表（柱状图/折线图）

xlsxwriter 可以直接在工作表嵌入原生Excel图表，图表数据源绑定单元格，修改明细图表自动刷新。
支持：column柱状、bar条形、line折线、pie饼图。

df_sale = pd.read_excel("data.xlsx", sheet_name="销售明细", engine="openpyxl")
row_cnt = len(df_sale)

with pd.ExcelWriter("adv_chart.xlsx", engine="xlsxwriter") as w:
    df_sale.to_excel(w, sheet_name="销售明细", index=False)
    ws = w.sheets["销售明细"]
    # 金额公式
    ws.write("E1", "金额")
    for r in range(2, row_cnt + 2):
        ws.write_formula(f"E{r}", f"=C{r}*D{r}")
    ws.freeze_panes("A2")

    # 创建柱状图
    chart = w.book.add_chart({"type": "column"})
    chart.add_series({
        "name": "销售金额",
        "categories": ["销售明细", 1, 1, row_cnt, 1], # B列商品名称
        "values": ["销售明细", 1, 4, row_cnt, 4],     # E列金额
    })
    chart.set_title({"name": "商品销售金额统计"})
    chart.set_x_axis({"name": "商品"})
    chart.set_y_axis({"name": "金额"})
    # 将图表插入 G2 位置
    ws.insert_chart("G2", chart)

categories、values 参数格式：[sheet名, 起始行, 起始列, 结束行, 结束列]，行、列从0开始。

六、打印页面设置（Page Setup）

控制打印方向、纸张大小、缩放、打印标题、页边距、居中打印，导出报表后直接打印无需手动调整页面布局。

常用API：

  • set_landscape() 横向打印；set_portrait() 纵向
  • set_paper() 纸张大小
  • set_margins() 页边距
  • repeat_rows() 设置每页都打印表头
  • fit_to_pages() 缩放适配到指定页数
df_sale = pd.read_excel("data.xlsx", sheet_name="销售明细", engine="openpyxl")
row_cnt = len(df_sale)

with pd.ExcelWriter("adv_print.xlsx", engine="xlsxwriter") as w:
    df_sale.to_excel(w, sheet_name="销售明细", index=False)
    ws = w.sheets["销售明细"]
    # 金额公式
    ws.write("E1", "金额")
    for r in range(2, row_cnt + 2):
        ws.write_formula(f"E{r}", f"=C{r}*D{r}")
    ws.freeze_panes("A2")

    # 打印设置
    ws.set_landscape()               # 横向打印
    ws.set_paper(9)                  # 9 = A4纸
    ws.set_margins(left=0.5, right=0.5, top=0.6, bottom=0.6)
    ws.repeat_rows(0)                # 第0行表头，每页打印
    ws.fit_to_pages(1, 0)            # 宽度适配为1页，高度不限
    ws.set_center_horizontally()     # 水平居中

纸张编码对照表（常用）

  • 9：A4
  • 1：Letter

七、整合完整版：全部进阶功能合并示例

一份代码同时实现：冻结首行、公式计算、下拉验证、条件高亮、柱状图表、打印配置，适合直接作为报表模板。

import pandas as pd

df_sale = pd.read_excel("data.xlsx", sheet_name="销售明细", engine="openpyxl")
rows = len(df_sale)

with pd.ExcelWriter("sale_full_adv.xlsx", engine="xlsxwriter") as w:
    df_sale.to_excel(w, sheet_name="销售明细", index=False)
    workbook = w.book
    ws = w.sheets["销售明细"]

    # 1. 冻结首行
    ws.freeze_panes("A2")

    # 2. 写入金额公式
    ws.write("E1", "金额")
    for r in range(2, rows + 2):
        ws.write_formula(f"E{r}", f"=C{r}*D{r}")

    # 3. 商品下拉验证 
    ws.data_validation("B2:B100", {
        "validate": "list",
        "source": ["笔记本", "钢笔", "文件夹"],
        "error_type": "stop"
    })

    # 4. 条件格式：金额>50高亮绿色
    fmt_green = workbook.add_format({"bg_color": "#C6EFCE"})
    ws.conditional_format("E2:E5", {
        "type": "cell", "criteria": ">", "value": 50, "format": fmt_green
    })

    # 5. 插入柱状图
    chart = workbook.add_chart({"type": "column"})
    chart.add_series({
        "name": "销售金额",
        "categories": ["销售明细", 1, 1, rows, 1],
        "values": ["销售明细", 1, 4, rows, 4],
    })
    chart.set_title({"name": "商品销售金额统计"})
    ws.insert_chart("G2", chart)

    # 6. 打印设置 A4横向，每页打印表头
    ws.set_landscape()
    ws.set_paper(9)
    ws.repeat_rows(0)
    ws.fit_to_pages(1, 0)

print("高级报表生成完成 sale_full_adv.xlsx")

输出的excel表格：

八、避坑清单（更新）

  1. 引擎必须是 xlsxwriter，openpyxl 没有这套API，混用会报错、功能不生效。
  2. 顺序：df.to_excel() 先写入数据，再操作 freeze / formula / data_validation / chart / conditional_format。
  3. ✅ 重点修正坑：下拉验证是 ws.data_validation()，不是 workbook 的 add_validation。
  4. 下拉验证范围尽量放大（B2:B100），适配后续新增行。
  5. 图表行列索引从0开始，和Excel界面行号不一样，容易错位。
  6. repeat_rows(0) 打印表头，必须配合打印设置；冻结窗格只是视觉固定，不等于打印每页带表头。
  7. xlsxwriter 只支持写入，不能读取、不能追加到已有文件；追加文件请用 openpyxl。

九、API速查表

功能方法
冻结首行ws.freeze_panes("A2")
写入原生公式ws.write_formula("E2", "=C2*D2")
下拉数据验证ws.data_validation(区域, 配置字典)
条件格式ws.conditional_format(区域, 配置字典)
插入图表workbook.add_chart() + ws.insert_chart()
横向打印ws.set_landscape()
每页重复表头ws.repeat_rows(0)
A4纸张ws.set_paper(9)

小结

本篇进阶功能适合交付可交互业务报表：数据人员打开后可以修改数量单价、公式自动重算、下拉规范输入、异常自动标色、自带统计图表，并且直接配置好打印模板。
基础读写、追加sheet场景继续用 openpyxl；需要交付美化、交互式报表时切换 xlsxwriter。

标签：

0 条笔记