为什么导出数据到.xlsx比.xls或.csv慢这么多？

Question

我有一个数据表格要导出到Excel，大家希望它是.xlsx格式。我用的是to_excel这个方法，但当我把文件后缀从.xls改成.xlsx时，导出的时间从1秒变成了大约9秒。导出成.csv格式的速度更快，我觉得这是因为.csv其实就是一种特别格式的文本文件。

可能是因为.xlsx文件增加了很多功能，所以写入的时间变长了，但我希望能找到一些方法来解决这个问题。

Answer 1

根据不同的Python与Excel模块的性能测试，pyexcelerate的表现更好。下面的代码用于将sqlite数据库中的表格数据导入到xlsx文件的工作表中。如果表格的原始数据量超过1000000行，那么这些信息就不会存储在xlsx文件里，而是会存储在csv文件中。

def passfile(datb, tables):
    """copy to xlsx or csv files tables from query results"""
    import sqlite3
    import pandas as pd
    import timeit
    import csv
    from pyexcelerate import Workbook
    from pathlib import Path
    from datetime import date
    dat_dir = Path("C:/XML")
    db_path = dat_dir / datb
    start_time = timeit.default_timer()
    conn = sqlite3.connect(db_path)                # database connection
    c = conn.cursor()
    today = date.today()
    tablist = []
    with open(tables, 'r') as csv_file:             # tables to be collected file
        csv_reader = csv.DictReader(csv_file)
        for line in csv_reader:
            tablist.append(line['table'])           #column header
    xls_file = "Param" + today.strftime("%y%m%d") + ".xlsx"
    xls_path = dat_dir / xls_file                   # xls file path-name
    csv_path = dat_dir / "csv"                      # csv path to store big data
    wb = Workbook()                                 # excelerator file init
    for line in tablist:
        try:
            df = pd.read_sql_query("select * from " + line + ";", conn)  # pandas dataframe from sqlite
            if len(df) > 1000000:                   # excel not supported
                print('save to csv')
                csv_loc = line + today.strftime("%y%m%d") + '.csv.gz'   # compressed csv file name
                df.to_csv(csv_path / csv_loc, compression='gzip')
            else:
                data = [df.columns.tolist()] + df.values.tolist()
                data = [[index] + row for index, row in zip(df.index, data)]
                wb.new_sheet(line, data=data)
        except sqlite3.Error as error:  # sqlite error handling
            print('SQLite error: %s' % (' '.join(error.args)))
    print("saving workbook")
    wb.save(xls_path)
    end_time = timeit.default_timer()
    delta = round(end_time - start_time, 2)
    print("Took " + str(delta) + " secs")
    c.close()
    conn.close()


passfile("20200522_sqlite.db", "tablesSQL.csv")

Answer 2

Pandas 默认使用 OpenPyXL 来写 xlsx 文件，这个速度可能比用 xlwt 模块写 xls 文件要慢。

你可以试试用 XlsxWriter 作为 xlsx 文件的输出引擎：

df.to_excel('file.xlsx', sheet_name='Sheet1', engine='xlsxwriter')

这样速度应该和 xls 引擎差不多快。