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

Pandas Excel 文件操作

Pandas 教程

简介

在数据分析工作中，Excel 是非常普遍的数据载体，业务台账、销售报表、统计汇总表大多使用 .xlsx 格式。借助 Pandas 配合 Excel 引擎，可以自动化读取、清洗、汇总、导出多工作表 Excel 文件，替代人工复制粘贴，是报表自动化的常用方案。

Pandas 不自带 Excel 解析引擎，需要第三方库支持：

  • openpyxl：主推引擎，支持读写 .xlsx，支持追加工作表，本文示例全部基于它
  • xlrd==1.2.0：仅用于读取老式 .xls 文件
  • xlsxwriter：仅写入，适合做单元格样式、表头美化

本文配套示例文件：data.xlsx，包含两张工作表：Sheet1（学生表）、销售明细

环境准备

pip install pandas openpyxl
# 可选：样式美化
pip install xlsxwriter

import pandas as pd

生成配套示例文件 data.xlsx（运行一次即可）

下面代码会直接生成教程全程用到的 data.xlsx，内置两个 sheet：

  • Sheet1：学生表
  • 销售明细：销售业务数据
import pandas as pd

# 学生表数据
df_student = pd.DataFrame({
    "编号": ["001", "002", "003"],
    "姓名": ["张三", "李四", "王五"],
    "年龄": [18, 22, 20],
    "分数": [88, 92, 79],
    "日期": ["2026-01-05", "2026-01-06", "2026-01-07"]
})

# 销售明细数据
df_sale = pd.DataFrame({
    "订单号": ["S001", "S002", "S003", "S004"],
    "商品名称": ["笔记本", "钢笔", "文件夹", "笔记本"],
    "数量": [2, 5, 3, 1],
    "单价": [35, 8, 12, 35],
    "销售日期": ["2026-01-05", "2026-01-05", "2026-01-06", "2026-01-07"]
})

# 写入多sheet
with pd.ExcelWriter("data.xlsx", engine="openpyxl") as writer:
    df_student.to_excel(writer, sheet_name="Sheet1", index=False)
    df_sale.to_excel(writer, sheet_name="销售明细", index=False)

print("data.xlsx 生成完成！包含 Sheet1、销售明细 两个工作表")

销售明细 表结构预览：

订单号商品名称数量单价销售日期
S001笔记本2352026-01-05
S002钢笔582026-01-05
S003文件夹3122026-01-06
S004笔记本1352026-01-07

一、读取 Excel：pd.read_excel()

1.1 基础读取

读取第一个工作表（Sheet1）

df = pd.read_excel("data.xlsx", engine="openpyxl")
print(df)

按工作表名称读取【销售明细】（本教程重点示例）

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

按序号读取，序号从 0 开始：

# 第0个sheet：Sheet1，第1个sheet：销售明细
df_sale = pd.read_excel("data.xlsx", sheet_name=1)

1.2 常用参数示例（读取销售明细）

df_sale = pd.read_excel(
    "data.xlsx",
    sheet_name="销售明细",
    header=0,
    usecols="A:D",
    dtype={"订单号": str},
    parse_dates=["销售日期"],
    engine="openpyxl"
)
print(df_sale.dtypes)

参数说明：

  • header=0：第0行作为表头
  • usecols="A:D"：只读取 A~D 列
  • dtype={"订单号":str}：订单号强制文本，防止前导 0 丢失
  • parse_dates=["销售日期"]：自动转为日期类型

1.3 一次性读取全部工作表

sheet_name=None，返回字典，key=工作表名称，value=DataFrame

all_sheet = pd.read_excel("data.xlsx", sheet_name=None, engine="openpyxl")
print(all_sheet.keys())

df_stu = all_sheet["Sheet1"]
df_sale = all_sheet["销售明细"]

1.4 ExcelFile 方式（推荐，多次读取同文件）

with pd.ExcelFile("data.xlsx", engine="openpyxl") as f:
    print(f.sheet_names)
    df_stu = pd.read_excel(f, sheet_name="Sheet1")
    df_sale = pd.read_excel(f, sheet_name="销售明细")

1.5 大文件分块读取 chunksize

适合行数很大、内存不足的场景：

chunk_iter = pd.read_excel("data.xlsx", sheet_name="销售明细", chunksize=2)
for chunk in chunk_iter:
    print(chunk)

二、写入 Excel：df.to_excel()

⚠️ 重点：多次直接调用 to_excel() 会覆盖整个文件；多sheet导出必须使用 ExcelWriter

2.1 单sheet导出

df_sale.to_excel("sale_out.xlsx", sheet_name="销售汇总", index=False, na_rep="")
  • index=False：不导出 pandas 自带行索引（几乎所有业务场景必加）
  • na_rep=""：空值导出为空白单元格，而不是 nan

2.2 多工作表写入（核心用法）

