How to write an Excel file to memory?

Question

I am writing a set of records in excel but I am not supposed to create a physical file. How do I write the records in excel in memory? If I do write it, how do I see the output of the records? I have tried:

import xlsxwriter
import io

filename= io.BytesIO()
workbook=xlsxwriter.Workbook(filename,{'in_memory': True})
worksheet=workbook.add_worksheet('sheet1')
worksheet.write(1,1,'Testing')
print(workbook.get_worksheet_by_name('sheet1'))
workbook.close()

Relief

What exactly is the problem with what you have (as far as writing the file to memory goes)? BTW, I think filename should be a string (and from the documentation it doesn't sound like you need to be using io.BytesIO anyway). – Teishateixeira

The issue i am facing is that i was able to write the contents from a database to an excel file, but i was told not to write t Ao a physical disk. I tried with io.stringIO() but no luck. The excel workbook has 10 sheets and i have to pipe it as a stream Any help will be appreciated. – Relief

After looking at the xlsxwriter source code, I don't think you can do what you want—it depends on being given the name of file. Using 'in_memory' prevents it from creating any temp files, but ultimately it needs the name of file that it can open (by name) and write data into. – Teishateixeira

Thank you Martineau. Yes your are right. I tweaked the code to look as follows and i was able to write in memory. with open("demo.xlsx", 'wb') as out: ## Open temporary file as bytes out.write(output.getvalue()) As mentioned by you, i had to supply a file name. – Relief

That means you are creating a (temporary) physical file. – Teishateixeira

That line was to check whether i am able to write to memory or not. The actual code is: workbook = xlsxwriter.Workbook(output, {'in_memory': True}) The above code wrote the xlsx file into memory – Relief

I am trying to read a streamed xlsx file. The user inputs the data via user interface and then the data is passed to me via streaming in xlsx. How do i read it ? I couldn't find any documentation. cat text.xlsx | python myprogram.py How do i read this stream ? Any help would be appreciated. I am not sure whether openpyxl allows this read. I am using python3 – Relief

If you are on a linux system, you could make a temporary filesystem in RAM using the methods found here and write your file into it. This is probably overkill for you application though. – Blalock

you could create a memory disk – Jehiah

Answer 1

From the XlsxWriter docs:

It is possible to write files to in-memory strings using BytesIO as follows:

    from io import BytesIO

    output = BytesIO()
    workbook = xlsxwriter.Workbook(output)
    worksheet = workbook.add_worksheet()

    worksheet.write('A1', 'Hello')
    workbook.close()


    xlsx_data = output.getvalue()

To avoid the use of any temporary files and keep the entire file in-memory use the in_memory constructor option shown above.

See also Example: Simple HTTP Server.

Pumphrey

Answer 2

If it's okay for you to use xlwt (seems to only work with old .xls files), you can try something like this:

import io
from xlwt import Workbook

file_excel = io.BytesIO()
workbook = Workbook(encoding='utf-8')
sheet = workbook.add_sheet('sheet1')

sheet.write(0, 0, 'column1')
sheet.write(0, 1, 'column2')
sheet.write(0, 2, 'column3')

sheet.write(1, 0, 'Field11')
sheet.write(1, 1, 'Field2')
sheet.write(1, 2, 'Field3')

workbook.save(file_excel)

data = file_excel.getvalue()

Source: https://www.programcreek.com/python/example/6932/xlwt.Workbook.

Potato