Python操作Excel文件的11种方法(全网最全)_Python

引言

python 提供了多种库和方法来操作 excel 文件，每种方法都有其独特的优势和适用场景。本文将详细介绍这 11 种方法，包括它们的优缺点、适用场景以及详细的使用方式和代码示例，帮助读者全面掌握这些工具。本文不仅涵盖了基本的读写操作，还将深入探讨高级功能和最佳实践。

1. 使用 pandas 库

优点：

功能强大，支持数据清洗、转换和分析。
支持多种文件格式，包括 .xls 和 .xlsx。
提供丰富的数据操作方法，如筛选、排序、分组等。

缺点：

学习曲线较高，需要一定的编程基础。
处理大规模数据时可能占用较多内存。

适用场景：

数据分析和处理任务。
需要进行复杂数据操作和分析的场景。

详细使用方式：

安装 pandas：

pip install pandas

读取 excel 文件：

import pandas as pd

# 读取 excel 文件
df = pd.read_excel('example.xlsx')
print(df.head())  # 显示前五行数据

# 读取特定工作表
df = pd.read_excel('example.xlsx', sheet_name='sheet1')
print(df.head())

写入 excel 文件：

import pandas as pd

# 创建数据
data = {
    'name': ['tom', 'jerry'],
    'age': [20, 21]
}
df = pd.dataframe(data)

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

数据操作：

选择特定列：

df = pd.read_excel('example.xlsx', usecols=['name', 'age'])
print(df.head())

过滤数据：

df = pd.read_excel('example.xlsx')
filtered_df = df[df['age'] > 20]
print(filtered_df)

数据分组：

df = pd.read_excel('example.xlsx')
grouped_df = df.groupby('age').mean()
print(grouped_df)

添加新列：

df = pd.read_excel('example.xlsx')
df['newcolumn'] = df['age'] * 2
print(df)

更新单元格：

df = pd.read_excel('example.xlsx')
df.at[0, 'age'] = 25
print(df)

删除列：

df = pd.read_excel('example.xlsx')
del df['age']
print(df)

合并多个 excel 文件：

df1 = pd.read_excel('file1.xlsx')
df2 = pd.read_excel('file2.xlsx')
merged_df = pd.concat([df1, df2], ignore_index=true)
print(merged_df)

数据透视表：

df = pd.read_excel('example.xlsx')
pivot_table = pd.pivot_table(df, values='age', index=['name'], aggfunc='sum')
print(pivot_table)

2. 使用 openpyxl 库

优点：

专注于 .xlsx 文件格式。
提供细粒度的操作，如单元格格式、图表等。
支持读取、写入和修改 excel 文件。

缺点：

不支持 .xls 文件格式。
功能相对单一，不适用于复杂的数据分析任务。

适用场景：

需要对 excel 文件进行细粒度操作的场景。
处理 .xlsx 文件格式的任务。

详细使用方式：

安装 openpyxl：

pip install openpyxl

读取 excel 文件：

from openpyxl import load_workbook

# 加载 excel 文件
wb = load_workbook('example.xlsx')
sheet = wb.active

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

写入 excel 文件：

from openpyxl import workbook

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

# 写入数据
sheet.append(['name', 'age'])
sheet.append(['tom', 20])
sheet.append(['jerry', 21])

# 保存文件
wb.save('output.xlsx')

修改 excel 文件：

from openpyxl import load_workbook

# 加载 excel 文件
wb = load_workbook('example.xlsx')
sheet = wb.active

# 修改单元格数据
sheet['a1'] = 'new name'
sheet['b1'] = 25

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

设置单元格格式：

from openpyxl import workbook
from openpyxl.styles import font, alignment

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

# 设置单元格格式
cell = sheet['a1']
cell.value = 'hello, world!'
cell.font = font(bold=true, color='ff0000')
cell.alignment = alignment(horizontal='center', vertical='center')

# 保存文件
wb.save('formatted_output.xlsx')

3. 使用 xlrd 和 xlwt 库

优点：

