摘要

在 Python 數(shù)據(jù)處理與分析場(chǎng)景中，Excel 文件作為經(jīng)典的數(shù)據(jù)存儲(chǔ)格式，其讀取與操作效率直接影響開(kāi)發(fā)流程。openpyxl 作為專(zhuān)注于 Excel 2007+（.xlsx/.xlsm）格式的原生庫(kù)，具備精細(xì)化操作單元格、樣式設(shè)置、圖表編輯等底層能力；而 pandas 依托強(qiáng)大的數(shù)據(jù)結(jié)構(gòu)與矢量化運(yùn)算，在批量數(shù)據(jù)讀取、清洗、轉(zhuǎn)換等場(chǎng)景中表現(xiàn)卓越。本文從技術(shù)原理、實(shí)戰(zhàn)案例、性能表現(xiàn)、適用場(chǎng)景四個(gè)維度，對(duì)兩種工具進(jìn)行全方位對(duì)比，通過(guò)完整代碼示例與流程圖拆解核心邏輯，幫助開(kāi)發(fā)者根據(jù)實(shí)際需求選擇最優(yōu)技術(shù)方案，同時(shí)規(guī)避常見(jiàn)操作陷阱，提升 Excel 數(shù)據(jù)處理效率。

1. 引言：Python 操作 Excel 的核心工具

Excel 文件憑借直觀的表格結(jié)構(gòu)、豐富的格式支持，廣泛應(yīng)用于辦公自動(dòng)化、數(shù)據(jù)報(bào)表、日志存儲(chǔ)等領(lǐng)域。在 Python 生態(tài)中，操作 Excel 的工具眾多，其中 openpyxl 與 pandas 是最具代表性的兩類(lèi)工具——二者定位互補(bǔ)，覆蓋不同場(chǎng)景的需求。

openpyxl 是 Python 原生的 Excel 操作庫(kù)，專(zhuān)注于 .xlsx/.xlsm 格式文件的讀寫(xiě)，支持單元格級(jí)別的精細(xì)化控制，無(wú)需依賴(lài)其他第三方工具，適合需要對(duì) Excel 文件進(jìn)行樣式編輯、公式計(jì)算、圖表生成等底層操作的場(chǎng)景。而 pandas 是 Python 數(shù)據(jù)分析的核心庫(kù)，以 DataFrame 數(shù)據(jù)結(jié)構(gòu)為核心，能夠快速讀取 Excel 數(shù)據(jù)并轉(zhuǎn)化為可直接分析的數(shù)據(jù)結(jié)構(gòu)，同時(shí)集成了數(shù)據(jù)清洗、篩選、聚合等一系列功能，適合批量數(shù)據(jù)處理與分析場(chǎng)景。

本文將通過(guò)實(shí)戰(zhàn)案例，系統(tǒng)對(duì)比二者的使用方式、性能差異與適用場(chǎng)景，為開(kāi)發(fā)者提供清晰的選型指南與操作參考。

2. 工具基礎(chǔ)：openpyxl 與 pandas 核心特性

2.1 openpyxl 核心特性

openpyxl 是一款開(kāi)源的 Excel 操作庫(kù)，僅支持 Excel 2007 及以上版本的 .xlsx/.xlsm 格式（不支持 .xls 格式，需借助 xlrd 等庫(kù)輔助），其核心特性包括：

• 精細(xì)化單元格操作：支持單元格值讀寫(xiě)、格式設(shè)置（字體、顏色、邊框）、公式注入、合并/拆分單元格等底層操作；
• 工作表管理：可創(chuàng)建、刪除、重命名工作表，支持工作表之間的數(shù)據(jù)復(fù)制與切換；
• 動(dòng)態(tài)數(shù)據(jù)處理：支持讀取 Excel 中的圖表、圖像、批注等元素，同時(shí)可生成新的圖表與批注；
• 低內(nèi)存占用：支持迭代器模式讀取大型 Excel 文件，避免一次性加載全部數(shù)據(jù)導(dǎo)致的內(nèi)存溢出；
• 兼容性強(qiáng)：支持 Excel 中的大部分公式與函數(shù)，生成的文件可直接用 Excel 打開(kāi)且格式保持一致。

安裝命令：pip install openpyxl

2.2 pandas 核心特性

pandas 并非專(zhuān)門(mén)的 Excel 操作庫(kù)，但其通過(guò)集成 openpyxl、xlrd 等庫(kù)，實(shí)現(xiàn)了 Excel 文件的高效讀寫(xiě)，核心優(yōu)勢(shì)集中在數(shù)據(jù)處理層面：

