使用python从BytesIO创建一个excel文件 [英] Create an excel file from BytesIO using python
本文介绍了使用python从BytesIO创建一个excel文件的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用 pandas
库将 excel 存储到 bytesIO
内存中.后来,我将这个 bytesIO
对象存储到 SQL Server 中,如下所示 -
I am using pandas
library to store excel into bytesIO
memory. Later, I am storing this bytesIO
object into SQL Server as below-
df = pandas.DataFrame(data1, columns=['col1', 'col2', 'col3'])
output = BytesIO()
writer = pandas.ExcelWriter(output,engine='xlsxwriter')
df.to_excel(writer)
writer.save()
output.seek(0)
workbook = output.read()
#store into table
Query = '''
INSERT INTO [TABLE]([file]) VALUES(?)
'''
values = (workbook)
cursor = conn.cursor()
cursor.execute(Query, values)
cursor.close()
conn.commit()
#Create excel file.
Query1 = "select [file] from [TABLE] where [id] = 1"
result = conn.cursor().execute(Query1).fetchall()
print(result[0])
现在,我想从表中拉回 BytesIO 对象并创建一个 excel 文件并将其存储在本地.我该怎么做?
Now, I want to pull the BytesIO object back from table and create an excel file and store it locally. How Do I do it?
推荐答案
终于有了解决方案.以下是执行的步骤:
Finally, I got solution.Below are the steps performed:
- 获取 Dataframe 并将其转换为 excel 并以 BytesIO 格式存储在内存中.
- 在具有 varbinary(max) 的数据库列中存储 BytesIO 对象
- 拉取存储的 BytesIO 对象并在本地创建一个 excel 文件.
Python 代码:
#Get Required data in DataFrame:
df = pandas.DataFrame(data1, columns=['col1', 'col2', 'col3'])
#Convert the data frame to Excel and store it in BytesIO object `workbook`:
output = BytesIO()
writer = pandas.ExcelWriter(output,engine='xlsxwriter')
df.to_excel(writer)
writer.save()
output.seek(0)
workbook = output.read()
#store into Database table
Query = '''
INSERT INTO [TABLE]([file]) VALUES(?)
'''
values = (workbook)
cursor = conn.cursor()
cursor.execute(Query, values)
cursor.close()
conn.commit()
#Retrieve the BytesIO object from Database
Query1 = "select [file] from [TABLE] where [id] = 1"
result = conn.cursor().execute(Query1).fetchall()
WriteObj = BytesIO()
WriteObj.write(result[0][0])
WriteObj.seek(0)
df = pandas.read_excel(WriteObj)
df.to_excel("outputFile.xlsx")
这篇关于使用python从BytesIO创建一个excel文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文