支持 .xls 文件格式。
xlrd 用于读取 excel 文件，xlwt 用于写入 excel 文件。
轻量级，适合简单的数据操作任务。

缺点：

不支持 .xlsx 文件格式。
功能相对有限，不适合复杂的操作。

适用场景：

处理 .xls 文件格式的任务。
需要简单数据操作的场景。

详细使用方式：

安装 xlrd 和 xlwt：

pip install xlrd xlwt

读取 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)

写入 excel 文件：

import xlwt

# 创建新的工作簿
workbook = xlwt.workbook()
sheet = workbook.add_sheet('sheet1')

# 写入数据
sheet.write(0, 0, 'name')
sheet.write(0, 1, 'age')
sheet.write(1, 0, 'tom')
sheet.write(1, 1, 20)
sheet.write(2, 0, 'jerry')
sheet.write(2, 1, 21)

# 保存文件
workbook.save('output.xls')

4. 使用 xlwings 库

优点：

支持 .xls 和 .xlsx 文件格式。
可以读写 excel 文件，并进行单元格格式的修改。
提供与 excel 应用程序交互的功能。

缺点：

需要安装 excel 应用程序。
功能相对复杂，学习曲线较高。

适用场景：

需要与 excel 应用程序交互的场景。
处理复杂数据操作和格式设置的任务。

详细使用方式：

安装 xlwings：

pip install xlwings

读取 excel 文件：

import xlwings as xw

# 创建 excel 应用程序对象
app = xw.app(visible=true, add_book=false)

# 打开工作簿
wb = app.books.open('example.xlsx')
sheet = wb.sheets[0]

# 读取数据
data = sheet.range('a1:b7').value
print(data)

# 关闭工作簿和应用程序
wb.close()
app.quit()

写入 excel 文件：

import xlwings as xw

# 创建 excel 应用程序对象
app = xw.app(visible=true, add_book=false)

# 创建新的工作簿
wb = app.books.add()
sheet = wb.sheets[0]

# 写入数据
sheet.range('a1').value = [['name', 'age'], ['tom', 20], ['jerry', 21]]

# 保存文件
wb.save('output.xlsx')

# 关闭工作簿和应用程序
wb.close()
app.quit()

设置单元格格式：

import xlwings as xw

# 创建 excel 应用程序对象
app = xw.app(visible=true, add_book=false)

# 创建新的工作簿
wb = app.books.add()
sheet = wb.sheets[0]

# 写入数据
sheet.range('a1').value = [['name', 'age'], ['tom', 20], ['jerry', 21]]

# 设置单元格格式
cell = sheet.range('a1')
cell.api.font.bold = true
cell.api.horizontalalignment = -4108  # 水平居中
cell.api.verticalalignment = -4108  # 垂直居中

# 保存文件
wb.save('formatted_output.xlsx')

# 关闭工作簿和应用程序
wb.close()
app.quit()

5. 使用 xlsxwriter 库

优点：

专注于写入 .xlsx 文件。
支持文本、数字、公式等的写入。
提供丰富的单元格格式设置功能。

缺点：

不支持读取 excel 文件。
功能相对单一，不适用于复杂的数据操作任务。

适用场景：

需要写入 .xlsx 文件的任务。
需要精细格式设置的场景。

详细使用方式：

安装 xlsxwriter：

pip install xlsxwriter

写入 excel 文件：

import xlsxwriter

