Python操作Excel文件的11種方法(全網(wǎng)最全)

更新時(shí)間：2025年03月06日 11:13:01 作者：極客代碼

在日常工作或開發(fā)過程中,Excel文件作為一種常用的數(shù)據(jù)存儲(chǔ)格式,其高效便捷的數(shù)據(jù)處理能力被廣泛應(yīng)用于數(shù)據(jù)統(tǒng)計(jì)、數(shù)據(jù)分析等領(lǐng)域,Python作為一種強(qiáng)大的編程語言,提供了豐富的庫支持來實(shí)現(xiàn)對Excel文件的操作,本篇將詳細(xì)介紹如何使用Python來操作Excel文件

引言

Python 提供了多種庫和方法來操作 Excel 文件，每種方法都有其獨(dú)特的優(yōu)勢和適用場景。本文將詳細(xì)介紹這 11 種方法，包括它們的優(yōu)缺點(diǎn)、適用場景以及詳細(xì)的使用方式和代碼示例，幫助讀者全面掌握這些工具。本文不僅涵蓋了基本的讀寫操作，還將深入探討高級(jí)功能和最佳實(shí)踐。

1. 使用 pandas 庫

優(yōu)點(diǎn)：

功能強(qiáng)大，支持?jǐn)?shù)據(jù)清洗、轉(zhuǎn)換和分析。
支持多種文件格式，包括 .xls 和 .xlsx。
提供豐富的數(shù)據(jù)操作方法，如篩選、排序、分組等。

缺點(diǎn)：

學(xué)習(xí)曲線較高，需要一定的編程基礎(chǔ)。
處理大規(guī)模數(shù)據(jù)時(shí)可能占用較多內(nèi)存。

適用場景：

數(shù)據(jù)分析和處理任務(wù)。
需要進(jìn)行復(fù)雜數(shù)據(jù)操作和分析的場景。

詳細(xì)使用方式：

安裝 pandas：

pip install pandas

讀取 Excel 文件：

import pandas as pd

# 讀取 Excel 文件
df = pd.read_excel('example.xlsx')
print(df.head())  # 顯示前五行數(shù)據(jù)

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

寫入 Excel 文件：

import pandas as pd

# 創(chuàng)建數(shù)據(jù)
data = {
    'Name': ['Tom', 'Jerry'],
    'Age': [20, 21]
}
df = pd.DataFrame(data)

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

數(shù)據(jù)操作：

選擇特定列：

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

過濾數(shù)據(jù)：

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

數(shù)據(jù)分組：

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)

合并多個(gè) 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)

數(shù)據(jù)透視表：

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

2. 使用 openpyxl 庫

優(yōu)點(diǎn)：

專注于 .xlsx 文件格式。
提供細(xì)粒度的操作，如單元格格式、圖表等。
支持讀取、寫入和修改 Excel 文件。

缺點(diǎn)：

不支持 .xls 文件格式。
功能相對單一，不適用于復(fù)雜的數(shù)據(jù)分析任務(wù)。

適用場景：

需要對 Excel 文件進(jìn)行細(xì)粒度操作的場景。
處理 .xlsx 文件格式的任務(wù)。

詳細(xì)使用方式：

安裝 openpyxl：

pip install openpyxl

讀取 Excel 文件：

from openpyxl import load_workbook

# 加載 Excel 文件
wb = load_workbook('example.xlsx')
sheet = wb.active

# 讀取數(shù)據(jù)
for row in sheet.iter_rows(values_only=True):
    print(row)

寫入 Excel 文件：

from openpyxl import Workbook

# 創(chuàng)建新的工作簿
wb = Workbook()
sheet = wb.active

# 寫入數(shù)據(jù)
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

# 修改單元格數(shù)據(jù)
sheet['A1'] = 'New Name'
sheet['B1'] = 25

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

設(shè)置單元格格式：

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

# 創(chuàng)建新的工作簿
wb = Workbook()
sheet = wb.active

# 設(shè)置單元格格式
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 庫

優(yōu)點(diǎn)：

