Python操作Excel，一篇就够了：从入门到精通的常用方法大全

引言

在数据处理和办公自动化领域，Excel无疑是最广泛使用的工具之一。而Python，凭借其丰富的第三方库生态，成为了操作Excel的强大助手。无论你是财务分析师、数据科学家，还是行政人员，掌握Python操作Excel的技能，都能让你的工作效率提升数倍。

本文将全面系统地介绍Python操作Excel的所有常用方法，涵盖从基础读写到高级图表、从数据清洗到自动化报表的全流程技术。一文在手，Excel无忧！

一、Python操作Excel的库生态全景图

在开始编写代码之前，我们需要了解Python生态中主流的Excel操作库，根据不同的需求选择合适的工具。

1.1 核心库对比

1.2 选型建议

根据我的实战经验，给出以下选型建议：

• 日常数据分析：首选pandas + openpyxl（作为引擎）
• 需要保留模板格式：openpyxl是唯一选择
• 生成高质量图表报表：xlsxwriter功能最强大
• 处理旧版.xls文件：使用xlrd/xlwt组合
• 需要调用Excel高级功能：xlwings可以操作Excel应用程序

1.3 环境安装

# 安装最常用的几个库
pip install openpyxl pandas xlsxwriter xlrd xlwt matplotlib

二、基础操作：文件的读写与遍历

2.1 使用openpyxl读写Excel

创建新工作簿

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment

# 创建工作簿
wb = Workbook()
ws = wb.active
ws.title = "销售数据"

# 写入数据
ws['A1'] = "产品名称"
ws['B1'] = "销售额"
ws['A2'] = "产品A"
ws['B2'] = 10000

# 设置样式
ws['A1'].font = Font(bold=True)
ws['B1'].font = Font(bold=True)

# 保存文件
wb.save("sales_report.xlsx")

读取现有文件

from openpyxl import load_workbook

# 加载工作簿
wb = load_workbook("sales_report.xlsx")
ws = wb.active  # 或 wb['销售数据']

# 读取单个单元格
value = ws['A2'].value
print(f"A2: {value}")

# 遍历所有行
for row in ws.iter_rows(min_row=2, max_row=ws.max_row, values_only=True):
    product, sales = row
    print(f"{product}: {sales}元")

# 读取指定区域
data = []
for row in ws.iter_rows(min_row=2, max_col=2, values_only=True):
    data.append(row)

wb.close()

2.2 使用pandas读写Excel

pandas是数据处理的瑞士军刀，适合批量操作 。

import pandas as pd

# 读取Excel文件
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')
print(df.head())  # 查看前5行

# 数据筛选
filtered = df[df['销售额'] > 10000]

# 新增列
df['利润率'] = df['利润'] / df['销售额'] * 100

# 分组统计
summary = df.groupby('部门')['销售额'].agg(['sum', 'mean', 'count'])

# 写入新文件
with pd.ExcelWriter('output.xlsx', engine='openpyxl') as writer:
    df.to_excel(writer, sheet_name='原始数据', index=False)
    summary.to_excel(writer, sheet_name='统计汇总')

小技巧：pandas 2.2+版本引入了更快的calamine引擎，处理大文件时性能提升显著 。

2.3 使用xlsxwriter写入Excel

xlsxwriter专注于高质量写入，特别适合报表生成 。

import xlsxwriter

# 创建工作簿
workbook = xlsxwriter.Workbook('report.xlsx')
worksheet = workbook.add_worksheet('销售报表')

# 定义格式
bold = workbook.add_format({'bold': True, 'font_color': 'blue'})
money = workbook.add_format({'num_format': '¥#,##0'})

# 写入数据
worksheet.write('A1', '产品', bold)
worksheet.write('B1', '销售额', bold)

expenses = [
    ['产品A', 15000],
    ['产品B', 23000],
    ['产品C', 12000],
]

row = 1
for product, amount in expenses:
    worksheet.write(row, 0, product)
    worksheet.write(row, 1, amount, money)
    row += 1

# 添加公式
worksheet.write(row, 0, '合计', bold)
worksheet.write(row, 1, '=SUM(B2:B4)', money)

workbook.close()

2.4 处理旧版.xls文件

虽然现在.xlsx是主流，但偶尔还是会遇到遗留的.xls文件 。

