Python中读取Excel最快的6种方法

在本文中，比较了从 Python 读取 Excel 的几种方法：

1、使用 Pandas 读取 Excel
Pandas 是 Python 的数据分析库，是用 Python 处理与数据有关的任何问题的首选，因此是一个很好的开始。

import pandas

def iter_excel_pandas(file: IO[bytes]) -> Iterator[dict[str, object]]:
    yield from pandas.read_excel(file).to_dict('records')

只需将两条命令串联起来，就能从 Excel 文件中获取字典列表。这是结果中的一行：

>>> with open('file.xlsx', 'rb') as f:
...     rows = iter_excel_pandas(f)
...     row = next(rows)
...     print(row)
...
{'boolean': True,
 'date': Timestamp('2000-01-01 00:00:00'),
 'decimal': 1.1,
 'number': 1,
 'text': 'CONTROL ROW'}

2、使用 Tablib 读取 Excel
Tablib是 Python 中最流行的库之一，用于导入和导出各种格式的数据。它最初是由流行requests库的创建者开发的，因此其特点是同样关注开发人员体验和人体工程学。

安装：
$ pip install tablib

代码：

import tablib

def iter_excel_tablib(file: IO[bytes]) -> Iterator[dict[str, object]]:
    yield from tablib.Dataset().load(file).dict

只需一行代码，该库就能完成所有繁重的工作。

在继续执行基准测试之前，我们先看看第一行的结果：

>>> with open('file.xlsx', 'rb') as f:
...     rows = iter_excel_tablib(f)
...     row = next(rows)
...     print(row)
...
OrderedDict([('number', 1),
             ('decimal', 1.1),
             ('date', datetime.datetime(2000, 1, 1, 0, 0)),
             ('boolean', True),
             ('text', 'CONTROL ROW')])

OrderedDict是 Python 的子类，dict具有一些额外的方法来重新排列字典顺序。它是在内置collections模块中定义的，当您请求字典时，它就是 tablib 返回的内容。由于OrderedDict是 的子类dict并且它是在内置模块中定义的，因此我们不介意并认为它足以满足我们的目的。

3、使用 Openpyxl 读取 Excel
Openpyxl是一个用 Python 读写 Excel 文件的库。与 Tablib 不同，Openpyxl 仅专用于 Excel，不支持任何其他文件类型。

事实上，tablib和pandas在读取 xlsx 文件时都在底层使用 Openpyxl。也许这种专业化会带来更好的表现。

安装：
$ pip install openpyxl

代码：

import openpyxl

def iter_excel_openpyxl(file: IO[bytes]) -> Iterator[dict[str, object]]:
    workbook = openpyxl.load_workbook(file)
    rows = workbook.active.rows
    headers = [str(cell.value) for cell in next(rows)]
    for row in rows:
        yield dict(zip(headers, (cell.value for cell in row)))

这次我们要写的代码更多一些，让我们来分解一下：

  • 从打开的文件中加载工作簿：函数 load_workbook 同时支持文件路径和可读数据流。在本例中，我们对打开的文件进行操作。
  • 获取活动工作表：Excel 文件可以包含多个工作表，我们可以选择读取哪个工作表。在本例中，我们只有一张工作表。
  • 构建页眉列表：Excel 文件的第一行包含页眉。要将这些页眉作为字典的键，我们需要读取第一行并生成页眉列表。
  • 返回结果：openpyxl 使用的单元格类型包含值和一些元数据。这对其他用途很有用，但我们只需要值。要访问单元格的值，我们使用 cell.value。

输出：

>>> with open('file.xlsx', 'rb') as f:
...     rows = iter_excel_openpyxl(f)
...     row = next(rows)
...     print(row)
{'boolean': True,
 'date': datetime.datetime(2000, 1, 1, 0, 0),
 'decimal': 1.1,
 'number': 1,
 'text': 'CONTROL ROW'}

4、使用 LibreOffice 读取 Excel
我们现在已经用尽了将 Excel 导入 Python 的传统且显而易见的方法。我们使用了顶级指定库并获得了不错的结果。现在是跳出框框思考的时候了。

LibreOffice是其他办公套件的免费开源替代品。LibreOffice 可以处理 xls 和 xlsx 文件，并且还恰好包含带有一些有用的命令行选项的无头模式：

LibreOffice 命令行选项之一是在不同格式之间转换文件。例如，我们可以使用 libreoffice 将 xlsx 文件转换为 csv 文件：

$ libreoffice --headless --convert-to csv --outdir . file.xlsx
convert file.xlsx -> file.csv using filter: Text - txt - csv (StarCalc)

$ head file.csv
number,decimal,date,boolean,text
1,1.1,01/01/2000,TRUE,CONTROL ROW
2,1.2,01/02/2000,FALSE,RANDOM TEXT:0.716658989024692
3,1.3,01/03/2000,TRUE,RANDOM TEXT:0.966075283958641