支持 .xls 文件格式。
xlrd 用于讀取 Excel 文件，xlwt 用于寫入 Excel 文件。
輕量級(jí)，適合簡單的數(shù)據(jù)操作任務(wù)。

缺點(diǎn)：

不支持 .xlsx 文件格式。
功能相對有限，不適合復(fù)雜的操作。

適用場景：

處理 .xls 文件格式的任務(wù)。
需要簡單數(shù)據(jù)操作的場景。

詳細(xì)使用方式：

安裝 xlrd 和 xlwt：

pip install xlrd xlwt

讀取 Excel 文件：

import xlrd

# 打開 Excel 文件
workbook = xlrd.open_workbook('example.xls')
sheet = workbook.sheet_by_index(0)

# 讀取數(shù)據(jù)
for row_idx in range(sheet.nrows):
    row = sheet.row_values(row_idx)
    print(row)

寫入 Excel 文件：

import xlwt

# 創(chuàng)建新的工作簿
workbook = xlwt.Workbook()
sheet = workbook.add_sheet('Sheet1')

# 寫入數(shù)據(jù)
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 庫

優(yōu)點(diǎn)：

支持 .xls 和 .xlsx 文件格式。
可以讀寫 Excel 文件，并進(jìn)行單元格格式的修改。
提供與 Excel 應(yīng)用程序交互的功能。

缺點(diǎn)：

需要安裝 Excel 應(yīng)用程序。
功能相對復(fù)雜，學(xué)習(xí)曲線較高。

適用場景：

需要與 Excel 應(yīng)用程序交互的場景。
處理復(fù)雜數(shù)據(jù)操作和格式設(shè)置的任務(wù)。

詳細(xì)使用方式：

安裝 xlwings：

pip install xlwings

讀取 Excel 文件：

import xlwings as xw

# 創(chuàng)建 Excel 應(yīng)用程序?qū)ο?
app = xw.App(visible=True, add_book=False)

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

# 讀取數(shù)據(jù)
data = sheet.range('A1:B7').value
print(data)

# 關(guān)閉工作簿和應(yīng)用程序
wb.close()
app.quit()

寫入 Excel 文件：

import xlwings as xw

# 創(chuàng)建 Excel 應(yīng)用程序?qū)ο?
app = xw.App(visible=True, add_book=False)

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

# 寫入數(shù)據(jù)
sheet.range('A1').value = [['Name', 'Age'], ['Tom', 20], ['Jerry', 21]]

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

# 關(guān)閉工作簿和應(yīng)用程序
wb.close()
app.quit()

設(shè)置單元格格式：

import xlwings as xw

# 創(chuàng)建 Excel 應(yīng)用程序?qū)ο?
app = xw.App(visible=True, add_book=False)

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

# 寫入數(shù)據(jù)
sheet.range('A1').value = [['Name', 'Age'], ['Tom', 20], ['Jerry', 21]]

# 設(shè)置單元格格式
cell = sheet.range('A1')
cell.api.Font.Bold = True
cell.api.HorizontalAlignment = -4108  # 水平居中
cell.api.VerticalAlignment = -4108  # 垂直居中

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

# 關(guān)閉工作簿和應(yīng)用程序
wb.close()
app.quit()

5. 使用 XlsxWriter 庫

優(yōu)點(diǎn)：

專注于寫入 .xlsx 文件。
支持文本、數(shù)字、公式等的寫入。
提供豐富的單元格格式設(shè)置功能。

缺點(diǎn)：

不支持讀取 Excel 文件。
功能相對單一，不適用于復(fù)雜的數(shù)據(jù)操作任務(wù)。

適用場景：

需要寫入 .xlsx 文件的任務(wù)。
需要精細(xì)格式設(shè)置的場景。

詳細(xì)使用方式：

安裝 XlsxWriter：

pip install XlsxWriter

寫入 Excel 文件：

import xlsxwriter

# 創(chuàng)建新的 Excel 文件
workbook = xlsxwriter.Workbook('output.xlsx')
worksheet = workbook.add_worksheet()

