PythonのExcelライブラリ性能比較 – 大量データの読み書き速度を検証

import time
import pandas as pd
from openpyxl import Workbook
from openpyxl import load_workbook
from xlsxwriter import Workbook as XlsxWriterWorkbook
import xlrd
import xlwt
import xlwings as xw
import pylightxl as pl
import pyexcel

TEST_NUM = 30000

def test_openpyxl():
    # 書き込みテスト
    start_time = time.time()
    wb = Workbook()
    ws = wb.active
    for i in range(1, TEST_NUM+1):
        ws.append([f"Row {i}", i])
    wb.save("openpyxl_test.xlsx")
    write_time = time.time() - start_time

    # 読み込みテスト
    start_time = time.time()
    wb = load_workbook("openpyxl_test.xlsx")
    ws = wb.active
    data = [[cell.value for cell in row] for row in ws.iter_rows()]
    read_time = time.time() - start_time

    return write_time, read_time

def test_xlsxwriter():
    # 書き込みテスト
    start_time = time.time()
    wb = XlsxWriterWorkbook("xlsxwriter_test.xlsx")
    ws = wb.add_worksheet()
    for i in range(TEST_NUM):
        ws.write(i, 0, f"Row {i+1}")
        ws.write(i, 1, i+1)
    wb.close()
    write_time = time.time() - start_time

    # 読み込みにはpandasを使用（XlsxWriterは書き込み専用）
    start_time = time.time()
    df = pd.read_excel("xlsxwriter_test.xlsx")
    read_time = time.time() - start_time

    return write_time, read_time

def test_pandas():
    # 書き込みテスト
    start_time = time.time()
    df = pd.DataFrame({
        'Text': [f"Row {i}" for i in range(1, TEST_NUM+1)],
        'Number': range(1, TEST_NUM+1)
    })
    df.to_excel("pandas_test.xlsx", index=False, header=False) 
    write_time = time.time() - start_time

    # 読み込みテスト
    start_time = time.time()
    df = pd.read_excel("pandas_test.xlsx", header=None) 
    read_time = time.time() - start_time

    return write_time, read_time

def test_pylightxl():
    # 書き込みテスト
    start_time = time.time()
    db = pl.Database()
    db.add_ws("Sheet1")
    for i in range(1, TEST_NUM+1):
        db.ws("Sheet1").update_index(row=i, col=1, val=f"Row {i}")
        db.ws("Sheet1").update_index(row=i, col=2, val=i)
    pl.writexl(db=db, fn="pylightxl_test.xlsx")
    write_time = time.time() - start_time

    # 読み込みテスト
    start_time = time.time()
    db = pl.readxl("pylightxl_test.xlsx")
    data = [[db.ws("Sheet1").index(row=r, col=c) for c in range(1, 3)] 
            for r in range(1, TEST_NUM+1)]
    read_time = time.time() - start_time

    return write_time, read_time
    
def main():
    results = {}
    
    print("テスト開始...")
    
    results["OpenPyXL"] = test_openpyxl()
    results["XlsxWriter"] = test_xlsxwriter()
    results["Pandas"] = test_pandas()
    results["pylightxl"] = test_pylightxl()

    print(f"\nLinenum:{TEST_NUM}")

    print("\n結果（秒）:")
    print(f"{'ライブラリ':<15} {'書き込み時間':>12} {'読み込み時間':>12}")
    print("-" * 40)
    for lib, (write_time, read_time) in results.items():
        print(f"{lib:<15} {write_time:>12.3f} {read_time:>12.3f}")

if __name__ == "__main__":
    main()