不错！让我们用 Python 将其拼接起来。我们首先将 xlsx 文件转换为 CSV，然后将 CSV 导入 Python：

import subprocess, tempfile, csv

def iter_excel_libreoffice(file: IO[bytes]) -> Iterator[dict[str, object]]:
    with tempfile.TemporaryDirectory(prefix='excelbenchmark') as tempdir:
        subprocess.run([
            'libreoffice', '--headless', '--convert-to', 'csv',
            '--outdir', tempdir, file.name,
        ])
        with open(f'{tempdir}/{file.name.rsplit(".")[0]}.csv', 'r') as f:
            rows = csv.reader(f)
            headers = list(map(str, next(rows)))
            for row in rows:
                yield dict(zip(headers, row))

让我们来分析一下：

  • 创建一个用于存储 CSV 文件的临时目录：使用内置的 tempfile 模块创建一个临时目录，完成后会自动清理。理想情况下，我们希望将特定文件转换为内存中的类文件对象，但 libreoffice 命令行不提供转换为特定文件的方法，只能转换为目录。
  • 使用 libreoffice 命令行将文件转换为 CSV：使用内置子进程模块执行操作系统命令。
  • 读取生成的 CSV：打开新创建的 CSV 文件，使用内置的 csv 模块进行解析并生成 dicts。

5、使用 DuckDB 读取 Excel
如果我们已经开始使用外部工具，为什么不给新人一个竞争的机会呢？
DuckDB是一个“进程内SQL OLAP数据库管理系统”。此描述并没有立即说明为什么 DuckDB 在这种情况下有用，但确实如此。DuckDB 非常擅长移动数据和格式之间的转换。

安装：
$ pip install duckdb

代码：

import duckdb

def iter_excel_duckdb(file: IO[bytes]) -> Iterator[dict[str, object]]:
    duckdb.install_extension('spatial')
    duckdb.load_extension('spatial')
    rows = duckdb.sql(f"""
        SELECT * FROM st_read(
            '{file.name}',
            open_options=['HEADERS=FORCE', 'FIELD_TYPES=AUTO'])
    """)
    while row := rows.fetchone():
        yield dict(zip(rows.columns, row))

让我们来分析一下：

  • 安装并加载空间扩展：要使用 duckdb 从 Excel 中导入数据，需要安装空间扩展。这有点奇怪，因为空间扩展用于地理操作，但这正是它想要的。
  • 查询文件：直接使用 duckdb 全局变量执行查询时，默认情况下会使用内存数据库，类似于使用 :memory: 选项的 sqlite。要实际导入 Excel 文件，我们使用 st_read 函数，并将文件路径作为第一个参数。在函数选项中，我们将第一行设置为标题，并激活自动检测类型的选项（这也是默认选项）。
  • 构建结果遍历每一行，并使用每一行的标题和值列表构建 dict。

6、使用 Calamine 读取 Excel
近年来，Python 中的每个性能问题似乎最终都用另一种语言来解决。作为一名 Python 开发人员，我认为这是一个真正的祝福。这意味着我可以继续使用我习惯的语言并享受所有其他语言的性能优势！

Calamine是一个纯 Rust 库，用于读取 Excel 和 OpenDocument 电子表格文件。要安装python-calamine（炉甘石的 Python 绑定），请执行以下命令：
$ pip install python-calamine

代码：

import python_calamine

def iter_excel_calamine(file: IO[bytes]) -> Iterator[dict[str, object]]:
    workbook = python_calamine.CalamineWorkbook.from_filelike(file)  # type: ignore[arg-type]
    rows = iter(workbook.get_sheet_by_index(0).to_python())
    headers = list(map(str, next(rows)))
    for row in rows:
        yield dict(zip(headers, row))


性能总结
总结一下使用Python读取Excel文件的方法以及读取Excel花费的时间：

Pandas    32.98    
Tablib    28.52    
Openpyxl    35.62  
Openpyxl (readonly)    24.79  
LibreOffice    15.27  
DuckDB (sql)    11.36    
DuckDB (execute)    5.73    
Calamine (python-calamine)    3.58  

那么您应该使用哪一个呢？这取决于...在选择在 Python 中处理 Excel 文件的库时，除了速度之外，还有一些其他考虑因素：

  • 写入能力：我们对读取 Excel 的方法进行了基准测试，但有时也需要生成 Excel 文件。我们进行基准测试的一些库不支持写入。例如 Calamine，不能写入 Excel 文件，只能读取。
  • 其他格式：系统可能需要加载和生成 Excel 以外的其他格式的文件。一些库，例如 pandas 和 Tablib 支持多种附加格式，而 calamine 和 openpyxl 仅支持 Excel。