Python操作Excel全攻略：从入门到精通

最新推荐文章于 2025-03-05 08:48:17 发布

极客代码

最新推荐文章于 2025-03-05 08:48:17 发布

阅读量1.9k

点赞数 17

分类专栏：玩转Python 文章标签： python excel 开发语言

本文链接：https://blog.csdn.net/suifengme/article/details/143835220

版权

玩转Python 专栏收录该内容

164 篇文章

订阅专栏

在这里插入图片描述

随着Python语言的日益普及，越来越多的人开始利用它来提高工作效率，尤其是在处理Excel文件时。Python提供了多个库来读取、写入和修改Excel文件，使得自动化办公成为可能。本文将带你深入了解Python操作Excel的技术，从基础知识到高级应用，让你能够熟练运用Python处理Excel文件。

Python Excel库概览

在Python中，有多个库可以用来处理Excel文件，每个库都有其特点和适用场景。以下是几个常用的库及其特点：

pandas: 是一个强大的数据分析库，可以轻松处理Excel数据，适用于数据处理和分析任务。
openpyxl: 主要针对.xlsx格式的Excel文件，提供读写功能，适合进行复杂的数据操作和样式设置。
xlrd/xlwt: 分别用于读取和写入.xls格式的Excel文件，适合处理旧版Excel文件。
xlwings: 可以读写Excel文件，并且能够进行单元格格式的修改，特别适合需要与Excel应用程序交互的场景。
XlsxWriter: 专注于写入.xlsx文件，支持文本、数字、公式等的写入，并且可以调整单元格格式、插入图片和图表等。

安装Python Excel库

在开始使用Python操作Excel之前，需要安装相应的库。安装命令如下：

pip install pandas openpyxl xlrd xlwt xlwings XlsxWriter

Python Excel基础操作

读取Excel文件

使用pandas读取Excel文件

import pandas as pd

# 读取Excel文件
df = pd.read_excel('example.xlsx')

# 显示数据
print(df)

这段代码使用pandas库读取了一个名为example.xlsx的Excel文件，并将数据存储在一个DataFrame对象中。

使用openpyxl读取Excel文件

from openpyxl import load_workbook

# 加载Excel文件
workbook = load_workbook('example.xlsx')

# 选择工作表
sheet = workbook.active

# 读取数据
for row in sheet.iter_rows(values_only=True):
    print(row)

这段代码使用openpyxl库加载了一个Excel文件，并打印了第一个工作表的所有行数据。

使用xlrd读取Excel文件

import xlrd

# 打开Excel文件
workbook = xlrd.open_workbook('example.xls')

# 选择工作表
sheet = workbook.sheet_by_index(0)

# 读取数据
for row_idx in range(sheet.nrows):
    row = sheet.row_values(row_idx)
    print(row)

这段代码使用xlrd库读取了一个.xls格式的Excel文件，并打印了第一个工作表的所有行数据。

写入Excel文件

使用pandas写入Excel文件

import pandas as pd

# 创建数据
data = {
    'Name': ['Tom', 'Jerry'],
    'Age': [20, 21]
}
df = pd.DataFrame(data)

# 写入Excel文件
df.to_excel('output.xlsx', index=False)

这段代码创建了一个包含姓名和年龄的数据框，并将其写入一个名为output.xlsx的Excel文件中。

使用openpyxl写入Excel文件

from openpyxl import Workbook

# 创建新的工作簿
workbook = Workbook()
sheet = workbook.active

# 写入数据
sheet.append(['Name', 'Age'])
sheet.append(['Tom', 20])
sheet.append(['Jerry', 21])

# 保存文件
workbook.save('output_openpyxl.xlsx')

这段代码使用openpyxl库创建了一个新的Excel文件，并向其中写入了一些数据。

使用XlsxWriter写入Excel文件

import xlsxwriter

# 创建一个新的Excel文件
workbook = xlsxwriter.Workbook('output_xlsxwriter.xlsx')
worksheet = workbook.add_worksheet()