# 寫入數(shù)據(jù)
data = [['Name', 'Age'], ['Tom', 20], ['Jerry', 21]]
for row_num, row_data in enumerate(data):
    worksheet.write_row(row_num, 0, row_data)

# 設(shè)置單元格格式
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 庫

優(yōu)點(diǎn)：

支持多種 Excel 文件格式，包括 .xls 和 .xlsx。
提供一致的接口來讀取和寫入這些文件。
輕量級(jí)，易于使用。

缺點(diǎn)：

功能相對有限，不適用于復(fù)雜的操作。
不如 pandas 和 openpyxl 功能豐富。

適用場景：

處理多種 Excel 文件格式的任務(wù)。
需要簡單數(shù)據(jù)操作的場景。

詳細(xì)使用方式：

安裝 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

# 創(chuàng)建數(shù)據(jù)
data = [['Name', 'Age'], ['Tom', 20], ['Jerry', 21]]

# 寫入 Excel 文件
sheet = pyexcel.Sheet(data)
sheet.save_as("output.xlsx")

7. 使用 et_xmlfile 庫

優(yōu)點(diǎn)：

用于處理 Excel 文件的 XML 內(nèi)容。
適用于高級(jí)用戶，可以直接操作 Excel 文件的內(nèi)部結(jié)構(gòu)。

缺點(diǎn)：

學(xué)習(xí)曲線較高，需要了解 XML 結(jié)構(gòu)。
功能相對復(fù)雜，不適合初學(xué)者。

適用場景：

需要直接操作 Excel 文件內(nèi)部結(jié)構(gòu)的場景。
處理復(fù)雜 Excel 文件的任務(wù)。

詳細(xì)使用方式：

安裝 et_xmlfile：

pip install et_xmlfile

讀取 Excel 文件的 XML 內(nèi)容：

from et_xmlfile import xmlfile

# 讀取 Excel 文件的 XML 內(nèi)容
with xmlfile.XmlFile('example.xlsx') as xf:
    for event, elem in xf.iterparse():
        print(event, elem.tag)

8. 使用 win32com.client 庫

優(yōu)點(diǎn)：

通過 COM 接口操作 Excel 文件。
支持多種 Excel 文件格式。
提供與 Excel 應(yīng)用程序交互的功能。

缺點(diǎn)：

需要安裝 Excel 應(yīng)用程序。
功能相對復(fù)雜，學(xué)習(xí)曲線較高。
僅適用于 Windows 環(huán)境。

適用場景：

需要與 Excel 應(yīng)用程序交互的場景。
處理復(fù)雜數(shù)據(jù)操作和格式設(shè)置的任務(wù)。

詳細(xì)使用方式：

安裝 pywin32：

pip install pywin32

讀取 Excel 文件：

import win32com.client

# 創(chuàng)建 Excel 應(yīng)用程序?qū)ο?
excel = win32com.client.Dispatch("Excel.Application")
excel.Visible = True

# 打開工作簿
workbook = excel.Workbooks.Open(r'C:\path\to\example.xlsx')
sheet = workbook.Sheets(1)

# 讀取數(shù)據(jù)
cell_value = sheet.Cells(1, 1).Value
print(cell_value)

# 關(guān)閉工作簿和應(yīng)用程序
workbook.Close()
excel.Quit()

寫入 Excel 文件：

import win32com.client

# 創(chuàng)建 Excel 應(yīng)用程序?qū)ο?
excel = win32com.client.Dispatch("Excel.Application")
excel.Visible = True

# 創(chuàng)建新的工作簿
workbook = excel.Workbooks.Add()
sheet = workbook.Sheets(1)

# 寫入數(shù)據(jù)
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')

# 關(guān)閉工作簿和應(yīng)用程序
workbook.Close()
excel.Quit()

9. 使用 tablib 庫

優(yōu)點(diǎn)：

支持多種數(shù)據(jù)格式，包括 Excel。
提供一致的接口來處理不同格式的數(shù)據(jù)。
輕量級(jí)，易于使用。

缺點(diǎn)：

功能相對有限，不適用于復(fù)雜的操作。
不如 pandas 和 openpyxl 功能豐富。

適用場景：

