为什么使用Python操作Excel？
Excel是数据分析和商业智能中最常用的工具之一，但当处理大量数据或需要自动化复杂操作时，Python提供了更强大的解决方案。Python可以：
- 自动化重复性任务，节省大量时间
- 处理大型数据集（超过Excel的行数限制）
- 执行复杂的数据清洗和转换操作
- 整合多个数据源并进行高级分析
- 创建可重复的数据处理流程
核心Python库介绍
pandas
强大的数据分析库，提供DataFrame结构处理表格数据
主要功能：
- 读写Excel文件
- 数据清洗和转换
- 数据分析和聚合
openpyxl
专门用于读写Excel 2010 xlsx/xlsm/xltx/xltm文件
主要功能：
- 创建和修改Excel文件
- 操作单元格、行和列
- 设置格式和公式
xlrd/xlwt
用于读取(xlrd)和写入(xlwt)旧版Excel文件(.xls)
主要功能：
- 处理.xls格式文件
- 读取单元格数据和元信息
- 创建简单的Excel文件
使用pandas读写Excel文件
安装pandas和openpyxl
pip install pandas openpyxl
读取Excel文件
import pandas as pd
# 读取Excel文件
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')
# 显示前5行数据
print(df.head())
# 获取数据基本信息
print(df.info())
# 描述性统计
print(df.describe())
# 读取Excel文件
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')
# 显示前5行数据
print(df.head())
# 获取数据基本信息
print(df.info())
# 描述性统计
print(df.describe())
写入Excel文件
import pandas as pd
# 创建示例DataFrame
data = {
'Name': ['Alice', 'Bob', 'Charlie'],
'Age': [25, 30, 35],
'City': ['New York', 'London', 'Paris']
}
df = pd.DataFrame(data)
# 写入Excel文件
df.to_excel('output.xlsx', index=False, sheet_name='Users')
# 写入多个sheet
with pd.ExcelWriter('multi_sheet.xlsx') as writer:
df.to_excel(writer, sheet_name='Sheet1')
df.describe().to_excel(writer, sheet_name='Summary')
# 创建示例DataFrame
data = {
'Name': ['Alice', 'Bob', 'Charlie'],
'Age': [25, 30, 35],
'City': ['New York', 'London', 'Paris']
}
df = pd.DataFrame(data)
# 写入Excel文件
df.to_excel('output.xlsx', index=False, sheet_name='Users')
# 写入多个sheet
with pd.ExcelWriter('multi_sheet.xlsx') as writer:
df.to_excel(writer, sheet_name='Sheet1')
df.describe().to_excel(writer, sheet_name='Summary')
使用openpyxl操作Excel
安装openpyxl
pip install openpyxl
创建和修改Excel文件
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment
# 创建新工作簿
wb = Workbook()
ws = wb.active
ws.title = "员工信息"
# 添加表头
headers = ["姓名", "部门", "工资"]
ws.append(headers)
# 设置表头样式
for cell in ws[1]:
cell.font = Font(bold=True, color="FFFFFF")
cell.fill = PatternFill(start_color="4F81BD", end_color="4F81BD", fill_type="solid")
cell.alignment = Alignment(horizontal="center")
# 添加数据
data = [
["张三", "技术部", 15000],
["李四", "市场部", 12000],
["王五", "财务部", 13000]
]
for row in data:
ws.append(row)
# 添加公式计算平均工资
ws['A5'] = "平均工资"
ws['C5'] = "=AVERAGE(C2:C4)"
# 保存文件
wb.save("employees.xlsx")
from openpyxl.styles import Font, Alignment
# 创建新工作簿
wb = Workbook()
ws = wb.active
ws.title = "员工信息"
# 添加表头
headers = ["姓名", "部门", "工资"]
ws.append(headers)
# 设置表头样式
for cell in ws[1]:
cell.font = Font(bold=True, color="FFFFFF")
cell.fill = PatternFill(start_color="4F81BD", end_color="4F81BD", fill_type="solid")
cell.alignment = Alignment(horizontal="center")
# 添加数据
data = [
["张三", "技术部", 15000],
["李四", "市场部", 12000],
["王五", "财务部", 13000]
]
for row in data:
ws.append(row)
# 添加公式计算平均工资
ws['A5'] = "平均工资"
ws['C5'] = "=AVERAGE(C2:C4)"
# 保存文件
wb.save("employees.xlsx")
读取Excel文件
from openpyxl import load_workbook
# 加载工作簿
wb = load_workbook('employees.xlsx')
ws = wb.active
# 读取数据
for row in ws.iter_rows(min_row=2, values_only=True):
name, department, salary = row
print(f"姓名: {name}, 部门: {department}, 工资: {salary}")
# 获取特定单元格的值
avg_salary = ws['C5'].value
print(f"平均工资: {avg_salary}")
# 加载工作簿
wb = load_workbook('employees.xlsx')
ws = wb.active
# 读取数据
for row in ws.iter_rows(min_row=2, values_only=True):
name, department, salary = row
print(f"姓名: {name}, 部门: {department}, 工资: {salary}")
# 获取特定单元格的值
avg_salary = ws['C5'].value
print(f"平均工资: {avg_salary}")
实用技巧与最佳实践
处理大型Excel文件
当处理大型Excel文件时，使用以下方法提高性能：
- 只读模式：使用openpyxl的
read_only=True参数减少内存占用 - 分块读取：pandas的
chunksize参数分块处理大数据 - 指定列：只读取需要的列，节省内存
数据清洗技巧
- 处理缺失值：
df.dropna()或df.fillna(value) - 删除重复行：
df.drop_duplicates() - 数据类型转换：
df.astype() - 重命名列：
df.rename(columns={'old':'new'})
常见问题解决
问题： 读取Excel文件时出现编码错误
解决： 尝试指定编码：pd.read_excel('file.xlsx', encoding='latin1')
问题： 日期格式不正确
解决： 使用parse_dates参数：pd.read_excel('file.xlsx', parse_dates=['DateColumn'])
发表评论