如何用Python操作Excel？

访客 python案例 2026-06-05 14:54:17 1

如何用Python操作Excel？从零进阶的完整指南与实战案例

目录导读

为什么选择Python操作Excel？
- 传统Excel操作的瓶颈
- Python的核心优势：自动化、批量化、可扩展
必备库与安装指南
- openpyxl：读写.xlsx文件的终极利器
- pandas：数据分析师的首选
- xlrd与xlwt：老牌库的兼容性说明
- 安装命令（附常见问题解决）
基础操作全解析
- 读取Excel：工作表、行列、单元格
- 写入数据：从零创建与追加内容
- 样式与格式：字体、颜色、边框、合并单元格
进阶技巧：自动化办公场景
- 批量合并多个Excel文件
- 条件筛选与数据清洗
- 生成图表与透视表
- 跨文件数据关联（VLOOKUP的Python替代）
代码实战：3个真实案例
- 案例1：销售报表自动汇总
- 案例2：学生成绩表按班级拆分
- 案例3：邮件附件自动处理
常见问题与Q&A
性能优化与最佳实践
总结与资源推荐

为什么选择Python操作Excel？

在日常工作中，处理Excel表格是许多职场人的“必修课”，当需要重复合并100张报表、从数千行数据中提取指定信息、或生成带条件格式的图表时，纯手动操作往往耗时且易出错，Python凭借其简洁的语法和强大的第三方库,能将这类任务简化成几行代码。

核心价值对比：
| 操作方式 | 处理1000行数据 | 合并20个文件 | 生成图表 | |----------|----------------|--------------|----------| | 手动Excel| 5分钟（含可能错误） | 精神崩溃 | 20分钟 | | Python脚本| 0.3秒 | 2秒 | 3秒 |

必备库与安装指南

1 核心库选择

openpyxl：目前最活跃的.xlsx操作库，支持读写、样式、图表、公式。
pandas：当需要复杂数据清洗、分组统计时，pandas + openpyxl组合最强。
xlrd/xlwt：已停止更新，仅用于.xls旧格式，建议新项目优先使用openpyxl。

2 安装命令（终端运行）

# 基础安装
pip install openpyxl
# 同时安装pandas提升数据处理能力
pip install pandas openpyxl

常见问题：若报错Permission denied，在命令前加sudo（Mac/Linux）或以管理员身份运行CMD（Windows）。

基础操作全解析

1 读取Excel文件

import openpyxl
# 加载工作簿
wb = openpyxl.load_workbook('example.xlsx')
# 选择工作表（按名字或索引）
sheet = wb['销售数据']  # 或 wb.active（默认活动表）
# 读取单元格
cell_value = sheet['A1'].value  # 方法1
cell_value = sheet.cell(row=1, column=1).value  # 方法2（推荐循环时使用）
# 遍历所有数据
for row in sheet.iter_rows(min_row=1, values_only=True):
    print(row)  # 每行返回元组

2 写入数据与新建文件

from openpyxl import Workbook
wb = Workbook()
sheet = wb.active
sheet.title = "新员工名单"
# 写入表头
sheet['A1'] = "姓名"
sheet['B1'] = "部门"
# 写入数据行
sheet.append(["张三", "技术部"])
sheet.append(["李四", "市场部"])
# 保存
wb.save('员工信息.xlsx')

3 样式设置（单元格美化）

from openpyxl.styles import Font, Alignment, Border, Side, PatternFill
# 字体：加粗红色
sheet['A1'].font = Font(name='微软雅黑', bold=True, color='FF0000', size=14)
# 对齐：水平居中，垂直居中
sheet['A1'].alignment = Alignment(horizontal='center', vertical='center')
# 边框：细线全边框
thin_border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin')
)
sheet['A1'].border = thin_border
# 填充：浅灰色背景
sheet['A1'].fill = PatternFill(start_color='F2F2F2', end_color='F2F2F2', fill_type='solid')
# 合并单元格
sheet.merge_cells('A1:C1')

进阶技巧：自动化办公场景

1 批量合并多个Excel文件（同结构）

import pandas as pd
import glob
files = glob.glob('data/*.xlsx')  # 匹配文件夹内所有xlsx文件
dfs = [pd.read_excel(f) for f in files]
merged_df = pd.concat(dfs, ignore_index=True)
merged_df.to_excel('合并结果.xlsx', index=False)

效率提升：传统手动复制粘贴需15分钟,此代码不到1秒完成。

2 条件筛选与数据清洗

import pandas as pd
df = pd.read_excel('销售记录.xlsx')
# 筛选销售额>5000的行
filtered = df[df['销售额'] > 5000]
# 删除空行
df_clean = df.dropna(subset=['客户名'])
# 替换指定值
df['城市'] = df['城市'].replace('bj', '北京')

3 生成图表（以柱状图为例）

from openpyxl.chart import BarChart, Reference
chart = BarChart()
chart.title = "各部门销售额"
data = Reference(sheet, min_col=2, min_row=1, max_row=5)  # 数据范围
cats = Reference(sheet, min_col=1, min_row=2, max_row=5)  # 分类轴
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
sheet.add_chart(chart, "E2")  # 将图表放在E2单元格

代码实战：3个真实案例

案例1：销售日报自动汇总

需求：每天从各区域提交的Excel中提取销量，汇总生成总表。
关键代码（仅核心片段）：

# 遍历各区域文件
for file in glob.glob('区域*/日报.xlsx'):
    df = pd.read_excel(file)
    total_qty += df['销量'].sum()  # 累加
# 写入总表
summary_df.to_excel('日报汇总.xlsx')

案例2：成绩表按班级拆分为独立文件

df = pd.read_excel('全校成绩.xlsx')
for class_name in df['班级'].unique():
    class_df = df[df['班级'] == class_name]
    class_df.to_excel(f'{class_name}成绩.xlsx', index=False)

案例3：处理邮件附件中的Excel

思路：从.eml文件或邮箱中提取附件 → 用openpyxl读取 → 处理数据 → 替换附件并保存。

常见问题与Q&A

Q1：openpyxl能否处理宏（.xlsm）或密码保护的文件？
A：openpyxl不支持宏执行，但可以读取/写入.xlsm（宏不会被触发），密码保护需先用工具解除密码。

Q2：如何处理超过100万行的大文件？
A：使用pandas的chunksize参数分块读取，或切换到数据库（如SQLite）。openpyxl的单文件建议行数上限约10万行。

Q3：公式写入后，读取时是公式还是计算结果？
A：openpyxl默认返回公式字符串（如=A1+B1），若需计算结果，需先保存Excel并用Excel打开，或使用data_only=True参数：openpyxl.load_workbook('file.xlsx', data_only=True).

Q4：为什么我的字体设置不生效？
A：确保被操作的单元格在保存时未被合并覆盖，部分字体需系统支持（如微软雅黑需Windows）。