import xlrd
import xlwt

# 读取.xls
workbook = xlrd.open_workbook('old_data.xls')
sheet = workbook.sheet_by_index(0)

# 遍历行
for row in range(sheet.nrows):
    print(sheet.row_values(row))

# 写入.xls
new_workbook = xlwt.Workbook()
new_sheet = new_workbook.add_sheet('Sheet1')
new_sheet.write(0, 0, 'Hello')
new_workbook.save('new_file.xls')

三、高级操作：样式、公式与数据验证

3.1 单元格样式设置

openpyxl样式设置

from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
from openpyxl import load_workbook

wb = load_workbook('template.xlsx')
ws = wb.active

# 设置字体
header_font = Font(name='微软雅黑', size=12, bold=True, color='FFFFFF')

# 设置背景填充
header_fill = PatternFill(start_color='4F81BD', end_color='4F81BD', fill_type='solid')

# 设置对齐
center_align = Alignment(horizontal='center', vertical='center')

# 设置边框
thin_border = Border(
    left=Side(style='thin'), 
    right=Side(style='thin'), 
    top=Side(style='thin'), 
    bottom=Side(style='thin')
)

# 应用到表头
for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = center_align
    cell.border = thin_border

wb.save('styled_report.xlsx')

xlsxwriter格式设置

import xlsxwriter

workbook = xlsxwriter.Workbook('formats.xlsx')
worksheet = workbook.add_worksheet()

# 创建多种格式
header_format = workbook.add_format({
    'bold': True,
    'font_color': 'white',
    'bg_color': '#4F81BD',
    'align': 'center',
    'valign': 'vcenter',
    'border': 1
})

percent_format = workbook.add_format({'num_format': '0.00%'})
date_format = workbook.add_format({'num_format': 'yyyy-mm-dd'})
currency_format = workbook.add_format({'num_format': '¥#,##0.00'})

# 应用格式
worksheet.write('A1', '日期', header_format)
worksheet.write('B1', '销售额', header_format)
worksheet.write('A2', '2025-01-15', date_format)
worksheet.write('B2', 15000.50, currency_format)

workbook.close()

3.2 公式的使用

Excel的灵魂在于公式，Python操作Excel自然也支持公式。

openpyxl中使用公式

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# 写入数据
data = [
    ['产品', '一月', '二月', '三月'],
    ['A', 100, 150, 200],
    ['B', 200, 250, 300],
    ['C', 150, 180, 220],
]

for row in data:
    ws.append(row)

# 添加公式
ws['E2'] = '=SUM(B2:D2)'  # 行求和
ws['B5'] = '=AVERAGE(B2:B4)'  # 列平均值

# 复杂公式
ws['A7'] = '总计'
ws['B7'] = '=SUM(B2:B4)'
ws['C7'] = '=IF(B7>500,"达标","不达标")'

wb.save('formulas.xlsx')

xlsxwriter中的公式

import xlsxwriter

workbook = xlsxwriter.Workbook('formulas_xl.xlsx')
worksheet = workbook.add_worksheet()

# 写入数据
worksheet.write('A1', '产品')
worksheet.write('B1', '销售额')
worksheet.write('A2', 'A')
worksheet.write('B2', 500)
worksheet.write('A3', 'B')
worksheet.write('B3', 600)
worksheet.write('A4', 'C')
worksheet.write('B4', 400)

# 使用公式
worksheet.write('B5', '=SUM(B2:B4)')
worksheet.write('B6', '=AVERAGE(B2:B4)')
worksheet.write('B7', '=MAX(B2:B4)')
worksheet.write('B8', '=MIN(B2:B4)')

# 使用IF公式
worksheet.write('C2', '=IF(B2>500,"优秀","普通")')
worksheet.write('C3', '=IF(B3>500,"优秀","普通")')

workbook.close()

3.3 数据验证与下拉列表

使用openpyxl添加数据验证

from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation

wb = Workbook()
ws = wb.active

# 创建数据验证：下拉列表
dv = DataValidation(
    type="list", 
    formula1='"选项A,选项B,选项C"', 
    allow_blank=True
)

# 添加到工作表
ws.add_data_validation(dv)

# 应用到单元格区域
dv.add('A2:A10')

