1. 概述与库介绍
1.1 Excel自动化的重要性
在数据处理领域，Excel是最常用的工具之一。手动操作Excel对于小规模数据和简单任务尚可，但当面对：
- 大规模数据集
- 重复性操作
- 复杂计算和分析
时，手动操作效率低下且容易出错。Python提供了多种强大的库来自动化这些流程。
1.2 Python操作Excel的主要库
| 库名称 | 功能特点 | 支持格式 | 安装命令 |
|---|---|---|---|
| xlrd | 读取Excel数据 | xls/xlsx | pip install xlrd |
| xlwt | 写入Excel数据 | xls | pip install xlwt |
| xlutils | Excel实用工具 | xls | pip install xlutils |
| XlsxWriter | 高级写入功能，支持图表 | xlsx | pip install XlsxWriter |
| openpyxl | 完整读写功能 | xlsx | pip install openpyxl |
1.3 环境准备
pip install xlrd xlwt xlutils XlsxWriter openpyxl
2. 基础写入操作
2.1 使用xlwt创建简单表格
import xlwt
from datetime import datetime# 创建工作簿
wb = xlwt.Workbook()# 添加工作表
sh = wb.add_sheet('员工信息')# 设置样式
style_header = xlwt.XFStyle()
font_header = xlwt.Font()
font_header.bold = True
style_header.font = font_headerstyle_data = xlwt.XFStyle()
style_data.num_format_str = 'YYYY-MM-DD' # 日期格式# 创建默认样式（无特殊格式）
style_default = xlwt.XFStyle()# 写入表头
headers = ['ID', '姓名', '部门', '入职日期', '薪资']
for col, header in enumerate(headers):sh.write(0, col, header, style_header)# 写入数据
employees = [[1, '张三', '技术部', datetime(2020,5,15), 15000],[2, '李四', '市场部', datetime(2019,8,22), 12000],[3, '王五', '财务部', datetime(2021,3,10), 13500]
]for row, emp in enumerate(employees, start=1):for col, value in enumerate(emp):# 使用三元表达式选择样式current_style = style_data if col == 3 else style_defaultsh.write(row, col, value, current_style)# 保存文件
wb.save('employee_info.xls')
print("员工信息表已成功创建")
2.2 使用XlsxWriter创建带图表的报表
import xlsxwriter# 创建工作簿
wb = xlsxwriter.Workbook('sales_report.xlsx')
ws = wb.add_worksheet('季度销售')# 定义格式
fmt_header = wb.add_format({'bold': True, 'bg_color': '#4F81BD','font_color': 'white','align': 'center'
})# 数据
data = [['季度', '产品A', '产品B', '产品C'],['Q1', 150000, 200000, 180000],['Q2', 180000, 210000, 190000],['Q3', 220000, 180000, 210000]
]# 写入数据
for row_num, row_data in enumerate(data):ws.write_row(row_num, 0, row_data, fmt_header if row_num == 0 else None)# 创建图表
chart = wb.add_chart({'type': 'column'})
chart.add_series({'name': '=季度销售!$B$1','categories': '=季度销售!$A$2:$A$4','values': '=季度销售!$B$2:$B$4'
})
# 可添加更多系列...# 插入图表
ws.insert_chart('F2', chart)
wb.close()
3. 数据读取操作
3.1 使用xlrd读取数据
import xlrddef read_excel(file_path):wb = xlrd.open_workbook(file_path)sh = wb.sheet_by_index(0)print(f"工作表 '{sh.name}' 包含 {sh.nrows} 行 {sh.ncols} 列")# 读取表头print("\n表头:", sh.row_values(0))# 读取数据print("\n数据样例:")for row in range(1, min(4, sh.nrows)): # 只显示前3行数据print(sh.row_values(row))# 获取特定单元格print("\n特定单元格B2:", sh.cell_value(1, 1))read_excel('employee_info.xls')
3.2 使用openpyxl读取现代Excel
from openpyxl import load_workbookdef read_xlsx(file_path):wb = load_workbook(file_path)ws = wb.activeprint(f"\n读取 {file_path} 中的数据:")for row in ws.iter_rows(values_only=True):print(row)read_xlsx('sales_report.xlsx')
4. 数据修改与更新
4.1 使用xlutils修改现有文件
from xlrd import open_workbook
from xlutils.copy import copydef update_salary(file_path, raise_percent=0.1):# 打开文件并保留格式rb = open_workbook(file_path, formatting_info=True)wb = copy(rb)ws = wb.get_sheet(0)# 获取原数据sh = rb.sheet_by_index(0)# 更新薪资for row in range(1, sh.nrows):old_salary = sh.cell_value(row, 4)new_salary = old_salary * (1 + raise_percent)ws.write(row, 4, new_salary)# 添加备注ws.write(sh.nrows, 0, f"注：薪资已上调{raise_percent*100}%")# 保存new_file = file_path.replace('.xls', '_updated.xls')wb.save(new_file)print(f"\n文件已更新: {new_file}")update_salary('employee_info.xls')
4.2 使用openpyxl修改现代Excel
from openpyxl import load_workbook
from openpyxl.styles import Fontdef add_bonus(file_path, bonus=2000):wb = load_workbook(file_path)ws = wb.active# 添加新列ws['F1'] = "奖金"ws['F1'].font = Font(bold=True)# 填充奖金数据for row in range(2, ws.max_row + 1):ws[f'F{row}'] = bonus# 添加总计列ws['G1'] = "总计"for row in range(2, ws.max_row + 1):ws[f'G{row}'] = f'=E{row}+F{row}'wb.save(file_path.replace('.xlsx', '_with_bonus.xlsx'))add_bonus('sales_report.xlsx')
5. 高级功能示例
5.1 使用openpyxl创建专业报表
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill
from openpyxl.chart import PieChart, Referencedef create_project_report():wb = Workbook()ws = wb.activews.title = "项目状态"# 设置样式header_font = Font(bold=True, color="FFFFFF")header_fill = PatternFill(start_color="4472C4", fill_type="solid")# 数据data = [["项目", "进度%", "状态"],["官网改版", 85, "进行中"],["APP开发", 65, "进行中"],["系统升级", 45, "延期"]]# 写入数据for row in data:ws.append(row)# 应用样式for cell in ws[1]:cell.font = header_fontcell.fill = header_fill# 创建饼图chart = PieChart()labels = Reference(ws, min_col=1, min_row=2, max_row=4)data = Reference(ws, min_col=2, min_row=2, max_row=4)chart.add_data(data, titles_from_data=False)chart.set_categories(labels)chart.title = "项目进度分布"ws.add_chart(chart, "E2")wb.save("project_status.xlsx")create_project_report()
6. 库对比与选择指南
功能对比表
| 功能需求 | 推荐库 | 原因说明 |
|---|---|---|
| 读取旧版Excel(.xls) | xlrd | 专为读取xls设计 |
| 创建简单xls文件 | xlwt | 轻量级解决方案 |
| 修改现有xls文件 | xlutils | 能保留原格式 |
| 创建带图表的专业报表 | XlsxWriter | 图表功能强大 |
| 读写现代Excel文件 | openpyxl | 功能最全面 |
| 大数据量处理 | openpyxl只读模式 | 内存效率高 |
常见问题解决方案
-
日期处理问题
# xlrd读取日期（返回的是Excel日期数字） date_value = xlrd.xldate_as_datetime(cell_value, wb.datemode)# openpyxl直接读取datetime对象 cell.value # 直接返回Python datetime对象 -
公式计算
# XlsxWriter写入公式 worksheet.write_formula('A1', '=SUM(B1:B10)')# openpyxl刷新公式 wb = load_workbook('file.xlsx', data_only=True) # 获取计算后的值 -
性能优化
# openpyxl只读模式处理大文件 from openpyxl import load_workbook wb = load_workbook('large_file.xlsx', read_only=True)# openpyxl只写模式生成大文件 wb = Workbook(write_only=True)
7. 最佳实践总结
-
项目开始前：
- 明确需要处理的Excel版本（xls还是xlsx）
- 评估需要的功能（是否需要图表、条件格式等）
- 考虑数据量大小
-
代码编写时：
- 封装常用操作为函数
- 添加适当的异常处理
- 编写清晰的注释
-
性能关键点：
- 批量操作优于单个单元格操作
- 尽量减少样式设置
- 大文件使用只读/只写模式
-
扩展学习：
# 使用pandas简化数据分析 import pandas as pd df = pd.read_excel('data.xlsx') # 读取 df.to_excel('output.xlsx') # 写入
通过本指南，您应该已经掌握了Python操作Excel的完整技能栈，能够根据不同的项目需求选择合适的工具和方法，实现Excel处理的自动化，大大提高工作效率。