• 高效數(shù)據(jù)讀取：一鍵將 Excel 數(shù)據(jù)轉(zhuǎn)化為 DataFrame 結(jié)構(gòu)，支持指定工作表、列范圍、表頭行等參數(shù)；
• 強(qiáng)大的數(shù)據(jù)處理能力：集成數(shù)據(jù)清洗（去重、缺失值填充）、篩選、排序、聚合、合并等一系列操作，無(wú)需額外編寫(xiě)底層邏輯；
• 矢量化運(yùn)算：基于 NumPy 實(shí)現(xiàn)矢量化運(yùn)算，批量處理數(shù)據(jù)的效率遠(yuǎn)超循環(huán)迭代；
• 多格式兼容：除 Excel 外，還支持 CSV、JSON、SQL 等多種數(shù)據(jù)格式的讀寫(xiě)，適配全流程數(shù)據(jù)處理；
• 靈活的參數(shù)配置：支持跳過(guò)空行、指定數(shù)據(jù)類(lèi)型、處理日期格式等，滿(mǎn)足復(fù)雜場(chǎng)景需求。

安裝命令：pip install pandas openpyxl（pandas 讀取 .xlsx 文件需依賴(lài) openpyxl 庫(kù)）

3. 實(shí)戰(zhàn)演練：兩種工具讀取 Excel 的完整案例

本文以一個(gè)實(shí)際的 Excel 數(shù)據(jù)文件（data.xlsx）為測(cè)試對(duì)象，該文件包含“學(xué)生信息表”和“成績(jī)表”兩個(gè)工作表，其中“學(xué)生信息表”結(jié)構(gòu)如下：

3.1 openpyxl 實(shí)戰(zhàn)：讀取 Excel 數(shù)據(jù)并處理

openpyxl 讀取 Excel 需遵循“打開(kāi)工作簿→選擇工作表→讀取單元格數(shù)據(jù)”的流程，支持按行、按列或單個(gè)單元格讀取，同時(shí)可處理復(fù)雜格式與公式。

3.1.1 基礎(chǔ)讀?。鹤x取指定工作表全部數(shù)據(jù)

from openpyxl import load_workbook

# 1. 打開(kāi)工作簿（read_only=True 開(kāi)啟只讀模式，適合大型文件）
wb = load_workbook('data.xlsx', read_only=True)

# 2. 選擇工作表（兩種方式：按名稱(chēng)、按索引）
ws = wb['學(xué)生信息表']  # 按名稱(chēng)選擇
# ws = wb.worksheets[0]  # 按索引選擇（第一個(gè)工作表索引為0）

# 3. 讀取數(shù)據(jù)（按行迭代讀?。?
data = []
# 讀取表頭
headers = [cell.value for cell in ws[1]]  # ws[1] 表示第一行
data.append(headers)

# 讀取內(nèi)容行（從第二行開(kāi)始，直到最后一行有數(shù)據(jù)的行）
for row in ws.iter_rows(min_row=2, values_only=True):
    # values_only=True 直接返回單元格值，否則返回單元格對(duì)象
    if any(cell is not None for cell in row):  # 跳過(guò)空行
        data.append(row)

# 4. 關(guān)閉工作簿
wb.close()

# 打印結(jié)果
print("openpyxl 讀取結(jié)果：")
for row in data:
    print(row)

3.1.2 進(jìn)階操作：讀取指定單元格、處理公式

from openpyxl import load_workbook

# 打開(kāi)工作簿（默認(rèn)read_only=False，可讀取公式結(jié)果）
wb = load_workbook('data.xlsx', data_only=True)
ws = wb['成績(jī)表']

# 讀取單個(gè)單元格
cell_a2 = ws['A2'].value  # 讀取A2單元格值
print(f"A2單元格值：{cell_a2}")

# 讀取指定范圍單元格（B2到D4）
range_data = []
for row in ws['B2:D4']:
    row_values = [cell.value for cell in row]
    range_data.append(row_values)
print("指定范圍數(shù)據(jù)：")
for row in range_data:
    print(row)

# 處理公式（data_only=True 時(shí)，返回公式計(jì)算結(jié)果；否則返回公式字符串）
formula_cell = ws['E2'].value  # 假設(shè)E2是公式單元格（如=B2+C2+D2）
print(f"公式單元格結(jié)果：{formula_cell}")

wb.close()

3.2 pandas 實(shí)戰(zhàn)：讀取 Excel 數(shù)據(jù)并分析