ws['A1'] = '请选择'
ws['A2'] = '选项A'

wb.save('data_validation.xlsx')

xlsxwriter的数据验证

import xlsxwriter

workbook = xlsxwriter.Workbook('validation.xlsx')
worksheet = workbook.add_worksheet()

# 数据验证：下拉列表
worksheet.data_validation(
    'B2:B10',
    {
        'validate': 'list',
        'source': ['选项1', '选项2', '选项3'],
        'input_title': '请选择',
        'input_message': '从下拉列表中选择'
    }
)

# 数据验证：数值范围
worksheet.data_validation(
    'C2:C10',
    {
        'validate': 'integer',
        'criteria': 'between',
        'minimum': 1,
        'maximum': 100,
        'error_title': '输入错误',
        'error_message': '数值必须在1-100之间'
    }
)

workbook.close()

3.4 合并单元格与调整行高列宽

from openpyxl import Workbook
from openpyxl.styles import Alignment

wb = Workbook()
ws = wb.active

# 合并单元格
ws.merge_cells('A1:D1')
cell = ws['A1']
cell.value = '这是合并后的标题'
cell.alignment = Alignment(horizontal='center', vertical='center')

# 调整列宽
ws.column_dimensions['A'].width = 20
ws.column_dimensions['B'].width = 15

# 调整行高
ws.row_dimensions[1].height = 30

# 插入行和列 
ws.insert_rows(2)  # 在第2行上方插入一行
ws.insert_cols(2)  # 在第2列左侧插入一列

# 删除行和列
ws.delete_rows(5)
ws.delete_cols(3)

wb.save('merge_and_size.xlsx')

四、图表与可视化

图表是数据呈现的最佳方式，Python可以生成各类Excel图表。

4.1 openpyxl创建图表

创建柱状图

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

wb = Workbook()
ws = wb.active

# 准备数据
data = [
    ['季度', '销售额'],
    ['Q1', 8500],
    ['Q2', 9200],
    ['Q3', 7800],
    ['Q4', 10500],
]

for row in data:
    ws.append(row)

# 创建图表
chart = BarChart()
chart.title = "季度销售额"
chart.x_axis.title = "季度"
chart.y_axis.title = "销售额"

# 选择数据
data = Reference(ws, min_col=2, min_row=1, max_row=5)
categories = Reference(ws, min_col=1, min_row=2, max_row=5)

# 添加数据
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)

# 插入图表
ws.add_chart(chart, "E2")

wb.save("bar_chart.xlsx")

创建饼图

from openpyxl.chart import PieChart, Reference

# 创建饼图
chart = PieChart()
chart.title = "各产品占比"

# 数据范围
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
data = Reference(ws, min_col=2, min_row=1, max_row=5)

chart.add_data(data, titles_from_data=True)
chart.set_categories(labels)

ws.add_chart(chart, "E15")

4.2 xlsxwriter创建高级图表

xlsxwriter的图表功能更加强大，支持更多图表类型和自定义选项 。

import xlsxwriter

workbook = xlsxwriter.Workbook('charts.xlsx')
worksheet = workbook.add_worksheet()

# 准备数据
data = [
    ['月份', '产品A', '产品B'],
    ['一月', 100, 120],
    ['二月', 150, 130],
    ['三月', 200, 180],
    ['四月', 180, 210],
    ['五月', 220, 240],
    ['六月', 250, 230],
]

for row_num, row_data in enumerate(data):
    worksheet.write_row(row_num, 0, row_data)

# 创建柱状图
bar_chart = workbook.add_chart({'type': 'column'})
bar_chart.add_series({
    'name': '=Sheet1!$B$1',
    'categories': '=Sheet1!$A$2:$A$7',
    'values': '=Sheet1!$B$2:$B$7',
    'data_labels': {'value': True},
})
bar_chart.add_series({
    'name': '=Sheet1!$C$1',
    'categories': '=Sheet1!$A$2:$A$7',
    'values': '=Sheet1!$C$2:$C$7',
})
bar_chart.set_title({'name': '产品销售趋势'})
bar_chart.set_x_axis({'name': '月份'})
bar_chart.set_y_axis({'name': '销售额'})
bar_chart.set_legend({'position': 'bottom'})

