Python如何导入Excel——全网最全实战指南

在数据处理领域,Excel作为最广泛使用的电子表格工具,其数据格式(.xls/.xlsx)承载着海量业务信息。而Python凭借其强大的数据科学生态,已成为自动化处理Excel数据的首选语言。本文系统性梳理Python导入Excel的各类主流方案,涵盖pandas、openpyxl、xlrd三大核心库,深入解析其适用场景、性能特征、异常处理机制及企业级实践路径,助您构建高效、健壮的数据导入流水线。

无论您是Excel新手程序员,还是寻求自动化升级的数据分析师,本文均提供可落地的解决方案。内容严格遵循:原理→代码→调试→优化→扩展五步法,确保知识结构完整、实操性强。

当前主流Excel格式分为两类:.xls(Excel 97-2003,基于BIFF二进制格式)与.xlsx(Excel 2007+,基于Office Open XML标准的ZIP压缩包)。不同格式需采用不同解析策略,此为导入流程的首要决策点。

一、基础准备:环境与依赖

在正式导入前,需完成基础环境配置。推荐使用Python 3.8+版本,确保pip包管理器可用。通过以下命令安装核心依赖:

pip install pandas openpyxl xlrd==1.2.0

【注意】xlrd自2.0.0起已移除.xlsx支持,仅保留.xls兼容性,因此处理旧版Excel时需锁定版本号为1.2.0。若需处理.xlsx文件,请改用openpyxl或pandas内置引擎。

验证安装是否成功:

import pandas as pd
import openpyxl
import xlrd
print(pd.__version__)  # 输出示例:2.0.3
print(openpyxl.__version__)  # 输出示例:3.1.2

【最佳实践】建议使用虚拟环境(venv或conda)隔离项目依赖,避免版本冲突。创建虚拟环境命令:

python -m venv excel_env
source excel_env/bin/activate  # Linux/macOS
excel_env\Scripts\activate     # Windows

常见安装问题解决方案:

二、pandas方案:高效数据导入首选

pandas作为Python数据科学核心库,其read_excel()函数封装了底层解析逻辑,支持自动推断数据类型、缺失值处理、多Sheet读取等功能,是大多数场景的最优解。

2.1 基础读取

import pandas as pd
# 读取单Sheet(默认读取第一个)
df = pd.read_excel('sales_data.xlsx')
# 指定Sheet名称或索引
df_q1 = pd.read_excel('sales_data.xlsx', sheet_name='Q1')
df_q2 = pd.read_excel('sales_data.xlsx', sheet_name=1)  # 索引从0开始

【输出示例】
┌─────┬────────────┬───────────────┬─────────┬────────┐
│ │ 日期 │ 产品 │ 数量 │ 单价 │
├─────┼────────────┼───────────────┼─────────┼────────┤
│ 0 │ 2025-01-05 │ Python教材 │ 150 │ 89.00 │
│ 1 │ 2025-01-06 │ 数据分析课 │ 28 │ 299.00 │
│ 2 │ 2025-01-07 │ 云服务VIP │ 12 │ 999.00 │
└─────┴────────────┴───────────────┴─────────┴────────┘

2.2 关键参数详解

参数类型默认值说明
iostr/bytes必填Excel文件路径或文件对象
sheet_namestr/int/list0Sheet名称、索引或列表(如[0,'Sheet2'])
headerint/list0行号作为列名(None表示无表头)
nameslistNone自定义列名列表(覆盖原列名)
usecolsstr/int/listNone指定读取列(如'A:C', [0,2]或['日期','单价'])
dtypedictNone强制指定列数据类型(如{'数量':int,'单价':float})
skiprowsint/listNone跳过行(数字表示跳过前N行,列表指定跳过行号)
nrowsintNone仅读取前N行

2.3 高级用例

案例1:处理合并单元格
pandas默认将合并单元格的值复制到所有行,需配合fillna()填充:

df = pd.read_excel('merged.xlsx')
df['分类'] = df['分类'].ffill()  # 向前填充缺失值

案例2:跳过页脚注释
当Excel末尾存在“数据来源:财务部”等注释时:

df = pd.read_excel('report.xlsx', skipfooter=3)

案例3:多Sheet合并
读取多个Sheet并横向拼接:

