Python中處理Excel數(shù)據(jù)的方法對比(pandas和openpyxl)

更新時間：2025年07月07日 09:16:18 作者：大蟲小囈

openpyxl?和?pandas在處理Excel數(shù)據(jù)時各有優(yōu)勢,選擇需結(jié)合具體場景,本文將從核心功能,性能,適用場景展等幾個不同的維度做個對比,感興趣的可以了解下

前言

雖然我的編程母語是 javascript，但是經(jīng)常會使用python來處理各種格式的數(shù)據(jù)文件（如.txt、.json、.xlsx .csv、.sav等等）。當然處理得最多的還是Excel文件。為啥用python？好用啊！有各種開箱即用的工具庫，如 pandas 和 openpyxl 這倆個好哥們都是我的碼中常客。

pandas 和 openpyxl 哪家強？

openpyxl 和 pandas 各有優(yōu)勢，選擇需結(jié)合具體場景（如數(shù)據(jù)規(guī)模、操作類型、性能需求）。下面我們先從核心功能、性能、適用場景展等幾個不同的維度做個對比。

一、openpyxl 與 pandas 核心對比

維度	openpyxl	pandas
核心定位	Excel 文件底層操作（格式、樣式、公式、圖表）	數(shù)據(jù)分析與批量處理（清洗、聚合、透視表）
數(shù)據(jù)處理能力	基礎(chǔ)讀寫，無內(nèi)置分析函數(shù)	強大（篩選、聚合、時間序列分析等）
性能表現(xiàn)	小文件讀取更快；增量模式（read_only=True）節(jié)省內(nèi)存	大數(shù)據(jù)分析快（向量化計算）；全加載內(nèi)存，大文件易崩潰
格式支持	僅支持 .xlsx/.xlsm 等新格式	支持 Excel、CSV、JSON 等
樣式/圖表	精細控制（字體、顏色、圖表、條件格式）	需依賴 openpyxl 引擎輔助實現(xiàn)

性能測試數(shù)據(jù)（10,000行數(shù)據(jù)集）：

數(shù)據(jù)篩選：pandas 快 10 倍以上（0.02秒 vs 0.25秒）
數(shù)據(jù)聚合：pandas 快 50 倍（0.01秒 vs 0.5秒）
大文件讀取：openpyxl 增量模式內(nèi)存占用低 60%

二、其他 Excel 處理工具推薦

除 openpyxl 和 pandas 外，以下工具可以滿足其他不同場景的需求：

工具	核心優(yōu)勢	適用場景
xlwings	雙向交互 Excel VBA，實時更新數(shù)據(jù)	自動化報表（Python 計算 → Excel 動態(tài)展示）
xlsxwriter	專注寫入，支持高級格式（圖表、條件格式、加密）	生成復(fù)雜格式報告（如帶動態(tài)圖表的儀表盤）
xlrd/xlwt	兼容舊版 .xls 格式（xlrd 讀取，xlwt 寫入）	處理 Excel 2003 及更早版本文件
Tablib	輕量級多格式轉(zhuǎn)換（Excel/CSV/JSON 互轉(zhuǎn)）	快速導(dǎo)出數(shù)據(jù)，無需復(fù)雜分析

三、如何選擇工具

可根據(jù)下圖決策：

四、混合使用策略（推薦場景）

結(jié)合兩者優(yōu)勢可讓效率最大化：

# 示例：openpyxl讀取 → pandas處理 → openpyxl美化輸出
from openpyxl import load_workbook
import pandas as pd

# 1. openpyxl增量讀取大文件
wb = load_workbook("large_file.xlsx", read_only=True)
data = [row for row in wb.active.values][1:]  # 跳過標題
wb.close()

# 2. pandas處理數(shù)據(jù)
df = pd.DataFrame(data, columns=["Name", "Age"])
df_filtered = df[df["Age"] > 30].groupby("Name").mean()

# 3. openpyxl輸出帶樣式
with pd.ExcelWriter("output.xlsx", engine="openpyxl") as writer:
    df_filtered.to_excel(writer, sheet_name="Summary")
    workbook = writer.book
    worksheet = writer.sheets["Summary"]
    # 設(shè)置標題樣式
    for cell in worksheet[1]:
        cell.font = Font(bold=True, color="FFFFFF")
        cell.fill = PatternFill(start_color="366092", fill_type="solid")