# 写入数据
data = [['Name', 'Age'], ['Tom', 20], ['Jerry', 21]]
for row_num, row_data in enumerate(data):
    worksheet.write_row(row_num, 0, row_data)

# 保存文件
workbook.close()

这段代码使用XlsxWriter库创建了一个新的Excel文件，并向其中写入了一些数据。

修改Excel文件

使用openpyxl修改Excel文件

from openpyxl import load_workbook

# 加载Excel文件
workbook = load_workbook('example.xlsx')

# 获取工作表
sheet = workbook.active

# 修改单元格数据
sheet['A1'] = 'New Name'
sheet['B1'] = 25

# 保存修改后的Excel文件
workbook.save('modified_example.xlsx')

这段代码加载了一个Excel文件，修改了其中的数据，并保存了修改后的文件。

Python Excel高级操作

数据格式化与样式设置

使用openpyxl设置单元格样式

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

# 创建工作簿和工作表
workbook = Workbook()
sheet = workbook.active

# 设置单元格字体和边框
cell = sheet['A1']
cell.font = Font(bold=True)
cell.border = Border(
    left=Side(border_style='thin', color='000000'),
    right=Side(border_style='thin', color='000000'),
    top=Side(border_style='thin', color='000000'),
    bottom=Side(border_style='thin', color='000000')
)

# 设置单元格对齐方式
cell.alignment = Alignment(horizontal='center', vertical='center')

# 保存工作簿
workbook.save('styled_example.xlsx')

这段代码使用openpyxl库设置了单元格的字体、边框和对齐方式。

使用XlsxWriter设置单元格样式

import xlsxwriter

# 创建一个新的Excel文件
workbook = xlsxwriter.Workbook('styled_xlsxwriter.xlsx')
worksheet = workbook.add_worksheet()

# 添加样式
bold = workbook.add_format({'bold': True})
border = workbook.add_format({'border': 1})
align_center = workbook.add_format({'align': 'center', 'valign': 'vcenter'})

# 写入数据并应用样式
worksheet.write('A1', 'Name', bold)
worksheet.write('B1', 'Age', bold)
worksheet.write('A2', 'Tom', border)
worksheet.write('B2', 20, border)
worksheet.write('A3', 'Jerry', border)
worksheet.write('B3', 21, border)

# 设置对齐方式
worksheet.set_column('A:A', None, align_center)
worksheet.set_column('B:B', None, align_center)

# 保存文件
workbook.close()

这段代码使用XlsxWriter库设置了单元格的字体、边框和对齐方式。

条件过滤与数据清洗

使用pandas进行条件过滤

import pandas as pd

# 读取Excel文件
df = pd.read_excel('example.xlsx')

# 基于条件过滤数据
filtered_df = df[df['Age'] > 25]

# 清洗数据：删除重复值和缺失值
cleaned_df = filtered_df.drop_duplicates().dropna()

# 保存清洗后的数据到新的Excel文件
cleaned_df.to_excel('cleaned_example.xlsx', index=False)

这段代码使用pandas库对数据进行了条件过滤，并删除了重复值和缺失值。

数据合并与重塑

使用pandas合并数据

import pandas as pd

# 读取两个Excel文件
df1 = pd.read_excel('data1.xlsx')
df2 = pd.read_excel('data2.xlsx')

# 合并数据
merged_df = pd.merge(df1, df2, on='KeyColumn')

# 保存合并后的数据
merged_df.to_excel('merged_example.xlsx', index=False)

这段代码使用pandas库合并了两个Excel文件中的数据。

数据透视表

使用pandas创建数据透视表

import pandas as pd

# 读取Excel文件
df = pd.read_excel('sales_data.xlsx')

# 创建数据透视表
pivot_table = pd.pivot_table(df, values='Sales', index=['Region'], columns=['Product'], aggfunc=sum)

# 保存数据透视表到新的Excel文件
pivot_table.to_excel('pivot_table_example.xlsx')

这段代码使用pandas库创建了一个数据透视表，并将其保存到一个新的Excel文件中。

图表创建

使用pandas和matplotlib创建图表

import pandas as pd
import matplotlib.pyplot as plt

