Python办公自动化（一）：Excel 文件处理完全指南
引言
在日常办公中，Excel 是最常用的数据处理工具之一。无论是财务报表、销售数据还是人事档案，我们都离不开 Excel。本文将详细介绍如何使用 Python 自动化处理 Excel 文件，让你从繁琐的重复劳动中解放出来。
欢迎大家关注此公众号，后台留言"python书籍"可免费获取【Python办公自动化高清PDF】电子书一本
此外小庄推荐一本适合于新手\小白入手一本 Python基础书籍，欢迎大家订阅
环境准备
安装必要的库
pip install openpyxl pandas xlrd xlwt
主要库介绍
- • openpyxl: 读写 Excel 2010+ (.xlsx) 文件
- • xlrd/xlwt: 处理旧版 Excel 文件 (.xls)
基础操作
读取 Excel 文件
from openpyxl import load_workbook
import pandas as pd
# 方法一：使用 openpyxl
wb = load_workbook('data.xlsx')
ws = wb.active
# 读取单元格数据
print(ws['A1'].value)
print(ws.cell(row=2, column=1).value)
# 遍历所有行
for row in ws.iter_rows(values_only=True):
print(row)
# 方法二：使用 pandas（推荐）
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')
print(df.head())
写入 Excel 文件
from openpyxl import Workbook
# 创建工作簿
wb = Workbook()
ws = wb.active
ws.title = "员工信息"
# 写入数据
ws['A1'] = "姓名"
ws['B1'] = "部门"
ws['C1'] = "工资"
employees = [
("张三", "技术部", 15000),
("李四", "销售部", 12000),
("王五", "财务部", 13000)
]
for idx, emp inenumerate(employees, start=2):
ws[f'A{idx}'] = emp[0]
ws[f'B{idx}'] = emp[1]
ws[f'C{idx}'] = emp[2]
# 保存文件
wb.save('employee_data.xlsx')
实用案例
案例 1：批量合并多个 Excel 文件
import pandas as pd
import glob
defmerge_excel_files(folder_path, output_file):
"""合并指定文件夹下的所有 Excel 文件"""
all_files = glob.glob(f"{folder_path}/*.xlsx")
df_list = []
for file in all_files:
df = pd.read_excel(file)
df_list.append(df)
merged_df = pd.concat(df_list, ignore_index=True)
merged_df.to_excel(output_file, index=False)
print(f"已合并 {len(all_files)} 个文件到 {output_file}")
# 使用示例
merge_excel_files('./monthly_reports', 'annual_report.xlsx')
案例 2：数据筛选与统计
defanalyze_sales_data(file_path):
"""分析销售数据"""
df = pd.read_excel(file_path)
# 筛选销售额大于 10000 的记录
high_value = df[df['销售额'] > 10000]
# 按部门分组统计
dept_summary = df.groupby('部门')['销售额'].sum().reset_index()
# 计算总体统计信息
stats = {
'总销售额': df['销售额'].sum(),
'平均销售额': df['销售额'].mean(),
'最大订单': df['销售额'].max(),
'最小订单': df['销售额'].min()
}
return high_value, dept_summary, stats
# 使用示例
high_value, summary, stats = analyze_sales_data('sales_2025.xlsx')
print(stats)
案例 3：自动生成月度报表
defgenerate_monthly_report(template_file, data_file, output_file):
"""根据模板和数据生成月度报表"""
from openpyxl import load_workbook
# 加载模板
wb = load_workbook(template_file)
ws = wb.active
# 读取数据
df = pd.read_excel(data_file)
# 填充数据
ws['B2'] = f"{df['月份'].iloc[0]}月报表"
ws['D5'] = df['收入'].sum()
ws['D6'] = df['支出'].sum()
ws['D7'] = df['利润'].sum()
# 添加详细数据表
start_row = 10
for idx, row in df.iterrows():
ws[f'A{start_row + idx}'] = row['日期']
ws[f'B{start_row + idx}'] = row['项目']
ws[f'C{start_row + idx}'] = row['金额']
wb.save(output_file)
print(f"报表已生成：{output_file}")
案例 4：Excel 格式美化
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
defformat_excel_beautiful(file_path):
"""美化 Excel 表格样式"""
wb = load_workbook(file_path)
ws = wb.active
# 定义样式
title_font = Font(name='微软雅黑', size=16, bold=True, color='FFFFFF')
header_font = Font(name='微软雅黑', size=11, bold=True)
normal_font = Font(name='微软雅黑', size=10)
blue_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
light_blue_fill = PatternFill(start_color='D6DCE4', end_color='D6DCE4', fill_type='solid')
center_alignment = Alignment(horizontal='center', vertical='center')
# 设置标题行
ws.row_dimensions[1].height = 25
for cell in ws[1]:
cell.font = title_font
cell.fill = blue_fill
cell.alignment = center_alignment
# 设置表头
ws.row_dimensions[2].height = 20
for cell in ws[2]:
cell.font = header_font
cell.fill = light_blue_fill
cell.alignment = center_alignment
# 设置列宽
ws.column_dimensions['A'].width = 15
ws.column_dimensions['B'].width = 20
ws.column_dimensions['C'].width = 12
# 添加边框
thin_border = Border(
left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin')
)
for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
for cell in row:
cell.border = thin_border
wb.save(file_path)
高级技巧
数据验证
from openpyxl.worksheet.datavalidation import DataValidation
# 创建下拉列表
dv = DataValidation(type="list", formula1='"选项 A,选项 B,选项 C'")
ws.add_data_validation(dv)
dv.add('A1:A100')
# 数字范围验证
num_dv = DataValidation(type="whole", operator="between", formula1="1", formula2="100")
ws.add_data_validation(num_dv)
num_dv.add('B1:B100')
条件格式
from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import Font
# 大于 10000 的值显示为红色
red_font = Font(color='FF0000')
rule = CellIsRule(operator='greaterThan', formula=['10000'], font=red_font)
ws.conditional_formatting.add('C1:C100', rule)
创建图表
from openpyxl.chart import BarChart, Reference
defcreate_chart(ws):
"""在 Excel 中创建柱状图"""
chart = BarChart()
chart.type = "col"
chart.style = 10
chart.title = "销售统计"
chart.y_axis.title = '销售额'
chart.x_axis.title = '月份'
data = Reference(ws, min_col=2, min_row=1, max_col=2, max_row=12)
cats = Reference(ws, min_col=1, min_row=2, max_row=12)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, "E1")
最佳实践
- 1. 使用 pandas 进行数据分析：pandas 提供了更简洁高效的数据操作接口
- 3. 异常处理：添加 try-except 块处理可能的错误
- 5. 性能优化：对于大文件，使用
read_only 和 write_only 模式
常见问题解决
问题 1：中文乱码
# 确保系统编码正确，使用 UTF-8
df.to_excel('output.xlsx', engine='openpyxl', encoding='utf-8')
问题 2：文件格式不支持
# .xls 文件使用 xlrd 读取
df = pd.read_excel('old_file.xls', engine='xlrd')
# .xlsx 文件使用 openpyxl
df = pd.read_excel('new_file.xlsx', engine='openpyxl')
问题 3：内存不足
# 分块读取大文件
chunk_iter = pd.read_excel('large_file.xlsx', chunksize=1000)
for chunk in chunk_iter:
process(chunk)
总结
通过本文的学习，你已经掌握了使用 Python 处理 Excel 文件的核心技能。从基本的读写操作到复杂的数据分析，这些技巧将极大提升你的办公效率。记住，自动化的关键在于找到重复性工作的规律，然后用代码将其固化下来。
在下一篇文章中，我们将学习如何使用 Python 处理 Word 文档，敬请期待！
练习作业
- 1. 编写一个脚本，将多个 Excel 文件合并为一个
- 2. 创建一个销售数据分析报表，包含统计信息和图表
如果你觉得这篇文章有帮助，欢迎分享给更多需要的朋友！
10个月宝宝每天需要喝多少奶粉？