all_sheets = pd.read_excel('multi_sheet.xlsx', sheet_name=None)
df_combined = pd.concat(all_sheets.values(), axis=1)

三、openpyxl方案:精确控制.xlsx文件结构

openpyxl是专为.xlsx格式设计的底层库,支持读写、样式修改、公式计算等高级操作。当需要保留原Excel格式或进行复杂数据操作时,openpyxl是不可替代的选择。

3.1 基础读取

from openpyxl import load_workbook
wb = load_workbook('inventory.xlsx', data_only=True)  # data_only=True读取公式值
ws = wb.active  # 获取活动工作表
# 读取单个单元格
cell_value = ws['B5'].value
# 读取整行
row_data = [cell.value for cell in ws[3]]
# 读取整列
col_data = [cell.value for cell in ws['C']]
# 遍历所有数据
for row in ws.iter_rows(min_row=2, values_only=True):
    print(row)

3.2 与pandas协同

将openpyxl读取的数据转换为DataFrame:

from openpyxl import load_workbook
import pandas as pd
wb = load_workbook('data.xlsx', data_only=True)
ws = wb.active
# 获取所有数据(含表头)
data = list(ws.iter_rows(min_row=1, values_only=True))
df = pd.DataFrame(data[1:], columns=data[0])  # 跳过表头行

3.3 保留格式读取

当需要获取单元格颜色、字体等样式信息时,需禁用data_only参数:

wb = load_workbook('styled.xlsx')
ws = wb.active
cell = ws['D10']
print(cell.fill.start_color.rgb)  # 输出颜色代码:FF037EF3
print(cell.font.name)             # 输出字体:'Microsoft YaHei UI'

四、xlrd方案:兼容旧版.xls文件

尽管xlrd已停止更新,但在处理大量历史.xls文件时仍具不可替代性。其核心优势在于轻量级、解析速度快。

4.1 读取.xls文件

import xlrd
wb = xlrd.open_workbook('archive_data.xls')
sheet = wb.sheet_by_index(0)
# 获取行数和列数
nrows, ncols = sheet.nrows, sheet.ncols
# 读取特定单元格
value = sheet.cell_value(2, 1)  # 第3行第2列(索引从0开始)
# 遍历所有数据
for row_idx in range(1, nrows):
    row_data = sheet.row_values(row_idx)
    print(row_data)

4.2 日期处理

xlrd将Excel日期存储为浮点数(1900年序列号),需转换为Python datetime:

import xlrd
from datetime import datetime, timedelta
def excel_date(date_tuple):
    """将Excel序列号转换为datetime"""
    year, month, day, hour, minute, second = xlrd.xldate_as_tuple(date_tuple, 0)
    return datetime(year, month, day, hour, minute, second)
# 示例
excel_serial = 45000  # 对应2023-01-01
dt = excel_date(excel_serial)  # 输出:datetime(2023, 1, 1, 0, 0)

4.3 与pandas集成

import xlrd
import pandas as pd
wb = xlrd.open_workbook('legacy.xlsx')
sheet = wb.sheet_by_index(0)
# 转换为列表
data = [sheet.row_values(i) for i in range(sheet.nrows)]
df = pd.DataFrame(data[1:], columns=data[0])

五、三大方案对比:性能与适用场景

性能测试结果(10万行×20列.xlsx)

方案读取时间内存占用适用场景
pandas1.8秒中(约120MB)通用数据处理、分析、清洗
openpyxl2.4秒高(约280MB)需保留格式/公式/样式
xlrd1.2秒低(约80MB)仅读取.xls数据

【选择建议】

六、常见错误及解决方案

路径相关错误

错误现象:FileNotFoundError: [Errno 2] No such file or directory
解决方案:

# 使用绝对路径
df = pd.read_excel(r'C:\data\sales.xlsx')  # Windows需加r前缀
# 检查文件是否存在
import os
if os.path.exists('sales.xlsx'):
    df = pd.read_excel('sales.xlsx')
else:
    print("文件不存在,请检查路径!")

【跨平台建议】使用pathlib.Path统一路径处理:

from pathlib import Path
file_path = Path('data') / 'sales.xlsx'
if file_path.exists():
    df = pd.read_excel(file_path)

格式兼容性问题