處理多種數(shù)據(jù)格式的任務(wù)。
需要簡單數(shù)據(jù)操作的場景。

詳細(xì)使用方式：

安裝 tablib：

pip install tablib

寫入 Excel 文件：

import tablib

# 創(chuàng)建數(shù)據(jù)集
data = tablib.Dataset()
data.headers = ['Name', 'Age']
data.append(['Tom', 20])
data.append(['Jerry', 21])

# 導(dǎo)出為 Excel 文件
with open('output.xlsx', 'wb') as f:
    f.write(data.export('xlsx'))

10. 使用 odfpy 庫

優(yōu)點(diǎn)：

用于處理 OpenDocument 格式文件，包括 .ods 文件。
提供讀取和寫入 .ods 文件的功能。
輕量級(jí)，易于使用。

缺點(diǎn)：

不支持 .xls 和 .xlsx 文件格式。
功能相對有限，不適用于復(fù)雜的操作。

適用場景：

處理 .ods 文件格式的任務(wù)。
需要簡單數(shù)據(jù)操作的場景。

詳細(xì)使用方式：

安裝 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]

# 遍歷表格中的數(shù)據(jù)
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

# 創(chuàng)建新的 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 庫

優(yōu)點(diǎn)：

支持 .ods 文件格式。
提供一致的接口來讀取和寫入 .ods 文件。
輕量級(jí)，易于使用。

缺點(diǎn)：

不支持 .xls 和 .xlsx 文件格式。
功能相對有限，不適用于復(fù)雜的操作。

適用場景：

處理 .ods 文件格式的任務(wù)。
需要簡單數(shù)據(jù)操作的場景。

詳細(xì)使用方式：

安裝 pyexcel-ods3：

pip install pyexcel-ods3

讀取 ODS 文件：

import pyexcel_ods3

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

寫入 ODS 文件：

import pyexcel_ods3

# 創(chuàng)建數(shù)據(jù)
data = {
    'Sheet1': [
        ['Name', 'Age'],
        ['Tom', 20],
        ['Jerry', 21]
    ]
}

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

總結(jié)

Python 提供了多種庫和方法來操作 Excel 文件，每種方法都有其獨(dú)特的優(yōu)缺點(diǎn)和適用場景。選擇合適的庫可以提高開發(fā)效率和代碼質(zhì)量。以下是每種方法的簡要總結(jié)：

pandas：功能強(qiáng)大，支持?jǐn)?shù)據(jù)清洗、轉(zhuǎn)換和分析，適用于數(shù)據(jù)分析和處理任務(wù)。
openpyxl：專注于 .xlsx 文件格式，提供細(xì)粒度的操作，適用于需要對 Excel 文件進(jìn)行細(xì)粒度操作的場景。
xlrd 和 xlwt：支持 .xls 文件格式，輕量級(jí)，適用于處理 .xls 文件格式的任務(wù)。
xlwings：支持 .xls 和 .xlsx 文件格式，提供與 Excel 應(yīng)用程序交互的功能，適用于需要與 Excel 應(yīng)用程序交互的場景。
XlsxWriter：專注于寫入 .xlsx 文件，提供豐富的單元格格式設(shè)置功能，適用于需要寫入 .xlsx 文件的任務(wù)。
pyexcel：支持多種 Excel 文件格式，提供一致的接口，適用于處理多種 Excel 文件格式的任務(wù)。
et_xmlfile：用于處理 Excel 文件的 XML 內(nèi)容，適用于需要直接操作 Excel 文件內(nèi)部結(jié)構(gòu)的場景。
win32com.client：通過 COM 接口操作 Excel 文件，適用于需要與 Excel 應(yīng)用程序交互的場景。
tablib：支持多種數(shù)據(jù)格式，提供一致的接口，適用于處理多種數(shù)據(jù)格式的任務(wù)。
odfpy：用于處理 OpenDocument 格式文件，包括 .ods 文件，適用于處理 .ods 文件格式的任務(wù)。
pyexcel-ods3：支持 .ods 文件格式，提供一致的接口，適用于處理 .ods 文件格式的任務(wù)。