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?

Enzootic

Do you have a specific issue? What have you tried already? – Hepler

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 – Enzootic

Alright but you said you got it in the database, right? – Hepler

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. – Enzootic

I’m not familiar with BytesIO, can you explain what it’s for/what it’s being used for here? – Hepler

I figured out the solution. – Enzootic

Answer 1

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

1. Takes Dataframe and convert it to excel and store it in memory in BytesIO format.
2. Store BytesIO object in Database column having varbinary(max)
3. 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") 

Enzootic

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]). – Skite

Ohk. I wanted to utilize pandas for file generation hence used it. But good direction though. – Enzootic