利用pyxlsbwriter包实现写入xlsb和xlsx格式及读取效率的比较

原创于 2025-08-17 13:42:54 发布 · 758 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#pandas #数据库 #duckdb #sql #python

编程语言软件工程同时被 2 个专栏收录

271 篇文章

订阅专栏

数据库

182 篇文章

订阅专栏

calamine支持读入xlsb格式，但大文件用xlsb保存的例子很少，pyxlsbwriter是pypi.org上新上架的一个包，它能写入xlsb和xlsx格式文件。
于是让deepseek将它的例子改写成支持dataFrame, 然后就可以靠它打通duckdb, 用duckdb的查询结果写入文件了。

from pyxlsbwriter import XlsbWriter
import pandas as pd
from datetime import datetime
import duckdb

def df_to_xlsb(df, output_file, sheet_name="Sheet1", hidden=False, compression_level=6):
    """
    将 Pandas DataFrame 写入 XLSB 文件
    
    参数:
        df (pd.DataFrame): 要写入的DataFrame
        output_file (str): 输出文件名
        sheet_name (str): 工作表名称，默认为"Sheet1"
        hidden (bool): 是否隐藏工作表，默认为False
        compression_level (int): 压缩级别(0-9)，默认为6
    """
    # 将DataFrame转换为二维列表格式
    data = [df.columns.tolist()]  # 添加列名作为首行
    data.extend(df.values.tolist())  # 添加数据行
    
    # 写入XLSB文件
    with XlsbWriter(output_file, compressionLevel=compression_level) as writer:
        writer.add_sheet(sheet_name, hidden=hidden)
        writer.write_sheet(data)

# 示例用法
if __name__ == "__main__":

    sss="""
    select "Unique Key","Created Date", "Closed Date", "Latitude",  "Longitude" from 'NYC_311_SR_2010-2020-sample-1M.csv'
    """
    ss2="""
    select * from '5m Sales Records.csv'
    """
    df=duckdb.sql(ss2).df()
    print(df)
    # 写入XLSB文件
    df_to_xlsb(
        df=df,
        output_file="duckdbdf5.xlsb",
        sheet_name="Employees",
        hidden=False,
        compression_level=6
    )
    
    print("DataFrame successfully written to output.xlsb")

只要把上述代码中的

    # 写入XLSB文件
    with XlsbWriter(output_file, compressionLevel=compression_level) as writer:

改为

    # 写入XLSX文件
    with XlsxWriter(output_file, compressionLevel=compression_level) as writer:

同时在程序开头添加from pyxlsbwriter import XlsxWriter，其他代码原封不动就能实现写入xlsx文件，非常方便。
以下分别是写入100万行和500万行的结果。


[1000000 rows x 5 columns]
DataFrame successfully written to duckdbdf.xlsb


Kernel  Time =     0.468 =    4%
User    Time =    10.781 =  104%
Process Time =    11.250 =  108%    Virtual  Memory =   1076 MB
Global  Time =    10.330 =  100%    Physical Memory =    624 MB

[1000000 rows x 5 columns]
DataFrame successfully written to duckdbdf.xlsx


Kernel  Time =     0.500 =    4%
User    Time =    11.703 =  104%
Process Time =    12.203 =  108%    Virtual  Memory =   1559 MB
Global  Time =    11.243 =  100%    Physical Memory =   1033 MB

[5000000 rows x 14 columns]
DataFrame successfully written to duckdbdf5.xlsx


Kernel  Time =     5.546 =    6%
User    Time =    85.000 =   97%
Process Time =    90.546 =  103%    Virtual  Memory =  10870 MB
Global  Time =    87.103 =  100%    Physical Memory =   7467 MB

[5000000 rows x 14 columns]
DataFrame successfully written to duckdbdf5.xlsb


Kernel  Time =     1.828 =    2%
User    Time =    81.046 =  102%
Process Time =    82.875 =  104%    Virtual  Memory =   5878 MB
Global  Time =    79.040 =  100%    Physical Memory =   5415 MB

时间差不多，xlsb略快，但是写xlsb节省内存比较多, 查看文件大小，居然xlsx的文件更小，两个数据集都如此，这和网上的说法不一样。

2025/08/17  11:36       225,840,081 duckdbdf5.xlsx
2025/08/17  11:38       233,894,167 duckdbdf5.xlsb

2025/08/17  11:25        28,704,798 duckdbdf.xlsx
2025/08/17  11:20        32,590,397 duckdbdf.xlsb

其实xlsb外壳也是zip,只是其中保存的.xml变成了.bin格式，可能zip方便压缩文本，不方便压缩二进制。

再看rusty_sheet对两种格式读取的效果对比，官方excel插件因不支持xlsb格式，无法测试。

D create table xlsx as from read_sheet('duckdbdf5.xlsx',header=1);

Run Time (s): real 70.917 user 61.187500 sys 8.734375
D create table xlsb as from read_sheet('duckdbdf5.xlsb',header=1);

Run Time (s): real 7.639 user 7.312500 sys 0.281250