pandas 讀取 Excel 核心函數(shù)為 pandas.read_excel()，可直接將數(shù)據(jù)轉(zhuǎn)化為 DataFrame，后續(xù)可快速進(jìn)行數(shù)據(jù)處理與分析。

3.2.1 基礎(chǔ)讀?。阂绘I讀取工作表數(shù)據(jù)

import pandas as pd

# 1. 讀取指定工作表數(shù)據(jù)（默認(rèn)讀取第一個(gè)工作表）
df = pd.read_excel('data.xlsx', sheet_name='學(xué)生信息表')

# 2. 查看數(shù)據(jù)基本信息
print("pandas 讀取結(jié)果（DataFrame）：")
print(df.head())  # 查看前5行數(shù)據(jù)
print("\n數(shù)據(jù)基本信息：")
print(df.info())  # 查看數(shù)據(jù)類(lèi)型、缺失值等信息
print("\n數(shù)據(jù)統(tǒng)計(jì)描述：")
print(df.describe())  # 數(shù)值型字段統(tǒng)計(jì)描述（年齡）

3.2.2 進(jìn)階操作：自定義讀取參數(shù)與數(shù)據(jù)處理

import pandas as pd

# 自定義參數(shù)讀取Excel
df = pd.read_excel(
    'data.xlsx',
    sheet_name='成績(jī)表',  # 指定工作表
    header=0,  # 第0行作為表頭（默認(rèn)）
    usecols=['學(xué)號(hào)', '姓名', '數(shù)學(xué)', '語(yǔ)文'],  # 只讀取指定列
    skiprows=[2],  # 跳過(guò)第2行（索引從0開(kāi)始）
    dtype={'學(xué)號(hào)': str},  # 指定學(xué)號(hào)為字符串類(lèi)型（避免數(shù)字截?cái)啵?
    parse_dates=False  # 不解析日期格式（如需解析可指定對(duì)應(yīng)列）
)

# 數(shù)據(jù)清洗：處理缺失值
df = df.dropna(subset=['數(shù)學(xué)', '語(yǔ)文'])  # 刪除數(shù)學(xué)、語(yǔ)文成績(jī)?nèi)笔У男?
df['數(shù)學(xué)'] = df['數(shù)學(xué)'].fillna(0)  # 缺失數(shù)學(xué)成績(jī)填充為0

# 數(shù)據(jù)篩選：篩選數(shù)學(xué)成績(jī)大于80分的學(xué)生
high_math_df = df[df['數(shù)學(xué)'] > 80]
print("數(shù)學(xué)成績(jī)大于80分的學(xué)生：")
print(high_math_df)

# 數(shù)據(jù)聚合：按班級(jí)分組計(jì)算平均成績(jī)（假設(shè)存在班級(jí)列）
if '班級(jí)' in df.columns:
    class_avg = df.groupby('班級(jí)')[['數(shù)學(xué)', '語(yǔ)文']].mean()
    print("\n各班級(jí)平均成績(jī)：")
    print(class_avg)

# 數(shù)據(jù)保存：將處理后的數(shù)據(jù)寫(xiě)入新Excel
high_math_df.to_excel('high_math_students.xlsx', index=False)
print("\n處理后的數(shù)據(jù)已保存至 high_math_students.xlsx")

4. 深度對(duì)比：功能、性能與適用場(chǎng)景

4.1 功能對(duì)比

4.2 性能對(duì)比

為驗(yàn)證兩種工具的性能差異，本文針對(duì)不同大小的 Excel 文件（100行、1萬(wàn)行、10萬(wàn)行）進(jìn)行讀取速度測(cè)試，測(cè)試環(huán)境為：Python 3.9、CPU i5-10400、內(nèi)存 16GB，測(cè)試結(jié)果如下（單位：秒）：

• 小規(guī)模文件（1萬(wàn)行以?xún)?nèi)）：pandas 速度優(yōu)于 openpyxl，默認(rèn)模式下效率最高；
• 大規(guī)模文件（10萬(wàn)行以上）：openpyxl 只讀迭代模式內(nèi)存占用更低，不易出現(xiàn)卡頓；pandas 可通過(guò)chunksize 參數(shù)分塊讀取，平衡速度與內(nèi)存占用；
• 數(shù)據(jù)處理附加場(chǎng)景：若讀取后需進(jìn)行篩選、聚合等操作，pandas 一站式處理效率遠(yuǎn)超 openpyxl（無(wú)需額外編寫(xiě)循環(huán)）。

4.3 適用場(chǎng)景對(duì)比

4.3.1 openpyxl 適用場(chǎng)景