# 创建折线图
line_chart = workbook.add_chart({'type': 'line'})
line_chart.add_series({
    'name': '=Sheet1!$B$1',
    'categories': '=Sheet1!$A$2:$A$7',
    'values': '=Sheet1!$B$2:$B$7',
    'line': {'color': 'red', 'width': 2},
    'marker': {'type': 'circle', 'size': 5},
})

worksheet.insert_chart('E2', bar_chart, {'x_offset': 25, 'y_offset': 10})
worksheet.insert_chart('E20', line_chart)

workbook.close()

4.3 组合图与双轴图表

# 创建组合图（柱状图+折线图）
chart = workbook.add_chart({'type': 'column'})

# 第一个系列（柱状图）
chart.add_series({
    'name': '=Sheet1!$B$1',
    'values': '=Sheet1!$B$2:$B$7',
})

# 第二个系列（折线图，使用次坐标轴）
chart.add_series({
    'name': '=Sheet1!$C$1',
    'values': '=Sheet1!$C$2:$C$7',
    'y2_axis': True,  # 使用次坐标轴
    'type': 'line',   # 覆盖图表类型
})

五、数据清洗与处理

5.1 使用pandas进行数据清洗

pandas是数据清洗的最佳工具 。

import pandas as pd
import numpy as np

# 读取数据
df = pd.read_excel('raw_data.xlsx')

# 查看基本信息
print(df.info())
print(df.describe())

# 处理缺失值
df.dropna(subset=['关键字段'], inplace=True)  # 删除关键字段为空的行
df['数值列'].fillna(df['数值列'].mean(), inplace=True)  # 用均值填充
df['文本列'].fillna('未知', inplace=True)  # 用指定值填充

# 删除重复值
df.drop_duplicates(subset=['ID'], keep='first', inplace=True)

# 数据类型转换
df['日期'] = pd.to_datetime(df['日期'])
df['金额'] = pd.to_numeric(df['金额'], errors='coerce')

# 文本处理
df['产品名称'] = df['产品名称'].str.strip()  # 去除空格
df['类别'] = df['类别'].str.upper()  # 转为大写

# 条件筛选
high_sales = df[df['销售额'] > 10000]
specific_products = df[df['产品'].isin(['A', 'B'])]

# 分组聚合
result = df.groupby('部门').agg({
    '销售额': ['sum', 'mean', 'count'],
    '利润': 'sum'
}).round(2)

# 保存清洗后的数据
result.to_excel('cleaned_data.xlsx')

5.2 多文件批量处理

import pandas as pd
import glob
import os

# 批量读取所有Excel文件
folder_path = './data/'
all_files = glob.glob(os.path.join(folder_path, "*.xlsx"))

df_list = []
for file in all_files:
    df = pd.read_excel(file)
    df['来源文件'] = os.path.basename(file)  # 添加来源标记
    df_list.append(df)

# 合并所有数据
combined_df = pd.concat(df_list, ignore_index=True)

# 按条件筛选
filtered_df = combined_df[combined_df['日期'] >= '2025-01-01']

# 生成汇总报表
summary = filtered_df.pivot_table(
    values='销售额',
    index='部门',
    columns='月份',
    aggfunc='sum',
    fill_value=0
)

# 输出到Excel
with pd.ExcelWriter('合并报表.xlsx', engine='openpyxl') as writer:
    combined_df.to_excel(writer, sheet_name='全部数据', index=False)
    summary.to_excel(writer, sheet_name='透视汇总')

六、自动化报表实战案例

6.1 使用模板生成报表

这是企业中最常用的场景：保留模板格式，只填充数据 。

from openpyxl import load_workbook
import pandas as pd
from datetime import datetime

