代码之家 › 专栏 › 技术社区 › Ron Johnson

与xlrd相比,使用openpyxl读取Excel文件要慢得多

openpyxl xlrd python

Ron Johnson · 技术社区 · 9 年前

我有一个Excel电子表格,每天都需要导入SQL Server。电子表格将包含大约250000行,跨越大约50列。我已经使用 打开pyxl 和 xlrd公司 使用几乎相同的代码。

下面是我正在使用的代码(减去调试语句):

import xlrd
import openpyxl

def UseXlrd(file_name):
    workbook = xlrd.open_workbook(file_name, on_demand=True)
    worksheet = workbook.sheet_by_index(0)
    first_row = []
    for col in range(worksheet.ncols):
        first_row.append(worksheet.cell_value(0,col))
    data = []
    for row in range(1, worksheet.nrows):
        record = {}
        for col in range(worksheet.ncols):
            if isinstance(worksheet.cell_value(row,col), str):
                record[first_row[col]] = worksheet.cell_value(row,col).strip()
            else:
                record[first_row[col]] = worksheet.cell_value(row,col)
        data.append(record)
    return data


def UseOpenpyxl(file_name):
    wb = openpyxl.load_workbook(file_name, read_only=True)
    sheet = wb.active
    first_row = []
    for col in range(1,sheet.max_column+1):
        first_row.append(sheet.cell(row=1,column=col).value)
    data = []
    for r in range(2,sheet.max_row+1):
        record = {}
        for col in range(sheet.max_column):
            if isinstance(sheet.cell(row=r,column=col+1).value, str):
                record[first_row[col]] = sheet.cell(row=r,column=col+1).value.strip()
            else:
                record[first_row[col]] = sheet.cell(row=r,column=col+1).value
        data.append(record)
    return data

xlrd_results = UseXlrd('foo.xls')
openpyxl_resuts = UseOpenpyxl('foo.xls')

传递同一个包含3500行的Excel文件会产生截然不同的运行时间。使用 xlrd 我可以在2秒内将整个文件读入字典列表。使用 openpyxl 我得到以下结果:

Reading Excel File...
Read 100 lines in 114.14509415626526 seconds
Read 200 lines in 471.43183994293213 seconds
Read 300 lines in 982.5288782119751 seconds
Read 400 lines in 1729.3348784446716 seconds
Read 500 lines in 2774.886833190918 seconds
Read 600 lines in 4384.074863195419 seconds
Read 700 lines in 6396.7723388671875 seconds
Read 800 lines in 7998.775000572205 seconds
Read 900 lines in 11018.460735321045 seconds

虽然我可以使用 xlrd公司 在最后一个脚本中,由于各种问题(即int读取为float,date读取为int,datetime读取为floot),我将不得不硬编码大量格式。由于我需要为更多的导入重用此代码,所以尝试硬编码特定列以正确格式化它们,并且必须在4个不同的脚本中维护类似的代码是没有意义的。

有关于如何进行的建议吗?

3 回复 | 直到 9 年前

Mike Müller 9 年前

你可以 iterate 在纸张上:

def UseOpenpyxl(file_name):
    wb = openpyxl.load_workbook(file_name, read_only=True)
    sheet = wb.active
    rows = sheet.rows
    first_row = [cell.value for cell in next(rows)]
    data = []
    for row in rows:
        record = {}
        for key, cell in zip(first_row, row):
            if cell.data_type == 's':
                record[key] = cell.value.strip()
            else:
                record[key] = cell.value
        data.append(record)
    return data

这应该扩展到大型文件。如果列表 data 变得太大。

现在openpyxl版本的时间大约是xlrd版本的两倍:

%timeit xlrd_results = UseXlrd('foo.xlsx')
1 loops, best of 3: 3.38 s per loop

%timeit openpyxl_results = UseOpenpyxl('foo.xlsx')
1 loops, best of 3: 6.87 s per loop

请注意,xlrd和openpyxl可能对什么是整数和什么是浮点的解释略有不同。对于我的测试数据,我需要添加 float() 使产出具有可比性:

def UseOpenpyxl(file_name):
    wb = openpyxl.load_workbook(file_name, read_only=True)
    sheet = wb.active
    rows = sheet.rows
    first_row = [float(cell.value) for cell in next(rows)]
    data = []
    for row in rows:
        record = {}
        for key, cell in zip(first_row, row):
            if cell.data_type == 's':
                record[key] = cell.value.strip()
            else:
                record[key] = float(cell.value)
        data.append(record)
    return data

现在,两个版本对我的测试数据给出了相同的结果:

>>> xlrd_results == openpyxl_results
True

soartseng 5 年前

您多次调用“sheet.max_column”或“sheet.mix_row”。不要那样做。只需打一次电话。如果您在for循环中调用它,每次它都会计算一次max_column或max_row。

我修改如下,供您参考:

def UseOpenpyxl(file_name):
    wb = openpyxl.load_workbook(file_name, read_only=True)
    sheet = wb.active
    max_col = sheet.max_column
    max_row = sheet.max_row
    first_row = []
    for col in range(1,max_col +1):
        first_row.append(sheet.cell(row=1,column=col).value)
    data = []
    for r in range(2,max_row +1):
        record = {}
        for col in range(max_col):
            if isinstance(sheet.cell(row=r,column=col+1).value, str):
                record[first_row[col]] = sheet.cell(row=r,column=col+1).value.strip()
            else:
                record[first_row[col]] = sheet.cell(row=r,column=col+1).value
        data.append(record)
    return data

-2

MaxU - stand with Ukraine 9 年前

我觉得这是一个完美的候选人 Pandas 模块:

import pandas as pd
import sqlalchemy
import pyodbc

# pyodbc
#
# assuming the following:
# username: scott
# password: tiger
# DSN: mydsn
engine = create_engine('mssql+pyodbc://scott:tiger@mydsn')

# pymssql
#
#engine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname')


df = pd.read_excel('foo.xls')

# write the DataFrame to a table in the sql database
df.to_sql("table_name", engine)

的说明 DataFrame.to_sql() 作用

PS它应该很快而且很容易使用