• 需要對(duì) Excel 文件進(jìn)行精細(xì)化格式編輯（如設(shè)置字體、顏色、邊框，合并單元格）；
• 需操作 Excel 中的公式、圖表、批注等元素；
• 處理超大型 Excel 文件，需控制內(nèi)存占用（只讀迭代模式）；
• 辦公自動(dòng)化場(chǎng)景，需生成符合規(guī)范的 Excel 報(bào)表（格式嚴(yán)格要求）。

4.3.2 pandas 適用場(chǎng)景

• 數(shù)據(jù)分析師場(chǎng)景，需快速讀取 Excel 數(shù)據(jù)并進(jìn)行清洗、分析、可視化；
• 批量數(shù)據(jù)處理場(chǎng)景（如批量篩選、合并、統(tǒng)計(jì)匯總）；
• 多格式數(shù)據(jù)轉(zhuǎn)換場(chǎng)景（Excel 與 CSV、JSON、SQL 等格式互轉(zhuǎn)）；
• 小規(guī)模至中規(guī)模 Excel 文件（10萬(wàn)行以?xún)?nèi)）的快速讀取與處理。

5. 常見(jiàn)問(wèn)題與解決方案

5.1 openpyxl 常見(jiàn)問(wèn)題

問(wèn)題1：無(wú)法讀取 .xls 格式文件
解決方案：.xls 是 Excel 97-2003 格式，openpyxl 不支持，需先將文件轉(zhuǎn)為 .xlsx 格式，或借助 xlrd 庫(kù)讀取 .xls 文件后再用 openpyxl 處理。

問(wèn)題2：讀取公式單元格返回 None 或公式字符串
解決方案：加載工作簿時(shí)設(shè)置 data_only=True，返回公式計(jì)算結(jié)果；若仍為 None，說(shuō)明公式未在 Excel 中手動(dòng)計(jì)算過(guò)，需先打開(kāi) Excel 計(jì)算公式后再保存。

問(wèn)題3：讀取大型文件內(nèi)存溢出
解決方案：開(kāi)啟只讀模式load_workbook(read_only=True)，使用 iter_rows() 迭代讀取，避免一次性加載全部數(shù)據(jù)。

5.2 pandas 常見(jiàn)問(wèn)題

問(wèn)題1：讀取 Excel 時(shí)日期格式被解析為數(shù)字
解決方案：在 read_excel() 中設(shè)置 parse_dates=True，或指定 date_cols 參數(shù)明確日期列，自動(dòng)解析為 datetime 類(lèi)型。

問(wèn)題2：讀取大型文件出現(xiàn) MemoryError
解決方案：設(shè)置 chunksize 參數(shù)分塊讀?。ㄈ?chunksize=1000），逐塊處理數(shù)據(jù)后合并結(jié)果。

問(wèn)題3：表頭不規(guī)范（多行表頭、無(wú)表頭）
解決方案：無(wú)表頭時(shí)設(shè)置 header=None，手動(dòng)指定列名；多行表頭時(shí)設(shè)置 header=[0,1]，合并多行作為表頭。

6. 總結(jié)與選型建議

6.1 核心總結(jié)

openpyxl 與 pandas 作為 Python 操作 Excel 的核心工具，二者定位互補(bǔ)，無(wú)絕對(duì)優(yōu)劣之分：openpyxl 強(qiáng)在 Excel 底層操作與格式控制，適合辦公自動(dòng)化與精細(xì)化報(bào)表生成；pandas 強(qiáng)在數(shù)據(jù)處理效率與便捷性，適合數(shù)據(jù)分析與批量數(shù)據(jù)處理。

性能層面，小規(guī)模文件 pandas 更高效，大規(guī)模文件 openpyxl 內(nèi)存控制更優(yōu)；功能層面，openpyxl 覆蓋 Excel 格式與元素操作，pandas 覆蓋數(shù)據(jù)處理全流程，開(kāi)發(fā)者需根據(jù)實(shí)際需求選擇。

6.2 選型建議

• 若需求聚焦“Excel 格式與元素操作”，優(yōu)先選擇 openpyxl；
• 若需求聚焦“數(shù)據(jù)讀取與分析處理”，優(yōu)先選擇 pandas；
• 復(fù)雜場(chǎng)景（如生成格式規(guī)范的分析報(bào)表）：可組合使用二者——pandas 處理數(shù)據(jù)，openpyxl 美化報(bào)表格式。

以上就是Python使用openpyxl與pandas讀取Excel文件的對(duì)比詳解的詳細(xì)內(nèi)容，更多關(guān)于Python讀取Excel文件的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論