Python 操作 Excel 详细教程

Python 提供了多个强大的库来操作 Excel 文件，包括读取、写入、修改和分析数据。以下是主要的 Excel 操作库及其使用方法。

1. 常用 Excel 操作库

1.1 openpyxl

• 特点：处理 .xlsx 格式，支持读写和修改
• 适用场景：需要修改现有 Excel 文件或创建新文件

1.2 xlrd/xlwt

• 特点：xlrd 用于读取 .xls，xlwt 用于写入 .xls
• 适用场景：处理旧版 Excel 文件 (2003 以前版本)

1.3 pandas

• 特点：高级数据处理，底层依赖 openpyxl/xlrd
• 适用场景：数据分析、批量处理

1.4 xlsxwriter

• 特点：仅支持写入 .xlsx，功能强大
• 适用场景：需要创建复杂格式的 Excel 文件

2. openpyxl 详细使用

2.1 安装

pip install openpyxl

2.2 基本操作

创建新 Excel 文件

from openpyxl import Workbook
# 创建工作簿
wb = Workbook()
# 获取活动工作表
ws = wb.active
# 设置工作表标题
ws.title = "我的工作表"
# 写入数据
ws['A1'] = "姓名"
ws['B1'] = "年龄"
ws['A2'] = "张三"
ws['B2'] = 25
# 保存文件
wb.save("example.xlsx")

读取现有 Excel 文件

from openpyxl import load_workbook
# 加载工作簿
wb = load_workbook("example.xlsx")
# 获取工作表
ws = wb["我的工作表"]
# 读取单元格数据
print(ws['A1'].value)  # 输出: 姓名
print(ws['B2'].value)  # 输出: 25
# 遍历行
for row in ws.iter_rows(values_only=True):
    print(row)

修改 Excel 文件

from openpyxl import load_workbook
wb = load_workbook("example.xlsx")
ws = wb.active
# 修改单元格
ws['B2'] = 26
# 添加新行
ws.append(["李四", 30])
# 保存修改
wb.save("example_modified.xlsx")

2.3 高级功能

样式设置

from openpyxl.styles import Font, Alignment, Border, Side
# 设置字体
ws['A1'].font = Font(bold=True, color="FF0000")
# 设置对齐
ws['A1'].alignment = Alignment(horizontal="center")
# 设置边框
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=1, max_row=2):
    for cell in row:
        cell.border = thin_border

公式计算

ws['B3'] = "=SUM(B1:B2)"

图表操作

from openpyxl.chart import BarChart, Reference
# 创建图表
chart = BarChart()
data = Reference(ws, min_col=2, min_row=1, max_row=3)
categories = Reference(ws, min_col=1, min_row=2, max_row=3)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
# 添加图表到工作表
ws.add_chart(chart, "D1")

3. pandas 操作 Excel

3.1 安装

pip install pandas openpyxl xlrd

3.2 基本操作

读取 Excel

import pandas as pd
# 读取整个文件
df = pd.read_excel("example.xlsx", sheet_name="我的工作表")
# 读取特定范围
df = pd.read_excel("example.xlsx", sheet_name="我的工作表", usecols="A:B", nrows=5)

写入 Excel

# 创建 DataFrame
data = {
    "姓名": ["张三", "李四", "王五"],
    "年龄": [25, 30, 28],
    "城市": ["北京", "上海", "广州"]
}
df = pd.DataFrame(data)
# 写入 Excel
df.to_excel("output.xlsx", index=False, sheet_name="员工信息")
# 写入多个工作表
with pd.ExcelWriter("multi_sheet.xlsx") as writer:
    df.to_excel(writer, sheet_name="Sheet1", index=False)
    df.head(2).to_excel(writer, sheet_name="Sheet2", index=False)

修改 Excel

# 读取文件
df = pd.read_excel("example.xlsx")
# 修改数据
df.loc[df["姓名"] == "张三", "年龄"] = 26
# 添加新列
df["入职年份"] = 2023 - df["年龄"] + 22
# 保存修改
df.to_excel("modified.xlsx", index=False)