def generate_monthly_report(month, data):
    """
    根据模板生成月度报表
    """
    # 1. 加载模板
    template_path = '月度报表模板.xlsx'
    wb = load_workbook(template_path)
    ws = wb['报表']
    
    # 2. 填充标题（使用占位符替换）
    ws['B2'] = f'{month}月度销售报表'
    ws['B3'] = f'生成时间：{datetime.now().strftime("%Y-%m-%d %H:%M:%S")}'
    
    # 3. 填充数据（从第5行开始）
    start_row = 5
    for i, row_data in enumerate(data):
        current_row = start_row + i
        ws.cell(row=current_row, column=2, value=row_data['产品'])
        ws.cell(row=current_row, column=3, value=row_data['销售额'])
        ws.cell(row=current_row, column=4, value=row_data['利润'])
        # 添加公式
        ws.cell(row=current_row, column=5, value=f'=C{current_row}/D{current_row}')
    
    # 4. 更新总计行
    last_row = start_row + len(data)
    ws[f'C{last_row+1}'] = f'=SUM(C{start_row}:C{last_row})'
    ws[f'D{last_row+1}'] = f'=SUM(D{start_row}:D{last_row})'
    
    # 5. 保存新文件
    output_path = f'{month}销售报表_{datetime.now().strftime("%Y%m%d")}.xlsx'
    wb.save(output_path)
    print(f'报表生成成功：{output_path}')
    return output_path

# 示例数据
sales_data = [
    {'产品': '产品A', '销售额': 15000, '利润': 3000},
    {'产品': '产品B', '销售额': 22000, '利润': 4500},
    {'产品': '产品C', '销售额': 18000, '利润': 3600},
]

generate_monthly_report('2026年3月', sales_data)

6.2 自动化调度系统

将报表生成自动化，定时运行 。

import schedule
import time
from datetime import datetime
import pandas as pd
from openpyxl import load_workbook

def daily_report_job():
    """每日报表任务"""
    print(f"{datetime.now()} - 开始生成日报")
    
    try:
        # 1. 从数据库获取数据
        # data = fetch_from_database()
        
        # 2. 处理数据
        # processed = process_data(data)
        
        # 3. 生成报表
        # generate_report(processed)
        
        print(f"{datetime.now()} - 日报生成完成")
    except Exception as e:
        print(f"任务失败：{e}")

def weekly_report_job():
    """每周报表任务"""
    print(f"{datetime.now()} - 开始生成周报")
    # 周报逻辑...

# 设置定时任务
schedule.every().day.at("09:00").do(daily_report_job)  # 每天9点
schedule.every().monday.at("10:00").do(weekly_report_job)  # 每周一10点

# 运行调度器
while True:
    schedule.run_pending()
    time.sleep(60)  # 每分钟检查一次

七、性能优化与避坑指南

7.1 大文件处理技巧

处理超大Excel文件时，需要注意内存管理 。

# openpyxl只读模式（适合读取大文件）
from openpyxl import load_workbook

wb = load_workbook('large_file.xlsx', read_only=True)
ws = wb.active

for row in ws.iter_rows(values_only=True):
    # 逐行处理，不占用内存
    process_row(row)

wb.close()


# pandas分块读取（虽然read_excel不支持直接分块，但可以配合其他方法）
import pandas as pd

# 方法1：先获取总行数，然后分批
df_chunks = pd.read_excel('large_file.xlsx', chunksize=10000)
for chunk in df_chunks:
    process_chunk(chunk)

# 方法2：使用skiprows和nrows分批读取
total_rows = 100000
batch_size = 10000
for i in range(0, total_rows, batch_size):
    df = pd.read_excel(
        'large_file.xlsx', 
        skiprows=range(1, i+1) if i > 0 else None,  # 跳过已读行
        nrows=batch_size
    )
    process_batch(df)

7.2 常见错误及解决方案

7.3 性能对比数据

根据实测，不同库的性能差异明显 ：

• pandas + calamine引擎：读取10万行数据约1.2秒
• pandas + openpyxl引擎：读取10万行数据约3.5秒
• openpyxl直接读取：读取10万行数据约4.8秒
• xlrd读取.xls：读取10万行数据约2.1秒

写入性能方面：

• xlsxwriter：写入10万行数据约2.8秒
• pandas + xlsxwriter：写入10万行数据约3.2秒
• openpyxl：写入10万行数据约5.6秒

建议：读取用pandas+calamine，写入用xlsxwriter，修改模板用openpyxl。

八、总结与学习资源

8.1 各库最佳实践总结

根据前面的内容，总结出各库的最佳使用场景：

8.2 学习路径建议

1. 入门阶段：掌握openpyxl的基础读写和pandas的数据处理
2. 进阶阶段：学习xlsxwriter的高级图表和格式设置
3. 实战阶段：结合模板和自动化调度，构建完整报表系统
4. 精通阶段：性能优化、异常处理、与Web系统集成