Create an excel file from BytesIO using python

Question

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])

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?

With my above code, I get to see the bytesIO data in console. But I don't know how to write it to excel file — user2961127, Dec 15 '19 at 22:06
Yes, in database the file is in varbinary format. Now, I am trying to export the varbinary data back to excel to make sure my code is working. — user2961127, Dec 15 '19 at 22:17
I’m not familiar with BytesIO, can you explain what it’s for/what it’s being used for here? — AMC, Dec 15 '19 at 22:32

score 5 · Accepted Answer · answered Dec 15 '19 at 23:08

Finally, I got solution.Below are the steps performed:

Takes Dataframe and convert it to excel and store it in memory in BytesIO format.
Store BytesIO object in Database column having varbinary(max)
Pull the stored BytesIO object and create an excel file locally.

Python Code:

#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")

You probably don't need the last steps with BytesIO and Pandas. You could just write the result of the query with something like `with open('outputFile.xlsx', 'wb') as xlsxfile: xlsxfile.write(result[0][0])`. — jmcnamara, Dec 16 '19 at 00:20
Ohk. I wanted to utilize `pandas` for file generation hence used it. But good direction though. — user2961127, Dec 16 '19 at 04:00

Create an excel file from BytesIO using python

1 Answers1

Linked