4. xlrd/xlwt 操作旧版 Excel

4.1 安装

pip install xlrd xlwt

4.2 基本操作

读取 .xls 文件

import xlrd
# 打开工作簿
book = xlrd.open_workbook("old_example.xls")
# 获取工作表
sheet = book.sheet_by_index(0)  # 第一个工作表
# 读取数据
print(sheet.cell_value(0, 0))  # 第一行第一列
print(sheet.nrows)  # 总行数
print(sheet.ncols)  # 总列数
# 遍历行
for row_idx in range(sheet.nrows):
    print(sheet.row_values(row_idx))

写入 .xls 文件

import xlwt
# 创建工作簿
book = xlwt.Workbook()
# 添加工作表
sheet = book.add_sheet("员工信息")
# 写入数据
sheet.write(0, 0, "姓名")
sheet.write(0, 1, "年龄")
data = [("张三", 25), ("李四", 30)]
for row_idx, row_data in enumerate(data, start=1):
    for col_idx, cell_data in enumerate(row_data):
        sheet.write(row_idx, col_idx, cell_data)
# 保存文件
book.save("new_old_example.xls")

5. xlsxwriter 高级写入

5.1 安装

pip install XlsxWriter

5.2 高级写入功能

import xlsxwriter
# 创建工作簿
workbook = xlsxwriter.Workbook('advanced.xlsx')
worksheet = workbook.add_worksheet()
# 设置格式
bold = workbook.add_format({'bold': True})
money_format = workbook.add_format({'num_format': '$#,##0'})
# 写入数据
worksheet.write('A1', '项目', bold)
worksheet.write('B1', '金额', bold)
data = [("电脑", 5000), ("手机", 3000), ("平板", 2000)]
for row, (item, cost) in enumerate(data, start=1):
    worksheet.write(row, 0, item)
    worksheet.write(row, 1, cost, money_format)
# 添加公式
worksheet.write(len(data)+1, 0, "总计", bold)
worksheet.write(len(data)+1, 1, f'=SUM(B2:B{len(data)+1})', money_format)
# 添加图表
chart = workbook.add_chart({'type': 'column'})
chart.add_series({
    'categories': f'=Sheet1!$A$2:$A${len(data)+1}',
    'values': f'=Sheet1!$B$2:$B${len(data)+1}',
})
worksheet.insert_chart('D2', chart)
# 关闭工作簿
workbook.close()

6. 性能优化技巧

1. 批量写入：对于大量数据，先收集到列表中，然后一次性写入
2. 只读模式：如果只需要读取数据，使用read_only=True参数
3. 仅写入模式：如果只需要写入，使用write_only=True参数
4. 禁用计算：在写入大量公式时，临时禁用计算
5. 内存优化：处理大文件时考虑使用chunksize参数分块读取

   # pandas 分块读取大文件
   chunk_size = 1000
   for chunk in pd.read_excel("large_file.xlsx", chunksize=chunk_size):
    process(chunk)  # 处理每个数据块
   

7. 常见问题解决

1. 编码问题：指定正确的编码格式，如encoding='utf-8'
2. 日期处理：Excel 日期是数字，需要转换

   from datetime import datetime
   excel_date = 44197  # Excel中的日期数字
   py_date = datetime.fromordinal(datetime(1900, 1, 1).toordinal() + excel_date - 2)
   

3. 合并单元格处理：使用merged_cells属性检查合并单元格
4. 大文件处理：考虑使用dask库处理超大Excel文件

总结

根据需求选择合适的库：

• 简单读写：openpyxl 或 pandas
• 数据分析：pandas
• 旧版 .xls 文件：xlrd/xlwt
• 复杂格式创建：xlsxwriter
• 高性能需求：考虑分块处理或专用库 以上方法涵盖了Python操作Excel的大部分场景，可以根据具体需求选择最适合的方式。