错误现象:ValueError: Excel file format cannot be determined
典型场景:

  • 尝试用xlrd读取.xlsx文件(xlrd≥2.0)
  • 文件被损坏或非标准格式

解决方案:

# 方案1:使用openpyxl处理.xlsx
df = pd.read_excel('data.xlsx', engine='openpyxl')
# 方案2:检测文件扩展名
ext = Path('file').suffix.lower()
if ext == '.xls':
    df = pd.read_excel('file', engine='xlrd')
elif ext == '.xlsx':
    df = pd.read_excel('file', engine='openpyxl')

数据类型自动转换异常

错误现象:数字被识别为字符串,或日期显示为序列号
解决方案:

# 强制指定列类型
df = pd.read_excel('data.xlsx', dtype={'手机号': str, '金额': float})
# 日期列转换
df['日期'] = pd.to_datetime(df['日期'], format='%Y/%m/%d', errors='coerce')

【高级技巧】使用converters参数自定义转换逻辑:

def clean_phone(x):
    return str(x).strip() if pd.notna(x) else ''
df = pd.read_excel('contacts.xlsx', converters={'电话': clean_phone})

大文件内存溢出

错误现象:MemoryError或系统卡死
解决方案:

  • 分块读取:
    chunk_size = 10000
    for chunk in pd.read_excel('large.xlsx', chunksize=chunk_size):
        process(chunk)
  • 仅读取必要列:
    df = pd.read_excel('large.xlsx', usecols=['ID', 'Name', 'Amount'])
  • 转换数据类型:
    df['Category'] = df['Category'].astype('category')  # 减少内存占用

七、性能优化实战:企业级导入流水线

7.1 多线程并发处理

当需处理多个Excel文件时,可利用线程池加速:

import pandas as pd
from concurrent.futures import ThreadPoolExecutor
from pathlib import Path
def load_file(file_path):
    return pd.read_excel(file_path)
files = list(Path('data/').glob('*.xlsx'))
with ThreadPoolExecutor(max_workers=4) as executor:
    dfs = list(executor.map(load_file, files))
# 合并所有数据
df_all = pd.concat(dfs, ignore_index=True)

【注意】单文件读取不适用多线程,因pandas本身已实现内部并行优化。

7.2 缓存机制

对重复读取的文件启用缓存:

import hashlib
import pickle
from pathlib import Path
def cached_read_excel(path, cache_dir='cache'):
    # 生成文件哈希值
    file_hash = hashlib.md5(Path(path).read_bytes()).hexdigest()
    cache_path = Path(cache_dir) / f"{Path(path).stem}_{file_hash}.pkl"
    if cache_path.exists():
        return pd.read_pickle(cache_path)
    df = pd.read_excel(path)
    cache_path.parent.mkdir(exist_ok=True)
    df.to_pickle(cache_path)
    return df

7.3 异步处理(asyncio)

适用于网络存储(如S3)上的Excel文件:

import asyncio
import aiohttp
import pandas as pd
from io import BytesIO
async def download_excel(url):
    async with aiohttp.ClientSession() as session:
        async with session.get(url) as resp:
            data = await resp.read()
            return pd.read_excel(BytesIO(data))
async def main():
    urls = ['https://example.com/file1.xlsx', 'https://example.com/file2.xlsx']
    tasks = [download_excel(url) for url in urls]
    dfs = await asyncio.gather(*tasks)
    return pd.concat(dfs)
# 运行
# asyncio.run(main())

八、自动化脚本:构建企业级数据管道

8.1 定时导入+邮件通知

每日凌晨自动导入销售数据并发送汇总邮件:

import pandas as pd
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from datetime import datetime
def daily_import():
    # 1. 导入数据
    df = pd.read_excel('sales_daily.xlsx')
    # 2. 数据处理
    summary = df.groupby('产品')['数量'].sum().sort_values(ascending=False)
    # 3. 生成报告
    report = summary.to_string()
    # 4. 发送邮件
    msg = MIMEMultipart()
    msg['From'] = 'bot@company.com'
    msg['To'] = 'manager@company.com'
    msg['Subject'] = f'销售日报-{datetime.now().date()}'
    msg.attach(MIMEText(report, 'plain'))
    server = smtplib.SMTP('smtp.company.com', 587)
    server.starttls()
    server.login('bot@company.com', 'password')
    server.sendmail(msg['From'], msg['To'], msg.as_string())
    server.quit()