方法補充

Python操作Excel的多種方式Pandas+openpyxl+xlrd

Pandas

pip install pandas

使用pandas操作Excel文件主要涉及讀?。?code>read_excel）和寫入（to_excel）兩個主要操作。

讀取Excel文件（read_excel）

pandas的read_excel函數(shù)用于讀取Excel文件（.xls或.xlsx），并將其內(nèi)容加載到DataFrame對象中。

語法參數(shù)

io: 文件路徑或文件對象。
sheet_name: 指定要讀取的工作表名稱或索引?？梢允亲址⒄麛?shù)、字符串列表或None。如果是None，則返回字典，其中包含所有工作表。
header: 指定作為列名的行，默認為0（第一行）。如果文件沒有列標題，可以設(shè)置為None。
names: 用于結(jié)果的列名的列表，如果文件不包含列標題行，應(yīng)該明確指定此參數(shù)。
index_col: 用作行索引的列編號或列名，可以是整數(shù)、字符串、整數(shù)列表、字符串列表或False（默認）。
usecols: 返回列的列號或列名列表。
dtype: 數(shù)據(jù)或字典，用于強制指定某些列的數(shù)據(jù)類型。
engine: 用于讀取Excel文件的引擎。None將嘗試使用io的擴展名來選擇引擎。如果安裝了xlrd，則.xls文件將使用它；否則，將使用openpyxl或odfpy（對于.ods文件）。

案例：

import pandas as pd  
  
# 讀取Excel文件  
df = pd.read_excel('example.xlsx', sheet_name='Sheet1', header=0, index_col=None, usecols=None, dtype=None)  
  
# 顯示前幾行數(shù)據(jù)  
print(df.head())  
  
# 如果文件沒有列標題  
df_no_header = pd.read_excel('example_no_header.xlsx', header=None, names=['Column1', 'Column2', 'Column3'])  
print(df_no_header.head())  
  
# 讀取多個工作表  
xls = pd.ExcelFile('example.xlsx')  
df1 = pd.read_excel(xls, 'Sheet1')  
df2 = pd.read_excel(xls, 'Sheet2')  
# 或者  
dfs = pd.read_excel(xls, sheet_name=None)  # 返回一個字典，鍵為工作表名，值為DataFrame

進階案例：讀取特定單元格范圍

雖然read_excel沒有直接讀取特定單元格范圍的參數(shù)，但你可以通過usecols和行切片來實現(xiàn)類似的效果。

# 假設(shè)我們只想讀取'A'列和'C'列的前兩行  
df = pd.read_excel('example.xlsx', usecols=['A', 'C']).head(2)

寫入Excel文件（to_excel）

DataFrame的to_excel方法用于將DataFrame寫入Excel文件。

語法參數(shù)

excel_writer: 文件路徑或ExcelWriter對象。
sheet_name: 字符串，默認為'Sheet1'。
columns: 要寫入的列名列表。
header: 是否寫入列名作為Excel文件的第一行，默認為True。
index: 是否將行索引寫入Excel文件，默認為True。
startrow 和 startcol: 左上角單元格的行號和列號，用于開始寫入，默認為0。
engine: 用于寫入Excel文件的引擎，默認為None（將嘗試使用openpyxl或xlsxwriter）。

案例

# 創(chuàng)建一個簡單的DataFrame  
df = pd.DataFrame({  
    'A': [1, 2, 3, 4],  
    'B': ['foo', 'bar', 'baz', 'qux'],  
    'C': [1.0, 2.1, 3.2, 4.3]  
})  
  
# 寫入Excel文件  
df.to_excel('output.xlsx', sheet_name='Sheet1', index=False, header=True)  
  
# 如果要寫入多個工作表  
with pd.ExcelWriter('output_multiple_sheets.xlsx') as writer:  
    df.to_excel(writer, sheet_name='Sheet1', index=False)  
    df.to_excel(writer, sheet_name='Sheet2', index=False, startrow=10)  # 從第11行開始寫入

進階案例：寫入帶有樣式的Excel

為了寫入帶有樣式的Excel文件，你需要使用ExcelWriter和xlsxwriter或openpyxl引擎（取決于你的需求）。