# 创建新的 excel 文件
workbook = xlsxwriter.workbook('output.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)

# 设置单元格格式
bold = workbook.add_format({'bold': true})
worksheet.write('a1', 'name', bold)
worksheet.write('b1', 'age', bold)

# 插入图表
chart = workbook.add_chart({'type': 'column'})
chart.add_series({
    'categories': '=sheet1!$a$2:$a$3',
    'values': '=sheet1!$b$2:$b$3',
})
worksheet.insert_chart('d2', chart)

# 保存文件
workbook.close()

6. 使用 pyexcel 库

优点：

支持多种 excel 文件格式，包括 .xls 和 .xlsx。
提供一致的接口来读取和写入这些文件。
轻量级，易于使用。

缺点：

功能相对有限，不适用于复杂的操作。
不如 pandas 和 openpyxl 功能丰富。

适用场景：

处理多种 excel 文件格式的任务。
需要简单数据操作的场景。

详细使用方式：

安装 pyexcel：

pip install pyexcel pyexcel-xls pyexcel-xlsx

读取 excel 文件：

import pyexcel

# 读取 excel 文件
sheet = pyexcel.get_sheet(file_name="example.xlsx")
print(sheet)

# 读取特定工作表
sheet = pyexcel.get_sheet(file_name="example.xlsx", sheet_name="sheet1")
print(sheet)

写入 excel 文件：

import pyexcel

# 创建数据
data = [['name', 'age'], ['tom', 20], ['jerry', 21]]

# 写入 excel 文件
sheet = pyexcel.sheet(data)
sheet.save_as("output.xlsx")

7. 使用 et_xmlfile 库

优点：

用于处理 excel 文件的 xml 内容。
适用于高级用户，可以直接操作 excel 文件的内部结构。

缺点：

学习曲线较高，需要了解 xml 结构。
功能相对复杂，不适合初学者。

适用场景：

需要直接操作 excel 文件内部结构的场景。
处理复杂 excel 文件的任务。

详细使用方式：

安装 et_xmlfile：

pip install et_xmlfile

读取 excel 文件的 xml 内容：

from et_xmlfile import xmlfile

# 读取 excel 文件的 xml 内容
with xmlfile.xmlfile('example.xlsx') as xf:
    for event, elem in xf.iterparse():
        print(event, elem.tag)

8. 使用 win32com.client 库

优点：

通过 com 接口操作 excel 文件。
支持多种 excel 文件格式。
提供与 excel 应用程序交互的功能。

缺点：

需要安装 excel 应用程序。
功能相对复杂，学习曲线较高。
仅适用于 windows 环境。

适用场景：

需要与 excel 应用程序交互的场景。
处理复杂数据操作和格式设置的任务。

详细使用方式：

安装 pywin32：

pip install pywin32

读取 excel 文件：

import win32com.client

# 创建 excel 应用程序对象
excel = win32com.client.dispatch("excel.application")
excel.visible = true

# 打开工作簿
workbook = excel.workbooks.open(r'c:\path\to\example.xlsx')
sheet = workbook.sheets(1)

# 读取数据
cell_value = sheet.cells(1, 1).value
print(cell_value)

# 关闭工作簿和应用程序
workbook.close()
excel.quit()

写入 excel 文件：

import win32com.client

# 创建 excel 应用程序对象
excel = win32com.client.dispatch("excel.application")
excel.visible = true

# 创建新的工作簿
workbook = excel.workbooks.add()
sheet = workbook.sheets(1)

# 写入数据
sheet.cells(1, 1).value = 'name'
sheet.cells(1, 2).value = 'age'
sheet.cells(2, 1).value = 'tom'
sheet.cells(2, 2).value = 20
sheet.cells(3, 1).value = 'jerry'
sheet.cells(3, 2).value = 21

# 保存文件
workbook.saveas(r'c:\path\to\output.xlsx')

# 关闭工作簿和应用程序
workbook.close()
excel.quit()

9. 使用 tablib 库

优点：

支持多种数据格式，包括 excel。
提供一致的接口来处理不同格式的数据。
轻量级，易于使用。

缺点：

功能相对有限，不适用于复杂的操作。
不如 pandas 和 openpyxl 功能丰富。

适用场景：

处理多种数据格式的任务。
需要简单数据操作的场景。

详细使用方式：

安装 tablib：

pip install tablib

写入 excel 文件：

import tablib

# 创建数据集
data = tablib.dataset()
data.headers = ['name', 'age']
data.append(['tom', 20])
data.append(['jerry', 21])

# 导出为 excel 文件
with open('output.xlsx', 'wb') as f:
    f.write(data.export('xlsx'))

10. 使用 odfpy 库

优点：

用于处理 opendocument 格式文件，包括 .ods 文件。
提供读取和写入 .ods 文件的功能。
轻量级，易于使用。

缺点：

不支持 .xls 和 .xlsx 文件格式。
功能相对有限，不适用于复杂的操作。

适用场景：

处理 .ods 文件格式的任务。
需要简单数据操作的场景。

详细使用方式：

安装 odfpy：

pip install odfpy

读取 ods 文件：

from odf.opendocument import load
from odf.table import tablerow, tablecell
from odf.text import p

# 读取 ods 文件
doc = load('example.ods')
table = doc.spreadsheet.getelementsbytype(table)[0]

# 遍历表格中的数据
for row in table.getelementsbytype(tablerow):
    cells = row.getelementsbytype(tablecell)
    row_data = [cell.getelementsbytype(p)[0].text for cell in cells]
    print(row_data)

写入 ods 文件：

from odf.opendocument import opendocumentspreadsheet
from odf.table import table, tablerow, tablecell
from odf.text import p

# 创建新的 ods 文件
doc = opendocumentspreadsheet()
table = table(name="sheet1")
doc.spreadsheet.addelement(table)

# 添加新行
new_row = tablerow()
new_row.addelement(tablecell(text=p(text='name')))
new_row.addelement(tablecell(text=p(text='age')))
table.addelement(new_row)

# 添加更多行
new_row = tablerow()
new_row.addelement(tablecell(text=p(text='tom')))
new_row.addelement(tablecell(text=p(text='20')))
table.addelement(new_row)

new_row = tablerow()
new_row.addelement(tablecell(text=p(text='jerry')))
new_row.addelement(tablecell(text=p(text='21')))
table.addelement(new_row)

# 保存文件
doc.save('output.ods')

11. 使用 pyexcel-ods3 库

优点：

支持 .ods 文件格式。
提供一致的接口来读取和写入 .ods 文件。
轻量级，易于使用。

缺点：

不支持 .xls 和 .xlsx 文件格式。
功能相对有限，不适用于复杂的操作。

适用场景：

处理 .ods 文件格式的任务。
需要简单数据操作的场景。

详细使用方式：

安装 pyexcel-ods3：

pip install pyexcel-ods3

读取 ods 文件：

import pyexcel_ods3

# 读取 ods 文件
data = pyexcel_ods3.get_data('example.ods')
print(data)

写入 ods 文件：

import pyexcel_ods3

# 创建数据
data = {
    'sheet1': [
        ['name', 'age'],
        ['tom', 20],
        ['jerry', 21]
    ]
}

# 写入 ods 文件
pyexcel_ods3.save_data('output.ods', data)

总结

python 提供了多种库和方法来操作 excel 文件，每种方法都有其独特的优缺点和适用场景。选择合适的库可以提高开发效率和代码质量。以下是每种方法的简要总结：

pandas：功能强大，支持数据清洗、转换和分析，适用于数据分析和处理任务。
openpyxl：专注于 .xlsx 文件格式，提供细粒度的操作，适用于需要对 excel 文件进行细粒度操作的场景。
xlrd 和 xlwt：支持 .xls 文件格式，轻量级，适用于处理 .xls 文件格式的任务。
xlwings：支持 .xls 和 .xlsx 文件格式，提供与 excel 应用程序交互的功能，适用于需要与 excel 应用程序交互的场景。
xlsxwriter：专注于写入 .xlsx 文件，提供丰富的单元格格式设置功能，适用于需要写入 .xlsx 文件的任务。
pyexcel：支持多种 excel 文件格式，提供一致的接口，适用于处理多种 excel 文件格式的任务。
et_xmlfile：用于处理 excel 文件的 xml 内容，适用于需要直接操作 excel 文件内部结构的场景。
win32com.client：通过 com 接口操作 excel 文件，适用于需要与 excel 应用程序交互的场景。
tablib：支持多种数据格式，提供一致的接口，适用于处理多种数据格式的任务。
odfpy：用于处理 opendocument 格式文件，包括 .ods 文件，适用于处理 .ods 文件格式的任务。
pyexcel-ods3：支持 .ods 文件格式，提供一致的接口，适用于处理 .ods 文件格式的任务。