把学生表、销售明细、统计结果一起写入同一个 Excel：

# 简单统计：按商品汇总销量
df_stat = df_sale.groupby("商品名称")["数量"].sum().reset_index()
df_stat.columns = ["商品名称", "总销量"]

with pd.ExcelWriter("report.xlsx", engine="openpyxl") as writer:
    df_stu.to_excel(writer, sheet_name="学生表", index=False)
    df_sale.to_excel(writer, sheet_name="销售明细", index=False)
    df_stat.to_excel(writer, sheet_name="销量统计", index=False)

2.3 追加写入已有 Excel

mode="a" 追加模式，仅 openpyxl 支持；if_sheet_exists 控制同名sheet策略

  • replace：同名工作表直接覆盖（常用）
  • new：新建sheet
  • overlay：在原有sheet指定位置叠加写入
with pd.ExcelWriter(
    "report.xlsx",
    mode="a",
    engine="openpyxl",
    if_sheet_exists="replace"
) as w:
    df_stat.to_excel(w, sheet_name="销量统计", index=False)

注意：追加写入时，目标 Excel 文件不能在 WPS/Excel 中打开占用。

2.4 同一个sheet放置多张表格（startrow / startcol）

with pd.ExcelWriter("one_sheet_multi_table.xlsx", engine="openpyxl") as w:
    df_sale.to_excel(w, sheet_name="汇总页", index=False, startrow=0, startcol=0)
    df_stat.to_excel(w, sheet_name="汇总页", index=False, startrow=8, startcol=0)

2.5 样式美化（xlsxwriter）

with pd.ExcelWriter("sale_style.xlsx", engine="xlsxwriter") as w:
    df_sale.to_excel(w, sheet_name="销售明细", index=False)
    workbook = w.book
    worksheet = w.sheets["销售明细"]
    header_fmt = workbook.add_format({"bold": True, "bg_color": "#E6E6E6"})
    worksheet.set_row(0, cell_format=header_fmt)
    worksheet.set_column("A:E", 14)

三、综合实战：读取销售明细，统计并导出报表

import pandas as pd

# 读取配套示例文件中的销售明细
df_sale = pd.read_excel("data.xlsx", sheet_name="销售明细", engine="openpyxl", parse_dates=["销售日期"])

# 新增金额列
df_sale["金额"] = df_sale["数量"] * df_sale["单价"]

# 按商品汇总
df_goods_sum = df_sale.groupby("商品名称").agg(
    总数量=("数量", "sum"),
    总金额=("金额", "sum")
).reset_index()

# 按日期汇总
df_date_sum = df_sale.groupby("销售日期")["金额"].sum().reset_index()
df_date_sum.columns = ["销售日期", "当日销售额"]

# 多sheet输出报表
with pd.ExcelWriter("销售报表.xlsx", engine="openpyxl") as w:
    df_sale.to_excel(w, sheet_name="原始明细", index=False)
    df_goods_sum.to_excel(w, sheet_name="商品汇总", index=False)
    df_date_sum.to_excel(w, sheet_name="日期汇总", index=False)

print("报表生成完成！")

导出的excel报表：

四、批量合并文件夹内所有销售Excel

适合多个分店上报的销售台账合并：

import os
import pandas as pd

folder = "./sale_files/"
df_list = []
for fname in os.listdir(folder):
    if fname.endswith(".xlsx"):
        path = os.path.join(folder, fname)
        tmp = pd.read_excel(path, sheet_name="销售明细", engine="openpyxl")
        df_list.append(tmp)

df_all = pd.concat(df_list, ignore_index=True)
df_all.to_excel("分店销售合并表.xlsx", index=False)

五、常见坑与避坑

  1. No module named openpyxl：缺少引擎，pip install openpyxl
  2. 导出多出一列序号：忘记写 index=False
  3. 订单号、编号前导0丢失：读取时设置 dtype={"订单号":str}
  4. 多次 to_excel 只剩最后一张表：未使用 ExcelWriter
  5. 追加模式报错：只能 openpyxl，文件不能被其他程序打开占用
  6. 日期变成数字：读取使用 parse_dates 解析日期

六、参数速查表

read_excel

参数说明
sheet_name工作表名称/序号；None读取全部
header表头行号，None无表头
usecols指定读取列
dtype指定列类型
parse_dates自动解析日期
chunksize分块读取行数

to_excel

参数说明
index是否导出行索引，推荐 False
sheet_name工作表名称
na_repNaN替换为指定文本
startrow/startcol写入起始行列
modew新建覆盖 / a追加
if_sheet_exists同名sheet处理策略

小结

本文配套的 data.xlsx 包含 Sheet1 和 销售明细 两张工作表，可以直接跑通全部示例代码。日常自动化报表流程：读取明细 → 清洗统计 → 多sheet导出。基础读写使用 openpyxl；需要样式美化时切换 xlsxwriter。

标签：

0 条笔记