看起来很好，读取xlsb只用了xlsx十分之一的时间。但是查询行数，xlsb少了很多，是catamine读取xlsb文件时丢数据？还是xlsb规范就支持那么多行？再用wps打开，两种后缀都是只显示1,048,576行，在微软网站看到xlsx格式单表最多1,048,576行，那为什么catamine却能从xlsx文件读出500万行, 需要进一步研究。

D select count(*) from xlsx;
┌────────────────┐
│  count_star()  │
│     int64      │
├────────────────┤
│    5000000     │
│ (5.00 million) │
└────────────────┘
Run Time (s): real 0.007 user 0.000000 sys 0.000000
D select count(*) from xlsb;
┌────────────────┐
│  count_star()  │
│     int64      │
├────────────────┤
│    1048576     │
│ (1.05 million) │
└────────────────┘
Run Time (s): real 0.007 user 0.000000 sys 0.000000
变通的方法是加sheet,就能存更多行，然后用read_sheet指定xlsx的sheet，再把结果union all就行了。如果能保持这个比率，总体算下来还是比读入单sheet的xlsx文件快。

实际测试一下

D create table xlsb as from read_sheet('duckdbdf5.xlsb',header=1,sheet_name='Sheet1')
union all from read_sheet('duckdbdf5.xlsb',header=1,sheet_name='Sheet2')
union all from read_sheet('duckdbdf5.xlsb',header=1,sheet_name='Sheet3')
union all from read_sheet('duckdbdf5.xlsb',header=1,sheet_name='Sheet4')
union all from read_sheet('duckdbdf5.xlsb',header=1,sheet_name='Sheet5');
100% ▕████████████████████████████████████████████████████████████▏
Run Time (s): real 33.616 user 31.484375 sys 2.000000
D select count(*) from xlsb;
┌────────────────┐
│  count_star()  │
│     int64      │
├────────────────┤
│    5000000     │
│ (5.00 million) │
└────────────────┘
Run Time (s): real 0.007 user 0.000000 sys 0.000000

执行时间与预想的一致，所以xlsb格式确实能提高读取效率。
以下是分Sheet写入XLSB文件deepseek代码

from pyxlsbwriter import XlsbWriter
import pandas as pd
from datetime import datetime
import math
import duckdb

def df_to_xlsb(
    df, 
    output_file, 
    sheet_prefix="Sheet", 
    hidden=False, 
    compression_level=6,
    max_rows_per_sheet=1048575  # XLSB单Sheet最大行数
):
    """
    将大型Pandas DataFrame分Sheet写入XLSB文件
    
    参数:
        df (pd.DataFrame): 要写入的DataFrame
        output_file (str): 输出文件名
        sheet_prefix (str): 工作表名称前缀，默认为"Sheet"
        hidden (bool): 是否隐藏工作表，默认为False
        compression_level (int): 压缩级别(0-9)，默认为6
        max_rows_per_sheet (int): 每个Sheet最大行数，默认为1,048,576
    """
    # 计算需要的Sheet数量
    total_rows = len(df)
    num_sheets = math.ceil(total_rows / max_rows_per_sheet)
    
    # 生成Sheet名称列表
    sheet_names = [f"{sheet_prefix}{i+1}" for i in range(num_sheets)]
    
    with XlsbWriter(output_file, compressionLevel=compression_level) as writer:
        for i, sheet_name in enumerate(sheet_names):
            # 计算当前Sheet的行范围
            start_row = i * max_rows_per_sheet
            end_row = min((i + 1) * max_rows_per_sheet, total_rows)
            
            # 获取当前Sheet的数据块
            chunk = df.iloc[start_row:end_row]
            
            # 准备写入数据(列名+数据)
            data = [chunk.columns.tolist()]  # 添加列名作为首行
            data.extend(chunk.values.tolist())  # 添加数据行
            
            # 创建并写入Sheet
            writer.add_sheet(sheet_name, hidden=hidden)
            writer.write_sheet(data)
            
            print(f"写入Sheet: {sheet_name}, 行数: {len(chunk)}")

# 示例用法
if __name__ == "__main__":

    ss2="""
    select * from '5m Sales Records.csv'
    """
    df=duckdb.sql(ss2).df()
    # 写入XLSB文件(自动分Sheet)
    df_to_xlsb(
        df=df,
        output_file="large_data.xlsb",
        sheet_prefix="Sheet",
        hidden=False,
        compression_level=6
    )
    
    print("大型DataFrame已成功分Sheet写入XLSB文件")

它的执行时间与不分sheet基本一致

timer64 python xlsbsheets.py
写入Sheet: Sheet1, 行数: 1048575
写入Sheet: Sheet2, 行数: 1048575
写入Sheet: Sheet3, 行数: 1048575
写入Sheet: Sheet4, 行数: 1048575
写入Sheet: Sheet5, 行数: 805700
大型DataFrame已成功分Sheet写入XLSB文件


Kernel  Time =     2.218 =    2%
User    Time =    79.515 =  102%
Process Time =    81.734 =  105%    Virtual  Memory =   5662 MB
Global  Time =    77.788 =  100%    Physical Memory =   5198 MB