为什么要学Python自动化Excel
工作中 70% 的人都涉及 Excel，但很多人还在用复制粘贴、手动改格式、人工汇总数据。Python openpyxl 让你用代码一次性处理几十、几百个 Excel 文件，处理速度是人工的 50-100 倍。一次学会终身受益。
第一步：环境准备
需要：
- Python 3.8+
- openpyxl 库（专门读写 .xlsx）
- IDE——VSCode 或 PyCharm
安装：
pip install openpyxl
第二步：打开第一个文件
from openpyxl import load_workbook
wb = load_workbook(‘员工名单.xlsx’)
print(wb.sheetnames) # [‘Sheet1’, ‘Sheet2’]ws = wb[‘Sheet1’]
print(ws[‘A1’].value) # ‘姓名’
print(ws[‘A2’].value) # ‘张三’print(ws.max_row) # 最大行号
print(ws.max_column) # 最大列号
第三步：批量读取数据
# 遍历所有行
for row in ws.iter_rows(values_only=True):
print(row)# 批量读取为列表
data = []
for row in ws.iter_rows(min_row=2, values_only=True):
data.append(row)
适合从 Excel 提取数据后做计算或入数据库。
第四步：写入新数据
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws.title = ‘新表’ws[‘A1’] = ‘姓名’
ws[‘B1’] = ‘年龄’
ws.append([‘张三’, 25])
ws.append([‘李四’, 30])wb.save(‘新表.xlsx’)
第五步：批量处理多个文件
import os, glob
from openpyxl import load_workbook# 处理文件夹下所有 .xlsx
for filepath in glob.glob(‘sales_*.xlsx’):
wb = load_workbook(filepath)
ws = wb.active
# 计算总销售额
total = 0
for row in ws.iter_rows(min_row=2, values_only=True):
if row[3]: # 销售额在第4列
total += row[3]
print(f'{filepath}: 总销售 {total}’)
一个 30 行的脚本处理 100 个文件只需几秒。
第六步：格式处理
6.1 单元格样式
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
cell = ws[‘A1’]
cell.font = Font(name=’微软雅黑’, size=14, bold=True, color=’FFFFFF’)
cell.fill = PatternFill(start_color=’FF0000′, end_color=’FF0000′, fill_type=’solid’)
cell.alignment = Alignment(horizontal=’center’, vertical=’center’)thin = Side(border_style=’thin’, color=’000000′)
cell.border = Border(left=thin, right=thin, top=thin, bottom=thin)
6.2 列宽行高
ws.column_dimensions[‘A’].width = 20 # A列宽20
ws.row_dimensions[1].height = 30 # 第1行高30
6.3 数字格式
ws[‘B2’].number_format = ‘0.00’ # 保留2位小数
ws[‘B2’].number_format = ‘#,##0’ # 千分位
ws[‘B2’].number_format = ‘0%’ # 百分比
第七步：跨表合并数据
import pandas as pd
# 方法1：用 pandas 合并多个 Excel
dfs = []
for filepath in glob.glob(‘month_*.xlsx’):
df = pd.read_excel(filepath)
dfs.append(df)
result = pd.concat(dfs)
result.to_excel(‘合并.xlsx’, index=False)# 方法2：openpyxl 精细控制
from openpyxl import load_workbook
wb_dst = load_workbook(‘合并.xlsx’)
ws_dst = wb_dst.active
ws_dst.append([‘合并列1’, ‘合并列2’])for filepath in glob.glob(‘month_*.xlsx’):
wb_src = load_workbook(filepath)
ws_src = wb_src.active
for row in ws_src.iter_rows(min_row=2, values_only=True):
ws_dst.append(row)
wb_dst.save(‘合并.xlsx’)
第八步：公式与数据透视
# 写公式
ws[‘D1’] = ‘总价’
ws[‘D2’] = ‘=B2*C2’
ws[‘D3’] = ‘=B3*C3’
ws[‘D5’] = ‘=SUM(D2:D4)’# 公式复制
for row in range(2, 10):
ws[f’D{row}’] = f’=B{row}*C{row}’# 数据透视表（openpyxl 支持有限，建议用 pandas）
import pandas as pd
df = pd.read_excel(‘销售数据.xlsx’)
pivot = df.pivot_table(values=’金额’, index=’品类’, columns=’月份’, aggfunc=’sum’)
pivot.to_excel(‘透视表.xlsx’)
第九步：自动生成报表并发送邮件
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email import encodersdef send_email(to, subject, file):
msg = MIMEMultipart()
msg[‘Subject’] = subject
msg[‘From’] = ‘sender@company.com’
msg[‘To’] = towith open(file, ‘rb’) as f:
part = MIMEBase(‘application’, ‘octet-stream’)
part.set_payload(f.read())
encoders.encode_base64(part)
part.add_header(‘Content-Disposition’, f’attachment; filename={file}’)
msg.attach(part)with smtplib.SMTP(‘smtp.company.com’) as server:
server.send_message(msg)# 每天生成报表后自动发送给领导
wb = Workbook()
ws = wb.active
ws.append([‘部门’, ‘销售额’, ‘环比’])
# … 填数据
wb.save(‘日报.xlsx’)
send_email(‘leader@company.com’, ‘每日销售报告’, ‘日报.xlsx’)
第十步：实战项目
10.1 自动生成周报
从多个数据源读取、计算 KPI、生成 Excel 周报、定时发送邮件。
10.2 工资条自动生成
读取员工信息和考勤数据，自动算工资、生成工资条 PDF、批量发送。
10.3 销售数据汇总
每月合并 30 个区域的销售 Excel、生成透视表、输出年度对比。
10.4 客户数据清洗
规范化地址、合并重复客户、补全缺失字段、生成新数据库文件。
效率提升
- 批量改格式——人工 2 小时 → 代码 5 秒
- 合并多文件——人工 30 分钟 → 代码 10 秒
- 复杂计算——人工 1 小时 + 易错 → 代码 5 秒 + 0 错
- 定时报表——每天 1 小时 → 自动跑
整体办公效率提升 10-50 倍。
学习路径
- 入门——openpyxl 基础读写
- 进阶——格式处理 + 公式 + 透视
- 高级——pandas + openpyxl 组合
- 实战——自动报表系统
- 专家——自动化 RPA 平台集成
进阶学习资源
- 官方文档——openpyxl.readthedocs.io
- pandas 文档——pandas.pydata.org
- 实战案例——GitHub 搜’openpyxl project’
学会 Python 自动化 Excel 是职场里性价比最高的一项技能，年均节省 200+ 小时工作时间。