# 定时执行(Linux用crontab)
# 0 8 * * * python /path/to/daily_import.py

8.2 Web自动化(Selenium + pandas)

自动下载网页Excel并导入:

from selenium import webdriver
from selenium.webdriver.common.by import By
import pandas as pd
import time
# 配置浏览器(无头模式)
options = webdriver.ChromeOptions()
options.add_argument('--headless')
driver = webdriver.Chrome(options=options)
driver.get('https://example.com/reports')
driver.find_element(By.LINK_TEXT, '今日数据').click()
time.sleep(5)  # 等待下载完成
# 导入已下载文件
df = pd.read_excel('downloads/sales_20250101.xlsx')
print(f'导入成功,共{len(df)}条记录')

九、网友最关心的10个问题

Q1:Python能直接修改Excel文件而不生成新文件吗?

A:可以。openpyxl支持原地修改:

from openpyxl import load_workbook
wb = load_workbook('report.xlsx')
ws = wb.active
ws['D10'] = '已更新'
wb.save('report.xlsx')  # 覆盖原文件

【警告】pandas默认仅支持写入新文件,需先读取再覆盖:

df = pd.read_excel('data.xlsx')
df.to_excel('data.xlsx', index=False)  # 会覆盖原文件

Q2:如何处理包含公式的Excel?

A:两种方案:

  1. 读取公式计算值:
    df = pd.read_excel('calc.xlsx', engine='openpyxl',
                       read_only=True, data_only=True)
  2. 保留公式结构:
    from openpyxl import load_workbook
    wb = load_workbook('calc.xlsx', data_only=False)  # data_only=False
    ws = wb.active
    print(ws['E5'].value)  # 输出:=SUM(C2:D2)

Q3:导入时中文显示乱码怎么办?

A:通常因文件编码非UTF-8导致。解决方案:

  • Excel另存为“CSV UTF-8(逗号分隔)”格式
  • 使用pandas指定编码:
    df = pd.read_excel('data.xlsx',
                       converters={'列名': lambda x: str(x).encode('latin1').decode('gbk')})

Q4:如何跳过空行?

A:pandas提供skip_blank_lines参数:

df = pd.read_excel('data.xlsx', skip_blank_lines=True)

或手动过滤:

df = df.dropna(how='all')  # 删除全空行

Q5:导入后日期显示为数字(如44927)?

A:使用pandas的parse_dates参数:

df = pd.read_excel('data.xlsx', parse_dates=['日期列名'])

或手动转换:

df['日期'] = pd.to_datetime(df['日期'], unit='D', origin='1899-12-30')

Q6:支持导入Excel Online文件吗?

A:直接支持。pandas可读取共享链接:

url = 'https://1drv.ms/x/s!A.../sales.xlsx?download=1'
df = pd.read_excel(url)

【注意】需确保链接为“任何人拥有链接可查看”权限。

Q7:如何处理超长数字(如身份证号)?

A:Excel会自动转为科学计数法,需强制字符串读取:

df = pd.read_excel('data.xlsx', dtype={'身份证号': str})

或使用converters:

df = pd.read_excel('data.xlsx',
                   converters={'身份证号': lambda x: f'{int(x):018d}' if pd.notna(x) else ''})

Q8:能导入带密码保护的Excel吗?

A:pandas原生不支持,需先用其他工具解密:

  • Windows:用Excel打开输入密码后另存为无密码文件
  • 跨平台:使用olefile库提取数据(仅限.xls)

Q9:如何导出为Excel?

A:pandas的to_excel方法:

df.to_excel('output.xlsx',
            sheet_name='结果',
            index=False,
            startrow=1,
            header=True)

支持多Sheet写入:

with pd.ExcelWriter('report.xlsx') as writer:
    df_sales.to_excel(writer, sheet_name='销售')
    df_inventory.to_excel(writer, sheet_name='库存')

Q10:性能对比中openpyxl为何内存更高?

A:openpyxl加载整个文件到内存对象树(包含样式、合并单元格等元数据),而pandas仅保留核心数据。解决方案:

  • 使用data_only=True减少元数据加载
  • 分块读取:for row in ws.iter_rows(values_only=True): ...
  • 处理后立即释放:del wb + gc.collect()