# 使用xlsxwriter引擎寫入帶有樣式的Excel  
with pd.ExcelWriter('styled_output.xlsx', engine='xlsxwriter') as writer:  
    df.to_excel(writer, sheet_name='Sheet1', index=False)  
    workbook = writer.book  
    worksheet = writer.sheets['Sheet1']  
      
    # 創(chuàng)建一個格式對象  
    format = workbook.add_format({'bold': True, 'font_color': 'red'})  
      
    # 應(yīng)用格式到特定單元格  
    worksheet.write('A1', 'Styled Cell', format)

注意：xlsxwriter引擎在寫入時不支持直接修改已存在的DataFrame內(nèi)容（如通過DataFrame.style），它主要用于在寫入時添加額外的樣式或格式。如果你需要復(fù)雜的樣式處理，可能需要結(jié)合使用pandas和openpyxl（或xlsxwriter）的高級功能。

openpyxl

openpyxl 是一個用于讀寫 Excel 2010 xlsx/xlsm/xltx/xltm 文件的 Python 庫。它提供了豐富的接口來操作 Excel 文件，包括讀取、修改和寫入數(shù)據(jù)，以及設(shè)置樣式等。下面我將詳細解釋如何使用 openpyxl 操作 Excel，并給出案例代碼和進階案例。

安裝 openpyxl

首先，確保你已經(jīng)安裝了 openpyxl。如果還沒有安裝，可以通過 pip 安裝：

pip install openpyxl

基本操作

1.加載工作簿

使用 openpyxl.load_workbook() 函數(shù)加載一個現(xiàn)有的 Excel 文件。

from openpyxl import load_workbook  
  
wb = load_workbook(filename='example.xlsx')

2.激活工作表

通過工作簿對象獲取工作表。你可以通過工作表名稱或索引來訪問特定的工作表。

# 通過名稱  
ws = wb['Sheet1']  
  
# 或者通過索引（索引從0開始）  
ws = wb.worksheets[0]  
  
# 也可以使用 active 屬性獲取當前活動的工作表  
ws = wb.active

3.讀取數(shù)據(jù)

你可以通過單元格的坐標來讀取數(shù)據(jù)。

# 讀取單元格的值  
cell_value = ws['A1'].value  
print(cell_value)  
  
# 遍歷行  
for row in ws.iter_rows(values_only=True):  
    print(row)  
  
# 遍歷列  
for col in ws.iter_cols(values_only=True):  
    for value in col:  
        print(value)

4.寫入數(shù)據(jù)

你可以直接給單元格賦值來寫入數(shù)據(jù)。

# 寫入數(shù)據(jù)  
ws['B2'] = 'Hello, openpyxl!'  
  
# 保存修改  
wb.save('modified_example.xlsx')

語法參數(shù)詳解

由于 openpyxl 的功能非常廣泛，這里只列舉一些常用函數(shù)和方法的參數(shù)。

load_workbook(filename, read_only=False, data_only=False, keep_vba=True, ...)
- filename: Excel 文件路徑。
- read_only: 是否以只讀模式打開文件。
- data_only: 是否只讀取單元格的值，忽略公式（默認為 False）。
- keep_vba: 是否保留 VBA 內(nèi)容（默認為 True）。
Worksheet.iter_rows(min_row=None, max_row=None, min_col=None, max_col=None, values_only=False, ...)
- min_row, max_row, min_col, max_col: 指定迭代的行或列的范圍。
- values_only: 是否只迭代單元格的值（默認為 False，迭代單元格對象）。
Worksheet.iter_cols(...) 與 iter_rows 類似，但用于列迭代。

案例代碼

讀取并修改 Excel

from openpyxl import load_workbook  
  
# 加載工作簿  
wb = load_workbook('example.xlsx')  
  
# 激活工作表  
ws = wb.active  
  
# 讀取單元格數(shù)據(jù)  
print(ws['A1'].value)  
  
# 修改單元格數(shù)據(jù)  
ws['B2'] = 'New Value'  
  
# 保存修改后的工作簿  
wb.save('modified_example.xlsx')

進階案例

設(shè)置樣式

from openpyxl import Workbook  
from openpyxl.styles import Font, Color, Alignment, Border, Side  
  
# 創(chuàng)建一個新的工作簿和工作表  
wb = Workbook()  
ws = wb.active  
  
