Pandas输出Excel文件流

一、前言

业务有个需求，筛选后的结果导出excel，起初实现方案：先在本地生成一个临时excel文件，然后返回，感觉麻烦，随利用pandas生成一个Excel文件流，直接Response，不用考虑临时文件写/删问题。

二、实现

官网文档地址：https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html#writing-excel-files-to-memory

from io import BytesIO

bio = BytesIO()

# By setting the 'engine' in the ExcelWriter constructor.
writer = pd.ExcelWriter(bio, engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')

# Save the workbook
writer.save()

# Seek to the beginning and read to copy the workbook to a variable in memory
bio.seek(0)
workbook = bio.read()

 

三、示例

In [1]:  import pandas as pd                                                                                     

In [2]: order_info_dict = dict()                                                                                 

In [3]: order_info_dict["下单用户"] = ["hello", "world", "hha", "hehe", "xixi"]                                  

In [4]: order_info_dict["订单号"] = [1,2,3,4,5]                                                                  

In [5]: df = pd.DataFrame(order_info_dict)                                                                       

In [6]: from io import BytesIO                                                                                   

In [7]: bio = BytesIO()                                                                                          

In [8]: writer = pd.ExcelWriter(bio, engine='xlsxwriter')                                                        

In [9]: df.to_excel(writer, sheet_name='Sheet1', index=None)                                                                 

In [10]: writer.save()                                                                                           

In [11]: bio.seek(0)                                                                                             
Out[11]: 0

In [12]: workbook = bio.read()                                                                                   

In [13]: with open("1.xlsx", wb) as f:     # 此处代码只是演示，可以以字节形式写入文件。
    ...:     f.write(worbook)