# 读取Excel文件
df = pd.read_excel('sales_data.xlsx')

# 创建图表
df.plot(kind='bar', x='Region', y='Sales', title='Sales by Region')
plt.xlabel('Region')
plt.ylabel('Sales')
plt.show()

这段代码使用pandas库读取Excel文件，并使用matplotlib库创建了一个条形图。

Excel中的Python

Microsoft Excel现在支持在单元格中直接使用Python代码，这对于需要在Excel中进行复杂计算和数据处理的用户来说是一个巨大的福音。

在Excel中使用Python

启用Python支持：确保你使用的是Microsoft 365预览体验计划，并且运行的是当前频道（预览版）。
插入Python代码：选择一个单元格，然后在“公式”选项卡上，选择“插入Python”。
编写Python代码：在弹出的对话框中编写Python代码，然后点击“运行”。

Python Excel批量处理

在实际工作中，我们经常需要处理多个Excel文件。Python提供了强大的工具来实现这一需求。

读取多个Excel文件

import pandas as pd
import os

# 设置工作目录到包含Excel文件的文件夹
os.chdir('path_to_directory')

# 获取所有Excel文件的列表
excel_files = [file for file in os.listdir() if file.endswith('.xlsx')]

# 读取并处理每个Excel文件
for file in excel_files:
    df = pd.read_excel(file)
    # 这里可以对df进行各种数据处理操作
    # ...

这段代码读取了指定目录下的所有Excel文件，并对每个文件进行了处理。

写入多个Excel文件

import pandas as pd

# 假设我们有一个DataFrame列表，每个DataFrame对应一个Excel文件
dfs = [
    pd.DataFrame({'Name': ['Tom'], 'Age': [20]}),
    pd.DataFrame({'Name': ['Jerry'], 'Age': [21]})
]

# 写入每个DataFrame到单独的Excel文件
for i, df in enumerate(dfs):
    df.to_excel(f'output_{i}.xlsx', index=False)

这段代码将多个DataFrame分别写入到不同的Excel文件中。

Excel数据的高级处理

使用pandas进行数据转换

import pandas as pd

# 读取Excel文件
df = pd.read_excel('example.xlsx')

# 数据转换：将年龄转换为分类
age_categories = pd.cut(df['Age'], bins=[0, 20, 30, 40, 50, 60, 100], labels=['0-20', '20-30', '30-40', '40-50', '50-60', '60+'])
df['Age Category'] = age_categories

# 保存转换后的数据到新的Excel文件
df.to_excel('transformed_example.xlsx', index=False)

这段代码使用pandas库将年龄转换为分类，并将结果保存到一个新的Excel文件中。

使用pandas进行数据聚合

import pandas as pd

# 读取Excel文件
df = pd.read_excel('sales_data.xlsx')

# 数据聚合：按地区计算销售额总和
grouped_df = df.groupby('Region')['Sales'].sum().reset_index()

# 保存聚合后的数据到新的Excel文件
grouped_df.to_excel('aggregated_example.xlsx', index=False)

这段代码使用pandas库按地区计算销售额总和，并将结果保存到一个新的Excel文件中。

Excel数据的自动化任务

使用Python自动化生成报告

import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment
from openpyxl.chart import BarChart, Reference

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

# 数据处理
grouped_df = df.groupby('Region')['Sales'].sum().reset_index()

# 创建新的工作簿和工作表
workbook = Workbook()
sheet = workbook.active

# 写入数据
sheet.append(['Region', 'Sales'])
for _, row in grouped_df.iterrows():
    sheet.append([row['Region'], row['Sales']])

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

# 设置对齐方式
for cell in sheet['A:B']:
    cell.alignment = Alignment(horizontal='center', vertical='center')

# 创建图表
data = Reference(sheet, min_col=2, min_row=2, max_col=2, max_row=len(grouped_df) + 1)
categories = Reference(sheet, min_col=1, min_row=2, max_row=len(grouped_df) + 1)
chart = BarChart()
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
chart.title = 'Sales by Region'
sheet.add_chart(chart, 'D2')

# 保存工作簿
workbook.save('report.xlsx')