# 創(chuàng)建一個字體對象  
font = Font(name='Calibri', size=11, bold=True, italic=False, vertAlign=None,  
            underline='none', strike=False, color='FF0000')  
  
# 創(chuàng)建一個對齊對象  
alignment = Alignment(horizontal='general', vertical='bottom',  
                      text_rotation=0, wrap_text=False,  
                      shrink_to_fit=False, indent=0)  
  
# 創(chuàng)建一個邊框?qū)ο? 
thin = Side(border_style="thin", color="000000")  
border = Border(top=thin, left=thin, right=thin, bottom=thin)  
  
# 應(yīng)用樣式到單元格  
ws['A1'].font = font  
ws['A1'].alignment = alignment  
ws['A1'].border = border  
ws['A1'] = 'Styled Cell'  
  
# 保存工作簿  
wb.save('styled_workbook.xlsx')

在進階案例中，我們展示了如何創(chuàng)建字體、對齊和邊框樣式，并將它們應(yīng)用到單元格上。openpyxl 提供了豐富的樣式選項，可以滿足大多數(shù) Excel 樣式設(shè)置的需求。

xlrd

xlrd 是一個用于讀取 Excel 文件（主要是 .xls 和 .xlsx 格式，盡管對 .xlsx 的支持可能不如 openpyxl 全面）的 Python 庫。然而，需要注意的是，從版本 2.0.0 開始，xlrd 僅支持 .xls 格式的文件，不再支持 .xlsx。對于 .xlsx 文件，推薦使用 openpyxl 或 pandas（后者底層可以調(diào)用 openpyxl）。

安裝 xlrd

首先，確保你已經(jīng)安裝了 xlrd。如果還沒有安裝，可以通過 pip 安裝：

pip install xlrd==1.2.0  # 最好選擇一個支持.xlsx的舊版本

基本操作

加載工作簿

使用 xlrd.open_workbook() 函數(shù)加載 Excel 文件。

import xlrd  
  
# 加載工作簿  
workbook = xlrd.open_workbook('example.xls')

訪問工作表

通過索引或名稱訪問工作表。

# 通過索引訪問（索引從0開始）  
sheet = workbook.sheet_by_index(0)  
  
# 或者通過名稱訪問  
sheet = workbook.sheet_by_name('Sheet1')

讀取數(shù)據(jù)

使用行號（從0開始）和列號（也從0開始）或單元格名稱（如 'A1'）來讀取數(shù)據(jù)。

# 通過行號和列號讀取  
cell_value = sheet.cell_value(0, 0)  # 讀取第一行第一列的數(shù)據(jù)  
print(cell_value)  
  
# 或者使用 cell 方法（返回單元格對象，然后可以調(diào)用 value 屬性）  
cell = sheet.cell(0, 0)  
print(cell.value)  
  
# 通過單元格名稱讀取（需要安裝xlutils或類似庫來轉(zhuǎn)換A1表示法）  
# 注意：xlrd本身不直接支持A1表示法，這里僅作為說明  
# 通常，你會通過計算行號和列號來訪問單元格

語法參數(shù)詳解

由于 xlrd 的 API 相對簡單，這里主要關(guān)注 open_workbook() 和 sheet_by_...() 方法的參數(shù)。

xlrd.open_workbook(filename=None, ...)
- filename: Excel 文件路徑。
- 其他參數(shù)（如 on_demand、formatting_info 等）在較新版本的 xlrd 中可能不再支持或用途有限，特別是針對 .xlsx 文件的處理。
workbook.sheet_by_index(sheetx) 和 workbook.sheet_by_name(sheet_name)
- sheetx: 工作表的索引（整數(shù)）。
- sheet_name: 工作表的名稱（字符串）。

案例代碼

讀取 Excel 文件中的數(shù)據(jù)

import xlrd  
  
# 加載工作簿  
workbook = xlrd.open_workbook('example.xls')  
  
# 通過名稱訪問工作表  
sheet = workbook.sheet_by_name('Sheet1')  
  
# 讀取并打印第一行和第一列的數(shù)據(jù)  
print(sheet.cell_value(0, 0))  
  
# 遍歷所有行和列  
for row_idx in range(sheet.nrows):  
    for col_idx in range(sheet.ncols):  
        print(sheet.cell_value(row_idx, col_idx), end='\t')  
    print()  # 換行